بایگانی آذر ۱۳۹۹ :: دیتابیس اوراکل

DBMS_JOB in Oracle DB 19c

راهله شاکری | يكشنبه, ۱۶ آذر ۱۳۹۹، ۰۱:۴۸ ب.ظ | ۰ نظر

اوراکل از نسخه 12cR2 اعلام کرد که پکیج DBMS_JOB، منسوخ (deprecate) شده است. و در نسخه 19c با تبدیل هر DBMS_JOB ایجاد شده به یک جاب DBMS_SCHEDULER، یک گام هم در جهت منسوخ کردن این پکیج جلوتر رفته است.

در زیر مثالی در رابطه با این موضوع آورده شده است:

SQL> conn raheleh

SQL> select job, what from user_jobs;

no rows selected

SQL> select job_name, job_action from user_scheduler_jobs;

no rows selected

SQL>

CREATE TABLE raheleh.tbl1

(

id NUMBER,

name VARCHAR2 (30)

);

SQL> INSERT INTO tbl1

(SELECT user_id, username FROM dba_users)

COMMIT;

SQL>

CREATE OR REPLACE PROCEDURE raheleh.prc_dbmsjob (username IN VARCHAR2)

IS

userid NUMBER;

BEGIN

SELECT id

INTO userid

FROM raheleh.tbl1

WHERE name = username;

END;

SQL>

DECLARE

job1 PLS_INTEGER;

BEGIN

DBMS_JOB.submit (job => job1,

what => 'begin raheleh.prc_dbmsjob (''SYSTEM''); end;',

next_date => TRUNC (SYSDATE) + 1,

interval => 'trunc(sysdate)+1');

END;

COMMIT;

SQL> select job, what from user_jobs;

JOB WHAT

---------- ------------------------------

22 begin raheleh.prc_dbmsjob ('SYSTEM'); end;

SQL> select job_name, job_action from user_scheduler_jobs;

JOB_NAME JOB_ACTION

------------------------------ ------------------------------

DBMS_JOB$_22 begin raheleh.prc_dbmsjob ('SYSTEM'); end;

همانطور که در مثال بالا مشاهده کردید، وقتی یک جاب با DBMS_JOB ایجاد کردیم، اوراکل به صورت اتومات، معادل همان جاب ، یک جاب هم با DBMS_SCHEDULER ایجاد کرد که نام آن با DBMS_JOB$_* شروع میشود.

َAutomatic Indexing در اوراکل دیتابیس 19c

راهله شاکری | چهارشنبه, ۱۲ آذر ۱۳۹۹، ۱۲:۰۸ ب.ظ | ۱ نظر

Category: Big Data and Data Warehousing

Oracle DB Version: 19.3.0.0

Oracle DB Type: RAC

ایندکس یکی از ساختارهای مهم در بالا بردن کارایی دیتابیس است. ایندکسها هم برای محیط OLTP که SQLهای زیادی در روز اجرا میکنند، هم برای محیط های OLAP که میخواهند بخش کوچکی از دیتا را از یک جدول بزرگ انتخاب کنند ضروری هستند. اگر همزمان با تغییر در لود اپلیکیشن، ایندکس ها آپدیت نشوند، این موضوع می تواند باعث کاهش قابل توجه عملکرد دیتابیس شود. از featureهای جدید در اوراکل دیتابیس 19c، Automatic Indexing است که برای بهبود بخشیدن کارایی دیتابیس هنگام تغییر در لود اپلیکیشن، میتواند به صورت اتومات، ایندکسهای لازم را create، rebuild یا drop کند.

Automatic Indexing چگونه کار میکند؟

فرآیند Automatic Indexing در background، هر 15 دقیقه اجرا و عملیات زیر را انجام میدهد:

انتخاب ایندکسهای کاندید بر اساس میزان استفاده ستونهای جداول در دستورات SQL.
ایجاد ایندکسهای کاندید به صورت invisible.
اگر performance مربوط به آن SQL که ایندکس برای آن ایجاد شده است بهبود پیدا کرد، وضعیت ایندکس به visible تغییر پیدا میکند. و از این به بعد میتواند در دستورات SQL مورد استفاده قرار بگیرید.
ولی اگر performance بهبود پیدا نکرد، وضعیت ایندکس به unusable تغییر پیدا میکند و در نهایت طی یک فرآیند اتومات کلا پاک میشود. و آن دستور SQL در balck list قرار میگیرد. دستورات SQL که جزو black list قرار گرفته اند، در آینده در فرآیند automatic indexing مورد بررسی قرار نمی گیرند.

نکات مهم:

برای استفاده از auto indexing، باید statisticها آپدیت باشند. جداولی که statistic نداشته باشند یا statistic آنها stale باشد و real-time statistic هم برای آنها در دسترس نباشد در لیست بررسی automatic indexing قرار نمی گیرند.
Automatic Indexها میتوانند به صورت single-column یا multi-column باشند.
Auto Indexها برای SQLهایی که اولین بار در دیتابیس اجرا میشوند، مورد استفاده قرار نمی گیرند.
میتوان تنظیم کرد که جاب مربوط به Auto Indexing برای یک ساعاتی غیرفعال شود. یا اینکه تنظیم کنید این جاب کلا با ریسورس خیلی کمی اجرا شود.

کانفیگ کردن Automatic Indexing در Oracle DB

فعالسازی و غیر فعالسازی Automatic Indexing

برای فعالسازی Automatic Indexing، باید از پروسیجر CONFIGURE در پکیج DBMS_AUTO_INDEX استفاده کنید، که میتواند مقادیر زیر را بگیرد.

IMPLEMENT: فعال کردن Automatic Indexing

REPORT ONLY: ایندکسهای جدید ایجاد میشوند ولی invisible باقی می مانند.

OFF: غیرفعال کردن Automatic Indexing

وقتی می خواهیم این ویژگی را فعال کنیم، خطای زیر را می گیریم:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');

BEGIN DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT'); END;

*

ERROR at line 1:

ORA-40216: feature not supported

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79

ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 9180

ORA-06512: at "SYS.DBMS_AUTO_INDEX", line 283

ORA-06512: at line 1

EE-ES: Available on Exadata. Not available on Oracle Database Appliance.

دلیل آن هم این است که این feature در حال حاضر فقط در نسخه های Oracle Database Exadata Cloud Service و Oracle Database Enterprise Edition on Engineered Systems فعال می باشد.

Notes

ExaCS

DBCS EE-EP

DBCS EE-HP

DBCS EE

DBCS SE

EE-ES

EE

SE2

Feature /

Option /

Pack

EE-ES: Available on Exadata. Not available on Oracle Database Appliance.

Y

N

Y

N

Automatic Indexing

برای اینکه این feature را تست کنیم می توانیم با استفاده از روش زیر برای نسخه Enterprise Edition آن را فعال کنیم.

SQL> alter system set "_exadata_feature_on"=true scope=spfile sid='*';

[oracle@test1-rac19c ~]$ srvctl stop database -d ORCL

[oracle@test1-rac19c ~]$ srvctl start database -d ORCL

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');

PL/SQL procedure successfully completed.

SQL> SELECT parameter_name, parameter_value

FROM dba_auto_index_config;

PARAMETER_NAME PARAMETER_VALUE
----------------------------------- --------------------
AUTO_INDEX_COMPRESSION OFF
AUTO_INDEX_DEFAULT_TABLESPACE
AUTO_INDEX_MODE IMPLEMENT
AUTO_INDEX_REPORT_RETENTION 31
AUTO_INDEX_RETENTION_FOR_AUTO 373
AUTO_INDEX_RETENTION_FOR_MANUAL
AUTO_INDEX_SCHEMA
AUTO_INDEX_SPACE_BUDGET 50

غیر فعالسازی Automatic Indexing برای یک یا چند اسکیما

وقتی Automatic Indexing را فعال می کنید به صورت پیش فرض همه اسکیماها میتوانند از این ویژگی استفاده کنند. با استفاده از دستور زیر می توانیم این ویژگی را برای یک یا چند اسکیما غیرفعال کنیم یا اصطلاحا آن اسکیما را در exclusion list قرار دهیم.

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'HR', allow => FALSE);

SQL> SELECT parameter_name, parameter_value

FROM dba_auto_index_config

WHERE parameter_name = 'AUTO_INDEX_SCHEMA';

PARAMETER_NAME PARAMETER_VALUE

-------------------- ----------------------------------------

AUTO_INDEX_SCHEMA schema NOT IN (HR)

و با دستور زیر میتوان یک اسکیما را از exclusion list خارج کرد:

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'HR', NULL);

دستور زیر همه اسکیماها را از exclusion list خارج میکند، یعنی Auto Indexing مثل حالت پیش فرض برای همه اسکیماها فعال میشود.

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', NULL, allow => TRUE);

تغییر retention period برای unused auto index

به صورت پیش فرض auto indexهایی که ایجاد شده ولی استفاده نشده اند، بعد از 373 روز drop می شوند. به صورت زیر میتوان این پیش فرض را تغییر داد:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_AUTO', '90');

PL/SQL procedure successfully completed.

SQL> SELECT parameter_name, parameter_value

FROM dba_auto_index_config

WHERE parameter_name = 'AUTO_INDEX_RETENTION_FOR_AUTO';

PARAMETER_NAME PARAMETER_VALUE

------------------------------ --------------------

AUTO_INDEX_RETENTION_FOR_AUTO 90

و به صورت زیر میتوان آن را به مقدار پیش فرض برگرداند:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_AUTO', NULL);

SQL> SELECT parameter_name, parameter_value

FROM dba_auto_index_config

WHERE parameter_name = 'AUTO_INDEX_RETENTION_FOR_AUTO';

PARAMETER_NAME PARAMETER_VALUE

------------------------------ --------------------

AUTO_INDEX_RETENTION_FOR_AUTO 373

مشخص کردن retention period برای non-auto indexes

اگر میخواهید برای ایندکس هایی که به صورت دستی هم ایجاد شده اند، یک retention تعیین کنید که بعد از n روز که unused بودند به صورت اتومات drop شوند، می توانید با استفاده از روش زیر این کار را انجام دهید. در غیر اینصورت ایندکس هایی که به صورت دستی ایجاد شده اند به صورت اتومات drop نمی شوند.

SQL> SELECT parameter_name, parameter_value

FROM dba_auto_index_config

WHERE parameter_name = 'AUTO_INDEX_RETENTION_FOR_MANUAL';

PARAMETER_NAME PARAMETER_VALUE

--------------------------------- --------------------

AUTO_INDEX_RETENTION_FOR_MANUAL

به صورت زیر میتوان این پارامتر را تغییر داد:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_MANUAL', '60');

PL/SQL procedure successfully completed.

SQL> SELECT parameter_name, parameter_value

FROM dba_auto_index_config

WHERE parameter_name = 'AUTO_INDEX_RETENTION_FOR_MANUAL';

PARAMETER_NAME PARAMETER_VALUE

------------------------------ --------------------

AUTO_INDEX_RETENTION_FOR_MANUAL 60

و به صورت زیر هم میتوان آن را به مقدار پیش فرض برگرداند:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_MANUAL', NULL);

PL/SQL procedure successfully completed.

SQL> SELECT parameter_name, parameter_value

FROM dba_auto_index_config

WHERE parameter_name = 'AUTO_INDEX_RETENTION_FOR_MANUAL';

PARAMETER_NAME PARAMETER_VALUE

------------------------------ --------------------

AUTO_INDEX_RETENTION_FOR_MANUAL

تعیین یک tablespace مشخص برای auto indexها

میتوانیم برای ذخیره کردن Auto Indexها یک tablespace جداگانه تعریف کنیم. به این نکته توجه داشته باشید که نمیتوان از tablespaceهای پیش فرض اوراکل مثل sysaux برای این کار استفاده کرد.

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE','TBS_AUTOIDX');

PL/SQL procedure successfully completed.

SQL> SELECT parameter_name, parameter_value

FROM dba_auto_index_config

WHERE parameter_name = 'AUTO_INDEX_DEFAULT_TABLESPACE';

PARAMETER_NAME PARAMETER_VALUE

------------------------------ --------------------

AUTO_INDEX_DEFAULT_TABLESPACE TBS_AUTOIDX

ارائه یک مثال از کاربرد Auto Indexing

1. ابتدا یک جدول ایجاد می کنیم و تعداد زیادی رکورد در آن insert میکنیم.

SQL> CREATE TABLE test1.tab_test1_autoidx AS

( SELECT * FROM dba_objects );

SQL> INSERT INTO test1.tab_test1_autoidx

( SELECT * FROM test1.tab_test1_autoidx );

COMMIT;

SQL> exec DBMS_STATS.GATHER_TABLE_STATS('TEST1','TAB_TEST1_AUTOIDX');

SQL> SELECT COUNT(*)

FROM test1.tab_test1_autoidx;

COUNT(*)

----------

586248

2. روی جدول ایجاد شده یک select اجرا می کنیم و plan را برای آن دستور مشاهده می کنیم.

SQL> SELECT * FROM test1.tab_test1_autoidx WHERE owner='DBSNMP';

SQL> EXPLAIN PLAN FOR

SELECT * FROM test1.tab_test1_autoidx WHERE owner='DBSNMP';

SQL> select * from table(dbms_xplan.display());

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

SQL> SELECT execution_name, index_name, index_owner, command, statement

FROM dba_auto_index_ind_actions

WHERE table_name = 'TAB_TEST1_AUTOIDX';

STATEMENT	COMMAND	INDEX_NAME	INDEX_OWNER	EXECUTION_NAME
CREATE INDEX "TEST1"."SYS_AI_85xrrw9rhauv0" ON "TEST1"."TAB_TEST1_AUTOIDX"("OWNER") TABLESPACE "TBS_AUTOIDX" UNUSABLE INVISIBLE AUTO ONLINE	CREATE INDEX	SYS_AI_85xrrw9rhauv0	TEST1	SYS_AI_2020-12-01/10:40:22
ALTER INDEX "TEST1"."SYS_AI_85xrrw9rhauv0" REBUILD ONLINE	REBUILD INDEX	SYS_AI_85xrrw9rhauv0	TEST1	SYS_AI_2020-12-01/10:40:22
ALTER INDEX "TEST1"."SYS_AI_85xrrw9rhauv0" VISIBLE	ALTER INDEX VISIBLE	SYS_AI_85xrrw9rhauv0	TEST1	SYS_AI_2020-12-01/10:40:22

ستونی که تقریبا در تمام viewهای مرتبط با Auto Indexها مشترک است، execution_name است. بر این اساس میتوانیم بر اساس execution_name یا execution_nameهای مرتبط با یک ایندکس، اطلاعاتی از دیگر viewها راجع به آن بدست آوریم.

SQL> SELECT *

FROM dba_auto_index_statistics

WHERE execution_name = 'SYS_AI_2020-12-01/10:40:22';

لیست viewهای دیگر که میتوان از آنها در مورد Auto Indexها اطلاعات بدست آورد در زیر آمده است:

DBA_AUTO_INDEX_EXECUTIONS

DBA_AUTO_INDEX_SQL_ACTIONS

DBA_AUTO_INDEX_VERIFICATIONS

بعد از ایجاد ایندکس وقتی مجددا دستور قبل را اجرا کردم، plan آن به شکل زیر تغییر پیدا کرده بود:

SQL> EXPLAIN PLAN FOR

SELECT * FROM test1.tab_test1_autoidx WHERE owner='DBSNMP';

SQL> SELECT * FROM table(dbms_xplan.display());

از view زیر هم میتوانیم لیست تمام Auto Indexهای ایجاد شده در دیتابیس را مشاهده کنیم:

SELECT *

FROM dba_indexes

WHERE auto = 'YES';

تهیه ریپورتهای مختلف بر اساس یک بازه زمانی

میتوانیم با استفاده از یک سری پروسیجرها، گزارشهای مختلفی در رابطه با Automatic Indexها تهیه کنیم. نوع این ریپورت ها میتواند HTML، TEXT و XML باشد. اسامی این پروسیجرها به شرح زیر میباشد:

DBMS_AUTO_INDEX.REPORT_ACTIVITY();

DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY();

یک نمونه از اجرای این ریپورتها در زیر آمده است:

SET SERVEROUTPUT ON

DECLARE

report CLOB := NULL;

BEGIN

report := dbms_auto_index.report_activity(

activity_start => to_timestamp('2020-12-01','YYYY-MM-DD'),

activity_end => to_timestamp('2020-12-02','YYYY-MM-DD'),

type => 'HTML',

section => 'ALL',

level => 'ALL');

dbms_output.put_line(report);

END;

خروجی ریپورت بالا به شکل زیر است:

AutoIndex_Report.html

منابع

https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-indexes.html#GUID-FAE533D7-9C9E-48C9-9A61-EBC060BF3D70

https://webcache.googleusercontent.com/search?q=cache:NvWPTTaq_csJ:https://juliandontcheff.wordpress.com/2019/02/18/automatic-indexing-in-19c/+&cd=6&hl=en&ct=clnk&gl=ir

مشاهده مقدار bind_variable

راهله شاکری | دوشنبه, ۳ آذر ۱۳۹۹، ۰۱:۴۳ ب.ظ | ۰ نظر

SELECT *
FROM gv$sql_bind_capture
WHERE inst_id = 1
AND sql_id = '16h7r0vpcgvu2'
AND child_address = '00000000ECCD5350';

SELECT *
FROM DBA_HIST_SQLBIND
WHERE sql_id = '16h7r0vpcgvu2';

session_waits

راهله شاکری | يكشنبه, ۲ آذر ۱۳۹۹، ۱۱:۳۹ ق.ظ | ۰ نظر

کوئری زیر sessionهایی که در حال حاضر wait هستند را نمایش می دهد.

SELECT s.inst_id,s.sid,s.serial#,s.username,s.status,s.osuser,s.program,s.machine,
s.sql_id,s.sql_hash_value,s.sql_child_number,
s.event,s.wait_class,s.p1,s.p2,s.p3,s.state,s.wait_time,s.seconds_in_wait
FROM gv$session s
WHERE s.inst_id = 1
AND s.wait_class <> 'Idle'
AND s.username NOT IN ('SYS','SYSTEM','DBSNMP','AUDSYS')
AND s.state='WAITING'
ORDER BY seconds_in_wait DESC;

اگر میخواهید بدانید که P1,P2,P3 به ازای هر event چه مقداری را نمایش میدهند، کوئری زیر را اجرا کنید.

SELECT name,parameter1,parameter2,parameter3
FROM v$event_name
WHERE name = 'gc current request';

PARAMETER3	PARAMETER2	PARAMETER1	NAME
id#	block#	file#	gc current request

دیتابیس اوراکل

دیتابیس اوراکل

DataPump

12C

Tablespace

Oracle Linux

SQL & PL/SQL

Data Guard

Optimizer

FlashBack

Scripts

ORA-error

OEM

Parameter

Partitioning

RAC

19C

RMAN

Multitenant

Grid

Wait

۴ مطلب در آذر ۱۳۹۹ ثبت شده است

DBMS_JOB in Oracle DB 19c

َAutomatic Indexing در اوراکل دیتابیس 19c

مشاهده مقدار bind_variable

session_waits