Statistics in 11g

dbms_stats.get_prefs:  It will get you the default values of various preferences.

select dbms_stats.get_prefs('stale_percent','APP_OWNER','MY_CUSTOMER')  stale_percent from dual;

PL/SQL procedure successfully completed.

set_global_pref:  Let you set global statistics preferences for various parameters.  You need the sysdba to use this procedure.

exec dbms_stats.set_global_prefs('estimate_percent','10');

PL/SQL procedure successfully completed.

delete_table_prefs: Delete statistics preferences of the specified table

SQL> exec dbms_stats.delete_table_prefs('APP_OWNER','TR_CUSTOMER','DEGREE');

PL/SQL procedure successfully completed.

delete_schema_prefs:  Delete statistics preferences for a specific schema

SQL> exec dbms_stats.delete_schema_prefs('JOHN_DEV,'CASCADE');

PL/SQL procedure successfully completed.

delete_database_prefs:  Delete statistics preferences for all tables in the database

Export / Import Statistics Prefrence:

·         export_table_prefs / import_table_prefs

·         export_schema_prefs / import_schema_prefs

·         exprt_database_prefs / import_database_prefs

Pending  (Private Statistics) v.s Publishing Statistics Immediately

In prior database version, statistics was always ready for use by the cost optimizer.  The pushing of the statistics was automatic.  In 11g, you can now not to publish the statistics, hence giving you tan opportunity to test the new statistics for actual improve the SQL performance. 

SQL> select dbms_stats.get_prefs('publish') from dual;

DBMS_STATS.GET_PREFS('PUBLISH')

----------------------------------------------------

TRUE

Pending statistics is also called Private Statistics.  It’s not published unless you pushing it by running the publish_pending_stats procedure in the DBMS_STATS package.  If the query performance is not gained, you can delete the pending statistics by running the delete_pending_stats procedure.

Example:

SQL> exec dbms_stats.set_table_prefs('APP_OWNER','PRODUCTS','publish','false');

PL/SQL procedure successfully completed.

select dbms_stats.get_prefs('publish','APP_OWNER','PRODUCTS') from dual;

DBMS_STATS.GET_PREFS('PUBLISH','APP_OWNER','PRODUCTS')

------------------------------------------------------------------------------------------

FALSE

 

Gather Private Statistics

SQL> exec dbms_stats.delete_table_stats('APP_OWNER','PRODUCTS');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('APP_OWNER','PRODUCTS');

PL/SQL procedure successfully completed.

select table_name, last_analyzed, num_rows

from dba_tables

where table_name = 'PRODUCTS';

TABLE_NAME                     LAST_ANALYZED         NUM_ROWS

------------------------------ ----------------- ------------

PRODUCTS  ========================è not publish yet..

select table_name, last_analyzed, num_rows

from dba_tab_pending_stats

 where table_name = 'PRODUCTS';

TABLE_NAME                     LAST_ANALYZED         NUM_ROWS

------------------------------ ----------------- ------------

PRODUCTS                       20110130 17:17:35        49780

The dba_tab_pending_stats / user_tab_pending_stats show pending statistics for tables, partitions, and subpatitions. 

Publish Stats

SQL> exec dbms_stats.publish_pending_stats('APP_OWNER', 'PRODUCTS');

PL/SQL procedure successfully completed.

SQL> select table_name, last_analyzed, num_rows  from dba_tables where table_name = 'PRODUCTS';

TABLE_NAME                     LAST_ANALYZED         NUM_ROWS

------------------------------ ----------------- ------------

PRODUCTS                       20110130 17:17:35        49780

1 row selected.

SQL> select table_name, last_analyzed, num_rows from dba_tab_pending_stats where table_name = 'PRODUCTS';

no rows selected

Delete Pending Stats:  if the query doesn’t gain performance, you can always delete the pending stats

SQL> exec dbms_stats.delete_pending_stats('APP_OWNER','PRODUCTS');

PL/SQL procedure successfully completed.

Pulish Pending Stats for a schema: 

SQL> exec dbms_stats.publish_pending_stats('ETL_APP', null);

Publish all pending statistics in the database: 

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.publish_pending_stats(null,null);

Extended Statistics - Multicolumn Statistics

In Oracle 11g introduces extended statistics (also called multicolumn statistics) wherein you can collect statistics on a set of columns for the queries involving predicates.  This will give the optimizer more correct estimate of the selectivity for the group of columns as a whole. 

Creating Multicolumn statistics:

SQL> select dbms_stats.create_extended_stats(USER,’CUST_STAGE’,'(CUST_ID,ALERT_ID)') from dual;

DBMS_STATS.CREATE_EXTENDED_STATS(USER,’CUST_STAGE’,'(CUST_ID,ALERT_ID)')

-----------------------------------------------------------------------------------------------------------------------------------------------------------

SYS_STUSTUBNP0BHDV2DL3CX16_KX5

1 row selected.

Gathering Statistics:

SQL> exec dbms_stats.gather_table_stats(ownname=> USER, tabname=>‘CUST_STAGE’,estimate_percent=>100, cascade=>true);

PL/SQL procedure successfully completed.

SQL> select table_name, column_name, num_distinct, num_buckets, sample_size, histogram

  2  from user_tab_col_statistics

  3  where table_name = ‘CUST_STAGE’;

TABLE_NAME                     COLUMN_NAME                    NUM_DISTINCT  NUM_BUCKETS  SAMPLE_SIZE HISTOGRAM

------------------------------ ------------------------------ ------------ ------------ ------------ ---------------

CUST_STAGE               CUST_ID                           18264            1        25879 NONE

CUST_STAGE               ALERT_ID                             1            1        25879 NONE

CUST_STAGE               ALERT_DTS                                 1            1        25879 NONE

CUST_STAGE               AUDIT_REC_CREATE_DTS                      1            1        25879 NONE

select extension_name, extension, table_name

 from dba_stat_extensions

 where owner = SOWN

and table_name = ‘CUST_STAGE’

EXTENSION_NAME                   EXTENSION                        TABLE_NAME

-------------------------------- -------------------------------- ------------------------------

SYS_STUSTUBNP0BHDV2DL3CX16_KX5   ("CUST_ID","ALERT_ID")  CUST_STAGE

show_extended_stats function returns the name of the column group as a virtual column.

SQL> select sys.dbms_stats.show_extended_stats_name(SOWN,’CUST_STAGE’,'(CUST_ID,ALERT_ID)') col_group_name from dual;

COL_GROUP_NAME

------------------------------------------------------------------------------------------------------------------------------------

SYS_STUSTUBNP0BHDV2DL3CX16_KX5

Deleting Extended Statistics

SQL> exec dbms_stats.drop_extended_stats(SOWN,’CUST_STAGE’,'(CUST_ID,ALERT_ID)');

PL/SQL procedure successfully completed.

SQL Query Cache Result

A dedicated memory buffer stored in the shared pool can be used for storing the retrieving the cached result.  It's cached the result of a query or query block for future reuse by retrieves the data from the database and cache the result in the SQL query cache result.  If a next session using the same query, it is veyr fast because it retrieves the result directly from the cache instead of the disks.  In a RAC environment, each instance has a private result cache and cannot be used by another instance.  However, invalidations work across instance and to handle synchronization between RAC instances, a special process RCGB is use.

• RESULT_CACHE_MODE

       MANUAL:  use the RESULT_CACHE hint to specify results to be stored
       in the cache.
       FORCE:  All results are stored in the cache

• RESULT_CACHE_MAX_SIZE: depends on other memory settings.  It's 0.25% of MEMORY_TARGET or 0.5% SGA_TARGET or 1% of SHARED_POOL_SIZE
• RESULT_CACHE_MAX_RESULT:  Sets max of cache memory for a single result.  5% is the defaults
• RESULT_CACHE_REMOTE_EXPIRATION:  Sets the expired time (default 0)

NAME                                         TYPE        VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_lag              big integer   3000
client_result_cache_size             big integer   0
result_cache_max_result             integer         5
result_cache_max_size                big integer   2M
result_cache_mode                      string           FORCE
result_cache_remote_expiration  integer     0

Select /*+ RESULT_CACHE */ col1, col2
From T1

If you don't want to store the result of a query in a result cache, you must use NO_RESULT_CACHE hint in your query.  For example..

Select /*+ NO_RESULT_CACHE */ col1, col2
FROM T1

Query Result Cache Views:
V$RESULT_CACHE_STATISTICS:  cache settings and memory usage statistics
V$RESULT_CACHE_MEMORY:  memory blocks and statistics
V$RESULT_CACHE_OBJECTS:  objects cache results
V$RESULT_CACHE_DEPENDENCY:  cache results and dependencies

Adaptive Cursor Sharing

Oracle 11g introduces Adaptive Cursor Sharing.  This feature is designed to not sharing the cursor blindly but generate multiple plans per SQL statement with bind variables if the benefit of multiple execution plans outweights the parse and memory usage overhead.  Adaptive Cursor Sharing benefits are:

• Automatically detects when different executions would benefit from different execution plans
• Limits the number of generated hild cursors to a minimum

Adaptive Cursor Sharing Views

• V$SQL:  2 new columns have been added.  IS_BIND_SENSITIVE:  indicates if a cursor is bind-sensitive.  IS_BIND_AWARE:  indicates if a cusor is bind-aware
• V$SQL_C_HISTOGRAM:  shows the distribution of the execution count across the execution history histogram
• V$SQL_CS_SELECTIVITY:  shows the selectivity cubes stored for every predicate containing a bind variable and whose selectivity is use in the cursor sharing checks
• V$_SQL_CS_STATISTICS:  Statistics of a cursor use different bind sets

Invisible Index

Invisible index is an index can be ignored by the optimizer unless you set the OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE at the session or system level.  The default value is FALSE.  Invisible index is maintained during DML statements.

Create an index as invisible

SQL > create index APP_OWNER.TRANX (IDN) invisible;


Index is alter as not visible to optimizer:

SQL> alter index APP_OWNER.TR_INDX9 invisible;
Index altered.


The optimizer will always consider the index:

SQL> alter index APP_OWNER.TR_IDX9 visible;

DB Smart Flash Cache

The DB Smart Flash Cache is an extersion of the buffer cache that resides on a flash disk.  A flash disk is a solid state device made of flash memory like MP3 devices.  The flash devices is very fast because disk seek time is eliminated.  It has large capaciy and cheaper compared to DRAM.  Using the smart flash cache is mainly for read-intensive OLTP workloads.  The main benefits are:

• Better performance for the same price:  When the buffer cache memory is maxed out and I/O starts thrashing, you need to buy more memory and memory is not cheap and the system might has not enough DRAM slots.  You can use the flash cache future and configure with the main memory buffer cache to provide a much larget combine buffer cache that you can cache the whole working set of the database into flash cache to improve the response time and overall throughput.
• Easy to setup:  Two initialization parameters to be set

          DB_FLASH_CACHE_FILE:  specifies the OS file/disk path and the
          name for the flash cache.

          DB_FLASH_CACHE_SIZE:  is the name of the flash cache
          All Standard block size buffers use the same flash cache.  Nonstandard
          block size is not supported.  DB smart flash cache is not auto-tuned

• Fine-tune object-level granularity control of the LRU machanism

11g Memory Parameters

MEMORY_TARGET triggers automatic memory management.  Here are the rules:

• If SGA_TARGET and PGA_AGGREGATE_TARGET are set, they will be considred to be minimum values for the sizes of SGA and PGA.  
• SGA_TARGET is set but PGA_AGGREGATE_TARGET is not set, it is equal to MEMORY_TARGET - SGA_TARGET
• If PGA_AGGREGATE_TARGET is set and SGA_TARGET is not set, SGA_TARGET is initialized to MEMORY_TARGET - PGA_TARGET
• If neither is set, 60% is ditributed to SGA and 40% is distributed to PGA at startup.
• If MEMORY_TARGET is not set, they're not auto-tuned.
• If MEMORY_MAX_TARGET is set and MEMORY_TARGET   = 0, auto-tune behavior defaults to 10gR2 behavior.

The following views provide memory information:

• V$MEMORY_DYNAMIC_COMPONENTS:  Shows the current size of all dynamically tuned memory components.
• V$MEMORY_RESIZE_OPS:  Shows information about the last 800 completed memory resize operations.
• V$MEMORY_CURRENT_RESIZE_OPS:  Shows the current/in progress resize operations both manual and dynamic.
• V$SGA_DYNAMIC_COMPONENTS:  Summarizes all dynamic SGA resize operation since startup
• V$SGA_DYNAMIC_FREE_MEMORY:  Shows the amount of memory is available for futhre dynamic SGA resize operation.

Using DBMS_ADDM Package

The dbms_addm package eases the addm management.  It consists of the following procedures and functions:

• ANALYZE_DB:  creates an addm task for analyzing the database globally
• ANALYZE_INST:  Create an addm task for analyzing a local instance
• ANALYZE_PARTIAL:  Create an ADDM task for analyzing a subset of instances
• DELETE:  Deletes a created ADDM task( of any kind)
• GET_REPORT:  Get the default text report of an executed ADDM task
• Parameters 1000,1001:  start and end snapshot

  declare

  tname varchar2(30) :='My Task';

  begin

  dbms_addm.analyze_db(tname, 3000,3001);

  dbms_output.put_line(tname);

  end;

SQL> /

My Task

PL/SQL procedure successfully completed.

SQL> select execution_name, finding_name, parent, impact

  2  from dba_addm_findings

  3  where task_name = 'My Task';

EXECUTION_NAME  FINDING_NAME                   PARENT       IMPACT

--------------- ------------------------------ ------ ------------

EXEC_8808       normal, successful completion       0            0

SQL> select execution_name, finding_name,message

  2  from dba_addm_findings

  3  where task_name = 'My Task';

EXECUTION_NAME  FINDING_NAME                   MESSAGE

--------------- ------------------------------ ------------------------------------------------------------

EXEC_8808       normal, successful completion  There was no significant database activity to run the ADDM.

select dbms_addm.get_report('My Task') from dual;

select * from dba_advisor_finding_names

begin

dbms_addm.insert_finding_directive (NULL,

'MY undersized SGA directive 2',

'Undersized SGA',

2,

10);

:tname :='my instance ADDM task';

dbms_addm.analyze_inst(:tname, 3000,3002);

END;

/

SQL> SELECT task_name, directive_name, description

  2  FROM dba_addm_task_directives;

TASK_NAME                      DIRECTIVE_NAME

------------------------------ ------------------------------

DESCRIPTION

-------------------------------------------------------------------------------------------------------------------------

my instance ADDM task          Finding Filter

Filter findings of type "Undersized SGA" if the impact is less than 2 active sessions or the impact is less than 10 percent of database time for the analysis period.

SQL> exec dbms_addm.delete('my instance ADDM task');

PL/SQL procedure successfully completed.

select task_name, message from dba_advisor_actions

ADVISOR VIEWS

DBA_ADVISOR_FINDINGS         USER_ADVISOR_FINDINGS
DBA_ADVISOR_RECOMENDATIONS   USER_ADVISOR_RECOMMENDATIONS
DBA_ADVISOR_ACTIONS          USER_ADVISOR_ACTIONS

Health Monitor

Health monitor runs diagnositic checks on various components of the database including files, memory, transaction interity, datameta and process uage.  The health checks have 2 categories:  1)  DB-Online and DB-offline.  These checkers generate reports of their findings and recommendations for fixing the problems. 

·         Reactive way:  Health monitor checks automatically in response to critical errors

·         Manual way:  DBA can run it manually via DBMS_HM PL/SQL or via Enterprise Manager interface

You can views via V$HM_RUN, DBMS_HM, ADRCI, or Enterprise Manager

  1  select name, offline_capable, description, internal_check

  2* from v$hm_check

NAME                             O DESCRIPTION                      I

-------------------------------- - -------------------------------- -

HM Test Check                    Y Check for health monitor functio Y

                                   nality

DB Structure Integrity Check     Y Checks integrity of all database N

                                    files

CF Block Integrity Check         Y Checks integrity of a control fi N

                                   le block

Data Block Integrity Check       Y Checks integrity of a data file  N

                                   block

Redo Integrity Check             Y Checks integrity of redo log con N

                                   tent

Logical Block Check              N Checks logical content of a bloc Y

                                   k

Transaction Integrity Check      N Checks a transaction for corrupt N

                                   ions

Undo Segment Integrity Check     N Checks integrity of an undo segm N

                                   ent

No Mount CF Check                Y Checks control file in NOMOUNT m Y

                                   ode

Mount CF Check                   Y Checks control file in mount mod Y

                                   e

CF Member Check                  Y Checks a multiplexed copy of the Y

                                    control file

All Datafiles Check              Y Checks all datafiles in the data Y

                                   base

Single Datafile Check            Y Checks a data file               Y

Tablespace Check Check           Y Checks a tablespace              Y

Log Group Check                  Y Checks all members of a log grou Y

                                   p

Log Group Member Check           Y Checks a particular member of a  Y

                                   log group

Archived Log Check               Y Checks an archived log           Y

Redo Revalidation Check          Y Checks redo log content          Y

IO Revalidation Check            Y Checks file accessibility        Y

Block IO Revalidation Check      Y Checks file accessibility        Y

Txn Revalidation Check           N Revalidate corrupted transaction Y

Failure Simulation Check         Y Creates dummy failures           Y

Dictionary Integrity Check       N Checks dictionary integrity      N

ASM Mount Check                  Y Diagnose mount failure           Y

ASM Allocation Check             Y Diagnose allocation failure      N

ASM Disk Visibility Check        Y Diagnose add disk failure        Y

ASM File Busy Check              Y Diagnose file drop failure       Y

27 rows selected.

SQL> exec dbms_hm.run_check('Dictionary Integrity Check','DicoCheck',0,'TABLE_NAME=tab$');

PL/SQL procedure successfully completed.

SQL> set long 100000

SQL> select dbms_hm.get_run_report('DicoCheck') from dual;

DBMS_HM.GET_RUN_REPORT('DICOCHECK')

--------------------------------------------------------------------------------

Basic Run Information

 Run Name                     : DicoCheck

 Run Id                       : 981

 Check Name                   : Dictionary Integrity Check

 Mode                         : MANUAL

 Status                       : COMPLETED

 Start Time                   : 2011-01-18 22:14:16.580684 -06:00

 End Time                     : 2011-01-18 22:14:16.685162 -06:00

 Error Encountered            : 0

 Source Incident Id           : 0

 Number of Incidents Created  : 0

Input Paramters for the Run

 TABLE_NAME=tab$

 CHECK_MASK=ALL

Run Findings And Recommendations

SQL> select name from v$hm_check where internal_check='N';

NAME

--------------------------------

DB Structure Integrity Check

CF Block Integrity Check

Data Block Integrity Check

Redo Integrity Check

Transaction Integrity Check

Undo Segment Integrity Check

Dictionary Integrity Check

ASM Allocation Check

8 rows selected.

SQL> exec dbms_hm.run_check('Redo Integrity Check','my_run2');

PL/SQL procedure successfully completed.

SQL> SELECT c.name check_name, p.name parameter_name, p.type,

  2  p.default_value, p.description

  3  FROM v$hm_check_param p, v$hm_check c

  4  WHERE p.check_id = c.id and c.internal_check = 'N'

  5  ORDER BY c.name;

CHECK_NAME                     PARAMETER_NAME  TYPE            DEFAULT_VALUE   DESCRIPTION

------------------------------ --------------- --------------- --------------- ----------------------------------------

ASM Allocation Check           ASM_DISK_GRP_NA DBKH_PARAM_TEXT                 ASM group name

                               ME

CF Block Integrity Check       CF_BL_NUM       DBKH_PARAM_UB4                  Control file block number

Data Block Integrity Check     BLC_DF_NUM      DBKH_PARAM_UB4                  File number

Data Block Integrity Check     BLC_BL_NUM      DBKH_PARAM_UB4                  Block number

Dictionary Integrity Check     CHECK_MASK      DBKH_PARAM_TEXT ALL             Check mask

Dictionary Integrity Check     TABLE_NAME      DBKH_PARAM_TEXT ALL_CORE_TABLES Table name

Redo Integrity Check           SCN_TEXT        DBKH_PARAM_TEXT 0               SCN of the latest good redo (if known)

Transaction Integrity Check    TXN_ID          DBKH_PARAM_TEXT                 Transaction ID

Undo Segment Integrity Check   USN_NUMBER      DBKH_PARAM_TEXT                 Undo segment number