Thursday, November 17, 2016

Run time SQL Statistics in Production with SQL Patch

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>

Thursday, October 13, 2016

Deep or wide?



The recursive with clause (recursive subquery factoring) was introduced in 11.2, this gives an alternative to the good old “start with – connect by prior” syntax used for hierarchical queries. One of the features of this new syntax is that it allows to rather easily traverse the hierarchical relationship in either “direction”.  

Do you want to go deep then wide?
Or do you want to go wide then deep?

To illustrate this here is the classic employee manager relationship in the good old EMP table.  Each employee has a manager, except for the president of the company. 

SQL> select empno,ename,mgr from emp;

EMPNO ENAME        MGR
----- ---------- -----
 7369 SMITH       7902
 7499 ALLEN       7698
 7521 WARD        7698
 7566 JONES       7839
 7654 MARTIN      7698
 7698 BLAKE       7839
 7782 CLARK       7839
 7788 SCOTT       7566
 7839 KING
 7844 TURNER      7698
 7876 ADAMS       7788
 7900 JAMES       7698
 7902 FORD        7566
 7934 MILLER      7782

With the new syntax you can first show the managers and their reports, or show ALL managers then the reports.  Let’s take a look to see what this means.  This first query has the clause SEARCH DEPTH FIRST BY eid.  This gives the output we see below.  

WITH
  org_chart (eid, emp_last, mgr_id, reportLevel) AS
  (
     SELECT empno, ename, mgr,1 reportLevel
     FROM emp
     WHERE job='PRESIDENT'
   UNION ALL
     SELECT e.empno, e.ename, e.mgr,
            reportLevel+1
     FROM org_chart r, emp e
     WHERE r.eid = e.mgr
  )
  SEARCH DEPTH FIRST BY eid SET order1 /* NOTICE THIS LINE */
SELECT lpad(' ',2*reportLevel)||eid emp_no, emp_last
FROM org_chart
ORDER BY order1
/

EMP_NO               EMP_LAST
-------------------- --------
  7839               KING
    7566             JONES
      7788           SCOTT
        7876         ADAMS
      7902           FORD
        7369         SMITH
    7698             BLAKE
      7499           ALLEN
      7521           WARD
      7654           MARTIN
      7844           TURNER
      7900           JAMES
    7782             CLARK
      7934           MILLER

This second query has the clause SEARCH BREADTH FIRST BY eid.  This gives the output we see below.  

WITH
  org_chart (eid, emp_last, mgr_id, reportLevel) AS
  (
     SELECT empno, ename, mgr,1 reportLevel
     FROM emp
     WHERE job='PRESIDENT'
   UNION ALL
     SELECT e.empno, e.ename, e.mgr,
            reportLevel+1
     FROM org_chart r, emp e
     WHERE r.eid = e.mgr
  )
  SEARCH BREADTH FIRST BY eid SET order1 /* NOTICE THIS LINE */
SELECT lpad(' ',2*reportLevel)||eid emp_no, emp_last
FROM org_chart
ORDER BY order1
/

EMP_NO               EMP_LAST
-------------------- --------
  7839               KING
    7566             JONES
    7698             BLAKE
    7782             CLARK
      7499           ALLEN
      7521           WARD
      7654           MARTIN
      7788           SCOTT
      7844           TURNER
      7900           JAMES
      7902           FORD
      7934           MILLER
        7369         SMITH
        7876         ADAMS

With the new syntax you have a bit easier time to negotiate the recursive relationship which every way you like.  Also it does appear to have a performance advantage in that it doesn’t sort the data as much as the old start with syntax.   One last point, the docs don’t seem to say this anywhere but BREADTH FIRST appears to be the default if you don’t put in the clause at all.