تا قبل از اوراکل 12c، رفتار اوراکل در زمان جمع آوری آمار برای جداول از نوع (global temporary table(GTT تفاوتی با جداول معمولی نداشت و این مسئله می توانست در مواردی چالش برانگیز باشد.

در اوراکل 12c بهبودهایی در این زمینه حاصل شد که در ادامه دو مورد از این بهبودها را مشاهده می کنید.

بهبود اول: تا قبل از اوراکل نسخه 12c، هر گونه آمار جمع آوری شده برای جداول از نوع GTT مابین همه sessionها به اشتراک گذاشته می شد(همانند جداول معمولی):

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0

SQL> create global temporary table GTT_MYTBL(id number,name varchar2(14)) on commit preserve rows;

Table created

–session 1:

SQL> insert into GTT_MYTBL values(1,’USEF’);

1 row inserted

SQL> select p.table_name,p.num_rows,p.last_analyzed from user_tables p where p.table_name=’GTT_MYTBL’;

TABLE_NAME                       NUM_ROWS LAST_ANALYZED

—————————— ———- ————-

GTT_MYTBL     

SQL> exec dbms_stats.gather_table_stats(ownname => user,tabname => ‘GTT_MYTBL’);

PL/SQL procedure successfully completed

SQL> select p.table_name,p.num_rows,to_char(last_analyzed,’YYYY/MM/DD HH24:mi:ss’) last_analyzed from user_tables p where p.table_name=’GTT_MYTBL’;

TABLE_NAME                       NUM_ROWS LAST_ANALYZED

—————————— ———- ———————

GTT_MYTBL                               1 2020/03/05 10:39:13

–session 2:

SQL> begin

  2    for i in 1..1000 loop

  3      insert into GTT_MYTBL values(1,’USEF’);

  4    end loop;

  5    end;

  6  /

PL/SQL procedure successfully completed

SQL> select p.table_name,p.num_rows,p.last_analyzed from user_tables p where p.table_name=’GTT_MYTBL’;

TABLE_NAME                       NUM_ROWS LAST_ANALYZED

—————————— ———- ————-

GTT_MYTBL                               1 2020/03/05 10

در اوراکل 12c بهبودی در این زمینه حاصل شد که بر اساس آن می توان برای این نوع از جداول(GTT)، در سطح session آمار جمع آوری کرد. این قابلیت بصورت پیش فرض فعال می باشد.

Connected to Oracle Database 12c Enterprise Edition Release 12.2.0.1.0

SQL> select dbms_stats.get_prefs(ownname=>user, tabname=>’GTT_MYTBL’,pname=>’GLOBAL_TEMP_TABLE_STATS‘) from dual;

TABLE_NAME     STAT_LEVEL

——–                  ———

GTT_MYTBL     SESSION

–session 1

SQL> create global temporary table GTT_MYTBL(id number,name varchar2(14)) on commit preserve rows;

Table created

SQL> insert into GTT_MYTBL values(1,’USEF’);

1 row inserted

SQL> select table_name, num_rows, scope from user_tab_statistics where table_name =’GTT_MYTBL’;

TABLE_NAME        NUM_ROWS SCOPE

————— ———- ——-

GTT_MYTBL                  SHARED

SQL> exec dbms_stats.gather_table_stats(ownname => user,tabname => ‘GTT_MYTBL’);

PL/SQL procedure successfully completed

SQL> select table_name, num_rows, scope from user_tab_statistics where table_name =’GTT_MYTBL’;

TABLE_NAME  NUM_ROWS    SCOPE

———-  ———- ——-

GTT_MYTBL              SHARED

GTT_MYTBL     1        SESSION

SQL> set autotrace traceonly explain

SQL> Select * from GTT_MYTBL;

–session 2

SQL> begin

  2        for i in 1..1000 loop

  3          insert into GTT_MYTBL values(1,’USEF’);

  4        end loop;

  5  end;

  6  /

PL/SQL procedure successfully completed

SQL> exec dbms_stats.gather_table_stats(ownname => user,tabname => ‘GTT_MYTBL’);

PL/SQL procedure successfully completed

SQL> select table_name, num_rows, scope from user_tab_statistics where table_name =’GTT_MYTBL’;

TABLE_NAME  NUM_ROWS                SCOPE

———-  ———-   ——-

GTT_MYTBL                            SHARED

GTT_MYTBL     1000       SESSION

SQL> set autotrace traceonly explain

SQL> Select * from GTT_MYTBL;

با مشاهده مجدد plan این دستور در session  شماره یک، مطمئن خواهیم شد که هر کدام از sessionها، از statisticsهای مختص به خود استفاده می کنند:

–session 1

SQL> select table_name, num_rows, scope from user_tab_statistics where table_name =’GTT_MYTBL’;

TABLE_NAME  NUM_ROWS    SCOPE

———-  ———- ——-

GTT_MYTBL              SHARED

GTT_MYTBL     1        SESSION

SQL> set autotrace traceonly explain

SQL> Select * from GTT_MYTBL;

 

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

SQL> exec dbms_stats.set_table_prefs(ownname=>user, tabname=>’GTT_MYTBL’,pname=>’GLOBAL_TEMP_TABLE_STATS’,pvalue=>’SHARED’);

PL/SQL procedure successfully completed

همچنین می توان با کمک دستور زیر، محدوده جمع آوری آمار را برای همه جداول GTT به SHARED تغییر داد:

SQL> exec dbms_stats.set_global_prefs(‘GLOBAL_TEMP_TABLE_STATS‘,’SHARED’);

PL/SQL procedure successfully completed

نکته پایانی: جمع آوری آمار برای جداول از نوع GTT در نسخه های قبل از 12c توصیه نمی شود و استفاده از قابلیت Dynamic sampling می تواند در این زمینه راهگشا باشد. در صورتی که برای جداول از نوع GTT آماری وجود نداشته باشد جمع آوری آمار با کمک dynamic sampling و با درجه دو، انجام خواهد شد.

SQL> insert into GTT_MYTBL values(1,’USEF’);

1 row created.

SQL> select /*+ gather_plan_statistics session1 */ count(*) from GTT_MYTBL;

  COUNT(*)

———-

         1

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

 برای تغییر درجه dynamic sampling در سطح یک پرس و جو، می توان از هینتِ با فرمت زیر استفاده کرد:

/*+ dynamic_sampling({alias} {level}) */ 

 بهبود دوم: بهبودی برای GTTهای از نوع on commit delete rows: تا قبل از اوراکل نسخه 12c، با اجرای بسته dbms_stats، ابتدا تراکنشهای جاری sessionه commint شده و سپس دستور اجرا می شد.

در مثال زیر، این مسئله را برای جدول non-GTT مشاهده می کنید:

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0

SQL> create table mytbl (id number,name varchar2(10));

Table created

SQL> insert into mytbl values(10,’USEF’);

1 row inserted

SQL> exec dbms_stats.gather_table_stats(ownname => user,tabname => ‘MYTBL’);

PL/SQL procedure successfully completed

SQL> select * from mytbl;

        ID NAME

———- ———-

        10 USEF

SQL> rollback;

Rollback complete

SQL> select * from mytbl;

        ID NAME

———- ———-

        10 USEF

این مسئله برای جداول GTT از نوع on commit delete rows هم صادق است:

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0

SQL> create global temporary table GTT_MYTBL(id number,name varchar2(14)) on commit delete rows;

Table created

SQL> insert into GTT_MYTBL values(1,’USEF’);

1 row inserted

SQL> exec dbms_stats.gather_table_stats(ownname => user,tabname => ‘GTT_MYTBL’);

PL/SQL procedure successfully completed

SQL> select * from GTT_MYTBL;

        ID NAME

———- ————–

SQL>

در اوراکل 12c، برای جداول non-GTT تغییری در این زمینه ایجاد نشده است:

Connected to Oracle Database 12c Enterprise Edition Release 12.2.0.1.0

SQL> create table mytbl (id number,name varchar2(10));

Table created

SQL> insert into mytbl values(10,’USEF’);

1 row inserted

SQL> exec dbms_stats.gather_table_stats(ownname => user,tabname => ‘MYTBL’);

PL/SQL procedure successfully completed

SQL> select * from mytbl;

        ID NAME

———- ———-

        10 USEF

SQL> rollback;

Rollback complete

SQL> select * from mytbl;

        ID NAME

———- ———-

        10 USEF

اما این موضوع برای جداول GTT از نوع on commit delete rows در اوراکل 12c حل شده و با اجرای بسیاری از پروسیجریهای این بسته(dbms_stat)، تراکنشهای قبلی commit نخواهند شد:

Connected to Oracle Database 12c Enterprise Edition Release 12.2.0.1.0

SQL> create global temporary table GTT_MYTBL(id number,name varchar2(14)) on commit delete rows;

Table created

SQL> insert into GTT_MYTBL values(1,’USEF’);

1 row inserted

SQL> exec dbms_stats.gather_table_stats(ownname => user,tabname => ‘GTT_MYTBL’);

PL/SQL procedure successfully completed

SQL> select * from GTT_MYTBL;

        ID NAME

———- ————–

         1 USEF

SQL> rollback;

Rollback complete

SQL> select * from GTT_MYTBL;

        ID NAME

———- ————–

SQL>

اسامی پروسیجرهای مربوط به بسته dbms_stat که در این زمینه پشتیبانی می شوند را در قسمت زیر می بینید:

GATHER_TABLE_STATS

DELETE_TABLE_STATS

DELETE_COLUMN_STATS

DELETE_INDEX_STATS

SET_TABLE_STATS

SET_COLUMN_STATS

SET_INDEX_STATS

GET_TABLE_STATS

GET_COLUMN_STATS

GET_INDEX_STATS