oracle数据库优化11g,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/，如需转载，请注明出处，否则将追究法律责任。