================================================================ -- Hands-on-exercises - Statitiscs -- -- 1.- When no statistics are generated, the RBO will be used -- 2.- Gather statistics for table ORD -- 3.- Explain Plan for query - after statistics for table ORD -- 4.- Veryfying Table Statistics -- 5.- Gather statistics for complete schema including indexes -- 6.- Explain Plan for query - with schema statistics -- 7.- Verifying Index Statistics -- 8.- Delete statistics -- 9.- Generate statistics regularly if data or values changed in your tables. ================================================================================ SQL> REM @$ORACLE_HOME/rdbms/admin/utlxplan.sql alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS'; set pagesize 100; set linesize 110; ================================================================== 1.- If no statistics, then the Rule Based Optimizer (RBO) is used. ------------------------------------------------------------------ SQL> select table_name, last_analyzed from user_tables; TABLE_NAME LAST_ANALYZ ------------------------------ ----------- BONUS CUSTOMER DEPT DUMMY EMP ITEM ORD PRICE PRODUCT SALGRADE 10 rows selected. SQL> truncate table plan_table; Table truncated. SQL> 1 explain plan for 2 select c.custid, i.itemid 3 from customer c, ord o, item i 4 where c.custid=o.custid and 5* o.ordid=i.ordid SQL>/ Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | | 1 | NESTED LOOPS | | | | | | 2 | NESTED LOOPS | | | | | | 3 | TABLE ACCESS FULL | ITEM | | | | | 4 | TABLE ACCESS BY INDEX ROWID| ORD | | | | |* 5 | INDEX UNIQUE SCAN | ORD_ORDID_PK | | | | |* 6 | INDEX UNIQUE SCAN | CUSTOMER_CUSTID_PK | | | | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("O"."ORDID"="I"."ORDID") 6 - access("C"."CUSTID"="O"."CUSTID") Note: rule based optimization 20 rows selected. ================================================================== 2.- Gather statistics for table ORD ------------------------------------- SQL> execute DBMS_STATS.ALTER_SCHEMA_TAB_MONITORING; PL/SQL procedure successfully completed. ** DOC: Gather statistics for a table DBMS_STATS.GATHER_TABLE_STATS ( ownname VARCHAR2, tabname VARCHAR2, partname VARCHAR2 DEFAULT NULL, estimate_percent NUMBER DEFAULT NULL, block_sample BOOLEAN DEFAULT FALSE, method_opt VARCHAR2 DEFAULT 'FOR ALL COLUMNS SIZE 1', degree NUMBER DEFAULT NULL, granularity VARCHAR2 DEFAULT 'DEFAULT', cascade BOOLEAN DEFAULT FALSE, stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT FALSE); ********************************************** SQL> execute dbms_stats.gather_table_stats(ownname=>NULL, tabname=>'ORD' , estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt=>'FOR COLUMNS SIZE AUTO'); PL/SQL procedure successfully completed. SQL> select table_name, last_analyzed from user_tables; TABLE_NAME LAST_ANALYZ ------------------------------ ----------- BONUS CUSTOMER DEPT DUMMY EMP ITEM ORD 12-JAN-2005 PLAN_TABLE PRICE PRODUCT SALGRADE 11 rows selected. ================================================================== 3.- Explain Plan for query - after statistics for table ORD ------------------------------------------------------------- SQL> truncate table plan_table; Table truncated. SQL> 1 explain plan for 2 select c.custid, i.itemid 3 from customer c, ord o, item i 4 where c.custid=o.custid and 5* o.ordid=i.ordid SQL> / Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 669 | 43485 | 5 | | 1 | NESTED LOOPS | | 669 | 43485 | 5 | | 2 | NESTED LOOPS | | 27 | 1404 | 5 | |* 3 | TABLE ACCESS FULL | ORD | 1 | 26 | 4 | |* 4 | INDEX RANGE SCAN | ITEM_ORDID_ITEMID_PK | 25 | 650 | 1 | |* 5 | INDEX UNIQUE SCAN | CUSTOMER_CUSTID_PK | 25 | 325 | | ------------------------------------------------------------------------------ PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("O"."CUSTID">0) 4 - access("O"."ORDID"="I"."ORDID") 5 - access("C"."CUSTID"="O"."CUSTID") Note: cpu costing is off 20 rows selected. ============================================================ 4.- Veryfying Table Statistics ------------------------------- SQL> select table_name, num_rows, blocks, avg_row_len, last_analyzed from user_tables; TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED ------------------------------ ---------- ---------- ----------- -------------------- BONUS CUSTOMER DEPT DUMMY EMP ITEM ORD 21 13 29 13-JAN-2005 17:08:13 PLAN_TABLE PRICE PRODUCT SALGRADE 11 rows selected. ============================================================ 5.- Gather statistics for complete schema including indexes ------------------------------------------------------------ ** DOC: Gather statistics for complete schema DBMS_STATS.GATHER_SCHEMA_STATS ( ownname VARCHAR2, estimate_percent NUMBER DEFAULT NULL, block_sample BOOLEAN DEFAULT FALSE, method_opt VARCHAR2 DEFAULT 'FOR ALL COLUMNS SIZE 1', degree NUMBER DEFAULT NULL, granularity VARCHAR2 DEFAULT 'DEFAULT', cascade BOOLEAN DEFAULT FALSE, stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, options VARCHAR2 DEFAULT 'GATHER', statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT FALSE, gather_temp BOOLEAN DEFAULT FALSE); *********************************************** SQL> execute dbms_stats.gather_schema_stats(ownname=>NULL, estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt=> 'FOR COLUMNS SIZE AUTO', cascade=> TRUE, options=>'GATHER AUTO'); PL/SQL procedure successfully completed. SQL> select table_name, last_analyzed from user_tables; TABLE_NAME LAST_ANALYZED ------------------------------ -------------------- BONUS 13-JAN-2005 17:09:05 CUSTOMER 13-JAN-2005 17:09:05 DEPT 13-JAN-2005 17:09:05 DUMMY 13-JAN-2005 17:09:05 EMP 13-JAN-2005 17:09:06 ITEM 13-JAN-2005 17:09:06 ORD 13-JAN-2005 17:08:13 PLAN_TABLE 13-JAN-2005 17:09:06 PRICE 13-JAN-2005 17:09:06 PRODUCT 13-JAN-2005 17:09:07 SALGRADE 13-JAN-2005 17:09:07 ============================================================ 6.- Explain Plan for query - with schema statistics ---------------------------------------------------- SQL> truncate table plan_table; SQL> 1 explain plan for 2 select c.custid, i.itemid 3 from customer c, ord o, item i 4 where c.custid=o.custid and 5* o.ordid=i.ordid SQL> / Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 3 | 111 | 5 | | 1 | NESTED LOOPS | | 3 | 111 | 5 | | 2 | NESTED LOOPS | | 1 | 30 | 4 | |* 3 | TABLE ACCESS FULL | ORD | 1 | 26 | 4 | |* 4 | INDEX UNIQUE SCAN | CUSTOMER_CUSTID_PK | 1 | 4 | | |* 5 | INDEX RANGE SCAN | ITEM_ORDID_ITEMID_PK | 3 | 21 | 1 | ------------------------------------------------------------------------------ PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("O"."CUSTID">0) 4 - access("C"."CUSTID"="O"."CUSTID") 5 - access("O"."ORDID"="I"."ORDID") Note: cpu costing is off 20 rows selected. ============================================================ 7.- Verifying Index Statistics ------------------------------- SQL> 1 select index_name, num_rows, distinct_keys, leaf_blocks, 2 clustering_factor "CF", blevel, avg_leaf_blocks_per_key "ALBPKEY" 3 from user_indexes; INDEX_NAME NUM_ROWS DISTINCT_KEYS LEAF_BLOCKS CF BLEVEL ALBPKEY ------------------------- ---------- ------------- ----------- ---------- ---------- ---------- CUSTOMER_CUSTID_PK 9 9 1 1 0 1 DEPT_PRIMARY_KEY 4 4 1 1 0 1 EMP_EMPNO_PK 14 14 1 1 0 1 ITEM_ORDID_ITEMID_PK 64 64 1 1 0 1 ORD_ORDID_PK 21 21 1 1 0 1 PRICE_INDEX 17 17 1 1 0 1 PRODID_PK 10 10 1 1 0 1 STATS 0 0 0 0 0 0 ============================================================ 8.- Delete Statistics ------------------------------- SQL> execute dbms_stats.delete_schema_stats(ownname=>NULL); =========================================================================== 9.- Generate statistics regularly if data or values changed in your tables --------------------------------------------------------------------------- VARIABLE jobno number; BEGIN DBMS_JOB.SUBMIT(:jobno, 'dbms_stats.gather_schema_stats(ownname=>NULL, estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE, cascade=> TRUE, options=>''GATHER AUTO'');', SYSDATE+5/1440, 'SYSDATE + 1'); COMMIT; END; / SQL> select job, last_date, next_date, broken, failures from user_jobs; JOB LAST_DATE NEXT_DATE B FAILURES ---------- --------------------- --------------------- - ---------- 1 13-JAN-2005 15:08:31 14-JAN-2005 15:08:31 N 0 SQL> execute dbms_job.remove(1);