Category Archives: statistics

How to Move From ANALYZE DBMS_STATS

How to Move From ANALYZE/DBMS_UTILITY.ANALYZE_* to DBMS_STATS ============================================================= Cost-Based optimization relies on accurate statistics to determine the relative cost of execution plans. In Oracle 7, there was two methods to gather statistics: o ANALYZE o DBMS_UTILITY Since the Oracle Version … Continue reading

Posted in statistics | Leave a comment

How To Run DBMS_STATS.GATHER_TABLE_STATS Using Dynamic Query

Connect as scott, scott has DBA and ANALYSE ANY privileges create or replace procedure getstattest IS str1 varchar2(1000); cnt NUMBER; CURSOR c_C1 IS select TABLE_NAME, OWNER from dba_tables where OWNER IN (‘SCOTT’) and TABLE_NAME IN (‘EMP’, ‘DEPT’,’BONUS’,’SALGRADE’); begin FOR tab_rec … Continue reading

Posted in statistics | Leave a comment

How to Backup and Restore Statistics

Backup Optimizer Statistics Step Checklist: 1. Create a statistics table in the ¬†User schema 2. Transfer the statistics to this table Step detail: ———— 1. Create a statistics table in the user schema : User is the owner of the … Continue reading

Posted in statistics | Leave a comment

How to check automatic statistics collection is scheduled on 10g

With Oracle 10g, the gathering of statistics has become automated. The GATHER_STATS_JOB that is built in the database creation process schedules automatic statistics collection. The job initiates a ‘program’ of statistics gathering appropriate for the database in question. The job … Continue reading

Posted in statistics, Uncategorized | Leave a comment