ORACLE 11G SQL 调优

--即时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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/18900329/viewspace-1743643/