How to Tune query that cannot modify ?

Query cannot be modified as it is hardcoded in an application or is generated by application code.

“Query Tuning issues can often be alleviated by modification of the query in question, either to add

hints or to change the query structure to avoid an issue. However in some cases the query cannot be

modified because it is hardcoded in an application or is generated by application code.

Oracle provides the option of changing the execution plan for running queries, without changing the

code itself. This feature also allows us to insulate our application execution plans against any

structural changes or upgrades. This feature is known as Stored Outlines and the idea is to store

the execution plan in Oracle provided tables, which will later be referred to by the optimizer to
execute the queries, thus providing optimizer plan stability.

Stored outlines are global and apply to all identical statements, regardless of the user firing it.
Under user SYS run $ORACLE_HOME/rdbms/admin/dbmsol.sql”

PARAMETERS TO BE SET
“CREATE_STORED_OUTLINES   Setting this to TRUE or an appropriate category name will automatically enable creating and storing
of outlines for every subsequent query fired. A DEFAULT category is used unless specified.

“”USE_STORED_OUTLINES This parameter enables the use of public stored outlines. Setting USE_STORED_OUTLINES to TRUE causes
the execution plan stored in the DEFAULT category to take effect. Set the parameter to a proper category name to explicitly use a different category.”
“QUERY_REWRITE_ENABLED allows you to enable or disable query rewriting globally for the database”
Example:
“EXAMPLE  1
select * from Joshi;
Execution Plan
———————————————————-
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=15 Bytes=852)
1    0   TABLE ACCESS (FULL) OF ‘Joshi’ (Cost=1 Card=15 Bytes=852)”
CREATE OUTLINES AND THEN UPDATE THE OL$HINTS VIEW AS FOLLOWING :
“CREATE OR REPLACE OUTLINE ORIGINALSQL ON
select * from Joshi;
CREATE OR REPLACE OUTLINE HINTSQL ON
select /*+ index(Joshi) */  * from Joshi;
UPDATE OUTLN.OL$HINTS
SET OL_NAME=DECODE(OL_NAME,’HINTSQL’,'ORIGINALSQL’,'ORIGINALSQL’,'HINTSQL’)
WHERE OL_NAME IN (‘ORIGINALSQL’,'HINTSQL’);
SET FOLLOWING PARAMETERS
“alter session set query_rewrite_enabled=true;
alter session set use_stored_outlines=true;”
NOTE THE INDEX SCAN WITHOUT ADDITION OF ANY HINT
select * from Joshi;
Query cannot be modified as it is hardcoded in an application or is generated by application code. “Query Tuning issues can often be alleviated by modification of the query in question, either to addhints or to change the query structure to avoid an issue. However in some cases the query cannot bemodified because it is hardcoded in an application or is generated by application code. Oracle provides the option of changing the execution plan for running queries, without changing thecode itself. This feature also allows us to insulate our application execution plans against anystructural changes or upgrades. This feature is known as Stored Outlines and the idea is to storethe execution plan in Oracle provided tables, which will later be referred to by the optimizer toexecute the queries, thus providing optimizer plan stability.Stored outlines are global and apply to all identical statements, regardless of the user firing it.

Under user SYS run $ORACLE_HOME/rdbms/admin/dbmsol.sql” PARAMETERS TO BE SET

“CREATE_STORED_OUTLINES   Setting this to TRUE or an appropriate category name will automatically enable creating and storingof outlines for every subsequent query fired. A DEFAULT category is used unless specified.

“”USE_STORED_OUTLINES This parameter enables the use of public stored outlines. Setting USE_STORED_OUTLINES to TRUE causesthe execution plan stored in the DEFAULT category to take effect. Set the parameter to a proper category name to explicitly use a different category.” “QUERY_REWRITE_ENABLED allows you to enable or disable query rewriting globally for the database”

Example:”EXAMPLE  1

select * from Joshi;

Execution Plan

———————————————————-

0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=15 Bytes=852)

1    0   TABLE ACCESS (FULL) OF ‘Joshi’ (Cost=1 Card=15 Bytes=852)”

CREATE OUTLINES AND THEN UPDATE THE OL$HINTS VIEW AS FOLLOWING : “CREATE OR REPLACE OUTLINE ORIGINALSQL ON select * from Joshi;

CREATE OR REPLACE OUTLINE HINTSQL ONselect /*+ index(Joshi) */  * from Joshi;

UPDATE OUTLN.OL$HINTS SET OL_NAME=DECODE(OL_NAME,’HINTSQL’,'ORIGINALSQL’,'ORIGINALSQL’,'HINTSQL’) WHERE OL_NAME IN (‘ORIGINALSQL’,'HINTSQL’); SET FOLLOWING PARAMETERS “alter session set query_rewrite_enabled=true;alter session set use_stored_outlines=true;”

NOTE THE INDEX SCAN WITHOUT ADDITION OF ANY HINT

select * from Joshi;