When we use the Auto Optimizer / Advance SQL Optimizer to optimize a large query with many table join, does TOAD for Oracle review the query to see the following:
TOAD for Oracle version: 16.1.5
Table and or Partition have good statistics or missing stats. if missing stats, suggest gathering stats.
Does it check for Table / Partition or index statistic, are they stale? if stale, suggest gathering stats.
will it look for plans in AWR to see the same SQL ran faster some days ago with certain SQL_ID, etc.?
Many times, it is not possible to change production code without getting many approval, so the about would come in handy, i.e. gather new stats or set SQL baseline
I know you are using Toad, but this is really more of a question for the SQL Optimizer team. This feature calls their product. I'll move your forum post there and hopefully someone over there can answer it.
The Quest SQL Optimizer (SQL re-write engine) was designed to concentrate on finding alternative plans by altering the structure of the SQL to be tuned. Each alternative SQL structure the product finds is guaranteed to be (semantically) equivalent to the original SQL. This part of the product can find access plans that even Oracle does not see, and it does this by converting the (readable) syntax of the SQL to its relational notation and then applying algebraic operations to form equivalent statements that might yield alternative (hopefully better-performing) plans than Oracle's best choice.
The Quest methodology does not rely on statistics, whether good, bad or missing on the object/partition/index. It does not look automatically in AWR output to check alternative plans, although the product does have the ability to scan AWR and other output files in order to identify the existence of SQL.
Most of the customers I speak to rely on their monitoring systems to be alerted to stale or missing stats, so that DBAs can react accordingly.
Lastly, Quest has long been aware that many application vendors do not allow their customers to change code, even for the simple task of replacing a badly-performing SQL for a good one that you/we find. In these cases, you will be happy to know that the Quest SQL Optimizer does allow you to define plan baselines on the database side for your best performing alternative SQL. That is, instead of replacing the bad SQL for the good alternative in the vendor code, you can force the database to use the better access plan for the exact same query.
Gary,
Thank you for your prompt response. We do gather stats in our 250 TB database with various applications/schemas. some times, it require for us to gather stats again when and if the tables/partitions/indexes have stale stats.
I have been using TOAD for 20+ years and this is my suggestions that TOAD should check the following when customer wants to use advance SQL Optimizer for a SQL.
Table and or Partition have good statistics or missing stats. if missing stats, suggest gathering stats.
Do any Table / Partition or index statistic have stale stats? if stale, suggest gathering stats.