PT - Improving Performance Through Literals, Bind Variables and Cursor Sharing

Improving Performance Through Literals, Bind Variables and Cursor Sharing 

Scenario :

In this example we execute query and find explain plan in terms by executing value from literals to bind variable we will see same explain plan sql_id  for the query.

SQL> conn soe/soe

Connected.

SQL> SELECT /* my query */ TO_CHAR(SUM(ORDER_TOTAL),'999,999,999') TOTAL FROM SOE.ORDERS WHERE TO_CHAR(ORDER_DATE,'MM-RRRR') ='01-2010';

TOTAL

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

  53,308,707

SQL> SELECT /* my query */ TO_CHAR(SUM(ORDER_TOTAL),'999,999,999') TOTAL FROM SOE.ORDERS WHERE TO_CHAR(ORDER_DATE,'MM-RRRR') ='02-2010';

TOTAL

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

  49,166,476

SQL> SELECT /* my query */ TO_CHAR(SUM(ORDER_TOTAL),'999,999,999') TOTAL FROM SOE.ORDERS WHERE TO_CHAR(ORDER_DATE,'MM-RRRR') ='02-2010';

TOTAL

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

  49,166,476

SQL> SELECT /* my query */ TO_CHAR(SUM(ORDER_TOTAL),'999,999,999') TOTAL FROM SOE.ORDERS WHERE TO_CHAR(ORDER_DATE,'MM-RRRR') ='03-2010';

TOTAL

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

  53,907,203

• Explain plan for previous query

SQL> COL SQL_TEXT FORMAT a30

SQL>  SELECT SQL_TEXT, SQL_ID, VERSION_COUNT, HASH_VALUE FROM V$SQLAREA WHERE SQL_TEXT LIKE '%my query%' AND SQL_TEXT NOT LIKE '%SQL_TEXT%';

SQL_TEXT                       SQL_ID        VERSION_COUNT HASH_VALUE

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

SELECT /* my query */ TO_CHAR( 4qa4ftwp1nks3             1  706366211

SUM(ORDER_TOTAL),'999,999,999'

) TOTAL FROM SOE.ORDERS WHERE

TO_CHAR(ORDER_DATE,'MM-RRRR')

='02-2010'

SELECT /* my query */ TO_CHAR( 6xt52tc3u9h82             1  128237826

SUM(ORDER_TOTAL),'999,999,999'

) TOTAL FROM SOE.ORDERS WHERE

TO_CHAR(ORDER_DATE,'MM-RRRR')

='01-2010'

SQL_TEXT                       SQL_ID        VERSION_COUNT HASH_VALUE

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

SELECT /* my query */ TO_CHAR( agv63ft219k4w             1 1142212764

SUM(ORDER_TOTAL),'999,999,999'

) TOTAL FROM SOE.ORDERS WHERE

TO_CHAR(ORDER_DATE,'MM-RRRR')

='03-2010'

SQL>

• Flush shared pool

SQL>  ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

• Use variable instead using literals

SQL> VARIABLE month_year VARCHAR2(7)

SQL>  EXEC :month_year := '01-2010';

PL/SQL procedure successfully completed.

SQL> SELECT /* my query */ TO_CHAR(SUM(ORDER_TOTAL),'999,999,999') TOTAL FROM SOE.ORDERS WHERE TO_CHAR(ORDER_DATE,'MM-RRRR') =:month_year;

TOTAL

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

  53,308,707

SQL> EXEC :month_year := '02-2010';

PL/SQL procedure successfully completed.

SQL> SELECT /* my query */ TO_CHAR(SUM(ORDER_TOTAL),'999,999,999') TOTAL FROM SOE.ORDERS WHERE TO_CHAR(ORDER_DATE,'MM-RRRR') =:month_year;

TOTAL

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

  49,166,476

SQL> EXEC :month_year := '03-2010';

PL/SQL procedure successfully completed.

SQL> SELECT /* my query */ TO_CHAR(SUM(ORDER_TOTAL),'999,999,999') TOTAL FROM SOE.ORDERS WHERE TO_CHAR(ORDER_DATE,'MM-RRRR') =:month_year;

TOTAL

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

  53,907,203

SQL>

• sql_id is same from previous query.

SQL> COL SQL_TEXT FORMAT a30

SQL>  SELECT SQL_TEXT, SQL_ID, VERSION_COUNT, HASH_VALUE FROM V$SQLAREA WHERE SQL_TEXT LIKE '%my query%' AND SQL_TEXT NOT LIKE '%SQL_TEXT%';

SQL_TEXT                       SQL_ID        VERSION_COUNT HASH_VALUE

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

SELECT /* my query */ TO_CHAR( g6muxwg8d93aj             1 3503590737

SUM(ORDER_TOTAL),'999,999,999'

) TOTAL FROM SOE.ORDERS WHERE

TO_CHAR(ORDER_DATE,'MM-RRRR')

=:month_year

SQL>

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>