PT - Gathering Optimizer Statistics Manually Table examples with stats and explain plan

Gathering Optimizer Statistics Manually Table examples with stats and explain plan

• Create TEST1 table

SQL> CREATE TABLE test.test1 (  ORDER_ID   NUMBER(12),  ORDER_DATE  TIMESTAMP(6) WITH LOCAL TIME ZONE,  ORDER_MODE  VARCHAR2(8),  CUSTOMER_ID   NUMBER(12),  ORDER_STATUS  NUMBER(2),  ORDER_TOTAL  NUMBER(8,2),  SALES_REP_ID  NUMBER(6),  NOTES   VARCHAR2(15));

Table created.

• Check table analyzed status of table (dba_table)

SQL> SELECT NUM_ROWS, BLOCKS, LAST_ANALYZED FROM USER_TAB_STATISTICS WHERE TABLE_NAME='TEST1';

  NUM_ROWS     BLOCKS LAST_ANAL

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

SQL>

• Insert values on table for testing purpose

SQL> SET AUTOT OFF

SQL>

SQL> INSERT INTO TEST1 SELECT ORDER_ID, ORDER_DATE, ORDER_MODE, CUSTOMER_ID, ORDER_STATUS, ORDER_TOTAL, SALES_REP_ID, DBMS_RANDOM.STRING('a',ROUND(DBMS_RANDOM.VALUE(5,15))) FROM ORDERS fetch first 30000 rows only;

40000 rows created.

SQL>  COMMIT;

Commit complete.

• Check the status of analyzed table

SQL> SELECT NUM_ROWS, BLOCKS, LAST_ANALYZED FROM USER_TAB_STATISTICS WHERE TABLE_NAME='TEST1';

  NUM_ROWS     BLOCKS LAST_ANAL

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

SQL>

• Stats gathering of table table TEST1

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'TEST', TABNAME=>'TEST1');

PL/SQL procedure successfully completed.

• Check the status of stats gather

SQL> SELECT NUM_ROWS, BLOCKS, LAST_ANALYZED  FROM USER_TAB_STATISTICS WHERE TABLE_NAME='TEST1';

  NUM_ROWS     BLOCKS LAST_ANAL

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

     30000        370 11-MAY-20

SQL>

• Delete TEST1 table values 

SQL> DELETE TEST1 WHERE MOD(ORDER_ID,2)=0;

19999 rows deleted.

SQL> COMMIT;

Commit complete.

• Check the status of stats gather

SQL> SELECT NUM_ROWS, BLOCKS, LAST_ANALYZED FROM USER_TAB_STATISTICS WHERE TABLE_NAME='TEST1';

  NUM_ROWS     BLOCKS LAST_ANAL

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

     30000        370 11-MAY-20

SQL>

• Check the execution plan and statistics of query

set autot on 

SELECT * FROM TEST1 WHERE ORDER_ID BETWEEN 30000 AND 50000; 

3753 rows selected.

Execution Plan

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

Plan hash value: 4122059633

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

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

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

|   0 | SELECT STATEMENT  |       | 12409 |   545K|   103   (1)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| TEST1 | 12409 |   545K|   103   (1)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - filter("ORDER_ID"<=30000 AND "ORDER_ID">=50000)

Statistics

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

         15  recursive calls

         36  db block gets

        573  consistent gets

          0  physical reads

       7116  redo size

     199509  bytes sent via SQL*Net to client

       3358  bytes received via SQL*Net from client

        252  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

       3753  rows processed

• STATS gather of table TEST1 again

SQL> set autot off

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'TEST', TABNAME=>'TEST1');

PL/SQL procedure successfully completed.

• Check analyzed status of table TEST1

SQL> SELECT NUM_ROWS, BLOCKS, LAST_ANALYZED FROM USER_TAB_STATISTICS WHERE TABLE_NAME='TEST1';

  NUM_ROWS     BLOCKS LAST_ANAL

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

     20001        370 11-MAY-20

SQL>

• Execute Query Again and explain plan and statistics status

set autot on 

SELECT * FROM ORDERS2 WHERE ORDER_ID BETWEEN 30000 AND 50000;

3753 rows selected.

Execution Plan

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

Plan hash value: 4122059633

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

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

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

|   0 | SELECT STATEMENT  |       |  3720 |   163K|   102   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| TEST1 |  3720 |   163K|   102   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - filter("ORDER_ID"<=30000 AND "ORDER_ID">=50000)

Statistics

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

          0  recursive calls

          0  db block gets

        566  consistent gets

          0  physical reads

          0  redo size

     199509  bytes sent via SQL*Net to client

       3358  bytes received via SQL*Net from client

        252  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

       3753  rows processed

SQL>