Sometimes we need to “test” in production. Or maybe we just want to see more statistics
of a SQL in production. To get full good
statistics on a SQL statement running in Oracle you need one of two things, STATISTICS_LEVEL
set to ALL
or use the GATHER_PLAN_STATISTICS hint in the statement.
Cool as STATISTICS_LEVEL to ALL is, this is likely not practical
in most production environments. It adds
a level of work that likely will be noticed.
The reason is quite simple, to get accurate timing statistics in particular
Oracle polls the system clock at a very high rate and this added to the run
time of the SQL. And this will happen to
all SQL on the system, making everything slow down. Not something most folks want.
Using the
hint is nice because it focuses this extra work to the statement and only the
statement. But how do you get the hint
into already existing code? And what if
you don’t have access to the source? And
even if you do, if you add a hint, you’d like have to go thru change control to
punch it out to your production system. Ugh.
Enter SQL
PATCH.
Starting
with Oracle 11 this functionality gives you the ability to add a hint to a SQL
ID without modifying the code. This is
pretty cool, but I’d like to caution you that this shouldn’t be used often. And
when used it should be a temporary fix. And this case of getting run time statistics
is a good example of how it could be used in that context.
The basic steps are:
1. Identify the SQL you want
to gather statistics on.
2. Create a patch for the SQL
with 'GATHER_PLAN_STATISTICS'
as the hint to be added with DBMS_SQLDIAG_INTERNAL.I_CREATE_PATCH. When a patch is created it is enabled by
default. NOTE: to create a patch you
must be connected as SYSDBA.
3. Then either disable the
patch or drop it once you’re done.
Also I’d like give some kudos to Shiraz Kamal who asked me
about doing this in a recent class which got me to write the demo and this blog
post. Thanks Z!
Here is an example:
SQL>
SQL>
@sql_patch_demo2
SQL> --
sql_patch_demo2.sql
SQL> --
RVD November 2016
SQL> --
SQL>
SQL> set
pages 9999
SQL> set
lines 150
SQL>
column plan_table_output format a130
SQL>
column NAME format a20
SQL>
column CATEGORY format a20
SQL>
column STATUS format a10
SQL>
SQL> set
echo on
SQL>
SQL>
DECLARE
2
PATCHNOTFND EXCEPTION;
3
PRAGMA EXCEPTION_INIT(PATCHNOTFND, -13833);
4
BEGIN
5
DBMS_SQLDIAG.DROP_SQL_PATCH('TEST_PATCH99');
6
EXCEPTION
7
WHEN PATCHNOTFND THEN
8
dbms_output.put_line('*** Patch Not Found ***');
9 END;
10 /
*** Patch
Not Found ***
SQL>
SQL>
select name,category,status,sql_text from dba_sql_patches;
SQL>
SQL>
@hflush sp
SQL> set
echo off
*** Shared
pool flushed ***
PL/SQL
procedure successfully completed.
SQL>
SQL>
alter session set statistics_level=typical
2 /
SQL>
SQL> set
serveroutput off
SQL> set
termout off
SQL>
SQL>
select * from table(dbms_xplan.display_cursor(format=>'allstats last'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID d1v4v24j9y297, child number 0
-------------------------------------
select
ord2.order_no, cust_no, order_date, product_id, quantity,
item_price
from ord_item2, ord2 where ord_item2.order_no =
ord2.order_no
and ord2.order_no between 10000 and 20000
Plan hash
value: 2269185734
----------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem |
1Mem | Used-Mem |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| | |
| |
|* 1 |
HASH JOIN | |
134 | 1263K| 1263K| 1245K (0)|
|* 2 |
TABLE ACCESS FULL| ORD2
| 131 | |
| |
|* 3 |
TABLE ACCESS FULL| ORD_ITEM2 |
721 | | | |
----------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
1 -
access("ORD_ITEM2"."ORDER_NO"="ORD2"."ORDER_NO")
2 -
filter(("ORD2"."ORDER_NO"<=20000 AND
"ORD2"."ORDER_NO">=10000))
3 -
filter(("ORD_ITEM2"."ORDER_NO"<=20000 AND
"ORD_ITEM2"."ORDER_NO">=10000))
Note
-----
- this is an adaptive plan
- Warning: basic plan statistics not
available. These are only collected when:
* hint 'gather_plan_statistics' is used
for the statement or
* parameter 'statistics_level' is set to
'ALL', at session or system level
SQL>
SQL> set
serveroutput on
SQL> --
***************************************************************************
SQL> --
Notice the message that only basic stats are available
SQL> --
Now to create a patch to give us the stats we want
SQL> --
Need to connect as SYS to create the patch
SQL> --
***************************************************************************
SQL>
SQL>
connect / as sysdba
Connected.
SQL>
SQL>
BEGIN
2
SYS.DBMS_SQLDIAG_INTERNAL.I_CREATE_PATCH(
3
SQL_TEXT => 'select
ord2.order_no, cust_no, order_date, product_id, quantity,'||
4 'item_price from
ord_item2, ord2 where ord_item2.order_no ='||
5 'ord2.order_no and
ord2.order_no between 10000 and 20000',
6
HINT_TEXT => 'GATHER_PLAN_STATISTICS',
7
NAME => 'TEST_PATCH99');
8 END;
9 /
SQL>
SQL> --
***************************************************************************
SQL> --
Reconnect as OP
SQL> --
***************************************************************************
SQL>
connect op/op
Connected.
SQL>
SQL>
select name,category,status,sql_text from dba_sql_patches;
NAME CATEGORY STATUS SQL_TEXT
--------------------
-------------------- ----------
--------------------------------------------------------------------------------
TEST_PATCH99 DEFAULT ENABLED select ord2.order_no, cust_no, order_date,
product_id, quantity,item_price from
SQL>
SQL> --
***************************************************************************
SQL> --
Lets see if the SQL patch has an effect
SQL> --
***************************************************************************
SQL>
SQL> set
serveroutput off
SQL> set
termout off
SQL>
SQL>
select * from table(dbms_xplan.display_cursor(format=>'allstats last'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID d1v4v24j9y297, child number 0
-------------------------------------
select
ord2.order_no, cust_no, order_date, product_id, quantity,
item_price
from ord_item2, ord2 where ord_item2.order_no =
ord2.order_no
and ord2.order_no between 10000 and 20000
Plan hash
value: 2269185734
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time
| Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | |
723 |00:00:00.01 | 557 | |
| |
|* 1 |
HASH JOIN | | 1 |
134 | 723 |00:00:00.01 | 557 |
1263K| 1263K| 1310K (0)|
|* 2 |
TABLE ACCESS FULL| ORD2
| 1 | 131 |
132 |00:00:00.01 | 191 | |
| |
|* 3 |
TABLE ACCESS FULL| ORD_ITEM2 |
1 | 721 | 723 |00:00:00.01 | 366 |
| | |
---------------------------------------------------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
1 -
access("ORD_ITEM2"."ORDER_NO"="ORD2"."ORDER_NO")
2 - filter(("ORD2"."ORDER_NO"<=20000
AND "ORD2"."ORDER_NO">=10000))
3 -
filter(("ORD_ITEM2"."ORDER_NO"<=20000 AND
"ORD_ITEM2"."ORDER_NO">=10000))
Note
-----
- SQL patch "TEST_PATCH99" used
for this statement
- this is an adaptive plan
SQL>
SQL>
SQL> --
***************************************************************************
SQL> --
Disable the patch and go again
SQL> --
***************************************************************************
SQL>
SQL>
SQL>
EXEC DBMS_SQLDIAG.ALTER_SQL_PATCH('TEST_PATCH99', 'STATUS', 'DISABLED');
SQL>
SQL>
select name,category,status,sql_text from dba_sql_patches;
NAME CATEGORY STATUS SQL_TEXT
--------------------
-------------------- ---------- --------------------------------------------------------------------------------
TEST_PATCH99 DEFAULT DISABLED select ord2.order_no, cust_no, order_date,
product_id, quantity,item_price from
SQL>
SQL> set
serveroutput off
SQL> set
termout off
SQL>
SQL>
select * from table(dbms_xplan.display_cursor(format=>'allstats last'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID d1v4v24j9y297, child number 0
-------------------------------------
select
ord2.order_no, cust_no, order_date, product_id, quantity,
item_price
from ord_item2, ord2 where ord_item2.order_no =
ord2.order_no
and ord2.order_no between 10000 and 20000
Plan hash
value: 2269185734
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time
| Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | |
723 |00:00:00.01 | 557 | |
| |
|* 1 |
HASH JOIN | | 1 |
134 | 723 |00:00:00.01 | 557 |
1263K| 1263K| 1295K (0)|
|* 2 |
TABLE ACCESS FULL| ORD2
| 1 | 131 |
132 |00:00:00.01 | 191 | |
| |
|* 3 |
TABLE ACCESS FULL| ORD_ITEM2 |
1 | 721 | 723 |00:00:00.01 | 366 |
| | |
---------------------------------------------------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
1 -
access("ORD_ITEM2"."ORDER_NO"="ORD2"."ORDER_NO")
2 -
filter(("ORD2"."ORDER_NO"<=20000 AND
"ORD2"."ORDER_NO">=10000))
3 -
filter(("ORD_ITEM2"."ORDER_NO"<=20000 AND
"ORD_ITEM2"."ORDER_NO">=10000))
Note
-----
- this is an adaptive plan
SQL>
SQL> --
***************************************************************************
SQL> --
Notice that the patch is not used. The patch is not mentioned in the notes.
SQL> --
The plans still has stats from the prior run which are shown.
SQL> --
***************************************************************************
SQL>
SQL>
SQL> set
serveroutput on
SQL> --
***************************************************************************
SQL> --
Now time to clean up
SQL> --
***************************************************************************
SQL>
SQL>
DECLARE
2
PATCHNOTFND EXCEPTION;
3
PRAGMA EXCEPTION_INIT(PATCHNOTFND, -13833);
4
BEGIN
5
DBMS_SQLDIAG.DROP_SQL_PATCH('TEST_PATCH99');
6
EXCEPTION
7
WHEN PATCHNOTFND THEN
8
dbms_output.put_line('*** Patch Not Found ***');
9 END;
10 /
SQL>
SQL>
select name,category,created, sql_text from dba_sql_patches;
SQL>
