Oracle database experiences

Oracle database blog

Category Archives: Database Monitoring

Average Active Sessions – Portable Light Sampling Sessions – Part II

Leave a comment Posted by mourmansk on August 1, 2016

Scope

In the previous post, we have sampled active sessions in the global temporary table PLAN_TABLE. If we want to display the average active sessions like in the part XXI, we will have first to process the data. Nevertheless, in this case we could not use directly the view v$aas_light_sampling_sessions. Instead, we will use a SELECT query based on this view and stored in the PLAN_TABLE table.

aas_light_sampling_sessions

The underlying SELECT of the view v$aas_light_sampling_sessions will be stored in the table PLAN_TABLE. Basically the table lss$_sampling_session will be replaced by the following sub-query:

SELECT lss."SESSION_STATE"                           session_state, 
       lss."WAIT_CLASS#"                             wait_class#,
       TO_DATE(lss."SAMPLE_TIME",'YYYYMMDDHH24MISS') sample_time
FROM plan_table,
     XMLTABLE('/ROW'
     PASSING xmltype(plan_table.other_xml)
     COLUMNS 
         "SESSION_STATE" CHAR(1)      PATH 'SESSION_STATE',
         "WAIT_CLASS#"   NUMBER       PATH 'WAIT_CLASSH',
         "SAMPLE_TIME"   VARCHAR2(16) PATH 'SAMPLE_TIME') 
     lss
WHERE id > 0
  AND id IS NOT NULL;

The XML data field values are available as column values with the XMLTABLE function. You will find below a SQL script to store the SELECT statement linked to aas_light_sampling_sessions:

-- =======================================================================
--  File Name     : aas_light_sampling_sessions.sql
--  Area          :
--  Description   : Portable Light Sampling Sessions
--  Author        : Arnaud Fargues
--  Tested On     : 12.1, 11.2
--  Plateform     : 
--  Requires      : 
--  Creation Date : 14.05.2016
--  Modified      : 31.07.2016  
-- =======================================================================

INSERT INTO plan_table(object_name,other_xml)
VALUES ('aas_light_sampling_sessions',TO_CLOB('
SELECT sample_yyyymmddhhmi,
SUBSTR(sample_yyyymmddhhmi,9,2) || '':'' || SUBSTR(sample_yyyymmddhhmi,11,2) BEGIN_TIME,
aas1 C_CPU,
aas1+aas2 C_OTHER,
aas1+aas2+aas3 C_APPLICATION,
aas1+aas2+aas3+aas4 C_CONFIGURATION,
aas1+aas2+aas3+aas4+aas5 C_ADMINISTRATIVE,
aas1+aas2+aas3+aas4+aas5+aas6 C_CONCURRENCY,
aas1+aas2+aas3+aas4+aas5+aas6+aas7 C_COMMIT,
aas1+aas2+aas3+aas4+aas5+aas6+aas7+aas8 C_NETWORK,
aas1+aas2+aas3+aas4+aas5+aas6+aas7+aas8+aas9 C_USER_IO,
aas1+aas2+aas3+aas4+aas5+aas6+aas7+aas8+aas9+aas10 C_SYSTEM_IO,
aas1+aas2+aas3+aas4+aas5+aas6+aas7+aas8+aas9+aas10+aas11 C_SCHEDULER,
aas1+aas2+aas3+aas4+aas5+aas6+aas7+aas8+aas9+aas10+aas11+aas12 C_CLUSTER,
aas1+aas2+aas3+aas4+aas5+aas6+aas7+aas8+aas9+aas10+aas11+aas12+aas13 C_QUEUEING,
MAX(CEIL(aas1+aas2+aas3+aas4+aas5+aas6+aas7+aas8+aas9+aas10+aas11+aas12+aas13)) OVER() MAX_AAS
FROM (SELECT sample_yyyymmddhhmi,
TRUNC((ccpu/(nb_samples)),3) aas1,
TRUNC((cother/(nb_samples)),3) aas2,
TRUNC((capplication/(nb_samples)),3) aas3,
TRUNC((cconfiguration/(nb_samples)),3) aas4,
TRUNC((cadministrative/(nb_samples)),3) aas5,
TRUNC((cconcurrency/(nb_samples)),3) aas6,
TRUNC((ccommit/(nb_samples)),3) aas7,
TRUNC((cnetwork/(nb_samples)),3) aas8,
TRUNC((cuser_io/(nb_samples)),3) aas9,
TRUNC((csystem_io/(nb_samples)),3) aas10, 
TRUNC((cscheduler/(nb_samples)),3) aas11, 
TRUNC((ccluster/(nb_samples)),3) aas12, 
TRUNC((cqueueing/(nb_samples)),3)aas13
FROM (SELECT NVL(co_base_qry.sample_yyyymmddhhmi,time_window.start_time) sample_yyyymmddhhmi,
NVL(co_base_qry.nb_samples,1) nb_samples,
NVL(co_base_qry.ccpu,0) ccpu,
NVL(co_base_qry.cother,0) cother,
NVL(co_base_qry.capplication,0) capplication,
NVL(co_base_qry.cconfiguration,0) cconfiguration,
NVL(co_base_qry.cadministrative,0) cadministrative,
NVL(co_base_qry.cconcurrency,0) cconcurrency,
NVL(co_base_qry.ccommit,0) ccommit,
NVL(co_base_qry.cnetwork,0) cnetwork,
NVL(co_base_qry.cuser_io,0) cuser_io,
NVL(co_base_qry.csystem_io,0) csystem_io,
NVL(co_base_qry.cscheduler,0) cscheduler,
NVL(co_base_qry.ccluster,0) ccluster,
NVL(co_base_qry.cqueueing,0)cqueueing
FROM (SELECT TO_CHAR(SYSDATE - (LEVEL/24/60),''YYYYMMDDHH24MI'') start_time
FROM DUAL 
CONNECT BY LEVEL <= 61
) time_window
LEFT OUTER JOIN 
(SELECT TO_CHAR(sample_time,''YYYYMMDDHH24MI'') sample_yyyymmddhhmi,
30 nb_samples,
COUNT(DECODE(DECODE(session_state,''C'',-1,wait_class#),-1,1)) ccpu,
COUNT(DECODE(DECODE(session_state,''C'',-1,wait_class#),0,1)) cother,
COUNT(DECODE(DECODE(session_state,''C'',-1,wait_class#),1,1)) capplication,
COUNT(DECODE(DECODE(session_state,''C'',-1,wait_class#),2,1)) cconfiguration,
COUNT(DECODE(DECODE(session_state,''C'',-1,wait_class#),3,1)) cadministrative,
COUNT(DECODE(DECODE(session_state,''C'',-1,wait_class#),4,1)) cconcurrency,
COUNT(DECODE(DECODE(session_state,''C'',-1,wait_class#),5,1)) ccommit,
COUNT(DECODE(DECODE(session_state,''C'',-1,wait_class#),7,1)) cnetwork,
COUNT(DECODE(DECODE(session_state,''C'',-1,wait_class#),8,1)) cuser_io,
COUNT(DECODE(DECODE(session_state,''C'',-1,wait_class#),9,1)) csystem_io,
COUNT(DECODE(DECODE(session_state,''C'',-1,wait_class#),10,1)) cscheduler,
COUNT(DECODE(DECODE(session_state,''C'',-1,wait_class#),11,1)) ccluster,
COUNT(DECODE(DECODE(session_state,''C'',-1,wait_class#),12,1)) cqueueing
FROM (
SELECT lss."SESSION_STATE" session_state, 
lss."WAIT_CLASS#" wait_class#,
TO_DATE(lss."SAMPLE_TIME",''YYYYMMDDHH24MISS'') sample_time
FROM plan_table,
XMLTABLE(''/ROW''
PASSING xmltype(plan_table.other_xml)
COLUMNS 
"SESSION_STATE" CHAR(1) PATH ''SESSION_STATE'',
"WAIT_CLASS#" NUMBER PATH ''WAIT_CLASSH'',
"SAMPLE_TIME" VARCHAR2(16) PATH ''SAMPLE_TIME'') 
lss
WHERE id > 0
AND id IS NOT NULL)
GROUP BY TO_CHAR(sample_time,''YYYYMMDDHH24MI'')
) co_base_qry
ON time_window.start_time=co_base_qry.sample_yyyymmddhhmi
)
) ORDER BY sample_yyyymmddhhmi'));

COMMIT;

As you can see, the query has less than 4000 characters. It is due to the fact that we are using the function DBMS_XMLGEN.GETXMLTYPE in a way that can handle only queries with less than 4000 characters.

Modified SVG source query

We will use the query defined in the part XXI as a starting point. We will modify the source query as follow:

SELECT XMLTRANSFORM
   (DBMS_XMLGEN.GETXMLTYPE(other_xml)
    ...
FROM plan_table
WHERE object_name='aas_light_sampling_sessions';

XSLT variables

The variable “graph_name” is set to ‘PLSS Active Sessions – Waiting + Working’,
The variable “cpu_count” is set to ‘4’.

Modified query

SET LINESIZE      2000
SET LONGCHUNKSIZE 30000
SET LONG          30000

SET FEEDBACK OFF
SET VERIFY   OFF
SET PAGESIZE 0

SET HEADING OFF

SPOOL generate_svg_plss.svg

SELECT XMLTRANSFORM
   (DBMS_XMLGEN.GETXMLTYPE(other_xml)
,      XMLTYPE.CREATEXML
   (TO_CLOB(
    '<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
     <xsl:variable name="margin_top">20</xsl:variable>
     <xsl:variable name="margin_bottom">30</xsl:variable>
     <xsl:variable name="margin_left">40</xsl:variable>
     <xsl:variable name="margin_right">100</xsl:variable>
     <xsl:variable name="bar_width">10</xsl:variable>
     <xsl:variable name="graph_width"><xsl:value-of select="600+$margin_left+$margin_right"/></xsl:variable>
     <xsl:variable name="graph_height"><xsl:value-of select="100+$margin_top+$margin_bottom"/></xsl:variable>
     <xsl:variable name="graph_name">PLSS Active Sessions - Waiting + Working</xsl:variable>
     <xsl:variable name="graph_unit">Session Count</xsl:variable>
     <xsl:variable name="yval_max"><xsl:value-of select="/descendant::MAX_AAS[position()=1]"/></xsl:variable>
     <xsl:variable name="yval_min">0</xsl:variable>
     <xsl:variable name="cpu_count"><xsl:value-of select="4"/></xsl:variable>
       <xsl:template match="/">'
           )
    || 
    TO_CLOB(
         '<svg xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#" xmlns="http://www.w3.org/2000/svg" width="{$graph_width}" height="{$graph_height}">
           <text x="{$margin_left+1}" y="{($margin_top)-5}" style="fill: #000000; stroke: none;font-size:10px;text-anchor=start"><xsl:value-of select="$graph_name"/></text>
           <text x="{($margin_bottom)-($graph_height)}" y="10" transform="rotate(-90)" style="fill: #000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="$graph_unit"/></text>
           <line x1="{($margin_left)-5}" y1="{($graph_height)-($margin_bottom)-0}"   x2="{($graph_width)-($margin_right)+5}" y2="{($graph_height)-($margin_bottom)-0}"  style="stroke:lightblue;stroke-width:1" />
           <line x1="{($margin_left)-5}" y1="{($graph_height)-($margin_bottom)-25}"  x2="{($graph_width)-($margin_right)+5}" y2="{($graph_height)-($margin_bottom)-25}" style="stroke:lightblue;stroke-width:1" />
           <line x1="{($margin_left)-5}" y1="{($graph_height)-($margin_bottom)-50}"  x2="{($graph_width)-($margin_right)+5}" y2="{($graph_height)-($margin_bottom)-50}" style="stroke:lightblue;stroke-width:1" />
           <line x1="{($margin_left)-5}" y1="{($graph_height)-($margin_bottom)-75}"  x2="{($graph_width)-($margin_right)+5}" y2="{($graph_height)-($margin_bottom)-75}" style="stroke:lightblue;stroke-width:1" />
           <line x1="{($margin_left)-5}" y1="{($graph_height)-($margin_bottom)-100}" x2="{($graph_width)-($margin_right)+5}" y2="{($graph_height)-($margin_bottom)-100}" style="stroke:lightblue;stroke-width:1" />
           <text x="{($margin_left)-24}" y="{($graph_height)-($margin_bottom)-2}"   style="fill: #000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="format-number(($yval_min),''00.00'')"/></text>
           <text x="{($margin_left)-24}" y="{($graph_height)-($margin_bottom)-25}"  style="fill: #000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="format-number((($yval_min)+(1*(($yval_max)-($yval_min)) div 4)),''00.00'')"/></text>
           <text x="{($margin_left)-24}" y="{($graph_height)-($margin_bottom)-50}"  style="fill: #000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="format-number((($yval_min)+((($yval_max)-($yval_min)) div 2)),''00.00'')"/></text>
           <text x="{($margin_left)-24}" y="{($graph_height)-($margin_bottom)-75}"  style="fill: #000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="format-number((($yval_min)+(3*(($yval_max)-($yval_min)) div 4)),''00.00'')"/></text>
           <text x="{($margin_left)-24}" y="{($graph_height)-($margin_bottom)-100}" style="fill: #000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="format-number(($yval_max),''00.00'')"/></text>
           <line x1="{$margin_left}" y1="{($graph_height)-($margin_bottom)}" x2="{$margin_left}" y2="{($graph_height)-($margin_bottom)-100}" style="stroke:lightblue;stroke-width:1" />'
           )
    || 
    TO_CLOB(
          '<xsl:for-each select="ROWSET/ROW/BEGIN_TIME">
             <xsl:choose>
               <xsl:when test="(position()-1) mod 5=0">
                 <text x="{($margin_left)-9+($bar_width*(position()-1))}" y="{($graph_height)-($margin_bottom)+12}" style="fill: #000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="self::node()"/></text>
                 <line x1="{($margin_left)+($bar_width*(position()-1))}" y1="{($graph_height)-($margin_bottom)+4}" x2="{($margin_left)+($bar_width*(position()-1))}" y2="{($graph_height)-($margin_bottom)-100}" style="stroke:lightblue;stroke-width:1" /> 
               </xsl:when>
               <xsl:otherwise>
                 <line x1="{($margin_left)+($bar_width*(position()-1))}" y1="{($graph_height)-($margin_bottom)+3}" x2="{($margin_left)+($bar_width*(position()-1))}" y2="{($graph_height)-($margin_bottom)+0}" style="stroke:lightblue;stroke-width:1" /> 
               </xsl:otherwise>
             </xsl:choose>    
           </xsl:for-each>
           <xsl:variable name="v_path0">
             <xsl:for-each select="ROWSET/ROW/C_CPU">
               <xsl:variable name="x_val0">
                 <xsl:value-of select="$margin_left+$bar_width*(position()-1)"/>
               </xsl:variable>
               <xsl:variable name="y_val0">
                 <xsl:value-of select="round(($graph_height)-($margin_bottom)-((($yval_min)-(self::node()))*(100 div (($yval_min)-($yval_max)))))"/>
               </xsl:variable>
               <xsl:value-of select="concat($x_val0,'','',$y_val0,'' '')"/>
             </xsl:for-each>
           </xsl:variable>
           <polygon points="{$v_path0} {$x_val0},{($graph_height)-($margin_bottom)} {$margin_left},{($graph_height)-($margin_bottom)}" style="fill:limegreen;stroke:none;stroke-width:1" />
           <xsl:variable name="v_path1">
             <xsl:for-each select="ROWSET/ROW/C_OTHER">
               <xsl:sort select="position()" order="descending" data-type="number"/>
               <xsl:variable name="x_val1">
                 <xsl:value-of select="$margin_left+$bar_width*(last()-position())"/>
               </xsl:variable>
               <xsl:variable name="y_val1">
                 <xsl:value-of select="round(($graph_height)-($margin_bottom)-((($yval_min)-(self::node()))*(100 div (($yval_min)-($yval_max)))))"/>
               </xsl:variable>
               <xsl:value-of select="concat($x_val1,'','',$y_val1,'' '')"/>
             </xsl:for-each>
           </xsl:variable>
           <polygon points="{$v_path0}{$v_path1}" style="fill:hotpink;stroke:none;stroke-width:1" />
           <xsl:variable name="v_path2">
             <xsl:for-each select="ROWSET/ROW/C_APPLICATION">
               <xsl:variable name="x_val2">
                 <xsl:value-of select="$margin_left+$bar_width*(position()-1)"/>
               </xsl:variable>
               <xsl:variable name="y_val2">
                 <xsl:value-of select="round(($graph_height)-($margin_bottom)-((($yval_min)-(self::node()))*(100 div (($yval_min)-($yval_max)))))"/>
               </xsl:variable>
               <xsl:value-of select="concat($x_val2,'','',$y_val2,'' '')"/>
             </xsl:for-each>
           </xsl:variable>
           <polygon points="{$v_path1}{$v_path2}" style="fill:indianred;stroke:none;stroke-width:1" />'
           )
   ||
   TO_CLOB(
          '<xsl:variable name="v_path3">
             <xsl:for-each select="ROWSET/ROW/C_CONFIGURATION">
               <xsl:sort select="position()" order="descending" data-type="number"/>
               <xsl:variable name="x_val3">
                 <xsl:value-of select="$margin_left+$bar_width*(last()-position())"/>
               </xsl:variable>
               <xsl:variable name="y_val3">
                 <xsl:value-of select="round(($graph_height)-($margin_bottom)-((($yval_min)-(self::node()))*(100 div (($yval_min)-($yval_max)))))"/>
               </xsl:variable>
               <xsl:value-of select="concat($x_val3,'','',$y_val3,'' '')"/>
             </xsl:for-each>
           </xsl:variable>
           <polygon points="{$v_path2}{$v_path3}" style="fill:olive;stroke:none;stroke-width:1" />'
           )
   ||
   TO_CLOB(
          '<xsl:variable name="v_path4">
             <xsl:for-each select="ROWSET/ROW/C_ADMINISTRATIVE">
               <xsl:variable name="x_val4">
                 <xsl:value-of select="$margin_left+$bar_width*(position()-1)"/>
               </xsl:variable>
               <xsl:variable name="y_val4">
                 <xsl:value-of select="round(($graph_height)-($margin_bottom)-((($yval_min)-(self::node()))*(100 div (($yval_min)-($yval_max)))))"/>
               </xsl:variable>
               <xsl:value-of select="concat($x_val4,'','',$y_val4,'' '')"/>
             </xsl:for-each>
           </xsl:variable>
           <polygon points="{$v_path3}{$v_path4}" style="fill:gray;stroke:none;stroke-width:1" />
           <xsl:variable name="v_path5">
             <xsl:for-each select="ROWSET/ROW/C_CONCURRENCY">
               <xsl:sort select="position()" order="descending" data-type="number"/>
               <xsl:variable name="x_val5">
                 <xsl:value-of select="$margin_left+$bar_width*(last()-position())"/>
               </xsl:variable>
               <xsl:variable name="y_val5">
                 <xsl:value-of select="round(($graph_height)-($margin_bottom)-((($yval_min)-(self::node()))*(100 div (($yval_min)-($yval_max)))))"/>
               </xsl:variable>
               <xsl:value-of select="concat($x_val5,'','',$y_val5,'' '')"/>
             </xsl:for-each>
           </xsl:variable>
           <polygon points="{$v_path4}{$v_path5}" style="fill:sienna;stroke:none;stroke-width:1" />
           <xsl:variable name="v_path6">
             <xsl:for-each select="ROWSET/ROW/C_COMMIT">
               <xsl:variable name="x_val6">
                 <xsl:value-of select="$margin_left+$bar_width*(position()-1)"/>
               </xsl:variable>
               <xsl:variable name="y_val6">
                 <xsl:value-of select="round(($graph_height)-($margin_bottom)-((($yval_min)-(self::node()))*(100 div (($yval_min)-($yval_max)))))"/>
               </xsl:variable>
               <xsl:value-of select="concat($x_val6,'','',$y_val6,'' '')"/>
             </xsl:for-each>
           </xsl:variable>
           <polygon points="{$v_path5}{$v_path6}" style="fill:orange;stroke:none;stroke-width:1" />
           <xsl:variable name="v_path7">
             <xsl:for-each select="ROWSET/ROW/C_NETWORK">
               <xsl:sort select="position()" order="descending" data-type="number"/>
               <xsl:variable name="x_val7">
                 <xsl:value-of select="$margin_left+$bar_width*(last()-position())"/>
               </xsl:variable>
               <xsl:variable name="y_val7">
                 <xsl:value-of select="round(($graph_height)-($margin_bottom)-((($yval_min)-(self::node()))*(100 div (($yval_min)-($yval_max)))))"/>
               </xsl:variable>
               <xsl:value-of select="concat($x_val7,'','',$y_val7,'' '')"/>
             </xsl:for-each>
           </xsl:variable>
           <polygon points="{$v_path6}{$v_path7}" style="fill:tan;stroke:none;stroke-width:1" />
           <xsl:variable name="v_path8">
             <xsl:for-each select="ROWSET/ROW/C_USER_IO">
               <xsl:variable name="x_val8">
                 <xsl:value-of select="$margin_left+$bar_width*(position()-1)"/>
               </xsl:variable>
               <xsl:variable name="y_val8">
                 <xsl:value-of select="round(($graph_height)-($margin_bottom)-((($yval_min)-(self::node()))*(100 div (($yval_min)-($yval_max)))))"/>
               </xsl:variable>
               <xsl:value-of select="concat($x_val8,'','',$y_val8,'' '')"/>
             </xsl:for-each>
           </xsl:variable>
           <polygon points="{$v_path7}{$v_path8}" style="fill:royalblue;stroke:none;stroke-width:1" />'
           )
   ||
   TO_CLOB(
          '<xsl:variable name="v_path9">
             <xsl:for-each select="ROWSET/ROW/C_SYSTEM_IO">
               <xsl:sort select="position()" order="descending" data-type="number"/>
               <xsl:variable name="x_val9">
                 <xsl:value-of select="$margin_left+$bar_width*(last()-position())"/>
               </xsl:variable>
               <xsl:variable name="y_val9">
                 <xsl:value-of select="round(($graph_height)-($margin_bottom)-((($yval_min)-(self::node()))*(100 div (($yval_min)-($yval_max)))))"/>
               </xsl:variable>
               <xsl:value-of select="concat($x_val9,'','',$y_val9,'' '')"/>
             </xsl:for-each>
           </xsl:variable>
           <polygon points="{$v_path8}{$v_path9}" style="fill:skyblue;stroke:none;stroke-width:1" />
           <xsl:variable name="v_path10">
             <xsl:for-each select="ROWSET/ROW/C_SCHEDULER">
               <xsl:variable name="x_val10">
                 <xsl:value-of select="$margin_left+$bar_width*(position()-1)"/>
               </xsl:variable>
               <xsl:variable name="y_val10">
                 <xsl:value-of select="round(($graph_height)-($margin_bottom)-((($yval_min)-(self::node()))*(100 div (($yval_min)-($yval_max)))))"/>
               </xsl:variable>
               <xsl:value-of select="concat($x_val10,'','',$y_val10,'' '')"/>
             </xsl:for-each>
           </xsl:variable>
           <polygon points="{$v_path9}{$v_path10}" style="fill:lightcyan;stroke:none;stroke-width:1" />'
           )
   ||
   TO_CLOB(
          '<xsl:variable name="v_path11">
             <xsl:for-each select="ROWSET/ROW/C_CLUSTER">
               <xsl:sort select="position()" order="descending" data-type="number"/>
               <xsl:variable name="x_val11">
                 <xsl:value-of select="$margin_left+$bar_width*(last()-position())"/>
               </xsl:variable>
               <xsl:variable name="y_val11">
                 <xsl:value-of select="round(($graph_height)-($margin_bottom)-((($yval_min)-(self::node()))*(100 div (($yval_min)-($yval_max)))))"/>
               </xsl:variable>
               <xsl:value-of select="concat($x_val11,'','',$y_val11,'' '')"/>
             </xsl:for-each>
           </xsl:variable>
           <polygon points="{$v_path10}{$v_path11}" style="fill:lightgray;stroke:none;stroke-width:1" />
           <xsl:variable name="v_path12">
             <xsl:for-each select="ROWSET/ROW/C_QUEUEING">
               <xsl:variable name="x_val12">
                 <xsl:value-of select="$margin_left+$bar_width*(position()-1)"/>
               </xsl:variable>
               <xsl:variable name="y_val12">
                 <xsl:value-of select="round(($graph_height)-($margin_bottom)-((($yval_min)-(self::node()))*(100 div (($yval_min)-($yval_max)))))"/>
               </xsl:variable>
               <xsl:value-of select="concat($x_val12,'','',$y_val12,'' '')"/>
             </xsl:for-each>
           </xsl:variable>
           <polygon points="{$v_path11}{$v_path12}" style="fill:bisque;stroke:none;stroke-width:1" />
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+5}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">CPU</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+0}" width="{10}" height="{6}" fill="limegreen" stroke="black"/> 
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+13}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">Other</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+8}" width="{10}" height="{6}" fill="hotpink" stroke="black"/> 
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+21}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">Application</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+16}" width="{10}" height="{6}" fill="indianred" stroke="black"/>   
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+29}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">Configuration</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+24}" width="{10}" height="{6}" fill="olive" stroke="black"/>   
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+37}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">Administrative</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+32}" width="{10}" height="{6}" fill="gray" stroke="black"/> 
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+45}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">Concurrency</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+40}" width="{10}" height="{6}" fill="sienna" stroke="black"/>'
           ) 
    || 
    TO_CLOB(
          '<text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+53}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">Commit</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+48}" width="{10}" height="{6}" fill="orange" stroke="black"/> 
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+61}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">Network</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+56}" width="{10}" height="{6}" fill="tan" stroke="black"/>  
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+69}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">User IO</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+64}" width="{10}" height="{6}" fill="royalblue" stroke="black"/> 
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+77}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">System IO</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+72}" width="{10}" height="{6}" fill="skyblue" stroke="black"/>
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+85}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">Scheduler</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+80}" width="{10}" height="{6}" fill="lightcyan" stroke="black"/> 
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+93}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">Cluster</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+88}" width="{10}" height="{6}" fill="lightgray" stroke="black"/> 
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+101}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">Queueing</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+96}" width="{10}" height="{6}" fill="bisque" stroke="black"/> 
           <xsl:choose>
             <xsl:when test="$yval_max&gt;$cpu_count">
               <line x1="{($margin_left)-5}" y1="{($graph_height)-($margin_bottom)-((($yval_min)-($cpu_count))*(100 div (($yval_min)-($yval_max))))}" x2="{($graph_width)-($margin_right)+5}" y2="{($graph_height)-($margin_bottom)-((($yval_min)-($cpu_count))*(100 div (($yval_min)-($yval_max))))}" style="stroke-dasharray: 9, 5;stroke:red;stroke-width:1" />
               <text x="{($margin_left)+2}" y="{($graph_height)-($margin_bottom)-((($yval_min)-($cpu_count))*(100 div (($yval_min)-($yval_max))))-2}" style="fill:red; stroke: none;font-size:8px;text-anchor=start">CPU cores</text>
             </xsl:when>
             <xsl:otherwise>
               <text x="{($margin_left)-38}" y="{($graph_height)-($margin_bottom)-93}" style="fill:red; stroke: none;font-size:6px;text-anchor=start"><xsl:value-of select="format-number((($yval_max) div ($cpu_count))*100,''00'')"/>% cpu cores</text>
             </xsl:otherwise>
           </xsl:choose>   
         </svg>
       </xsl:template>
     </xsl:stylesheet>'
           )
   )
   ) 
FROM plan_table
WHERE object_name='aas_light_sampling_sessions';

SPOOL OFF
SET HEADING ON

SET VERIFY   ON
SET FEEDBACK ON

This query will be saved in the file generate_svg_plss.sql.

Sampling + SVG generation

In order to display the average active sessions of the PDB1 container database we will:

Connect to the PDB1 container database as the PLSS user,
Execute the previous script aas_light_sampling_sessions.sql in order to store the aas_light_sampling_sessions query,
Execute the script plss_sampling.sql in order to sample the active sessions every 2 seconds on a one minute period,
Execute the script generate_svg_plss.sql in order to generate a SVG graph,
Repeat the steps 3 and 4 as many times as you want.

You will find below an example of such a script:

-- =======================================================================
--  File Name     : plss_master.sql
--  Area          :
--  Description   : Portable Light Sampling Sessions
--  Author        : Arnaud Fargues
--  Tested On     : 12.1, 11.2
--  Plateform     : 
--  Requires      : 
--  Creation Date : 31.07.2016
--  Modified      :   
-- =======================================================================

SET DEFINE OFF

--
--Load aas_light_sampling_sessions query into PLAN_TABLE
--
@aas_light_sampling_sessions.sql

--
--1
--
@plss_sampling.sql

@generate_svg_plss.sql

--
--2
--
@plss_sampling.sql

@generate_svg_plss.sql

--
--3
--
@plss_sampling.sql

@generate_svg_plss.sql

--
--4
--
@plss_sampling.sql

@generate_svg_plss.sql

--
--5
--
@plss_sampling.sql

@generate_svg_plss.sql

--
--6
--
@plss_sampling.sql

@generate_svg_plss.sql

--
--7
--
@plss_sampling.sql

@generate_svg_plss.sql

--
--8
--
@plss_sampling.sql

@generate_svg_plss.sql

--
--9
--
@plss_sampling.sql

@generate_svg_plss.sql

--
--10
--
@plss_sampling.sql

@generate_svg_plss.sql

Result:

Remark:

You could repeat the steps @plss_sampling.sql and @generate_svg_plss.sql a high number of times and if you want to display the average active sessions every 5 minutes for example, you could copy the file generate_svg_plss.svg every 5 minutes to check this activity.

Database Monitoring, Performance Tuning, PL/SQL 11gR2, 12cR1, Average active sessions, CDB database, CDB$ROOT, CEIL, CLOB, CONNECT BY, Container, DECODE, Global Tempory Table, MAX, NVL, PDB, Performance monitoring, PLAN_TABLE, PLSS, Pluggable Database, Portable Light sampling sessions, SUBSTR, TO_CHAR, TO_DATE, TRUNC, XML, XMLTABLE, XMLType

Average Active Sessions – Portable Light Sampling Sessions – Part I

Leave a comment Posted by mourmansk on May 22, 2016

Scope

In some situations, it might happened that you have access to an Oracle database but you can not set up a Light Sampling Sessions installation because you have limited privileges e.g. you don’t have the CREATE TABLE system privilege.
However with some limited privileges you could display a graph on the Average Active Sessions.
In the rest of this post we will make the assumption that an Oracle user has been created in a 12.1 PDB database.

User creation

Let’s create a user PLSS in the PDB1 container:

@> connect / as sysdba
Connected.
SYS@orcl> ALTER SESSION SET CONTAINER=PDB1;

Session altered.

SYS@orcl> show con_name;

CON_NAME
------------------------------
PDB1
SYS@orcl> CREATE USER PLSS IDENTIFIED BY plss
  2  CONTAINER=CURRENT;

User created.

This user will have only the CREATE SESSION privilege and will have access to the V$SESSION view:

User privileges

SYS@orcl> GRANT CREATE SESSION TO PLSS;

Grant succeeded.

SYS@orcl> GRANT SELECT ON SYS.V_$SESSION TO PLSS;

Grant succeeded.

Plss sampling

As you can see the Oracle user PLSS has limited privileges. How can he store data in the database?
We could use the global temporary table PLAN_TABLE. The data could be stored in this table during the session lifetime.

Basically, we will use an anonymous block similar to the procedure DBMS_SAMPLING_SESSION.start_sampling to record active sessions as following:

-- =======================================================================
--  File Name     : plss_sampling.sql
--  Area          :
--  Description   : Portable Light Sampling Sessions
--  Author        : Arnaud Fargues
--  Tested On     : 12.1, 11.2
--  Plateform     : 
--  Requires      : 
--  Creation Date : 12.05.2016
--  Modified      :   
-- =======================================================================

DECLARE
   v_nb_sample      INTEGER;
   v_index          INTEGER;
   v_cur_date       DATE;
   v_sample_id      NUMBER;
   v_sample_per_min NUMBER;
   v_session_sid    NUMBER;
   v_start_date     DATE;

   v_sampling_interval_sec NUMBER := 2;
   v_sampling_window_min   NUMBER := 1;

BEGIN

   v_sample_per_min := 60 / v_sampling_interval_sec;
   v_nb_sample      := v_sampling_window_min * v_sample_per_min;

   SELECT NVL(MAX(id),0)
   INTO v_sample_id
   FROM plan_table;

   SELECT SYS_CONTEXT ('USERENV', 'SID')
   INTO v_session_sid
   FROM DUAL;

   FOR v_index IN 1..v_nb_sample
   LOOP
      v_sample_id := v_sample_id + 1;

      v_cur_date := SYSDATE;

      INSERT INTO plan_table (id,other_xml)
      SELECT v_sample_id,
             XMLSERIALIZE(CONTENT
         (XMLELEMENT("ROW",
             XMLELEMENT("SAMPLE_ID",v_sample_id ),
             XMLELEMENT("SAMPLE_TIME",TO_CHAR(v_cur_date,'YYYYMMDDHH24MISS')),
             XMLELEMENT("SID",SID),
             XMLELEMENT("SERIALH",SERIAL#),
             XMLELEMENT("SESSION_STATE",decode(state,'WAITING','W','C')),
             XMLELEMENT("USERH",user#),
             XMLELEMENT("TYPE",type),
             XMLELEMENT("COMMAND",command),
             XMLELEMENT("PROGRAM",program),
             XMLELEMENT("MODULE",module),
             XMLELEMENT("ACTION",action),
             XMLELEMENT("SERVICE_NAME",service_name),
             XMLELEMENT("CLIENT_IDENTIFIER",client_identifier),
             XMLELEMENT("EVENTH",event#),
             XMLELEMENT("EVENT",event),
             XMLELEMENT("SEQH",seq#),
             XMLELEMENT("P1",p1),
             XMLELEMENT("P2",p2),
             XMLELEMENT("P3",p3),
             XMLELEMENT("WAIT_CLASSH",wait_class#),
             XMLELEMENT("WAIT_TIME_MICRO",wait_time_micro),
             XMLELEMENT("SQL_ID",sql_id),
             XMLELEMENT("SQL_HASH_VALUE",sql_hash_value),
             XMLELEMENT("SQL_CHILD_NUMBER",sql_child_number) )))
      FROM v$session
      WHERE status='ACTIVE'
        AND ((state = 'WAITING' AND wait_class# <> 6)
              OR (state <> 'WAITING' AND sid <> v_session_sid));

      v_start_date := SYSDATE;
      
      WHILE SYSDATE <= v_start_date + (v_sampling_interval_sec/86400) 
      LOOP
         NULL;
      END LOOP;

   END LOOP;
END ;
/

Remarks:

Every sample will be stored in the column other_xml of PLAN_TABLE as XML data,
The sample_id values will be also stored in the column id of PLAN_TABLE,
The function XMLSERIALIZE is used to convert XML datatype into CLOB datatype,
We have used the function XMLELEMENT instead of XMLFOREST in order to store explicitly NULL values,
The symbol # contained in some column names has been replaced by H because such a symbol is invalid in a XML tag name,
We have replaced the DBMS_LOCK.SLEEP procedure with a WHILE LOOP,
We will record the active sessions every 2 seconds during 1 minute.

Shortly

We will see how to generate a SVG graph with those “temporary data”.

Database Monitoring, Performance Tuning, PL/SQL 11gR2, 12cR1, Average active sessions, CDB database, CDB$ROOT, CLOB, Container, Global Tempory Table, MAX, NVL, PDB, Performance monitoring, PLAN_TABLE, PLSS, Pluggable Database, Portable Light sampling sessions, SYS_CONTEXT, V$SESSION, XML, XMLELEMENT, XMLFOREST, XMLSERIALIZE

Average Active Sessions – Light Sampling Sessions – Database Group – RAC Update

Leave a comment Posted by mourmansk on January 26, 2016

Scope

Back to part XXII, it could be interesting to have a consolidated activity view of non-CDB databases or of PDB pluggable databases belonging to different CDBs.
We will define the concept of “database group”, in which one database will be the primary database (like the CDB$ROOT container database) and the other databases will be defined as secondary databases.

We will take two databases as an example. In our case we will use a CDB database where the CDB$ROOT container will be the primary database and the PDB1 container will be the secondary database. We will also modify the structure of the table lss$_sampling_session in order to handle RAC instances.

Let’s begin with the PDB1 container.

PDB1 – Tablespace modification

We will use the ‘LSS_REPOSITORY’ tablespace that was already created in the post “Average Active Sessions and CDB – Light Sampling Sessions”, but we will increase its size to 150 Mo:

@> connect / as sysdba
Connected.
SYS@orcl> ALTER SESSION SET CONTAINER=PDB1;

Session altered.

SYS@orcl> show con_name;

CON_NAME
------------------------------
PDB1
SYS@orcl> ALTER DATABASE
  2  DATAFILE 'C:\APP\ORACLE\ORADATA\ORCL\PDB1\LSS_REPOSITORY01.DBF' RESIZE 150 M;

Database altered.

PDB1 – Local user creation

Connected to the PDB1 container, we will create the LSS local user:

SYS@orcl> CREATE USER LSS IDENTIFIED BY lss
  2  CONTAINER=CURRENT
  3  DEFAULT TABLESPACE LSS_REPOSITORY
  4  QUOTA UNLIMITED ON LSS_REPOSITORY;

User created.

PDB1 – Base privileges

We will grant the following privileges to the LSS user:

SYS@orcl> GRANT CREATE SESSION      TO LSS;

Grant succeeded.

SYS@orcl> GRANT SELECT_CATALOG_ROLE TO LSS;

Grant succeeded.

SYS@orcl> GRANT CREATE PROCEDURE    TO LSS;

Grant succeeded.

SYS@orcl> GRANT CREATE VIEW         TO LSS;

Grant succeeded.

SYS@orcl> GRANT CREATE TABLE        TO LSS;

Grant succeeded.

PDB1 – Table creation

Let’s now create the table lss$_sampling_session in the LSS schema:

@> connect lss@pdb1
Enter password:
Connected.
LSS@pdb1> show con_name;

CON_NAME
------------------------------
PDB1
LSS@pdb1> CREATE TABLE lss$_sampling_session
  2  AS
  3  SELECT CAST (1 AS NUMBER) sample_id,
  4         CAST (NULL AS DATE) sample_time,
  5         sid,
  6         serial#,
  7         user#,
  8         type,
  9         command,
 10         program,
 11         module,
 12         action,
 13         service_name,
 14         client_identifier,
 15         CAST ('1' AS CHAR(1)) session_state,
 16         state,
 17         event#,
 18         event,
 19         seq#,
 20         p1,
 21         p2,
 22         p3,
 23         wait_class#,
 24         wait_time_micro,
 25         sql_id,
 26         con_id,
 27         inst_id,
 28         sql_hash_value,
 29         sql_child_number,
 30         CAST (1 AS NUMBER) element_id
 31  FROM gv$session
 32  WHERE 1=2;

Table created.

We have added four new columns:

inst_id: to handle RAC instances,
sql_hash_value and sql_child_number: to handle more precisely sql statements,
element_id: to handle the database group. This number must be unique in all the databases that belong to a database group.

PDB1 – Other privileges

Let’s grant the SELECT privilege on the underlying object of gv$session in order to use it in a procedure:

@> connect / as sysdba
Connected.
SYS@orcl> ALTER SESSION SET CONTAINER=PDB1;

Session altered.

SYS@orcl> show con_name;

CON_NAME
------------------------------
PDB1
SYS@orcl> GRANT SELECT ON SYS.GV_$SESSION TO LSS;

Grant succeeded.

Let’s grant the EXECUTE privilege on the DBMS_LOCK package:

SYS@orcl> GRANT EXECUTE ON DBMS_LOCK TO LSS;

Grant succeeded.

PDB1 – Package specifications

Let’s create the package specifications as following:

CREATE OR REPLACE PACKAGE LSS.DBMS_SAMPLING_SESSION AS 

-- =======================================================================
--  Package       : DBMS_SAMPLING_SESSION
--  Description   : Sample active sessions
--  Author        : Arnaud Fargues
--  Creation      : 14.06.2015
--  Modifications : 
-- =======================================================================

   PROCEDURE start_sampling (
      p_sampling_window_min   IN NUMBER DEFAULT 60,
      p_sampling_interval_sec IN NUMBER DEFAULT 5);

END DBMS_SAMPLING_SESSION;
/

PDB1 – Package body

You will find below the package body:

CREATE OR REPLACE PACKAGE BODY LSS.DBMS_SAMPLING_SESSION AS

PROCEDURE start_sampling (
   p_sampling_window_min   IN NUMBER DEFAULT 60,
   p_sampling_interval_sec IN NUMBER DEFAULT 5)

-- =======================================================================
--  Procedure     : start_sampling
--  Description   : Starts sampling active sessions
--  Parameters    : p_sampling_window_min   : sampling period time in minutes
--                  p_sampling_interval_sec : sampling period interval in seconds
--  Author        : Arnaud Fargues
--  Creation      : 14.06.2015
--  Modifications : 10.12.2015 Handle RAC instances
--                  12.01.2016 Add columns sql_hash_value and sql_child_number
--                  22.01.2016 Add column element_id
-- =======================================================================

AS
   v_nb_sample      INTEGER;
   v_index          INTEGER;
   v_cur_date       DATE;
   v_sample_id      NUMBER;
   v_sample_per_min NUMBER;
   v_session_sid    NUMBER;
BEGIN

   v_sample_per_min := 60 / p_sampling_interval_sec;
   v_nb_sample      := p_sampling_window_min * v_sample_per_min;
   
   SELECT NVL(MAX(sample_id),0)
   INTO v_sample_id
   FROM lss$_sampling_session;

   SELECT SYS_CONTEXT ('USERENV', 'SID')
   INTO v_session_sid
   FROM DUAL;

   FOR v_index IN 1..v_nb_sample
   LOOP
      v_sample_id := v_sample_id + 1;

      DBMS_APPLICATION_INFO.SET_MODULE(
         module_name => 'DBMS_SAMPLING_SESSION',
         action_name => 'Sample:'|| TO_CHAR(v_sample_id));

      v_cur_date := SYSDATE;

      INSERT INTO lss$_sampling_session(
         sample_id,
         sample_time,
         inst_id,
         sid,
         serial#,
         session_state,
         state,
         user#,
         type,
         command,
         program,
         module,
         action,
         service_name,
         client_identifier,
         event#,
         event,
         seq#,
         p1,
         p2,
         p3,
         wait_class#,
         wait_time_micro,
         sql_id,
         sql_hash_value,
         sql_child_number,
         con_id,
         element_id)
      SELECT
         v_sample_id,
         v_cur_date,
         inst_id,
         sid,
         serial#,
         decode(state,'WAITING','W','C'),
         state,
         user#,
         type,
         command,
         program,
         module,
         action,
         service_name,
         client_identifier,
         event#,
         event,
         seq#,
         p1,
         p2,
         p3,
         wait_class#,
         wait_time_micro,
         sql_id,
         sql_hash_value,
         sql_child_number,
         con_id,
         2
      FROM gv$session
      WHERE status='ACTIVE'
        AND ((state = 'WAITING' AND wait_class# <> 6)
              OR (state <> 'WAITING' AND ((SID = v_session_sid AND event# <> 389) OR sid <> v_session_sid)));

      IF v_index MOD v_sample_per_min = 0 THEN
         COMMIT;
      END IF;

      DBMS_LOCK.SLEEP(p_sampling_interval_sec);

   END LOOP;
END start_sampling;

END DBMS_SAMPLING_SESSION;
/

Remarks:

For the PDB1 container which is a secondary database the element_id value is 2,
For non-CDB databases, you can set the con_id column value to 0.

Let’s continue with the CDB$ROOT container.

CDB$ROOT – Table update

We will add the four new columns to the table lss$_sampling_session in the C##LSS schema:

@> connect c##lss@orcl
Enter password:
Connected.
C##LSS@orcl> show con_name;

CON_NAME
------------------------------
CDB$ROOT
C##LSS@orcl> ALTER TABLE lss$_sampling_session
  2  ADD (inst_id           NUMBER,
  3       sql_hash_value    NUMBER,
  4       sql_child_number  NUMBER,
  5       element_id        NUMBER);

Table altered.

CDB$ROOT – Other privileges

Let’s grant the SELECT privilege on the underlying object of gv$session in order to use it in a procedure instead of v$session:

@> connect / as sysdba
Connected.
SYS@orcl> show con_name

CON_NAME
------------------------------
CDB$ROOT
SYS@orcl> GRANT SELECT ON SYS.GV_$SESSION TO C##LSS;

Grant succeeded.

Gv$session is a view that contains data linked to several containers (CDB$ROOT, PDB’s). Because the common user C##LSS should see data of all those containers we will set the CONTAINER_DATA attribute as follow:

SYS@orcl> ALTER USER C##LSS
  2  SET CONTAINER_DATA = (CDB$ROOT,PDB1)
  3  FOR gv_$session CONTAINER=CURRENT;

User altered.

CDB$ROOT – Package body

The package body is the same than for the PDB1 container except that the value of element_id is 1 for CDB$ROOT:

You must replace this code fragment

...         con_id,
         2
      FROM gv$session
      WHERE status='ACTIVE'...

...         con_id,
         1
      FROM gv$session
      WHERE status='ACTIVE'...

Shortly

We will consolidated the two database activities in a stacked area graph and compare it to the graph described in the part XXII.

Database Monitoring, Performance Tuning, PL/SQL, RAC 11gR2, 12cR1, Average active sessions, CDB database, CDB$ROOT, Container, CONTAINER_DATA, Database group, DBMS_APPLICATION_INFO, DBMS_LOCK, GV$SESSION, Light sampling sessions, LSS, Non-CDB database, PDB, Performance monitoring, Pluggable Database, Primary database, RAC, Secondary database

Average Active Sessions – Light Sampling Sessions – Views update

Leave a comment Posted by mourmansk on December 31, 2015

Scope

The views v$aas_light_sampling_sessions, v$cdb_pdb_aas_lss, v$top_activity_session_lss and v$session_activity_lss defined in the Part XXI, Part XXII, Part XXIII and Part XXIV respectively, are better suited for high database activity periods e.g. at leat one active session per sample, but are not well suited for low database activity periods.

We will modify the following views in order to handle high database activity periods as well as low database activity periods.

Assumption:

Those views are based on a sampling period interval set to 2 seconds.

View v$aas_light_sampling_sessions (Part XXI)

CREATE OR REPLACE VIEW v$aas_light_sampling_sessions
AS
SELECT sample_yyyymmddhhmi,
       SUBSTR(sample_yyyymmddhhmi,9,2) || ':' || SUBSTR(sample_yyyymmddhhmi,11,2) BEGIN_TIME,
       aas1                                                                 C_CPU,
       aas1+aas2                                                            C_OTHER,
       aas1+aas2+aas3                                                       C_APPLICATION,
       aas1+aas2+aas3+aas4                                                  C_CONFIGURATION,
       aas1+aas2+aas3+aas4+aas5                                             C_ADMINISTRATIVE,
       aas1+aas2+aas3+aas4+aas5+aas6                                        C_CONCURRENCY,
       aas1+aas2+aas3+aas4+aas5+aas6+aas7                                   C_COMMIT,
       aas1+aas2+aas3+aas4+aas5+aas6+aas7+aas8                              C_NETWORK,
       aas1+aas2+aas3+aas4+aas5+aas6+aas7+aas8+aas9                         C_USER_IO,
       aas1+aas2+aas3+aas4+aas5+aas6+aas7+aas8+aas9+aas10                   C_SYSTEM_IO,
       aas1+aas2+aas3+aas4+aas5+aas6+aas7+aas8+aas9+aas10+aas11             C_SCHEDULER,
       aas1+aas2+aas3+aas4+aas5+aas6+aas7+aas8+aas9+aas10+aas11+aas12       C_CLUSTER,
       aas1+aas2+aas3+aas4+aas5+aas6+aas7+aas8+aas9+aas10+aas11+aas12+aas13 C_QUEUEING,
       MAX(CEIL(aas1+aas2+aas3+aas4+aas5+aas6+aas7+aas8+aas9+aas10+aas11+aas12+aas13)) OVER() MAX_AAS
FROM
   (SELECT sample_yyyymmddhhmi,
           TRUNC((count_cpu/(nb_samples)),3)            aas1,  --aas_cpu,
           TRUNC((count_other/(nb_samples)),3)          aas2,  --aas_other,
           TRUNC((count_application/(nb_samples)),3)    aas3,  --aas_application,
           TRUNC((count_configuration/(nb_samples)),3)  aas4,  --aas_configuration,
           TRUNC((count_administrative/(nb_samples)),3) aas5,  --aas_administrative,
           TRUNC((count_concurrency/(nb_samples)),3)    aas6,  --aas_concurrency,
           TRUNC((count_commit/(nb_samples)),3)         aas7,  --aas_commit,
           TRUNC((count_network/(nb_samples)),3)        aas8,  --aas_network,
           TRUNC((count_user_io/(nb_samples)),3)        aas9,  --aas_user_io,
           TRUNC((count_system_io/(nb_samples)),3)      aas10, --aas_system_io,
           TRUNC((count_scheduler/(nb_samples)),3)      aas11, --aas_scheduler,
           TRUNC((count_cluster/(nb_samples)),3)        aas12, --aas_cluster,
           TRUNC((count_queueing/(nb_samples)),3)       aas13  --aas_queueing    
    FROM 
       (SELECT NVL(co_base_qry.sample_yyyymmddhhmi,time_window.start_time) sample_yyyymmddhhmi,
               NVL(co_base_qry.nb_samples,1)           nb_samples,
               NVL(co_base_qry.count_cpu,0)            count_cpu,
               NVL(co_base_qry.count_other,0)          count_other,
               NVL(co_base_qry.count_application,0)    count_application,
               NVL(co_base_qry.count_configuration,0)  count_configuration,
               NVL(co_base_qry.count_administrative,0) count_administrative,
               NVL(co_base_qry.count_concurrency,0)    count_concurrency,
               NVL(co_base_qry.count_commit,0)         count_commit,
               NVL(co_base_qry.count_network,0)        count_network,
               NVL(co_base_qry.count_user_io,0)        count_user_io,
               NVL(co_base_qry.count_system_io,0)      count_system_io,
               NVL(co_base_qry.count_scheduler,0)      count_scheduler,
               NVL(co_base_qry.count_cluster,0)        count_cluster,
               NVL(co_base_qry.count_queueing,0)       count_queueing
        FROM
           (SELECT TO_CHAR(SYSDATE - (LEVEL/24/60),'YYYYMMDDHH24MI') start_time
            FROM DUAL 
            CONNECT BY LEVEL <= 61
            ) time_window
        LEFT OUTER JOIN 
           (SELECT TO_CHAR(sample_time,'YYYYMMDDHH24MI') sample_yyyymmddhhmi,
                   30 nb_samples,
                   COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),-1,1)) count_cpu,
                   COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),0,1))  count_other,
                   COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),1,1))  count_application,
                   COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),2,1))  count_configuration,
                   COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),3,1))  count_administrative,
                   COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),4,1))  count_concurrency,
                   COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),5,1))  count_commit,
                   COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),7,1))  count_network,
                   COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),8,1))  count_user_io,
                   COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),9,1))  count_system_io,
                   COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),10,1)) count_scheduler,
                   COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),11,1)) count_cluster,
                   COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),12,1)) count_queueing
            FROM lss$_sampling_session
            GROUP BY TO_CHAR(sample_time,'YYYYMMDDHH24MI')
            ) co_base_qry
          ON time_window.start_time=co_base_qry.sample_yyyymmddhhmi
       )
  );

View v$cdb_pdb_aas_lss (Part XXII)

CREATE OR REPLACE VIEW v$cdb_pdb_aas_lss
AS          
SELECT sample_yyyymmddhhmi,
       SUBSTR(sample_yyyymmddhhmi,9,2) || ':' || SUBSTR(sample_yyyymmddhhmi,11,2)                                        BEGIN_TIME,
       aas_cpu0                                                                                                          C_CONT0_CPU,
       aas_cpu0+aas_wait0                                                                                                C_CONT0_WAIT,
       aas_cpu0+aas_wait0+aas_cpu1                                                                                       C_CONT1_CPU,
       aas_cpu0+aas_wait0+aas_cpu1+aas_wait1                                                                             C_CONT1_WAIT,
       aas_cpu0+aas_wait0+aas_cpu1+aas_wait1+aas_cpu2                                                                    C_CONT2_CPU,
       aas_cpu0+aas_wait0+aas_cpu1+aas_wait1+aas_cpu2+aas_wait2                                                          C_CONT2_WAIT,
       aas_cpu0+aas_wait0+aas_cpu1+aas_wait1+aas_cpu2+aas_wait2+aas_cpu3                                                 C_CONT3_CPU,
       aas_cpu0+aas_wait0+aas_cpu1+aas_wait1+aas_cpu2+aas_wait2+aas_cpu3+aas_wait3                                       C_CONT3_WAIT,
       aas_cpu0+aas_wait0+aas_cpu1+aas_wait1+aas_cpu2+aas_wait2+aas_cpu3+aas_wait3+aas_cpu4                              C_CONT4_CPU,
       aas_cpu0+aas_wait0+aas_cpu1+aas_wait1+aas_cpu2+aas_wait2+aas_cpu3+aas_wait3+aas_cpu4+aas_wait4                    C_CONT4_WAIT,
       aas_cpu0+aas_wait0+aas_cpu1+aas_wait1+aas_cpu2+aas_wait2+aas_cpu3+aas_wait3+aas_cpu4+aas_wait4+aas_cpu5           C_CONT5_CPU,
       aas_cpu0+aas_wait0+aas_cpu1+aas_wait1+aas_cpu2+aas_wait2+aas_cpu3+aas_wait3+aas_cpu4+aas_wait4+aas_cpu5+aas_wait5 C_CONT5_WAIT,
       MAX(CEIL(aas_cpu0+aas_wait0+aas_cpu1+aas_wait1+aas_cpu2+aas_wait2+aas_cpu3+aas_wait3+aas_cpu4+aas_wait4+aas_cpu5+aas_wait5)) OVER() MAX_CONT_AAS
FROM 
   (SELECT NVL(co_base_qry.sample_yyyymmddhhmi,time_window.start_time) sample_yyyymmddhhmi,
           NVL(co_base_qry.sum_aas_cpu0,0)   aas_cpu0,
           NVL(co_base_qry.sum_aas_wait0,0)  aas_wait0,
           NVL(co_base_qry.sum_aas_cpu1,0)   aas_cpu1,
           NVL(co_base_qry.sum_aas_wait1,0)  aas_wait1,
           NVL(co_base_qry.sum_aas_cpu2,0)   aas_cpu2,
           NVL(co_base_qry.sum_aas_wait2,0)  aas_wait2,
           NVL(co_base_qry.sum_aas_cpu3,0)   aas_cpu3,
           NVL(co_base_qry.sum_aas_wait3,0)  aas_wait3,
           NVL(co_base_qry.sum_aas_cpu4,0)   aas_cpu4,
           NVL(co_base_qry.sum_aas_wait4,0)  aas_wait4,
           NVL(co_base_qry.sum_aas_cpu5,0)   aas_cpu5,
           NVL(co_base_qry.sum_aas_wait5,0)  aas_wait5
    FROM 
       (SELECT TO_CHAR(SYSDATE - (LEVEL/24/60),'YYYYMMDDHH24MI') start_time
        FROM DUAL 
        CONNECT BY LEVEL <= 61
       ) time_window
    LEFT OUTER JOIN 
       (SELECT sample_yyyymmddhhmi,
               SUM(aas_cpu0)  sum_aas_cpu0,
               SUM(aas_wait0) sum_aas_wait0,
               SUM(aas_cpu1)  sum_aas_cpu1,
               SUM(aas_wait1) sum_aas_wait1,
               SUM(aas_cpu2)  sum_aas_cpu2,
               SUM(aas_wait2) sum_aas_wait2,
               SUM(aas_cpu3)  sum_aas_cpu3,
               SUM(aas_wait3) sum_aas_wait3,
               SUM(aas_cpu4)  sum_aas_cpu4,
               SUM(aas_wait4) sum_aas_wait4,
               SUM(aas_cpu5)  sum_aas_cpu5,
               SUM(aas_wait5) sum_aas_wait5
        FROM 
           (SELECT sample_yyyymmddhhmi,
                   CASE con_id
                      WHEN 0 THEN TRUNC(count_cpu/(nb_samples),3)
                      ELSE 0
                   END aas_cpu0,
                   CASE con_id
                      WHEN 0 THEN TRUNC(count_wait/(nb_samples),3)
                      ELSE 0
                   END aas_wait0,
                   CASE con_id
                      WHEN 1 THEN TRUNC(count_cpu/(nb_samples),3)
                      ELSE 0
                   END aas_cpu1,
                   CASE con_id
                      WHEN 1 THEN TRUNC(count_wait/(nb_samples),3)
                      ELSE 0
                   END aas_wait1,
                   CASE con_id
                      WHEN 3 THEN TRUNC(count_cpu/(nb_samples),3)
                      ELSE 0
                   END aas_cpu2,
                   CASE con_id
                      WHEN 3 THEN TRUNC(count_wait/(nb_samples),3)
                      ELSE 0
                   END aas_wait2,
                   CASE con_id
                      WHEN 4 THEN TRUNC(count_cpu/(nb_samples),3)
                      ELSE 0
                   END aas_cpu3,
                   CASE con_id
                      WHEN 4 THEN TRUNC(count_wait/(nb_samples),3)
                      ELSE 0
                   END aas_wait3,
                   CASE con_id
                      WHEN 5 THEN TRUNC(count_cpu/(nb_samples),3)
                      ELSE 0
                   END aas_cpu4,
                   CASE con_id
                      WHEN 5 THEN TRUNC(count_wait/(nb_samples),3)
                      ELSE 0
                   END aas_wait4,
                   CASE con_id
                      WHEN 6 THEN TRUNC(count_cpu/(nb_samples),3)
                      ELSE 0
                   END aas_cpu5,
                   CASE con_id
                      WHEN 6 THEN TRUNC(count_wait/(nb_samples),3)
                      ELSE 0
                   END aas_wait5
            FROM 
               (SELECT TO_CHAR(sample_time,'YYYYMMDDHH24MI') sample_yyyymmddhhmi,
                       con_id,
                       30 nb_samples,
                       COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),-1,1)) count_cpu,
                       COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),0,1,1,1,2,1,3,1,4,1,5,1,7,1,8,1,9,1,10,1,11,1,12,1)) count_wait
                FROM lss$_sampling_session
                GROUP BY con_id,
                        TO_CHAR(sample_time,'YYYYMMDDHH24MI')
                )
           ) GROUP BY sample_yyyymmddhhmi
       ) co_base_qry
      ON time_window.start_time=co_base_qry.sample_yyyymmddhhmi
   );

View v$top_activity_session_lss (Part XXIII)

CREATE OR REPLACE VIEW v$top_activity_session_lss
AS
SELECT sid,
       serial#,
       user#,
       user_program,
       user_con_id,
       count_total,
       aas1                                                                 C_CPU,
       aas1+aas2                                                            C_OTHER,
       aas1+aas2+aas3                                                       C_APPLICATION,
       aas1+aas2+aas3+aas4                                                  C_CONFIGURATION,
       aas1+aas2+aas3+aas4+aas5                                             C_ADMINISTRATIVE,
       aas1+aas2+aas3+aas4+aas5+aas6                                        C_CONCURRENCY,
       aas1+aas2+aas3+aas4+aas5+aas6+aas7                                   C_COMMIT,
       aas1+aas2+aas3+aas4+aas5+aas6+aas7+aas8                              C_NETWORK,
       aas1+aas2+aas3+aas4+aas5+aas6+aas7+aas8+aas9                         C_USER_IO,
       aas1+aas2+aas3+aas4+aas5+aas6+aas7+aas8+aas9+aas10                   C_SYSTEM_IO,
       aas1+aas2+aas3+aas4+aas5+aas6+aas7+aas8+aas9+aas10+aas11             C_SCHEDULER,
       aas1+aas2+aas3+aas4+aas5+aas6+aas7+aas8+aas9+aas10+aas11+aas12       C_CLUSTER,
       aas1+aas2+aas3+aas4+aas5+aas6+aas7+aas8+aas9+aas10+aas11+aas12+aas13 C_QUEUEING,
       MAX(aas1+aas2+aas3+aas4+aas5+aas6+aas7+aas8+aas9+aas10+aas11+aas12+aas13) OVER() MAX_AAS
FROM 
   (SELECT sid,
           serial#,
           max_user#   user#,
           max_program user_program,
           max_con_id  user_con_id,
           TRUNC((count_cpu/(max_samples)),3)            aas1,  --aas_cpu
           TRUNC((count_other/(max_samples)),3)          aas2,  --aas_other
           TRUNC((count_application/(max_samples)),3)    aas3,  --aas_application
           TRUNC((count_configuration/(max_samples)),3)  aas4,  --aas_configuration
           TRUNC((count_administrative/(max_samples)),3) aas5,  --aas_administrative
           TRUNC((count_concurrency/(max_samples)),3)    aas6,  --aas_concurrency
           TRUNC((count_commit/(max_samples)),3)         aas7,  --aas_commit
           TRUNC((count_network/(max_samples)),3)        aas8,  --aas_network
           TRUNC((count_user_io/(max_samples)),3)        aas9,  --aas_user_io
           TRUNC((count_system_io/(max_samples)),3)      aas10, --aas_system_io
           TRUNC((count_scheduler/(max_samples)),3)      aas11, --aas_scheduler
           TRUNC((count_cluster/(max_samples)),3)        aas12, --aas_cluster
           TRUNC((count_queueing/(max_samples)),3)       aas13, --aas_queueing 
           count_total
    FROM
       (SELECT sid,
               serial#,
               COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),-1,1)) count_cpu,
               COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),0,1))  count_other,
               COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),1,1))  count_application,
               COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),2,1))  count_configuration,
               COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),3,1))  count_administrative,
               COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),4,1))  count_concurrency,
               COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),5,1))  count_commit,
               COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),7,1))  count_network,
               COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),8,1))  count_user_io,
               COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),9,1))  count_system_io,
               COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),10,1)) count_scheduler,
               COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),11,1)) count_cluster,
               COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),12,1)) count_queueing,
               COUNT(*)                             count_total,
               300                                  max_samples,
               MAX(user#)                           max_user#,
               MAX(program)                         max_program,
               MAX(con_id)                          max_con_id
        FROM lss$_sampling_session
        WHERE sample_time >= SYSDATE -(10/24/60)
        GROUP BY sid,
                 serial#
        ORDER BY count_total DESC
        FETCH FIRST 20 ROWS ONLY
        )
   );

View v$session_activity_lss (Part XXIV)

CREATE OR REPLACE VIEW v$session_activity_lss
AS
WITH session_events AS
   (
   SELECT event,
          COUNT(*) count_event, 
          RANK() OVER (ORDER BY COUNT(*) DESC, EVENT) rank_event 
   FROM lss$_sampling_session
   WHERE SID = get_current_session
     AND sample_time >= SYSDATE -(1/24) 
     AND session_state <> 'C'
   GROUP BY event
   ORDER BY count_event DESC
   FETCH FIRST 12 ROWS ONLY
   )
SELECT sample_yyyymmddhhmi,
       SUBSTR(sample_yyyymmddhhmi,9,2) || ':' || SUBSTR(sample_yyyymmddhhmi,11,2) BEGIN_TIME,
       avge1                                                                             C_EVENT0,
       avge1+avge2                                                                       C_EVENT1,
       avge1+avge2+avge3                                                                 C_EVENT2,
       avge1+avge2+avge3+avge4                                                           C_EVENT3,
       avge1+avge2+avge3+avge4+avge5                                                     C_EVENT4,
       avge1+avge2+avge3+avge4+avge5+avge6                                               C_EVENT5,
       avge1+avge2+avge3+avge4+avge5+avge6+avge7                                         C_EVENT6,
       avge1+avge2+avge3+avge4+avge5+avge6+avge7+avge8                                   C_EVENT7,
       avge1+avge2+avge3+avge4+avge5+avge6+avge7+avge8+avge9                             C_EVENT8,
       avge1+avge2+avge3+avge4+avge5+avge6+avge7+avge8+avge9+avge10                      C_EVENT9,
       avge1+avge2+avge3+avge4+avge5+avge6+avge7+avge8+avge9+avge10+avge11               C_EVENT10,
       avge1+avge2+avge3+avge4+avge5+avge6+avge7+avge8+avge9+avge10+avge11+avge12        C_EVENT11,
       avge1+avge2+avge3+avge4+avge5+avge6+avge7+avge8+avge9+avge10+avge11+avge12+avge13 C_EVENT12,
       MAX(avge1+avge2+avge3+avge4+avge5+avge6+avge7+avge8+avge9+avge10+avge11+avge12+avge13) OVER() MAX_EVENT,
       (SELECT get_current_session FROM DUAL) session_id,
       MAX(event_name0)  OVER() EVENT_NAME0,
       MAX(event_name1)  OVER() EVENT_NAME1,
       MAX(event_name2)  OVER() EVENT_NAME2,
       MAX(event_name3)  OVER() EVENT_NAME3,
       MAX(event_name4)  OVER() EVENT_NAME4,
       MAX(event_name5)  OVER() EVENT_NAME5,
       MAX(event_name6)  OVER() EVENT_NAME6,
       MAX(event_name7)  OVER() EVENT_NAME7,
       MAX(event_name8)  OVER() EVENT_NAME8,
       MAX(event_name9)  OVER() EVENT_NAME9,
       MAX(event_name10) OVER() EVENT_NAME10,
       MAX(event_name11) OVER() EVENT_NAME11,
       MAX(event_name12) OVER() EVENT_NAME12
FROM
   (SELECT sample_yyyymmddhhmi,
           TRUNC((count_event0/(nb_samples)),3)  avge1,  
           TRUNC((count_event1/(nb_samples)),3)  avge2,  
           TRUNC((count_event2/(nb_samples)),3)  avge3, 
           TRUNC((count_event3/(nb_samples)),3)  avge4,  
           TRUNC((count_event4/(nb_samples)),3)  avge5,  
           TRUNC((count_event5/(nb_samples)),3)  avge6,  
           TRUNC((count_event6/(nb_samples)),3)  avge7,  
           TRUNC((count_event7/(nb_samples)),3)  avge8,  
           TRUNC((count_event8/(nb_samples)),3)  avge9,  
           TRUNC((count_event9/(nb_samples)),3)  avge10, 
           TRUNC((count_event10/(nb_samples)),3) avge11, 
           TRUNC((count_event11/(nb_samples)),3) avge12, 
           TRUNC((count_event12/(nb_samples)),3) avge13,  
           event_name0,
           event_name1,
           event_name2,
           event_name3,
           event_name4,
           event_name5,
           event_name6,
           event_name7,
           event_name8,
           event_name9,
           event_name10,
           event_name11,
           event_name12
    FROM 
       (SELECT NVL(co_base_qry.sample_yyyymmddhhmi,time_window.start_time) sample_yyyymmddhhmi,
               NVL(co_base_qry.nb_samples,1)    nb_samples,
               NVL(co_base_qry.count_event0,0)  count_event0,
               NVL(co_base_qry.count_event1,0)  count_event1,
               NVL(co_base_qry.count_event2,0)  count_event2,
               NVL(co_base_qry.count_event3,0)  count_event3,
               NVL(co_base_qry.count_event4,0)  count_event4,
               NVL(co_base_qry.count_event5,0)  count_event5,
               NVL(co_base_qry.count_event6,0)  count_event6,
               NVL(co_base_qry.count_event7,0)  count_event7,
               NVL(co_base_qry.count_event8,0)  count_event8,
               NVL(co_base_qry.count_event9,0)  count_event9,
               NVL(co_base_qry.count_event10,0) count_event10,
               NVL(co_base_qry.count_event11,0) count_event11,
               NVL(co_base_qry.count_event12,0) count_event12,
               co_base_qry.event_name0,
               co_base_qry.event_name1,
               co_base_qry.event_name2,
               co_base_qry.event_name3,
               co_base_qry.event_name4,
               co_base_qry.event_name5,
               co_base_qry.event_name6,
               co_base_qry.event_name7,
               co_base_qry.event_name8,
               co_base_qry.event_name9,
               co_base_qry.event_name10,
               co_base_qry.event_name11,
               co_base_qry.event_name12
        FROM
           (SELECT TO_CHAR(SYSDATE - (LEVEL/24/60),'YYYYMMDDHH24MI') start_time
            FROM DUAL 
            CONNECT BY LEVEL <= 61
            ) time_window
        LEFT OUTER JOIN 
           (SELECT TO_CHAR(sample_time,'YYYYMMDDHH24MI') sample_yyyymmddhhmi,
                   30 nb_samples,
                   COUNT(DECODE(DECODE(session_state,'C',-1,sess_event.rank_event),-1,1)) count_event0,
                   COUNT(DECODE(DECODE(session_state,'C',-1,sess_event.rank_event),1,1))  count_event1,
                   COUNT(DECODE(DECODE(session_state,'C',-1,sess_event.rank_event),2,1))  count_event2,
                   COUNT(DECODE(DECODE(session_state,'C',-1,sess_event.rank_event),3,1))  count_event3,
                   COUNT(DECODE(DECODE(session_state,'C',-1,sess_event.rank_event),4,1))  count_event4,
                   COUNT(DECODE(DECODE(session_state,'C',-1,sess_event.rank_event),5,1))  count_event5,
                   COUNT(DECODE(DECODE(session_state,'C',-1,sess_event.rank_event),6,1))  count_event6,
                   COUNT(DECODE(DECODE(session_state,'C',-1,sess_event.rank_event),7,1))  count_event7,
                   COUNT(DECODE(DECODE(session_state,'C',-1,sess_event.rank_event),8,1))  count_event8,
                   COUNT(DECODE(DECODE(session_state,'C',-1,sess_event.rank_event),9,1))  count_event9,
                   COUNT(DECODE(DECODE(session_state,'C',-1,sess_event.rank_event),10,1)) count_event10,
                   COUNT(DECODE(DECODE(session_state,'C',-1,sess_event.rank_event),11,1)) count_event11,
                   COUNT(DECODE(DECODE(session_state,'C',-1,sess_event.rank_event),12,1)) count_event12,
                   'cpu'    event_name0,
                   MAX(DECODE(DECODE(session_state,'C',-1,sess_event.rank_event),1,sess_event.event))  event_name1,
                   MAX(DECODE(DECODE(session_state,'C',-1,sess_event.rank_event),2,sess_event.event))  event_name2,
                   MAX(DECODE(DECODE(session_state,'C',-1,sess_event.rank_event),3,sess_event.event))  event_name3,
                   MAX(DECODE(DECODE(session_state,'C',-1,sess_event.rank_event),4,sess_event.event))  event_name4,
                   MAX(DECODE(DECODE(session_state,'C',-1,sess_event.rank_event),5,sess_event.event))  event_name5,
                   MAX(DECODE(DECODE(session_state,'C',-1,sess_event.rank_event),6,sess_event.event))  event_name6,
                   MAX(DECODE(DECODE(session_state,'C',-1,sess_event.rank_event),7,sess_event.event))  event_name7,
                   MAX(DECODE(DECODE(session_state,'C',-1,sess_event.rank_event),8,sess_event.event))  event_name8,
                   MAX(DECODE(DECODE(session_state,'C',-1,sess_event.rank_event),9,sess_event.event))  event_name9,
                   MAX(DECODE(DECODE(session_state,'C',-1,sess_event.rank_event),10,sess_event.event)) event_name10,
                   MAX(DECODE(DECODE(session_state,'C',-1,sess_event.rank_event),11,sess_event.event)) event_name11,
                   MAX(DECODE(DECODE(session_state,'C',-1,sess_event.rank_event),12,sess_event.event)) event_name12
            FROM lss$_sampling_session lss
            LEFT OUTER JOIN session_events sess_event
              ON sess_event.event = lss.event
            WHERE lss.sid = get_current_session
            GROUP BY TO_CHAR(sample_time,'YYYYMMDDHH24MI')
            ) co_base_qry
          ON time_window.start_time=co_base_qry.sample_yyyymmddhhmi
       )
  );

Remarks:

The views v$aas_light_sampling_sessions, v$cdb_pdb_aas_lss, v$session_activity_lss and v$top_activity_session_lss are created from the CDB$ROOT container in the C##LSS schema,
The view v$session_activity30s_lss (Part XXV) will be updated shortly.

Errata

In the view v$top_activity_session_lss, the “max_samples” column value should be 300 instead of 30 (the view has been fixed to reflect this change).

Database Monitoring, Performance Tuning 12cR1, Average active sessions, CDB, CDB$ROOT, Container, Light sampling sessions, LSS, Performance monitoring, v$aas_light_sampling_sessions, v$cdb_pdb_aas_lss, v$session_activity_lss, v$top_activity_session_lss, Views

Generating SVG graphics with SQL*Plus – Part XXV – Stacked area graph – Session Activity – Light Sampling Sessions – Update

Leave a comment Posted by mourmansk on November 16, 2015

Scope

In this post we will focus on tracking session activity at CDB/PDB level and on increasing the accuracy of the graph. The previous post displays the consolidated session id at the instance level. If we want to track a session at the CDB/PDB level we need:

A session id,
A serial#,
A container id.

Function get_current_session

We will slightly modify the function get_current_session in order to get the triplet ‘[session_id],[serial#],[con_id]’:

CREATE OR REPLACE FUNCTION get_current_session RETURN VARCHAR2
AS 
   v_param_value lss$parameter.parameter_value%TYPE;
BEGIN

   SELECT parameter_value
   INTO v_param_value
   FROM lss$parameter
   WHERE parameter_name = 'current_session';
   
   RETURN v_param_value;
   
   EXCEPTION
      WHEN TOO_MANY_ROWS THEN
         RETURN '<UNKNOWN>';
      WHEN NO_DATA_FOUND THEN
         RETURN '<UNKNOWN>';
      WHEN OTHERS THEN
         RAISE;
         
END get_current_session;

Basically the triplet will be stored in the table lss$parameter as a string.

Increase graph accuracy

Instead of counting the events on a period of time of one minute we will count them on a period of time of 30 seconds. We will create a new view v$session_activity30s_lss in order to display the session activity every 30 seconds. The starting point will be the view v$session_activity_lss.

View creation – Step 1