Check SQL Query performance with different session parameters
The Step third will test the SQL queries with all instance or session parameter for testing the SQL queries in one go. which will let you know which parameter changes will help SQL queries to run better in your environment.
1. Save the SQL Query in Q.SQL file
2. Login with user of the schema from which SQL query object exists
SQLPLUS username/password
3. Test the SQL Query with setting different parameter and options set at session level
spool C:\SQL_QUERY_TESTING.log
set linesize 260 pagesize 999 timing on
PROMPT ---Simple Execution plan----
start q.sql
select * from table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'advanced ALLSTATS LAST'));
conn username/password
PROMPT ---11204 optimizer features enabled ------
alter session set optimizer_features_enable = "11.2.0.4";
start q.sql
select * from table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'advanced ALLSTATS LAST'));
conn username/password
PROMPT ---- FALSE optimizer_adaptive_plans ------
alter session set optimizer_adaptive_plans= FALSE;
start q.sql
select * from table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'advanced ALLSTATS LAST'));
conn username/password
PROMPT ---- FALSE optimizer_adaptive_statistics------
alter session set optimizer_adaptive_statistics= FALSE;
start q.sql
select * from table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'advanced ALLSTATS LAST'));
conn username/password
PROMPT ---- ZERO optimizer_dynamic_sampling ------
ALTER SESSION SET optimizer_dynamic_sampling = 0;
start q.sql
conn username/password
PROMPT ---- TWO optimizer_dynamic_sampling ------
ALTER SESSION SET optimizer_dynamic_sampling = 2;
start q.sql
conn username/password
PROMPT ---- FOUR optimizer_dynamic_sampling ------
ALTER SESSION SET optimizer_dynamic_sampling = 4;
start q.sql
conn username/password
PROMPT ---- SIX optimizer_dynamic_sampling ------
ALTER SESSION SET optimizer_dynamic_sampling = 6;
start q.sql
conn username/password
PROMPT ---- Eight optimizer_dynamic_sampling ------
ALTER SESSION SET optimizer_dynamic_sampling = 8;
~~ run bad query
conn username/password
PROMPT ---- TEN optimizer_dynamic_sampling ------
ALTER SESSION SET optimizer_dynamic_sampling = 10;
start q.sql
conn username/password
PROMPT ----920 optimizer_features_enable ------
ALTER SESSION SET optimizer_features_enable = '9.2.0';
start q.sql
conn username/password
PROMPT ---- 4096 db_file_multiblock_read_count------
ALTER SESSION SET db_file_multiblock_read_count=4096;
start q.sql
conn username/password
PROMPT ---- 32768 db_file_multiblock_read_count------
alter session set db_file_multiblock_read_count = 32768;
start q.sql
conn username/password
PROMPT ---- 50 optimizer_index_cost_adj ------
alter session set optimizer_index_cost_adj = 500;
start q.sql
conn username/password
PROMPT ---- 100 optimizer_index_caching ------
alter session set optimizer_index_caching = 100;
start q.sql
conn username/password
PROMPT ---- 80 optimizer_index_caching ------
alter session set optimizer_index_caching = 80;
start q.sql
conn username/password
PROMPT ---- 25 optimizer_index_cost_adj ------
alter session set optimizer_index_cost_adj = 25;
start q.sql
spool off
