Posts Tagged Performance Tuning

#Oracle SQL Tuning Advisor on the Command Line

This post will show a brief example how to use the SQL Tuning Advisor on the Command Line. The documentation is very verbose about it, but maybe you’ll find it a bit hard to dig through and prefer to start easy.

SQL> grant dba to adam identified by adam;

Grant succeeded.

SQL> connect adam/adam
Connected.
SQL> create table adam.test(n number, w varchar2(3));

Table created.

SQL> insert into adam.test values (1,'uwe');

1 row created.

SQL> commit;

Commit complete.

SQL> create index adam.idx on adam.test(w);

Index created.

The playing field contains nothing special, just an ordinary table and an ordinary index. Now let’s pretend that the following statement has a very long runtime and we don’t know why it does not use the index on the table:

SQL> select * from test where lower(w)='uwe';

         N W
---------- ---
         1 uwe

I will now ask the SQL Tuning Advisor about that ‘slow’ statement:

SQL> declare
    tempstring  varchar2(300);
    task_id   varchar2(200);
    begin
       tempstring := 'select * from adam.test where lower(w)=''uwe''';
       task_id := dbms_sqltune.create_tuning_task(sql_text => tempstring, task_name=>'SQLTUNE1');
       dbms_sqltune.execute_tuning_task('SQLTUNE1');
    end;
    /

PL/SQL procedure successfully completed.

SQL> set long 5000
SQL> select dbms_sqltune.report_tuning_task('SQLTUNE1') from dual; 

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQLTUNE1')
-------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : SQLTUNE1
Tuning Task Owner  : ADAM
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at         : 10/11/2013 08:46:19
Completed at       : 10/11/2013 08:46:21

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQLTUNE1')
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Schema Name: ADAM
SQL ID     : 71vcnzazw9kvw
SQL Text   : select * from adam.test where lower(w)='uwe'

-------------------------------------------------------------------------------
FINDINGS SECTION (3 findings)
-------------------------------------------------------------------------------

1- Statistics Finding
---------------------

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQLTUNE1')
-------------------------------------------------------------------------------
  Table "ADAM"."TEST" was not analyzed.

  Recommendation
  --------------
  - Consider collecting optimizer statistics for this table.
    execute dbms_stats.gather_table_stats(ownname => 'ADAM', tabname =>
            'TEST', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
            method_opt => 'FOR ALL COLUMNS SIZE AUTO');

  Rationale
  ---------

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQLTUNE1')
-------------------------------------------------------------------------------
    The optimizer requires up-to-date statistics for the table in order to
    select a good execution plan.

2- Index Finding (see explain plans section below)
--------------------------------------------------
  The execution plan of this statement can be improved by creating one or more
  indices.

  Recommendation (estimated benefit: 66.52%)
  ------------------------------------------
  - Consider running the Access Advisor to improve the physical schema design

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQLTUNE1')
--------------------------------------------------------------------------------
    or creating the recommended index.
    create index ADAM.IDX$$_000C0001 on ADAM.TEST(LOWER("W"),"N","W");

  Rationale
  ---------
    Creating the recommended indices significantly improves the execution plan
    of this statement. However, it might be preferable to run "Access Advisor"
    using a representative SQL workload as opposed to a single statement. This
    will allow to get comprehensive index recommendations which takes into
    account index maintenance overhead and additional space consumption.

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQLTUNE1')
--------------------------------------------------------------------------------
3- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
  The predicate LOWER("TEST"."W")='uwe' used at line ID 1 of the execution
  plan contains an expression on indexed column "W". This expression prevents
  the optimizer from selecting indices on table "ADAM"."TEST".

  Recommendation
  --------------
  - Rewrite the predicate into an equivalent form to take advantage of
    indices. Alternatively, create a function-based index on the expression.

Pretty much to the point, don’t you agree? I removed the explain plan sections from the output; they show that without the recommended function based index, the statement needs to do a Full Table Scan .

That was a demonstration I did during my recent class Oracle Database 11g: Performance Tuning. Hope you find it useful :-)

About these ads

7 Comments

Why your Parallel DML is slower than you thought

In the Data Warehouse Administration course that I delivered this week, one topic was Parallel Operations. Queries, DDL and DML can be done in parallel, but DML is special: You need to enable it for your session! This is reflected in v$session with the three columns PQ_STATUS, PDDL_STATUS and PDML_STATUS. Unlike the other two, PDML_STATUS defaults to disabled. It requires not only a parallel degree on the table respectively a parallel hint inside the statement, but additionally a command like ALTER SESSION ENABLE PARALLEL DML; Look what happens when I run an UPDATE with or without that command:

Two updates running in parallelThe table sales has a parallel degree of 4. The two marked statements seem to be identical – and they are. But the second has a much longer runtime. Why is that?

Parallel DML with QC doing the updateIt’s because Parallel DML is disabled in that session. The fetching of the rows can be done in parallel, but the Query Coordinator (QC) needs to do the update! That is of course not efficient. The mean thing is that you see actually Parallel Processes (PXs) running and appearing in the execution plan, so this may look like it does what it is supposed to – but is does NOT. Here is how it should be, with the correct ALTER SESSION ENABLE PARALLEL DML before the update:

Parallel DML with PXs doing the workThe QC does only the job of coordinating the PXs here that do both, fetching and updating the rows now. Result is a way faster execution time. I’m sure you knew that already, but just in case ;-)

 

,

3 Comments

Evidence for successful #Oracle Performance Tuning

This article shows an easy way to determine, whether your Oracle Database Performance Tuning task has been successful – or not. In the end, it boils down to “The objective for tuning an Oracle system could be stated as reducing the time that users spend in performing some action on the database, or simply reducing DB time.” as the Online Documentation says. Best proof would be a confirmation from the end users that run time got reduced; second best is a proof of reduced DB time, which is discussed here.

A tuning task should always end with such a proof; your gut feeling or high confidence is not sufficient – or as I like to say: “Don’t believe it, test it!” :-)

The demo scenario: With an Oracle Enterprise Edition version 11.2.0.3, an application uses these commands to delete rows:

SQL> create table t as select * from dual where 1=2;
 
Table created.
SQL> begin
 for i in 1..100000 loop
 execute immediate 'delete from t where dummy='||to_char(i);
 end loop;
 end;
 /

I am pretty sure that this code is not optimal, because it uses Literals instead of Bind Variables where it is not appropriate. Before I implement an improved version of the code, I take a Baseline with Automatic Workload Repository (AWR) snapshots. On my demo system, snapshots are taken every ten minutes:

EM Homepage at 8:51 amThe code with Literals was just called – now about 10 minutes later on the Enterprise Manager (EM) Performance page:

CPU peak on EM Performance pageThe AWR report that I take (with EM or with awrrpt.sql) as the baseline shows the following:

AWR report baseline DB timeNotice especially the poor Library Cache hit ration, significant for not using Bind Variables – and the meaningless high Buffer Cache hit ration :-)

AWR baseline low Library Cache hit ratioStarting after 9:00 am, my improved code that uses Bind Variables runs:

SQL> begin
 for i in 1..100000 loop
 delete from t where dummy=to_char(i);
 end loop;
 end; 
 /

The EM Performance page show no peak during the next 10 minutes which represent my comparison period after the tuning task:

No peak on EM Performance page after tuning taskLet’s look at the AWR report of the second snapshot range after the tuning task:

AWR report after tuning task shows reduced DB timeSame wall clock time, same application load, but reduced DB time – I was successful! Could stop here, but some more details:

AWR report shows improved Library Cache hit ratioThe important (especially for OLTP systems) Library Cache hit ratio is now very good. A very convenient way to compare the two snapshot ranges is the ‘AWR Compare Periods’ feature in EM (or awrddrpt.sql) , which shows us instructively:

AWR Compare Periods with less DB timeAlthough in both periods, CPU was the top event (also in % DB time), it took much less time in total for the 2nd period:

AWR compare periods with less CPU wait for 2nd periodThe Time Model Statistics confirm a strongly reduced Parse Time for the 2nd period:

Time Model Statistics show reduced Parse Time for 2nd periodEspecially, we see a striking improvement for the run time of the code with Bind Variables: From about 82 seconds down to about 3 seconds!

SQL comparison section show much less run time in 2nd periodThis kind of proof (less DB time) can be used also in cases where the reduction of run time for a single statement is not so obvious as in my example. If 1000 users had done each 100 deletes, they would have seen not much difference in run time each – but the parse time summarizes and impacts overall performance similar as seen here. If you would like to see the three original AWR reports were I took the screen shots above from, they are here as PDFs

AWR_Report_baseline

AWR_report_after_tuning_task

AWR_Compare_Periods

Conclusion: You will – and should – be able to prove the effectiveness of your Oracle Database Tuning task with a reduction of DB time from an AWR report comparison. After all, you don’t want to waste your efforts, do you? :-)

Addendum: This posting was published in the Oracle University EMEA Newsletter May 2013

 

, ,

7 Comments

Follow

Get every new post delivered to your Inbox.

Join 2,892 other followers

%d bloggers like this: