Menu

21 Ocak 2013 Pazartesi

Oracle Cursor Sharing`e bakis

Cursor Sharing
       Oracle 9i versiyonundan sonra, benzer SQL ifadeleri icin ayni execution plani olusturmaya baslamistir. Sadece literal degeri degisen sorgular icin ilk calisan sorguda olusturulan execution plan diger sorgular icinde kullanilmaya baslanmis ve her sorgu icin yeni execution plan olusturulmamis oldu. Simdi asagidaki iki sorguya bakalim.
                SQL> SELECT * FROM emps WHERE dep_id = 100;
                SQL> SELECT * FROM emps WHERE dep_id = 20;
         Yukaridaki iki sorgumuzda, ikinci sorguyu birinci sorguya bagli olarak, nasil bir execution plan olusturulmus ise ikinci sorgudada ayni execution plan devam edecektir.(Bu Full table scan olabilcegi gibi INDEX RANGE SCAN da olabilir)

Sorgularınızı yazarken dikkat etmeniz gereken konu sorgunun genel yapı olarak sürekli değişmiyor olması. Yaptığınız her en ufak değişiklik Oracle tarafından farklı bir sorgu olarak algılanır ve eğer bir de içeride bind variable yerine literal (numara veya karakter) kullanıyorsanız ve cursor_sharing parametreniz SIMILAR’da kalmış ise arka tarafda cok iyi seyler olmuyor diyebiliriz.

- CURSOR_SHARING bir oracle initilization parametresidir ve Shared pool içerisinde tutulan sqllerin benzerleri geldiğinde aynı plan ile diğerlerininde çalışıp çalışmayacağının set edildiği parametredir (literal(numara veya karakter) kullanımını görüp bunun birer bind variable olarak plan’larının saklanmasını sağlar)
Cursor sharing parametresinin degerleri nelerdir ve nasil degistirebilirim.
CURSOR_SHARING paremetresinin alabilecegi degerler
-  FORCE
-  EXACT(default (varsayilan degerdir))
-  SIMILAR
CURSOR_SHARING parametresini nasil degistirebilirim.
- ALTER SYSTEM CURSOR_SHARING = ?
- ALTER SESSION CURSOR_SHARING = ?
- Initializtaion parameter dosyasi icinden degistirebiliriz

Simdi CURSOR_SHARING parametresinin alabilecegi, FORCE, EXACT, ve SIMILAR degerlerinin ne yaptiklarina bakalim.
EXACT : Çalışan sqlerin cache’ den çalışması için sorguların birebir aynısı olması gerekmektedir.
FORCE : Cache de Çalışan sql lerin benzeri varsa mevcut execeution plan kullanılmasını zorlar.(Yani yazilan, ve benzer butun sql`ler icin ilk olusutural execution planlarin ortak kullanilmasi)
SIMILAR : sql’ lerin birebir aynı olmasa da benzeyenler için (execution planlarına da bakar) cache den çalıştırmaya yönlendirir.

Simdi asagidaki uc SQL` e bakalim ve bu uc sql`in ayni execution plani kullandigini gorecegiz. Oncelikle CURSOR_SHARING parametresini FORCE olarak set edelim.
SQL> alter session set cursor_sharing = FORCE;
SQL> SELECT * FROM emps WHERE salary > 10000;
SQL> SELECT * FROM emps WHERE salary > 15000;
SQL> SELECT * FROM emps WHERE salary > 9000;

Birinci SQL`imiz calsitikdan sonra Oracle bu SQL`e shared pool`da asagidaki gibi bir bind-variable generate ediyor ve bu SQL icin execution plan olusturuyor.

SELECT * FROM emps WHERE salary > :"SYS_B_0";
Daha sonra diger iki SQL calsitiginda bind-variable generate ettigi ve exectuion plani olusuturdugu plani calistiriyor.

Yukaridaki ornekten sunu cikarabiliriz. CURSOR_SHARING parametresi “auto binder”, yani biz sql`imizde bind_variable kullanmiyorsak Oracle bu parametre sayesinde bizim yerimize  otomatik bind variable yapiyor.
Simdi bu degerleri veritabanizda degistirerek sonuclara bakalim.

Labs :
SQL> alter system set cursor_sharing='SIMILAR';
System altered.
SQL> select /*TEST_SIMILAR*/  count(*) from system.myobjects where owner='SYS';
SQL> select /*TEST_SIMILAR*/  count(*) from system.myobjects where owner='SYSTEM';
SQL> select /*TEST_SIMILAR*/  count(*) from system.myobjects where owner='SCOTT';

SQL>  select sql_id,sql_text from v$sql where sql_text like 'select /*TEST_SIMILAR*/%';
SQL_ID               SQL_TEXT
7qsnvbzwh79tj  select /*TEST_SIMILAR*/  count(*) from system.myobjects where owner=:"SYS_B_0"
SQL> select sql_id,child_number,EXECUTIONS,LOADS,PARSE_CALLS from v$sql where sql_text like 'select /*TEST_SIMILAR*/%';
SQL_ID        CHILD_NUMBER EXECUTIONS      LOADS PARSE_CALLS
------------- ------------ ---------- ---------- -----------
7qsnvbzwh79tj            0          3          1           3

Goruldugu gibi, bir tane SQL`imi var ve otomatik olarak bind variable atanmis :SYS_B_0 ve 3 defa cagirilmis.

Simdi “v$sqlarea”`da kactane sqlmiz var bakalim.
SQL> select version_count from v$sqlarea where sql_id='7qsnvbzwh79tj';
VERSION_COUNT
-------------
            1

Ayni durumu LIKE kullanarak yapalim ve durumun ne kadar degistigini ve ortaya ayni sqlidye  uc tane SQL ciktigini gorecegiz.

SQL> select /*TEST_SIMILAR*/  distinct owner from myobjects where owner like 'A%';
SQL> select /*TEST_SIMILAR*/  distinct owner from myobjects where owner like 'B%';
SQL> select /*TEST_SIMILAR*/  distinct owner from myobjects where owner like 'C%';

SQL> select sql_id,child_number,EXECUTIONS,LOADS,PARSE_CALLS from v$sql where sql_text like 'select /*TEST_SIMILAR*/%';
SQL_ID        CHILD_NUMBER EXECUTIONS      LOADS PARSE_CALLS
------------- ------------ ---------- ---------- -----------
6hkkxzpas5hyq            0          1          1           1
6hkkxzpas5hyq            1          1          1           1
6hkkxzpas5hyq            2          1          1           1
SQL> select version_count from v$sqlarea where sql_id='6hkkxzpas5hyq';
VERSION_COUNT
-------------
3

Simdi ayni ornegi CURSOR_SHARING parametresini FORCE olarak set edip test edelim.

SQL> alter system set cursor_sharing='FORCE';
            System altered.
SQL> select /*FORCE*/  distinct owner from myobjects where owner like 'A%';
SQL>  select /* FORCE */  distinct owner from myobjects where owner like 'B%';
SQL> select /* FORCE */  distinct owner from myobjects where owner like 'C%';
SQL> select version_count from v$sqlarea where sql_id='6c4f9xwp19pff';
VERSION_COUNT
     1
SQL>  select sql_id,child_number,EXECUTIONS,LOADS,PARSE_CALLS from v$sql where sql_text like 'select /*FORCE*/%';
SQL_ID        CHILD_NUMBER EXECUTIONS      LOADS PARSE_CALLS
6c4f9xwp19pff            0          3          1           3
SQL> select /*TEST_SIMILAR*/  distinct owner from myobjects where object_type='A';
SQL> select /*TEST_SIMILAR*/  distinct owner from myobjects where object_type='B';
SQL> select /*TEST_SIMILAR*/  distinct owner from myobjects where object_type='C';
SQL> select /*TEST_SIMILAR*/  distinct owner from myobjects where object_type='D';
SQL> select /*TEST_SIMILAR*/  distinct owner from myobjects where object_type='E';
SQL> select sql_id,child_number,EXECUTIONS,LOADS,PARSE_CALLS from v$sql where sql_text like 'select /*TEST_SIMILAR*/%';
SQL_ID        CHILD_NUMBER EXECUTIONS      LOADS PARSE_CALLS
------------- ------------ ---------- ---------- -----------
2m80tr9fhhbwn            0          1          1           1
2m80tr9fhhbwn            1          1          1           1
2m80tr9fhhbwn            2          1          1           1
2m80tr9fhhbwn            3          1          1           1
2m80tr9fhhbwn            4          1          1           1
SQL>  select version_count from v$sqlarea where sql_id='2m80tr9fhhbwn';
VERSION_COUNT
             5
SQL> select hash_value,address from v$sqlarea where sql_text like 'select /*TEST_SIMILAR*/%';
HASH_VALUE ADDRESS
1560817556 0000000095B58FD8
SQL> select * from v$sql_shared_cursor where address='0000000095B58FD8';
SQL_ID        ADDRESS          CHILD_ADDRESS    CHILD_NUMBER U S O O S L F E B P I S T A B D L T B I I R L I O E M U T N F A I T D L D B P C S C P T M B M R O P M F L P L A F L R L H P B
2m80tr9fhhbwn 0000000095B58FD8 000000009160F6B8            0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
2m80tr9fhhbwn 0000000095B58FD8 0000000091633E68            1 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N
2m80tr9fhhbwn 0000000095B58FD8 0000000091741E10            2 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N
2m80tr9fhhbwn 0000000095B58FD8 000000008DA39108            3 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N
2m80tr9fhhbwn 0000000095B58FD8 00000000947D5B30            4 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N

3 yorum: