Verify CBO stats and Database initalization parameters for 11i

In order to get better performace of the database , we usually schedule Apps Gather schema . Oracle provide very important tool/script to verify the CBO stats .

bde_last_analyzed.sql :-verifies the CBO statistics in the data dictionary for all tables, indexes, and partitions. It also validates the statistics on tables and indexes owned by ‘SYS’. This should be first step to check overall performance of the database . Download this script from metalink , follow metalink note :-163208.1

Run this script

 $ sqlplus “/as sysdba”

SQL*Plus: Release 10.2.0.3.0 – Production on Fri Nov 21 12:39:34 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> @bde_last_analyzed.sql
Generating BDE_TABLES staging table…
Generating BDE_INDEXES staging table…
Generating BDE_TAB_PARTITIONS staging table…
Generating BDE_IND_PARTITIONS staging table…
Generating BDE_TABLES spool file…
Generating BDE_INDEXES spool file…
Generating BDE_TAB_PARTITIONS spool file…
Generating BDE_IND_PARTITIONS spool file…
Generating BDE_SUMMARY spool file…

It gererates five reports :-

-rw-r–r–   1 oadev  dba       370641 Nov 21 12:44 bde_last_analyzed_ind_partitions.html
-rw-r–r–   1 oadev  dba      14734392 Nov 21 12:44 bde_last_analyzed_indexes.html
-rw-r–r–   1 oadev  dba       158011 Nov 21 12:44 bde_last_analyzed_summary.html
-rw-r–r–   1 oadev  dba       183966 Nov 21 12:44 bde_last_analyzed_tab_partitions.html
-rw-r–r–   1 oadev  dba      6333434 Nov 21 12:43 bde_last_analyzed_tables.html

Its  a good idea if you are  creating TAR with Oracle  for performance issue  , you can check your own and upload this output which will help.

BDE_CHK_CBO.sql :-   is another important script/tool which can be dwonloaded to verify the database initialization parameter settings for Oracle Applications Release 11i, Releases 11.5.1 through to 11.5.10. .Follow metalink note 174605.1  . It helps us to identify the parameter which is important and the parameters which is not required .

Run script

 $ sqlplus “/as sysdba”

SQL*Plus: Release 10.2.0.3.0 – Production on Fri Nov 21 12:15:22 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> @bde_chk_cbo.sql
Spool file bde_chk_cbo.html has been generated.
SQL>
 

Share This Post with Your Friends over Social Media!

About the Author Rajat

I am Rajat Dey and I have more than decade of Oracle experience from all major regions throughout the globe. I am well versed in most facets of Oracle, supporting database, fusion middleware and the eBusiness suite across many operating system platforms.Currently I am located in Sydney  Oracle Financials 11i and Release 12,12.2  Oracle RDBMS 7x,8i , 9i,10g and 11g  Amazon cloud .  Oracle RAC 10g and 11g  Oracle Applications Server  Oracle Web logic Server  OBIEE  Hyperion  SSO  Oracle Portal  PL/SQL  UNIX  SQL  MS-SQL 2008.  OEM 12c .  Data Guard .  Essbase .  Database backup and recovery.  Performance Tunning.  TimeSten

Leave a Comment: