--即时SQL
select a.sql_id, a.MODULE, count(*), sql_text
from v$active_session_history a, v$sqlarea b
where a.sql_id = b.sql_id(+)
and sample_time > systimestamp - 1 / 1440
group by a.sql_id, a.MODULE, sql_text
order by count(*) desc
--根据SQLID 查执行计划
SELECT * FROM TABLE(dbms_xplan.display_cursor(:sql_id, null, 'outline'))
--查询AWR 报告
SELECT * FROM TABLE(dbms_xplan.display_awr(:sql_id))
/*
--当前session
SELECT USERENV('SID') FROM DUAL;
--执行计划
explain plan for :SQL;
select * from table(DBMS_XPLAN.display);
SELECT * FROM TABLE(dbms_xplan.display_awr(:sid, :plan_hash_value));
select * from DBA_HIST_SQLBIND where sql_id = '5dckzxzkp84s7'
*/
--查询超过6S的sql
select * from V$SESSION_LONGOPS;
--查询使用扫描的SQL
select ltrim(sql_text)
from V$SQLAREA
WHERE SQL_ID IN
(select distinct sql_id
from V$SQL_PLAN
where OPERATION = 'TABLE ACCESS'
and OPTIONS = 'FULL'
AND OBJECT_OWNER NOT IN
('SYS', 'SYSMAN', 'SYSTEM', 'MDSYS', 'WKSYS', 'DBSNMP'))
AND SQL_FULLTEXT NOT LIKE '/*%'
and MODULE = 'JDBC Thin Client'
--查询是否有超过200的EXTEND
select segment_name,
tablespace_name,
extents
from dba_segments
where owner not in ('SYS','SYSTEM')
and extents >200
--根据进程ID，查询SESSION
select sql_text
from v$sqlarea
where address in
(select sql_address
from v$session
where paddr in (select addr from v$process where spid = :spid));
--查询数据库等待事件
Select count(*), event
from v$session_wait
where event not in ('smon timer', 'pmon timer', 'rdbms ipc message',
'SQL*Net message from client')
group by event
order by 1 desc;
--查看分区快到期的情况
select a.*
from dba_tab_partitions a,
(select table_owner, table_name, max(PARTITION_POSITION) pp
from dba_tab_partitions
group by table_owner, table_name) cc
where a.table_name = cc.table_name
and a.table_owner = cc.table_owner
and a.PARTITION_POSITION = cc.pp - 1
order by a.table_owner, a.table_name;
--查看session 占用CPU的情况
select ss.sid, se.command, ss.value CPU, se.username, se.program
from v$sesstat ss, v$session se
where ss.statistic# in
(select statistic#
from v$statname
where name = 'CPU used by this session')
and se.sid = ss.sid
and ss.sid > 6
order by ss.value;
--查看某SESSION CPU具体情况
select s.sid, w.event, w.wait_time, w.seq#, q.sql_text
from v$session_wait w, v$session s, v$process p, v$sqlarea q
where s.paddr = p.addr
and s.sid = :sid
and s.sql_address = q.address;
--占用空间大的表
select * from DBA_SEGMENTS where BYTES>1024*1024*50 order by bytes desc ;
--手工提取AWR报告
begin
dbms_workload_repository.create_snapshot;
end;
--sql id 查询session
select * from v$session where sql_id=:sql_id;
--查询归档状态
select dbid, name, log_mode from v$database;;
--查询 字段集情况
select * from v$nls_parameters;
--查询无效对象
select object_name,
object_type,
owner,
status
from dba_objects
where status !='VALID'
and owner not in ('SYS','SYSTEM')
and object_type in
('TRIGGER','VIEW','PROCEDURE','FUNCTION')
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18900329/viewspace-1743643/，如需转载，请注明出处，否则将追究法律责任。
转播
