下面语句有些需要有一定的权限,或者执行时需要做针对性的修改。
1、统计数据库的执行情况:
select s.parsing_schema_name 执行账号,
decode(sq.command_type, '3', 'select', '2', 'insert', '6', 'UPDATE', '7', 'DELETE', '26', 'lock', '50', 'explain plan',
'189', 'merge', '170', 'call', '1', 'CREATE table', '9', 'CREATE UNIQUE INDEX', '15', 'alter table', '62',
' ANALYZE TABLE', '25', 'ALTER PROCEDURE', '98', 'ALTER PACKAGE', sq.command_type) sql_type,s.sql_id, s.plan_hash_value,
round(avg(s.elapsed_time_delta / 1000 / 1000), 2) elapsed_time_delta_all,
round(avg(s.elapsed_time_delta / s.executions_delta / 1000 / 1000), 2) elapsed_time_delta_one,
round(avg(s.elapsed_time_total / s.executions_delta / 1000 / 1000),2) elaped_time,
round(avg(s.cpu_time_delta / s.executions_delta / 1000 / 1000), -2) cpu_time_delta,
round(avg(s.iowait_delta / s.executions_delta / 1000 / 1000), -2) iowait_delta,
round(avg(s.clwait_delta / s.executions_delta / 1000 / 1000), -2) clwait_delta, sum(executions_delta) 执行次数
from dba_hist_sqlstat s, dba_hist_sqltext sq
where s.elapsed_time_delta > 107975155
and s.snap_id > 438 and s.executions_delta > 0 and s.sql_id = sq.sql_id and sq.command_type not in (47, 170)
and s.parsing_schema_name not in ('SYS')
group by decode(sq.command_type, '3', 'select', '2', 'insert', '6', 'UPDATE', '7', 'DELETE', '26', 'lock', '50', 'explain plan',
'189', 'merge', '170','call', '1', 'CREATE table','9', 'CREATE UNIQUE INDEX','15', 'alter table','62',
' ANALYZE TABLE', '25', 'ALTER PROCEDURE', '98', 'ALTER PACKAGE', sq.command_type),
s.parsing_schema_name,s.plan_hash_value, s.sql_id
order by 6 desc
2、查看sql执行计划语句:
select * from table(dbms_xplan.display_awr('63gmans2kjk6q'));-- 查看已经运行结束的sql执行计划
select * from table(dbms_xplan.display_cursor('63gmans2kjk6q'));-- 查看正在运行的sql执行计划
--说明:如果sql text很长,上面的结果不能显示完整的sql内容,可以从下面的sql查看完整的sql text
3、查看sql text:
select * from dba_hist_sqltext t where t.sql_id ='d7dufuwwxns21';-- 看已经运行结束的sql
select * from gv$sqlarea t where t.sql_id ='63gmans2kjk6q';--查看正在运行的sql
4、查看sql中超过50万行的表,执行计划中全表扫描:
select distinct p.OBJECT_OWNER, t.SQL_ID, p.OBJECT_NAME, p.OBJECT_OWNER, a.num_rows, a.blocks
from gv$sqltext t, gv$sql_plan p, sys.all_tables a
where t.hash_value = p.hash_value and p.OBJECT_NAME = a.table_name and a.num_rows > 500000 -- 小表
and p.operation = 'TABLE ACCESS' and p.options = 'FULL'
-- and p.OBJECT_OWNER in (/*'SAD',*/'RCR')
and t.SQL_TEXT like '%:%' -- : 绑定变量,只考虑程序中的sql
order by 6 desc;
5、查看历史sql中超过50万行的表,执行计划中全表扫描
select distinct p.OBJECT_OWNER, p.SQL_ID, p.OBJECT_NAME, p.OBJECT_OWNER, a.num_rows, a.blocks
from dba_hist_sqltext t, dba_hist_sql_plan p, sys.all_tables a
where t.sql_id = p.sql_id and p.OBJECT_NAME = a.table_name
and a.num_rows > 500000 -- 小表
and p.operation = 'TABLE ACCESS' and p.options = 'FULL'
-- and p.OBJECT_OWNER in (/*'SAD',*/'RCR') --输入具体的schema
and t.SQL_TEXT like '%:%' -- : 绑定变量,只考虑程序中的sql
order by 6 desc;
6、部分sequence当前最大值接近定义的最大值,未来有风险
select a.max_value - a.last_number, a.last_number / a.max_value, a.last_number, a.max_value, a.* from dba_sequences a
where a.last_number / a.max_value > 0.8 and a.increment_by > 0
--and a.max_value > 0