PT - EXPDP IMPDP using EXPORT| IMPORT _TABLE_STATS Optimizer Statistics

Scenario:

Stats gathering of schema transport using expdp and impdp by creating stats gathering on particular table the by using EXPORT_TABLE_STATS and IMPORT_TABLE_STATS option on stats.

• Create table test2 and insert vlaues on table also create index. 

SQL> CREATE TABLE TEST.TEST2 ( ORDER_ID  NUMBER(12), ORDER_DATE TIMESTAMP(6) WITH LOCAL TIME ZONE, ORDER_TOTAL NUMBER(8,2));

Table created.

SQL> INSERT INTO TEST.TEST2 SELECT ORDER_ID, ORDER_DATE, ORDER_TOTAL FROM TEST.ORDERS WHERE ORDER_TOTAL >100;

1304324 rows created.

SQL> COMMIT;

Commit complete.

SQL> CREATE INDEX TEST.TEST2_TOTAL_IX ON TEST.TEST2(ORDER_TOTAL);

Index created.

• Statstics gathering of table test2

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'TEST', TABNAME=>'TEST2', CASCADE=>TRUE);

PL/SQL procedure successfully completed.

• Create stats table TEST_STATS

SQL> exec DBMS_STATS.CREATE_STAT_TABLE(OWNNAME=>'TEST',STATTAB=>'TEST_STATS');

PL/SQL procedure successfully completed.

• Export TEST_STATS using EXPORT_TABLE_STATS

SQL> exec DBMS_STATS.EXPORT_TABLE_STATS(OWNNAME=>'TEST', TABNAME=>'TEST2', STATTAB=>'TEST_STATS', STATID=>NULL, STATOWN=>'TEST', CASCADE=>TRUE);

PL/SQL procedure successfully completed.

• Perform EXPDP on  tables TEST_STATS

SQL> host expdp TEST/TEST directory=expdp dumpfile=TEST_STATS.dmp tables=TEST_STATS

Export: Release 12.2.0.1.0 - Production on Thu May 14 17:07:02 2020

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Starting "TEST"."SYS_EXPORT_TABLE_01":  TEST/******** directory=expdp dumpfile=TEST_STATS.dmp tables=TEST_STATS

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

. . exported "TEST"."TEST_STATS"                           18.25 KB       5 rows

Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for TEST.SYS_EXPORT_TABLE_01 is:

  C:\APP\EXPDP\TEST_STATS.DMP

Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at Thu May 14 17:07:22 2020 elapsed 0 00:00:19

SQL>

• We can delete stats of particular table for this example scenario we delete all and same table stats will be imported.

SQL> exec DBMS_STATS.DELETE_TABLE_STATS (OWNNAME=>'TEST', TABNAME=>'TEST2');

PL/SQL procedure successfully completed.

SQL>

SQL> SELECT NUM_ROWS FROM USER_TAB_STATISTICS WHERE TABLE_NAME='TEST2';

  NUM_ROWS

----------

SQL>

SQL> exec DBMS_STATS.DROP_STAT_TABLE('TEST','TEST_STATS');

PL/SQL procedure successfully completed.

SQL>

SQL> exec DBMS_STATS.DROP_STAT_TABLE('TEST','TEST_STATS');

PL/SQL procedure successfully completed.

• IMPDP of table TEST_STATS on same DB as previously we clean all things before import operation.

SQL> host impdp TEST/TEST directory=expdp dumpfile=TEST_STATS.dmp tables=TEST_STATS

Import: Release 12.2.0.1.0 - Production on Thu May 14 17:11:40 2020

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Master table "TEST"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded

Starting "TEST"."SYS_IMPORT_TABLE_01":  TEST/******** directory=expdp dumpfile=TEST_STATS.dmp tables=TEST_STATS

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "TEST"."TEST_STATS"                           18.25 KB       5 rows

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Job "TEST"."SYS_IMPORT_TABLE_01" successfully completed at Thu May 14 17:11:49 2020 elapsed 0 00:00:09

SQL>

• Import stats of table 

SQL> exec DBMS_STATS.IMPORT_TABLE_STATS(OWNNAME=>'TEST', TABNAME=>'TEST2', STATTAB=>'TEST_STATS',STATID=>NULL,STATOWN=>'TEST');

PL/SQL procedure successfully completed.

SQL>

• Check the output same as earlier

SQL> SELECT NUM_ROWS FROM USER_TAB_STATISTICS WHERE TABLE_NAME='TEST2';

  NUM_ROWS

----------

   1304324

SQL>

SQL> exec DBMS_STATS.DROP_STAT_TABLE('TEST','TEST_STATS');

PL/SQL procedure successfully completed.

PT - Histograms are Automatically Gathered example

Scenario:

Example of how Histograms are Automatically Gathered

Querey : display_colstats.sql

col COLUMN_NAME format a15 

SELECT COLUMN_NAME, NUM_DISTINCT, NUM_NULLS, HISTOGRAM, DENSITY, NUM_BUCKETS FROM   USER_TAB_COL_STATISTICS WHERE  TABLE_NAME = 'ORDERS2';

Querey : display_histo.sql 

col COLUMN_NAME format a15 

SELECT COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE, ENDPOINT_REPEAT_COUNT FROM USER_HISTOGRAMS WHERE TABLE_NAME='ORDERS2' AND COLUMN_NAME='&1' ORDER BY 2; 

Querey : display_preds.sql

col COLUMN_NAME format a14

SELECT C.COLUMN_NAME, EQUIJOIN_PREDS, NONEQUIJOIN_PREDS, RANGE_PREDS, LIKE_PREDS, NULL_PREDS  FROM   SYS.COL_USAGE$ CU, USER_OBJECTS O, USER_TAB_COLUMNS C WHERE  O.OBJECT_ID = CU.OBJ# AND (O.OBJECT_NAME=C.TABLE_NAME AND C.COLUMN_ID = CU.INTCOL#)        AND OBJECT_NAME='ORDERS2' ORDER BY OBJECT_NAME,  C.COLUMN_ID;

 Automatically Histograms Gathered example

• Check METHOD_OPT for stats which is FOR ALL COLUMNS SIZE AUTO

SQL> SELECT DBMS_STATS.GET_PREFS(PNAME =>'METHOD_OPT') METHOD_OPT FROM DUAL;

METHOD_OPT

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

FOR ALL COLUMNS SIZE AUTO

SQL>

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE

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

TESTPLM    READ WRITE           PRIMARY

• Create table test.orders , index and insert data in table

SQL> CREATE TABLE TEST.ORDERS2 ( ORDER_ID NUMBER(12), ORDER_DATE TIMESTAMP(6) WITH LOCAL TIME ZONE, ORDER_TOTAL NUMBER(8,2),ORDER_MODE VARCHAR2(8), CUSTOMER_ID NUMBER(12), ORDER_STATUS NUMBER(2));

Table created.

SQL> CREATE INDEX TEST.ORDERS2_TOTAL_IX ON TEST.ORDERS2(ORDER_TOTAL);

Index created.

SQL> INSERT INTO TEST.ORDERS2 SELECT ORDER_ID, ORDER_DATE, ORDER_TOTAL, ORDER_MODE, CUSTOMER_ID, ORDER_STATUS FROM TEST.ORDERS WHERE ORDER_TOTAL BETWEEN 10000 AND 15000;

200625 rows created.

SQL>

SQL>

SQL>  COMMIT;

Commit complete.

• Gather stats of table orders2

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'TEST', TABNAME=>'ORDERS2', CASCADE=>TRUE);

PL/SQL procedure successfully completed.

SQL>

• Check the stats  specific column base histogram statistics 

SQL> @display_colstats

COLUMN_NAME     NUM_DISTINCT  NUM_NULLS HISTOGRAM          DENSITY NUM_BUCKETS

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

ORDER_ID              200096          0 NONE            4.9976E-06           1

ORDER_DATE            200048          0 NONE            4.9988E-06           1

ORDER_TOTAL             2600          0 NONE            .000384615           1

ORDER_MODE                 2       1807 NONE                    .5           1

CUSTOMER_ID            11136          0 NONE            .000089799           1

ORDER_STATUS              10          0 NONE                    .1           1

6 rows selected.

• Flush database monitoring information from SGA

SQL>    set linesize 180

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO

PL/SQL procedure successfully completed.

• Selectivity estimation  predicate range of cloumn ORDER_TOTAL on  ORDERS2 table

SQL> @display_preds

COLUMN_NAME    EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS

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

ORDER_TOTAL                 0                 0           1          0          0

SQL>

• Stast gather table orders2

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

PL/SQL procedure successfully completed.

• Check the stats  specific column base histogram  statistics 

SQL> @display_colstats

COLUMN_NAME     NUM_DISTINCT  NUM_NULLS HISTOGRAM          DENSITY NUM_BUCKETS

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

ORDER_ID              200096          0 NONE            4.9976E-06           1

ORDER_DATE            200048          0 NONE            4.9988E-06           1

ORDER_TOTAL             2600          0 HYBRID             .000297         254

ORDER_MODE                 2       1807 NONE                    .5           1

CUSTOMER_ID            11136          0 NONE            .000089799           1

ORDER_STATUS              10          0 NONE                    .1           1

6 rows selected.

SQL>

PT - Lock | Unlock Optimizer Statistics Examples ORA-20005: object statistics are locked (stattype = ALL)

Scenario : 

 We Lock the Optimizer Statistics of tables  and then unlock the Optimizer Statistics of same table 

• Create table test2

SQL> CREATE TABLE TEST.TEST2 ( ORDER_ID  NUMBER(12), ORDER_DATE TIMESTAMP(6) WITH LOCAL TIME ZONE, ORDER_TOTAL NUMBER(8,2));

Table created.

• Insert values on test2 table

SQL> INSERT INTO TEST.TEST2 SELECT ORDER_ID, ORDER_DATE, ORDER_TOTAL FROM TEST.ORDERS WHERE ORDER_TOTAL >100;

1304324 rows created.

SQL> COMMIT;

Commit complete.

• Create index on table test2

SQL> CREATE INDEX TEST.TEST2_TOTAL_IX ON TEST.TEST2(ORDER_TOTAL);

Index created.

• Stats gathering of table test2

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'TEST', TABNAME=>'TEST2', CASCADE=>TRUE);

PL/SQL procedure successfully completed.

• Check the locked staus of  table

SQL> SELECT STATTYPE_LOCKED, NUM_ROWS FROM USER_TAB_STATISTICS WHERE TABLE_NAME='TEST2';

STATT   NUM_ROWS

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

         1304324

• Lock table using   DBMS_STATS.LOCK_TABLE_STATS

SQL> exec DBMS_STATS.LOCK_TABLE_STATS('TEST','TEST2') ;

PL/SQL procedure successfully completed.

SQL>

• Check lock status using query

SQL> SELECT STATTYPE_LOCKED FROM USER_TAB_STATISTICS WHERE TABLE_NAME='TEST2';

STATT

-----

ALL

• Try to gather stats using DBMS_STATS.GATHER_TABLE_STATS it will through error

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'TEST', TABNAME=>'TEST2', CASCADE=>TRUE);

BEGIN DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'TEST', TABNAME=>'TEST2', CASCADE=>TRUE); END;

*

ERROR at line 1:

ORA-20005: object statistics are locked (stattype = ALL)

ORA-06512: at "SYS.DBMS_STATS", line 36873

ORA-06512: at "SYS.DBMS_STATS", line 36507

ORA-06512: at "SYS.DBMS_STATS", line 8582

ORA-06512: at "SYS.DBMS_STATS", line 9461

ORA-06512: at "SYS.DBMS_STATS", line 35836

ORA-06512: at "SYS.DBMS_STATS", line 36716

ORA-06512: at line 1

SQL>

• We can perform DML operations on table only affect on stats gathering

SQL> DELETE TEST.TEST2 WHERE ORDER_TOTAL>115;

1304048 rows deleted.

SQL> COMMIT;

Commit complete.

• Unlock the stats gathering

SQL> exec DBMS_STATS.UNLOCK_TABLE_STATS('TEST', 'TEST2');

PL/SQL procedure successfully completed.

• Execute stats gathering again to check status 

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'TEST', TABNAME=>'TEST2', CASCADE=>TRUE);

PL/SQL procedure successfully completed.