Oracle Point, Oracle Life.

Another good case study about wrong action of CBO on Oracle database.

It can be read along with my posting at http://oraclepoint.com/oralife/?p=80 (It’s also available at http://www.oraclepoint.com/topic.php?filename=134&extra=page%3D1)

Comparing to my posting above, the case study is established readily upon oracle 10g instrumental tools, such as ADDM and AWR. But, whatever tools are used to diagnose and analyze this kind of performance problem, the essential internals are never changed. We all need to rely on the followings.

• statspack report
• histograms on column of tables
• value of column “PLAN_HASH_VALUE” in table dba_hist_sqlstat (refer to same column in view v$sql at my posting http://oraclepoint.com/oralife/?p=121 )

For this kind of performance problem, the final solution seems always be:

“By removing the histogram on this column and reparsing the SQL, an immediate database-wide improvement and lasting stability was realized. ”

Yes, that’s right and go to find what columns should be done statistics in my article listed above.

To read this complete article of V.J. Jain, go find here.

Popularity: 4% [?]