Friday, February 17, 2017

Hotsos Symposium 2017 Feb27-Mar2



Here we go!  It’s just over a week away and it’s another excellent line up of speakers.   I’ll mention a few here to whet your appetite and go to our page on the line up to read more on these and the other great speakers. 



We have Larry Carpenter back for an excellent Keynote on Data Guard and the Cloud.   I’ve interested to hear what kind of cloud Data Guard works best in, Cirrus, Status, Cumulus….



Also coming all the way from down under again is Richard Foote, Mr. Oracle Index.  He’s talks last year were a huge hit as I’m sure they will be again this year.



Bryn Llewellyn will be back to spread the word about EBR. He’s also supporting his partner in crime Toon Koppelaars. Toon will take up where Bryn’s “Why Use PL/SQL” left off last year to present a real-world comparison of the NoPlsql & ThickDB paradigms. 


Toon and Vlado Braun will be doing the training day (Thursday March 2nd).  They will bring to the table their vast experience on Real-World Performance Training.



We have Jim Czuprynski to talk on In-Memory and some new Analytics in 12cr2, that’s going to be supper cool stuff.  These analytics and in-memory features are taking SQL statements to warp speed, really great stuff.



Carlos Sierra with be there taking in depth about SQL Trace and execution plans for beginners, but knowing Carlos, his talk will be valuable for all anyone.  Be you a wet-behind-the-ears newbie or an old seasoned veteran.



Paul Jackson brings shares with us his expertise on using SQL Monitor to do tuning, if you’ve not used SQL Monitor yet, you need to see this.  Even if you have Paul’s likely got so cool tricks and techniques you want to catch.



Kerry Osborne is back again with big talk on a big data.  Kerry’s tell it like it is style is sure to cut to the core of what this big data thing is all about and the how to use it with lots of technical discussion and not just market fluff.



Tim Gorman brings in an excellent discussion on testing with Data Virtualization, being from Colorado I’m wondering what this type of “virtualization” this will be.  I’ll have to make sure I get there to get the full story.



Many others will be talking as well; I hope this short list of a few of the folks speaking peaks your interest to attend.   If you haven’t registered there is still time!  There is a virtual option as well, but it’s always best to be there so you can mingle with the speakers and other oracle professionals to talk the talk so you can walk the walk.

Go here to Register:
https://www.hotsos.com/apex/f?p=200:61705:3373695737072::NO:::

Wednesday, February 1, 2017

Advanced Format option in DISPLAY_CURSOR


Oracle is good at sneaking things it to the database.  And the Advanced format option for DISPLAY CURSOR is one of those things.   Using by itself you get the Basic plan, the Alias information,  The Outline data,  Predicate information, column projection information and notes. 

The cool stuff is the outline data.  For those of us who have been around Oracle for a while this is the old “stored outline” which is the set of hints that would make a sorted outline.  Today these are the hints used to make a baseline.  They can just be cool to read and see what the hints would be to create the plan and to give you some insight into the hints themselves.

Here a quick example.  First the little script I used in SQLcl:  (Yea I’m moving to SQLcl, really a great tool.  A big step up for out command line tool, SQL*Plus is still great and a wonderful tool, SQLcl take all the greatness of SQL*Plus and adds some great functionality.)

set serveroutput off

create or replace view orderview as
select /*+ qb_name(orderview) */
  ord2.cust_no custnum, ord2.order_no ordnum,
  order_date orddate, product_id prodid, quantity
from ord_item2, ord2
where ord_item2.order_no = ord2.order_no
/

set termout off
select /*+ qb_name(mainblock) */lastname, custnum, ordnum, orddate, prodid, quantity
from customer2 c2, orderview ov
where ov.custnum = c2.cust_no;
set termout on

select * from table(dbms_xplan.display_cursor(format=>'ADVANCED'));

 Here’s the run:
************************************

SQL> @rvd02
PLAN_TABLE_OUTPUT
SQL_ID  bsu4vh744stwq, child number 0
-------------------------------------
select /*+ qb_name(mainblock) */lastname, custnum, ordnum, orddate,
prodid, quantity from customer2 c2, orderview ov  where ov.custnum =
c2.cust_no

Plan hash value: 4000018549

---------------------------------------------------------------------------------
| Id  | Operation           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |       |       |   118 (100)|          |
|*  1 |  HASH JOIN          |           | 70975 |  3881K|   118   (1)| 00:00:01 |
|*  2 |   HASH JOIN         |           | 12890 |   553K|    51   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| CUSTOMER2 |  1000 | 26000 |     7   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| ORD2      | 12890 |   226K|    44   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL | ORD_ITEM2 | 70975 |   831K|    66   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$19724973
   3 - SEL$19724973 / C2@MAINBLOCK
   4 - SEL$19724973 / ORD2@ORDERVIEW
   5 - SEL$19724973 / ORD_ITEM2@ORDERVIEW

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      DB_VERSION('12.1.0.2')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$19724973")
      MERGE(@"ORDERVIEW")
      OUTLINE(@"MAINBLOCK")
      OUTLINE(@"ORDERVIEW")
      FULL(@"SEL$19724973" "C2"@"MAINBLOCK")
      FULL(@"SEL$19724973" "ORD2"@"ORDERVIEW")
      FULL(@"SEL$19724973" "ORD_ITEM2"@"ORDERVIEW")
      LEADING(@"SEL$19724973" "C2"@"MAINBLOCK" "ORD2"@"ORDERVIEW"
              "ORD_ITEM2"@"ORDERVIEW")
      USE_HASH(@"SEL$19724973" "ORD2"@"ORDERVIEW")
      USE_HASH(@"SEL$19724973" "ORD_ITEM2"@"ORDERVIEW")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("ORD_ITEM2"."ORDER_NO"="ORD2"."ORDER_NO")
   2 - access("ORD2"."CUST_NO"="C2"."CUST_NO")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=1) "ORD2"."ORDER_NO"[NUMBER,22],
       "ORD2"."CUST_NO"[NUMBER,22], "LASTNAME"[VARCHAR2,20],
       "QUANTITY"[NUMBER,22], "ORDER_DATE"[DATE,7], "PRODUCT_ID"[NUMBER,22],
       "QUANTITY"[NUMBER,22], "PRODUCT_ID"[NUMBER,22]
   2 - (#keys=1) "ORD2"."CUST_NO"[NUMBER,22], "LASTNAME"[VARCHAR2,20],
       "ORD2"."ORDER_NO"[NUMBER,22], "ORDER_DATE"[DATE,7]
   3 - (rowset=200) "C2"."CUST_NO"[NUMBER,22], "LASTNAME"[VARCHAR2,20]
   4 - (rowset=200) "ORD2"."ORDER_NO"[NUMBER,22],
       "ORD2"."CUST_NO"[NUMBER,22], "ORDER_DATE"[DATE,7]
   5 - "ORD_ITEM2"."ORDER_NO"[NUMBER,22], "PRODUCT_ID"[NUMBER,22],
       "QUANTITY"[NUMBER,22]

Note
-----
   - this is an adaptive plan


SQL>

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>