oracle认证辅导:oracle优化和管理sql2
6.v$session中的列部分说明
0 - WAITING (当前等待的 Session)
-2 - WAITED UNKNOWN TIME (最后等待持续时间未知)
-1 - WAITED SHORT TIME (最后的等待 《1/100 秒)
》0 - WAITED KNOWN TIME (WAIT_TIME = 最后等待持续时间)
STATUS VARCHAR2(8) Status of the session:
ACTIVE - Session currently executing SQL
INACTIVE - sql及其session没有释放或正常退出……
KILLED - Session marked to be killed
CACHED - Session temporarily cached for use by Oracle*XA
SNIPED - Session inactive, waiting on the client
7.根据上面的地址找对应的SQL
SELECT *
FROM v$sqltext
WHERE address=HexToRaw(‘0700000036D20268’)
AND hash_value=2348072240
ORDER BY address,hash_value,piece;
--或者
SELECT *
FROM v$sqlarea
WHERE address=HexToRaw(‘0700000036D20268’)
AND hash_value=2348072240
ORDER BY address,hash_value;
HexToRaw(‘0700000036D20268’)和hash_value=2348072240
对应的是v$session中的sql_address,sql_hash_value,
这两个值分别在楼上的SQL中有取出
8.根据ID找SQL
SELECT tb.command_type,
Tb.Piece,
Tb.Sql_Text
FROM V$open_Cursor Ta,
V$sqltext Tb
WHERE Ta.Sid = &SID
AND Ta.Address = Tb.Address
AND Ta.Hash_Value = Tb.Hash_Value
ORDER BY Tb.Address,
Tb.Hash_Value,
Tb.Piece ASC;
9.寻找没有使用绑定变量的sql语句
SELECT Plan_Hash_Value,
COUNT(*)
FROM V$sql
WHERE Plan_Hash_Value 《》 0
GROUP BY Plan_Hash_Value
ORDER BY 2 DESC;
10.查询正打开的游标
SELECT User_Name,
Sql_Text
FROM V$open_Cursor
WHERE Sid IN (SELECT Sid
FROM (SELECT Sid,
Serial#,
Username,
Program
FROM V$session
WHERE Status = ‘ACTIVE’))
子查询
SELECT Sid,
Serial#,
Username,
Program
FROM V$session
WHERE Status = ‘ACTIVE’
查出的是不活动的session的sid |
|