sql監控與調優(sql monitoring and tuning) （轉載）

奮奮熊發表於2013-04-17
SQL
一、監控正在執行的sql的統計資訊(11g)       
SQL> select *
2 from (select a.sid session_id,
3 a.sql_id,
4 a.status,
5 a.cpu_time / 1000000 cpu_sec,
6 a.buffer_gets,
7 a.disk_reads,
8 b.sql_text sql_text
9 from v$sql_monitor a, v$sql b
10 where a.sql_id = b.sql_id
11 order by a.cpu_time desc)
12 where rownum <= 20;
未選定行
SQL> select *
2 from (select a.sid session_id,
3 a.sql_id,
4 a.status,
5 a.cpu_time / 1000000 cpu_sec,
6 a.buffer_gets,
7 a.disk_reads,
8 substr(b.sql_text, 1, 15) sql_text
9 from v$sql_monitor a, v$sql b
10 where a.sql_id = b.sql_id
11 and a.status = 'EXECUTING'
12 order by a.disk_reads desc)
13 where rownum <= 20;
未選定行
二、顯示查詢語句執行時的資訊。(11g)
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 mem_bytes,
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;
三、監控sql執行的開始時間，已執行時間和剩餘執行時間。
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 FORMAT 9,999,990 HEAD "Total|Work"
COL percent FORMAT 999.90 HEAD "%|Done"
--
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語句
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去監控（詳細略）。
SQL> @?/rdbms/admin/awrrpt
SQL> @?/rdbms/admin/ashrpt
SQL> @?/rdbms/admin/addmrpt
SQL> @?/rdbms/admin/spcreate.sql
SQL> @?/rdbms/admin/spauto.sql
SQL> @?/rdbms/admin/spreport.sql
六、使用作業系統命令去檢查佔用資源較多的查詢。
（1）$ps -e -o pcpu,pid,user,tty,args | sort -n -k 1 -r | head
65.4 1165 oracle ? ora_j001_orcl
3.0 17571 oracle ? oracleorcl (LOCAL=NO)
0.8 17357 oracle ? oracleorcl (LOCAL=NO)
0.8 15950 oracle ? oracleorcl (LOCAL=NO)
0.7 605 oracle ? oracleorcl (LOCAL=NO)
0.5 17062 oracle ? oracleorcl (LOCAL=NO)
0.5 16259 oracle ? oracleorcl (LOCAL=NO)
0.3 15315 oracle ? oracleorcl (LOCAL=NO)
0.2 29187 oracle ? oracleorcl (LOCAL=NO)
0.2 17419 oracle ? oracleorcl (LOCAL=NO)
注：
1、ps命令解釋：-e顯示全部程式 -o顯示使用者指定的資訊，如-o pcpu,pid,user,tty,args
2、| ：管道命令，把第一個的命令輸出作為第二個命令的輸入。
3、sort命令：-n依照數值的大小排序 ；-k key[position1,position2]如：-k 1；-r倒序輸出。
4、head：檢視命令。
（2）查處最佔cpu時間的sql語句。
select 'USERNAME : ' || s.username || chr(10) ||
'OSUSER : ' || s.osuser || chr(10) ||
'PROGRAM : ' || s.program || chr(10) ||
'SPID : ' || p.spid || chr(10) ||
'SID : ' || s.sid || chr(10) ||
'SERIAL# : ' || s.serial# || chr(10) ||
'MACHINE : ' || s.machine || chr(10) ||
'TERMINAL : ' || s.terminal || chr(10) ||
'SQL TEXT : ' || q.sql_text
from v$session s
,v$process p
,v$sql q
where s.paddr = p.addr
and s.sql_id = q.sql_id
and p.spid = 605;
（3）$ ps -e -o pmem,pid,user,tty,args | grep -i oracle | sort -n -k 1 -r | head
注：grep指令用於查詢內容包含指定的範本樣式的檔案，如果發現某檔案的內容符合所指定的範本樣式，預設grep指令會把含有範本樣式的那一列顯示出來。若不指定任何檔名稱，或是所給予的檔名為“-”，則grep指令會從標準輸入裝置讀取資料。其中-i為忽略字元大小寫的差別。
（4）作業系統命令：top,vmstat,iostat,mpstat,netstat, and traceroute.
七、顯示執行計劃。
SQL> conn / as sysdba
SQL> desc plan_table;
SQL> @?/rdbms/admin/utlxplan
SQL> @?/sqlplus/admin/plustrce
SQL> grant plustrace to star1;
SQL> set autotrace on;
類似還有：
SET AUTOTRACE ON
SET AUTOTRACE OFF
SET AUTOTRACE ON EXPLAIN
SET AUTOTRACE ON EXPLAIN STAT
SET AUTOTRACE ON STAT
SET AUTOTRACE TRACE
八、通過DBMS_XPLAN包生成執行計劃
SQL> desc plan_table
SQL> @?/rdbms/admin/utlxplan
SQL> select * from table(dbms_xplan.display);
九、sql跟蹤。（略）
十、執行計劃解釋。（略）
十一、獲得優化指導。
1.
GRANT ADMINISTER 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;
2.
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 => 'STAR_APR',
scope => 'COMPREHENSIVE',
time_limit => 1800,
task_name => 'tune1',
description => 'Basic tuning example');
END;
/
3.
SQL> SELECT task_name FROM user_advisor_log WHERE task_name LIKE 'tune1';
4.Run the tuning task:
SQL> EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name=>'tune1');
5. Display the SQL Tuning Advisor report. Run the following SQL statements to display the output:
SET LONG 10000
SET LONGCHUNKSIZE 10000
SET LINESIZE 132
SET PAGESIZE 200
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('tune1') FROM dual;
 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26077805/viewspace-758653/，如需轉載，請註明出處，否則將追究法律責任。

相關文章