Thursday, April 16, 2009

Performance monitoring & tuning

ข้อเขียนนี้ช่วยฉัน: 
Last Updated: 8/03/2009

1. ขนาดของ Shared Pool
อัตราการ Reparse
แสดงค่าของอัตราที่ระบบฯ ไม่ได้ใช้ Library Cache และ Row (Dictionary) Cache โดยปกติจำนวนครั้งของการ Reparse จะสะท้อนถึงการใช้ Library Cache ถ้าค่าอัตรานี้อยู่ประมาณ ๆ 1 (ค่าของการ Reload มีจำนวนน้อย) เราไม่จำเป็นต้องเพิ่ม Pool Size
เมื่อ PARSE_CALLS มีค่าใกล้เคียงกับ EXECUTION แสดงว่ามีการ Reparse อยู่ตลอด
SQL> select sql_text,parse_calls,executions from v$sqlarea;
SQL> select * from v$statname where name in ('parse count (hard)','execute count');
รายการของทุก ๆ Session และจำนวนของการ Reparse
SQL> select * from v$sesstat where statistic# in (165,166) order by value,sid;

Library Cache
แสดงหน่วยความจำส่วนที่ใช้ในการเก็บคำสั่ง SQL ที่ได้แปลแล้ว
คำสั่งที่ใช้ในการตรวจดูการที่ Oracle ใช้คำสั่งที่เคยแปลมาแล้ว โดยไม่ต้องแปลซ้ำอีก
SQL> select (sum(pins-reloads)) / sum(pins) "LIB CACHE" from v$librarycache;
หรือ
SQL> select sum(pins) "EXECUTIONS",sum(reloads) "CACHE MISSES WHILE EXECUTING" from v$librarycache;
ถ้าอัตราส่วนของการไม่ใช้ Cache (Cache Misses) มีจำนวนสูง (CACHE MISSES WHILE EXECUTING*100/EXECUTIONS) หรือมากกว่า 1% ให้พยายามลดจำนวน Cache Miss
และถ้าไม่มี Cache Misses เราอาจจะยังสามารถเร่งการทำงานได้โดยการตั้งค่า CURSOR_SPACE_FOR_TIME เป็น TRUE ซึ่งจะทำให้ SQL Area ที่ใช้ร่วมกันจะถูกลบก็ต่อเมื่อทุก ๆ Application Cursors ที่ใช้คำสั่งนั้น ๆ อยู่ปิดไปแล้ว อย่างไรก็ตามอย่าใช้วิธีนี้ถ้าพบ Library Cache Misses

Row(Data Dictionary) Cache
แสดงหน่วยความจำส่วนที่ใช้ในการเก็บ Data Dictionary เช่นโครงสร้างตาราง สิทธิ์ต่าง ๆ เป็นต้น
คำสั่งที่ใช้ในการตรวจดูการที่ Oracle ใช้ข้อมูล Data Dictionary ที่อยู่ในหน่วยความจำ ไม่ต้องไปเอาที่ดิสก์
SQL> select (sum(gets-getmisses-usage-fixed)) / sum(gets) "ROW CACHE" from v$rowcache;
หรือ
SQL> select sum(gets) "DATA DICTIONARY GETS",sum(getmisses) "DATA DICT CACHE GET MISSES" from v$rowcache;
อัตราส่วนของ GETMISSES ทั้งหมดต่อ GETS ทั้งหมด (GETS/GET MISSES*100) ควรจะน้อยกว่า 10 หรือ 15% ถ้าไม่เช่นนั้นให้เพิ่ม Shared Pool

User Processes ที่มีผลต่อ Shared Pool
ถ้า Session เชื่อมต่อแบบ Dedicated Server Processes หน่วยความจำส่วนนี้จะแยกออกจาก Shared Pool แต่ถ้าเป็นการเชื่อมต่อแบบ Shared Server หน่วยความจำส่วนนี้จะเป็นส่วนหนึ่งของ Shared Pool ซึ่งเราสามารถใช้ผลที่ได้จากคิวรีตัวข้างล่างเพื่อใช้ในการกำหนดว่าจะเพิ่มขนาด Shared Pool เท่าไร ถ้าเราใช้โหมด Multi-Thread Server (Shared Server).
SQL> select sum(value)' BYTES' "TOTAL MEMORY FOR ALL SESSIONS"
from v$sesstat, v$statname
where UPPER(name) = 'SESSION UGA MEMORY'
and v$sesstat.statistic# = v$statname.statistic#;
SQL> select sum(value)' BYTES' "TOTAL MAX MEM FOR ALL SESSIONS"
from v$sesstat, v$statname
where UPPER(name) = 'SESSION UGA MEMORY MAX'
and v$sesstat.statistic# = v$statname.statistic#;

DB_CACHE_HIT_RATIO
แสดงอัตราส่วนที่ Oracle ใช้ Data ที่เคยใช้ซึ่งอยู่ในหน่วยความจำอยู่แล้ว โดยไม่ต้องโหลดจากดิสก์ใหม่
Database Buffer Cache Hit ratio ควรจะมากกว่า 90% ในระบบที่เป็น OLTP การเพิ่ม DB_CACHE_SIZE จะช่วยให้ Cache Hit Ratio สูงขึ้น อย่างไรก็ตามขนาดต้องไม่เกิน SGA_MAX_SIZE เนื่องจาก Database Buffer เป็นส่วนหนึ่งของ SGA
SQL> select 1-(PHY.VALUE - LOB.VALUE - DIR.VALUE)/SES.VALUE "CACHE HIT RATIO"
FROM V$SYSSTAT SES, V$SYSSTAT LOB, V$SYSSTAT DIR, V$SYSSTAT PHY
WHERE SES.NAME ='session logical reads'
and DIR.NAME ='physical reads direct'
and LOB.NAME ='physical reads direct (lob)'
and PHY.NAME ='physical reads';

การตรวจสอบหน่วยความจำคงเหลือใน Shared Pool
ไม่ต้องเพิ่มขนาดของ Pool ถ้ามักจะมีหน่วยความจำใน Shared Pool เหลืออยู่เสมอ อย่างไรก็ตามการที่ Shared Pool เต็มก็ไม่ได้หมายความว่าจะต้องมีปัญหาเสมอไป ต้องดูข้อมูลอื่นประกอบด้วย
SQL> select * from v$sgastat where name = 'free memory';
ถ้าหน่วยความจำคงเหลือเข้าใกล้ 0 และ Library Cache หรือ Row Cache Hit Ratio น้อยกว่า 95% ให้เพิ่มขนาดของ Shared Pool จนกว่าอัตราส่วนนี้จะหยุดการเคลื่อนไหว

2. File Path
การดูตำแหน่งของไฟล์ต่าง ๆ ในระบบฐานข้อมูลว่าอยู่ที่ไหนบ้าง
SQL> SELECT NAME,VALUE FROM v$system_parameter WHERE NAME IN ('spfile','control_files','log_archive_dest_1','backgroud_dump_dest','user_dump_dest')
UNION ALL
SELECT 'data_file_' file_id,file_name FROM Dba_Data_Files
UNION ALL
SELECT 'log_file_group_' GROUP#, MEMBER FROM v$logfile;

3. IO
แสดงการทำงานของ IO คือดูว่ามีการอ่านเขียนลงดิสก์มากน้อยแค่ไหน
หากมีไฟล์ใดทำงานมากกว่าไฟล์อื่น ๆ อย่างมาก เราอาจจะกระจายการทำงานของดิสก์ เช่นโดยการแยกไฟล์ที่ทำงานหนักนั้นไปไว้บนดิสก์ (และดิสก์คอนโทรลเลอร์) ลูกอื่น หรือแยกตารางที่มีการอ่านเขียนจำนวนมากที่อยู่บนไฟล์นี้ไปไว้บนไฟล์ (เทเบิลสเปซ) อื่น
SQL> select file_name,phyrds,phywrts,readtim,writetim
from v$filestat a, dba_data_files b
where a.file# = b.file_id;

4. Cursors Stats
ดูข้อมูลสถิติของคำสั่ง SQL ต่าง ๆ เช่นจำนวนเรคคอร์ดที่ใช้, เวลาของซีพียู หรือสถิติการอ่านข้อมูลจากดิสก์เป็นต้น
SQL> select a.sid, b.first_load_time,a.sql_id,regexp_replace(b.sql_text,'[[:blank:]]+',' ') sql,b.rows_processed
,b.PARSE_CALLS, b.EXECUTIONS, b.FETCHES, b.CPU_TIME,b.ELAPSED_TIME,b.DISK_READS,b.DIRECT_WRITES,b.BUFFER_GETS
from v$open_cursor a, v$sqlarea b where a.sql_id = b.sql_id;

5. ดู PGA
ดูการทำงานของ Program Global Area ซึ่งเป็นส่วนของหน่วยความจำที่ใช้เก็บข้อมูลของแต่ละ Session เช่นข้อมูลการ Sort หรือเก็บตัวแปรของ Session เป็นต้น
SQL> SELECT NAME,round(VALUE/1048576,3) ' M' Value FROM v$pgastatWHERE NAME IN ('total PGA inuse','total PGA allocated','maximum PGA allocated','over allocation count','bytes processed','extra bytes read/written')union allselect name,to_char(value) from v$pgastat where name = 'cache hit percentage'
Over Allocation Count จะบอกถึงการใช้ PGA ที่เกินจากที่ได้กำหนดไว้ อาจจะพิจารณาถึงการเพิ่ม PGA_AGGREGATE_TARGET

6. SGA
ดูการใช้หน่วยความจำของ System Global Area (SGA) แต่ละส่วน
SQL> SELECT * FROM (
SELECT NAME,round(bytes / (1024*1024),2) Mb FROM v$sgastat WHERE pool IS NULL
UNION ALL
SELECT pool AS NAME,round(sum(bytes) / (1024*1024),2) Mb FROM v$sgastat WHERE pool IS NOT NULL AND NAME != 'free memory' GROUP BY pool
UNION ALL
SELECT pool ' (Free)' AS name,round(sum(bytes) / (1024*1024),2) Mb FROM v$sgastat WHERE NAME = 'free memory' GROUP BY pool
)
ORDER BY NAME;

7. Pin Package
ใช้เพื่อดูว่ามี Package ขนาดใหญ่ใด ๆ ที่ยังไม่ถูกตรึงอยู่ในหน่วยความจำ เราอาจจะพิจารณาตรึง Package เหล่านี้ถ้ามีการเรียกใช้บ่อย ๆ เพื่อเพิ่มประสิทธิภาพ
SQL> SELECT owner ,NAME,TYPE ,sharable_mem,executions
FROM v$db_object_cache
WHERE sharable_mem >10000
AND TYPE IN ('PACKAGE','PACKAGE BODY','PROCEDURE','FUNCTION','TRIGGER')
AND kept = 'NO'
ORDER BY SHARABLE_MEM DESC;

8. Sort Segment
แสดงข้อมูลขนาดของ Temp Tablespace (ซึ่งโดยปกติใช้ในการ Sort)
SQL> SELECT tablespace_name,current_users,total_extents,total_blocks,used_extents,max_size FROM v$sort_segment;
แสดงประสิทธิภาพในการ Sorts โดยเปรียบเทียบปริมาณการ Sort ระหว่างการ Sort บน Memory กับการ Sort บน Disk
SQL> select name,value from v$sysstat where name like 'sorts%';

9. Undo
ขนาดของ Undo Tablespace จะเป็นตัวกำหนดว่าเราจะสามารถทำการ Undo ย้อนหลังกลับไปได้ไกลแค่ไหน
คำนวณขนาดของ Undo Tablespace โดยกำหนด Retention Rate ให้
SQL> SELECT 'With this retention rate you need to have your untbs as big as' remark, round((ur * (ups * overhead) + overhead) / (1024 * 1024),2) AS Mb
FROM (SELECT VALUE AS ur FROM v$parameter WHERE NAME = 'undo_retention')
, (SELECT (SUM(undoblks) / SUM(((end_time - begin_time) * 86400))) AS ups FROM v$undostat)
, (SELECT VALUE AS overhead FROM v$parameter WHERE NAME = 'db_block_size');

9 comments:

Anonymous said...

Hi good one

Surachart said...

เยี่ยมครับ

Anonymous said...

อยากจะถามหน่อยค่ะ คือว่า
อยาก ทราบว่า การ to flush shared pool ซึ่ง ใช้ command

SQL> alter system flush shared_pool;
ซึ่ง ค่าก่อน flush คือ
NAME Allocated (M) Free (K) % Free
-------------------------- ------------- ---------- ----------
free memory 141.492165 4501.9375 4.11
free memory 141.492165 7812.5 7.12
free memory 141.492165 19532 15.38

ส่วน ค่าหลัง flush คือ
NAME Allocated (M) Free (K) % Free
-------------------------- ------------- ---------- ----------
free memory 141.492165 70596.8672 46.73
free memory 141.492165 7812.5 7.12
free memory 141.492165 19532 15.38

ซึ่ง หลังจากที่ flush shared_pool แล้วค่า ของ free memory สองตัวข้างล่างไม่มีการเปลี่ยนแปลง เลยค่ะ
แต่ ค่า free memory ตัวบนสุด มีการคืน พื้นที่ให้ เลยอยากจะทราบว่า เพราะเหตุใด สองตัวข้างล่างจึงไม่มีการเปลี่ยนแปลง

ขอบคุณค่ะ
DBA มือใหม่

Tanakorn Tavornsasnavong said...

รบกวนขอรายละเอียดหน่อยครับว่า แต่ละบรรทัดหมายถึง free memory ส่วนไหนครับ อาจจะ copy คำสั่ง sql ที่ใช้ในการคิวรีข้อมูลหลัง flush มาด้วยก็จะดีครับ

Anonymous said...

รบกวนช่วยแนะนำ oracle performance counter ที่สำคัญๆ สำหรับการดู performance รวมถึงการ tuning ครับ

ขอบคุณครับ

Tanakorn Tavornsasnavong said...

ขอบคุณที่ให้ความสนใจครับ ตัวผมเองยังไม่เคยใช้ Load Runner เลยครับ อาจจะใช้ Tools ตัวอื่นอยู่บ้าง แต่ที่เห็นว่าสามารถใช้งานได้ไม่เลวเลยก็คือ Tool ของ Oracle เองก็คือตัว Oracle Enterprise Manager นี่แหล่ะครับ ผมขออนุญาตจะเขียนเรื่องนี้ในบทความเรื่องต่อไปนะครับ หวังว่าคงไม่นานเกินรอนะครับ อย่างไรก็ตามก่อนที่จะคุยกันเรื่องของเคาน์เตอร์สำคัญ ๆ ผมคิดว่ามีประเด็นสำคัญ ๆ ในการจัดการเรื่องประสิทธิภาพของฐานข้อมูลอยู่สามตัวครับ

ตัวที่ 1 ฐานข้อมูลจะต้องวิ่งบน RAM ไม่ใช่วิ่งบนดิสก์ เมื่อระบบฐานข้อมูลวิ่งไปได้สักระยะหนึ่ง ข้อมูลต่าง ๆ จะไปอยู่ใน Cache ของระบบ จะมีข้อมูลส่วนน้อยที่จะต้องวิ่งกลับไปเอาที่ดิสก์ จริง ๆ แล้วอันนี้ก็เป็นผลจากการจูนพารามิเตอร์ในข้อ 3 แต่ที่ยกมาเป็นข้อ ๆ หนึ่งเนื่องจากคิดว่าเป็นประเด็นสำคัญประเด็นหนึ่งครับ

ตัวที่ 2 เรียกข้อมูลอย่างประหยัด เช่นจัดการให้ข้อมูลจำนวนน้อยที่สุดที่เคลื่อนที่ระยะไกล (ดึงข้อมูลจำนวนน้อยเท่าที่จำเป็นไปแสดงหรือคำนวณใน Application) และข้อมูลจำนวนมากเคลื่อนที่ระยะใกล้ หรือไม่เคลื่อนที่เลยเป็นต้น (ถ้าต้องการจัดการกับข้อมูลจำนวนมากการใช้ PL/SQL อาจจะดีกว่า) ซึ่งมีรายละเอียดในเรื่องนี้มากจริง ๆ ครับ รวมถึงเรื่องการ Tune SQL Statement ด้วย ซึ่งผมก็อยากจะเขียนถึงเรื่องนี้ด้วยเช่นกัน

ตัวที่ 3 จูนพารามิเตอร์ให้เหมาะสม หรือการเพิ่มฮาร์ดแวร์ เมื่อพบว่าไม่มีทางอื่นแล้ว ปกติแล้วพารามิเตอร์ที่เป็นค่าดีฟอลต์ก็สามารถทำงานได้ดีในระดับหนึ่ง การจูนอาจจะต้องใช้เวลาสักพักในการเก็บข้อมูลพร้อมทั้ง Work Load ต่าง ๆ ที่เกี่ยวข้อง ผมคิดว่าเราน่าจะทำเป็นรายการสุดท้ายครับ

รบกวนขอตอบแค่นี้ก่อนนะครับแล้วจะพยายามลงรายละเอียดในแต่ละเรื่องต่อไปนะครับ ขอบคุณอีกครั้งที่ให้ความสนใจนะครับ

Anonymous said...

ขอบคุณอย่างสูงที่ตอบกลับครับ ข้อมูลเป็นประโยชน์ต่อผมมากครับ

สำหรับ LoadRunner นั้น เป็น Tools สำหรับการทดสอบ Performance Test โดยหลักการจะ generate Virtual users ตามจำนวนที่ต้องการ เข้าใช้งาน application ที่จะใช้ทดสอบ โดย tools จะสามารถ monitor ค่าต่าง ๆ ได้ เช่น server capacity (CPU, memory), webSphere (Pool, JVM) นอกจากนี้ก็สามารถที่จะดู database ได้ด้วย

สำหรับ Oracle โดย default นั้น tools นี้จะดูได้ในส่วนที่เป็น V$SYSSTAT และ V$SESSTAT ซึ่งมันมี counters มากมายที่สามารถดูได้

เนื่องจากมี counters มากมายที่สามารถดูได้ จึงเป็นที่มาจากที่ผมได้
เรียนถาม K.Tanakorn ว่า อยากทราบ counters ที่สำคัญๆ สำหรับการดู performance ของ Oracle รวมถึง parameter ที่ใช้ในการ tuning ด้วย

ขอบคุณครับ

Tanakorn Tavornsasnavong said...

V$SESSSTAT และ V$SYSSTAT เป็น Dynamic Performance View ที่สำคัญในการดูสถิติการทำงานของระบบฐานข้อมูล นอกจากนี้ก็ยังมีตัวอื่น ๆ อีกอย่างเช่น V$LIBRARYCACHE ใช้ดูสถิติการใช้คำสั่ง SQL ใน Cache (Memory) หรือ V$ROWCACHE ใช้ดูสถิติการใช้ Data Dictionary บน Cache อย่างที่ได้กล่าวแล้วนะครับว่า เราสนใจการทำงานว่าให้ทำบน Cache หรือ Memory ให้มาก อย่างไรก็ตามเรื่องของ Parameter กับการ Tuning มีเนื้อหาอีกมาก ซึ่งผมจะพยายามเขียนเกี่ยวกับเรื่องนี้ หรือไม่ก็หาบทความเกี่ยวกับเรื่อง Performance มาแปลให้ได้อ่านกันนะครับ

ขอบคุณที่ให้ความสนใจนะครับ

Anonymous said...

ขอขอบคุณอย่างสูงสำหรับความกรุณา แล้วจะรออ่านนะครับ

Post a Comment