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

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

Shared Pool در اوراکل چیست و چگونه با SQL و PL/SQL کار می‌کند؟

Shared Pool در اوراکل چیست

اگر با دیتابیس Oracle کار می‌کنی—چه به عنوان برنامه‌نویس، چه DBA—حتماً اسم Shared Pool را زیاد شنیدی.

Shared Pool یکی از حیاتی‌ترین بخش‌های حافظه SGA است و دقیقاً همان جایی است که می‌تواند سرعت اجرای SQL را چند برابر کند یا برعکس، اگر درست مدیریت نشود، سیستم را کند و سنگین کند.

در این مقاله آموزش Oracle در بخش آموزش معماری اوراکل ، به شما توضیح می‌دهم که:

  • Shared Pool دقیقاً چیست
  • چطور با SQL کار می‌کند
  • چطور با PL/SQL ارتباط دارد
  • Hard Parse و Soft Parse یعنی چه
  • چه چیزهایی در Library Cache ذخیره می‌شود
  • چه اشتباهاتی باعث مصرف بی‌رویه Shared Pool می‌شود
  • چطور عملکرد Shared Pool را بهتر کنیم

در بسیاری از سازمان‌ها، پایگاه داده Oracle شامل اطلاعات بسیار حساسی است؛ اطلاعاتی مثل شماره کارت بانکی، شماره موبایل، حقوق کارکنان یا داده‌های شخصی مشتریان.

در چنین شرایطی همیشه این سؤال مطرح می‌شود: آیا همه کاربران دیتابیس باید بتوانند این اطلاعات را به‌صورت کامل ببینند؟

اوراکل برای حل این مسئله قابلیتی بسیار قدرتمند به نام Data Redaction ارائه کرده که از طریق پکیج DBMS_REDACT در دسترس است.

پیشنهاد می کنم این مقاله زیر رو حتما مطالعه کنی.

در این مقاله شما می خوانید

Shared Pool چیست؟

Shared Pool جایی از حافظه Oracle است که وظیفه‌اش ذخیره و مدیریت SQL و PL/SQL است تا:

  • اجرای کوئری‌ها سریع‌تر شود
  • پردازنده کمتر مصرف شود
  • Parse دوباره انجام نشود
  • Execution Planهای تولیدشده، دوباره استفاده شوند

به بیان خیلی ساده:

Oracle سعی می‌کند SQL هایی را که یک‌بار اجرا شده‌اند، به خاطر بسپارد تا دفعه بعد همان‌ها را سریع‌تر اجرا کند.

اجزای Shared Pool: همه چیز حول این دو بخش می‌چرخد

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

۱. Library Cache

جایی برای ذخیره کردن موارد زیر:

  • SQL
  • کدهای PL/SQL
  • Execution Plan ها
  • cursors
  • metadataهای اجرایی

۲. Data Dictionary Cache

جایی برای ذخیره metadata شامل:

  • جداول
  • ستون‌ها
  • ایندکس‌ها
  • کاربران
  • دسترسی‌ها

این همان اطلاعاتی است که Oracle برای تحلیل SQL نیاز دارد.

ارتباط Shared Pool با SQL

وقتی تو یک SQL اجرا می‌کنی، Oracle یک پروسه چهار مرحله‌ای را طی می‌کند:

۱. دریافت SQL

مثلاً:

				
					SELECT salary FROM employees WHERE employee_id = 100;

۲. جستجو در Shared Pool

Oracle دنبال SQL مشابه می‌گردد:

  • اگر پیدا کند → Soft Parse
  • اگر پیدا نکند → Hard Parse

۳. Parse کردن

در Hard Parse این کارها انجام می‌شود:

  • بررسی Syntax
  • بررسی دسترسی کاربر
  • بررسی وجود جدول و ستون
  • ساخت Execution Plan
  • ذخیره کردن SQL در Library Cache

۴. اجرا

این کل جریان کار SQL در Shared Pool است و همین بخش است که یک دیتابیس را سریع یا کند می‌کند.

تفاوت Hard Parse با Soft Parse

Hard Parse

وقتی SQL برای اولین بار دیده می‌شود.

مشکلش:

  • مصرف زیاد CPU
  • کند شدن سیستم
  • فشار بالا روی Library Cache

Soft Parse

وقتی SQL قبلاً در Shared Pool وجود دارد.

مزیت:

  • سرعت بسیار بیشتر
  • عدم نیاز به ساخت Execution Plan

مثال:

اگر سه بار این کوئری اجرا شود:

				
					SELECT * FROM employees WHERE employee_id = 100;

بار اول → Hard Parse

دو بار بعدی → Soft Parse

اشتباه رایج: Literal SQL (قاتل Shared Pool)

اگر SQL به شکل literal نوشته شود:

				
					WHERE id = 1
WHERE id = 2
WHERE id = 3

Oracle همه را SQL جدید می‌بیند، چون متن آنها با هم فرق دارد.

نتیجه:

  • چندین Hard Parse غیرضروری
  • رشد بی‌رویه تعداد cursor ها
  • پر شدن Shared Pool

راه‌حل حرفه‌ای:

استفاده از Bind Variable

				
					SELECT * FROM employees WHERE employee_id = :id;

حالا Oracle این SQL را یک بار parse می‌کند و فقط مقدار :id تغییر می‌کند.

Shared Pool و PL/SQL: چرا PL/SQL همیشه سریع‌تر است؟

وقتی یک Procedure، Function یا Package را کامپایل می‌کنی، Oracle آن را داخل Library Cache می‌گذارد.

به همین دلیل:

  • فقط یک بار کامپایل می‌شود
  • دفعه بعد از حافظه اجرا می‌شود
  • اجرای SQLهای داخل PL/SQL بسیار سریع‌تر است

مثلاً این Procedure:

				
					CREATE OR REPLACE PROCEDURE get_salary(p_id NUMBER)
IS
v_salary NUMBER;
BEGIN
SELECT salary INTO v_salary
FROM employees
WHERE employee_id = p_id;
END;

فقط یک بار کامپایل می‌شود و چندین بار با سرعت بالا اجرا می‌شود.

PL/SQL مخصوصاً برای اپلیکیشن‌هایی که حجم زیادی SQL مشابه اجرا می‌کنند، واقعاً معجزه می‌کند.

Cursor چیست و چه نقشی در Shared Pool دارد؟

هر SQL در Shared Pool به صورت یک cursor ذخیره می‌شود.

Cursor شامل:

  • متن SQL
  • Execution Plan
  • بایت‌کد PL/SQL (برای برنامه‌ها)
  • آمار اجرا (executions)

مشاهده آن:

				
					SELECT sql_text, executions FROM v$sql;

مشکلات رایج Shared Pool

۱. Hard Parse زیاد

علت:

  • عدم استفاده از bind variable
  • اجرای زیاد dynamic SQL
  • Shared Pool کوچک

راه‌حل:

  • Bind Variable
  • cursor_sharing مناسب
  • افزایش shared_pool_size

۲. Library Cache Miss

علت:

  • Purge شدن Cache
  • fragmentation

۳. Shared Pool Fragmentation

علت:

  • کوئری‌های متنوع و زیاد
  • پکیج‌های سنگین PL/SQL

چگونه اندازه Shared Pool را بررسی کنیم؟

				
					SHOW PARAMETER shared_pool_size;

تغییر اندازه:

				
					ALTER SYSTEM SET shared_pool_size = 800M SCOPE=BOTH;

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

Shared Pool جایی است که Oracle کوئری‌ها و Execution Plan ها را در آن ذخیره می‌کند. وقتی یک SQL برای اولین بار اجرا می‌شود، Oracle باید آن را parse کند که این کار زمان‌بر است.
اما اگر همان SQL دوباره اجرا شود و در Shared Pool وجود داشته باشد، Oracle بدون parse مجدد از نسخه ذخیره‌شده استفاده می‌کند.

این یعنی:

  • CPU کمتر مصرف می‌شود
  • سرعت اجرای کوئری بالا می‌رود
  • Session ها کمتر منتظر می‌مانند
  • Performance کلی دیتابیس بسیار بهتر می‌شود

به همین دلیل است که tuning کردن Shared Pool یکی از مهم‌ترین کارهای یک DBA است.

سه دلیل اصلی Hard Parse بالا عبارتند از:

  1. استفاده نکردن از Bind Variableیعنی این SQL ها:

WHERE id = 10
WHERE id = 20
WHERE id = 30

برای Oracle سه statement متفاوتند.

  1. استفاده از Dynamic SQL زیاد

    مخصوصاً اگر متن SQL داخل برنامه تغییر کند.

  2. Shared Pool کوچک

    وقتی فضای کافی نباشد، Oracle مجبور می‌شود Cache را پاک (evict) کند.

Hard Parse زیاد باعث می‌شود:

  • CPU به‌شدت درگیر شود
  • latency بالا برود
  • Library Cache latch/mutex contention ایجاد شود

Library Cache یکی از بخش‌های مهم Shared Pool است که موارد زیر را ذخیره می‌کند:

  • متن SQL
  • Execution Plan
  • کد کامپایل‌شده PL/SQL
  • اطلاعات cursor
  • dependency های objectها

اهمیتش از این جهت است که:

  • Parse کمتر انجام شود
  • کوئری‌های تکراری سریع‌تر اجرا شوند
  • پکیج‌ها و پروسیجرهای PL/SQL فقط یک بار کامپایل شوند
  • برنامه‌های سنگین، load کمتری روی CPU بگذارند

اگر Library Cache خوب کار نکند، حتی بهترین سخت‌افزار هم نمی‌تواند دیتابیس را سریع نگه دارد.

۳ تکنیک فوق‌العاده مؤثر که تقریباً همه DBAهای حرفه‌ای به آن تکیه می‌کنند:

  1. استفاده از Bind Variable در همه SQL های تکرارشونده

    این مهم‌ترین عامل کاهش Hard Parse است.

  2. افزایش اندازه Shared Pool و اتصال LRU مناسب

    اگر Shared Pool کوچک باشد، Cache دائم تخلیه می‌شود.

  3. استفاده از PL/SQL به‌جای اجرای چندین SQL پشت سر هم

    اجرای SQL داخل PL/SQL بسیار بهینه‌تر است چون Compilation فقط یک‌بار انجام می‌شود.

جمع‌بندی: Shared Pool قلب عملکرد SQL در Oracle است

Shared Pool جایی است که Oracle از آن برای:

  • ذخیره SQL
  • ذخیره PL/SQL
  • نگه داشتن execution plan
  • کم کردن Hard Parse
  • افزایش سرعت اجرا

استفاده می‌کند.

اگر Shared Pool را خوب بشناسی و SQL را درست بنویسی،

در عمل می‌توانی سرعت یک سیستم را چند برابر بهتر کنی.

سؤالی درباره این مقاله داری؟

اگر نکته‌ای در این مقاله برات مبهم بود یا خواستی بیشتر بدونی، همین حالا برام بنویس تا دقیق و صمیمی پاسخت رو بدم — مثل یه گفت‌وگوی واقعی 💬

برو به صفحه پرسش و پاسخ

میثم راد

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

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

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