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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.