Interesting new V$ table in 11.2

[Mladen Gogala]

Database version 11.2 (I don't have any 11.1 at hand) has the table
V$SESSION_BLOCKERS which lists blocking sessions on any instance. Before
this, I've had to write "black magic" queries using GV$LOCK and ID1 and
ID2 columns and GV$SESSION using V$ROW_OBJECT#, FILE#, BLOCK# and ROW#.
This helps a lot. Good job, Oracle! Diagnosing lock contention has just
become a lot easier, easy enough for a DBA 2.0.



--
http://mgogala.byethost5.com

[joel garry]

Oops, that was supposed to be select sid, username, serial#, process,
nvl(sql_id,0), sql_address, blocking_session, wait_class, event, p1,
p2, p3, seconds_in_wait from v$session where blocking_session_status =
'VALID' OR sid IN (select blocking_session from v$session where
blocking_session_status = 'VALID')

jg
--
@home.com is bogus.
Must. Get. Coffee.

[dombrooks]

See also the blocking_*, final_blocking_*, etc columns in the ever-
extending v$session.

[Mladen Gogala]

The view is a direct select from an X$ table:
SQL> select view_definition
2 from v$fixed_view_definition
3 where view_name='GV$SESSION_BLOCKERS';

VIEW_DEFINITION
--------------------------------------------------------------------------------
select inst_id, sid, sess_serial#, wait_id, wait_event,
wait_event_text,
blocker_instance_id, blocker_sid, blocker_sess_serial# from
X$KSDHNG_SESSION_BLOCKERS


SQL> desc X$KSDHNG_SESSION_BLOCKERS
Name Null? Type
----------------------------------------- --------
----------------------------
ADDR RAW(4)
INDX NUMBER
INST_ID NUMBER
SID NUMBER
SESS_SERIAL# NUMBER
WAIT_ID NUMBER
WAIT_EVENT NUMBER
WAIT_EVENT_TEXT VARCHAR2(64)
BLOCKER_INSTANCE_ID NUMBER
BLOCKER_SID NUMBER
BLOCKER_SESS_SERIAL# NUMBER

I can't see the source of an X$ table.

--
http://mgogala.byethost5.com

[Mladen Gogala]

On Wed, 14 Sep 2011 09:13:47 -0700, dombrooks wrote:

> See also the blocking_*, final_blocking_*, etc columns in the ever-
> extending v$session.

Yes, V$SESSION_WAIT and V$SESSION views have effectively been merged.



--
http://mgogala.byethost5.com

[John Hurley]

Take a look at v$wait_chains ... been around since 11.1 ( or
earlier ).

You can use blocking_session and blocking_session_status from v
$session when state = 'WAITING' then go after wait_chains ...

[joel garry]

Taking a gander at (10.2) dbconsole with dbconsole, could this be the
magic?

SELECT
s.status,s.client_identifier,s.client_info,s.sql_id,s.sql_child_number,a.name,s.last_call_et,s.prev_sql_id,s.prev_child_number,s.module,s.action,s.blocking_session,s.row_wait_file#,s.row_wait_block#,s.row_wait_row#,s.event,s.wait_class,decode(s.wait_time,
0,s.seconds_in_wait,wait_time),s.p1text,s.p2text,s.p3text,s.p1,s.p2,s.p3,decode(s.p2text,'object
#',s.p2,s.row_wait_obj#),s.failed_over,s.pdml_status,s.pddl_status,s.pq_status,s.current_queue_duration,s.sql_trace
FROM v$session s, audit_actions a WHERE s.sid = :1 AND s.command =
a.action AND s.serial# = :2

We can all make fun of DBA 2.0, but I can't help but wonder if
actually having to put out a product that does this has some feedback
as to what should be there to begin with.

jg
--
@home.com is bogus.

"Stopped OEM and Problem went away. ! Is OEM really Schroedinger's
Cat? !" - Howard Latham

[Mladen Gogala]

On Wed, 14 Sep 2011 10:58:19 -0700, John Hurley wrote:


> Take a look at v$wait_chains ... been around since 11.1 ( or earlier ).

Nice!



--
http://mgogala.byethost5.com

[Noons]

Am I the only one using
select * from dba_waiters
???
From dba_views:
SQL> select text from dba_views where view_name = 'DBA_WAITERS';
TEXT
--------------------------------------------------------------------------------
select /*+ordered */ w.sid
,s.ksusenum
,decode(r.ksqrsidt,
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distributed Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalidation',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table',
r.ksqrsidt)
,decode(l.lmode,
0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
l.lmode)
,decode(bitand(w.p1,65535),
0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
to_char(bitand(w.p1,65535)))
,r.ksqrsid1, r.ksqrsid2
from v$session_wait w, x$ksqrs r, v$_lock l, x$ksuse s
where w.wait_Time = 0
and w.event like 'enq:%'
and r.ksqrsid1 = w.p2
and r.ksqrsid2 = w.p3
and r.ksqrsidt = chr(bitand(p1,-16777216)/16777215)||
chr(bitand(p1,16711680)/65535)
and l.block = 1
and l.saddr = s.addr
and l.raddr = r.addr
and s.inst_id = userenv('Instance')

[Mark D Powell]

Just for general information the v$session_wait veiw was always build
on the same internal (x$) table as v$session. It is just that the
information was not exposed by Oracle in the v$session view
definition.

HTH -- Mark D Powell --

[Mladen Gogala]

How can I check that? V$FIXED_VIEW_DEFINITION contains only the first
4000 characters of the SQL, there is nothing in any of the bsq files and
cdfixed.sql contains this for each and every table:

create or replace view gv_$session as select * from gv$session;
create or replace public synonym gv$session for gv_$session;
grant select on gv_$session to select_catalog_role;

create or replace view gv_$license as select * from gv$license;
create or replace public synonym gv$license for gv_$license;
grant select on gv_$license to select_catalog_role;

create or replace view gv_$transaction as select * from gv$transaction;
create or replace public synonym gv$transaction for gv_$transaction;
grant select on gv_$transaction to select_catalog_role;

This is obviously intended to hide the source code of the V$ views and is
very successful.


--
http://mgogala.byethost5.com

[Maxim Demenko]

On 16.09.2011 21:50, Mladen Gogala wrote:
> On Fri, 16 Sep 2011 06:56:50 -0700, Mark D Powell wrote:
>
>> On Sep 14, 12:56 pm, Mladen Gogala<gogala.mla...@gmail.com> wrote:
>>> On Wed, 14 Sep 2011 09:13:47 -0700, dombrooks wrote:
>>>> See also the blocking_*, final_blocking_*, etc columns in the ever-
>>>> extending v$session.
>>>
>>> Yes, V$SESSION_WAIT and V$SESSION views have effectively been merged.
>>>
>>> --http://mgogala.byethost5.com
>>
>>
>> Just for general information the v$session_wait veiw was always build on
>> the same internal (x$) table as v$session. It is just that the
>> information was not exposed by Oracle in the v$session view definition.
>>
>> HTH -- Mark D Powell --
>
> How can I check that? V$FIXED_VIEW_DEFINITION contains only the first
> 4000 characters of the SQL, there is nothing in any of the bsq files and
> cdfixed.sql contains this for each and every table:
>

I've seen recorded in the 10046 trace the full text of queries for the
views which are shortened due to 4000 byte restriction. However, if i
remember correctly, only

V$FLASH_RECOVERY_AREA_USAGE
V$BACKUP_CONTROLFILE_SUMMARY
V$BACKUP_DATAFILE_SUMMARY

were shortened, the rest doesn't reach the limit.

Best regards

Maxim

[Mladen Gogala]

On Fri, 16 Sep 2011 23:28:10 +0200, Maxim Demenko wrote:

> I've seen recorded in the 10046 trace the full text of queries for the
> views which are shortened due to 4000 byte restriction.

You don't need 10046 trace, V$FIXED_VIEW_DEFINITION will show you the
create statements.

> However, if i
> remember correctly, only
>
> V$FLASH_RECOVERY_AREA_USAGE
> V$BACKUP_CONTROLFILE_SUMMARY
> V$BACKUP_DATAFILE_SUMMARY
>
> were shortened, the rest doesn't reach the limit.

There are few more than that but the rest indeed are visible.

SQL> select view_name from V$FIXED_VIEW_DEFINITION
where length(view_definition)>=3999;

VIEW_NAME
------------------------------
GV$SESSION
GV$SQL_SHARED_CURSOR
GV$STREAMS_CAPTURE
V$RECOVERY_AREA_USAGE
GV$ACTIVE_SESSION_HISTORY
GV$WLM_PCMETRIC
V$RMAN_BACKUP_SUBJOB_DETAILS
V$BACKUP_DATAFILE_SUMMARY
V$BACKUP_CONTROLFILE_SUMMARY
GV$IOSTAT_FILE

10 rows selected.



In case of the GV$SESSION view, the from clause isn't visible, so it's
impossible to see which tables are being used. Not even the cunning plan
like this will reveal the tables:


SQL> select referenced_name from dba_dependencies
2 where name='GV$SESSION';

REFERENCED_NAME
----------------------------------------------------------------
GV_$SESSION


SQL> select referenced_name from dba_dependencies
2 where name='GV_$SESSION';

REFERENCED_NAME
----------------------------------------------------------------
GV$SESSION


I also tried re-factoring the V$FIXED_VIEW_DEFINITION view in vain hope
that the underlying column might be a CLOB, to no avail:

SQL> select view_definition from V$FIXED_VIEW_DEFINITION
2 where view_name='GV$FIXED_VIEW_DEFINITION';

VIEW_DEFINITION
--------------------------------------------------------------------------------
select i.inst_id,kqfvinam,kqftpsel from x$kqfvi i, x$kqfvt t where i.indx
= t.in
dx


The "SEL" column in the X$KQFVT is also VARCHAR2(4000), which means that
I have no way of extracting the DDL for the 10 views mentioned above.

SQL> desc x$kqfvt

Name Null? Type
----------------------------------------- --------
----------------------------
ADDR RAW(4)
INDX NUMBER
INST_ID NUMBER

KQFTPSEL VARCHAR2(4000)



Mark's tip puts me on the right track, because it is possible to get the
DDL and the tables for GV$SESSION_WAIT. My cunning plan has failed, which
should not come as a big surprise to anyone who has watched "Black Adder".
--
http://mgogala.byethost5.com

[Mladen Gogala]

It is, however, possible to find out which tables take part in GV$SESSION
view using the event 10046:


SQL ID: 80ztmgy196d94 Plan Hash: 643620770

select *
from
gv$session


call count cpu elapsed disk query
current rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.00 0.03 0 0
0 0
Execute 1 0.00 0.00 0 0
0 0
Fetch 2 0.00 0.01 0 0
0 27
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 4 0.00 0.05 0 0
0 27

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ----------
---------------------------------------------------
27 27 27 NESTED LOOPS (cr=0 pr=0 pw=0
time=17549 us cost=0 size=10260 card=30)
27 27 27 NESTED LOOPS (cr=0 pr=0 pw=0
time=1073 us cost=0 size=3630 card=30)
27 27 27 FIXED TABLE FULL X$KSLWT (cr=0 pr=0
pw=0 time=489 us cost=0 size=1710 card=30)
27 27 27 FIXED TABLE FIXED INDEX X$KSLED
(ind:2) (cr=0 pr=0 pw=0 time=233 us cost=0 size=64 card=1)
27 27 27 FIXED TABLE FIXED INDEX X$KSUSE
(ind:1) (cr=0 pr=0 pw=0 time=17986 us cost=0 size=221 card=1)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total
Waited
---------------------------------------- Waited ----------
------------
SQL*Net message to client 2 0.00
0.00
SQL*Net message from client 2 0.05
0.05
********************************************************************************

Comparing that to the DDL for GV$SESSION_WAIT verifies Mark's statement:

SQL> select view_definition from V$FIXED_VIEW_DEFINITION

2 where view_name='GV$SESSION_WAIT';

VIEW_DEFINITION
--------------------------------------------------------------------------------
select s.inst_id,s.kslwtsid,s.kslwtseq,e.kslednam,
e.ksledp1,s.kslwtp1,s.kslwtp1
r,e.ksledp2, s.kslwtp2,s.kslwtp2r,e.ksledp3,s.kslwtp3,s.kslwtp3r,
e.ksledclassid
, e.ksledclass#, e.ksledclass, decode(s.kslwtinwait, 0,decode
(bitand(s.ks
lwtflags,256), 0,-2, decode(round
(s.kslwtstime/1
0000), 0,-1, round
(s.kslwtstime/10
000))), 0), decode(s.kslwtinwait,0,round((s.kslwtstime
+s.kslwtltime)/1000
000), round(s.kslwtstime/1000000)), decode(s.kslwtinwait,1,'WAITING',
decode(b
itand(s.kslwtflags,256),0,'WAITED UNKNOWN TIME', decode(round
(s.kslwtstime/100
00),0,'WAITED SHORT TIME', 'WAITED KNOWN TIME'))), s.kslwtstime, decode
(s.ksl
wtinwait,0,to_number(null), decode(bitand
(s.kslwtflags,64),64,0,s.kslwttrem)),
s.kslwtltime from x$kslwt s, x$ksled e where s.kslwtevt=e.indx


It's not that I doubt Mark, I only want to be able to prove it.


--
http://mgogala.byethost5.com

[Jonathan Lewis]

>
>
> Mark's tip puts me on the right track, because it is possible to get the
> DDL and the tables for GV$SESSION_WAIT. My cunning plan has failed, which
> should not come as a big surprise to anyone who has watched "Black
> Adder".
> --
> http://mgogala.byethost5.com

If you want to see the object names, do an "explain plan".
From memory
v$session is based on x$ksuse (but added some bits in 10g and 11g)
v$session_wait is based on x$ksusecst

To show that they the two x$ are the same array with different bit exposed:

a) Select addr from the two arrays - the addresses are identical
b) join x$kqfta to x$kqfco (x$ tables and x$ columns) and you will see that
x$ksuse has a big can in the column positions, which happens to match the
column positions for x$ksusecst.

--
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

"Mladen Gogala" <gogala...@gmail.com> wrote in message
news:pan.2011.09...@gmail.com...

[Mladen Gogala]

On Sat, 17 Sep 2011 00:27:52 +0100, Jonathan Lewis wrote:


>>
>> Mark's tip puts me on the right track, because it is possible to get
>> the DDL and the tables for GV$SESSION_WAIT. My cunning plan has failed,
>> which should not come as a big surprise to anyone who has watched
>> "Black Adder".
>> --
>> http://mgogala.byethost5.com
>
>
> If you want to see the object names, do an "explain plan". From memory
> v$session is based on x$ksuse (but added some bits in 10g and 11g)
> v$session_wait is based on x$ksusecst
>
> To show that they the two x$ are the same array with different bit
> exposed:
>
> a) Select addr from the two arrays - the addresses are identical b)
> join x$kqfta to x$kqfco (x$ tables and x$ columns) and you will see that
> x$ksuse has a big can in the column positions, which happens to match
> the column positions for x$ksusecst.

Thanks. Yes, I didn't think of that.



--
http://mgogala.byethost5.com