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.
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.