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

一、監控正在執行的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;

文件地址：http://space.itpub.net/7204674/viewspace-622214

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

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

相關文章