Validation seems to run against all SQL collected for workload, not just the ones selected (the rest were marked "ignore by user")

SQL Optimizer SQL Optimizer for Oracle
1

Perhaps I’m missing something here, and couldn’t find another case of this in the forum.

I initially populated the SQL Workload area from the SGA, and it identified 4000+ queries. It took nearly a day to validate all that SQL, and then when I ran the index tuning, it hung at 11% when preparing index data.

I assumed my workload was too big - so I then marked all 4000+ queries as “ignored by user”, and then selected the top 20 queries from an aggregate elapsed time perspective. I kicked off the index search process again, and it seems to be going through all 4000+ queries to validate them. I would have expected this to be limited to just those 20 I had selected for “in use”.

Is this expected behavior? I can’t delete any of the queries in the collection, can only mark as in use or ignored …

2

Hi,

I have tried doing the same by collecting from SGA and then ignored most of my SQL. However, I was not able to see the same problem you described. Instead, I have only those SQL “In Used” evaluated as expected.

To help us identify the problem, could you please do the following and send back the log:

  1. Open your session in Optimize Indexes and then click “Start” to begin searching for indexes

  2. Launch “SQL Tracker” that comes with Toad.

  3. In SQL Tracker, you should see a process named “quest_sql_optimizer_oracle_optimize_indexes.exe”

  4. Select this process and then click the “green triangle” button to start monitoring

  5. Wait for 1 minute, then click the “red square” button to stop monitoring

  6. Save the log and send to me

This information would help us verify if SQL Optimize was really evaluating indexes for all your SQL.

Thanks,

Alex