Gathering Optimizer Statistics Manually
This chapter explains how to use theDBMS_STATS.GATHER_*_STATSprogram units. Gathering Optimizer Statistics Manually As an alternative or supplement to automatic statistics gathering, you can use theDBMS_STATSpackage to gather optimizer statistics manually. About Manual Statistics Collection with DBMS_STATS Use theDBMS_STATSpackage to manipulate optimizer statistics. You can gather statistics on objects and columns at various levels of granularity: object, schema, and database.
A=> GATHER_TABLE_STATS Procedure
This procedure gathers table, column, and index statistics.
It attempts to parallelize as much work as possible, but there are some restrictions, which are described in the individual parameters.
Syntax
DBMS_STATS.GATHER_TABLE_STATS (
ownname VARCHAR2,
tabname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
estimate_percent NUMBER DEFAULT to_estimate_percent_type (get_param('ESTIMATE_PERCENT')),
block_sample BOOLEAN DEFAULT FALSE,
method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),
degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),
granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')),
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (get_param('NO_INVALIDATE')),
stattype VARCHAR2 DEFAULT 'DATA',
force BOOLEAN DEFAULT FALSE,
context DBMS_STATS.CCONTEXT DEFAULT NULL, -- non operative
options VARCHAR2 DEFAULT get_param('OPTIONS'));
DEMO
1=> Changing Estimate_percent (dbms_stat.auto_sample_size).
In below demonstration we are changing estimate_percent to 2 percent. It means there will be only 2 percent of data scanned in table.
As we can see from DBA_TAB_STATISTICS only 2 percent rows are sampled as per SAMPLE_SIZE columns.
A=> SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname => '&OWNER', tabname =>'&name', estimate_percent=>2 , cascade => TRUE);
Enter value for owner: ABHI_TEST
Enter value for name: SALES
PL/SQL procedure successfully completed.
SQL> select table_name,SAMPLE_SIZE,NUM_ROWS from dba_tab_statistics where OWNER='ABHI_TEST' AND TABLE_NAME='SALES';
TABLE_NAME SAMPLE_SIZE NUM_ROWS
----------- ---------- ------------------------------
SALES 74000 3700000
Now in below demonstration we are changing estimate_percent to DEFAULT. It means there will be FULL Rows scanned in table.
As we can see from DBA_TAB_STATISTICS ALL rows are sampled as per SAMPLE_SIZE columns.
B=> SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'ABHI_TEST', tabname =>'SALES', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE ,method_opt=> 'FOR ALL COLUMNS SIZE AUTO');
PL/SQL procedure successfully completed.
SQL> select table_name,SAMPLE_SIZE,NUM_ROWS from dba_tab_statistics where OWNER='ABHI_TEST' AND TABLE_NAME='SALES';
TABLE_NAME SAMPLE_SIZE NUM_ROWS
---------------------------------------------------------
SALES 3675073 3675073
NOTE ->
Oracle recommends that the ESTIMATE_PERCENT parameter use the default setting of DBMS_STATS.AUTO_SAMPLE_SIZE.
In this case, Oracle Database chooses the sample size automatically.
This setting enables the use of the following:
• A hash-based algorithm that is much faster than sampling
This algorithm reads all rows and produces statistics that are nearly as accurate as statistics from a 100% sample.
• Incremental statistics
• Concurrent statistics
• New histogram types
The DBA_TABLES.SAMPLE_SIZE column indicates the actual sample size used to gather statistics.
2=> Changing METHOD_OPT parameter values (FOR ALL COLUMNS SIZE AUTO)
this parameter used to created HISTOGRAMS and moreover specifically to collect stats for column level.
In below example we are changing FOR ALL COLUMNS SIZE AUTO to FOR ALL COLUMNS SIZE 2 ,i.e it will create 3 Buckets and store all columns in it
A=> SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname => '&OWNER', tabname =>'&name', estimate_percent=>2 ,method_opt=> 'FOR ALL COLUMNS SIZE 2');
Enter value for owner: ABHI_TEST
Enter value for name: SALES
PL/SQL procedure successfully completed.
SQL>
COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
-------------------- --------------- --------------
PROD_ID 0 13
PROD_ID 1 48
PROD_ID 2 148
CUST_ID 0 2
CUST_ID 1 4897
CUST_ID 2 100995
TIME_ID 0 2450815
TIME_ID 1 2451590
TIME_ID 2 2452275
CHANNEL_ID 0 2
CHANNEL_ID 1 3
CHANNEL_ID 2 9
PROMO_ID 0 33
PROMO_ID 2 999
QUANTITY_SOLD 73659 1
AMOUNT_SOLD 0 6.4
AMOUNT_SOLD 1 33.5
AMOUNT_SOLD 2 1782.72
18 rows selected.
As we can see total buckets for column's values is 0 to 2 means total 3 buckets are theres as per definition from method_opt in above examples.
B=> In below example we are changing FOR ALL COLUMNS SIZE AUTO to FOR ALL COLUMNS SIZE 5 ,i.e it will create 6 Buckets and store all columns in it
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'ABHI_TEST', tabname =>'SALES', estimate_percent=>2 ,method_opt=> 'FOR ALL COLUMNS SIZE 5');
PL/SQL procedure successfully completed.
SQL> select column_name, endpoint_number, endpoint_value
from user_tab_histograms
where table_name = 'SALES'; 2 3
COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
-------------------- --------------- --------------
PROD_ID 0 13
PROD_ID 1 30
PROD_ID 2 40
PROD_ID 3 117
PROD_ID 4 130
PROD_ID 5 148
CUST_ID 0 2
CUST_ID 1 1954
CUST_ID 2 3705
CUST_ID 3 6397
CUST_ID 4 10188
CUST_ID 5 100996
C => Now let we DELETE STATISTICS and regather statistics as using SKEWONLY values.
SKEWONLY -> Oracle will gather stats only for most skewed columns in table as seen below,
SQL> exec DBMS_STATS.DELETE_TABLE_STATS(ownname => 'ABHI_TEST', tabname =>'SALES');
PL/SQL procedure successfully completed.
SQL> COMMIT;
Commit complete.
SQL> SELECT TABLE_NAME,COLUMN_NAME,NUM_DISTINCT,HISTOGRAM FROM USER_TAB_COL_STATISTICS WHERE TABLE_NAME='SALES';
no rows selected
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'ABHI_TEST', tabname =>'SALES', estimate_percent=>2 ,method_opt=> 'FOR ALL COLUMNS SIZE SKEWONLY');
PL/SQL procedure successfully completed.
SQL> SELECT TABLE_NAME,COLUMN_NAME,NUM_DISTINCT,HISTOGRAM FROM USER_tAB_COL_STATISTICS WHERE TABLE_NAME='SALES';
TABLE_NAME COLUMN_NAME NUM_DISTINCT HISTOGRAM
-----------------------------------------------------------
SALES PROD_ID 72 FREQUENCY
SALES CUST_ID 6188 HEIGHT BALANCED
SALES TIME_ID 1460 NONE
SALES CHANNEL_ID 4 FREQUENCY
SALES PROMO_ID 4 FREQUENCY
SALES QUANTITY_SOLD 1 FREQUENCY
SALES AMOUNT_SOLD 3118 HEIGHT BALANCED
7 rows selected.
D => Let we delete Stats again and collect using DEFAULT method, i.e method_opt=> 'FOR ALL COLUMNS SIZE AUTO'
When we use AUTO clause , Oracle gathers statistics for only columns which are used in Predicated mostly and skewed.
SQL> SQL> exec DBMS_STATS.DELETE_TABLE_STATS(ownname => 'ABHI_TEST', tabname =>'SALES');
PL/SQL procedure successfully completed.
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'ABHI_TEST', tabname =>'SALES', estimate_percent=>2 ,method_opt=> 'FOR ALL COLUMNS SIZE AUTO');
PL/SQL procedure successfully completed.
SQL> SELECT TABLE_NAME,COLUMN_NAME,NUM_DISTINCT,HISTOGRAM FROM USER_tAB_COL_STATISTICS WHERE TABLE_NAME='SALES'
2 ;
TABLE_NAME COLUMN_NAME NUM_DISTINCT HISTOGRAM
-------------------------------------------------------------------
SALES PROD_ID 72 FREQUENCY
SALES CUST_ID 6182 NONE
SALES TIME_ID 1461 NONE
SALES CHANNEL_ID 4 NONE
SALES PROMO_ID 4 NONE
SALES QUANTITY_SOLD 1 NONE
SALES AMOUNT_SOLD 3111 NONE
7 rows selected.
SQL> SELECT * FROM SALES WHERE PROMO_ID<1000;
PROD_ID CUST_ID TIME_ID CHANNEL_ID PROMO_ID QUANTITY_SOLD AMOUNT_SOLD
---------- ---------- --------- ---------- ---------- ------------- -----------
14 2004 21-JAN-98 3 999 1 1264.4
14 2013 21-JAN-98 3 999 1 1264.4
14 2762 21-JAN-98 3 999 1 1264.4
14 3014 21-JAN-98 3 999 1 1264.4
14 3268 21-JAN-98 3 999 1 1264.4
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'ABHI_TEST', tabname =>'SALES', estimate_percent=>2 ,method_opt=> 'FOR ALL COLUMNS SIZE AUTO');
PL/SQL procedure successfully completed.
SQL> SELECT TABLE_NAME,COLUMN_NAME,NUM_DISTINCT,HISTOGRAM FROM USER_tAB_COL_STATISTICS WHERE TABLE_NAME='SALES';
TABLE_NAME COLUMN_NAME NUM_DISTINCT HISTOGRAM
------------------------------------------------------
SALES PROD_ID 72 FREQUENCY
SALES CUST_ID 6164 NONE
SALES TIME_ID 1461 NONE
SALES CHANNEL_ID 4 NONE
SALES PROMO_ID 4 FREQUENCY
SALES NTITY_SOLD 1 NONE
SALES AMOUNT_SOLD 3126 NONE
7 rows selected.
SQL>
3=>Storing statistics in user-defined table (stattab,statid,statowner).
In this demo we are going to store all statistics information in user-defined tables using stattab values.
For this first we nee to create a Statistics table and then it will be stored there.
Steps are as below.
A=> Create Stats table using DBMS_STATS .
SQL> EXEC DBMS_STATS.CREATE_STAT_TABLE ('ABHI_TEST','MYSTAT_TAB');
PL/SQL procedure successfully completed.
SQL> desc ABHI_TEST.MYSTAT_TAB
Name Null? Type
-----------------------------------------
STATID VARCHAR2(128)
TYPE CHAR(1)
VERSION NUMBER
FLAGS NUMBER
C1 VARCHAR2(128)
C2 VARCHAR2(128)
C3 VARCHAR2(128)
C4 VARCHAR2(128)
C5 VARCHAR2(128)
C6 VARCHAR2(128)
N1 NUMBER
N2 NUMBER
N3 NUMBER
N4 NUMBER
N5 NUMBER
N6 NUMBER
N7 NUMBER
N8 NUMBER
N9 NUMBER
N10 NUMBER
N11 NUMBER
N12 NUMBER
N13 NUMBER
D1 DATE
T1 TIMESTAMP(6) WITH TIME ZONE
R1 RAW(1000)
R2 RAW(1000)
R3 RAW(1000)
CH1 VARCHAR2(1000)
CL1 CLOB
BL1 BLOB
SQL> select count(*) from ABHI_TEST.MYSTAT_TAB;
COUNT(*)
----------
0
B=> Collect Stats in stats table using DBMS_STATS .
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'ABHI_TEST', tabname =>'SALES',stattab=>'MYSTAT_TAB');
PL/SQL procedure successfully completed.
SQL> select count(*) from ABHI_TEST.MYSTAT_TAB;
COUNT(*)
----------
82
C=> Check values as below.
D=> Add statid & statowner if required as below.
exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'ABHI_TEST', tabname =>'SALES',stattab=>'MYSTAT_TAB',statid=>'MY_ID1',statown=>'abhi_test');
E=> Dropping user defined stat table.
BEGIN
DBMS_STATS.DROP_STAT_TABLE('ABHI_TEST','MYSTAT_TAB');
END;
/
PL/SQL procedure successfully completed.
4=> Changing CASCADE parameter values (TURE or FALSE)
This parameter determines whether to collect index statistics as part of gathering table statistics.
Let we test a scenrio as below. In this DEMO we will be chaning CASCADE preferences value as FALSE .
A=> Get count of rows for table as below .
SQL> SELECT COUNT(*) FROM EMP_dATA;
COUNT(*)
----------
1300
B=> Get Index value and it's Last gather time as below .
SQL> SELECT INDEX_NAME,LAST_ANALYZED FROM dba_indexes where table_name='EMP_DATA';
INDEX_NAME LAST_ANALYZED
--------------------
IDX_EMPD 28-MAY-2022 16:09:23
C=> Get Current Value for CASCADE parameter as below.
SELECT DBMS_STATS.GET_PREFS ('CASCADE', 'ABHI_TEST','EMP_DATA') AS "CASCADE" FROM DUAL;
CASCADE
--------------------------------
DBMS_STATS.AUTO_CASCADE
D=> Now let we change CASCASE value to FALSE as below.
SQL> EXEC DBMS_STATS.SET_TABLE_PREFS('ABHI_TEST','EMP_DATA','CASCADE','false');
PL/SQL procedure successfully completed.
E=> Let we DELETE few records and commit as below from TABLE.
SQL> DELETE FROM EMP_dATA WHERE ROWNUM<1000;
999 rows deleted.
SQL> COMMIT;
Commit complete.
F=> Check current count as below.
SQL> SELECT COUNT(*) FROM EMP_DATA;
COUNT(*)
----------
301
G=> Let we do SELECT as below from TABLE.
SQL> SELECT ID FROM EMP_DATA WHERE ID > 1000;
H=> Check Execution plan for above query
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID bqa1z6970bs22, child number 0
-------------------------------------
SELECT ID FROM EMP_DATA WHERE ID > 1000
Plan hash value: 1124357846
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | INDEX RANGE SCAN| IDX_EMPD | 300 | 1200 | 2 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID">1000)
18 rows selected.
I=> Now let check statistics as for INDEX from DBA_IND_STATISTICS views.
SQL> SELECT OWNER,INDEX_NAME,TABLE_NAME,NUM_ROWS FROM DBA_IND_STATISTICS WHERE TABLE_NAME='EMP_DATA';
OWNER INDEX_NAME TABLE_NAME NUM_ROWS
--------------- ----------
ABHI_TEST IDX_EMPD EMP_DATA 1300
As we can see It's still showing 1300 records ,
J=> Now let we gather statistics without using CASCADE clause and this will default to "FALSE" as we set above.
SQL> exec dbms_stats.gather_table_stats('ABHI_TEST', 'EMP_DATA');
PL/SQL procedure successfully completed.
K=> Now let check statistics as for INDEX from DBA_IND_STATISTICS views.
SQL> SELECT OWNER,INDEX_NAME,TABLE_NAME,NUM_ROWS FROM DBA_IND_STATISTICS WHERE TABLE_NAME='EMP_DATA';
OWNER INDEX_NAME TABLE_NAME NUM_ROWS
--------------- ----------
ABHI_TEST IDX_EMPD EMP_DATA 1300
It's still showing 1300 rows , hence CASCADE is not working . Let we change CASCADE to it's DEFAULT value as DBMS_STATS.AUTO_CASCADE
SQL> EXEC DBMS_STATS.SET_TABLE_PREFS('ABHI_TEST','EMP_DATA','CASCADE','DBMS_STATS.AUTO_CASCADE');
PL/SQL procedure successfully completed.
L=> Now let we gather statistics without using CASCADE clause and this will default to "DBMS_STATS.AUTO_CASCADE" as we set above.
SQL> exec dbms_stats.gather_table_stats('ABHI_TEST', 'EMP_DATA');
M=> Now let check statistics as for INDEX from DBA_IND_STATISTICS views.
SQL> SELECT OWNER,INDEX_NAME,TABLE_NAME,NUM_ROWS FROM DBA_IND_STATISTICS WHERE TABLE_NAME='EMP_DATA';
OWNER INDEX_NAME TABLE_NAME NUM_ROWS
--------------- ----------
ABHI_TEST IDX_EMPD EMP_DATA 301
5=> Changing OPTIONS parameter values (GATHER & GATHER AUTO)
Monitoring tracks the approximate number of DML operations on a table and whether the table has been truncated since the most recent statistics collection.
To check whether statistics are stale, query the STALE_STATS column in DBA_TAB_STATISTICS and DBA_IND_STATISTICS.
This column is based on data in the DBA_TAB_MODIFICATIONS view and the STALE_PERCENT preference for DBMS_STATS.
Starting in Oracle Database 12c Release 2 (12.2), you no longer need to use DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO to ensure that view metadata is current.
The statistics shown in the DBA_TAB_STATISTICS, DBA_IND_STATISTICS, and DBA_TAB_MODIFICATIONS views are obtained from both disk and memory.
The STALE_STATS column has the following possible values:
=> YES -The statistics are stale.
=> NO -The statistics are not stale.
=> null -The statistics are not collected.
Executing GATHER_SCHEMA_STATS or GATHER_DATABASE_STATS with the GATHER AUTO option collects statistics only for objects with no statistics or stale statistics.
When the OPTIONS parameter is set to GATHER STALE or GATHER AUTO, the GATHER_SCHEMA_STATS and GATHER_DATABASE_STATS,
procedures gather statistics for any table that has stale statistics and any table that is missing statistics.
If a monitored table has been modified more than 10%, then the database considers these statistics stale and gathers them again.
If the GATHER AUTO option is used then frequency histograms will be created using a sample rather than a full scan.
The following scenario shows where GATHER AUTO is used:
=> A table is created like this: CREATE TABLE NEWTAB as SELECT * FROM .....
This will create statistics on NEWTAB but no histograms.
=> Next, the DBA creates the histograms using GATHER AUTO on gather_table_stats.
=> The FREQUENCY histograms on NEWTAB will be created using a sample rather than a full table scan.
DEMO
SQL> CREATE TABLE AUTO_STATS AS SELECT * FROM all_objects;
Table created.
SQL> SET LINES 500
SQL> COL TABLE_NAME FOR A20
SQL> COL LAST_ANALYZED FOR A30
SQL> COL STATLE_STATS FOR A10
SQL> ALTER SESSION SET NLS_dATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> select table_name, last_analyzed,stale_stats
from user_tab_statistics
where table_name in ('AUTO_STATS')
order by 1,2; 2 3 4
TABLE_NAME LAST_ANALYZED STALE_S
-------------------- ------------------------------ -------
AUTO_STATS 29-MAY-2022 16:46:14 NO
SQL> SELECT TABLE_NAME,COLUMN_NAME,HISTOGRAM FROM USER_tAB_COL_STATISTICS WHERE TABLE_NAME='AUTO_STATS';
TABLE_NAME COLUMN_NAME HISTOGRAM
-------------------- ----------------------------------------------------------------------------------------------------
AUTO_STATS OWNER NONE
AUTO_STATS OBJECT_NAME NONE
AUTO_STATS SUBOBJECT_NAME NONE
AUTO_STATS OBJECT_ID NONE
AUTO_STATS DATA_OBJECT_ID NONE
AUTO_STATS OBJECT_TYPE NONE
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'ABHI_TEST', tabname =>'AUTO_STATS',OPTIONS=>'GATHER AUTO');
PL/SQL procedure successfully completed.
SQL> /
SQL> SELECT * FROM AUTO_STATS WHERE STATUS='VALID' and rownum<20;
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'ABHI_TEST', tabname =>'AUTO_STATS',OPTIONS=>'GATHER AUTO');
PL/SQL procedure successfully completed.
SQL> SELECT TABLE_NAME,COLUMN_NAME,HISTOGRAM FROM USER_tAB_COL_STATISTICS WHERE TABLE_NAME='AUTO_STATS';
TABLE_NAME COLUMN_NAME HISTOGRAM
-------------------- -----------------------------------------------------------------
AUTO_STATS OWNER NONE
AUTO_STATS OBJECT_NAME NONE
AUTO_STATS SUBOBJECT_NAME NONE
AUTO_STATS OBJECT_ID NONE
AUTO_STATS DATA_OBJECT_ID NONE
AUTO_STATS OBJECT_TYPE NONE
AUTO_STATS CREATED NONE
AUTO_STATS LAST_DDL_TIME NONE
AUTO_STATS TIMESTAMP NONE
AUTO_STATS STATUS FREQUENCY
AUTO_STATS TEMPORARY NONE
B=> GATHER_SCHEMA_STATS Procedures
This procedure gathers statistics for all objects in a schema.
DBMS_STATS.GATHER_SCHEMA_STATS (
ownname VARCHAR2,
estimate_percent NUMBER DEFAULT to_estimate_percent_type (get_param('ESTIMATE_PERCENT')),
block_sample BOOLEAN DEFAULT FALSE,
method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),
degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),
granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')),
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
options VARCHAR2 DEFAULT 'GATHER',
objlist OUT ObjectTab,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (get_param('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE,
obj_filter_list ObjectTab DEFAULT NULL);
DEMO
1=> Using OBJLIST parameter
List of objects found to be stale or empty.
The objlist parameter identifies an output parameter for the LIST STALE and LIST EMPTY options.
The objlist parameter is of type DBMS_STATS.OBJECTTAB.
Therefore you declare an output variable(of your choice, no reserved words) to be used by OBJLIST.
To use OBJLIST to show object with stale stats we are concerned with two parameters:
1) OPTIONS- Suggest setting to value LIST STALE. This will return a list of stale objects as determined by looking at the *_tab_modifications views.
2) OBJLIST- Descirption: List of objects found to be stale or empty.
A=> Let we create a table and mark this table as STALE as per below setup.
SQL> alter session set NLS_DATE_FORMAT = 'HH24:MI:SS YYYY-MM-DD';
Session altered.
SQL> create table STALE_TAB (col1 number(10));
Table created.
SQL> create index STALE_TAB_i on STALE_TAB(col1);
Index created.
SQL> exec dbms_stats.set_table_prefs(user,'STALE_TAB','stale_percent','5')
PL/SQL procedure successfully completed.
SQL> insert into STALE_TAB values (1);
1 row created.
SQL> /
1 row created.
SQL> COMMIT;
Commit complete.
SQL> exec dbms_stats.gather_table_stats(user,'STALE_TAB')
PL/SQL procedure successfully completed.
SQL> select table_name, last_analyzed,stale_stats
from user_tab_statistics
where table_name in ('STALE_TAB')
order by 1,2;
TABLE_NAME LAST_ANALYZED STALE_STATS
------------------- ---------------
STALE_TAB 17:34:30 2022-05-29 NO
SQL> insert into STALE_TAB values (1);
1 row created.
SQL> /
1 row created.
SQL> COMMIT;
Commit complete.
SQL> exec dbms_stats.flush_database_monitoring_info
PL/SQL procedure successfully completed.
1 select table_name, last_analyzed,stale_stats
2 from user_tab_statistics
3 where table_name in ('STALE_TAB')
4* order by 1,2;
TABLE_NAME LAST_ANALYZED STALE_STATS
---------------------------------------- ------------------------------
STALE_TAB 17:34:30 2022-05-29 YES
B=> LET we execute objlist with gather_schema_stats as below.
SQL> SET SERVEROUTPUT ON
SQL>
SQL>
SQL> declare
mystaleobjs dbms_stats.objecttab;
begin
-- check whether there is any stale objects
dbms_stats.gather_schema_stats(ownname=>'ABHI_TEST', options=>'LIST STALE',objlist=>mystaleobjs);
for i in 1 .. mystaleobjs.count loop
dbms_output.put_line(mystaleobjs(i).objname);
end loop;
end;
/
exit;
STALE_TAB <<<<<<<<<<<<, So this returns list of table having STALE STATS
PL/SQL procedure successfully completed.
2=> Using OPTIONS parameter
We have below values for OPTIONS parameters.
Collection_Mode -> GATHER,GATHER AUTO,GATHER STALE,GATHER EMPTY
Reporting_Mode -> LIST AUTO,LIST STALE, LIST EMPTY
A=> Using LIST AUTO value of OPTIONS parameter.
— Returns a list of objects to be processed with GATHER AUTO.
SQL> SET SERVEROUTPUT ON
SQL>
SQL>
SQL> declare
mystaleobjs dbms_stats.objecttab;
begin
-- check whether there is any stale objects
dbms_stats.gather_schema_stats(ownname=>'ABHI_TEST', options=>'LIST AUTO',objlist=>mystaleobjs);
for i in 1 .. mystaleobjs.count loop
dbms_output.put_line(mystaleobjs(i).objname);
end loop;
end;
/
exit;
STALE_TAB >>>>>> table which will be gatherd using Gather Auto
PL/SQL procedure successfully completed.
SQL>
B=> Using LIST EMPTY value of OPTIONS parameter.
Returns a list of objects that currently have no statistics.
Let we create table which will have NULL stale_stats as below.
SQL> CREATE TABLE STALE_STAT1 (IND INT);
Table created.
SQL> INSERT INTO STALE_sTAT1 SELECT ID FROM EMP_DATA;
282 rows created.
SQL> COMMIT;
Commit complete.
SQL> SELECT STALE_STATS FROM DBA_TAB_STATISTICS WHERE TABLE_NAME='STALE_STAT1';
STALE_STATS
-------
SQL> declare
mystaleobjs dbms_stats.objecttab;
begin
-- check whether there is any stale objects
dbms_stats.gather_schema_stats(ownname=>'ABHI_TEST', options=>'LIST EMPTY',objlist=>mystaleobjs);
for i in 1 .. mystaleobjs.count loop
dbms_output.put_line(mystaleobjs(i).objname);
end loop;
end;
/
STALE_STAT1 >>>>>>>>>>>>>>. This table having NULL STATS hence returned.
PL/SQL procedure successfully completed.
C=> Using GATHER EMPTY value for OPTIONS parameter.
=> Find list of table having EMPTY (NULL) Stats as below.
SQL> declare
mystaleobjs dbms_stats.objecttab;
begin
-- check whether there is any stale objects
dbms_stats.gather_schema_stats(ownname=>'ABHI_TEST', options=>'LIST EMPTY',objlist=>mystaleobjs);
for i in 1 .. mystaleobjs.count loop
dbms_output.put_line(mystaleobjs(i).objname);
end loop;
end;
/
STALE_STAT1 >>>>>> this is NULL stats tables
PL/SQL procedure successfully completed.
SQL> SELECT STALE_STATS FROM DBA_TAB_STATISTICS WHERE TABLE_NAME='STALE_STAT1';
STALE_S
-------
=> Run Proc with GATHE EMPTY value and check stale_stats as below
SQL> exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'ABHI_TEST',OPTIONS=>'GATHER EMPTY');
PL/SQL procedure successfully completed.
SQL> SELECT STALE_STATS FROM DBA_TAB_STATISTICS WHERE TABLE_NAME='STALE_STAT1';
STALE_S
-------
NO
D=> Using GATHER STALE value for OPTIONS parameter
=> Find Stale statistics tables as below,
1 select table_name, last_analyzed,stale_stats
2 from user_tab_statistics
3 where table_name in ('STALE_TAB')
4* order by 1,2;
TABLE_NAME LAST_ANALYZED STALE_STATS
---------------------------------------- ------------------------------
STALE_TAB 17:34:30 2022-05-29 YES
SQL> SET SERVEROUTPUT ON
SQL> declare
mystaleobjs dbms_stats.objecttab;
begin
-- check whether there is any stale objects
dbms_stats.gather_schema_stats(ownname=>'ABHI_TEST', options=>'LIST STALE',objlist=>mystaleobjs);
for i in 1 .. mystaleobjs.count loop
dbms_output.put_line(mystaleobjs(i).objname);
end loop;
end;
/
exit;
STALE_TAB <<<<<<<<<<<<, So this returns list of table having STALE STATS
PL/SQL procedure successfully completed.
=> Let we use GATHER STALE in OPTIONS parameter as below and verify.
SQL> SET SERVEROUTPUT ON
SQL> exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'ABHI_TEST',OPTIONS=>'GATHER STALE');SQL>
PL/SQL procedure successfully completed.
SQL> SELECT STALE_STATS FROM DBA_TAB_STATISTICS WHERE TABLE_NAME='STALE_TAB';
STALE_S
-------
NO
E=> Using GATHER AUTO in options parameter .( GATHER_EMPTY+GATHER_STALE).
Gathers all necessary statistics automatically.
Let we find list of tables having STALE or EMPTY statistics as below.
SQL> SELECT TABLE_NAME ,LAST_ANALYZED,STALE_STATS FROM DBA_TAB_STATISTICS WHERE TABLE_NAME IN ('EMP_DATA','EMPTY_STATS','STALE_STATS');
TABLE_NAME LAST_ANALYZED STALE_S
---------------------------------------- ---------------------------------------- -------
EMPTY_STATS NULL NULL
STALE_STATS 18:10:45 2022-05-29 YES
EMP_DATA 23:31:07 2022-05-28 NO
=> Let we collect schema level statistics using obj_fileter and GATHER AUTO in options parameter as below,
SQL> DECLARE
filter_lst DBMS_STATS.OBJECTTAB := DBMS_STATS.OBJECTTAB();
BEGIN
filter_lst.extend(3);
filter_lst(1).ownname := user;
filter_lst(1).objname := 'STALE_STATS';
filter_lst(1).ownname := user;
filter_lst(1).objname := 'EMPTY_STATS';
filter_lst(1).ownname := user;
filter_lst(1).objname := 'EMP_DATA';
DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>user,obj_filter_list=>filter_lst,options=>'gather auto');
END;
/
PL/SQL procedure successfully completed.
=> Now let we check below details.
SQL> SELECT TABLE_NAME ,LAST_ANALYZED,STALE_STATS FROM DBA_TAB_STATISTICS WHERE TABLE_NAME IN ('EMP_DATA','EMPTY_STATS','STALE_STATS');
TABLE_NAME LAST_ANALYZED STALE_S
---------------------------------------- ---------------------------------------- -------
STALE_STATS 18:19:24 2022-05-29 NO
EMP_DATA 23:31:07 2022-05-28 NO
EMPTY_STATS 18:19:24 2022-05-29 NO
As we can see only tables which are either STALE or EMPTY stats having got collected whereas EMP_DATA was not gathered.
3=> Using obj_filter_list parameter
A list of object filters.
When provided, GATHER_SCHEMA_STATS will gather statistics only on objects which satisfy at least one object filter in the list as needed.
In a single object filter, we can specify the constraints on the object attributes. The attribute values specified in the object filter are case- insensitive unless double-quoted.
SQL> DECLARE
filter_lst DBMS_STATS.OBJECTTAB := DBMS_STATS.OBJECTTAB();
BEGIN
filter_lst.extend(3);
filter_lst(1).ownname := user;
filter_lst(1).objname := 'STALE_STATS';
filter_lst(1).ownname := user;
filter_lst(1).objname := 'EMPTY_STATS';
filter_lst(1).ownname := user;
filter_lst(1).objname := 'EMP_DATA';
DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>user,obj_filter_list=>filter_lst,options=>'gather auto');
END;
/
C=> GATHER_INDEX_STATS Procedure
This procedure gathers index statistics. It attempts to parallelize as much of the work as possible.
DBMS_STATS.GATHER_INDEX_STATS (
ownname VARCHAR2,
indname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
estimate_percent NUMBER DEFAULT to_estimate_percent_type (GET_PARAM('ESTIMATE_PERCENT')),
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),
granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (GET_PARAM('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE);
DEMO
1=> Changing ESTIMATE_PERCENT value (NULL,AUTO_SAMPLE_SIZE,Integer).
Percentage of rows to estimate (NULL means compute). "NULL means COMPUTE" is "NULL means 100 percent".
A-> Using Estimate_Percent as NULL
SQL> CREATE TABLE SALES_IND_TEST AS SELECT * FROM SALES;
Table created.
SQL> CREATE INDEX SALE_ID ON SALES_IND_TEST(PROD_ID);
Index created.
SQL> SELECT SAMPLE_SIZE ,NUM_ROWS FROM DBA_IND_STATISTICS WHERE INDEX_NAME='SALE_ID';
SAMPLE_SIZE NUM_ROWS
----------- ----------
3675073 3675073
SQL> SELECT COUNT(*) FROM SALES_IND_TEST;
COUNT(*)
----------
3675073
SQL> DELETE FROM SALES_IND_TEST WHERE ROWNUM<1000;
999 rows deleted.
SQL> COMMIT;
Commit complete.
SQL> SELECT SAMPLE_SIZE ,NUM_ROWS FROM DBA_IND_STATISTICS WHERE INDEX_NAME='SALE_ID';
SAMPLE_SIZE NUM_ROWS
----------- ----------
3675073 3675073
SQL> exec DBMS_STATS.GATHER_INDEX_STATS(ownname => 'ABHI_TEST', INDNAME=>'SALE_ID',ESTIMATE_PERCENT=>NULL);
PL/SQL procedure successfully completed.
SQL> SELECT SAMPLE_SIZE ,NUM_ROWS FROM DBA_IND_STATISTICS WHERE INDEX_NAME='SALE_ID';
SAMPLE_SIZE NUM_ROWS
----------- ----------
3674074 3674074
B-> Using AUTO_SAMPLE_SIZE as below.
SQL> Exec DBMS_STATS.GATHER_INDEX_STATS(ownname => 'ABHI_TEST', INDNAME=>'SALE_ID',ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE);
PL/SQL procedure successfully completed.
SQL> SELECT SAMPLE_SIZE ,NUM_ROWS FROM DBA_IND_STATISTICS WHERE INDEX_NAME='SALE_ID';
SAMPLE_SIZE NUM_ROWS
----------- ----------
3673075 3673075
SQL>
2=> Changing granularity Parameter Values( AUTO,ALL,GLOBAL,GLOBAL & PARTITION,PARTITION,SUBPARTITION)
Setup table and Non-Partitioned & Partitioned Index as below
CREATE TABLE PARTITIONS_STATS (
id NUMBER,
code VARCHAR2(10),
description VARCHAR2(50)
)
PARTITION BY RANGE (id)
(
PARTITION t1 VALUES LESS THAN (10),
PARTITION t2 VALUES LESS THAN (20),
PARTITION t3 VALUES LESS THAN (30),
PARTITION t4 VALUES LESS THAN (40),
PARTITION t5 VALUES LESS THAN (MAXVALUE)
);
Table created.
SQL> SELECT TABLE_NAME,PARTITION_NAME,HIGH_VALUE FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='PARTITIONS_STATS';
TABLE_NAME PARTITION_ HIGH_VALUE
---------------------------------------- ---------- -----------
PARTITIONS_STATS T1 10
PARTITIONS_STATS T2 20
PARTITIONS_STATS T3 30
PARTITIONS_STATS T4 40
PARTITIONS_STATS T5 MAXVALUE
SQL> SELECT LOCALITY FROM DBA_PART_INDEXES WHERE INDEX_NAME IN ('T1_CODE_IDX','T1_IDX');
LOCALI
------
LOCAL
SQL> SELECT INDEX_NAME, LOCALITY FROM DBA_PART_INDEXES WHERE INDEX_NAME IN ('T1_CODE_IDX','T1_IDX');
INDEX_NAME LOCALI
----------------------
T1_CODE_IDX LOCAL
SQL> SELECT INDEX_NAME,NUM_ROWS FROM DBA_INDEXES WHERE INDEX_NAME IN ('T1_CODE_IDX','T1_IDX');
INDEX_NAME NUM_ROWS
-------------------- ----------
T1_IDX 0
T1_CODE_IDX 0
SQL> SELECT INDEX_OWNER,INDEX_NAME,PARTITION_NAME,NUM_ROWS FROM DBA_IND_PARTITIONS WHERE INDEX_NAME IN ('T1_CODE_IDX','T1_IDX');
INDEX_OWNER INDEX_NAME PARTITION_NAME NUM_ROWS
-------------------- -------------------- -------------------- ----------
ABHI_TEST T1_CODE_IDX T1 0
ABHI_TEST T1_CODE_IDX T2 0
ABHI_TEST T1_CODE_IDX T3 0
ABHI_TEST T1_CODE_IDX T4 0
ABHI_TEST T1_CODE_IDX T5 0
SQL> select index_name,partitioned from dba_indexes where index_name IN ('T1_CODE_IDX','T1_IDX');
INDEX_NAME PAR
-------------------- ---
T1_IDX NO
T1_CODE_IDX YES
SQL> INSERT INTO PARTITIONS_STATS VALUES (1,'ONE','THIS IS ONE');
1 row created.
SQL> INSERT INTO PARTITIONS_STATS VALUES (11,'ELEVEN','THIS IS ELEV');
1 row created.
SQL> INSERT INTO PARTITIONS_STATS VALUES (21,'TWENTY ONE','THIS IS 21');
1 row created.
SQL> INSERT INTO PARTITIONS_STATS VALUES (31,'THIRTY ONE','THIS IS 31');
1 row created.
SQL> INSERT INTO PARTITIONS_STATS VALUES (41,'FORTY ONE','THIS IS 41');
1 row created.
SQL> INSERT INTO PARTITIONS_STATS VALUES (51,'FIFTY ONE','THIS IS 51');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT INDEX_OWNER,INDEX_NAME,PARTITION_NAME,NUM_ROWS FROM DBA_IND_PARTITIONS WHERE INDEX_NAME IN ('T1_CODE_IDX','T1_IDX');
INDEX_OWNER INDEX_NAME PARTITION_NAME NUM_ROWS
-------------------- -------------------- -------------------- ----------
ABHI_TEST T1_CODE_IDX T1 0
ABHI_TEST T1_CODE_IDX T2 0
ABHI_TEST T1_CODE_IDX T3 0
ABHI_TEST T1_CODE_IDX T4 0
ABHI_TEST T1_CODE_IDX T5 0
SQL> SELECT INDEX_NAME,NUM_ROWS FROM DBA_INDEXES WHERE INDEX_NAME IN ('T1_CODE_IDX','T1_IDX');
INDEX_NAME NUM_ROWS
-------------------- ----------
T1_IDX 0
T1_CODE_IDX 0
A=> Setting Granularity as "Partition" as below.
SQL> exec DBMS_STATS.GATHER_INDEX_STATS(ownname => 'ABHI_TEST', INDNAME=>'T1_CODE_IDX',granularity=>'PARTITION');
PL/SQL procedure successfully completed.
SQL> SELECT INDEX_OWNER,INDEX_NAME,PARTITION_NAME,NUM_ROWS FROM DBA_IND_PARTITIONS WHERE INDEX_NAME IN ('T1_CODE_IDX','T1_IDX');
INDEX_OWNER INDEX_NAME PARTITION_NAME NUM_ROWS
-------------------- -------------------- -------------------- ----------
ABHI_TEST T1_CODE_IDX T1 1
ABHI_TEST T1_CODE_IDX T2 1
ABHI_TEST T1_CODE_IDX T3 1
ABHI_TEST T1_CODE_IDX T4 1
ABHI_TEST T1_CODE_IDX T5 2
SQL> SELECT INDEX_NAME,NUM_ROWS FROM DBA_INDEXES WHERE INDEX_NAME IN ('T1_CODE_IDX','T1_IDX');
INDEX_NAME NUM_ROWS
-------------------- ----------
T1_IDX 0
T1_CODE_IDX 6
B=> Setting Granularity as "GLOBAL" for NON-PARTITIONED INDEX as below.
SQL> exec DBMS_STATS.GATHER_INDEX_STATS(ownname => 'ABHI_TEST', INDNAME=>'T1_IDX',granularity=>'global');
PL/SQL procedure successfully completed.
SQL> SELECT INDEX_NAME,NUM_ROWS FROM DBA_INDEXES WHERE INDEX_NAME IN ('T1_CODE_IDX','T1_IDX');
INDEX_NAME NUM_ROWS
-------------------- ----------
T1_IDX 6
T1_CODE_IDX 6
c=> Setting Granularity as "GLOBAL" for PARTITIONED INDEX as below.
Let we Insert few records again as below.
INSERT INTO PARTITIONS_STATS VALUES (12,'FIFTY ONE1','THIS IS 511');
INSERT INTO PARTITIONS_STATS VALUES (22,'FIFTY ONE2','THIS IS 521');
INSERT INTO PARTITIONS_STATS VALUES (32,'FIFTY ONE3','THIS IS 513');
INSERT INTO PARTITIONS_STATS VALUES (42,'FIFTY ONE4','THIS IS 514');
INSERT INTO PARTITIONS_STATS VALUES (52,'FIFTY ONE5','THIS IS 515');
SQL> commit;
Commit complete.
SQL> select count(*) from PARTITIONS_STATS;
COUNT(*)
----------
11
SQL> SELECT INDEX_OWNER,INDEX_NAME,PARTITION_NAME,NUM_ROWS FROM DBA_IND_PARTITIONS WHERE INDEX_NAME IN ('T1_CODE_IDX','T1_IDX');
INDEX_OWNER INDEX_NAME PARTITION_NAME NUM_ROWS
-------------------- -------------------- -------------------- ----------
ABHI_TEST T1_CODE_IDX T1 1
ABHI_TEST T1_CODE_IDX T2 1
ABHI_TEST T1_CODE_IDX T3 1
ABHI_TEST T1_CODE_IDX T4 1
ABHI_TEST T1_CODE_IDX T5 2
SQL> exec DBMS_STATS.GATHER_INDEX_STATS(ownname => 'ABHI_TEST', INDNAME=>'T1_CODE_IDX',granularity=>'GLOBAL');
PL/SQL procedure successfully completed.
SQL> SELECT INDEX_OWNER,INDEX_NAME,PARTITION_NAME,NUM_ROWS FROM DBA_IND_PARTITIONS WHERE INDEX_NAME IN ('T1_CODE_IDX','T1_IDX');
INDEX_OWNER INDEX_NAME PARTITION_NAME NUM_ROWS
-------------------- -------------------- -------------------- ----------
ABHI_TEST T1_CODE_IDX T1 1
ABHI_TEST T1_CODE_IDX T2 1
ABHI_TEST T1_CODE_IDX T3 1
ABHI_TEST T1_CODE_IDX T4 1
ABHI_TEST T1_CODE_IDX T5 2
SQL> SELECT INDEX_NAME,NUM_ROWS FROM DBA_INDEXES WHERE INDEX_NAME IN ('T1_CODE_IDX','T1_IDX');
INDEX_NAME NUM_ROWS
-------------------- ----------
T1_IDX 6
T1_CODE_IDX 11
As we can see in above Example only GLOBAL Statistics for Partitioned INDEX gathered whereas Partition level stats are same for each partitions .
D=> Setting Granularity as "ALL" for PARTITIONED INDEX as below.
SQL> exec DBMS_STATS.GATHER_INDEX_STATS(ownname => 'ABHI_TEST', INDNAME=>'T1_CODE_IDX',granularity=>'ALL');
PL/SQL procedure successfully completed.
SQL> SELECT INDEX_OWNER,INDEX_NAME,PARTITION_NAME,NUM_ROWS FROM DBA_IND_PARTITIONS WHERE INDEX_NAME IN ('T1_CODE_IDX','T1_IDX');
INDEX_OWNER INDEX_NAME PARTITION_NAME NUM_ROWS
-------------------- -------------------- -------------------- ----------
ABHI_TEST T1_CODE_IDX T1 1
ABHI_TEST T1_CODE_IDX T2 2
ABHI_TEST T1_CODE_IDX T3 2
ABHI_TEST T1_CODE_IDX T4 2
ABHI_TEST T1_CODE_IDX T5 4
SQL> SELECT INDEX_NAME,NUM_ROWS FROM DBA_INDEXES WHERE INDEX_NAME IN ('T1_CODE_IDX','T1_IDX');
INDEX_NAME NUM_ROWS
-------------------- ----------
T1_IDX 6
T1_CODE_IDX 11
E=> Setting Granularity as "AUTO" for NON-PARTITIONED INDEX as below.
SQL> exec DBMS_STATS.GATHER_INDEX_STATS(ownname => 'ABHI_TEST', INDNAME=>'T1_IDX',granularity=>'AUTO');
PL/SQL procedure successfully completed.
SQL> SELECT INDEX_NAME,NUM_ROWS FROM DBA_INDEXES WHERE INDEX_NAME IN ('T1_CODE_IDX','T1_IDX');
INDEX_NAME NUM_ROWS
-------------------- ----------
T1_IDX 11
T1_CODE_IDX 11
SQL>
D=>GATHER_DICTIONARY_STATS Procedure
This procedure gathers statistics for dictionary schemasSYS,SYSTEMand schemas of RDBMS components. Dictionary statistics include the statistics on the tables and indexes owned by SYS (and other internal RDBMS schemas like SYSTEM) and the statistics on the fixed objects. DBMS_STATS.GATHER_DICTIONARY_STATS ( comp_id VARCHAR2 DEFAULT NULL, estimate_percent NUMBER DEFAULT to_estimate_percent_type (get_param('ESTIMATE_PERCENT')), block_sample BOOLEAN DEFAULT FALSE, method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'), degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')), granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'), cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')), stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, options VARCHAR2 DEFAULT 'GATHER AUTO', objlist OUT ObjectTab, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (get_param('NO_INVALIDATE')), obj_filter_list ObjectTab DEFAULT NULL); SQL> exec DBMS_STATS.GATHER_DICTIONARY_STATS (COMP_ID=>'CATALOG'); PL/SQL procedure successfully completed. SQL> SELECT NVL(TO_CHAR(LAST_ANALYZED, 'YYYY-MON-DD'), 'NO STATS') LAST_ANALYZED, COUNT(*) DICTIONARY_TABLES FROM DBA_TABLES WHERE OWNER = 'SYS' GROUP BY TO_CHAR(LAST_ANALYZED, 'YYYY-MON-DD') ORDER BY 1 DESC; LAST_ANALYZED DICTIONARY_TABLES -------------------- ----------------- NO STATS 72 2022-MAY-29 168
E=> GATHER_FIXED_OBJECTS_STATS Procedure
This procedure gathers statistics for all fixed objects (dynamic performance tables).
Fixed objects are the internal X$ tables and the so called dynamic performance views or V$ views which are based upon them.
These are not real tables and indexes, but rather memory structures. The statistics for the fixed objects need to be gathered manually;
they are not updated by the automatic statistics gathering.
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS (
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (get_param('NO_INVALIDATE')));
EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
-- DELETE:
EXEC DBMS_STATS.DELETE_FIXED_OBJECTS_STATS;
-- VIEW:
SELECT NVL(TO_CHAR(LAST_ANALYZED, 'YYYY-MON-DD'), 'NO STATS') LAST_ANALYZED, COUNT(*) FIXED_OBJECTS
FROM DBA_TAB_STATISTICS
WHERE OBJECT_TYPE = 'FIXED TABLE'
GROUP BY TO_CHAR(LAST_ANALYZED, 'YYYY-MON-DD')
ORDER BY 1 DESC;
F=> GATHER_DATABASE_STATS Procedures
This procedure gathers statistics for all objects in the database.
DBMS_STATS.GATHER_DATABASE_STATS (
estimate_percent NUMBER DEFAULT to_estimate_percent_type (get_param('ESTIMATE_PERCENT')),
block_sample BOOLEAN DEFAULT FALSE,
method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),
degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),
granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')),
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
options VARCHAR2 DEFAULT 'GATHER',
objlist OUT ObjectTab,
statown VARCHAR2 DEFAULT NULL,
gather_sys BOOLEAN DEFAULT TRUE,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (get_param('NO_INVALIDATE')),
obj_filter_list ObjectTab DEFAULT NULL);
-- GATHER:
EXEC DBMS_STATS.GATHER_DATABASE_STATS(ESTIMATE_PERCENT => 20);
EXEC DBMS_STATS.GATHER_DATABASE_STATS(ESTIMATE_PERCENT => 20, CASCADE => TRUE, DEGREE => 12);
EXEC DBMS_STATS.GATHER_DATABASE_STATS(ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, CASCADE => TRUE, DEGREE=>12);
-- DELETE:
EXEC DBMS_STATS.DELETE_DATABASE_STATS;
-- VIEW:
-- To view all the objects in the database that have stale stats or even no stats at all:
SELECT 'Total Number of Stale Tables: '||COUNT(*) OVER() TOTAL_COUNT, OWNER, TABLE_NAME OBJECT_NAME, 'TABLE' OBJECT_TYPE, PARTITION_NAME, SUBPARTITION_NAME, LAST_ANALYZED, GLOBAL_STATS, USER_STATS, STATTYPE_LOCKED, STALE_STATS
FROM DBA_TAB_STATISTICS
WHERE STATTYPE_LOCKED IS NULL AND
(STALE_STATS IS NULL OR STALE_STATS='YES') AND
OWNER NOT IN ('ANONYMOUS', 'CTXSYS', 'DBSNMP', 'EXFSYS','LBACSYS','MDSYS','MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS','ORDSYS','OUTLN','SI_INFORMTN_SCHEMA','SYS', 'SYSMAN','SYSTEM','TSMSYS','WK_TEST','WKSYS','WKPROXY','WMSYS','XDB' ) AND
OWNER NOT LIKE 'FLOW%'
UNION ALL
SELECT 'Total Number of Stale Indexes: '||COUNT(*) OVER() TOTAL_COUNT, OWNER, INDEX_NAME OBJECT_NAME, 'INDEX' OBJECT_TYPE, PARTITION_NAME, SUBPARTITION_NAME, LAST_ANALYZED, GLOBAL_STATS, USER_STATS, STATTYPE_LOCKED, STALE_STATS
FROM DBA_IND_STATISTICS
WHERE STATTYPE_LOCKED IS NULL AND
(STALE_STATS IS NULL OR STALE_STATS='YES') AND
OWNER NOT IN ('ANONYMOUS', 'CTXSYS', 'DBSNMP', 'EXFSYS','LBACSYS','MDSYS','MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS','ORDSYS','OUTLN','SI_INFORMTN_SCHEMA','SYS', 'SYSMAN','SYSTEM','TSMSYS','WK_TEST','WKSYS','WKPROXY','WMSYS','XDB' ) AND
OWNER NOT LIKE 'FLOW%';
G=> GATHER_SYSTEM_STATS Procedure
This procedure gathers system statistics. DBMS_STATS.GATHER_SYSTEM_STATS ( gathering_mode VARCHAR2 DEFAULT 'NOWORKLOAD', interval INTEGER DEFAULT NULL, stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL); DEMO A=>Changing gathering_mode (noworkload,start|stop,interval) all System gathering informations available in SYS.AUX_STATS$ views as below.=> Using Interval caluse SQL> exec DBMS_STATS.gather_system_stats('INTERVAL', INTERVAL => 1); PL/SQL procedure successfully completed. SQL> => Using start & stop options as below. SQL> EXEC DBMS_STATS.GATHER_SYSTEM_STATS (gathering_mode=>'START'); PL/SQL procedure successfully completed. SQL> select * from tab where rownum<10; ..... .... SQL> EXEC DBMS_STATS.GATHER_SYSTEM_STATS (gathering_mode=>'STOP'); PL/SQL procedure successfully completed. >
© 2021 Ace2Oracle. All Rights Reserved | Developed By IBOX444