Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to obtain the v$latch data source experiment in Oracle, which has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, let the editor take you to understand it.

Experimental environment: Oracle Rac 11.2.0.3

First get the definition of v$latch:

Only the view information related to v_$latch can be obtained through PL/SQL or get ddl and other conventional ways.

You need to obtain the ddl of v$latch through a special method

You can execute select * from vault latch when set autotrace traceonly is enabled. You can learn that the data source of the table is x$kslltr. The result is as follows:

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | | 1 | 352 | 0 (0) | 00:00:01 |

| | * 1 | FIXED TABLE FULL | X$KSLLTR | 1 | 352 | 0 (0) | 00:00:01 |

Because of the version problem, the output structure of trace is slightly different through sql_trace. The ddl can be obtained in 11.2.0.4.5, but not in my experimental environment. The method is as follows

Alter session set sql_trace=true

Select * from v$latch

Alter session set sql_trace=false

Select * from v$diag_info where name='Default Trace File'

Tkprof xxxx.trc

The specific underlying data sources of v$latch are obtained by 10053trace as follows

Alter session set events' 10053 trace name context forever,level 1'

Select * from v$latch

Alter session set events' 10053 trace name context off'

Select * from v$diag_info where name='Default Trace File'

Vim / u01/app/oracle/diag/rdbms/irac/IRAC1/trace/IRAC1_ora_16791.trc

Be careful! Please do not use sys users to do 10053 trace, you will not get the results.

The result is as follows. As can be seen from the output, the final converted sql statement is as follows

Final query after transformations:* UNPARSED QUERY IS *

SELECT "LT". "KSLLTADDR"ADDR", "LT". "KSLLTNUM"LATCH#", "LT". "KSLLTLVL"LEVEL#", "LT". "KSLLTNAM"NAME", "LT". "KSLLTHSH"HASH", "LT". "KSLLTWGT"GETS", "LT". "KSLLTWFF"MISSES", "LT". "KSLLTWSL"SLEEPS", "LT". "KSLLTNGT"IMMEDIATE_GETS", "LT". "KSLLTNFA"IMMEDIATE_MISSES" "LT". "KSLLTWKC"WAITERS_WOKEN", "LT". "KSLLTWTH"WAITS_HOLDING_LATCH", "LT". "KSLLTHST0"SPIN_GETS", "LT". "KSLLTHST1"SLEEP1", "LT". "KSLLTHST2"SLEEP2", "LT". "KSLLTHST3"SLEEP3", "LT". "KSLLTHST4"SLEEP4", "LT". "KSLLTHST5"SLEEP5", "LT". "KSLLTHST6"SLEEP6", "LT". "KSLLTHST7"SLEEP7" "LT". "KSLLTHST8"SLEEP8", "LT". "KSLLTHST9"SLEEP9", "LT". "KSLLTHST10"SLEEP10", "LT". "KSLLTHST11"SLEEP11", "LT". "KSLLTWTT"WAIT_TIME" FROM SYS. "X$KSLLTR"LT" WHERE "LT". "INST_ID" = USERENV ('INSTANCE')

Kkoqbc: optimizing query block SEL$88122447 (# 0)

In order to get the underlying data source of v$latch.

Of course, if you want to create a 10053trace every time you want to see a view that is not recorded in the official document, you can simply and easily query the data source of the view in the following way:

SQL > SELECT * FROM v$fixed_view_definition WHERE view_name = 'Venture LATCH'

VIEW_NAME

VIEW_DEFINITION

V$LATCH

Select addr,latch#,level#,name,hash,gets,misses,sleeps,immediate_gets, immediate

_ misses,waiters_woken,waits_holding_latch,spin_gets, sleep1,sleep2,sleep3,sleep4

, sleep5,sleep6,sleep7,sleep8,sleep9, sleep10,sleep11,wait_time from gv$latch whe

Re inst_id = USERENV ('Instance')

SQL > SELECT * FROM v$fixed_view_definition WHERE view_name = 'GV$LATCH'

VIEW_NAME

VIEW_DEFINITION

GV$LATCH

Select lt.inst_id,lt.kslltaddr,lt.kslltnum,lt.kslltlvl,lt.kslltnam, lt.ks

Llthsh,lt.kslltwgt,lt.kslltwff, lt.kslltwsl,lt.kslltngt,lt.kslltnfa,lt.ks

Lltwkc, lt.kslltwth,lt.ksllthst0,lt.ksllthst1,lt.ksllthst2, lt.ksl

Lthst3,lt.ksllthst4,lt.ksllthst5,lt.ksllthst6,lt.ksllthst7, lt.ksllthst8

Lt.ksllthst9,lt.ksllthst10, lt.ksllthst11, lt.kslltwtt from x$kslltr lt

SQL >

Thank you for reading this article carefully. I hope the article "how to get v$latch data Source experiment in Oracle" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.