Re: Shared Pool

From original post >>
I can't change the application, but I'm trying combinations of indexes, clustering the tables and other DBA things to try to get the application to run quicker. The explain plan tells me that the optimizer is giving me a very non-optimal result. Without that command my SQL statement would be executed the same way regardless of what I had done with the objects. <<

1. Compare the dba_table.num_rows to count. If they are not very close try updating the statistics. Do not use the default estimate option of the analyze statement if the table is large, instead try adding the 'estimate statistics sample nnnnn rows' to the analyze as this greatly improves the results. This syntax is acceptable with 7.1.3, but does not actually work until a higher release like 7.1.6+
2. If the stats are OK but Oracle insists on choosing a less than optimal access path, you might be able to code a view with 'hints' to force choosing a better path. Then you can have the ODBC application access the view.

Good luck.
Mark Powell -- Oracle 7 Certified DBA
- The only advice that counts is the advice that you follow so follow your own advice - Received on Tue Feb 17 1998 - 00:00:00 CST