1. Gather dictionary stats:

-- It gathers statistics for dictionary schemas 'SYS', 'SYSTEM' and other internal schemas.

EXEC DBMS_STATS.gather_dictionary_stats;

2. Gather fixed object stats:

--- Fixed object means gv$ or v$views

EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

3. Gather full database stats:

EXEC DBMS_STATS.gather_database_stats;

-- With estimate_percent to 15 percent or any other value , if the db size very huge. 

EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15);
EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15, cascade => TRUE);

-- With auto sample size and parallel degree 

EXEC DBMS_STATS.gather_database_stats(estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,degree => 8);

4. Gather schema statistics:

EXEC DBMS_STATS.gather_schema_stats('DBACLSS');

EXEC DBMS_STATS.gather_schema_stats('DBACLASS', estimate_percent => 25);

EXEC DBMS_STATS.gather_schema_stats('DBACLASS', estimate_percent => 100, cascade => TRUE);

-- STATS WITH AUTO ESTIMATION and degree 8 

exec dbms_stats.gather_schema_stats( ownname => 'DBACLASS',method_opt => 'FOR ALL COLUMNS SIZE 1', 
granularity => 'ALL', degree => 8, cascade => TRUE, 
estimate_percent=>dbms_stats.auto_sample_size);

5. Gather table statistics:

EXEC DBMS_STATS.gather_table_stats('DBACLASS', 'EMP');
EXEC DBMS_STATS.gather_table_stats('DBACLASS', 'EMP', estimate_percent => 15);
EXEC DBMS_STATS.gather_table_stats('DBACLASS', 'EMP', estimate_percent => 15, cascade => TRUE);

exec DBMS_STATS.GATHER_TABLE_STATS  (ownname => 'DBACLASS' , tabname => 'EMP',cascade => true, 
method_opt=>'for all indexed columns size 1', granularity => 'ALL', degree => 8);

exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'DBACLASS' , tabname => 'EMP',
cascade => true, method_opt=>'FOR ALL COLUMNS SIZE 1', granularity => 'ALL', degree => 8);

6. Gather stats for single partition of a table:

BEGIN
DBMS_STATS.GATHER_TABLE_STATS (
ownname => 'SCOTT',
tabname => 'TEST', --- TABLE NAME
partname => 'TEST_JAN2016' --- PARTITOIN NAME
method_opt=>'for all indexed columns size 1',
GRANULARITY => 'APPROX_GLOBAL AND PARTITION',
degree => 8);
END;
/

7. Lock/unlock statistics:

-- Lock stats of a schema:
EXEC DBMS_STATS.lock_schema_stats('DBACLASS');

-- Lock stats of a table:
EXEC DBMS_STATS.lock_table_stats('DBACLASS', 'EMP');

-- Lock stats of a partition:
EXEC DBMS_STATS.lock_partition_stats('DBACLASS', 'EMP', 'EMP');

-- unlock stats of a schema:

EXEC DBMS_STATS.unlock_schema_stats('DBACLASS');
-- unlock stats of a table:

EXEC DBMS_STATS.unlock_table_stats('DBACLASS', 'DBACLASS');
--unlock stats of a partition:

EXEC DBMS_STATS.unlock_partition_stats('DBACLASS', 'EMP', 'TEST_JAN2016');

--- check stats status:

SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'TEST' and owner = 'SCOTT';

8 . Delete statistics:

-- Delete complete db statistics:
EXEC DBMS_STATS.delete_database_stats;

-- Delete schema statistics:
EXEC DBMS_STATS.delete_schema_stats('DBACLASS');

-- Delete table statistics:
EXEC DBMS_STATS.delete_table_stats('DBACLASS', 'EMP');

-- Delete column statistics:
EXEC DBMS_STATS.delete_column_stats('DBACLASS', 'EMP', 'EMPNO');

-- Delete index statistics:

EXEC DBMS_STATS.delete_index_stats('DBACLASS', 'EMP_PK');

-- Delete dictionary statistics:
EXEC DBMS_STATS.delete_dictionary_stats;


-- Delete fixed object statistics:

exec dbms_stats.delete_fixed_objects_stats;

-- Delete system statistics:

exec dbms_stats.delete_system_stats('STAT_TAB');

8. Setting statistics preference:

-- View preference details for the database:

SELECT dbms_stats.get_prefs('PUBLISH') EST_PCT FROM dual;

-- View Publish preference for table 


-- View Publish preference for schema:

select dbms_stats.get_prefs('PUBLISH', 'SCOTT') from dual

-- View preference details for table

select dbms_stats.get_prefs(ownname=>'DBACLASS',tabname=>'EMP',pname=>'PUBLISH') FROM DUAL;
select  DBMS_STATS.get_prefs(ownname=>'DBACLASS',tabname=>'EMP',pname=>'INCREMENTAL') FROM DUAL;
select  DBMS_STATS.get_prefs(ownname=>'DBACLASS',tabname=>'EMP',pname=>'GRANULARITY') FROM DUAL;
select  DBMS_STATS.get_prefs(ownname=>'DBACLASS',tabname=>'EMP',pname=>'STALE_PERCENT')  FROM DUAL;
select   DBMS_STATS.get_prefs(ownname=>'DBACLASS',tabname=>'EMP',pname=>'ESTIMATE_PERCENT')  FROM DUAL;
select   DBMS_STATS.get_prefs(ownname=>'DBACLASS',tabname=>'EMP',pname=>'DEGREE')  FROM DUAL;


-- Set table  preferences

exec dbms_stats.set_table_prefs('DBACLASS','EMP','PUBLISH','FALSE');
exec dbms_stats.set_table_prefs('DBACLASS','EMP','ESTIMATE_PERCENT','20');
exec dbms_stats.set_table_prefs('DBACLASS','EMP','DEGREE','8');




-- Set schema preferences:

exec dbms_stats.SET_SCHEMA_PREFS('DBATEST','PUBLISH','FALSE');
exec dbms_stats.SET_SCHEMA_PREFS('DBATEST','ESTIMATE_PERCENT','20');
exec dbms_stats.SET_SCHEMA_PREFS('DBATEST','CASCADE','TRUE');


-- Set database preference:

exec dbms_stats.set_database_prefs('PUBLISH', 'TRUE');
exec dbms_stats.set_database_prefs('DEGREE', '16');


-- Set global preference:

exec dbms_stats.set_global_prefs('PUBLISH', 'TRUE');
exec dbms_stats.set_global_prefs('DEGREE', '16');

9 . Deleting preferences :

-- Deleting schema preference:

exec dbms_stats.delete_schema_prefs('DBACLASS', 'DEGREE');
exec dbms_stats.delete_schema_prefs('DBACLASS', 'CASCADE');


-- Delete database preference:
exec dbms_stats.delete_database_prefs('ESTIMATE_PERCENT', FALSE);
exec dbms_stats.delete_database_prefs('DEGREE', FALSE);

10 . Publish pending statistics:

-- For schema DBACLASS
exec dbms_stats.publish_pending_stats('DBACLASS',null);

-- For table DBACLASS.EMP
EXEC DBMS_STATS.PUBLISH_PENDING_STATS ('DBACLASS','EMP');

11. Delete pending statistics:

-- for table DBACLASS.EMP
exec dbms_stats.delete_pending_stats('DBACLASS', 'EMP');

-- For schema DBACLASS
exec dbms_stats.delete_pending_stats('DBACLASS', null);

12. Upgrade stats table:

----- If we are importing stats table from higher version to lower version,
then before importing in the database, we need to upgrade the stats table.


EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE(OWNNAME =>'RAJ',STATTAB =>'STAT_TEST');

13. View/modify statistics retention period:

-- View current stats retention

select dbms_stats.get_stats_history_retention from dual;

-- Modify the stats retention

exec DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(60);

14. create stats table:

--- Create staging table to store the statistics data

exec dbms_stats.create_stat_table(ownname => 'SCOTT', stattab => 'STAT_BACKUP',tblspace=>'USERS');

15. Export stats data:

-- Export full database stats to a table SCOTT.STAT_BACKUP

exec dbms_stats.export_database_stats(statown => 'SCOTT' ,stattab=>'STAT_BACKUP');

-- Export stats for table DBACLASS.EMP to a stats table SCOTT.STAT_BACKUP

exec dbms_stats.export_table_stats(ownname=>'DBACLASS', tabname=>'EMP', statown =>'SCOTT',stattab=>'STAT_BACKUP', cascade=>true);

-- Export stats for schema DBACLASS to a stats table SCOTT.STAT_BACKUP

exec dbms_stats.export_schema_stats(ownname=>'DBACLASS', statown =>'SCOTT' , stattab=>'STAT_BACKUP');

-- Export fixed object stats to table SCOTT.STAT_BACKUP

exec dbms_stats.export_fixed_objects_stats(statown => 'SCOTT' ,stattab=>'STAT_BACKUP');

-- Export dictionary stats to table SCOTT.STAT_BACKUP

exec dbms_stats.export_dictionary_stats(statown => 'SCOTT' ,stattab=>'STAT_BACKUP');

-- Export stats for index DBACLAS.EMP_UK1 to SCOTT.STAT_BACKUP table

exec dbms_stats.export_index_stats(ownname=>'DBACLASS', indname=>'EMP_UK1', statown =>'SCOTT',stattab=>'STAT_BACKUP');

16. Import stats table data:

-- Import full database stats from stats table SCOTT.STAT_BACKUP

exec dbms_stats.import_database_stats(statown => 'SCOTT' ,stattab=>'STAT_BACKUP');

-- Import stats for table DBACLASS.EMP from stats table SCOTT.STAT_BACKUP

exec dbms_stats.import_table_stats(ownname=>'DBACLASS', tabname=>'EMP', statown =>'SCOTT',stattab=>'STAT_BACKUP', cascade=>true);

-- Import stats for schema DBACLASS from  stats table SCOTT.STAT_BACKUP

exec dbms_stats.import_schema_stats(ownname=>'DBACLASS', statown =>'SCOTT' , stattab=>'STAT_BACKUP');

-- Import fixed object stats from stats table SCOTT.STAT_BACKUP

exec dbms_stats.import_fixed_objects_stats(statown => 'SCOTT' ,stattab=>'STAT_BACKUP');

-- Import dictionary stats from  table SCOTT.STAT_BACKUP

exec dbms_stats.import_dictionary_stats(statown => 'SCOTT' ,stattab=>'STAT_BACKUP');

-- Import stats for index DBACLAS.EMP_UK1 from  SCOTT.STAT_BACKUP table

exec dbms_stats.import_index_stats(ownname=>'DBACLASS', indname=>'EMP_UK1', statown =>'SCOTT',stattab=>'STAT_BACKUP');

17 . Few stats related sql queries:

-- Check stale stats for table:

select owner,table_name,STALE_STATS from dba_tab_statistics where owner='&SCHEMA_NAME' and table_name='&TABLE_NAME';

--Check stale stats for index:

select owner,INDEX_NAME,TABLE_NAME from DBA_IND_STATISTICS where owner='&SCHEMA_NAME' and index_name='&INDEX_NAME';


-- For getting history of TABLE statistics
setlines 200
col owner for a12
col table_name for a21
select owner,TABLE_NAME,STATS_UPDATE_TIME from dba_tab_stats_history where table_name='&TABLE_NAME';



-- Space used to store statistic data in SYSAUX tablespace:


SQL> select occupant_desc, space_usage_kbytes from v$sysaux_occupants where OCCUPANT_DESC like '%Statistics%';


-- Check whether table stats locked or not:

select owner, table_name, stattype_locked from dba_tab_statistics where stattype_locked is not null and owner not in ('SYS','SYSTEM');

The post Useful gather statistics commands in oracle appeared first on DBACLASS.

While importing a stats table in oracle 12c, for which the stats export was done from 11g , got below error. i.e both stats table was exported from a lower version and import attempt was done on higher version(12c).

SQL> exec dbms_stats.import_table_stats(ownname=>'RAJ', tabname=>'TEST',  stattab=>'STAT_TEST', cascade=>true);

ORA-20002: Version of statistics table SCOTT.STATS is too old. Please try upgrading it with dbms_stats.upgrade_stat_table
ORA-06512: at "SYS.DBMS_STATS", line 11476
ORA-06512: at "SYS.DBMS_STATS", line 11493
ORA-06512: at "SYS.DBMS_S>TATS", line 12628
ORA-06512: at line 1

SOLUTION:

If we are importing stats table from higher version to lower version, then before importing in the database, we need to upgrade the stats table.

UPGRADE STATS TABLE:

SQL> EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE('RAJ','STAT_TEST');

PL/SQL procedure successfully completed.

Now do the import:

 exec dbms_stats.import_table_stats(ownname=>'RAJ', tabname=>'TEST',  stattab=>'STAT_TEST', cascade=>true);

PL/SQL procedure successfully completed.

The post ORA-20002: Version of statistics table SCOTT.STATS is too old appeared first on DBACLASS.

Here i will take export of statistics of a table RAJ.TEST from PROD and import into TEST

DEMO:

create a table to store the stats:

--- RAJ is the owner of the stats table, STAT_TEST - name of the stats table

PROD> exec DBMS_STATS.CREATE_STAT_TABLE('RAJ','STAT_TEST','SYSAUX');

PL/SQL procedure successfully completed.

SQL> ;
  1* select owner,table_name from dba_tables where table_name='STAT_TEST'
SQL> /

OWNER	     TABLE_NAME
------------ ------------
RAJ	     STAT_TEST

SQL> 

Now export the statistics of the table RAJ.TEST to stats table ( STAT_TEST)

PROD> exec dbms_stats.export_table_stats(ownname=>'RAJ', tabname=>'TEST', stattab=>'STAT_TEST', cascade=>true);

PL/SQL procedure successfully completed.

Now take expdp of this stats table(STAT_TEST)

[oracle@PROD]$ expdp dumpfile=stats.dmp logfile=stats.log tables=raj.STAT_TEST directory=DIR1

Export: Release 12.1.0.2.0 - Production on Fri Aug 28 10:09:26 2015

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Username: raj/raj@orcl

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "RAJ"."SYS_EXPORT_TABLE_01":  raj/********@orcl dumpfile=stats.dmp logfile=stats.log tables=raj.STAT_TEST directory=DIR1 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "RAJ"."STAT_TEST"                           19.24 KB      19 rows
Master table "RAJ"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for RAJ.SYS_EXPORT_TABLE_01 is:
  /home/oracle/DIR1/stats.dmp
Job "RAJ"."SYS_EXPORT_TABLE_01" successfully completed at Fri Aug 28 10:12:33 2015 elapsed 0 00:03:00

Move the dump file from PROD to TEST machine and import the same

[oracle@TEST]$ impdp dumpfile=stats.dmp logfile=stats.log tables=raj.STAT_TEST directory=DIR1 table_exists_action=REPLACE

Import: Release 12.1.0.2.0 - Production on Fri Aug 28 10:12:42 2015

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Username: raj/raj@orcl

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "RAJ"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "RAJ"."SYS_IMPORT_TABLE_01":  raj/********@orcl dumpfile=stats.dmp logfile=stats.log tables=raj.STAT_TEST directory=DIR1 table_exists_action=REPLACE 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "RAJ"."STAT_TEST"                           19.24 KB      19 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "RAJ"."SYS_IMPORT_TABLE_01" successfully completed at Fri Aug 28 10:14:36 2015 elapsed 0 00:01:42

Now import the statistics in database:

SQL> set lines 200
SQL> set pagesize 200
SQL> col table_name for a12
SQL> col owner for a12
SQL> select owner,table_name,last_analyzed from dba_tables where table_name='TEST';

OWNER	     TABLE_NAME   LAST_ANAL
------------ ------------ ---------
RAJ	     TEST	  05-AUG-15


SQL> exec dbms_stats.import_table_stats(ownname=>'RAJ', tabname=>'TEST',  stattab=>'STAT_TEST', cascade=>true);

PL/SQL procedure successfully completed.

SQL> select owner,table_name,last_analyzed from dba_tables where table_name='TEST';

OWNER	     TABLE_NAME   LAST_ANAL
------------ ------------ ---------
RAJ	     TEST	  28-AUG-15

We can observe after import_table_stats, last_analyzed date has been updated.

We can do export/import statistics at schema level and database level:

For database :

----For export
 exec dbms_stats.EXPORT_DATABASE_STATS('','','');

--- For import
 exec dbms_stats.IMPORT_DATABASE_STATS('','','');

For schema:

--- For export
exec  dbms_stats.export_schema_stats( ownname=>, stattab=>'', statid=>'' );

--- For import
exec  dbms_stats.import_schema_stats( ownname=>, stattab=>'', statid=>'' );

The post How to export and import statistics in oracle appeared first on DBACLASS.

So what we can do it, first gather the stats of the table or schema,without publishing it. After testing, once we confirm that stats is not degrading the performance,Then we can go ahead and publish the stats.

DEMO:

First change the publish mode, so that stats won’t be published by default

SQL> select dbms_stats.get_prefs('PUBLISH', 'SCOTT', 'EMP_TAB' ) FROM DUAL;

DBMS_STATS.GET_PREFS('PUBLISH','SCOTT','EMP_TAB')
--------------------------------------------------------------------------------
TRUE

SQL> exec dbms_stats.set_table_prefs('SCOTT','EMP_TAB','PUBLISH','FALSE');

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_prefs('PUBLISH', 'SCOTT', 'EMP_TAB' ) FROM DUAL;

DBMS_STATS.GET_PREFS('PUBLISH','SCOTT','EMP_TAB')
--------------------------------------------------------------------------------
FALSE

Now gather stats:

SQL> select sysdate from dual;

SYSDATE
---------
28-AUG-15

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','EMP_TAB',degree=> 4 ,METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO');

PL/SQL procedure successfully completed.

Check the last_analyzed date in dba_tables and DBA_TAB_PENDING_STATS

  1* select owner,table_name,last_analyzed from dba_tables where table_name='EMP_TAB'
SQL> /

OWNER	    TABLE_NAME LAST_ANAL
----------- ---------- ---------
SCOTT	    EMP_TAB       05-AUG-15


SQL> SELECT TABLE_NAME,LAST_ANALYZED  FROM DBA_TAB_PENDING_STATS;

TABLE_NAME LAST_ANAL
---------- ---------
EMP_TAB	   28-AUG-15

We can see the last_analyzed date in dba_tables is old one , but it is showing as pending one in dba_tab_pending_stats.

Now we can test the whether stats are good or not at the session level by enabling below parameter.

alter session set optimizer_use_pending_statistics=TRUE;

Session altered.

You can run query in this session, test whether query performance is fine or not.
If  you can’t run the queries in this session, then another way is to  export the stats and import the same in your clone of the production database(PRE-PROD) , and test the queries.

Check :How to export and import stats in oracle :

Once testing is successful , if you decided to publish the stats, then

Publish pending statistics:

SQL> EXEC DBMS_STATS.PUBLISH_PENDING_STATS ('SCOTT','EMP_TAB');

PL/SQL procedure successfully completed.

SQL> select owner,TABLE_NAME,LAST_ANALYZED  FROM DBA_TAB_PENDING_STATS;


no rows selected
SQL> set lines 200
SQL> set pagesize 200
SQL> col table_name for a12
SQL> col owner for a12
SQL> select owner,table_name,last_analyzed from dba_tables where table_name='EMP_TAB';

OWNER	     TABLE_NAME   LAST_ANAL
------------ ------------ ---------
SCOTT	     EMP_TAB	  28-AUG-15

We can see there are no pending statistics.

The post Gather stats and Publish them later in oracle appeared first on DBACLASS.