What could be the reason for this?

You used SQL Tuning Advisor for a long-running SQL statement that suggested a SQL profile
which can be used by the query subsequently for a better execution plan. After implementing the
profile, you altered the profile to include it in a category as follows:
SQL> EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE ( name=>
‘SYS_SQLPROF_0146ced728214000, attribute_name => ‘CATEGORY’, value => ‘DEV2’).
But the performance has not improved for the user session. The profile is enabled and on
investigating the query plan, you find that the query from the user session does not use the SQL
profile. View the Exhibit and examine the parameters for the user session. What could be the
reason for this?

You used SQL Tuning Advisor for a long-running SQL statement that suggested a SQL profile
which can be used by the query subsequently for a better execution plan. After implementing the
profile, you altered the profile to include it in a category as follows:
SQL> EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE ( name=>
‘SYS_SQLPROF_0146ced728214000, attribute_name => ‘CATEGORY’, value => ‘DEV2’).
But the performance has not improved for the user session. The profile is enabled and on
investigating the query plan, you find that the query from the user session does not use the SQL
profile. View the Exhibit and examine the parameters for the user session. What could be the
reason for this?

A.
The CONTROL_MANAGEMENT_PACK_ACCESS parameter is not set to DEV2.

B.
The OPTIMIZER_MODE parameter is set to ALL_ROWS for the user session.

C.
The SQLTUNE_CATEGORY parameter is set to DEFAULT for the user session.

D.
The OPTIMIZER_USE_PLAN_BASELINES parameter is set to TRUE for the user session.



Leave a Reply 1

Your email address will not be published. Required fields are marked *


L. Zhu

L. Zhu

SQLTUNE_CATAGORY is the parameter for that session if want to use sql profile properly

So C is correct