一些sql

--获取当前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

  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;