Team,
I recently had one issue with one of my production database where we had an application upgrade completed. Once the upgrade completed , one of the select query stared performing very slow. I have tried debugging using the sqltrpt.sql , but this never gave me a proper fix for the issue, this tuning advisor was basically giving me suggestions on creating new indexes and rewrite the predicate.
I tried with these findings and it didn’t give me any improvement in the query in fact taking more time.

Later I see a difference in size for the schema after upgrade and I went with a table reorg. This improves the performance and my query ran in 30 sec from 48 minutes.

Does this SQL tuning advisor taking this defragmented tables in consideration and suggest for a reorg of the table?


1- Index Finding (see explain plans section below)
--------------------------------------------------

The execution plan of this statement can be improved by creating one or more
indices.

Recommendation (estimated benefit: 88.36%)
------------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index owner.indexname on
owner.table_name("col1","col2");

Rationale
---------
Creating the recommended indices significantly improves the execution plan
of this statement. However, it might be preferable to run "Access Advisor"
using a representative SQL workload as opposed to a single statement. This
will allow to get comprehensive index recommendations which takes into
account index maintenance overhead and additional space consumption.

2- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
Predicate "TBL_name"."Col"<>:B1 used at line ID 19
of the execution plan is an inequality condition on indexed column
"COl". This inequality condition prevents the optimizer from
selecting indices on table "owner".table_name

Recommendation
--------------
- Rewrite the predicate into an equivalent form to take advantage of
indices.

Rationale
---------
The optimizer is unable to use an index if the predicate is an inequality
condition or if there is an expression or an implicit data type conversion
on the indexed column.

Thanks
pksadoor