1. Purpose
This script is an automated way to deal with stale
statistics operations that are required to be done as part DBA activity or time of manual upgrade.
Operations of this script: This script will be primarily used during database upgrade before starting the actual upgrade. Execution of this script will report stale statistics if any. It reports on schemas which are part of DBA_REGISTRY as these are the ones which are upgraded. If it finds any stale statistics it will give the recommended commands to remove those stale statistics.
2. scenario
Stale data is one, in which an object in the cache is not the most recent version committed to the data source. Oracle gathers statistics on all database objects automatically and maintains those statistics in a regularly-scheduled maintenance job. Statistics can get stale during the day because of frequent DML modifications. If there are stale statistics in the database there will be chances of getting poor execution plans.
When we are gathering statistic for all objects of particular schema or complete database, Oracle build new execution plan for the entire Database object which makes the database to perform slow.
3. Software Requirements/Prerequisites
The Script needs to be run in SQL*Plus as SYS user to find the schema with stale statistics.
4. Solution
To solve the issue instead of gathering complete database statistics we are gathering statistics of the culprit tables only in a schema. So, there will be less or no performance issue.
Step 1: Connect as a SYS in the database and check the DB status
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
——— ———-
ORCL READ WRITE
1 row selected.
Step 2: Create a table for storing the stale object in the schema in SCOTT schema. If SCOTT schema does not exists in database you can create the table in other schema also. But change the “SCHEMA NAME.STALE_OBJECT” in the “SHOW_STALE_OBJECT_PROC.sql” table.
Run the below script.
CREATE TABLE STALE_OBJECT
(
OWNER VARCHAR2(30 BYTE),
OBJECT_NAME VARCHAR2(128 BYTE),
OBJECT_TYPE VARCHAR2(18 BYTE),
STATUS VARCHAR2(7 BYTE),
TEMPORARY VARCHAR2(1 BYTE)
)
TABLESPACE TOOLS
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;
SQL> conn scott/tiger
Connected.
SQL> @c:stale_object_tbl.sql
Table created.
Step 3: Create a procedure to store stale object of a schema from SYS. Modify the schema name inside according to require schema for which you need to find the stale object statistics. Here we are using it on HR user (example schema in Oracle database)
CREATE OR REPLACE PROCEDURE SHOW_STALE_OBJ
AS
— VARIABLES DECLARED
P_OTAB DBMS_STATS.OBJECTTAB;
MCOUNT NUMBER := 0;
P_VERSION VARCHAR2(10);
— CURSOR DEFINED
CURSOR C1
IS
SELECT USERNAME SCHEMA
FROM SYS.DBA_USERS
WHERE USERNAME=’HR’;
— BEGINNING OF THE ANONYMOUS BLOCK
BEGIN
— VERIFYING VERSION FROM V$INSTANCE
–SELECT VERSION INTO P_VERSION FROM V$INSTANCE;
DBMS_OUTPUT.PUT_LINE(CHR(13));
— DEFINING LOOP 1 FOR LISTING SCHEMA WHICH HAVE STALE STATS
FOR X IN C1
LOOP
DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>X.SCHEMA,OPTIONS=>’LIST AUTO’,OBJLIST=>P_OTAB);
— DEFINING LOOP 2 TO FIND NUMBER OF OBJECTS CONTAINING STALE STATS
FOR I IN 1 .. P_OTAB.COUNT
LOOP
IF P_OTAB(I).OBJNAME NOT LIKE ‘SYS_%’
AND P_OTAB(I).OBJNAME NOT IN (‘CLU$’,’COL_USAGE$’,’FET$’,’INDPART$’,
‘MON_MODS$’,’TABPART$’,’HISTGRM$’,
‘MON_MODS_ALL$’,
‘HIST_HEAD$’,’IN $’,’TAB$’,
‘WRI$_OPTSTAT_OPR’,’PUIU$DATA’,
‘XDB$NLOCKS_CHILD_NAME_IDX’,
‘XDB$NLOCKS_PARENT_OID_IDX’,
‘XDB$NLOCKS_RAW OKEN_IDX’, ‘XDB$SCHEMA_URL’,
‘XDBHI_IDX’, ‘XDB_PK_H_LINK’)
THEN
— INCREMENTING COUNT FOR EACH OBJECT FOUND WITH STATLE STATS
MCOUNT := MCOUNT + 1;
INSERT INTO SCOTT.STALE_OBJECT(OWNER, OBJECT_NAME, OBJECT_TYPE) VALUES(X.SCHEMA, P_OTAB(I).OBJNAME, P_OTAB(I).OBJTYPE);
END IF;
— END OF LOOP 2
END LOOP;
COMMIT;
— DISPLAYS NO STALE STATISTICS, IF COUN IS 0
— DISPLAYS SCHEMA WITH STALE STATS IF COUNT IS GREATER THAN 0
— IF MCOUNT !=0 THEN
DBMS_OUTPUT.PUT_LINE(‘——————————————————————————————————-‘);
DBMS_OUTPUT.PUT_LINE(‘– ‘ X.SCHEMA ‘ schema contains ‘ MCOUNT ‘ stale statistics use the following to gather the statistics ”–‘);
DBMS_OUTPUT.PUT_LINE(‘——————————————————————————————————-‘);
— DBMS_OUTPUT.PUT_LINE(”);
— DISPLAYS COMMAND TO BE EXECUTED IF SCHEMA WITH STALE STATISTICS IS FOUND DEPENDING ON THE VERSION.
— IF SUBSTR(P_VERSION,1,5) IN (‘8.1.7′,’9.0.1′,’9.2.0’)
— THEN
— DBMS_OUTPUT.PUT_LINE(CHR(13));
— DBMS_OUTPUT.PUT_LINE(‘EXEC DBMS_STATS.GATHER_SCHEMA_STATS(”’X.SCHEMA”’,OPTIONS=>””GATHER””, ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => ””FOR ALL COLUMNS SIZE AUTO””, CASCADE => TRUE);’);
— ELSIF SUBSTR(P_VERSION,1,6) IN (‘10.1.0′,’10.2.0′,’11.1.0’)
— THEN
— DBMS_OUTPUT.PUT_LINE(CHR(13));
— DBMS_OUTPUT.PUT_LINE(‘EXEC DBMS_STATS.GATHER_DICTIONARY_STATS(”’X.SCHEMA”’,OPTIONS=>””GATHER””, ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => ””FOR ALL COLUMNS SIZE AUTO””, CASCADE => TRUE);’);
— ELSE
— DBMS_OUTPUT.PUT_LINE(CHR(13));
— DBMS_OUTPUT.PUT_LINE(‘Version is ‘P_VERSION);
— END IF;
— ELSE
— DBMS_OUTPUT.PUT_LINE(‘– There are no stale statistics in ‘ X.SCHEMA ‘ schema.’);
— DBMS_OUTPUT.PUT_LINE(CHR(13));
— END IF;
— RESET COUNT TO 0.
MCOUNT := 0;
— END OF LOOP 1
END LOOP;
END SHOW_STALE_OBJ;
/
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> show user
USER is “SYS”
SQL> @c:SHOW_STALE_OBJECT_PROC.sql
Procedure created.
Step 4: Run the procedure.
SQL> EXECUTE SHOW_STALE_OBJ
PL/SQL procedure successfully completed.
Step 5: Check the STALE_OBJECT table for any stale object. It will return rows if there is stale object in the schema.
SQL> SQL> select a.OBJECT_NAME,a.OBJECT_TYPE,b.table_name from scott.STALE_OBJECT a,dba_indexes b where a.OWNER=’HR’and a.object_name=b.index_name;
OBJECT_NAME table_name OBJECT_TYPE
————————- —— ——–
DEPT_ID_PK DEPARTMENTS INDEX
DEPT_LOCATION_IX DEPARTMENTS INDEX
EMP_DEPARTMENT_IX EMPLOYEES INDEX
EMP_EMAIL_UK EMPLOYEES INDEX
EMP_EMP_ID_PK EMPLOYEES INDEX
EMP_JOB_IX EMPLOYEES INDEX
EMP_MANAGER_IX EMPLOYEES INDEX
EMP_NAME_IX EMPLOYEES INDEX
JHIST_DEPARTMENT_IX JOB_HISTORY INDEX
JHIST_EMPLOYEE_IX JOB_HISTORY INDEX
JHIST_EMP_ID_ST_DATE_PK JOB_HISTORY INDEX
JHIST_JOB_IX JOB_HISTORY INDEX
JOB_ID_PK JOBS INDEX
LOC_CITY_IX LOCATIONS INDEX
LOC_COUNTRY_IX LOCATIONS INDEX
LOC_ID_PK LOCATIONS INDEX
LOC_STATE_PROVINCE_IX LOCATIONS INDEX
REG_ID_PK REGIONS INDEX
18 rows selected.
Step 6: Once you found stale object in the schema, rebuild the statistics for those stale objects only instead of rebuilding for the entire object in the schema. Gather stats for the table including index
SQL> exec dbms_stats.gather_table_stats(‘HR’,’EMPLOYEES’,cascade=>true,degree=>4
,estimate_percent=>20)
PL/SQL procedure successfully completed
Step 7: Truncate the table.
SQL> truncate table scott.STALE_OBJECT;
Table truncated.
Step 8: Run the procedure again.
SQL> EXECUTE SHOW_STALE_OBJ
PL/SQL procedure successfully completed.
Step 9: Check the STALE_OBJECT table for any stale object. It will return rows of other table objects except employee. It won’t return any stale object from employee table.
SQL> select a.OBJECT_NAME,a.OBJECT_TYPE from scott.STALE_OBJECT a,dba_indexes b where a.OWNER=’HR’and b.table_name=’EMPLOYEES’ and a.object_name=b.index_name;
no rows selected
Other stale object in HR
SQL> select a.OBJECT_NAME,b.table_name,a.OBJECT_TYPE from scott.STALE_OBJECT a,d
ba_indexes b where a.OWNER=’HR’and a.object_name=b.index_name;
OBJECT_NAME TABLE_NAME OBJECT_TYPE
————————– —————————— ————
DEPT_ID_PK DEPARTMENTS INDEX
DEPT_LOCATION_IX DEPARTMENTS INDEX
JHIST_DEPARTMENT_IX JOB_HISTORY INDEX
JHIST_EMPLOYEE_IX JOB_HISTORY INDEX
JHIST_EMP_ID_ST_DATE_PK JOB_HISTORY INDEX
JHIST_JOB_IX JOB_HISTORY INDEX
JOB_ID_PK JOBS INDEX
LOC_CITY_IX LOCATIONS INDEX
LOC_COUNTRY_IX LOCATIONS INDEX
LOC_ID_PK LOCATIONS INDEX
LOC_STATE_PROVINCE_IX LOCATIONS INDEX
REG_ID_PK REGIONS INDEX
12 rows selected.
Do the same activity from step 6 to eliminate stale object from the HR schema.
5. References and Links
1 https://metalink.oracle.com doc id: Note:560336.1