هزینه اجرا (cost) انجام کوئری ها در اوراکل

در این قسمت از آموزش اوراکل، در بخش SQL Tuning قصد داریم آموزش هزینه اجرا (cost) انجام کوئری ها در اوراکل را یاد بگیریم.

مقایسه هزینه اجرا cost انجام کوئری ها

نرم افزار دیتابیس اوراکل از یک زیر سیستمی به نام optimizer کارآمدترین مسیر دسترسی داده های جداول را تولید می کند که به مسیر تولید شده توسط optimizer اصطلاحا execution plan می نامند.

در اوراکل نسخه ۱۰g به بعد این اطلاعات مسیر دسترسی به جداول و execution plan بصورت اتوماتیک جمع آوری می شود در یک جدول خاصی که بصورت مرتب ایندکس گذاری می شود و خودش بهترین execution plan را تولید میکند.

مقایسه execution plans تولید شده توسط optimizer به شما این اجازه را میدهد که از بین مسیرهای دسترسی تولید شده بهترین مسیر که کمترین هزیته اجرا را دارد با هم مقایسه کرده و با بهترین قضاوت یکی را انتخاب کنید برای اجرا کوئری که در نتیجه باعث می شود کوئری شما از لحاظ بهینه perfomance بهترین باشد.

بررسی Execution Plans

optimizer تولید میکند execution plan را برای کوئری ها و دستورات SQL. شما در محیط SQL*PLUS می توانید execution plan را با استفاده از دستور EXPLAIN PLAN بررسی کنید.

دستور EXPLAIN PLAN بررسی execution plan کوئری ها در جدولی به نام plan_table قرار می دهد و نتیجه را در این جدول قرار میدهد .

برای بررسی execution plan یک کوئری و دستور SQL ابتدا باید بررسی کنیم که جدول plan_table در دیتابیس وجود دارد یا خیر.

بررسی جدول Plan Table در دیتابیس

ابتدا یایستی بررسی شود که جدول Plan Table در دیتابیس وجود دارد یا خیر. اگر جدول Plan Table در دیتابیس وجود داشته باشد با دستور DESCRIBE که بصورت زیر می باشد خروجی زیر را بایستی نشان دهد.

sqltuning
آموزش اوراکل

با اجرای دستور بالا اگر اطلاعات و ساختار جدول را نمایش داد به این معنی که جدول Plan Table وجود دارد اگر با خطا موجه شدید نشان میدهد این جدول وجود ندارد و بایستی از جدول Plan Table را ایجاد نمایید.

ایجاد جدول Plan Table

اگر در دیتابیس جدول plan table نباشد بایستی به مدیر دیتابیس اعلام کنید تا این جدول را برای شما ایجاد کند و برای انجام این کار در محیط SQL*PLUS اسکریپت utlxplan.sql را اجرا کنید.

این اسکریپت در صورتی که اوراکل در سیستم عامل ویندوز نصب شده باشد در ادرس زیر می باشد.

C:\oracle12c\product\12.1.0\dbhome_1\RDBMS\ADMIN

و در صورتی که اوراکل در سیستم عامل linux نصب کرده اید در آدرس زیر می باشد.

/u01/app/oracle12c/product/12.1.0/dbhome_1/rdbms/admin

اسکریپت زیر را بصورت زیر در محیط SQL*PLUS اجرا کنید و در مرحله دوم یک public synonym برای جدول plan table بسازید و در مرحله سوم دسترسی های لازم این جدول plan table را به نقش public اعمال کنید.

آموزش اوراکل

تولید یک Execution Plan

بعد از اینکه جدول plan table ایجاد شد با وارد کردن دستور EXPLAIN PLAN برای کوئری و دستور SQL مورد نظر execution plan تولید میکنید که ساختار دستوری آن به شکل زیر می باشد :

sqltuning

که در دستور بالا :

  • statement_id نامی می باشد که برای صدا زدن و فراخوانی execution plan می باشد و این نام میتوانید از حروف باشد.
  • sql_statement همان کوئری و دستور SQL می باشد که برای آن execution plan را تولید کرده اید.

با توجه به مثال زیر یک execution plan برای کوئری زیر ایجاد شد و پس از تولید execution plan شما می توانید execution plan ذخیره شده را در جدول plan table بدست خواهید آورد که در ادامه خواهیم گفت.

آموزش اوراکل

طریقه کوئری زدن از جدول plan table

برای کوئری زدن از جدول plan table ابتدا در محیط SQL*PLUS اسکریپتی به نام explain_plan.sql که در دایرکتوری SQL را می باشد را اجرا کنید. این اسکریپت تلاش می کند از طریق statement_id که به عنوان ورودی ن نام کوئری برای execution plan را وارد کنید و در خروجی execution plan و هزینه اجرای هر خط از کوئری را به شما نمایش دهد.

اسکریپت explain_plan.sql به شرح زیل می باشد :

sqltuning

execution plan تولید شده و به صورت سلسه مراتبی از عملیات ها شبیه درخت که سلسله مراتبی می باشد سازمان دهی می شود. که جزئیات این execution plan کوئری مربوطه در جدول plan table ذخیره می شود. که سلسله مراتب این عملیات ها بصورت درختواره از ۰ شروع می شود که ۰ نشان دهنده آغاز عملیات و اصطلاحا root می نامند و در سطح های بعدی با عدد های ۱ و۲ و .. n ادامه پیدا میکند.

ترتیب اجرای سطح های عملیات که با اعداد مشخص می شود روند اجرای هر عملیات از آخر به اول می باشد به صورتی که عدد بزرگتر در بالاترین سطح اجرا شده به ترتیب تا اول سطح ۰ می باشد.

مثال زیر اجرای یک execution plan می باشد که نام statement_id به عنوان ورودی کوئری داده می شود.

آموزش اوراکل

مطالب بیشتر در مورد SQL Tuning

دیدگاه خود را بنویسید:

آدرس ایمیل شما نمایش داده نخواهد شد.

*

code

فوتر سایت