Sunday, May 13, 2018

OPTIMIZER MODE when first_rows leads to bad plans

Beware when setting optimizer_mode for application session, esp. if this session is used to execute different statements. 
I encounter this issue, which was made more elusive because bind variables are not used in sql text. So, no quick fix by sql profile or baseline. I am going to post only the plan two plan with timing information.
select OPTIMIZER,sql_id from DBA_HIST_SQL_PLAN where OPTIMIZER='FIRST_ROWS'; -- will indicate which sql_id using first_rows .


SYS@APPS_DB1> select SQL_ID,PLAN_HASH_VALUE,ELAPSED_TIME,CPU_TIME,PHYSICAL_READ_REQUESTS,DISK_READS from v$sql where PARSING_SCHEMA_NAME='APPS_SCHEMA' and sql_id in('9705zux4zfg3m','3zkfwn4c0dmfm');