Oracle Scratchpad

October 6, 2016

My session workload

Filed under: Oracle,Performance,Troubleshooting — Jonathan Lewis @ 1:19 pm BST Oct 6,2016

My old website (www.jlcomp.demon.co.uk) will be disappearing in a couple of weeks – but there are a couple of timeless articles on it that are worth saving and although the popularity of this one has probably been surpassed by Tanel Poder’s Snapper script, or other offerings by Tom Kyte or Adrian Billington, it’s still one of those useful little things to have around – it’s a package to takes a snapshot of your session stats.

The package depends on a view created in the SYS schema, and the package itself has to be installed in the SYS schema – which is why other strategies for collecting the information have become more popular; but if you want to have it handy, here are the two scripts:

rem
rem     Script:         c_mystats.sql
rem     Author:         Jonathan Lewis
rem     Dated:          March 2001
rem     Purpose:        Put names to v$mystat
rem
rem     Last tested
rem             12.1.0.2        -- naming issue
rem             11.2.0.4
rem             10.2.0.5
rem             10.1.0.4
rem              9.2.0.8
rem              8.1.7.4
rem
rem     Notes:
rem     Should be run by SYS - which means it has to be re-run
rem     on a full database export/import
rem
rem     It looks as if it is illegal to create a view with a
rem     name starting with v$ in the sys account as from 12c.
rem     (ORA-00999: invalid view name). Hence the JV$ name.
rem
rem     But you can create a public synonym starting "v$"
rem

create or replace view jv$my_stats
as
select
        /*+
                first_rows
                ordered
        */
        ms.sid,
        sn.statistic#,
        sn.name,
        sn.class,
        ms.value
from
        v$mystat        ms,
        v$statname      sn
where
        sn.statistic# = ms.statistic#
;

drop public synonym v$my_stats;
create public synonym v$my_stats for jv$my_stats;
grant select on v$my_stats to public;

rem
rem	Script:		snap_myst.sql
rem	Author:		Jonathan Lewis
rem	Dated:		March 2001
rem	Purpose:	Package to get snapshot start and delta of v$mystat
rem
rem	Last tested
rem		12.1.0.2
rem		11.2.0.4
rem		10.2.0.5
rem		10.1.0.4
rem		 9.2.0.8
rem		 8.1.7.4
rem
rem	Notes
rem	Has to be run by SYS to create the package
rem	Depends on view (j)v$my_stats (see c_mystats.sql)
rem
rem	Usage:
rem		set serveroutput on size 1000000 format wrapped
rem		set linesize 120
rem		set trimspool on
rem		execute snap_my_stats.start_snap
rem		-- do something
rem		execute snap_my_stats.end_snap
rem

create or replace package snap_my_stats as
	procedure start_snap;
	procedure end_snap (i_limit in number default 0);
end;
/

create or replace package body snap_my_stats as

cursor c1 is
	select 
		statistic#, 
		name,
		value
	from 
		v$my_stats
	where
		value != 0
	;


	type w_type is table of c1%rowtype index by binary_integer;
	w_list		w_type;
	empty_list	w_type;

	m_start_time	date;
	m_start_flag	char(1);
	m_end_time	date;

procedure start_snap is
begin

	m_start_time := sysdate;
	m_start_flag := 'U';
	w_list := empty_list;

	for r in c1 loop
		w_list(r.statistic#).value := r.value;
	end loop;

end start_snap;


procedure end_snap (i_limit in number default 0) 
is
begin

	m_end_time := sysdate;

	dbms_output.put_line('---------------------------------');

	dbms_output.put_line('Session stats - ' ||
				to_char(m_end_time,'dd-Mon hh24:mi:ss')
	);

	if m_start_flag = 'U' then
		dbms_output.put_line('Interval:-  '  || 
				trunc(86400 * (m_end_time - m_start_time)) ||
				' seconds'
		);
	else
		dbms_output.put_line('Since Startup:- ' || 
				to_char(m_start_time,'dd-Mon hh24:mi:ss')
		);
	end if;

	if (i_limit != 0) then
		dbms_output.put_line('Lower limit:-  '  || i_limit);
	end if;

	dbms_output.put_line('---------------------------------');

	dbms_output.put_line(
		rpad('Name',60) ||
		lpad('Value',18)
	);

	dbms_output.put_line(
		rpad('----',60) ||
		lpad('-----',18)
	);

	for r in c1 loop
		if (not w_list.exists(r.statistic#)) then
		    w_list(r.statistic#).value := 0;
		end if;

		if (
		       (r.value > w_list(r.statistic#).value + i_limit)
		) then
			dbms_output.put(rpad(r.name,60));
			dbms_output.put(to_char(
				r.value - w_list(r.statistic#).value,
					'9,999,999,999,990')
			);
			dbms_output.new_line;
		end if;
	end loop;

end end_snap;

begin
	select
		logon_time, 'S'
	into
		m_start_time, m_start_flag
	from
		v$session
	where
		sid = 	(
				select /*+ no_unnest */ sid 
				from v$mystat 
				where rownum = 1
			);

end snap_my_stats;
/

drop public synonym snap_my_stats;
create public synonym snap_my_stats for snap_my_stats;
grant execute on snap_my_stats to public;

One point to be cautious about with this package: do not embed it inside anonymous pl/sql blocks, e.g.

begin

        snap_my_stats.start_snap;

        -- some other code

        snap_my_stats.end_snap;

end;
/

There are some statistics in v$my_stats (v$mystat / v$sesstat) which are not updated until the end of a database call – and calling an anonymous pl/sql block counts as a single database call, so some of your statistics (for example “CPU used by this session”) will report misleading values.

2 Comments »

  1. Hello Jonathan,

    It’s not nice to ask you questions here.. Just curious to understand

    ---------------------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                        | Name                      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
    ---------------------------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                 |                           |      1 |        |      0 |00:00:02.33 |     623K|       |       |          |
    |*  1 |  VIEW                            |                           |      1 |      5 |      0 |00:00:02.33 |     623K|       |       |          |
    |*  2 |   WINDOW SORT PUSHED RANK        |                           |      1 |      5 |      0 |00:00:02.33 |     623K|  1024 |  1024 |          |
    |   3 |    NESTED LOOPS OUTER            |                           |      1 |      5 |      0 |00:00:02.33 |     623K|       |       |          |
    |   4 |     NESTED LOOPS                 |                           |      1 |      5 |      0 |00:00:02.33 |     623K|       |       |          |
    |   5 |      NESTED LOOPS                |                           |      1 |   2403 |   2869 |00:00:00.05 |    9488 |       |       |          |
    |   6 |       TABLE ACCESS BY INDEX ROWID| T1                        |      1 |   2403 |   2869 |00:00:00.01 |     878 |       |       |          |
    |*  7 |        INDEX SKIP SCAN           | IDX_T1_STATUS       	     |      1 |   2403 |   2869 |00:00:00.01 |     141 |       |       |          |
    |   8 |       TABLE ACCESS BY INDEX ROWID| T2         	             |   2869 |      1 |   2869 |00:00:00.03 |    8610 |       |       |          |
    |*  9 |        INDEX UNIQUE SCAN         | IDX_T2_ID                 |   2869 |      1 |   2869 |00:00:00.02 |    5740 |       |       |          |
    |* 10 |      TABLE ACCESS BY INDEX ROWID | T3                        |   2869 |      1 |      0 |00:00:02.28 |     613K|       |       |          |
    |* 11 |       INDEX RANGE SCAN           | IDX_T3_CUSTID      	     |   2869 |    135 |    605K|00:00:00.24 |   12280 |       |       |          |
    |  12 |     TABLE ACCESS BY INDEX ROWID  | T4                        |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
    |* 13 |      INDEX UNIQUE SCAN           | PK_T4                     |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
    ---------------------------------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter("ROW_NUM"=1)
       2 - filter(ROW_NUMBER() OVER ( PARTITION BY "T3"."CUSTID","T3"."AID","T3"."PDT","T4"."VPID" ORDER BY INTERNAL_FUNCTION("T3"."CDATE") DESC )<=1)
       7 - access("T1"."STATUS"=:N6)
           filter("T1"."STATUS"=:N6)
       9 - access("T1"."EID"="T2"."EID")
      10 - filter(("T3"."PAYROLL_SCHEDULE_DATE_ID"=:N1 AND "T3"."ROW_CONTENT" IS NOT NULL AND "T3"."BATCH_TYPE" IS NOT NULL AND
                  INTERNAL_FUNCTION("T3"."REMITTANCE_TYPE") AND "T2"."AID"="T3"."AID"))
      11 - access("T2"."CUSTID"="T3"."CUSTID")
      13 - access("T4"."PID"="T3"."PID")
    
    

    Id# 11 "Index Range Scan" produces 605k rowids , but the "Starts" stat on Id#10 "Table access by index rowid" shows only 2869. Am i interpreting wrong?

    Comment by dbabible — October 7, 2016 @ 4:10 pm BST Oct 7,2016 | Reply

  2. Since you know it’s “not nice” to post questions here why did you do it ?
    Why didn’t you at least make the effort to use one of the many postings on execution plans that are scattered throughout the blog ?
    Questions like this should be posted to the OTN database forum where there are plenty of people who could answer it.

    The simple answer is that you are interpreting the plan incorrectly.
    Line 10 calls line 11 to supply rowids that will be used access the table – Line 10 may have received 605K rowids in total, but it only “started” 2,869 times.

    Your interpretation of “line N produced X rows so line M should start X times” applies only when line N is used as a driver to line M – for example line 5 received 2,869 rows from line 6 so called line 8 to start 2,869 times.

    Comment by Jonathan Lewis — November 10, 2016 @ 10:19 pm BST Nov 10,2016 | Reply


RSS feed for comments on this post. TrackBack URI

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

Powered by WordPress.com.