Toad World® Forums

SQL Optimizer ( Index exercises )


#1

I have a small application with just procs()and functions and 62 tables… We Call the delta extract ins schema called ( HPXR_EDW)… pulling data from another schema (HPXR) … Simple daily updates, inserts and deletes… The sql is dynamic is it possible to Run the Delta Ex. program in the optimizer? b/c I did the Optim index and SQL on views and Procs() and fucntions with minimine success …It can’t see the dynamic sql getting created … Thats my issue… Thx

Jeff


#2

Hi - would you be able to post the SQL you are trying to optimize or send it to me using a direct message?


#3

You have to capture those dynamic SQL from SQL Area after/during the execution of the proc program. You can use our “Inspect SGA” to capture those dynamic SQL.


#4

Ok, I figured it out also…

Now, do I sent it to Batch optimizer SQL ? I am trying to see if this can figure out if Indexing are needed in this execution?

But very cool for sure !!!

FYI_ you guys need better videos on steps… I am former OCS . Oracle! lol

| Jeffrey | HCCO, Inc. | (585) 236-4303 |

From: Richard To [mailto:bounce-Richard_To@toadworld.com]
Sent: Tuesday, January 28, 2014 8:53 PM
To: sqloptoracle@toadworld.com
Subject: RE: [SQL Optimizer for Oracle - Discussion Forum] SQL Optimizer ( Index exercises )

RE: SQL Optimizer ( Index exercises )

Reply by Richard To

You have to capture those dynamic SQL from SQL Area after/during the execution of the proc program. You can use our “Inspect SGA” to capture those dynamic SQL.

To reply, please reply-all to this email.

Stop receiving emails on this subject.
Or Unsubscribe from SQL Optimizer for Oracle - General notifications altogether.
SQL Optimizer for Oracle - Discussion Forum

Flag this post as spam/abuse.


#5

Optimize Indexes is the place to send your SQL statements. It will evaluate multiple SQL statements to come up with suggestions of indexes.

If you have a particular SQL your want to optimize by rewriting the SQL, you can send this SQL to the Optimize SQL module.

If you have a PL/SQL script or a stored procedure/function, you can send it to the Batch Optimize SQL to optimize. This module will find all SQL inside your script and try optimize those classified as problematic.

Thanks,

Alex