OraCsdP: Optimizer

0] Miscellanous

0.1 Checklist to get same execution plan between 2 databases

Same objects structure ( index … )

Objects Statistics

Optimizer parameters including PGA settings

Systems statistics

0.2 How to get rid of - 'PLAN_TABLE' is old version

SQL> drop table plan_table ;

Table dropped.

SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql

Table created.

0.3 Modify Session Optimizer parameters

Using the Logon Trigger seen for Audit, we can add

-- B/ Outlines

execute immediate 'alter session set use_stored_outlines=TRUE';

-- C/ Set parameter

if ( user in ('SCH_ADMIN', 'SCH_BO','SCH_reference','SCH_PROC','SCH_CONFIG50005') ) then

execute immediate 'alter session set recyclebin = OFF';

end if;

-- D/ SQL Trace

--- if ( user in ('USR') ) then

--- execute immediate 'alter session set sql_trace=true';

--- end if;

-- E/ Compatible 9i

if ( user in ('USR1','USR2') ) then

EXECUTE IMMEDIATE 'alter session set "_OPTIMIZER_COST_BASED_TRANSFORMATION" = off';

EXECUTE IMMEDIATE 'alter session set optimizer_features_enable=''10.2.0.3''';

end if;

0.4 See which level of optimizer a session is in

select sid,NAME,VALUE from v$ses_optimizer_env

where ( name like '%optim%feat%' or name like '%optim%mode%' ) and sid in (select sid from v$session where username = 'SCH' and sid = 499 or sid = 494 )

SID NAME VALUE

494 optimizer_features_enable 10.2.0.3

494 optimizer_mode first_rows

499 optimizer_features_enable 10.2.0.5

499 optimizer_mode all_rows

0.5 How to Trace a session

Using the Logon trigger, assuming that SYSTEM gets the right to do it ( grant alter session to system ; ) :

-- E/ SQL Trace

if ( user in ('USR') ) then

execute immediate 'alter session set sql_trace=true';

end if;

CREATE OR REPLACE TRIGGER DB_LOGON_TRC

after logon on database

begin

if ( user in ('USR') ) then

execute immediate 'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 12'' ';

end if;

end;

Trace an existing Session :

sys.dbms_system.set_ev(user_sid, user_serial#, 10046, N, '');

EXECUTE SYS.DBMS_SYSTEM.SET_EV (118, 2940, 10046, 12, ''); -- -> enable tracing

EXECUTE SYS.DBMS_SYSTEM.SET_EV (118, 2940, 10046, 0, ''); -- -> disable tracing

select 'EXECUTE SYS.DBMS_SYSTEM.SET_EV('||SID||','||SERIAL#||',10046,12,'''') ;' from v$session where SID=11

For methods that require tracing levels the following are valid values.

0 - No trace. Like switching sql_trace off.

2 - The equivalent of regular sql_trace.

4 - The same as 2, but with the addition of bind variable values.

8 - The same as 2, but with the addition of wait events.

12 - The same as 2, but with both bind variable values and wait events.

TRACE w/ Bind Variables :

PARSING IN CURSOR #2 len=210 dep=0 uid=855 oct=2 lid=855 tim=3809739329401 hv=1326906613 ad='3460f990'

insert into APP.MESSAGES (BODY, CALL_DATE, CALLER, CREATION_DATE, IS_URGENT, MODIFICATION_DATE, SOURCE_AGENT_ID, SOURCE_LOGIN, SUBJECT, TARGET_AGENT_ID, ID) values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11)

END OF STMT

PARSE #2:c=0,e=61,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=3809739329399

BINDS #2:

kkscoacd

Bind#0

oacdty=01 mxl=32(15) mxlc=00 mal=00 scl=00 pre=00

oacflg=03 fl2=1000010 frm=01 csi=178 siz=248 off=0

kxsbbbfp=110ae2120 bln=32 avl=15 flg=05

value="body_2147483647"

-- find easily the trc files from spid information

select sysdate TS, s.username , s.logon_time , s.server , p.spid from v$session s , v$process p

where s.username = '<USER>' and s.paddr=p.addr order by logon_time desc

Caution : the SPID in case of Shared Server gives the Dispatcher’s PID.

0.6 plan_hash_value is changing with character set

Same code, different character set ( Oracle 10 and 11 )

set pages 5000 lines 150

set timing on ;

set autotrace traceonly ;

select /*+ INDEX_FFS (a, PK_TABLE1) */ count(1) from ADB.TABLE1 a ;

exit

UTF-8 11G Plan hash value: 136383568

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

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

| 0 | SELECT STATEMENT | | 1 | 747 (2)| 00:00:09 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | INDEX FAST FULL SCAN| PK_TABLE1 | 690K| 747 (2)| 00:00:09 |

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

WE8MSWIN1252 11G Plan hash value: 1689933652

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

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

| 0 | SELECT STATEMENT | | 1 | 759 (3)| 00:00:10 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | INDEX FAST FULL SCAN| PK_TABLE1 | 690K| 759 (3)| 00:00:10 |

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

WE8MSWIN1252 10G Plan hash value: 1689933652

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

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

| 0 | SELECT STATEMENT | | 1 | 412 (2)| 00:00:05 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | INDEX FAST FULL SCAN| PK_TABLE1 | 690K| 412 (2)| 00:00:05 |

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

1] V$SQL : in-memory SQL

1.0 Get Top SQL from V$SQL

select sql_id , plan_hash_value , parsing_schema_name , executions , buffer_gets , disk_reads , first_load_time , rows_processed , elapsed_time , last_load_time , sql_text from v$sql

where buffer_gets > 10000

order by buffer_gets/executions desc

select sql_id , plan_hash_value , parsing_schema_name , executions , buffer_gets , disk_reads , first_load_time , rows_processed , elapsed_time , last_load_time , sql_text from v$sql

where upper(sql_text) like '%SELECT%TRUNC(TABLE1.HEURE)%'

order by buffer_gets/executions desc

select sql_id , plan_hash_value , parsing_schema_name , executions , buffer_gets , disk_reads , first_load_time , rows_processed , elapsed_time/1000000 Sec , last_load_time , sql_text from v$sql

where sql_id = '5865vav66abys'

1.1 Get SQL text from SID

-- how to get sql from session SID in sqlarea memory

select a.sid, a.serial#, b.sql_text from v$session a, v$sqlarea b where a.sql_address=b.address and a.sid = 1260;

select a.sid, a.serial#, b.sql_id , b.sql_text from v$session a, v$sqlarea b where a.sql_address=b.address and a.username = '50004'

select a.sid, a.serial# , b.sql_id , b.plan_hash_value , b.sql_text from v$session a, v$sqlarea b where a.sql_address=b.address and a.sid = 988 -- cjadkfgvtkm52 / 2199050946

1.2 Get Explain Plan, using DBMS_XPLAN.display_cursor

select sid , a.sql_id , child_number from v$session a , v$sql b where a.sql_id=b.sql_id and a.sid = '1744' ;

SELECT * FROM TABLE (DBMS_XPLAN.display_cursor ('&sql_id', '&child_number', 'ADVANCED'));

1.3 Get Bind Variables, using $SQL_BIND_CAPTURE & SQL_ID

SELECT sql_id, child_number, name,

position, datatype_string,

value_string, last_captured

FROM V$SQL_BIND_CAPTURE

WHERE sql_id = 'fhnjysdwnr9rf'

ORDER BY sql_id, child_number, name ;

2] Query Tuning : when having the code

2.0 Run SQL with assigned bind variables, using SQLPLUS

set pages 5000 lines 150

set timing on ;

var Date_ref_Debut varchar2(15) ;

var Date_ref_Fin varchar2(15) ;

exec :Date_ref_Debut:='31/05/2012' ;

exec :Date_ref_Fin:='31/05/2012' ;

2.1 Get Real Execution Plan & Statistics

Add the following lines at the beginning of SQL script, to execute the query, without printing returned rows

set pages 5000 lines 150

set timing on ;

set autotrace traceonly ;

Run it – complete Log

$ sqlplus <con_string> @<sonde>.sql | tee <sonde>.log

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Jun 9 14:18:42 2011

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

10000 rows selected.

Elapsed: 00:00:08.39

Execution Plan

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

Plan hash value: 1751135198

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

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

| 0 | SELECT STATEMENT | | 927 | 58401 | 354 (5)| 00:00:05 |

| 1 | WINDOW BUFFER | | 927 | 58401 | 354 (5)| 00:00:05 |

|* 2 | COUNT STOPKEY | | | | | |

|* 3 | FILTER | | | | | |

| 4 | TABLE ACCESS BY INDEX ROWID | GROUPES_SYSTEMES_V2 | 13460 | 828K| 354 (5)| 00:00:05 |

| 5 | INDEX FULL SCAN | GROUPES_SYSTEMES_V2_PK | 13460 | | 77 (2)| 00:00:01 |

| 6 | NESTED LOOPS | | 1 | 83 | 6 (0)| 00:00:01 |

| 7 | NESTED LOOPS | | 1 | 60 | 4 (0)| 00:00:01 |

|* 8 | INDEX RANGE SCAN | GROUPES_COMPTES_V2_I3 | 1 | 46 | 3 (0)| 00:00:01 |

| 9 | TABLE ACCESS BY INDEX ROWID| UTILISATEUR | 1 | 14 | 1 (0)| 00:00:01 |

|* 10 | INDEX UNIQUE SCAN | UTILISATEUR_UK_I2U | 1 | | 0 (0)| 00:00:01 |

| 11 | INLIST ITERATOR | | | | | |

|* 12 | INDEX UNIQUE SCAN | UK_FONCTIONS_METIERS_PAR_UO | 1 | 23 | 2 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - filter(ROWNUM<=10000)

3 - filter("<SCHEMA>I2U_FIND_PROFIL"('CCERVANTES')='ADMIN_SYST' OR EXISTS (SELECT 0 FROM

"FONCTIONS_METIERS_PAR_UO" "FONCTIONS_METIERS_PAR_UO","UTILISATEUR" "UTILISATEUR","GROUPES_COMPTES_V2"

"G2" WHERE "G2"."GROUPE"=:B1 AND "G2"."CONTAINER"=:B2 AND "G2"."ARBRE"=:B3 AND "G2"."SYSTEME"=:B4 AND

"UTILISATEUR"."LOGIN_NAME_UNIQUE"="G2"."I2U" AND "FONCTIONS_METIERS_PAR_UO"."LOGIN_NAME_UNIQUE"='CCERVAN

TES' AND ("FONCTIONS_METIERS_PAR_UO"."COD_FONCTION"='CORR_INFO' OR

"FONCTIONS_METIERS_PAR_UO"."COD_FONCTION"='CORR_INFO_ADJ') AND

"UTILISATEUR"."UO_ID"="FONCTIONS_METIERS_PAR_UO"."UO_ID") OR

"<SCHEMA>I2U_FIND_PROFIL"('CCERVANTES')='ADMIN_I2U')

8 - access("G2"."SYSTEME"=:B1 AND "G2"."ARBRE"=:B2 AND "G2"."CONTAINER"=:B3 AND "G2"."GROUPE"=:B4)

10 - access("UTILISATEUR"."LOGIN_NAME_UNIQUE"="G2"."I2U")

12 - access("UTILISATEUR"."UO_ID"="FONCTIONS_METIERS_PAR_UO"."UO_ID" AND

("FONCTIONS_METIERS_PAR_UO"."COD_FONCTION"='CORR_INFO' OR

"FONCTIONS_METIERS_PAR_UO"."COD_FONCTION"='CORR_INFO_ADJ') AND

"FONCTIONS_METIERS_PAR_UO"."LOGIN_NAME_UNIQUE"='CCERVANTES')

Statistics

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

20001 recursive calls

0 db block gets

1893729 consistent gets

0 physical reads

0 redo size

606561 bytes sent via SQL*Net to client

7889 bytes received via SQL*Net from client

668 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

10000 rows processed

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Run it and filter results to get SQL Response Time

Create a bench Query to get daily performance Database Performance All subsystems ( Server , Storage )

Get Real DB Blocks Read as follow :

$ time sqlplus system@<db_name>d @hr_bo_sonde_1.sql | egrep -i "consistent gets|physical reads"

1128153 consistent gets

210337 physical reads

real 1m12.232s

Note : not possible to get block stats on a Standby DB Open in READ-ONLY mode.

$ time sqlplus hr_bo@hr_bo_sonde_1_RO.sql > /dev/null

real 30m16.299s

2.2 Get detailed Explain Plan, without running the Query

explain plan for

SELECT mes.id_egst

FROM gen.mes, gen.asm, gen.cod_mes

WHERE mes.cd_codmes_mes = cod_mes.cd_cod_mes

AND RTRIM (cod_mes.cd_catmes_cod_mes) = :kslvar16_

AND mes.in_refagr_mes = 'R'

AND (spe5.pkg_gen__sel_data_asm.f_get_dt_fin_agr (mes.id_perben_mes,

DECODE (:kslvar16_,

'AGRANP', 'N',

'P'

)

) > TO_DATE (:kslvar15_, 'DD/MM/YYYY')

AND (spe5.pkg_gen__sel_data_asm.f_get_dt_fin_agr (mes.id_perben_mes,

DECODE (:kslvar16_,

'AGRANP', 'N',

'P'

)

) <

TO_DATE (:kslvar14_, 'DD/MM/YYYY')

)

AND mes.id_perben_mes = asm.id_egst

AND ( mes.id_egst = asm.id_der_mes_p_dec_asm

OR mes.id_egst = asm.id_der_mes_np_dec_asm

)

ORDER BY 1

select * from table(dbms_xplan.display('PLAN_TABLE',null,'ADVANCED')) ;

PLAN_TABLE_OUTPUT

49 - filter("AAS"."AAS_DAT_FIN" IS NULL AND "AAS"."AAS_ACT_NUM_INT">=0 AND

"AAS"."AAS_ACT_NUM_INT"<=9999999)

50 - filter("DOS"."DOS_DAT_CLO" IS NULL AND "DOS"."DOS_DAT_ARCHIVE" IS NULL)

3] Index

3.1 Big Tables without any Index

select owner , table_name , num_rows from dba_tables a where a.owner = 'BI_APP_DTM'

and num_rows > 1000000

and not exists ( select '1' from dba_indexes b where a.table_name = b.table_name )

order by 3 desc

4] The effect of Version or Character Set on Full Scan

4.1 Full scan are more efficient in 11G than 10G

Same code, same machine, but 11G does Direct Read ( bypasses buffer cache )

set pages 5000 lines 150

set timing on ;

set autotrace traceonly ;

select /*+ FULL (a) */ count(1) from ADB.TABLE1 a ;

exit

WE8MSWIN1252 10G Elapsed: 00:00:06.57

Plan hash value: 2121393994

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

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

| 0 | SELECT STATEMENT | | 1 | 16943 (1)| 00:03:24 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| TABLE1 | 690K| 16943 (1)| 00:03:24 |

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

62081 consistent gets

62028 physical reads

WE8MSWIN1252 11G Elapsed: 00:00:04.72

Plan hash value: 2121393994

62041 consistent gets

62028 physical reads

4.2 Full scan read more blocks in Unicode

Same code, same machine, but UTF-8 response time * 1.8 vs WE8MSWIN1252

UTF-8 11G Elapsed: 00:00:11.98

Plan hash value: 3761518470

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

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

| 0 | SELECT STATEMENT | | 1 | 27681 (1)| 00:05:33 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| TABLE1 | 690K| 27681 (1)| 00:05:33 |

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

101574 consistent gets

101555 physical reads

5] The 11G+ Result Cache

Good when there are few rows returned, frequent executions and the source tables aren’t modify too often.

I’m using it in a View.

5.1 Setup

$ sqlplus / as sysdba

SQL> sho parameter result

NAME TYPE VALUE

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

client_result_cache_lag big integer 3000

client_result_cache_size big integer 0

result_cache_max_result integer 5

result_cache_max_size big integer 13088K

result_cache_mode string MANUAL

result_cache_remote_expiration integer 0

SQL>

SQL> alter system set result_cache_max_size = 64M ;

System altered.

Modify the view : it is unnecessary to add the hint in the sub-query ( won’t be cached ).

CREATE or replace VIEW OWNER.V_MY_VIEW

(NUMEQUIPEMENT, NOM, ID_SOCIETE, TYPESESSION)

SELECT /*+ result_cache */code_equipement,

NULL AS nom,

id_societe,

typesession

FROM t_tab01

WHERE (

t_tab01.typesession = 10 AND

NOT EXISTS

(SELECT *

FROM T_TAB02

WHERE id_typeequipement IN (10,20)

AND T_TAB02.numequipement = T_TAB01.code_equipement

AND t_tab02.id_societe =T_TAB01.id_societe

)

5.2 Check that the Result Cache is working

explain plan for

select count(1) from OWNER.V_MY_VIEW -- 79330

select * from table(dbms_xplan.display('PLAN_TABLE',null,'ADVANCED')) ;

PLAN_TABLE_OUTPUT

Plan hash value: 830699133

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

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

| 0 | SELECT STATEMENT | | 1 | | 18 (6)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | | | |

| 2 | VIEW | V_MY_VIEW | 174 | | 18 (6)| 00:00:01 |

| 3 | RESULT CACHE | fs0pw9ht8j22k9x7n21xdub6bn | | | | |

|* 4 | HASH JOIN ANTI | | 174 | 4002 | 18 (6)| 00:00:01 |

| 5 | TABLE ACCESS BY INDEX ROWID | T_TAB01 | 220 | 2420 | 13 (0)| 00:00:01 |

|* 6 | INDEX RANGE SCAN | I_T_TAB01_TYPESESSION | 220 | | 3 (0)| 00:00:01 |

| 7 | INLIST ITERATOR | | | | | |

| 8 | TABLE ACCESS BY INDEX ROWID| T_TAB02 | 42 | 504 | 4 (0)| 00:00:01 |

|* 9 | INDEX RANGE SCAN | I_ID_TYPEEQUIPEMENT | 42 | | 2 (0)| 00:00:01 |

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

5.3 Get the statistics about the Result Cache

select * from v$result_cache_statistics ;

215 Create Count Success Number of cache results successfully created

0 Create Count Failure Number of cache results that failed to create

5227 Find Count Number of cached results that were successfully found

187 Invalidation Count Total number of invalidations

205 Delete Count Invalid Number of invalid cached results deleted

0 Delete Count Valid Number of valid cached results deleted

select * from v$result_cache_objects order by creation_timestamp ;

select type , status , substr(name,1,15) , namespace , creation_timestamp from v$result_cache_objects order by creation_timestamp ;

TYPE	STATUS	SUBSTR(NAME,1,15)	NAMESPACE	CREATION_TIMESTAMP
Dependency	Published	OWNER.T_TAB02		12/12/2014 15:10
Dependency	Published	OWNER.T_TAB01		15/12/2014 14:16
Result	Invalid	SELECT /*+ resu	SQL	18/12/2014 10:48
Result	Published	SELECT /*+ resu	SQL	18/12/2014 16:25

Status

Invalid : dependent tables were modified, the result gets Invalid, will be deleted from the result cache ; the results from the next execution will be cached

New : is beeing executed

Published : result is good to be used

Others

select * from v$result_cache_memory

select * from v$result_cache_dependency

Real world : elapsed time

2127 rows selected.

Elapsed: 00:00:00.19 => result cache used

2127 rows selected.

Elapsed: 00:00:00.23 => result cache used

2127 rows selected.

Elapsed: 00:00:00.20 => result cache used

2127 rows selected.

Elapsed: 00:02:03.14 => result cache not used, tables were modified

2127 rows selected.

Elapsed: 00:00:00.17 => result cache used

2127 rows selected.

Elapsed: 00:00:00.16 => result cache used

OraCsdP

Categories

jeudi 28 juin 2012

Optimizer