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

    對於一個剛開始學習資料庫優化的新手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的執行計劃，達到優化的目的。

pxboracle@live.com
2014.08.18 13:28
share you knowledge with the world.

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

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

相關文章