SQL Profile: Case 2 - Part 1: Create/Execute/Report/Accept

SQL Profile: Case 2 - Part 2: Data Dictionary

Case 2: The statement is in SQL Area but it has a wrong execution plan

This case is like the previous one. The difference is that the statement is already run. First you have to run the DBMS_SQLTUNE.CREATE_TUNING_TASK procedure.

I remember that the 4 steps are:

(a) Create a tuning task
(b) Execute the tuning task
(c) Report the tuning task
(d) Accept the tuning task

Respect to the previous case, now the procedure that you have to run is:

declare
  task_name VARCHAR2(30);
  begin
     task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
     sql_id => '&sqlid',
     scope => 'COMPREHENSIVE',
     time_limit => 30, -- nr di secondi di analisi
     task_name => 'task_&sqlid',
     description => 'Task to tune a query');
end;
/

As you can see, we loose "sqltext" and "user_name" parameter. This is because now you have the sql_id (instead of sqltext) and the statement is not related to any user.

For this example, I use "&sqlid" so I generalize the pl/sql code. And, in order to show how this case and previous one are really close, I'm going to use the same statement:

select 
      ooo.owner
    , oao.object_name 
from 
      OSL_ALL_OBJECTS   oao 
join  OSL_OWNER_OBJECTS ooo 
on
      (ooo.object_id = oao.object_id);

Obviously, I start in a clear environment, where there is neither SQL Profile nor Tuning Task. You can find the tables and index definitions, here.

Looking for the sql_id in sql_area, I find:

SQL> select sql_id, sql_text from v$sql where sql_text like '%ooo%'

gwwazjphk20wc      3252867984

select       ooo.owner     , oao.object_name from       OSL_ALL_OBJECTS   oao jo

in  OSL_OWNER_OBJECTS ooo on       (ooo.object_id = oao.object_id)

The output of the explain plan is here: there are 2 FULL TABLE ACCESS.

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

| Id  | Operation          | Name              | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT   |                   |       |       |       | 16476 (100)|          |

|   1 |  HASH JOIN         |                   |  9999K|   371M|  7712K| 16476   (1)| 00:00:03 |

|   2 |   TABLE ACCESS FULL| OSL_OWNER_OBJECTS |   262K|  4613K|       |   113   (1)| 00:00:01 |

|   3 |   TABLE ACCESS FULL| OSL_ALL_OBJECTS   |  9999K|   200M|       |  5516   (1)| 00:00:01 |

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

Step a) Create a tuning task

DECLARE

    task_name VARCHAR2(30);

BEGIN

    task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(

        sql_id => 'gwwazjphk20wc',

        scope => 'COMPREHENSIVE',

        time_limit => 30, -- nr of seconds of analisys

        task_name => 'task_select',

        description => 'Task to tune a query');

END;

/

The "task_name" is just a name that I was given. You have to choose a different name (if you want).

Step b) Execute the tuning task

BEGIN
    DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'task_select');
END;
/

Step c) Report the tuning task

set long 10000
set longchunksize 1000
set linesize 230
set heading off
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('task_select') from DUAL;
set heading on

You can find the full output here. Accepting the reccomandation, the new plan will be

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name              | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                   |  9999K|   371M|  3136   (1)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR       |                   |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM) | :TQ10000          |  9999K|   371M|  3136   (1)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|*  3 |    HASH JOIN          |                   |  9999K|   371M|  3136   (1)| 00:00:01 |  Q1,00 | PCWP |            |
|   4 |     TABLE ACCESS FULL | OSL_OWNER_OBJECTS |   262K|  4613K|    63   (2)| 00:00:01 |  Q1,00 | PCWP |            |
|   5 |     PX BLOCK ITERATOR |                   |  9999K|   200M|  3063   (1)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |      TABLE ACCESS FULL| OSL_ALL_OBJECTS   |  9999K|   200M|  3063   (1)| 00:00:01 |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------------------------

Step d) Accept the tuning task

In the "SQL Profile Finding" section, you find the command that  you have to run, if you want to accept the reccomandation:

execute dbms_sqltune.accept_sql_profile( -
    , task_name => 'task_select' -
    , task_owner => 'ASALZANO' -
    , replace => TRUE
    , profile_type => DBMS_SQLTUNE.PX_PROFILE);


 I prefere to give to the SQL Profile a my name, so I mofify a little the previous statement:

execute dbms_sqltune.accept_sql_profile( -
      task_name => 'task_select'         -
    , task_owner => 'ASALZANO'           -
    , replace => TRUE                    -
    , name => 'profile_7srkyyv9jxhzm'    -
    , profile_type => DBMS_SQLTUNE.PX_PROFILE);


At this point it will use the SQL Profile:

SQL> set autot trace exp
SQL> select
      ooo.owner
    , oao.object_name
from
      OSL_ALL_OBJECTS   oao
join  OSL_OWNER_OBJECTS ooo
on
      (ooo.object_id = oao.object_id);  2    3    4    5    6    7    8

Execution Plan
----------------------------------------------------------
Plan hash value: 3357147682

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name              | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                   |  9999K|   371M|  3136   (1)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR       |                   |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM) | :TQ10000          |  9999K|   371M|  3136   (1)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|*  3 |    HASH JOIN          |                   |  9999K|   371M|  3136   (1)| 00:00:01 |  Q1,00 | PCWP |            |
|   4 |     TABLE ACCESS FULL | OSL_OWNER_OBJECTS |   262K|  4613K|    63   (2)| 00:00:01 |  Q1,00 | PCWP |            |
|   5 |     PX BLOCK ITERATOR |                   |  9999K|   200M|  3063   (1)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |      TABLE ACCESS FULL| OSL_ALL_OBJECTS   |  9999K|   200M|  3063   (1)| 00:00:01 |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------------------------

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

   3 - access("OOO"."OBJECT_ID"="OAO"."OBJECT_ID")

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 2
   - SQL profile "profile_7srkyyv9jxhzm" used for this statement

Delete/Disable/Modify SQL Profile

If you wnat to drop or modify the SQL Profile and/or the Tuning task, you have to run DBMS_SQLTUNE package. For my example:

• Drop the Tuning task

exec DBMS_SQLTUNE.DROP_TUNING_TASK ('task_select')

• Drop the SQL Profile

exec DBMS_SQLTUNE.DROP_SQL_PROFILE ('profile_7srkyyv9jxhzm')

• Disable (modify) the SQL Profile

exec DBMS_SQLTUNE.ALTER_SQL_PROFILE (  -

      name =>  'profile_7srkyyv9jxhzm' -

    , attribute_name => 'STATUS'       -

    , value =>  'DISABLED')

SQL Profile: Case 2 - Part 2: Data Dictionary

SQL Profile: Case 1 - Part 2: Data Dictionary

SQL Profile: Case 1 - Part 1: Create/Execute/Report/Accept

All informations about SQL Profile, are saved into Data Dictionary. Obiously.

Those are some DBA_ views from the 12cR2 reference manual:

dba_advisor_actions
dba_advisor_recommendations
dba_advisor_findings
dba_advisor_rationale

Using this query you can read somethings

col owner for a10
col action_message for a20
col finding_message for a20
col more_info for a30
col finding_name for a20
col RATIONALE_MESSAGE for a25
col ATTR5 for a40
col raccomandation_type for a15
col RATIONAL_TYPE for a15
set pages 99

SELECT 
      a.owner
    , t.rationale_id
    , a.message action_message
    , r.type raccomandation_type
    , f.finding_name
    , f.type finding_type
    , f.message finding_message
    , f.more_info
    , t.message rationale_message
    , t.type rational_type
    , t.attr5
FROM 
      dba_advisor_actions         a
    , dba_advisor_recommendations r
    , dba_advisor_findings        f
    , dba_advisor_rationale       t
WHERE
     a.task_name  = 'task_select'
AND  a.task_id    = r.task_id
AND  a.rec_id     = r.rec_id
AND  a.task_id    = t.task_id
AND  a.rec_id     = t.rec_id
AND  f.task_id    = r.task_id
AND  f.finding_id = r.finding_id
ORDER BY
     t.rationale_id;

You can find the output here (quey 1, section).

Interesting thing is that SQL Profile is just a "HINT" saved into Data Dictionary. This HINT is visible queryng SQLOBJ$DATA, table.

col outline_hints for a30
select hint as outline_hints
   from (select p.name, p.signature, p.category, row_number()
         over (partition by sd.signature, sd.category order by sd.signature) row_num,
         extractValue(value(t), '/hint') hint
         from sqlobj$data sd, dba_sql_profiles p,
              table(xmlsequence(extract(xmltype(sd.comp_data),
                                  '/outline_data/hint'))) t
                                    where sd.obj_type = 1
   and p.signature = sd.signature
   and p.category = sd.category
   and p.name like ('profile_7srkyyv9jxhzm'))
   order by row_num;  

OUTLINE_HINTS
------------------------------
PARALLEL


Reference.

You can find more about HINTs of SQL Profile, in a Christian Antognini's pdf (page 22), and in him another post. In the pdf Antognini show the reletionship between data dictionary views. Keep in mind that "As of Oracle Database 11g the previous query can no longer be used. In fact, the data dictionary has been changed. The tables SQLPROF$ and SQLPROF$ATTR no longer exist. As of Oracle Database 11g the information is stored in the tables SQLOBJ$ and SQLOBJ$DATA".


Also, there are a lot of post by Kerry Osborne. Here I link just 3 of them:

• How to Lock SQL Profiles Generated by SQL Tuning Advisor  (November 30, 2010)
• Single Hint SQL Profiles (February 1, 2010)
• SQL Profile (April 4, 2009)

SQL Profile: Case 1 - Part 1: Create/Execute/Report/Accept

SQL Profile: Case 1 - Part 1: Create/Execute/Report/Accept

SQL Profile: Case 1 - Part 2: Data Dictionary

Case 1: The statement is never been run. In this case, we obviously know the sql text.

For this post, please see this for understand the tables envolved.

Suppose you want to run following query

select 
      ooo.owner
    , oao.object_name 
from 
      OSL_ALL_OBJECTS   oao 
join  OSL_OWNER_OBJECTS ooo 
on
      (ooo.object_id = oao.object_id);

and you never ran it. So you want that SQL Tuning Advisor, perform a tuing task for you.

Usually, you have to run 4 steps:

(a) Create a tuning task
(b) Execute the tuning task
(c) Report the tuning task
(d) Accept the tuning task

You have to run the last one, if you accept what the advisor tell you.



Step a) Create a tuning task

declare
    task_name VARCHAR2(30);
    sqltext CLOB;
begin
    sqltext := q'[select ooo.owner, oao.object_name ]';
    sqltext := sqltext||q'[from OSL_ALL_OBJECTS oao join OSL_OWNER_OBJECTS ooo ]';
    sqltext := sqltext|| q'[on (ooo.object_id=oao.object_id)]';
    
    task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
        sql_text => sqltext,
        user_name => 'ASALZANO',
        scope => 'COMPREHENSIVE',
        time_limit => 30, -- nr of seconds of analisys
        task_name => 'task_select',
        description => 'Task to tune a query');
end;
/

PL/SQL procedure successfully completed.

The "task_name" is just a name that I was given. You have to choose a different name (if you want).



Step b) Execute the tuning task

BEGIN
    DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'task_select');
END;
/



Step c) Report the tuning task

set long 10000
set longchunksize 1000
set linesize 230
set heading off

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('task_select') from DUAL;

set heading on

You can find the full output in this file. What you see?

• The original plan

1- Original
-----------
Plan hash value: 3252867984

------------------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |  9999K|   371M|       | 16476   (1)| 00:00:03 |
|*  1 |  HASH JOIN         |                   |  9999K|   371M|  7712K| 16476   (1)| 00:00:03 |
|   2 |   TABLE ACCESS FULL| OSL_OWNER_OBJECTS |   262K|  4613K|       |   113   (1)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| OSL_ALL_OBJECTS   |  9999K|   200M|       |  5516   (1)| 00:00:01 |
------------------------------------------------------------------------------------------------

• The reccomandation:

2- Using Parallel Execution

---------------------------
Plan hash value: 3357147682

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name              | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                   |  9999K|   371M|  3136   (1)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR       |                   |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM) | :TQ10000          |  9999K|   371M|  3136   (1)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|*  3 |    HASH JOIN          |                   |  9999K|   371M|  3136   (1)| 00:00:01 |  Q1,00 | PCWP |            |
|   4 |     TABLE ACCESS FULL | OSL_OWNER_OBJECTS |   262K|  4613K|    63   (2)| 00:00:01 |  Q1,00 | PCWP |            |
|   5 |     PX BLOCK ITERATOR |                   |  9999K|   200M|  3063   (1)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |      TABLE ACCESS FULL| OSL_ALL_OBJECTS   |  9999K|   200M|  3063   (1)| 00:00:01 |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------------------------

Step d) Accept the tuning task

In the "SQL Profile Finding" section, you find the command that  you have to run, if you want to accept the reccomandation:

execute dbms_sqltune.accept_sql_profile( -
      task_name => 'task_select' -
    , task_owner => 'ASALZANO' -
    , replace => TRUE
    , profile_type => DBMS_SQLTUNE.PX_PROFILE);


 I prefere to give to the SQL Profile a my name, so I mofify a little the previous statement:

execute dbms_sqltune.accept_sql_profile( -
      task_name => 'task_select'         -
    , task_owner => 'ASALZANO'           -
    , replace => TRUE                    -
    , name => 'profile_7srkyyv9jxhzm'    -
    , profile_type => DBMS_SQLTUNE.PX_PROFILE);



Delete/Disable/Modify SQL Profile

If you wnat to drop or modify the SQL Profile and/or the Tuning task, you have to run DBMS_SQLTUNE package. For my example:

• Drop the Tuning task

exec DBMS_SQLTUNE.DROP_TUNING_TASK ('task_select')

• Drop the SQL Profile

exec DBMS_SQLTUNE.DROP_SQL_PROFILE ('profile_7srkyyv9jxhzm')

• Disable (modify) the SQL Profile

exec DBMS_SQLTUNE.ALTER_SQL_PROFILE (  -
      name =>  'profile_7srkyyv9jxhzm' -
    , attribute_name => 'STATUS'       -
    , value =>  'DISABLED')



SQL Profile: Case 1 - Part 2: Data Dictionary

SQL Profile: Enviroment

In order to show the SQL Profile cases, I have created a little environment.

This post is for my SQL Profile series

drop table OSL_ALL_OBJECTS;

drop table OSL_OWNER_OBJECTS;

create table OSL_ALL_OBJECTS as

    with generator as (select /*+ materialize */ 

        OBJECT_ID, OBJECT_NAME, OBJECT_TYPE 

        from dba_objects where rownum<10000 font="">

    select a.OBJECT_ID, a.OBJECT_NAME, a.OBJECT_TYPE

    from generator a, generator b

    where rownum > 10000000;

create table OSL_OWNER_OBJECTS as select OBJECT_ID, OWNER from dba_objects;

create index ooo_od_ix on OSL_OWNER_OBJECTS (OBJECT_ID);

create index oao_od_ix on OSL_ALL_OBJECTS (OBJECT_ID);

SQL> desc OSL_ALL_OBJECTS 
 Name                           Null?    Type 
 ------------------------------ -------- ---------------------- 
 OBJECT_ID                      NOT NULL NUMBER 
 OBJECT_NAME                             VARCHAR2(128) 
 SUBOBJECT_NAME                          VARCHAR2(128) 


SQL> desc OSL_MOD2_OWNER 
 Name                           Null?    Type 
 ------------------------------ -------- ---------------------- 
 OBJECT_ID                      NOT NULL NUMBER 
 OWNER                                   VARCHAR2(128)


SQL> select count(*) from OSL_OWNER_OBJECTS;

  COUNT(*)
----------
    132364


SQL> select count(*) from OSL_ALL_OBJECTS;

  COUNT(*)
----------
    264746

SQL Profile

Abstract
A SQL statement’s execution plan can change. The lack of a guarantee that a changed plan will always better lead some customers to several ways to manage this issue:

• lock the optimizer statistics
• lock the execution plans, using stored outlines

Starting with Oracle 10g, we have several options to respond to plan changing. One of these is SQL Profile, or more correctly SQL Tuning Advisor.



Introduction
From manual’s page:

SQL Tuning Advisor is SQL diagnostic software in the Oracle Database Tuning Pack. It is a mechanism for resolving problems related to suboptimally performing SQL statements. Use SQL Tuning Advisor to obtain recommendations for improving performance. Tuning recommendations include also the creation of SQL profiles.

Here, I want to explain how to generate a SQL Profile both using and not using SQL Tuning Advisor.

There are 5 scenarios on which we can work

1. The statement is never been run. In this case, we obviously know the sql text.
2. The statement is in SQL Area but have a wrong execution plan
3. The statement is in AWR repository 
4. The statement is in SQL Area and it have two or more execution plan 
5. The statement is neither in SQL Area nor in AWR 

With the first three, we use the SQL Tuning Advisor. With the last two, we create manually the SQL Profile.

I think that those scenarios cover all possible cases. In the next posts, I'll discuss all 5 cases.



Create SQL Profile using SQL Tuning Advisor (cases 1-3)
You can interact with SQL Tuning Advisor through DBMS_SQLTUNE package.

When you invoice SQL Tuning Advisor you have to follow 4 steps:
(a) Create a tuning task: This step create a task
(b) Execute the tuning task: This step analyze the issue and provide recommendations
(c) Report the tuning task: This step show the recommendations
(d) Accept the SQL Profile: This step accept the recommendations



Create SQL Profile without using SQL Tuning Advisor (cases 4-5) 
With respect to the previous case, when you run SQL Tuning Advisor manually, you need only the step (d). This is because in this case you already know all information (HINTs) in order to create a SQL Profile.


SQL Profile: The statement is never been run Part 1/Part 2
SQL Profile: The statement is in SQL Area but have a wrong execution plan (under construction)
SQL Profile: The statement is in AWR repository (under construction)
SQL Profile: The statement is in SQL Area and it have two or more execution plan (under construction)
SQL Profile: The statement is neither in SQL Area nor in AWR (under construction)

Access before the filter

This post is about what happens during anti-join operation.

Usually, when there is a JOIN+FILTER, the FILTER is made before the JOIN (the access). This is because in this way the CBO can JOIN only a subset of rows of a table with another one.

But there is a case when the FILTER is applied after the ACCESS.

In this post, I show you when it happens.

For this example, I created 2 tables. Here you can find the script for creating and populating them.

These are their contents:

NAMES table

DETAILS table

The ID column is the one I use in order to join the tables.

Well. What happens when I make a join between those two tables?

select 

       n.id

     , n.name

     , n.surname

     , d.street

  from names n 

  join details d 

    on (n.id = d.id);

Here it is.

Join 01

Now I add a filter to my query

select 

       n.id

     , n.name

     , n.surname

     , d.street

  from names n 

  join details d 

    on (n.id = d.id)

 where n.name='Diana';

This is the execution plan

Join 02

This is exactly what I aspect. The filter is before of the access (the join).

In "Join 02" picture, the CBO apply the filter to the NAMES table and then make the join. Obviously, the access is made through the "ID" columns of both tables.

For my goal, I change a little the where condition:

select 

       n.id

     , n.name

     , n.surname

     , d.street

  from names n 

  join details d 

    on (n.id = d.id)

 where d.street is NULL;

This is the execution plan

Join 03

As usual, the filter is applied before the join (the access). The steps are applied in this order:

1. FILTER (id 2 of execution plan) => D.STREET IS NULL
2. ACCESS (id 1 of execution plan) at row 1 => N.ID=D.ID

At this point I re-run all previous statements, using a (LEFT) OUTER JOIN

select 

       n.id

     , n.name

     , n.surname

     , d.street

  from names n 

  left join details d 

    on (n.id = d.id);

Join 04

The difference between the select, are;

1. Join 01: ACCESS => N.ID=D.ID
2. Join 04: ACCESS => N.ID=D.ID (+)

Using the filter....

select 

       n.id

     , n.name

     , n.surname

     , d.street

  from names n 

  left join details d 

    on (n.id = d.id)

 where n.name='Diana';

Join 05

... the ACCESS and the FILTER are the same (more or less)

1. Join 02: ACCESS => N.ID=D.ID    / FILTER => N.NAME='DIANA'
2. Join 05: ACCESS => N.ID=D.ID(+) / FILTER => N.NAME='DIANA'

For the last step, I apply the filter:

select 

       n.id

     , n.name

     , n.surname

     , d.street

  from names n 

  left join details d 

    on (n.id = d.id)

 where d.street is NULL;

Join 06

Here is the point. How you can see in this last case, the access (the join) is before the filter. It is the opposite of the Join 03. This is because now we make an ANTI (OUTER) JOIN. This is the order of operations:

1. ACCESS, (id 2 of execution plan) => N.ID=D.ID(+)
2. FILTER, (id 1 of execution plan) => D.STREET IS NULL

vmstat: average output

The OraPub community is very active. We chat via slack.

Someone asked a script, in order to average the output of vmstat command.

In order to do this, I made a python script. You can find it, here.

You can use it in tow ways.

1. Save the output of vmstat into a file, then use my script
2. Run my script interactively

• Save the output of vmstat into a file, then use my script

i. run vmstat and save the output

oracle> vmstat 5 5 > vmstat out

ii. Use my script

oracle> cat vmstat.out|vmstat.py

============

 Samples: 4

============

Avg Proc   : 30.2 (Sum=121, Min=18, Max=37)

Avg User   : 19.0 (Sum=76, Min=18, Max=20)

Avg System : 10.8 (Sum=43, Min=10, Max=11)

Avg Idle   : 67.2 (Sum=269, Min=65, Max=68)

Avg Waiting:  3.0 (Sum=12, Min=1, Max=5)

Avg Stolen :  0.0 (Sum=0, Min=0, Max=0)

• Run my script interactively

i. run vmstat and pipe my script

oracle> vmstat 5 5 |vmstat.py

============

 Samples: 4

============

Avg Proc   : 46.5 (Sum=186, Min=22, Max=61)

Avg User   : 22.5 (Sum=90, Min=21, Max=25)

Avg System : 14.8 (Sum=59, Min=14, Max=16)

Avg Idle   : 57.2 (Sum=229, Min=49, Max=60)

Avg Waiting:  5.2 (Sum=21, Min=3, Max=10)

Avg Stolen :  0.0 (Sum=0, Min=0, Max=0)

It works on Linux, Solaris, AIX and HP-UX.

I also made a script that read the vmstat output from the Oracle OS Watcher. Here an example

oracle> cat myserver_vmstat_17.03.15.1300.dat |osWatchVmstat.py
============
 Samples: 162
============
Avg Proc   : 40.2 (Sum=6511, Min=37, Max=37)
Avg User   : 14.9 (Sum=2420, Min=22, Max=22)
Avg System :  8.2 (Sum=1336, Min=16, Max=16)
Avg Idle   : 71.4 (Sum=11570, Min=41, Max=41)
Avg Waiting:  5.4 (Sum=868, Min=22, Max=22)
Avg Stolen :  0.0 (Sum=0, Min=0, Max=0)

where "myserver_vmstat_17.03.15.1300.dat" is the output from OS Watch

HINTs from V$SQL_PLAN

In this post I want to understand how to extract the hint, querying the V$SQL_PLAN.

The select I use, have to return just one row, otherwise

If it return more than one row, then you have the following exception

ORA-01427: single-row subquery returns more than one row

If it return NULL, then you have the following exception

ORA-06502: PL/SQL: numeric or value error

ORA-06512: at "SYS.XMLTYPE", line 272

ORA-06512: at line 1

Investigating SQL_ID= '93hwgvnjag3sz', I have more than 1 row in V$SQL_PLAN

select SQL_ID,CHILD_NUMBER,OTHER_XML 
from v$sql_plan 
where sql_id ='93hwgvnjag3sz';

For this reason, I have to modify the statement as

select other_xml

from gv$sql_plan

where sql_id       = '93hwgvnjag3sz'

  and child_number = 0

  and other_xml    is not null;

In order to understand how this xml is composed, I manually format the output

So I have two sections: "other_xml" and "outline_data". Because I want to get just the hints, I rewrite the query as

select xmltype(other_xml).extract('/other_xml/outline_data')

from v$sql_plan

where sql_id       = '93hwgvnjag3sz'

  and child_number = 0

  and other_xml    is not null;

and if I reformat the output ......

It's not enough, I have to go down another level. To do this I rewrite the query

select xmltype(other_xml).extract('/other_xml/outline_data/hint')

from v$sql_plan

where sql_id       = '93hwgvnjag3sz'

  and child_number = 0

  and other_xml    is not null;

Because I want to see the output in better way, I use XMLTABLE function

select *

from xmltable ('/*' passing (

                             select xmltype(other_xml).extract('/other_xml/outline_data/hint')

                             from v$sql_plan

                             where sql_id       = '93hwgvnjag3sz'

                               and child_number = 0

                               and other_xml    is not null)

);

XMLTABLE function, change the output of xml in an "xml table" (I don't know if it's correct) and the output is well formatted. In order to simplify the statement, I move the logic into outer select.

select *

from xmltable ('/other_xml/outline_data/hint' passing (

                                              select xmltype(other_xml)

                                              from v$sql_plan

                                              where sql_id     = '93hwgvnjag3sz'

                                              and child_number = 0

                                              and other_xml    is not null)

);

(obviously, the output is the same). At this point, I extract the hints from the XML

select extractvalue(value(xmltab), '.') hints

  from xmltable ('/other_xml/outline_data/hint' passing (

                                                select xmltype(other_xml)

                                                from v$sql_plan

                                                where sql_id     = '93hwgvnjag3sz'

                                                and child_number = 0

                                                and other_xml    is not null)

) xmltab;

Cool!!!