during investigation of a performance issue for a client it was found that due to
significant growth in the database a select query was suddenly taking hours to
run instead of minutes. Obvious things
like statistics and the database were checked and it was clear to see the SQL code needed a review. However this code is from a vendor and cannot
be changed so the client was interested in anything that could be
done to assist them in the short term. This
blog update covers the analysis and final answer which was to introduce the
parallel hint into the SQL code with NO actual code change using the ‘ dbms_sqldiag’ package and ‘i_create_patch’ procedure.
answer and quickest to implement was to execute the SQL tuning advisor against
the code and that prompted a couple of SQL profiles for answers. The most positive of these was a PX_PROFILE
which would introduce parallelism of a DOP (48) into the query and although warning
about resource increases it looked good enough to investigate further. To take a stage forward the query was tested
using the PARALLEL hint to see how much benefit parallelism would
introduce. It should be noted that care
is required before jumping into parallelism as a solution. For example in Oracle RAC what is the value of ‘parallel_force_local’ and what is the
available CPU, memory, ‘large_pool’ etc.
executed the code from SQL developer using the PARALLEL hint as below to see
the real effect of the parallelism suggestion.
Note this is not the actual code but
just an example of using the PARALLEL hint.
Select /*+PARALLEL */ count(*) from test_table;
was very positive with execution now taking around four minutes to complete which was a large difference from over 2 hours previously. However examining the explain plan with the
PARALLEL hint showed it was very different to that of the explain plan for the SQL profile produced
by the SQL tuning advisor. This meant the
SQL profile was not the answer as the requested approach was now to get the PARALLEL
hint implemented as tested and proven and not the execution as the SQL profile suggested with the PX_PROFILE.
a baseline was the next answer to adopt. Baselines are very easy to generate from the SQL cache linking the
execution plan for the SQL with the hint to that of the SQL without the hint. References on how to do this are contained in the acknowledgements.
baseline was introduced the code was re-tested to ensure correct results. Although the baseline was enabled and
accepted the original code refused to implement the new baseline. The execution was not going to parallel
execution as desired. What was wrong?
this further, the reason why the baseline was not being selected is that the
PARALLEL hint does not make it into the plan hints of the baseline but is obviously essential to force
the parallel plan and get the appropriate hash plan value. Again this is not going to be discussed
further here as it is available in other blog entries elsewhere, see
profile and a baseline were now ruled out as possible solutions. It was then that an alternative arose called SQL
PATCH. There are some pre-requisites in
what SQL PATCH will and won’t do as also referenced in the acknowledgements
section. However for a straight
/*+PARALLEL */ hint it worked a treat!
add the parallel hint to the query with SQL PATCH:
SELECT sql_text into sqltext FROM DBA_HIST_SQLTEXT WHERE sql_id =’9h8a1m1a4fwzc’;
=> sqltext, Hint_text=> ‘PARALLEL’,Name=>’PARALLEL Hint Patch’);
NOTE: The SQL text was obtained from the view DBA_HIST_SQLTEXT with the sql_id as it was a large query.
submitting the above and testing with explain plan and execution finally we
have the PARALLEL hint being used as was desired without any code change. In the explain plan there is even a note
showing the name as above which aids in identification later.
at a later stage to drop the above it can be implemented with the code as
DBMS_SQLDIAG.DROP_SQL_PATCH(name=>’PARALLEL Hint Patch’);
The following blogs were referenced in this research piece: