Database Reference
In-Depth Information
GROUP BY n_name,
r_name,
p_name,
s_name;
Step 8
Find the
SQL_ID
and
PLAN_HASH_VALUE
for the new modified SQL statement from
V$SQL
view:
SQL> SELECT SQL_ID, PLAN_HASH_VALUE, SQL_FULLTEXT FROM V$SQL WHERE SQL_TEXT LIKE 'SELECT N_NAME,R_
NAME%';
SQL_ID PLAN_HASH_VALUE SQL_FULLTEXT
------------- --------------- ----------------------------------------
5kzxaz6fbbncz 1879087708 SELECT N_NAME,R_NAME,P_NAME,S_NAME,SUM(P
S_SUPPLYCOST) FROM region,nation,supplie
Step 9
Using the values obtained from Step 8, create a new accepted plan for the original SQL statement by associating the
modified plan to the original statements
SQL_HANDLE
:
SQL> EXEC :sqlid := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (SQL_ID=>'5kzxaz6fbbncz',PLAN_HASH_
VALUE=>1879087708, SQL_HANDLE => 'SYS_SQL_26517e6bd220dc3c');
PL/SQL procedure successfully completed.
Step 10
Verify from the
DBA_SQL_PLAN_BASELINES
to ensure the operation is successful:
SQL> SELECT SQL_HANDLE, PLAN_NAME, ENABLED FROM DBA_SQL_PLAN_BASELINES;
SQL_HANDLE PLAN_NAME ENA
------------------------------ ------------------------------ ---
SYS_SQL_26517e6bd220dc3c SQL_PLAN_2cnbydg921r1w926d78b5 YES
SYS_SQL_26517e6bd220dc3c SQL_PLAN_2cnbydg921r1w9dcc863f NO
Once this new plan is proven to be the optimized plan, it can be made fixed until such time that a better
optimizer plan is generated. The SQL plan baselines can be marked as fixed. Fixed SQL plan baselines indicate to the
optimizer that they are preferred. Marking a plan as a fixed plan, the optimizer will only cost the fixed plan. If the fixed
plan is not reproducible by the optimizer, it will generate a new plan and cost the remaining SQL plan baselines to
select the plan with the lowest cost.
Optimizer Statistics
Statistics used by the optimizer when generating the execution plan are stored in the data dictionary. Statistics about
physical storage characteristics and data distribution in the various schema objects are generated by using the
DBMS_STATS
package.
Search WWH ::
Custom Search