Tuesday May 28, 2013

Oracle SQL Profile Internals

Black Box

It is very easy to pick a piece of poor performing code either by SQL text or SQL ID and then execute the Oracle SQL tuning advisor against that code. When a SQL profile is recommended though, how many people know what has actually been suggested by the profile internally?

This update covers a little known database view DBMSHSXP_SQL_PROFILE_ATTR which when supplied with the SQL profile name displays the internal hints that the profile is suggesting in XML format. This provides the missing piece to understanding what the SQL profile.

As always, here is an example;

1.  Create SQL tuning task on the statement. This can be done by SQL_ID or by SQL text.


                  l_sql_tune_task_id varchar2(100);
                  l_sql_tune_task_id := dbms_sqltune.create_tuning_task(
                                  sql_id=> '9yycxkm1kb8jn',
                                  description=>'Tuning Example by SQL ID');


l_sql_tune_task_id      varchar2(100);
my_sqltext              CLOB;
     my_sqltext := ‘select count(*) from scott.emp’;

     l_sql_tune_task_id := dbms_sqltune.create_tuning_task(
                          sql_text=> my_sqltext,
                          description=>'Tuning Example by SQL ID');

2.  Now Execute the SQL tuning task created in Step 1.

SQL>exec dbms_sqltune.execute_tuning_task(task_name=>’SQLID’);


            SQL>exec dbms_sqltune.execute_tuning_task(task_name=>’SQLTEXT’;

3.  Now run the report of the SQL tuning task recommendations. -

SQL>spool results.out

                       set long 999999999
                       set lines 80
                       set pages 0

SQL> select dbms_sqltune.report_tuning_task(‘SQLTEXT’) as recommended from dual;

See below some sample output recommending a SQL profile for the statement.

3- SQL Profile Finding (see explain plans section below)
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 98.05%)
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'SQLTEXT', task_owner => 'SYS', replace => TRUE);

To actually accept the SQL profile

 SQL>execute dbms_sqltune.accept_sql_profile(task_name => 'SQLTEXT',task_owner => 'SYS', replace => TRUE);

check that profile has been saved in the data dictionary

WHERE SQL_TEXT LIKE 'select count(*) from Scott.emp%';

-----------------------------                     ------------------------------------------------            

SYS_SQLPROF_014c9e57e27g0006 select count(*) from scott.emp

To drop the sql tuning session if not applicable, or if the profile is implemented and it is wanted to remove, then  dbms_sqltune.drop_sql_profile can be executed also.

SQL> execute dbms_sqltune.drop_tuning_task(task_name=>’SQLTEXT’);

 4.  Finally to display the hints suggested by the profile for further analysis

 NOTE: Under Oracle 10g SQL profiles were stored in : SQLPROF$ and SQLPROF$ATTR.  However in 11g SQL profiles are stored in SQLOBJ$ and SQLOBJ$DATA and DBMSHSXP_SQL_PROFILE_ATTR. It is also necessary to convert the format as they are stored in XML format.



 -----------------------------                       ------------------------------------------------

 SYS_SQLPROF_014c9e57e27g0006 <outline_data><hint><![CDATA[PARALLEL]]></hint></outline_data>

So it can be seen by analysis of the above that the Parallel hint has been suggested by the profile. However if other recommendations were made these in turn can be analyzed for relevance and understanding for further tuning.

  Some examples of other types of hint are:


  The scale_rows figure is multiplied by the number of rows in the table to get an estimate on the number of rows to be retrieved by the query and supply to the optimizer.

 COLUMN_STATS("SCOTT"."EMP", "ID", scale, length=3)]]>

  The above is storing the estimate on the average column length of a field.

 TABLE_STATS("SCOTT"."EMP", scale, blocks=10123 rows=988.542)]]>

  The above is displaying the number of rows and blocks to the optimizer.


  The above tells the optimizer to ignore any hints given in the statement itself.


The above tells the optimizer to execute the statement with features enabled in version 9.0.0

Thursday Apr 25, 2013

'IS NULL' SQL Tuning

NULLFrequently at client sites some very slow queries contain the SQL involving the ‘IS NULL’ criteria in the where clause. It is commonly known Oracle does not store NULL values in indexes so even though an index may exist for the column it is NOT usable in this case. However there is a solution to this situation and FULL table scans can be avoided!

Example: SQL> Select name from emp where dept_id is null;

So in the above example even if the dept_id is indexed on the table emp, due to the ‘IS NULL’ criteria it will NOT be used.

Many developers believe that this type of statement cannot be tuned and that a FULL table scan is the only option. However here is a solution to this issue, simply create an appropriate index but add in an additional constant value to the end of the index so that now NULL values are actually stored!

Example: SQL> create index E1 on EMP (DEPT_ID, -1);

Running an explain plan and executing the SQL it can be seen that the new index will now be used as NULL values are now indexed.

Real life example: At a particular customer site there was a situation where a table had over 11 million rows. A SQL statement had a lot of table joins but only 2 constraints in the where clause as below.



From analyzing the table almost every row had the PRIV_FLG = ‘N’ so that was not going to help, however looking at the integration_id from the 11 million rows, only 51,167 were null values. The field 'integration_id' was already indexed however this would not be used due to only wanting NULL values. By indexing as in the above example the new index helped optimize the query significantly as the number of rows in the driving table had been significantly reduced without any full table scans!

Friday Mar 15, 2013

Applying a Parallel Hint to SQL Code that cannot change

PerformanceLast night 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.

The first 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.

Developers 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;

The test 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.

Easy, create 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.

After the 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?

Researching 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 acknowledgements.

So a 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!

To add the parallel hint to the query with SQL PATCH:


Sqltext clob;


SELECT sql_text into sqltext FROM DBA_HIST_SQLTEXT WHERE sql_id =’9h8a1m1a4fwzc’;

DBMS_SQLDIAG_INTERNAL.I_CREATE_PATCH(Sql_text => 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.

After 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.

If required at a later stage to drop the above it can be implemented with the code as below:






The following blogs were referenced in this research piece:

1. http://metekarar.blogspot.com.au/2012/07/adding-hints-with-sql-patch.html

2. http://orastory.wordpress.com/2013/03/09/sql-patch-iii-plus-parallel-baselines/

3. http://orastory.wordpress.com/2011/10/13/no-need-to-change-source-code-hint-it-using-a-baseline/

4. http://docs.oracle.com/cd/B28359_01/server.111/b28274/sql_tune.html

Friday Feb 01, 2013

How to setup a simple Oracle streams example: 1 table to another table in the same schema and database

Oracle Streams ImageWhilst recently on site with a client, it was requested to provide a simple Oracle streams demonstration with documentation that showed how to replicate a single table to another table within the same database and schema. 

The attached document gives the scripts and step by step workings for the example.  It is aimed for those new to Oracle streams and wanting a simple working example to follow.  It should be noted that if looking at replication technology it is recommended to look first at the product Oracle Golden Gate. 

For support of this and further implementation help please contact the author of the paper as required.  The aims of the supplied scripts are purely for demonstration and training purposes.

The views expressed on this blog are those of the author and do not necessarily reflect the views of Oracle.  Please download the full paper for a detailed discussion as well as some recovery tricks you may not have come across.

Download The Full PDF here: Oracle_Streams_Example.pdf


About Me Image
Andy Baker, Senior Principal Consultant for Oracle Consulting Services (@Bakers_byte), shares his news, views and ideas about the Oracle Database with a focus on innovation and emerging technologies.


« July 2016