- 45 Views
- Uploaded on

Download Presentation
## PowerPoint Slideshow about 'Overview Statistics in CMS PRD / Non-PRD Environments' - sean-francis

**An Image/Link below is provided (as is) to download presentation**

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript

What Do Statistics Do?

To execute any SQL statement, Oracle optimizer has to derive an 'execution plan'. The execution plan of a query is a description of how Oracle will implement the retrieval of data to satisfy a given SQL statement.

- Cost Base Optimizer (CBO)
- Utilize statistics
- Oracle and PeopleSoft Recommended CBO
- CMS implemented CBO in PRD/RPT and non-PRD environments

- Rule Base Optimizer (RBO)
- Ignore statistics
- Oracle de-support RBO on release 10g

Where are statistics stored?

- Three levels of statistics: table, index, column
- Statistics stored into Oracle data dictionary in tables own by SYS account
- Views created on these tables to retrieve data more easily
- Views prefixed with DBA_ or ALL_ or USER_

Where are statistics stored? Table Level Statistics

Table level statistics can be retrieved from:

Columns to look at are:

- DBA_ALL_TABLES
- DBA_OBJECT_TABLES
- DBA_TABLES
- DBA_TAB_PARTITIONS
- DBA_TAB_SUBPARTITIONS

- NUM_ROWS
- BLOCKS
- EMPTY_BLOCKS*
- AVG_SPACE*
- CHAIN_CNT*
- AVG_ROW_LEN
- AVG_SPACE_FREELIST_BLOCKS
- NUM_FREELIST_BLOCKS
- SAMPLE_SIZE
- LAST-ANALYZED
- GLOBAL_STATS
- USER_STATS

* Statistics gathered by ANALYZE statement.

Where are statistics stored? Index Level Statistics

Index level statistics can be retrieved from:

Columns to look at are:

- DBA_INDEXES
- DBA_IND_PARTITIONS
- DBA_IND_SUBPARTITIONS

- BLEVEL
- LEAF_BLOCKS
- DISTINCT_KEYS
- AVG_LEAF_BLOCKS_PER_KEY
- AVG_DATA_BLOCKS_PER_KEY
- CLUSTERING_FACTOR
- SAMPLE_SIZE
- LAST_ANALYZED
- GLOBAL_STATS
- USER_STATS
- PCT_DIRECT_ACCESS

Where are statistics stored? Column Level Statistics

Column level statistics can be retrieved from:

Columns to look at are:

- DBA_TAB_COLUMNS
- DBA_TAB_COL_STATISTICS
- DBA_PART_COL_STATISTICS
- DBA_SUBPART_COL_STATISTICS

- NUM_DISTINCT
- LOW_VALUE
- HIGH_VALUE
- DENSITY
- NUM_NULLS
- AVG_COL_LEN
- NUM_BUCKETS
- SAMPLE_SIZE
- LAST_ANALYZED
- GLOBAL_STATS
- USER_STATS

Note: The last three views extract statistics from DBA_TAB_COLUMNS.

How to gather Statistics?

Statistics gathering methods:

- ANALYZE statement
- DBMS_UTILITY.ANALYZE_SCHEMA procedure
- DBMS_STATS package

How to gather Statistics? ANALYZE statement

- Gathers statistics on table/indexes and columns histograms
- Collect non-optimizer statistics:
- Validate the structure of an index partition, table/table partition, index-organized table, cluster, or object reference
- Identify migrated and chained rows of a table or cluster
- Collect information on freelist blocks

How to gather Statistics? ANALYZE statement

- Estimate statistics:
- ANALYZE TABLE tablename ESTIMATE STATISTICS SAMPLE 30 PERCENT;
- Compute statistics:
- ANALYZE TABLE tablename COMPUTE STATISTICS;

Note:

- The above ANALYZE statements will gather statistics for table, indexes and histograms on ALL columns for the table and it will cause performance issues with CMS automated gather stats (StatRanger)
- Oracle document does not recommend using the ANALYZE statement to gather optimizer statistics in 8i/9i

How to gather Statistics? DBMS_UTILITY.ANALYZE_SCHEMA

- Run ANALYZE statement on each table in the specified schema
- Gathers statistics on table, all indexes, histogram on ALL columns (default bucket size 75)

Note:

- The DBMS_UTILITY.ANALYZE_SCHEMA procedure will cause performance issues with CMS automated gather stats process
- Oracle document does not recommend using the DBMS_UTILITY.ANALYZE_SCHEMA procedure to gather optimizer statistics in 8i/9i

How to gather Statistics? DBMS_STATS package

- Generate and manage statistics only for cost-based optimization
- Collect, delete, modify, view, export, import statistics and gather stale tables
- Gather only statistics for database objects that have impact on the optimizer
- Set or get statistics
- Transfer statistics
- Gather statistics in parallel

Note:

- CMS uses DBMS_STATS package to gather optimizer statistics on PRD/RPT, non-PRD instances

How to gather Statistics?

Note:

- Oracle no longer enhance the ANALYZE statement and DBMS_UTILITY.ANALYZE_SCHEMA procedure
- Oracle recommended DBMS_STATS package to collect optimizer statistics on 8i/9i instances
- The cost-based optimizer, which depends upon statistics, will eventually use only statistics that have been collected by DBMS_STATS

Checking for Stale Statistics

- SYS.MON_MODS$ table keeps track of modifications to tables with MONITORING column=YES
- MONITORING column tracks the number of INSERT, UPDATE, and DELETE operations for the tables since the last time statistics were gathered
- DBA_TAB_MODIFICATIONS, ALL_TAB_MODIFICATIONS, or USER_TAB_MODIFICATIONS views identify tables with stale statistics

Note:

- CMS uses DBMS_STATS package and MONITORING to gather stale tables every 4 hours via Oracle job

Checking for Stale Statistics

Sample SQL script, which list tables with stale statistics:

Set pagesize 100

Set linesize 120

COL NUM_ROWS FORMAT 99,999,999 HEADING 'Num Row'

COL table_name FORMAT a20 HEADING 'Table Name'

COL STALE FORMAT 999.999 HEADING 'Stale %'

SELECT b.table_name, TO_CHAR(b.LAST_ANALYZED, 'DD-MON-YY HH24:MI') Last_analyzed, ((a.INSERTS + a.UPDATES + a.DELETES)/(b.NUM_ROWS))*100 STALE, a.INSERTS, a.UPDATES, a.DELETES, b.NUM_ROWS FROM sys.dba_tab_modifications a, dba_tables b where a.table_name= b.table_name;

How CMS Gather Statistics

- UNIVIEW application
- Centralize the management of the utilities including the CBO statistics gathering (StatRanger)
- Repository for CBO statistics gathering events by CBO_STATS package
- Use Oracle application form server - RCAT9P instance

- StatRanger
- CMS central can define the CBO methods, global CBO exceptions, CBO exceptions for tables in each instance
- CMS central can manually force refresh statistics, reload old statistics, transfer statistics for tables and view the audit of the statistics gathering events
- Audit all changes to CBO exception rules

How CMS Gather Statistics

CBO Statistics Gathering Exceptions menu option:

How CMS Gather Statistics

CBO Statistics Gathering Exceptions:

How CMS Gather Statistics

CBO Statistics Gathering Exceptions:

How CMS Gather Statistics

CBO Statistics Gathering Exceptions:

How CMS Gather Statistics

CBO Audit Events menu option:

How CMS Gather Statistics

CBO Statistic Gathering Events menu option:

How CMS Gather Statistics

Note:

- CMS automated the statistics gathering process by executing the CBO_STATS.GATHER_STATS procedure every 4 hours via Oracle job on each instance
- The default CBO method option for gathering statistics in CMS environments is “FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS SIZE 75”

How CMS Gather Statistics

CBO_STATS.GATHER_STATS procedure:

- Gather statistics on tables which have stale statistics >= 10%
- Gather statistics on tables/indexes which have no statistics
- Turn on MONITORING for all SYSADM tables which have MONITORING=NO
- Drop statistics for tables which have CBO method option as NONE
- Audit the CBO events in RCAT9P

Note:

If a table name does not exist in the global or local CBO exception lists, the default statistics gathering method is “FOR ALL INDEXED COLUMNS SIZE 75”

Why We Do It?

CMS proactively gathers statistics for any SYSADM table/index, which have no statistics or stale statistics to improve PeopleSoft processes/queries performance.

Download Presentation

Connecting to Server..