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>
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