My "Sessions" additional queries

[Christo Kutrovsky]

Since a person has liked my LOCKING and BLOCKING queries for PL/SQL Developer, i would like to share the other queries of my arsenal.

This is my "Events" query. It's rather simple, but it has the CPU time in the same place as waits, so that you can see where your session doing. Note that CPU TIME is only updated after the call is completed, as opposed to the wait events which are updated all the time:

select event, total_waits, total_timeouts, time_waited, average_wait, max_wait
from v$session_event where sid = :sid
union all
select names.name, 0, 0, stats.value as time_waited , 0, 0 as total_waits
from v$sesstat stats, v$statname names
where stats.sid = :sid
and names.Statistic# = stats.Statistic#
and names.name = 'CPU used by this session'
order by time_waited desc, total_waits desc

SQL O: The classic open cursors query, nothing fancy, just the per execution times is there, which is usefull in some cases. 9i only.

select sql_text, executions as exec,
decode(executions, 0, NULL,round(elapsed_time/executions/1000000,2)) as p_exec,
cpu_time, elapsed_time as elapsed, disk_reads as disk, buffer_gets,
rows_processed, sorts, users_opening, fetches, users_executing, loads,
first_load_time, invalidations, parse_calls, optimizer_mode, kept_versions, module, action
from v$sqlarea
where address = :sql_address
and hash_value = :sql_hash_value

SQL T: T stands for Text which abbreviated so that it doesn't take space in the tabs. It shows the full text of the query in SQL O. the "NEWLINE" is to mark dummy newlines. When I copy/paste it I replace "NEWLINE\n" with "" to get the original query as submited to oracle:

select sql_text||'NEWLINE' from v$sqltext_with_newlines
where address = hextoraw(:sql_address)
and hash_value = :sql_hash_value
order by piece

SQL P: Here starts the FANCY ones. This is a query which will show the SQL PLAN of the query in the previous 2 steps:

select rpad(' ', level*3)||operation||' '||options||
nvl2(object_name, ' -> ','')||object_name||decode(search_columns,0,NULL,' ('||search_columns||')') as OP,
cost, cardinality as CARD, bytes,
access_predicates as "ACCESS", filter_predicates as filter,temp_space,
partition_start ||nvl2(partition_start, ' - ', '')||partition_stop as P, partition_id, other, other_tag,
cpu_cost, io_cost, distribution, object_owner, optimizer, position
from (select * from V$SQL_PLAN
  where address = hextoraw(:sql_address) and hash_value = :sql_hash_value and child_number = 0) t
connect by prior id = parent_id start with id = 0

#2: If you have enabled ROW SOURCE statistics, this query will show you similar to tkprof output for the query involved earlier:

select last_output_rows as R, rpad(' ', level*3)||operation||' '||options||nvl2(object_name, ' -> ','')||object_name as OP,
cost, cardinality as CARD, bytes,
access_predicates as "ACCESS", filter_predicates as filter,temp_space,
partition_start ||nvl2(partition_start, ' - ', '')||partition_stop as P, partition_id, other, other_tag,
cpu_cost, io_cost, distribution, object_owner, optimizer, position, search_columns,
executions, last_starts, starts, output_rows, last_cr_buffer_gets, cr_buffer_gets,
last_cu_buffer_gets, cu_buffer_gets, last_disk_reads, disk_reads, last_disk_writes,
disk_writes, last_elapsed_time, elapsed_time, policy, estimated_optimal_size,
estimated_onepass_size, last_memory_used, last_execution, last_degree, total_executions,
optimal_executions, onepass_executions, multipasses_executions, active_time, max_tempseg_size, last_tempseg_size
from (select * from V$SQL_PLAN_STATISTICS_ALL
  where address = hextoraw(:sql_address) and hash_value = :sql_hash_value and child_number = 0) t
connect by prior id = parent_id start with id = 1

WORK: This is current work area query. Shows sort/hash areas and usage:

select operation_type, policy, estimated_optimal_size, estimated_onepass_size,
last_memory_used, last_execution, last_degree, total_executions, optimal_executions,
onepass_executions, multipasses_executions, active_time, max_tempseg_size, last_tempseg_size
from v$sql_workarea
where address = hextoraw(:sql_address)
and hash_value = :sql_hash_value

Act: This is my "activity" query just a number of statistics, which I consider critical in determining what's going on. Like a quick-view thing:

select names.name, decode(sign(instr(names.name, 'memory')), 1, trunc(stats.value/1024),stats.value) as value
from v$sesstat stats, v$statname names
where stats.sid = :sid
and names.Statistic# = stats.Statistic#
and (
names.name like 'user%' or
names.name like 'session pga memory%' or
names.name like 'session logical%' or
names.name like 'db block%' or
names.name like 'parse%' or
names.name like 'sorts%' or
names.name like 'immediate%' or
names.name like 'redo%time%' or
names.name like 'DBWR%' or
names.name in ('opened cursors current', 'CPU used by this session', 'physical writes',
'physical reads', 'redo size', 'user commits')
)
order by value desc, names.name

Temp: Temporary segment usage:

select extents, blocks, session_num, tablespace, contents, segtype, segfile#, segblk#, segrfno#, sqladdr, sqlhash
from v$tempseg_usage where session_addr = :saddr

Locks: This is the LOCKS query which another person liked very much, it is very fast even on large databases, this is the HOLDING locks list:

select  /*+ ORDERED */ ctime,type,
        decode(block,
                0, 'Not Blocking',
                1, 'Blocking',
                2, 'Global',
                to_char(block)) blocking,
        decode(lmode,
                0, '0 None',
                1, '1 Null',
                2, '2 Row-S (SS)',
                3, '3 Row-X (SX)',
                4, '4 Share',
                5, '5 S/Row-X (SSX)',
                6, '6 Exclusive',
                to_char(lmode)) held,
        decode(request,
                0, '0 None',
                1, '1 Null',
                2, '2 Row-S (SS)',
                3, '3 Row-X (SX)',
                4, '4 Share',
                5, '5 S/Row-X (SSX)',
                6, '6 Exclusive',
                to_char(request)) requested,
	o.owner as owner_or_type, o.object_Name as NAME
from  v$lock l, sys.all_objects o
where type = 'TM'
and l.sid = :sid
and o.object_id = l.id1
union all
select  /*+ ORDERED */ ctime,type,
        decode(block,
                0, 'Not Blocking',
                1, 'Blocking',
                2, 'Global',
                to_char(block)) blocking,
        decode(lmode,
                0, 'None',
                1, 'Null',
                2, 'Row-S (SS)',
                3, 'Row-X (SX)',
                4, '4 WAIT for ITL free',
                5, 'S/Row-X (SSX)',
                6, '6 ITL entry',
                to_char(lmode)) held,
        decode(request,
                0, 'None',
                1, 'Null',
                2, 'Row-S (SS)',
                3, 'Row-X (SX)',
                4, 'Share',
                5, 'S/Row-X (SSX)',
                6, 'Exclusive',
                to_char(request)) requested,
	'ROLLBACK' as owner_or_type, r.name as NAME
from v$lock l, v$rollname r
where type = 'TX'
and l.sid = :sid
and r.usn = trunc(l.ID1/65536)
union all
select  /*+ ORDERED */ ctime,type,
        decode(block,
                0, 'Not Blocking',
                1, 'Blocking',
                2, 'Global',
                to_char(block)) blocking,
        decode(lmode,
                0, '0 None',
                1, '1 Null',
                2, '2 Row-S (SS)',
                3, '3 Row-X (SX)',
                4, '4 Share',
                5, '5 S/Row-X (SSX)',
                6, '6 Exclusive',
                to_char(lmode)) held,
        decode(request,
                0, '0 None',
                1, '1 Null',
                2, '2 Row-S (SS)',
                3, '3 Row-X (SX)',
                4, '4 Share',
                5, '5 S/Row-X (SSX)',
                6, '6 Exclusive',
                to_char(request)) requested,
	'LOCK NAME' as owner_or_type, nvl(al.name, l.id1) as NAME
from  v$lock l, sys.dbms_lock_allocated al
where type = 'UL'
and l.sid = :sid
and al.lockid (+) = l.id1

Locks-b: And of course the blocking (who is this session blocking):

select  /*+ ORDERED */ ctime,type,
        decode(block,
                0, 'no',
                1, 'BLOCKED',
                2, 'Global',
                to_char(block)) blocking,
        decode(lmode,
                0, '0 None',
                1, '1 Null',
                2, '2 Row-S (SS)',
                3, '3 Row-X (SX)',
                4, '4 Share',
                5, '5 S/Row-X (SSX)',
                6, '6 Exclusive',
                to_char(lmode)) held,
        decode(request,
                0, '0 None',
                1, '1 Null',
                2, '2 Row-S (SS)',
                3, '3 Row-X (SX)',
                4, '4 Share',
                5, '5 S/Row-X (SSX)',
                6, '6 Exclusive',
                to_char(request)) requested,
	'BY SID' as owner_or_type, to_char(SID) as NAME
from v$lock
where (id1, id2) in (select id1, id2 from v$lock where sid = :sid and request <> 0)
and block = 1
and sid <> :sid
union all
select  /*+ ORDERED */ ctime,type,
        decode(block,
                0, 'no',
                1, 'BLOCKING',
                2, 'Global',
                to_char(block)) blocking,
        decode(lmode,
                0, '0 None',
                1, '1 Null',
                2, '2 Row-S (SS)',
                3, '3 Row-X (SX)',
                4, '4 Share',
                5, '5 S/Row-X (SSX)',
                6, '6 Exclusive',
                to_char(lmode)) held,
        decode(request,
                0, '0 None',
                1, '1 Null',
                2, '2 Row-S (SS)',
                3, '3 Row-X (SX)',
                4, '4 Share',
                5, '5 S/Row-X (SSX)',
                6, '6 Exclusive',
                to_char(request)) requested,
	'SID WAITING ON ME' as owner_or_type, to_char(SID) as NAME
from v$lock
where (id1, id2) in (select id1, id2 from v$lock where sid = :sid and block = 1)
and request <> 0
and sid <> :sid

SQL SS: This shows segment level statistics for the objects involved in the query (as derivied from the plan). This query is slow, as the select from v$segment_statistics is slow:

select object_type as TYPE, owner||'.'||object_name||nvl2(subobject_name,'->','')||
subobject_name as WHAT, statistic_name, value
from v$segment_statistics s where (obj#) in
  (select /*+ NO_MERGE */ object# from v$sql_plan where address = :sql_address and  hash_value = :sql_hash_value)
order by value desc

Enjoy and feel free to comment on those.

I am sure most of you will just love the SQL PLAN query.

P.S.
If you use them, and give them to other people, pelase indicate original source.

 

[Marco Kalter]

Thanks

 

[Christo Kutrovsky]

For those who are using my #2 query (#2: If you have enabled ROW SOURCE statistics...) the one that shows the row source statistics, you have to keep in mind that the session must exit for the data to be populated, like source rows and etc.

 

[Christo Kutrovsky]

Actually, you don't have to close the session, but you have to close the cursor somehow.

And it seems that PL/SQL developer is not closing it even if you fetch all rows , so you just have to execute something else to get the data populated.

(like select 1 from dual)

 

[Christo Kutrovsky]

Which actually poses the question, why PL/SQL developer doesn't close the cursor when all rows are fetched?

 

[Marco Kalter]

There does not seem to be a reason to close the cursor. Except for this situation maybe...

 

[Christo Kutrovsky]

WARNING.

Whoever is using my queries for the explain plan, I have discovered a bug in the plan build-up.

Here's the corrected queries:

SQL P:

select rpad(' ', depth*3)||operation||' '
||options||nvl2(object_name, ' -> ','')||object_name||decode(search_columns,0,NULL,' ('||search_columns||')') as OP,
cost, cardinality as CARD, bytes,
access_predicates as "ACCESS",
filter_predicates as filter,
round(temp_space/1024/1024) as TMP_MB,
partition_start ||nvl2(partition_start, ' - ', '')||partition_stop as P,
partition_id, other, other_tag,
cpu_cost, io_cost, distribution, object_owner, optimizer, position
from (select * from V$SQL_PLAN where address = hextoraw(:sql_address) and hash_value = :sql_hash_value and child_number = 0) t
connect by prior id = parent_id start with id = 0

And the #2 one:

select output_rows as TOT_R, last_output_rows as R, rpad(' ', depth*3)||operation||' '||options||nvl2(object_name, ' -> ','')||object_name as OP,
cost, cardinality as CARD, bytes,
access_predicates as "ACCESS", filter_predicates as filter,round(temp_space/1024/1024) as temp_mb,
partition_start ||nvl2(partition_start, ' - ', '')||partition_stop as P, partition_id, other, other_tag,
cpu_cost, io_cost, distribution, object_owner, optimizer, position, search_columns,
executions, last_starts, starts, last_cr_buffer_gets, cr_buffer_gets,
last_cu_buffer_gets, cu_buffer_gets, last_disk_reads, disk_reads, last_disk_writes, disk_writes, round(last_elapsed_time/1000000,2)
as LAST_ELA_TIME, round(elapsed_time/1000000,2) as ELAPSED_TIME, policy, estimated_optimal_size, estimated_onepass_size, last_memory_used,
last_execution, last_degree, total_executions, optimal_executions, onepass_executions,
multipasses_executions, round(active_time/1000000,2) as active_time_avg, max_tempseg_size, last_tempseg_size
from (select * from V$SQL_PLAN_STATISTICS_ALL where address = hextoraw(:sql_address) and hash_value = :sql_hash_value and child_number = 0) t
connect by prior id = parent_id start with id = 1

Sorry about that ... the plan_table and those seems to differ in layout ...

 

[Christo Kutrovsky]

Grr, I found more issues with my queries.

I will post an update when i've resolved all of them.

 

[Christo Kutrovsky]

Welcome back, to the PLAN_TABLE queries

Ok, I think i've got all issues resolved, I am yet to find incositensies in the explain plan tree when comparing with the oracle explain plan.

Those are a little changed, they alternate between the current sql and the previous one, so that you can see the explain plan of that select that *just* finished.

Appropriate displaying aids are in place

Query text (with child number). Look for /* prev */ when it's the previous one:

select decode(:sql_address, '00','/* prev */ ')||
'Child number: '||child_number as query_text
from v$sql
where address = hextoraw(decode(:sql_address, '00', :prev_sql_addr, :sql_address))
and hash_value = decode(:sql_hash_value, 0, :prev_hash_value, :sql_hash_value)
union all
select * from (
select sql_text||'NEWLINE' from v$sqltext_with_newlines
where address = hextoraw(decode(:sql_address, '00', :prev_sql_addr, :sql_address))
and hash_value = decode(:sql_hash_value, 0, :prev_hash_value, :sql_hash_value)
order by piece
)

And your favorites, the explain plan one with previous SQL too:

Keep in mind it's fixed to child number 0, so if you analyze a table for example, and the query is not changed, you will see the old plan. I still have not decided how to incorporate multiple child numbers.

You can tell if you have multiple childs, with the SQL T one, which reports the child numbers

select decode(:sql_address, '00',decode(id,0,'/* prev */ '))
||rpad(' ', depth*3)||operation||' '||options||nvl2(object_name, ' -> ','')||object_name||decode(search_columns,0,NULL,' ('||search_columns||')') as OP,
cost, cardinality as CARD, bytes,
access_predicates as "ACCESS", filter_predicates as filter,round(temp_space/1024/1024) as TMP_MB,
partition_start ||nvl2(partition_start, ' - ', '')||partition_stop as P, partition_id, other, other_tag,
cpu_cost, io_cost, distribution, object_owner, optimizer, id
from (select * from V$SQL_PLAN where address = hextoraw(decode(:sql_address, '00', :prev_sql_addr, :sql_address)) and hash_value = decode(:sql_hash_value, 0, :prev_hash_value, :sql_hash_value) and child_number = 0) t
connect by prior id = parent_id start with id = 0
order by id, position

Enjoy.

I still haven't changed the row source one, as i am yet to think of a way to represent multiple childs.

Hint: add a tab with child number = 1, 2 .. and etc if you need to.

 

[Christo Kutrovsky]

All comments are accepted on this one.

Including "wish list" and recommendations.

 

[aotte]

With the new Explain Plan, I get that it runs for a while and then I get: "ORA-03113: end-of-file on communication channel".

When I take the PreviousSQL logic out, it runs just fine...

Did you experience anything like that on your system? We're on Oracle 9.2.0.4. using Oracle81 connectivity.

 

[Christo Kutrovsky]

Some co-workers prefer the "SID" way and manually popping in the SID when using my query.

They were having similar problems, "end of communcation" which basically a oracle process crash on the server, and I've traced down the problem to be happening only when selecting specific fields, on specific rows from the v$ plan view.

I guess it's an Oracle bug. I am yet to see it however crashin when using it directly with the hash/address pair.

Will keep you posted if I happen to find something.

Just use it without the previous logic i guess ...

 

[aotte]

Originally posted by Christo Kutrovsky:
I guess it's an Oracle bug. I am yet to see it however crashin when using it directly with the hash/address pair.

Just use it without the previous logic i guess ...

Actually I use two tabs, one with the current hash/address pari, and one with the previous. That works just fine for me. Just wanted to let you know of the issue.

Tanks for the queries. Love them. They have saved me some headaches supporting stuff.

 

[Christo Kutrovsky]

To the people that like my session queries.

Here's my newest one, supports both previous, and current statement, and mutiple childs, all shown in a single query. The first columns will describe you which plan is what. I have tested it for PERFORMANCE, and they work quite well...

"0-P" - means Child 0, previous SQL
"0" - child 0, current SQL

select decode(id, 1, child_number)||decode(:sql_address,'00','-P') as C,
output_rows as TOT_R, last_output_rows as R, rpad(' ', depth*3)||operation||' '||options||nvl2(object_name, ' -> ','')||object_name as OP,
cost, cardinality as CARD, bytes,
access_predicates as "ACCESS", filter_predicates as filter,round(temp_space/1024/1024) as temp_mb,
partition_start ||nvl2(partition_start, ' - ', '')||partition_stop as P, partition_id, other, other_tag,
cpu_cost, io_cost, distribution, object_owner, optimizer, position, search_columns,
executions, last_starts, starts, last_cr_buffer_gets, cr_buffer_gets, last_cu_buffer_gets, cu_buffer_gets, last_disk_reads, disk_reads,
last_disk_writes, disk_writes, round(last_elapsed_time/1000000,2) as LAST_ELA_TIME, round(elapsed_time/1000000,2) as ELAPSED_TIME, policy,
estimated_optimal_size, estimated_onepass_size, last_memory_used, last_execution, last_degree, total_executions, optimal_executions,
onepass_executions, multipasses_executions, round(active_time/1000000,2) as active_time_avg, max_tempseg_size, last_tempseg_size
from (select * from V$SQL_PLAN_STATISTICS_ALL where address = hextoraw(:sql_address) and hash_value = :sql_hash_value
union all select * from V$SQL_PLAN_STATISTICS_ALL where address = hextoraw(:prev_sql_addr) and hash_value = :prev_hash_value) t
connect by address = prior address and hash_value = prior hash_value and child_number = prior child_number and prior id = parent_id  start with id = 1
order by address, hash_value, child_number desc, id, position

 

[AlienZzzz]

Originally posted by Christo Kutrovsky:
To the people that like my session queries.

Here's my newest one, supports both previous, and current statement, and mutiple childs, all shown in a single query. The first columns will describe you which plan is what. I have tested it for PERFORMANCE, and they work quite well...

"0-P" - means Child 0, previous SQL
"0" - child 0, current SQL

select decode(id, 1, child_number)||decode(:sql_address,'00','-P') as C,
output_rows as TOT_R, last_output_rows as R, rpad(' ', depth*3)||operation||' '||options||nvl2(object_name, ' -> ','')||object_name as OP,
cost, cardinality as CARD, bytes,
access_predicates as "ACCESS", filter_predicates as filter,round(temp_space/1024/1024) as temp_mb,
partition_start ||nvl2(partition_start, ' - ', '')||partition_stop as P, partition_id, other, other_tag,
cpu_cost, io_cost, distribution, object_owner, optimizer, position, search_columns,
executions, last_starts, starts, last_cr_buffer_gets, cr_buffer_gets, last_cu_buffer_gets, cu_buffer_gets, last_disk_reads, disk_reads,
last_disk_writes, disk_writes, round(last_elapsed_time/1000000,2) as LAST_ELA_TIME, round(elapsed_time/1000000,2) as ELAPSED_TIME, policy,
estimated_optimal_size, estimated_onepass_size, last_memory_used, last_execution, last_degree, total_executions, optimal_executions,
onepass_executions, multipasses_executions, round(active_time/1000000,2) as active_time_avg, max_tempseg_size, last_tempseg_size
from (select * from V$SQL_PLAN_STATISTICS_ALL where address = hextoraw(:sql_address) and hash_value = :sql_hash_value
union all select * from V$SQL_PLAN_STATISTICS_ALL where address = hextoraw(:prev_sql_addr) and hash_value = :prev_hash_value) t
connect by address = prior address and hash_value = prior hash_value and child_number = prior child_number and prior id = parent_id  start with id = 1
order by address, hash_value, child_number desc, id, position

=(( only on 9 oracle //// i have 8 =(

 

[AAZ]

In Oracle 10g you can use dbms_xplain package like this:

SELECT t.plan_table_output || CHR(10) plan_table_output FROM    table(dbms_xplan.display_cursor(:sql_id, :sql_child_number)) t
/* concatenate */

But you need to copy output to the text editor or Sql Window, for example.