Oracle database experiences

Oracle database blog

Generating SVG graphics with SQL*Plus – Part XXIII – Horizontal stacked bar graph – Top Activity Sessions – Light Sampling Sessions – CDB database

Scope

It could be interesting to have the top activity sessions of a whole CDB database e.g. through all the CDB/PDB. We will limit to the top 20 activity sessions. For each session we will display:

  • Session Id,
  • Activity,
  • User Name,
  • Program Name,
  • Container Name.

Remark: The maximum value of the ‘Activity’ field is linked to the maximum activity session during the last 10 minutes interval.

We will create a view in order to display the top activity sessions.

View creation – Step 1

For the last 10 minutes, we will count the number of active sessions that are linked to a wait class or a cpu event as well as the total number of events per session id/serial# as follow:

        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,
               MAX(lss_sample.nb_samples)           max_samples,
               MAX(user#)                           max_user#,
               MAX(program)                         max_program,
               MAX(con_id)                          max_con_id
        FROM lss$_sampling_session
        CROSS JOIN 
           (SELECT MAX(sample_id) - MIN(sample_id)  nb_samples
            FROM lss$_sampling_session
            WHERE sample_time >= SYSDATE -(10/24/60)
           ) lss_sample
        WHERE sample_time >= SYSDATE -(10/24/60)
        GROUP BY sid,
                 serial#
        ORDER BY count_total DESC
        FETCH FIRST 20 ROWS ONLY

Remarks:

  • The Cross Join query computes the number of samples during the last 10 minutes,
  • We have used the clause ‘FETCH FIRST 20 ROWS ONLY’ in order to have only the top 20.

View creation – Step 2

For the top 20 activity sessions, we will define for each session in average how much it is waiting on such or such wait class and how much it is using cpu as follow:

     SELECT sid,
           serial#,
           max_user#   user#,
           max_program user_program,
           max_con_id  user_con_id,
           TRUNC((count_cpu/(max_samples+1)),3)            aas1,  --aas_cpu
           TRUNC((count_other/(max_samples+1)),3)          aas2,  --aas_other
           TRUNC((count_application/(max_samples+1)),3)    aas3,  --aas_application
           TRUNC((count_configuration/(max_samples+1)),3)  aas4,  --aas_configuration
           TRUNC((count_administrative/(max_samples+1)),3) aas5,  --aas_administrative
           TRUNC((count_concurrency/(max_samples+1)),3)    aas6,  --aas_concurrency
           TRUNC((count_commit/(max_samples+1)),3)         aas7,  --aas_commit
           TRUNC((count_network/(max_samples+1)),3)        aas8,  --aas_network
           TRUNC((count_user_io/(max_samples+1)),3)        aas9,  --aas_user_io
           TRUNC((count_system_io/(max_samples+1)),3)      aas10, --aas_system_io
           TRUNC((count_scheduler/(max_samples+1)),3)      aas11, --aas_scheduler
           TRUNC((count_cluster/(max_samples+1)),3)        aas12, --aas_cluster
           TRUNC((count_queueing/(max_samples+1)),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,
               MAX(lss_sample.nb_samples)           max_samples,
               MAX(user#)                           max_user#,
               MAX(program)                         max_program,
               MAX(con_id)                          max_con_id
        FROM lss$_sampling_session
        CROSS JOIN 
           (SELECT MAX(sample_id) - MIN(sample_id)  nb_samples
            FROM lss$_sampling_session
            WHERE sample_time >= SYSDATE -(10/24/60)
           ) lss_sample
        WHERE sample_time >= SYSDATE -(10/24/60)
        GROUP BY sid,
                 serial#
        ORDER BY count_total DESC
        FETCH FIRST 20 ROWS ONLY
        )

View creation – Step 3

Finally we will accumulate the different wait classes and cpu AAS for the top 20 activity sessions as follow:

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+1)),3)            aas1,  --aas_cpu
           TRUNC((count_other/(max_samples+1)),3)          aas2,  --aas_other
           TRUNC((count_application/(max_samples+1)),3)    aas3,  --aas_application
           TRUNC((count_configuration/(max_samples+1)),3)  aas4,  --aas_configuration
           TRUNC((count_administrative/(max_samples+1)),3) aas5,  --aas_administrative
           TRUNC((count_concurrency/(max_samples+1)),3)    aas6,  --aas_concurrency
           TRUNC((count_commit/(max_samples+1)),3)         aas7,  --aas_commit
           TRUNC((count_network/(max_samples+1)),3)        aas8,  --aas_network
           TRUNC((count_user_io/(max_samples+1)),3)        aas9,  --aas_user_io
           TRUNC((count_system_io/(max_samples+1)),3)      aas10, --aas_system_io
           TRUNC((count_scheduler/(max_samples+1)),3)      aas11, --aas_scheduler
           TRUNC((count_cluster/(max_samples+1)),3)        aas12, --aas_cluster
           TRUNC((count_queueing/(max_samples+1)),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,
               MAX(lss_sample.nb_samples)           max_samples,
               MAX(user#)                           max_user#,
               MAX(program)                         max_program,
               MAX(con_id)                          max_con_id
        FROM lss$_sampling_session
        CROSS JOIN 
           (SELECT MAX(sample_id) - MIN(sample_id)  nb_samples
            FROM lss$_sampling_session
            WHERE sample_time >= SYSDATE -(10/24/60)
           ) lss_sample
        WHERE sample_time >= SYSDATE -(10/24/60)
        GROUP BY sid,
                 serial#
        ORDER BY count_total DESC
        FETCH FIRST 20 ROWS ONLY
        )
   );

Modified source query

We will modify the source query as follow:

        SELECT top_act.sid              SID
        ,      DECODE(top_act.user#,0,'SYS',1,'PUBLIC',cdb_users.username) USERNAME
        ,      top_act.user_program     USER_PROGRAM
        ,      DECODE(top_act.user_con_id,0,'CDB WIDE',cont.name) CONTAINER_NAME
        ,      top_act.c_cpu            C_CPU
        ,      top_act.c_other          C_OTHER
        ,      top_act.c_application    C_APPLICATION
        ,      top_act.c_configuration  C_CONFIGURATION
        ,      top_act.c_administrative C_ADMINISTRATIVE
        ,      top_act.c_concurrency    C_CONCURRENCY
        ,      top_act.c_commit         C_COMMIT
        ,      top_act.c_network        C_NETWORK
        ,      top_act.c_user_io        C_USER_IO
        ,      top_act.c_system_io      C_SYSTEM_IO
        ,      top_act.c_scheduler      C_SCHEDULER
        ,      top_act.c_cluster        C_CLUSTER
        ,      top_act.c_queueing       C_QUEUEING
        ,      top_act.max_aas          MAX_AAS
        FROM v$top_activity_session_lss top_act
        LEFT OUTER JOIN cdb_users 
          ON cdb_users.user_id = top_act.user#
         AND cdb_users.con_id = top_act.user_con_id
        LEFT OUTER JOIN v$containers cont
          ON cont.con_id = top_act.user_con_id
        ORDER BY top_act.count_total DESC, top_act.c_queueing DESC

In order to display the container name and the user name, we are using the v$containers and the cdb_users views. However, because the v$containers and cdb_users are views that contain data linked to several containers (CDB$ROOT, PDB’s) and 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 v_$containers CONTAINER=CURRENT;

User altered.

SYS@orcl>

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

User altered.

We have only restricted to the containers CDB$ROOT and PDB1.

XSLT processing

We will use the query defined in the part XVI as a starting point with the main following differences:

  • The X axis time value will be replaced by a session id value,
  • The graph bars will be horizontal instead of vertical in this case.

XSLT variables

We will add five additional variables in the XSLT document:

  • <xsl:variable name="bar_height">10</xsl:variable>

    Height of the horizontal bar,

  • <xsl:variable name="length_width">150</xsl:variable>

    Length of the horizontal bar,

  • <xsl:variable name="xval_min">0</xsl:variable>

    Minimun activity value,

  • <xsl:variable name="xval_max"><xsl:value-of select="/descendant::MAX_AAS[position()=1]"/>

    First value of the column MAX_AAS,

  • <xsl:variable name="session_nb"><xsl:value-of select="count(/descendant::SID)"/></xsl:variable>

    Number of occurrence of the column SID.

Furthermore:

  • The variable “margin_top” is set to 30,
  • The variable “margin_left” is set to 45,
  • The variable “graph_width” is set to ‘$length_width+$margin_left+$margin_right+210’,
  • The variable “graph_height” is set to ‘270+$margin_top+$margin_bottom’,
  • The variable “graph_name” is set to ‘LSS Top Activity Sessions (last 10 minutes) – CDB/PDB’.

Modified query

SET LINESIZE      300
SET LONGCHUNKSIZE 100000
SET LONG          100000

SET FEEDBACK OFF
SET VERIFY   OFF
SET PAGESIZE 0

SET HEADING OFF

SPOOL generate_svg_23f.svg

SELECT XMLTRANSFORM
   (DBMS_XMLGEN.GETXMLTYPE
      ('SELECT top_act.sid              SID
        ,      DECODE(top_act.user#,0,''SYS'',1,''PUBLIC'',cdb_users.username) USERNAME
        ,      top_act.user_program     USER_PROGRAM
        ,      DECODE(top_act.user_con_id,0,''CDB WIDE'',cont.name) CONTAINER_NAME
        ,      top_act.c_cpu            C_CPU
        ,      top_act.c_other          C_OTHER
        ,      top_act.c_application    C_APPLICATION
        ,      top_act.c_configuration  C_CONFIGURATION
        ,      top_act.c_administrative C_ADMINISTRATIVE
        ,      top_act.c_concurrency    C_CONCURRENCY
        ,      top_act.c_commit         C_COMMIT
        ,      top_act.c_network        C_NETWORK
        ,      top_act.c_user_io        C_USER_IO
        ,      top_act.c_system_io      C_SYSTEM_IO
        ,      top_act.c_scheduler      C_SCHEDULER
        ,      top_act.c_cluster        C_CLUSTER
        ,      top_act.c_queueing       C_QUEUEING
        ,      top_act.max_aas          MAX_AAS
        FROM v$top_activity_session_lss top_act
        LEFT OUTER JOIN cdb_users 
          ON cdb_users.user_id = top_act.user#
         AND cdb_users.con_id = top_act.user_con_id
        LEFT OUTER JOIN v$containers cont
          ON cont.con_id = top_act.user_con_id
        ORDER BY top_act.count_total DESC, top_act.c_queueing DESC')
,      XMLTYPE.CREATEXML
   (TO_CLOB(
    '<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
     <xsl:variable name="margin_top">30</xsl:variable>
     <xsl:variable name="margin_bottom">30</xsl:variable>
     <xsl:variable name="margin_left">45</xsl:variable>
     <xsl:variable name="margin_right">100</xsl:variable>
     <xsl:variable name="bar_height">10</xsl:variable>
     <xsl:variable name="length_width">150</xsl:variable>
     <xsl:variable name="graph_width"><xsl:value-of select="$length_width+$margin_left+$margin_right+210"/></xsl:variable>
     <xsl:variable name="graph_height"><xsl:value-of select="270+$margin_top+$margin_bottom"/></xsl:variable>
     <xsl:variable name="graph_name">LSS Top Activity Sessions (last 10 minutes) - CDB/PDB</xsl:variable>
     <xsl:variable name="xval_min">0</xsl:variable>
     <xsl:variable name="xval_max"><xsl:value-of select="/descendant::MAX_AAS[position()=1]"/></xsl:variable>
     <xsl:variable name="session_nb"><xsl:value-of select="count(/descendant::SID)"/></xsl:variable>
       <xsl:template match="/">
         <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)-20}" style="fill: #000000; stroke: none;font-size:10px;text-anchor=start"><xsl:value-of select="$graph_name"/></text>
           <text x="{($margin_left)-35}" y="{($margin_top)-5}" style="fill: #000000; stroke: none;font-size:8px;font-weight:bold;text-anchor=start">Session Id</text>
           <text x="{($margin_left)+1}" y="{($margin_top)-5}" style="fill: #000000; stroke: none;font-size:8px;font-weight:bold;text-anchor=start">Activity</text>
           <text x="{($margin_left)+160}" y="{($margin_top)-5}" style="fill: #000000; stroke: none;font-size:8px;font-weight:bold;text-anchor=start">User Name</text>
           <text x="{($margin_left)+240}" y="{($margin_top)-5}" style="fill: #000000; stroke: none;font-size:8px;font-weight:bold;text-anchor=start">Program Name</text>
           <text x="{($margin_left)+325}" y="{($margin_top)-5}" style="fill: #000000; stroke: none;font-size:8px;font-weight:bold;text-anchor=start">Container</text>
           <line x1="{($margin_left)-35}" y1="{($margin_top)-2+(($bar_height)+5)*(position()-1)}" x2="{($graph_width)-($margin_right)+10}" y2="{($margin_top)-2+(($bar_height)+5)*(position()-1)}" style="stroke:lightblue;stroke-width:1" />'
           ) 
    || 
    TO_CLOB(
          '<xsl:for-each select="ROWSET/ROW">
             <rect x="{($margin_left)}" y="{$margin_top+(($bar_height)+5)*(position()-1)}" width="{((($xval_min)-(descendant::C_CPU))*(($length_width) div (($xval_min)-($xval_max))))}" height="{$bar_height}" fill="limegreen" stroke="none"/> 
             <rect x="{((($xval_min)-(descendant::C_CPU))*(($length_width) div (($xval_min)-($xval_max))))+($margin_left)}" y="{$margin_top+(($bar_height)+5)*(position()-1)}" width="{(((descendant::C_CPU)-(C_OTHER))*(($length_width) div (($xval_min)-($xval_max))))}" height="{$bar_height}" fill="hotpink" stroke="none"/>
             <rect x="{((($xval_min)-(descendant::C_OTHER))*(($length_width) div (($xval_min)-($xval_max))))+($margin_left)}" y="{$margin_top+(($bar_height)+5)*(position()-1)}" width="{(((descendant::C_OTHER)-(C_APPLICATION))*(($length_width) div (($xval_min)-($xval_max))))}" height="{$bar_height}" fill="indianred" stroke="none"/>                                                                                                                                    
             <rect x="{((($xval_min)-(descendant::C_APPLICATION))*(($length_width) div (($xval_min)-($xval_max))))+($margin_left)}" y="{$margin_top+(($bar_height)+5)*(position()-1)}" width="{(((descendant::C_APPLICATION)-(C_CONFIGURATION))*(($length_width) div (($xval_min)-($xval_max))))}" height="{$bar_height}" fill="olive" stroke="none"/>   
             <rect x="{((($xval_min)-(descendant::C_CONFIGURATION))*(($length_width) div (($xval_min)-($xval_max))))+($margin_left)}" y="{$margin_top+(($bar_height)+5)*(position()-1)}" width="{(((descendant::C_CONFIGURATION)-(C_ADMINISTRATIVE))*(($length_width) div (($xval_min)-($xval_max))))}" height="{$bar_height}" fill="grey" stroke="none"/>   
             <rect x="{((($xval_min)-(descendant::C_ADMINISTRATIVE))*(($length_width) div (($xval_min)-($xval_max))))+($margin_left)}" y="{$margin_top+(($bar_height)+5)*(position()-1)}" width="{(((descendant::C_ADMINISTRATIVE)-(C_CONCURRENCY))*(($length_width) div (($xval_min)-($xval_max))))}" height="{$bar_height}" fill="sienna" stroke="none"/>'
           ) 
    || 
    TO_CLOB(
          '  <rect x="{((($xval_min)-(descendant::C_CONCURRENCY))*(($length_width) div (($xval_min)-($xval_max))))+($margin_left)}" y="{$margin_top+(($bar_height)+5)*(position()-1)}" width="{(((descendant::C_CONCURRENCY)-(C_COMMIT))*(($length_width) div (($xval_min)-($xval_max))))}" height="{$bar_height}" fill="orange" stroke="none"/>  
             <rect x="{((($xval_min)-(descendant::C_COMMIT))*(($length_width) div (($xval_min)-($xval_max))))+($margin_left)}" y="{$margin_top+(($bar_height)+5)*(position()-1)}" width="{(((descendant::C_COMMIT)-(C_NETWORK))*(($length_width) div (($xval_min)-($xval_max))))}" height="{$bar_height}" fill="tan" stroke="none"/>   
             <rect x="{((($xval_min)-(descendant::C_NETWORK))*(($length_width) div (($xval_min)-($xval_max))))+($margin_left)}" y="{$margin_top+(($bar_height)+5)*(position()-1)}" width="{(((descendant::C_NETWORK)-(C_USER_IO))*(($length_width) div (($xval_min)-($xval_max))))}" height="{$bar_height}" fill="royalblue" stroke="none"/>   
             <rect x="{((($xval_min)-(descendant::C_USER_IO))*(($length_width) div (($xval_min)-($xval_max))))+($margin_left)}" y="{$margin_top+(($bar_height)+5)*(position()-1)}" width="{(((descendant::C_USER_IO)-(C_SYSTEM_IO))*(($length_width) div (($xval_min)-($xval_max))))}" height="{$bar_height}" fill="skyblue" stroke="none"/>   
             <rect x="{((($xval_min)-(descendant::C_SYSTEM_IO))*(($length_width) div (($xval_min)-($xval_max))))+($margin_left)}" y="{$margin_top+(($bar_height)+5)*(position()-1)}" width="{(((descendant::C_SYSTEM_IO)-(C_SCHEDULER))*(($length_width) div (($xval_min)-($xval_max))))}" height="{$bar_height}" fill="lightcyan" stroke="none"/>   
             <rect x="{((($xval_min)-(descendant::C_SCHEDULER))*(($length_width) div (($xval_min)-($xval_max))))+($margin_left)}" y="{$margin_top+(($bar_height)+5)*(position()-1)}" width="{(((descendant::C_SCHEDULER)-(C_CLUSTER))*(($length_width) div (($xval_min)-($xval_max))))}" height="{$bar_height}" fill="lightgray" stroke="none"/>   
             <rect x="{((($xval_min)-(descendant::C_CLUSTER))*(($length_width) div (($xval_min)-($xval_max))))+($margin_left)}" y="{$margin_top+(($bar_height)+5)*(position()-1)}" width="{(((descendant::C_CLUSTER)-(C_QUEUEING))*(($length_width) div (($xval_min)-($xval_max))))}" height="{$bar_height}" fill="bisque" stroke="none"/> 
             <text x="{($margin_left)-25}" y="{$margin_top+8+(($bar_height)+5)*(position()-1)}" style="fill: #000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="(descendant::SID)"/></text>
             <text x="{($margin_left)+160}" y="{$margin_top+8+(($bar_height)+5)*(position()-1)}" style="fill: #000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="(descendant::USERNAME)"/></text>
             <text x="{($margin_left)+240}" y="{$margin_top+8+(($bar_height)+5)*(position()-1)}" style="fill: #000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="(descendant::USER_PROGRAM)"/></text>
             <text x="{($margin_left)+325}" y="{$margin_top+8+(($bar_height)+5)*(position()-1)}" style="fill: #000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="(descendant::CONTAINER_NAME)"/></text>
             <line x1="{($margin_left)-35}" y1="{($margin_top)-2+(($bar_height)+5)*(position()-0)}" x2="{($graph_width)-($margin_right)+10}" y2="{($margin_top)-2+(($bar_height)+5)*(position()-0)}" style="stroke:lightblue;stroke-width:1" />
           </xsl:for-each>'
           ) 
    || 
    TO_CLOB(
          '<text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+0}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">CPU</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)-5}" width="{10}" height="{6}" fill="limegreen" stroke="black"/> 
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+8}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">Other</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+3}" width="{10}" height="{6}" fill="hotpink" stroke="black"/> 
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+16}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">Application</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+11}" width="{10}" height="{6}" fill="indianred" stroke="black"/> 
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+24}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">Configuration</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+19}" width="{10}" height="{6}" fill="olive" stroke="black"/>   
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+32}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">Administrative</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+27}" width="{10}" height="{6}" fill="gray" stroke="black"/>   
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+40}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">Concurrency</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+35}" width="{10}" height="{6}" fill="sienna" stroke="black"/>'
           ) 
    || 
    TO_CLOB(
          '<text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+48}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">Commit</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+43}" width="{10}" height="{6}" fill="orange" stroke="black"/>
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+56}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">Network</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+51}" width="{10}" height="{6}" fill="tan" stroke="black"/> 
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+64}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">User IO</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+59}" width="{10}" height="{6}" fill="royalblue" stroke="black"/>  
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+72}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">System IO</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+67}" width="{10}" height="{6}" fill="skyblue" stroke="black"/> 
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+80}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">Scheduler</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+75}" width="{10}" height="{6}" fill="lightcyan" stroke="black"/>
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+88}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">Cluster</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+83}" width="{10}" height="{6}" fill="lightgray" stroke="black"/> 
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+96}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">Queueing</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+91}" width="{10}" height="{6}" fill="bisque" stroke="black"/>                                                  
         </svg>
       </xsl:template>
     </xsl:stylesheet>'
           )
   )
   ) 
FROM dual;

SPOOL OFF
SET HEADING ON

SET VERIFY   ON
SET FEEDBACK ON

Result:

generate_svg_23f1

Remarks:

  • The query has been executed from the CDB$ROOT container by the C##LSS common user,
  • The v$top_activity_session_lss has been created from the CDB$ROOT container in the C##LSS schema.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: