????1.???CPU????????м??????sql??????
????SELECT T.CPU_TIME??--??????????е?cpu???
????T.EXECUTIONS??--???????????д???
????T.ELAPSED_TIME??--??????????е????
????T.SORTS??
????T.PARSE_CALLS??--???????????
????T.OPTIMIZER_COST??--????????????
????T.SQL_TEXT--??е?sql???
????FROM SYS.V_$SQL T
????WHERE T.OPTIMIZER_COST IS NOT NULL
????AND T.EXECUTIONS > 500
????ORDER BY T.OPTIMIZER_COST DESC;
????2.????????????sql???
????select * from
????(select b.username username?? a.DISK_READS reads?? a.EXECUTIONS exec?? a.DISK_READS/decode(a.EXECUTIONS??0??1??a.EXECUTIONS) rds_exec_ratio?? a.SQL_TEXT statement
????from sys.v_$sqlarea a?? sys.dba_users b
????where a.PARSING_USER_ID = b.user_id
????and (b.username = '' or b.username = '')
????and a.DISK_READS >= 50 ) t
????where t.rds_exec_ratio >= 1
????order by t.rds_exec_ratio desc;
????3.??????????
???????plsql?е???????е?????????????????????????????sql???ж????????????
???????????????sql:
????select * from SYS.V_$SESSION t where t.STATUS='ACTIVE' and osuser='' and type='' and username=''
??????????α??????
????select * from v$open_cursor where sid = :sid??
?????????sql?????
????select sql_text from v$sqltext_with_newlines where address = hextoraw(:sql_address) and hash_value = :sql_hash_value order by piece / concatenate /
?????????????????
????select names.name?? stats.statistic#?? stats.value from v$sesstat stats?? v$statname names where stats.sid = :sid and names.Statistic# = stats.Statistic# order by stats.statistic#
????????????????
????select l.*?? o.owner object_owner?? o.object_Name from sys.all_objects o?? v$lock l where l.sid = :sid and l.type = 'TM' and o.object_id = l.id1
??????????????sql???????
????SELECT T.SID??
????T1.SQL_TEXT??
????T1.CPU_TIME??
????T1.EXECUTIONS??
????T1.OPTIMIZER_COST??
????T1.ELAPSED_TIME??
????T1.ELAPSED_TIME / T1.EXECUTIONS AS UNIT_ELAPSED_TIME??
????T1.CPU_TIME / T1.EXECUTIONS AS UNIT_CPU_TIME
????FROM SYS.V$SESSION T?? SYS.V$SQL T1
????WHERE T.STATUS = 'ACTIVE'
????AND TYPE = ''
????AND USERNAME = ''
????AND T1.HASH_VALUE = T.SQL_HASH_VALUE
????ORDER BY T1.CPU_TIME DESC