--获取当前session的sid
SELECT userenv('sid') FROM dual;
SELECT sid FROM v$mystat WHERE rownum=1;
--根据SID,从v$sql中找到相应SQL的HASH_VALUE和ADDRESS
SELECT * FROM v$session;
SELECT * FROM v$sql;
SELECT a.sql_text,a.address,a.hash_value
FROM v$sql a,
v$session b
WHERE a.hash_value=b.sql_hash_value
AND b.sid =
&sid;
--根据hash_value和address的值,从v$sql_plan中找到真实的执行计划 2964720583 608C68CC
select * from v$sql_plan where hash_value='&hash_value' and address='&add';
SELECT LPAD (oper, LENGTH (oper) + LEVEL * 2, ' ') oper,
cost
FROM
(SELECT object_name
|| ':'
|| operation
|| ' '
|| OPTIONS AS oper,
cost,
id,
parent_id
FROM v$sql_plan
WHERE hash_value = '&hash_value'
AND address = '&address'
)
START WITH id = 0
SELECT userenv('sid') FROM dual;
SELECT sid FROM v$mystat WHERE rownum=1;
--根据SID,从v$sql中找到相应SQL的HASH_VALUE和ADDRESS
SELECT * FROM v$session;
SELECT * FROM v$sql;
SELECT a.sql_text,a.address,a.hash_value
FROM v$sql a,
v$session b
WHERE a.hash_value=b.sql_hash_value
AND b.sid =
&sid;
--根据hash_value和address的值,从v$sql_plan中找到真实的执行计划 2964720583 608C68CC
select * from v$sql_plan where hash_value='&hash_value' and address='&add';
SELECT LPAD (oper, LENGTH (oper) + LEVEL * 2, ' ') oper,
cost
FROM
(SELECT object_name
|| ':'
|| operation
|| ' '
|| OPTIONS AS oper,
cost,
id,
parent_id
FROM v$sql_plan
WHERE hash_value = '&hash_value'
AND address = '&address'
)
START WITH id = 0
CONNECT BY PRIOR id = parent_id;
--查看 data dictionary cache 的命中率:
SELECT SUM(gets),
SUM(getmisses),
(1-(SUM(getmisses)/(SUM(gets)+SUM(getmisses)))) hitratio
FROM v$rowcache;
--查看data dictionary cache 的大小
SELECT SUM(sharable_mem) FROM v$sqlarea;