RUNNING GATHERSTATS
Description:
In this blog, we are going to see what has gathered stats and various levels of gather stats.
What is GATHERSTATS:
To gather stats in oracle we require to use the DBMS_STATS package. It will collect the statistics in parallel with collecting the global statistics for partitioned objects. The DBMS_STATS package is specially used only for optimizer statistics. As I explained in the first paragraph the dbms_stats is very vital for good SQL performance. We require to gather the stats before adjusting or setting up any optimizer parameters in oracle.
The less the query cost the execution time of the query is fast. We must have to gather the statistics on regular basis for database objects to give the best information to the Oracle optimizer to run queries in the best possible time. Using the analysis statement is the traditional way of checking the cost of the query. But nowadays to gather stats in oracle we need to use the DBMS_STATS package.
Gather STATS:
CASCADE => TRUE: Gather statistics on the indexes as well. If not used
Oracle will determine whether to collect it or not.
DEGREE => 4: Degree of parallelism.
ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE : (DEFAULT) Auto set
the sample size % for skew(distinct) values (accurate and faster than
setting a manual sample size).
METHOD_OPT=> : For gathering Histograms:
FOR COLUMNS SIZE AUTO: You can specify one column between “”
instead of all columns.
FOR ALL COLUMNS SIZE REPEAT: Prevent deletion of histograms and collect
it only for columns that already have histograms.
FOR ALL COLUMNS: Collect histograms on all columns.
FOR ALL COLUMNS SIZE SKEWONLY: Collect histograms for columns that have
skewed values should test skewness first
FOR ALL INDEXED COLUMNS: Collect histograms for columns that
have indexes only.
DATABASE Level:
Gathering statistics for all objects in the database, the cascade
will include indexes
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.
SQL> select OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS from
DBA_TAB_STATISTICS where STALE_STATS='YES';
SQL> exec dbms_stats.gather_database_stats(cascade=>TRUE,method_opt =>'FOR
ALL COLUMNS SIZE AUTO');
PL/SQL procedure successfully completed.
SCHEMA level:
Gathering statistics for all objects in a schema, the cascade
will include indexes.If not used Oracle will determine whether to
collect it or not.
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
select OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS from DBA_TAB_STATISTICS
where STALE_STATS='YES' and OWNER='&owner;
PL/SQL procedure successfully completed.
SQL> set timing on
SQL> exec dbms_stats.gather_schema_stats(ownname=>'&schema_name',
CASCADE=>TRUE,ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,degree =>4);
Enter value for schema_name: vbt
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.19
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('&schema_name');
Enter value for schema_name: vbt
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.09
TABLE Level:
The CASCADE parameter determines whether or not statistics are
gathered for the indexes on a table.
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('&schema_name');
Enter value for schema_name: vbt
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.09
SQL> SELECT OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS from
DBA_TAB_STATISTICS WHERE TABLE_NAME='&TNAME';
Enter value for tname: agent
old 1: SELECT OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS from
DBA_TAB_STATISTICS WHERE TABLE_NAME='&TNAME'
new 1: SELECT OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS from
DBA_TAB_STATISTICSWHERE TABLE_NAME='agent'
no rows selected
Index Statistics:
SQL> CREATE TABLE sam AS SELECT * FROM dba_tables ORDER BY table_name;
Table created.
SQL> CREATE INDEX idsam ON sam(table_name, num_rows);
Index created.
SQL> EXEC dbms_stats.gather_table_stats(ownname=>'SYS', tabname=>'sam');
PL/SQL procedure successfully completed.
SQL> SELECT t.table_name, i.index_name, t.blocks, t.num_rows,
i.clustering_factorFROM dba_tables t, dba_indexes i
WHERE t.table_name = i.table_name AND i.index_name='idsam';
TABLE_NAME INDEX_NAME BLOCKS NUM_ROWS CLUSTERING_FACTOR
------------------ ------------------- ---------- -----------------
sam idsam 46 1705 46
exec DBMS_STATS.GATHER_INDEX_STATS(ownname => '&OWNER',
indname =>'&INDEX_NAME',estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE);
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
