SQL PLAN & SQL PLAN FIX

SQL> explain plan for
2 select * from hr.employees;

Explained.

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

PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 1445457117

——————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————-
| 0 | SELECT STATEMENT | | 107 | 7704 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMPLOYEES | 107 | 7704 | 3 (0)| 00:00:01 |
——————————————————————————-

8 rows selected.

veya

SQL> set autotrace traceonly explain
SQL> select * from hr.employees;

Execution Plan
———————————————————-
Plan hash value: 1445457117

——————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————-
| 0 | SELECT STATEMENT | | 107 | 7704 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMPLOYEES | 107 | 7704 | 3 (0)| 00:00:01 |
——————————————————————————-

ile sql plan sqlplus üzerinden alınabilir burada sadece sqlplus üzerinden sqlplan nasıl alınır onu söylemiş olduk, sql plan nasıl yorumlanır dersek, sql tuning konusunda bunu göreceğiz.

sql_id sinden sql plan çıkarma

dbms_xplan.display_cursor(sql_id=>’gnjmz1udfd9cg’) ifadesini  kullan ır isek şu an optimizer ın bu sql için seçeceği sql planı görürüz

SQL> select * from table (dbms_xplan.display_cursor(sql_id=>'gnjmz1udfd9cg'));
SQL_ID  gnjmz1udfd9cg, child number 0
-------------------------------------
UPDATE TBL_ALARM_STATUS SET END_DATE = :B2 WHERE INVERTER_ID = :B1 AND
ERROR_NUMBER='0007' AND END_DATE IS NULL

Plan hash value: 1014706028

---------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                     |                  |       |       |   104 (100)|          |
|   1 |  UPDATE                              | TBL_ALARM_STATUS |       |       |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| TBL_ALARM_STATUS |     1 |    21 |   104   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | TBL_ALRM_STATS   |   284 |       |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("INVERTER_ID"=:B1)
   3 - access("ERROR_NUMBER"=U'0007' AND "END_DATE" IS NULL)


22 rows selected.

dbms_xplan_cursor_awr ile aynı komutu çalıştırdığımızda en son awr snapshot içerisndeki sql planı görürüz.

SQL> select * from table (dbms_xplan.display_awr('gnjmz1udfd9cg'));
SQL_ID gnjmz1udfd9cg
--------------------
UPDATE TBL_ALARM_STATUS SET END_DATE = :B2 WHERE INVERTER_ID = :B1 AND
ERROR_NUMBER='0007' AND END_DATE IS NULL

Plan hash value: 2256970134

---------------------------------------------------------------------------------------
| Id  | Operation          | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |                  |       |       |  2781 (100)|          |
|   1 |  UPDATE            | TBL_ALARM_STATUS |       |       |            |          |
|   2 |   TABLE ACCESS FULL| TBL_ALARM_STATUS |     1 |    21 |  2781   (1)| 00:00:01 |
---------------------------------------------------------------------------------------


15 rows selected.

Burada iki faklı sql plan var bakalım gerçekten v$active_session_history ve dba_hist_active_sess_history view larında bu sql_id için farklı planlar var mı?

SQL> SELECT distinct SQL_PLAN_HASH_VALUE FROM DBA_HIST_ACTIVE_SESS_HISTORY WHERE SQL_ID='gnjmz1udfd9cg';
         1014706028
         2256970134
                  0


SQL> select distinct SQL_PLAN_HASH_VALUE from v$active_session_history where sql_id='gnjmz1udfd9cg';
         1014706028
         2256970134
                  0

iki adet sql planımız var peki 1. Plan 225 ile başlayan sonraki plan ise 101 ile başlayan planlardır. Biz planın değişmesini istemediğimiz halde optimizer tarafından bazen farklı bir plan devreye alınabiliyor, bu durumda sql’i eski plana fix yapmayı isteyebiliriz yapmalıyız?

SQL PLAN FIX

SQL>    select sql_plan_hash_value,sql_id from dba_hist_active_sess_history
   where sql_id='gnjmz1udfd9cg' group by sql_plan_hash_value,sql_id;  2

SQL_PLAN_HASH_VALUE SQL_ID
------------------- -------------
         1014706028 gnjmz1udfd9cg
         2256970134 gnjmz1udfd9cg
                  0 gnjmz1udfd9cg

Planı yükleyelim

declare
    lv_plan number;
begin
    lv_plan:=dbms_spm.load_plans_from_cursor_cache  
        (sql_id=>'gnjmz1udfd9cg',
        plan_hash_value => '2256970134',
        fixed =>'NO');
end;

Planı yükledikten sonra dba_sql_baselines view ından sql_handle ve plan_name bilgilerini alalım, bu bilgiler ile de istediğimiz sql planın fix işlemini gerçekleştirelim;

SQL> select sql_handle, plan_name,fixed from dba_sql_plan_baselines where origin like '%MANUAL%';

SQL_HANDLE                     PLAN_NAME                                FIX
------------------------------ ---------------------------------------- ---
SQL_4b1050e80764fe67           SQL_PLAN_4q42hx03q9zm72613255d           NO

Şimdi Planı fixleyelim

declare
 lv_plan_fx number;
begin
 lv_plan_fx:=DBMS_SPM.alter_sql_plan_baseline(
            sql_handle=>'SQL_4b1050e80764fe67',
            plan_name => 'SQL_PLAN_4q42hx03q9zm72613255d',
            attribute_name => 'fixed',
            attribute_value => 'YES');
end;

Kontrol edelim;

SQL> select sql_handle, plan_name,fixed from dba_sql_plan_baselines where origin like '%MANUAL%';

SQL_HANDLE                     PLAN_NAME                                FIX
------------------------------ ---------------------------------------- ---
SQL_4b1050e80764fe67           SQL_PLAN_4q42hx03q9zm72613255d           YES

Drop Profile Plan

DECLARE lv_plans_dropped PLS_INTEGER; 
BEGIN 
lv_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (sql_handle => 'SQL_4b1050e80764fe67',plan_name => 'SQL_PLAN_4q42hx03q9zm72613255d'); DBMS_OUTPUT.put_line('Plans Dropped: ' || lv_plans_dropped); 
END; 
/

SQL Plan Değiştimi

-- SQL PLAN DEGISTI MI?
WITH
p AS (
SELECT plan_hash_value
  FROM gv$sql_plan
 WHERE sql_id = TRIM('&&sql_id')
   AND other_xml IS NOT NULL
 UNION
SELECT plan_hash_value
  FROM dba_hist_sql_plan
 WHERE sql_id = TRIM('&&sql_id.')
   AND other_xml IS NOT NULL ),
m AS (
SELECT plan_hash_value,
       SUM(elapsed_time)/SUM(executions) avg_et_secs
  FROM gv$sql
 WHERE sql_id = TRIM('&&sql_id')
   AND executions > 0
 GROUP BY
       plan_hash_value ),
a AS (
SELECT plan_hash_value,
       SUM(elapsed_time_total)/SUM(executions_total) avg_et_secs
  FROM dba_hist_sqlstat
 WHERE sql_id = TRIM('&&sql_id')
   AND executions_total > 0
 GROUP BY
       plan_hash_value )
SELECT p.plan_hash_value,
       ROUND(NVL(m.avg_et_secs, a.avg_et_secs)/1e6, 3) avg_et_secs
  FROM p, m, a
 WHERE p.plan_hash_value = m.plan_hash_value(+)
   AND p.plan_hash_value = a.plan_hash_value(+)
 ORDER BY
       avg_et_secs NULLS LAST;