ESC را فشار دهید تا بسته شود

زمیوس آموزش، یادگیری و سرگرمی

نقش Shared Pool و Library Cache در بهبود سرعت اجرای SQL در Oracle

نقش Shared Pool و Library Cache در بهبود سرعت اجرای SQL در Oracle

مقدمه : Shared Pool و Library Cache چه کاربردی دارند؟

اگر تا حالا با دیتابیس‌های اوراکل کار کرده باشی، حتماً شنیدی که سرعت اجرای Query یکی از دغدغه‌های اصلی هر برنامه‌نویس یا DBA هست.

اما تا حالا فکر کردی که اوراکل چطور این سرعت بالا رو فراهم می‌کنه؟ یکی از رازهای مهمش در استفاده هوشمندانه از بخش‌هایی به نام Shared Pool و Library Cache نهفته‌ست.

در این مقاله، قراره خیلی ساده و کاربردی بفهمی این دو بخش دقیقاً چیکار می‌کنن و چطور می‌تونن باعث پرواز سرعت SQL هات بشن!

اگر می خواهید در مورد Parallel Execution در اوراکل با جزئیات بیشتری آشنا بشید نوشته زیر را مطالعه کنید:

در این نوشته شما می خوانید

Shared Pool چیه؟ مغز متفکر حافظه اوراکل!

Shared Pool بخشی از حافظه SGA (System Global Area) هست که اوراکل ازش برای اشتراک‌گذاری داده‌ها و کدهایی استفاده می‌کنه که ممکنه توسط چندین کاربر و سشن به صورت همزمان استفاده بشن.
هدف اصلیش چیه؟ جلوگیری از تکرار کارهای سنگین مثل پردازش مجدد کوئری‌ها یا بارگذاری دوباره کدهای PL/SQL.

Shared Pool از دو بخش اصلی تشکیل شده:

  • Library Cache: محلی برای ذخیره و بازیابی Execution Plan های SQL/PLSQL.
  • Data Dictionary Cache: نگهداری Metadata از اشیاء دیتابیس مثل جداول، ایندکس‌ها و … .

Library Cache دقیقاً چه‌کاری انجام میده؟

Library Cache مسئول نگهداری کد SQL و PL/SQL کامپایل‌شده و پلان اجرایی اون‌هاست.
وقتی یه Query اجرا می‌کنی، اوراکل میاد اول داخل این Cache می‌گرده.

اگر قبلاً یه Query مشابه اجرا شده باشه و پلانش هنوز توی Cache باشه، همون رو دوباره استفاده می‌کنه. این یعنی:

  • صرفه‌جویی در زمان
  • کاهش بار پردازشی روی CPU
  • استفاده بهینه از منابع سیستم

Soft Parse vs Hard Parse: فرقش چیه؟

نوع Parse توضیح
Hard Parse فرآیند کامل Parse و Optimization و ساخت Execution Plan
Soft Parse استفاده مجدد از Execution Plan ذخیره‌شده در Library Cache

هدف ما همیشه باید این باشه که تعداد Soft Parse‌ها رو افزایش بدیم و Hard Parse رو تا جای ممکن کم کنیم.

اشتباه مرگبار: استفاده از Literal به‌جای Bind Variable

مثال زیر رو ببین:

				
					--  Hard Parse!
SELECT * FROM employees WHERE employee_id = 100;
SELECT * FROM employees WHERE employee_id = 101;

چون مقدار داخل شرط تغییر کرده، Oracle فکر می‌کنه این دوتا کوئری متفاوت هستن.
ولی با Bind Variable می‌تونیم جلوی این مشکل رو بگیریم:

				
					-- Vaghti 1 Bar Parse mishe:
SELECT * FROM employees WHERE employee_id = :emp_id;

چطور بفهمیم Cache خوب کار می‌کنه؟

برای تحلیل وضعیت اجرای Query ها، این دستور خیلی کاربردیه:

				
					SELECT sql_text, executions, parse_calls
FROM v$sql
WHERE sql_text LIKE 'SELECT * FROM employees%';
  • اگر parse_calls خیلی نزدیک به executions باشه، یعنی Query بهینه‌سازی نشده و دائم داره Hard Parse میشه!
  • هدف اینه که parse_calls خیلی کمتر از executions باشه.

ابزارهای حرفه‌ای مدیریت Cache در Oracle

اگر بخوای به صورت دستی روی Shared Pool کنترل داشته باشی، از پکیج DBMS_SHARED_POOL استفاده کن:

				
					-- Pak kardan Cache az Procedure
EXEC DBMS_SHARED_POOL.PURGE('SCOTT.MY_PROCEDURE', 'P');

-- Pin kardan Cache dar Object
EXEC DBMS_SHARED_POOL.KEEP('SCOTT.MY_PROCEDURE', 'P');

راهکارهای عملی برای بهبود سرعت SQL با Shared Pool

  1. همیشه از Bind Variable استفاده کن
  2. Cursor Sharing رو روی FORCE یا SIMILAR قرار بده (تنظیمات پارامتر CURSOR_SHARING)
  3. Shared Pool Size رو متناسب با حجم Query ها تنظیم کن
  4. کدهای PL/SQL که زیاد استفاده می‌شن رو Pin کن
  5. وضعیت SQLها رو با V$SQL مرتب مانیتور کن

وظایف Shared Pool و Library Cache

بخش نقش
Shared Pool حافظه اشتراکی برای SQL، PL/SQL و متادیتا
Library Cache ذخیره و استفاده مجدد از Execution Plan های SQL و PL/SQL

سوالات متداول درباره Shared Pool و Library Cache در اوراکل

Oracle از Shared Pool استفاده می‌کنه تا اطلاعات تکراری مثل SQL Parse شده، کدهای PL/SQL و متادیتا رو فقط یک‌بار در حافظه بارگذاری کنه.

این کار باعث کاهش بار روی CPU، افزایش سرعت اجرای Queryها و استفاده بهینه‌تر از منابع سیستم می‌شه.

  • Hard Parse یعنی SQL برای اولین بار وارد سیستم شده و Oracle باید اون رو Parse، Optimize و برای اجرا آماده کنه. این فرآیند سنگینه.
  • Soft Parse یعنی Oracle همون SQL رو قبلاً دیده و پلان اجرایی اون توی Library Cache هست، پس بدون پردازش مجدد اجراش می‌کنه. Soft Parse خیلی سریع‌تره.
  • با اجرای Query زیر می‌تونی تعداد اجرا (executions) و Parseها (parse_calls) رو ببینی:

SELECT sql_text, executions, parse_calls
FROM v$sql
WHERE executions > 10
ORDER BY parse_calls DESC;

اگر تعداد parse_calls نزدیک به executions باشه، یعنی دچار Hard Parse زیاد شدی و باید بررسی کنی از Bind Variables استفاده می‌کنی یا نه.

راهکارهای بهینه‌سازی Shared Pool شامل موارد زیر هست:

  • استفاده از Bind Variable برای کاهش Hard Parse
  • تنظیم پارامتر CURSOR_SHARING = FORCE برای جایگزینی خودکار Literalها
  • مانیتورینگ منظم با Viewهای V$SQL، V$LIBRARYCACHE
  • استفاده از DBMS_SHARED_POOL.KEEP برای Pin کردن اشیای پرکاربرد در Cache
  • بررسی اندازه Shared Pool و افزایش در صورت نیاز با پارامتر SHARED_POOL_SIZE

جمع‌بندی: Shared Pool، قلب تپنده Performance در Oracle

Shared Pool به زبان ساده، مغز متفکر حافظه اوراکل هست که با جلوگیری از پردازش‌های تکراری، سرعت اجرای SQL رو چند برابر می‌کنه.
Library Cache هم به‌طور خاص وظیفه داره Execution Plan های SQL/PLSQL رو ذخیره کنه تا هر بار از اول ساخته نشن.
اگر بدونی چطور از این دو تا درست استفاده کنی، خیلی راحت می‌تونی سیستم‌هایی با کارایی بالا و تاخیر کم بسازی.

📢 نظر شما چیست؟ اگر شما هم اطلاعات و تجربه خوبی در استفاده از Shared Pool و Library دارید خوشحال میشم در بخش نظرات، تجربه های ارزشمندتان را با ما به اشتراک بگذارید! 🚀

میثم راد

من یه برنامه نویسم که حسابی با دیتابیس اوراکل رفیقم! از اونایی ام که تا چیزی رو کامل نفهمم،ول کن نیستم، یادگرفتن برام مثل بازیه، و نوشتن اینجا کمک می کنه تا چیزایی که یاد گرفتم رو با بقیه به شریک بشم، با هم پیشرفت کنیم.

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *