ORA-01000 Maximum Cursors Exceeded! เจ้าตายแล้ว!

ห่างหายไปนานเลยครับ ฮ่าๆ ช่วงนี้งานชุกมากๆ แต่ยังไงวันนี้ก็มีเรื่องราวดีๆ มาฝากเหมือนเดิม ORA-01000

นี้มัน Error อะไรกันอีกเนี่ย! ORA-01000 maximum open cursors exceeded! เคยเจอกันไหมครับ? เคยหล่ะซิ โอเค วันนี้ผมจะมางัดแงะแกะเกา Error message ตัวนี้ให้ทุกคนเข้าใจตรงกันนะครับ!

ORA-01000 maximum open cursors exceeded
Cause: A host language program attempted to open too many cursors. The initialization parameter OPEN_CURSORS determines the maximum number of cursors per user.
Action: Modify the program to use fewer cursors. If this error occurs often, shut down Oracle, increase the value of OPEN_CURSORS, and then restart Oracle.

เข้าใจกันไหมครับจากรายละเอียดด้านบน สาเหตุจริงๆ ของมันก็คือ ในหนึ่งเซสชั่นนั้นสามารถเรียการใช้งาน Cursor ได้ที่ไม่เกินค่าที่กำหนดไว้ open_cursors นั่นเองครับ ซึ่งถ้าผมสมมุติว่า open_cursors บนฐานข้อมูลของผมนั้นถูกกำหนดค่าไว้ที่ 400 ดังนั้นเซสชั่นใดก็ตามที่มีการทำงานบนฐานข้อมูลนั้นจะสามารถเรียกใช้งาน Cursor บน Library cache ได้ที่ไม่เกิน 400 Cursor นั้นเองครับ

ซึ่งถ้าคุณกำหนดค่า open_cursors ไว้ต่ำเกินไป ก็มีโอกาสทำให้เกิด ORA-01000 ได้นั่นเองครับ

วิธีการมอนิเตอร์ Current open cursors นั้นสามารถดูได้ทั้งภาพรวมและเจาะจงแต่ละเซสชั่นดังนี้ครับ

1. Monitor High water mark of open cursors.
วิธีการนี้เป็นวิธีการเช็ค High Water Mark ของ Current open cursors ว่า ณ จุดสูงสุดในการเรียกใช้ Cursor อยู่ที่เท่าไรนั่นเองครับ

SQL> col hwm_open_cur format 99,999
SQL> col max_open_cur format a20
SQL> select
  2     max(a.value) as hwm_open_cur,
  3     p.value      as max_open_cur
  4  from
  5     v$sesstat a,
  6     v$statname b,
  7     v$parameter p
  8  where
  9     a.statistic# = b.statistic#
 10  and
 11     b.name = 'opened cursors current'
 12  and
 13     p.name= 'open_cursors'
 14  group by p.value;

HWM_OPEN_CUR MAX_OPEN_CUR
------------ --------------------
          10 150

SQL>

จากผลจะเห็นได้ว่า ณ เวลาล่าสุด เซสชั่นที่เรียกใช้ Cursor สูงสุดนั้นอยู่ที่ 10 Cursor แต่เรากำหนดค่าไว้ที่ 150 Cursor ครับ

2. Monitor current open cursors session by session
วิธีการนี้เป็นการเจาะจงว่าแต่ละเซสชั่นมีการเรียกใช้ Cursor มากน้อยแค่ไหนนั่นเองครับ

SQL> select
  2     stat.value,
  3     sess.username,
  4     sess.sid,
  5     sess.serial#
  6  from
  7     v$sesstat  stat,
  8     v$statname b,
  9     v$session  sess
 10  where
 11     stat.statistic# = b.statistic#
 12  and
 13     sess.sid=stat.sid
 14  and
 15     b.name = 'opened cursors current'
 16  order by stat.value desc;

     VALUE USERNAME                        SID        SERIAL#
---------- ------------------------------ ---------- ----------
        10 TEST1                           23         35385
        10 TEST1                           18         33377
         3 SYS                             35         15053
         3                                 8          1
         1 TEST1                           36         1325
         1 BP0USER00TEST1                  22         60805
         0                                 1          1
         0                                 5          1
         0                                 3          1
         0                                 7          1
         0                                 10         1
         0                                 9          1
         0                                 2          1
         0                                 4          1
         0                                 6          1

15 rows selected.

SQL>

จากผลลัพธ์ก็จะบอกว่าแต่ละเซสชั่นใช้งาน Cursor อยู่กี่ตัวและเซสชั่นนั้นมาจาก USERNAME, SID และ SERIAL# อะไรนั่นเองครับ

วิธีการแก้ปัญหา ORA-01000

1. เพิ่มค่า open_cursors เข้าไปอีก ใช่แล้วครับมันน้อยไปก็ต้องเพิ่มซิครับ แต่ก่อนที่จะเพิ่มอย่าลืมตรวจสอบพื้นที่หน่วยความจำบน Share pool ก่อนนะครับ เพราะ open cursors นั้นเมื่อถูกเรียกใช้งานมันจะไปทำการจองหน่วยความจำบน library cache ซึ่งเป็นส่วนหนึ่งของ Share pool นั่นเอง

2. ลดกระบวนการการทำงานของ Application ให้มีการเรียกใช้ Cursor น้อยลง วิธีนี้เหมาะสมมากสำหรับฐานข้อมูลที่มีการตั้งค่า open_cursors ไว้สูงมากแล้ว เพราะฉะนั้นเราอาจจำเป็นที่จะต้องแก้ที่ต้นเหตุนะครับ

และนี่ก็เป็นวิธีการง่ายๆ ในการจัดการเจ้า ORA-01000 ไม่ให้มากวนใจเราอีกครับ แล้วเดียวคราวหน้าจะเอาเรื่องราวเกี่ยวกับ open_cursors มาแชร์เพิ่มเติมให้ครับ รับรองสนุกแน่นอน

ขอบคุณข้อมูลประกอบจาก open_cursors Tips by Burleson

ขอบคุณภาพประกอบสวยๆ จาก Lisa Brewster

Advertisements

ใส่ความเห็น

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / เปลี่ยนแปลง )

Twitter picture

You are commenting using your Twitter account. Log Out / เปลี่ยนแปลง )

Facebook photo

You are commenting using your Facebook account. Log Out / เปลี่ยนแปลง )

Google+ photo

You are commenting using your Google+ account. Log Out / เปลี่ยนแปลง )

Connecting to %s