sql监控和优化

sql监控和优化


实时监控sql统计信息(消耗资源)


通过v$sql_monitor监控近乎实时的消耗资源
select * from(
select
a.sid session_id
,a.sql_id
,a.status
,a.cpu_time/1000000 cpu_sec
,a.buffer_gets
,a.disk_reads
,b.sqltext sql_test
from v$sql_monitor a,v$sql b
where a.sql_id=b.sql_id
order by a.cpu_time desc)
where rownum<=20;


按照磁盘读的次数排序的执行计划
select * from(
select
a.sid session_id
,a.sql_id
,a,status
,a.cpu_time/1000000 cpu_sec
,a.buffer_gets
,a.disk_reads
,substr(b.sql_text,1,15) sql_text
from v$sql_monitor a,v$sql b
where a.sql_id=b.sql_id
and a.status='EXECUTING'
order by a.disk_reads desc)
where rownum <=20;


显示查询的执行计划进展(消耗时间)


显示执行计划中每步的行数和所使用的内存
col sid format 99999
col status format a15
col start_time format a12
col plan_line_id format 99999 head "plan id"
col plan_options format a16
col mem_bytes format 99999999
col temp_bytes format 99999999
set linesize 132 pagesi 100 trimsp on
break on sid on status on start_time nodup skip 1
--
select
a.sid
,a.status
,to_char(a.sql_exec_start,'yymmdd hh24:mi') start_time
,a.plan_line_id
,a.plan_operation
,a.plan_options
,a.output_rows
,a.workarea_mem memmbytes
,a.workarea_tempseg temp_bytes
from v$sql_plan_monitor a,v$sql_monitor b
where a.status not like '%DONE%'
and a.key=b.key
order by a.sid,a.sql_exec_start,a.plan_line_id;


生成一个执行计划中实时的查询进度报告
set lines 3000 pages 0 long 1000000trimspool on
报告最后一次监控的查询
select dbms_sqltune.report_sql_monitor from dual;
报告特定会话
select dbms_sqltune.report_sql_monitor(session_id=>185) from dual;
将报告生成为一个html文件
set lines 3000 pages 0 long 1000000 trimspool on
spool out.html
select dbms_sqltune.report_sql_monitor(session_id=>185,
event_detail=>'YES',report_level=>'ALL',type=>'HTML')
from dual;
spool off;


还需多长时间来完成sql


目前正在运行的sql语句估计的进展
set linesize 141 trimspool on pages 66
col username fromat a8 head "User|Name"
col opname format a16 head "Operation|Type"
col sql_text format a33 head "Sql|Text" TRUNC
col start_time format a15 head "Start|Time"
col how_long format 99,990 head "Time|Run"
col secs_left format 99,990 head "Appr.|Secs Left"
col sofar format 9,999,990 head "Work|Done"
col totalwork fromat 9,999,990 head "Total|Work"
col percent fromat 999,90 head "%|Dnoe"
--
select
a.username
,a.opname
,b.sql_text
,to_char(a.start_time,'DD-MON-YY HH24:MI') start_time
,a.elapsed_seconds how_long
,a.time_remaining secs_left
,a.sofar
,a.totalwork
,round(a.sofar/a.totalwork*100,2) percent
from v$session_longops a,v$sql b
where a.sql_address = b.address
and a.sql_hash_value=b.hash_value
and a.sofar<>a.totalwork
and a.totalwork!=0;


识别资源密集型sql语句(哪些sql语句消耗的资源最多)


根据cpu时间使用下面的查询识别10个资源最密集的查询
select * from(
select
sql_text
,buffer_gets
,disk_reads,sorts
,cpu_time/1000000 cpu sec
,executions
,rows_processed
from v$sqlstats
order by cpu_time desc)
where rownum<11;


显示第一个解析查询的用户信息
select * from(
select
b.sql_text
,a.username
,b.buffer_gets
,b.disk_reads
,b.sorts
,b.cpu_time/1000000 cpu sec
from v$sqlarea b ,dba_users a
where b.parsing_user-id=a.user_id
order by b.cpu_time desc)
where rownum <11;


awr(自动负载存储库),ash(活动会话历史记录),addm(自动数据库诊断监控),statspack(性能采集)


awr(整个系统的性能和最占资源的sql)
@?/rdbms/admin/awrrpt
为特定语句生成awr报告
@?/rdbms/admin/awrsqrpt.sql


addm(那些语句需要调优)
@?/rdbms/admin/addmrpt


ash(最近运行并且可能仅执行少量时间的短暂的sql语句)
@?/rdbms/admin/ashrpt


statspack(免费,识别性能较差的sql语句)
安装statspack(创建一个perfstat用户,该用户拥有statspack存储库)
@?/rdbms/admin/spcreate.sql
启用统计信息自动收集
@?/rdbms/admin/spauto.sql
创建报告
@?/rdbms/admin/spreport.sql
补充:statspack文档放在$ORACLE_HOME/rdbms/admin/spdoc.txt


查询动态性能视图个数
select count(*) from dictionary where table_name like 'V$%';


在操作系统中识别资源密集型查询(当多个oracle,mysql,pssql运行在一台服务器上,想查哪个系统会话消耗最多资源,确定该会话与数据库相关,最终找出特定的sql)


识别占用大部分cpu的操作系统进程
ps -e -o pcpu,pid,user,tty,args|sort -n -k 1 -r |head
识别占用大部分内存的操作系统进程
ps -e -o pmem,pid,user,tty,args|grep -i oracle|sort -n -k 1 -r|head


根据上条语句信息,进入相关数据库,确定什么类型的程序与14028(假设)相关
select
'USERNAME:'||s.username||chr(10)||
'OSUSER  :'||s.osuser  ||chr(10)||
'PROGRAM :'||s.program ||chr(10)||
'SPID    :'||s.spid    ||chr(10)||
'SID     :'||s.sid     ||chr(10)||
'SERIAL# :'||s.serial# ||chr(10)||
'MACHINE :'||s.machine ||chr(10)||
'TERMINAL:'||s.termianl
from v$session s,v$process p
where s.paddr=p.addr
and p.spid='&PID_FROM_OS';


查询与操作系统进程14028(假设)相关的sql语句
select
'USERNAME:'||s.username||chr(10)||
'OSUSER  :'||s.osuser  ||chr(10)||
'PROGRAM :'||s.program ||chr(10)||
'SPID    :'||s.spid    ||chr(10)||
'SID     :'||s.sid     ||chr(10)||
'SERIAL# :'||s.serial# ||chr(10)||
'MACHINE :'||s.machine ||chr(10)||
'TERMINAL:'||s.termianl||chr(10)||
'SQL TEXT:'||q.sql_text
from v$session s,v$process p,v$sql q
where s.paddr=p.addr
and p.spid='&PID_FROM_OS'
and s.sql_id=q.sql_id;


使用autotrace显示执行计划(查看优化器怎么规划数据的检索,以构建查询的结果集)


配置autotrace
确认schema下是否有一个plan_table
desc plan_table;
若不存在则创建
@?/rdbms/admin/utlxplan
验证plustrace角色的权限
select username,granted_role from user_role_privs
where granted_role='PLUSTRACE'
若没有权限
conn / as sysdba
@?/rdbms/admin/plustrace
grant plustrace to star1;


生成执行计划
set autotrace on;


补充:
只生成统计信息不生成结果集
set autotrace trace explain
查看autotrace选项
set autotrace help


使用dbms_xplan生成执行计划


确认schema下是否有一个plan_table
desc plan_table;
若不存在则创建
@?/rdbms/admin/utlxplan


为解析的语句在plan_table中创建一个条目
explain plan for select emp_id from emp;


显示解释计划
select * from table(dbms_xplan.display);
也可以通过视图查看
create view pt as select * from table(dbms_xplan.dispaly);
select * from pt;


补充:
通过display_awr函数为某查询运行解释计划
select * from table(dbms_xplan.dispaly_awr('xxxxxxxx'));
生成会话中最近执行的查询的解释计划
select * from table(dbms_xplan.dispaly_cursor(null,null));


跟踪会话的所有sql(某程序调用数百条sql,确定哪些语句占最多资源)


启用会话跟踪
exec dbms_session.set_sql_trace(sql_trace=>true);
执行想跟踪的语句
关闭会话跟踪
exec dbms_session.set_sql_trace(sql_trace=>false);
查看user_dump_dest初始化参数的值
show parameter user_dump_dest
进入目录
cd <上方命令得出的地址>
找到相关trace查看


tkprof转成人类可读模式
tkprof RDB11_ORA_21846.trc readable.txt explain=用户名/密码 sys=no


补充(以下都是):
10g或更高版本
启用会话跟踪
exec dbms_monitor.session_trace_enable;
执行想跟踪的语句
关闭会话跟踪
exec dbms_monitor.session_trace_disable;


启用等待与绑定变量信息的跟踪
exec dbms_monitor.session_trace_enable(waits=>true,binds=>true);


用参数来起禁用跟踪
select username,sid,serial# from v$session;
exec dbms_monitor.session_trace_enable(session_id=>1234,serial_num=>12345);
执行想跟踪的语句
exec dbms_monitor.session_trace_disable(session_id=>1234,serial_num=>12345);
参数可以一起用
exec dbms_monitor.session_trace_disable(session_id=>1234,serial_num=>12345,waits=>true,binds=>true);


在另一个会话中启用跟踪
select username,sid,serial# from v$session;
exec dbms_system.set_sql_trace_in_session(sid=>200,serial#=>5,sql_trace=>true);
exec dbms_system.set_sql_trace_in_session(sid=>200,serial#=>5,sql_trace=>false);
dbms_system也可以用来捕获等待事件
exec dbms-systwm.set_ev(si=>123,se=>1234,ev=>12345,le=>8,nm=>' ');
exec dbms-systwm.set_ev(si=>123,se=>1234,ev=>12345,le=>0,nm=>' ');


用dbms_support
@?/rdbms/admin/dbmssupp.sql
exec dbms_support.start_trace(waits=>true,binds=>true);
exec dbms_support.stop_trace;
在不属于我们的会话里跟踪
exec dbms_support.start_trce_in_session(sid=>200,serial#=>5,waits=>true,binds=>true);
exec dbms_support.stop_trace_in_session(sid=>200,serial#=>5);


跟踪数据库中所有会话
alter system set sql_trace=true;
alter system set sql_trace=false;


使用oradebug
select spid os_pid,pid ora_pid from v$process
where addr=(select paddr from v$session where username='***')
oradebug setospid 31064;
oradebug EVENT10046 trace name context forever,level 8;
oradebug tracefile_name;
oradebug event 10046 trace name context off;
oradebug help


解释执行计划(如何理解执行计划)
生成解释计划
set autotrace trace explain
select * from * where *;


此部分为解释并举例如何调试,并无脚本


获取调优建议


使用Advisor
特权用户进入
grant administrator sql tuning set to &&tune_user;
grant advisor to &&tune_user;
grant create any sql profile to &&tune_user;
grant alter any sql profile to &&tune_user;
grant drop any sql profile to &&tune_user;
11g可以将grant……any sql profile替换为
grant administrator sql management object to &&tune_user;


创建优化任务
declare
tune_task_name varchar2(30);
tune_sql clob;
begin
tune_sql:='select a.emp_name,b.dept_name from emp a,dept b';
tune_task_name:=dbms_sqltune.create_tuning_task(
sql_text=>tune_sql,
user_name=>'START_APR',
scope=>'COMPREHENSIVE',
time_limit=>1800,
task_name=>'tune1',
description=>'Basic tuning example'
);
end;
/
确认调优任务存在
select task_name from user_advisor_log where task_name like 'tune1';
运行调优任务
exec dbms_sqltune.execute_tuning_task(task_name=>'tune1');
显示advisor报告
set long 10000
set longchunksize 10000
set linesize 132
selpagesize 200
--
select dbms_sqltune.report_tuning_task('tune1') from dual;


补充:
删除调优任务
exec dbms_sqltune.drop_tuning_task(task_name=>'tune1');


强制查询使用自己的执行计划(强制优化器使用索引)


用index提示指示优化器
select /*+ INDEX(emp emp_idx1)*/
ename
from emp
where ename='***';


用表别名
select /*+ FULL(a) */ ename
from emp a
where ename='***';


查看优化器统计信息(查看表是否存在统计信息)


检查统计信息是否准确
select a,num_rows/b.actual_rows
from user_tables a,(select count(*) actual_rows from &&table_name) b
where a.table_name=upper('&&table_name');
生成脚本来显示表过时的统计信息
set head off pages 0 lines 132 trimspool on
spo show_stale.sql
select
'select'||''''||table_name||': '||''''|| '||' ||chr(10)||
'round(decode(b.actual_rows,0,0,a.num_rows/b.acyual_rows),2)'||chr(10)||
'from user_tables a'||
',(select count(*) actual_rows from '|| table_name||') b'||chr(10)||
'where a.table_name=(' ||''''||table_name||''''|| ');'
from user_tables;
spo off;
这个脚本会生成一个show_stale.sql脚本,执行它来显示表过时的统计信息


显示上次分析表的时间
select
table_name
,last_analyzed
,monitoring
from user_tables
order by last_analyzed;


显示上次分析索引的时间
select index_name
,last_analyzed
from user_indexes
order by last_analyzed;


使用user_tab_statistics视图查询显示统计信息
select 
table_name
,partition_name
,last_analyzed
,num_rows
,sample_size
,stale_stats
from user_tab_statistics
order by last_analyzed;


通过user_ind_statistics视图查看索引的统计信息
select
index_name
,partition_name
,last_analyzed
,num_rows
,sample_size
,stale_stats
from user_ind_statistics
order by last_analyzed;


验证自上次统计信息生成以来有多少insert,update,delete,truncate发生
select
table_name
,partition_name
,inserts
,updates
,deletes
,truncated
,timestamp
from user_tab_modifications
order by table_name;


生成统计信息


为star1 schema生成统计信息
exec dbms_stats.gather_schema_stats(ownname=>'START1',estime_percent=>dbms_stats.auto_sample_size,degree=>dbms_stats.auto_degree,cascade=>true);



























  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值