PT - Optimizer Statistics Collection Automatic and Manual examples

Automatic Optimizer Statistics Collection 

To check internal task details :

QUERY:

SELECT * FROM DBA_AUTOTASK_CLIENT WHERE CLIENT_NAME='auto optimizer stats collection';

OUTPUT:

auto optimizer stats collection ENABLED ORA$AUTOTASK OS ON BY DEFAULT, VOLATILE, SAFE TO KILL ORA$AT_WGRP_OS FALSE 0 0:0:7.166666667 0 0:0:4.966388889 36 18 18 0 0:2:58.79 0 0:2:58.79 0 0:2:38.0 0 0:2:38.0 0 7:59:59.972 0 7:59:59.972

 Checking maintenance window opens and closes , We can modifies accordingly with date and time:

QUERY:

SELECT WINDOW_NAME, REPEAT_INTERVAL, DURATION, ENABLED FROM DBA_SCHEDULER_WINDOWS WHERE  '"SYS"."' || WINDOW_NAME ||'"' IN (SELECT MEMBER_NAME FROM DBA_SCHEDULER_GROUP_MEMBERS WHERE GROUP_NAME='ORA$AT_WGRP_OS') ORDER BY NEXT_START_DATE;

OUTPUT:

THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 0 4:0:0.0 TRUE

FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 0 4:0:0.0 TRUE

SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 0 20:0:0.0 TRUE

SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 0 20:0:0.0 TRUE

MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 0 4:0:0.0 TRUE

TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 0 4:0:0.0 TRUE

WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 0 4:0:0.0 TRUE

 Scheduler job automatically created, runs, and dropped 

QUERY:

SELECT TO_CHAR(LOG_DATE,'DD-MM-YYYY HH24:MI:SS') AS LOG_DATE, JOB_NAME, OPERATION, STATUS FROM   DBA_SCHEDULER_JOB_LOG WHERE  JOB_NAME LIKE 'ORA$AT%OPT%' ORDER  BY LOG_DATE DESC;

OUTPUT:

07-05-2020 01:44:15 ORA$AT_OS_OPT_SY_90 RUN FAILED

07-05-2020 01:44:15 ORA$AT_OS_OPT_SY_90 COMPLETED

07-05-2020 01:44:15 ORA$AT_OS_OPT_SY_90 DROP

07-05-2020 01:44:12 ORA$AT_OS_OPT_SY_90 ENABLE

07-05-2020 01:44:12 ORA$AT_OS_OPT_SY_90 UPDATE

07-05-2020 01:44:12 ORA$AT_OS_OPT_SY_90 UPDATE

07-05-2020 01:44:12 ORA$AT_OS_OPT_SY_90 UPDATE

07-05-2020 01:34:15 ORA$AT_OS_OPT_SY_88 DROP

07-05-2020 01:34:15 ORA$AT_OS_OPT_SY_88 COMPLETED

07-05-2020 01:34:15 ORA$AT_OS_OPT_SY_88 RUN FAILED

07-05-2020 01:34:11 ORA$AT_OS_OPT_SY_88 UPDATE

07-05-2020 01:34:11 ORA$AT_OS_OPT_SY_88 UPDATE

07-05-2020 01:34:11 ORA$AT_OS_OPT_SY_88 ENABLE

07-05-2020 01:34:11 ORA$AT_OS_OPT_SY_88 UPDATE

07-05-2020 01:24:11 ORA$AT_OS_OPT_SY_86 RUN FAILED

07-05-2020 01:24:11 ORA$AT_OS_OPT_SY_86 COMPLETED

07-05-2020 01:24:11 ORA$AT_OS_OPT_SY_86 ENABLE

Objects affected by the job can be obtained

QUERY:

SELECT TARGET, TARGET_TYPE, START_TIME, END_TIME, SUBSTR(TO_CHAR(END_TIME-START_TIME,'HH:MI:SS'),12,8) DURATION, STATUS FROM DBA_OPTSTAT_OPERATION_TASKS ORDER BY OPID DESC, START_TIME DESC;

OUTPUT:

SOE.WAREHOUSES_PK INDEX 07-05-20 04:26:42.183000000 PM +05:30 07-05-20 04:26:42.183000000 PM +05:30 00:00:00 COMPLETED

SOE.WAREHOUSES TABLE 07-05-20 04:26:42.167000000 PM +05:30 07-05-20 04:26:42.183000000 PM +05:30 00:00:00 COMPLETED

SOE.WHS_LOCATION_IX INDEX 07-05-20 04:26:42.167000000 PM +05:30 07-05-20 04:26:42.183000000 PM +05:30 00:00:00 COMPLETED

SOE.V TABLE 07-05-20 04:26:42.152000000 PM +05:30 07-05-20 04:26:42.167000000 PM +05:30 00:00:00 COMPLETED

SOE.PRODUCT_INFORMATION_PK INDEX 07-05-20 04:26:42.152000000 PM +05:30 07-05-20 04:26:42.152000000 PM +05:30 00:00:00 COMPLETED

SOE.PROD_CATEGORY_IX INDEX 07-05-20 04:26:42.152000000 PM +05:30 07-05-20 04:26:42.152000000 PM +05:30 00:00:00 COMPLETED

SOE.PROD_SUPPLIER_IX INDEX 07-05-20 04:26:42.152000000 PM +05:30 07-05-20 04:26:42.152000000 PM +05:30 00:00:00 COMPLETED

SOE.PROD_NAME_IX INDEX 07-05-20 04:26:42.136000000 PM +05:30 07-05-20 04:26:42.136000000 PM +05:30 00:00:00 COMPLETED

SOE.PRODUCT_INFORMATION TABLE 07-05-20 04:26:42.136000000 PM +05:30 07-05-20 04:26:42.152000000 PM +05:30 00:00:00 COMPLETED

SOE.PRD_DESC_PK INDEX 07-05-20 04:26:42.121000000 PM +05:30 07-05-20 04:26:42.136000000 PM +05:30 00:00:00 COMPLETED

SOE.PRODUCT_DESCRIPTIONS TABLE 07-05-20 04:26:42.105000000 PM +05:30 07-05-20 04:26:42.136000000 PM +05:30 00:00:00 COMPLETED

SOE.ITEM_PRODUCT_IX INDEX 07-05-20 04:26:41.652000000 PM +05:30 07-05-20 04:26:42.105000000 PM +05:30 00:00:00 COMPLETED

SOE.ITEM_ORDER_IX INDEX 07-05-20 04:26:41.605000000 PM +05:30 07-05-20 04:26:41.652000000 PM +05:30 00:00:00 COMPLETED

SOE.ORDER_ITEMS_PK INDEX 07-05-20 04:26:39.371000000 PM +05:30 07-05-20 04:26:41.605000000 PM +05:30 00:00:02 COMPLETED

Gathering Optimizer Statistics Manually 

 Examples

Gather statistics of all the objects in the database:

SQL> exec DBMS_STATS.GATHER_DATABASE_STATS;

PL/SQL procedure successfully completed.

Gather statistics of all the schema objects :

SQL> exec DBMS_STATS.GATHER_SCHEMA_STATS('SOE');

PL/SQL procedure successfully completed.

SQL> exec DBMS_STATS.GATHER_SCHEMA_STATS('SOE', CASCADE => TRUE);

PL/SQL procedure successfully completed.

 Gather statistics of specific table:

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

PL/SQL procedure successfully completed.

SQL> exec DBMS_STATS.GATHER_TABLE_STATS('SOE', 'ADDRESSES',CASCADE=> TRUE);

PL/SQL procedure successfully completed.

Gather statistics for schemas dictionary and RDBMS components:

SQL> exec DBMS_STATS.GATHER_DICTIONARY_STATS;

PL/SQL procedure successfully completed.

Goldengate examples

GGSCI - Initial load from source to target database 19c

ggsci : collision delete handling example 19c

ggsci: handle update collision on target database 19c

ggsci - DML replication from source GG to Target GG on pluggable database 19c

ggsci - ./ggsci: error while loading shared libraries: libnnz19.so: cannot open shared object file: No such file or directory

GGSCI - Golden gate 19c Installation on linux machine

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>