oracle数据库sad值,oracle的sql备用

下面语句有些需要有一定的权限，或者执行时需要做针对性的修改。

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