Re: Interesting new V$ table in 11.2

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Fri, 16 Sep 2011 23:05:48 +0000 (UTC)
Message-ID: <pan.2011.09.16.23.05.48_at_gmail.com>

On Fri, 16 Sep 2011 22:44:29 +0000, Mladen Gogala wrote:

> 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".

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

Received on Fri Sep 16 2011 - 18:05:48 CDT