Tag Archives: sql tuning

Check SQL Query performance with different session parameters

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