go to http://oracle.in.th

Sunday, June 20, 2010

การตรวจเช็คบนฐานข้อมูล(oracle) ตอนที่ 9

9. การจัดการกับหน่วยความจำ (Memory Management)
การจัดการกับหน่วยความจำของฐานข้อมูล Oracle จะมีความแตกต่างกันไป ซึ่งขึ้นอยู่กับ oracle เวอร์ชัน เพราะ oracle เองได้มีการเพิ่มคุณสมบัติ ในการจัดการกับหน่วยความจำกับฐานข้อมูลเวอร์ชันใหม่ๆ เพื่อให้ง่ายและมีประสิทธิภาพ ดังนั้นเรามักจะถูกแนะนำให้ใช้คุณสมบัติในการจัดการกับหน่วยความจำ ด้วยวิธีอัตโนมัติ9.1 ก่อน Oracle 9i
องค์ประกอบหน่วยความจำที่แตกต่างกัน (SGA และ PGA) ต้องการที่จะถูกกำหนดในการเริ่มต้นของฐานข้อมูล(startup) ค่าเหล่านี้เป็นค่าคงที่ ดังนั้นหากหนึ่งในองค์ประกอบของหน่วยความจำที่ต่ำเกินไป และจำเป็นต้องเปลี่ยนแปลง ฐานข้อมูลจำเป็นต้อง restart เพื่อให้ฐานข้อมูลใช้ค่าใหม่ที่ได้เปลี่ยนแปลง
ตัวอย่าง พารามิเตอร์ที่อาจใช้ผิดบ่อยๆ เช่น sort_area_size
SQL> show parameter sort_area_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sort_area_size integer 1024000
พารามิเตอร์ sort_area_size กำหนด จำนวนหน่วยความจำที่สามารถใช้ในการเรียงลำดับ ค่านี้เป็นส่วนหนึ่งของ User Global Area (UGA) ดังนั้นจะถูกจัดสรรให้แต่ละผู้ใช้
หากผู้ใช้เข้ามาใช้งานพร้อมกันเป็นจำนวนมาก ในการเรียงลำดับข้อมูลขนาดใหญ่ ระบบอาจจะใช้หน่วยความจำจำนวนมาก
เช่น sort_area_size ของ 1MB กับผู้ใช้ 200 คน เข้ามาพร้อมกันในฐานข้อมูล อย่างไรก็ตามหน่วยความจำนี้ถูกจัดสรรแบบไดนามิก มันสามารถจัดสรรหน่วยความจำถึง 200MB และอาจทำให้เกิดการใช้ SWAP บนระบบ

9.2 Oracle 9i
เริ่มต้นจาก Oracle 9i พวกเราจะพบพารามิเตอร์:
workarea_size_policy = [AUTO | MANUAL]
pga_aggregate_target = <value>

ซึ่งยอมให้เรากำหนดพื้นที่หน่วยความจำสำหรับ PGA โดยที่จะถูกใช้ร่วมกันของ sessions
ถ้าค่านี้น้อยเกินไป (pga_aggregate_target) บ่อยครั้งที่เราจะพบ ORA-4030
SQL> show parameter workarea_size_policy

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy string AUTO

SQL> show parameter pga_aggregate_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 500M
ถ้า workarea_size_policy เป็น AUTO หน่วยความจำสำหรับ work area (sort area) จะถูกจัดการอัตโนมัติ จากค่า pga_aggregate_target
ตัวอย่าง work area:
- Sort-based operators (ORDER BY, GROUP BY, ROLLUP, window functions)
- Hash-join
- Bitmap merge
- Bitmap create
อันได้แก่ พารามิเตอร์ *area_size
SQL> show parameter area_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
bitmap_merge_area_size integer 1048576
create_bitmap_area_size integer 8388608
hash_area_size integer 131072
sort_area_size integer 65536
ถ้า workarea_size_policy เป็น MANUAL หน่วยความจำสำหรับ work area (sort area) จะไม่ใช้ pga_aggregate_target เราต้องมีการจัดการกับพารามิเตอร์ *area_size แต่ละตัวเอง
การตรวจสอบ PGA เช่น ข้อมูลการใช้PGA
SELECT * FROM V$PGASTAT;
หรือ คำแนะนำเพื่อการปรับค่าของ PGA
SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,
ESTD_OVERALLOC_COUNT
FROM V$PGA_TARGET_ADVICE;
หรือการตรวจสอบ PGA แต่ละ session
set pages500 lines110 trims on
clear col
col name format a30
col username format a20
break on username nodup skip 1
select vses.username||':'||vsst.sid||','||vses.serial# username, vstt.name, max(vsst.value) value
from v$sesstat vsst, v$statname vstt, v$session vses
where vstt.statistic# = vsst.statistic# and vsst.sid = vses.sid and vstt.name in
('session pga memory','session pga memory max','session uga memory','session uga memory max',
'session cursor cache count','session cursor cache hits','session stored procedure space',
'opened cursors current','opened cursors cumulative') and vses.username is not null
group by vses.username, vsst.sid, vses.serial#, vstt.name order by vses.username, vsst.sid, vses.serial#, vstt.name;
การประมาณค่า PGA_AGGREGATE_TARGET:
- สำหรับระบบ OLTP
PGA_AGGREGATE_TARGET = (<Total Physical Memory > * 80%) * 20%
- สำหรับระบบ DSS
PGA_AGGREGATE_TARGET = (<Total Physical Memory > * 80%) * 50%
9.3 Oracle 10g
Automatic Shared Memory Management (ASMM) ถูกแนะนำใน Oracle เวอร์ชัน 10g คุณลักษณะการจัดการหน่วยความจำอัตโนมัติ ทำได้โดยการตั้งค่าพารามิเตอร์ SGA_TARGET ให้มากกว่าศุนย์
คุณลักษณะนี้มีประโยชน์ ช่วยให้เราสามารถแบ่งปันทรัพยากรหน่วยความจำในส่วนต่างๆ และการจัดสรรทรัพยากรณ์จะเป็นไปโดยอัตโนมัติ ด้วย Oracle

SGA_TARGET พารามิเตอร์มีผลต่อ องประกอบหน่วยความจำ:
- Fixed SGA และ other internal ที่ต้องการโดย Oracle Database instance
- Log buffer
- Shared pool
- Java pool
- Buffer cache
- keep และ recycle buffer caches (ถ้าระบุ)
- Nonstandard block size buffer caches (ถ้าระบุ)
- Streams pool

เมื่อเราเซตค่าสำหรับ SGA_TARGET, Oracle 10g จะกำหนดขนาดส่วนประกอบอันได้แก่
- Shared pool (SQL และ PL/SQL execution)
- Java pool (Java execution state)
- Large pool (large allocations เช่น RMAN backup buffers)
- Buffer cache
- Streams pool
SQL> show parameter sga_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 512M
การตรวจสอบข้อมูลการใช้ SGA
select * from v$sgastat;
select * from v$sgainfo;
หรือ คำแนะนำเพื่อการปรับค่าของ SGA
select * from v$sga_target_advice order by sga_size;
หรือตรวจสอบการเพิ่มค่าหรือลดค่า ของส่วนประกอบ SGA
select * from v$sga_dynamic_components;
และ
ALTER SESSION SET nls_date_format = 'DD/MM/YYYY:HH24:MI:SS';
SET PAGESIZE 900
SET LINESIZE 255
COL COMPONENT FORMAT A25
COL INITIAL_SIZE FORMAT A10
COL TARGET_SIZE FORMAT A10
COL FINAL_SIZE FORMAT A10
COL OPER_TYPE FORMAT A10
select START_TIME, component, oper_type, oper_mode,status, initial_size/1024/1024 "INITIAL", target_size/1024/1024 "TARGET", FINAL_SIZE/1024/1024 "FINAL", END_TIME
from v$sga_resize_ops
order by start_time, component;
ส่วนการจัดการหน่วยความจำ PGA อัตโนมัติ ยังใช้งานได้ผ่าน workarea_size_policy และ pga_aggregate_target พารามิเตอร์

9.4 Oracle 11g
Automatic Memory Management (AMM) ถูกแนะนำใน Oracle เวอร์ชัน 11g ช่วยให้ปรับค่า PGA และ SGA โดยอัตโนมัติ กับค่าพารามิเตอร์ MEMORY_MAX_TARGET และ MEMORY_TARGET
SQL> show parameter MEMORY_MAX_TARGET
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_max_target big integer 800M

SQL> show parameter MEMORY_TARGET
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_target big integer 800M
การตรวจสอบองค์ประกอบของหน่วยความจำที่ใช้ AMM
select * from V$MEMORY_DYNAMIC_COMPONENTS;
ตรวจสอบการเพิ่มและลดขององค์ประกอบใน AMM ด้วย V$MEMORY_RESIZE_OPS
หรือ คำแนะนำเพื่อการปรับค่าสำหรับ AMM
select * from v$memory_target_advice order by memory_size;
บทความนี้เป็นตัวอย่างคร่าวๆ ในการจัดการกับหน่วยความจำของฐานข้อมูล Oracle, เราสามารถหาอ่านเพิ่มได้จาก Oracle Documents, Oracle Support และ อื่นๆ

อ้างอิง
http://support.oracle.com
http://tahiti.oracle.com
ข้อเขียนนี้ช่วยฉัน:  

4 comments:

Sanivipa said...

เยี่ยมไปเลยค่ะ :)
@Surachart กะจะโพสให้พอดีวันเกิดเลยรึเปล่าคะเนี่ย ^^

Surachart said...

@Sanivipa รู้ได้ไงนี่
จริงๆ กะ POST เพื่อทำ News Letter นะ

Sanivipa said...

@Surachart เอ๊ะ จะมี News Letter ด้วยเหรอคะ แล้วจะ subscribe รับยังไงอ่ะคะ?

Surachart said...

@Sanivipa
งั้น ทำเป็นหน้า Google reader (รวมข่าวๆ webcast) แล้วแชร์ บน twitter ก็ได้

Post a Comment