PT - Statistics Gathering Optimizer Table Example

PT -  Statistics Gathering Optimizer Table  Example 

• Checking current  analyzed status of table 

SQL> SELECT NUM_ROWS, BLOCKS, LAST_ANALYZED  FROM DBA_TAB_STATISTICS WHERE OWNER='SOE' AND TABLE_NAME='EMP';

  NUM_ROWS     BLOCKS LAST_ANAL

---------- ---------- ---------

       879         11 31-JUL-19

• Table created from existing table

SQL> CREATE TABLE SOE.EMP2 NOLOGGING AS SELECT * FROM SOE.EMP;

Table created.

• Checking Analyzed status

SQL>  SELECT NUM_ROWS, BLOCKS, LAST_ANALYZED FROM DBA_TAB_STATISTICS WHERE OWNER='SOE' AND TABLE_NAME='EMP2';

  NUM_ROWS     BLOCKS LAST_ANAL

---------- ---------- ---------

       879         10 05-MAY-20

• New  table created EMP3

SQL> CREATE TABLE SOE.EMP3 ( EMP_NO  NUMBER(4), ENAME  VARCHAR2(20),     HIRE_DATE  DATE, DEPT_NO  NUMBER(2),     JOB_CODE  CHAR(4), SALARY  NUMBER(8,2),     MGR_ID  NUMBER(4), TERMINATED  CHAR(1), NOTES  VARCHAR2(1000));

Table created.

• Checking Analysed status 

SQL> SELECT NUM_ROWS, BLOCKS, LAST_ANALYZED FROM DBA_TAB_STATISTICS WHERE OWNER='SOE' AND TABLE_NAME='EMP3';

  NUM_ROWS     BLOCKS LAST_ANAL

---------- ---------- ---------

• Checking the Trace status 

SQL> SET LINESIZE 180

SQL> SET AUTOT TRACE EXP

SQL> SELECT * FROM SOE.EMP3 WHERE EMP_NO=641;

Execution Plan

----------------------------------------------------------

Plan hash value: 2425169977

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |   584 |     2   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| EMP3 |     1 |   584 |     2   (0)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("EMP_NO"=641)

Note

-----

   - dynamic statistics used: dynamic sampling (level=2)

SQL>

• Checking the status 

SQL> SET AUTOT OFF

SQL> SELECT NUM_ROWS, BLOCKS, LAST_ANALYZED FROM DBA_TAB_STATISTICS WHERE OWNER='SOE' AND TABLE_NAME='EMP3';

  NUM_ROWS     BLOCKS LAST_ANAL

---------- ---------- ---------

SQL>

• Insert the values from existing table emp

SQL> INSERT INTO SOE.EMP3 SELECT * FROM SOE.EMP;

879 rows created.

SQL> COMMIT;

Commit complete.

• Check the analysed status of table emp3

SQL> SELECT NUM_ROWS, BLOCKS, LAST_ANALYZED FROM DBA_TAB_STATISTICS WHERE OWNER='SOE' AND TABLE_NAME='EMP3';

  NUM_ROWS     BLOCKS LAST_ANAL

---------- ---------- ---------

SQL>

• Create unique index on EMP3 table 

SQL> CREATE UNIQUE INDEX SOE.EMP2_EMPNO_UQ   ON SOE.EMP3(EMP_NO) NOLOGGING TABLESPACE SOETBS;

Index created.

• Checking index compute status

SQL> SELECT BLEVEL, LEAF_BLOCKS AS "LEAFBLK", DISTINCT_KEYS AS "DIST_KEY", AVG_LEAF_BLOCKS_PER_KEY AS "LEAFBLK_PER_KEY", AVG_DATA_BLOCKS_PER_KEY AS "DATABLK_PER_KEY" FROM DBA_IND_STATISTICS WHERE OWNER = 'SOE' AND INDEX_NAME = 'EMP2_EMPNO_UQ';

no rows selected

SQL>

SQL>

• Checking the analysed status

SQL> SELECT NUM_ROWS, BLOCKS, LAST_ANALYZED FROM DBA_TAB_STATISTICS WHERE OWNER='SOE' AND TABLE_NAME='EMP3';

  NUM_ROWS     BLOCKS LAST_ANAL

---------- ---------- ---------

SQL>

• Explain plan of query

SQL> SET LINESIZE 180

SQL> SET AUTOT TRACE EXP

SQL> SELECT * FROM SOE.EMP3 WHERE EMP_NO=641;

Execution Plan

----------------------------------------------------------

Plan hash value: 2136555587

---------------------------------------------------------------------------------------------

| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |               |     1 |   584 |     1   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP3          |     1 |   584 |     1   (0)| 00:00:01 |

|*  2 |   INDEX UNIQUE SCAN         | EMP2_EMPNO_UQ |     1 |       |     1   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("EMP_NO"=641)

SQL>

• Gather stats of Table EMP3

SQL> exec DBMS_STATS.GATHER_TABLE_STATS('SOE','EMP3');

PL/SQL procedure successfully completed.

• Checking analysed status of table emp3 after stats gathering

SQL> set autot off

SQL> SELECT NUM_ROWS, BLOCKS, LAST_ANALYZED FROM DBA_TAB_STATISTICS WHERE OWNER='SOE' AND TABLE_NAME='EMP3';

  NUM_ROWS     BLOCKS LAST_ANAL

---------- ---------- ---------

       879         13 05-MAY-20

SQL>

SQL> DROP TABLE SOE.EMP2;

Table dropped.

SQL> DROP TABLE SOE.EMP3;

Table dropped.

SQL>