從作業系統命令TOP到資料庫的最佳化

dbasdk發表於2014-08-19
    對於一個剛開始學習資料庫最佳化的新手DBA來說,當使用者反饋系統比較慢時,他會非常緊張,面對資料庫,他無從下手,不知道從哪裡開始著手來最佳化資料庫,查詢系統
存在的問題。
    今天我們透過作業系統命令TOP,來最佳化資料,我們如何把作業系統與資料庫關聯起來哪,我們主要是透過作業系統TOP命令找到最消耗資源OS PID程式。
透過OS PID與V$PROCESS動態效能試圖進行管理。我們知道V$PROCESS是被認為從作業系統到資料庫的入口,而進入資料庫內部,程式需要建立回話(SESSION)執行資料庫操作的SQL語句,一般情況下,一個程式只會建立一個回話,但是在特殊的情況下,一個程式也可以建立多個資料庫回話。回話的資訊是透過動態效能試圖V$SESSION來進行管理和體現的。
    那麼我們透過一個實驗來看一下,如何完成從作業系統命令到資料庫內部的操作,我們模擬一個出現故障的場景,我們透過作業系統命令TOP,進行觀察,找到作業系統程式佔CPU資源比較高的程式。
1.首先我們建立一個測試表t1,向表中插入一些資料。
SQL>create table t1 as select * from emp;
SQL>insert into t1 as select * from t1;
SQL>/
SQL>/
SQL>/
SQL>/
使表T1大約有幾萬條記錄。
2.開3,4個會話,其中表t1有幾萬行的資料,同時執行,立刻查詢上面的語句
declare
v1 emp.sal%type;
begin
for n in 1..100 loop
for k in 1..100 loop
select count(*) into v1 from t1;
end loop;
dbms_lock.sleep(1);
end loop;
end;
/

3.透過作業系統命令TOP找到消耗CPU資源的程式

top - 12:57:42 up 19 min,  2 users,  load average: 1.18, 0.35, 0.23
Tasks: 132 total,   2 running, 130 sleeping,   0 stopped,   0 zombie
Cpu(s): 20.5%us,  5.9%sy,  0.0%ni, 73.1%id,  0.5%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   1034664k total,   883716k used,   150948k free,   125584k buffers
Swap:  4120664k total,        0k used,  4120664k free,   609440k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                 
 5182 oracle    19   0  368m  50m  48m S 37.9  5.0   0:03.57 oracle                                                  
    1 root      15   0  2160  652  564 S  0.0  0.1   0:02.30 init                                                    
    2 root      RT  -5     0    0    0 S  0.0  0.0   0:00.05 migration/0                                             
    3 root      34  19     0    0    0 S  0.0  0.0   0:00.00 ksoftirqd/0                                             
    4 root      RT  -5     0    0    0 S  0.0  0.0   0:00.04 migration/1                                             
    5 root      34  19     0    0    0 S  0.0  0.0   0:00.00 ksoftirqd/1                                             
    6 root      10  -5     0    0    0 S  0.0  0.0   0:00.04 events/0                                                
    7 root      10  -5     0    0    0 S  0.0  0.0   0:00.00 events/1                                                
    8 root      11  -5     0    0    0 S  0.0  0.0   0:00.01 khelper                                                 
    9 root      10  -5     0    0    0 S  0.0  0.0   0:00.00 kthread                                                 
   13 root      10  -5     0    0    0 S  0.0  0.0   0:00.10 kblockd/0                                               
   14 root      10  -5     0    0    0 S  0.0  0.0   0:00.02 kblockd/1                                               
   15 root      16  -5     0    0    0 S  0.0  0.0   0:00.00 kacpid                                                  
  179 root      12  -5     0    0    0 S  0.0  0.0   0:00.00 cqueue/0                                                
  180 root      12  -5     0    0    0 S  0.0  0.0   0:00.00 cqueue/1                                                
  183 root      11  -5     0    0    0 S  0.0  0.0   0:00.00 khubd                                                   
  185 root      10  -5     0    0    0 S  0.0  0.0   0:00.00 kseriod                                                 
  252 root      18   0     0    0    0 S  0.0  0.0   0:00.00 khungtaskd                                              
  253 root      17   0     0    0    0 S  0.0  0.0   0:00.00 pdflush                                                 
  254 root      15   0     0    0    0 S  0.0  0.0   0:00.03 pdflush 

4.我們看到程式PID等於5182,我們下面的一個指令碼,關聯V$PROCESS試圖和V$SESSION試圖、V$SQLTEST試圖,可以找出這個程式正在執行的SQL語句,這裡只需要一個“發動”條件,就是程式(PID):
SQL>SELECT /*+ ORDERED */
 sql_text
  FROM v$sqltext a
 WHERE (a.hash_value, a.address) IN
       (SELECT DECODE(sql_hash_value, 0, prev_hash_value, sql_hash_value),
               DECODE(sql_hash_value, 0, prev_sql_addr, sql_address)
          FROM v$session b
         WHERE b.paddr =
               (SELECT addr FROM v$process c WHERE c.spid = '&pid'))
 ORDER BY piece ASC;
/
提示輸入變數值。
Enter value for pid: 5182

old   9:  (SELECT addr FROM v$process c WHERE c.spid = '&pid'))
new   9:  (SELECT addr FROM v$process c WHERE c.spid = '5182'))

SQL_TEXT
----------------------------------------------------------------
declare v1  number; begin for n in 1..100 loop for k in 1..100 l
oop select count(*) into v1 from t1; end loop; dbms_lock.sleep(1
); end loop; end;

注:這裡我們使用了3個動態效能試圖,獲取到了執行的SQL語句。我們的邏輯是:
1)首先輸入一個PID,這個PID即是process id,也就是在TOP命令中看到的PID.
2)透過PID和v$process.spid相關,我們可以獲得process的詳細資訊。
3)透過v$process.addr和v$session.paddr相關聯,可以獲取session的相關詳細資訊。
4)再結合v$sqltest,即可獲得當前session正在執行的SQL語句。
總結:
1.首先我們透過作業系統命令TOP找到了PID.
2.我們結合3個試圖,就找打了當前正在瘋狂消耗CPU的罪魁禍首,那麼下面的工作就是如何最佳化這個SQL,我們可以進一步透過
dbms_system包跟蹤改程式,或者透過AWR獲取該SQL的執行計劃。來改變SQL的執行計劃,達到最佳化的目的。

 




2014.08.18 13:28
share you knowledge with the world. 




 

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

相關文章