Thinking Out Loud

November 2, 2016

Identify SQL_ID from Failed Per-SQL Time Limit SQL_TUNING_TASK

Filed under: 11g,oracle,performance — mdinh @ 2:32 am

First, many thanks to Ivica Arsov from Pythian for all his help is solving the issue.

The objective is to find the 2 SQLs failed due to time limit.

Advance apologies for lazy post as I am just going to only provide references and results from triage.

$ oerr ora 16957
16957, 00000, "SQL Analyze time limit interrupt"
// *Cause: This is an internal error code used indicate that SQL analyze has
//         reached its time limit.
// *Action:

$ oerr ora 13639
13639, 00000, "The current operation was interrupted because it timed out."
// *Cause:  The task or object operation timed out.
// *Action: None

+++++++++++

SQL> 
SELECT DBMS_AUTO_SQLTUNE.report_auto_tuning_task FROM dual;

REPORT_AUTO_TUNING_TASK
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                        : SYS_AUTO_SQL_TUNING_TASK
Tuning Task Owner                       : SYS
Workload Type                           : Automatic High-Load SQL Workload
Execution Count                         : 39
Current Execution                       : EXEC_17554
Execution Type                          : TUNE SQL
Scope                                   : COMPREHENSIVE
Global Time Limit(seconds)              : 14400
Per-SQL Time Limit(seconds)             : 2700
Completion Status                       : INTERRUPTED
Started at                              : 10/20/2016 22:00:01
Completed at                            : 10/21/2016 02:00:03
Number of Candidate SQLs                : 248
Cumulative Elapsed Time of SQL (s)      : 4837418

-------------------------------------------------------------------------------
Error: ORA-13639: The current operation was interrupted because it timed out.
-------------------------------------------------------------------------------

-------------------------------------------------------------------------------
SUMMARY SECTION
-------------------------------------------------------------------------------
                      Global SQL Tuning Result Statistics
-------------------------------------------------------------------------------
Number of SQLs Analyzed                      : 130
Number of SQLs in the Report                 : 23
Number of SQLs with Findings                 : 22
Number of SQLs with Statistic Findings       : 2
Number of SQLs with Alternative Plan Findings: 5
Number of SQLs with SQL profiles recommended : 14
Number of SQLs with Index Findings           : 11
Number of SQLs with SQL Restructure Findings : 3
Number of SQLs with Timeouts                 : 2
Number of SQLs with Errors                   : 1


Here are the references and what have been attempted.
It looks like attr7 varies by versions.

+++++++++

Automatic SQL Tune Job Fails With ORA-13639 (Doc ID 1363111.1)
Increase the job "Time Limit" Parameter to appropriate higher value to allow the tuning task to complete , using the following command :

Global Time Limit(seconds)              : 14400
Per-SQL Time Limit(seconds)             : 2700
Number of SQLs with Timeouts            : 2

+++++++

https://anargodjaev.wordpress.com/2014/07/22/ora-16957-sql-analyze-time-limit-interrupt-2/

SQL> r
  1  SELECT sql_id, sql_text FROM dba_hist_sqltext
  2  WHERE sql_id IN (SELECT attr1 FROM dba_advisor_objects
  3  WHERE execution_name = 'EXEC_17554'
  4  AND task_name = 'SYS_AUTO_SQL_TUNING_TASK'
  5  AND type = 'SQL' AND bitand(attr7,64) <> 0 )
  6*

no rows selected

SQL>

++++++++++

Bug 9874145 : PROCESS 0X0X00000002E2392938 APPEARS TO BE HUNG IN AUTO SQL TUNING TASK   
Product Version    11.1.0.7

SQL> select attr1 SQL_ID from dba_advisor_objects
  where task_name ='SYS_AUTO_SQL_TUNING_TASK'
  and type = 'SQL'
  and attr7 = 32
  order by 1;
 
SQL> r
  1* select attr7, count(*) from DBA_ADVISOR_OBJECTS where execution_name='EXEC_17554' and task_name ='SYS_AUTO_SQL_TUNING_TASK' and type='SQL' group by attr7

     ATTR7   COUNT(*)
---------- ----------
    32       99
     0       31

SQL> r
  1  SELECT count(sql_id)  FROM dba_hist_sqltext
  2  WHERE sql_id IN (SELECT attr1 FROM dba_advisor_objects
  3  WHERE execution_name = 'EXEC_17554'
  4  AND task_name = 'SYS_AUTO_SQL_TUNING_TASK'
  5  AND type = 'SQL' AND bitand(attr7,32) <> 0 )
  6*

COUNT(SQL_ID)
-------------
       99

SQL> r
  1  select  distinct sql_id
  2  from    DBA_ADVISOR_SQLPLANS
  3  where   execution_name = 'EXEC_17554'
  4         and sql_id not in (select sql_id from dba_advisor_sqlstats where execution_name = 'EXEC_17554')
  5*

SQL_ID
-------------
2dhfrqwrv0m16
58h2g858zqckc
apzyk43bfp6np
g84pgjy5ycw2g
20ypq7mzad6ah
cr1s7zpp7285p
ag01z6qn8450d
4z3gq9xh9a5jr
byav70kx3bj8w
8m3qru4z4687w
d28dgmyr5910n
5mgq2hd4xz6vv

12 rows selected.

SQL>

++++++++++

SQL> select sql_id from DBA_ADVISOR_SQLPLANS where EXECUTION_NAME='EXEC_17554'
minus
select sql_id from DBA_ADVISOR_SQLSTATS where EXECUTION_NAME='EXEC_17554'
;  2    3    4

SQL_ID
-------------
20ypq7mzad6ah
2dhfrqwrv0m16
4z3gq9xh9a5jr
58h2g858zqckc
5mgq2hd4xz6vv
8m3qru4z4687w
ag01z6qn8450d
apzyk43bfp6np
byav70kx3bj8w
cr1s7zpp7285p
d28dgmyr5910n
g84pgjy5ycw2g

12 rows selected.

SQL> select sql_id from DBA_ADVISOR_SQLSTATS where EXECUTION_NAME='EXEC_17554'
minus
select sql_id from DBA_ADVISOR_SQLPLANS where EXECUTION_NAME='EXEC_17554'
;  2    3    4

no rows selected

SQL>

++++++++++

case when finding_type = 3 AND bitand(finding_flags, 2) <> 0 then 1 else 0 end

1 - has timeout
0 - no timeout

finding_type and finding_flags are from sys.wri$_adv_findings table.

SQL> r
  1* select distinct TYPE, FLAGS from sys.wri$_adv_findings

      TYPE    FLAGS
---------- ----------
     4        1
     1        0
     4
     4        4
     2        0
     5        0
     2
     3        0
     4        0
     3        2
     4        9
     1
     3       16

13 rows selected.

SQL> r
  1  SELECT oe.*,
  2          f.id finding_id,
  3          f.type finding_type,
  4          f.flags finding_flags
  5  FROM
  6    (SELECT
  7       o.exec_name ,
  8        o.id object_id,
  9        o.attr1 sql_id,
 10        o.attr3 parsing_schema,
 11        to_number(NVL(o.attr5, '0')) phv,
 12        NVL(o.attr8,0) obj_attr8
 13      FROM  sys.wri$_adv_objects o
 14      WHERE o.exec_name = 'EXEC_17554'
 15      AND o.type      = 7
 16    ) oe,
 17    wri$_adv_findings f
 18  WHERE  f.exec_name (+)  = oe.exec_name
 19  AND f.obj_id (+)     = oe.object_id
 20  AND type             = 3
 21  AND bitand(flags,2) <> 0
 22*

EXEC_NAME        OBJECT_ID SQL_ID          PARSING_SCHEMA         PHV  OBJ_ATTR8 FINDING_ID FINDING_TYPE FINDING_FLAGS
--------------- ---------- --------------- --------------- ---------- ---------- ---------- ------------ -------------
EXEC_17554           29975 bxurwhv4muqj5   DEMO1234        2724156332 2438782765      31681            3             2
EXEC_17554           30015 1gzgzfuxn18v1   DEMO345670      4228937525 3182936792      31724            3             2

SQL> 
Advertisements

November 18, 2015

Extent Allocation

Filed under: 11g,ASM,oracle,performance — mdinh @ 11:43 pm

Some references on extents dealing with extent trimming, initial extent for parallel load, tablespace fragmentation, etc…

ASM AU Size And LMT AUTOALLOCATE
http://oracle-randolf.blogspot.com/2013/04/asm-au-size-and-lmt-autoallocate.html

Parallel Load: Uniform or AutoAllocate extents?
https://blogs.oracle.com/datawarehousing/entry/parallel_load_uniform_or_autoallocate

Tom Kyte covers this problem in great details in his post Loading and Extents
http://www.oracle.com/technetwork/issue-archive/2007/07-may/o37asktom-101781.html

System Managed Extent Size – 11g Improvements
https://antognini.ch/2009/08/system-managed-extent-size-11g-improvements/

PX and system allocation
https://jonathanlewis.wordpress.com/2012/06/14/px-and-system-allocation/

Initial Extent Size of a Partition Changed to 8MB from 64KB After Upgrade to 11.2.0.2 or Later (Doc ID 1295484.1)

Bug 19912552 : PARTITIONED INDEXES CREATED WITH 8M INITIAL EXTENT WHEN _INDEX_PARTITION_LARGE

Segments of type INDEX PARTITION or INDEX SUBPARTITION will be created with 8M initial extent in version 12.1.0.2, instead of 64K in earlier versions.
Global indexes or indexes on non partitioned tables still have 64K.

September 30, 2015

SQL Profile and Plan Baseline Notes

Filed under: 11g,12c,performance — mdinh @ 1:09 pm

Optimizer with Oracle Database 12c
Oracle White Paper – June 2013
http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-with-oracledb-12c-1963236.pdf

What is the difference between SQL Profiles and SQL Plan Baselines?

The difference between SQL profiles and SQL plan baselines and why SQL profiles can be shared but SQL plan baselines can’t.

By Maria Colgan-Oracle on May 08, 2012
https://blogs.oracle.com/optimizer/entry/what_is_the_different_between

Plan Stability using Sql Profiles and SQL Plan Management
Amit Bansal / 20 December, 2011
http://askdba.org/weblog/2011/12/plan-stability-using-sql-profiles-and-sql-plan-management/

what-is-the-difference-between-sql-profile-and-spm-baseline
3 OCT/10
http://intermediatesql.com/oracle/what-is-the-difference-between-sql-profile-and-spm-baseline/

Comments from Kerry Osborne September 9th, 2011 – 18:31
You didn’t mention the two biggest differences (in my opinion).

1.
Baselines know what plan they are trying recreate and SQL Profiles do not.
SQL Profiles will blindly apply any hints it has and what you get is what you get.
Baselines will apply the hints and if the optimizer gets the plan it was expecting, it uses the plan.
If it doesn’t come up with the expected plan, the hints are thrown away and the optimizer tries again (possibly with the hints from another accepted Baseline).

2.
Profiles have a “force matching” capability that allows them to be applied to multiple statements that differ only in the values of literals.
Think of it as a just in time cursor sharing feature. Baselines do not have this ability to act on multiple statements.

Comments from Kerry Osborne January 25th, 2012 – 16:38

I have seen Baselines be disregarded, even without such extreme conditions as a specified index having been removed.

The reason for this is that Baselines attempt to apply enough hints to limit the choices the optimizer has to a single plan,
but there are situations where the set of hints is not sufficient to actually force the desired plan.

What I mean is that the hints will eliminate virtually all possibility but there still may be a few that are valid and so it’s possible to get a different plan.

In fact, I have even seen situations where the act of creating a Baseline causes the plan to change.

This is clearly not intended behavior but it can happen.

I think I blogged about that once. At any rate, in these cases where the optimizer arrives at a different plan than was expected,
the optimizer uses a plan generated completely without the hints (or possible picks another accepted Baseline and applies it’s hints).

This behavior is very different from SQL Profiles which blindly apply the hints.

I should also note that SQL Profiles can also be created manually with the same set of hints that would be used in a Baseline and there is a MOS note regarding how to do this.

I have blogged about that as well here:
http://kerryosborne.oracle-guy.com/2010/07/sqlt-coe_xfr_sql_profilesql/

Updated Oct 19, 2015

Strategies for Minimising SQL Execution Plan Instability
https://orastory.wordpress.com/2015/05/01/strategies-for-minimising-sql-execution-plan-instability

To baseline or not to baseline?
https://mikepargeter.wordpress.com/2015/07/21/to-baseline-or-not-to-baseline/

What you need to know about SQL Plan Management and Auto Capture
https://blogs.oracle.com/optimizer/entry/what_you_need_to_know

October 24, 2014

Oracle Real World Performance

Filed under: oracle,performance — mdinh @ 4:39 am

Great videos on Oracle Real World Performance.

December 29, 2012

Poor Performance Compression Bug

Filed under: 11g,performance — mdinh @ 5:25 am

I was really puzzled why performance for compressed tables was horrendous.

My knowledge of the environment enabled me to make a hypothesis on the cause for performance degradation-db_block_checking.

I agree with Martin Berger the finding was generally indirect and not sure how someone else would have been able to identify the issue.

Even Oracle Support suggested the only hint is increase CPU. However, there can be many factors affecting CPU, e.g. nested loop.

The database version is 11.2.0.3 running on Sparc Solaris 10 64bit

Setup:

FACT table COMPRESS FOR OLTP with DEFAULT PCTFREE, INITTRANS settings (53 cols NOT NULL)
PK (1 col)
AK (3 col)
IX (1 col)
33,179,386 rows

STAGING table with DEFAULT PCTFREE, INITTRANS settings (52 cols, 1 col NOT NULL, remaining NULL)
No index/constraints
911,861 rows

Pseudo Code:

MERGE INTO FACT f USING STAGING s ON (f.KEY=s.KEY)
WHEN MATCHED THEN UPDATE SET … 52 columns

SQL Script:

set timing on serveroutput on
spool testcomp.log

exec runstats_pkg.rs_start;
commit;

alter session force parallel dml parallel 8;
@/oracle/sqlt/input/sample/oltp_false.sql
alter session disable parallel dml;

exec runstats_pkg.rs_middle;
commit;

alter session force parallel dml parallel 8;
@/oracle/sqlt/input/sample/oltp_medium.sql
alter session disable parallel dml;

exec runstats_pkg.rs_stop(0,’WORKLOAD’);
exec runstats_pkg.rs_stop(1000000);

alter system set db_block_checking=FALSE;
spool off

SQL Execution Snipit:

SQL> alter system set db_block_checking=FALSE;
System altered.
Elapsed: 00:00:00.01

SQL> set echo off
911861 rows merged.
Elapsed: 00:01:52.30
Commit complete.

SQL> alter system set db_block_checking=MEDIUM;
System altered.
Elapsed: 00:00:00.01

SQL> set echo off
911861 rows merged.
Elapsed: 00:23:44.38
Commit complete.

Run1 ran in 11483 hsecs  - db_block_checking=FALSE
Run2 ran in 142483 hsecs - db_block_checking=MEDIUM
run 1 ran in 8.06% of the time

Name                                                Run1            Run2        Diff
STAT...physical reads direct                     517,192         517,208              16
STAT...physical reads                            522,167         517,224          -4,943
STAT...physical reads cache                        4,975              16          -4,959
STAT...Elapsed Time                               11,487         142,487         131,000
STAT...recursive cpu usage                        34,909         498,166         463,257
STAT...CPU used by this session                   35,572         498,920         463,348
STAT...redo size                             889,907,464     892,430,032       2,522,568
STAT...session logical reads                   3,480,123       6,557,752       3,077,629

Run1 latches total versus runs -- difference and pct
Run1            Run2            Diff       Pct
20,508,977      68,940,229      48,431,252     29.75%
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.08

Run1 ran in 11483 hsecs  - db_block_checking=FALSE
Run2 ran in 142491 hsecs - db_block_checking=MEDIUM
run 1 ran in 8.06% of the time

Name                                                Run1            Run2        Diff
STAT...session uga memory max                    130,976       1,293,104       1,162,128
STAT...in call idle wait time                    115,158       1,409,641       1,294,483
LATCH.simulator hash latch                       247,055       1,556,432       1,309,377
LATCH.object queue header operation              205,535       1,766,312       1,560,777
STAT...session pga memory                        851,968        -720,896      -1,572,864
LATCH.transaction allocation                     108,304       1,936,419       1,828,115
STAT...DB time                                   189,534       2,196,702       2,007,168
STAT...redo size                             889,907,464     892,433,832       2,526,368
LATCH.checkpoint queue latch                      90,178       2,757,576       2,667,398
STAT...session logical reads                   3,480,123       6,557,897       3,077,774
STAT...consistent gets                           838,019       3,926,800       3,088,781
STAT...consistent gets from cache                 20,153       3,109,037       3,088,884
STAT...consistent gets - examination              11,341       3,101,011       3,089,670
STAT...data blocks consistent reads -
       undo records applied                            0       3,091,383       3,091,383
STAT...Effective IO time                       8,124,867       4,359,804      -3,765,063
LATCH.DML lock allocation                        450,593       5,815,377       5,364,784
STAT...file io wait time                      51,133,453      44,027,178      -7,106,275
LATCH.cache buffers chains                    14,751,617      45,136,022      30,384,405
STAT...physical read bytes                 8,555,184,128   8,474,198,016     -80,986,112
STAT...physical read total bytes           8,555,184,128   8,474,198,016     -80,986,112
STAT...cell physical IO interconnect byt   8,555,184,128   8,474,198,016     -80,986,112
STAT...logical read bytes from cache      43,618,418,688  94,044,405,760  50,425,987,072

Run1 latches total versus runs -- difference and pct
Run1            Run2            Diff       Pct
41,017,954     137,883,083      96,865,129     29.75%

Run1 - blk_checking_false
Disk Reads 522,167
Buffer Gets 3,479,799  
98% CPU Wait 1:60 (mm:ss)

Run2 - blk_checking_medium
Disk Reads 517,116
Buffer Gets 6,547,223
90% CPU Wait 21:08 (mm:ss)

Test shows db_block_checking and compression are bad for performance due to a bug.

Unfortunately, from the all the metrics I have looked at, there’s is not a direct indication to the cause.

Open to ideas as what to look for or what analysis can be performed.

I have used XTRACT, XECUTE, COMPARE, TRCANLZR from SQLTXPLAIN (SQLT) 11.4.5.2

References:

Notes with explanations for OLTP compression, db_block_checking, db_lost_write_protect.

Best Practices for Corruption Detection, Prevention, and Automatic Repair – in a Data Guard Configuration [ID 1302539.1]
Master Note for OLTP Compression [ID 1223705.1]

Bug 12711341: EXTREME SLOWNESS WITH DB_ULTRA_SAFE ON COMPRESSED TABLES

Customer is not happy with the performance of DML after enabling COMPRESS option and turning DB_BLOCK_CHECKING = FULL.

Bug 8931846: DB_BLOCK_CHECKING CAUSES LARGE PERFORMANCE HIT WITH COMPRESSED TABLE

November 15, 2012

SAN Insanity

Filed under: performance,solaris — mdinh @ 5:22 am

There was a migration to a new storage array for the DR site bringing down all the standby databases.

After 3 days of downtime, the DR site was available, media recovery was started and was painfully slow.

At first, I figured it could be slow since so many databases were performing media recovery.

After executing opatch apply for 11.2.3.4 PSU on top of 11.2.0.3, which took 20 minutes, I realized it has to be the I/O subsystem.

I was not able to prove it, but I trusted my gut instinct and nothing from the database has changed.

It seemed pretty obvious.  Of course, the system group denied all this, wanted proof and did not bother to look at the issue.

I googled and found a way to perform simple I/O benchmark.

time sh -c “dd if=/dev/zero of=dd-test-file bs=8k count=1000000 && sync”
1000000+0 records in
1000000+0 records out

real 14m34.625s
user 0m1.555s
sys 0m37.942s

More than 14 minutes to create ~8GB file. The throughput is less that 9MB/s. This time I got the system group’s attention.
Throughout the investigative process, Oracle was getting blamed for the I/O. After several hours, it was determined to be the SAN.

Admittedly, I have become rusty in utilizing system tools and wanted to know how I could have determined this.

I came across the following 2 sites and comment in my search:

http://prefetch.net/blog/index.php/2005/06/26/finding-busy-disks-with-iostat/

http://serverfault.com/questions/26286/what-does-it-mean-if-iostat-shows-100-busy-wait-but-the-cpu-is-completely-idle

IO Stat shouldn’t be 100%. If you have a heavy amount of disk IO, it may be high, above 50%, but exactly 100% usually means something is wrong your IO subsystem. This has happened to me when I’ve had a hard drive in the early stages of failure, when the disk takes longer and longer to respond to requests, but still responds.
Or it could just be a very badly written application. A simple DTrace script should tell you which it is. – Craig Lewis

Here is the output using: iostat -zxnM 5

                    extended device statistics
    r/s    w/s   Mr/s   Mw/s wait actv wsvc_t asvc_t  %w  %b device
    0.0    0.2    0.0    0.0  0.0  0.0    0.0   15.1   0   0 ssd10
    0.0    0.2    0.0    0.0  0.0  0.0    0.0   10.7   0   0 ssd11
   11.4  145.0    0.3    1.1  0.0  8.8    0.0   56.4   0 100 7/md101
   11.4  145.0    0.3    1.1  0.0  8.8    0.0   56.4   0 100 7/md1
    0.6   75.6    0.0    0.6  0.0  2.3    0.0   29.6   0 100 6/md100
    0.6   75.6    0.0    0.6  0.0  2.3    0.0   29.6   0 100 6/md1
    5.0  143.2    0.0    1.1  0.0  5.8    0.0   38.8   0 100 4/md101
    5.0  143.2    0.0    1.1  0.0  5.8    0.0   38.8   0 100 4/md1
    0.0    0.8    0.0    0.0  0.0  0.0    0.0   53.9   0   2 1/md100
   11.4   75.8    0.1    0.6  0.0  5.2    0.0   60.0   0  99 ssd81
   13.0   69.2    0.1    0.5  0.0  3.8    0.0   46.0   0  92 ssd82
    0.0    0.8    0.0    0.0  0.0  0.0    0.0   53.9   0   2 ssd91
    5.0  143.2    0.0    1.1  0.0  5.8    0.0   38.8   0 100 ssd114
    0.0   67.4    0.0    0.5  0.0  2.0    0.0   29.5   0 100 ssd121
    0.6    8.2    0.0    0.1  0.0  0.3    0.0   29.7   0  19 ssd122
                    extended device statistics
    r/s    w/s   Mr/s   Mw/s wait actv wsvc_t asvc_t  %w  %b device
   63.6   75.0    2.2    0.6  0.0  4.4    0.0   31.9   0 100 7/md101
   63.6   75.0    2.2    0.6  0.0  4.4    0.0   31.9   0 100 7/md1
    1.4   97.6    0.0    0.8  0.0  2.2    0.0   21.9   0 100 6/md100
    1.4   97.6    0.0    0.8  0.0  2.2    0.0   21.9   0 100 6/md1
   12.6  169.0    0.5    1.3  0.0  4.0    0.0   22.2   0 100 4/md101
   12.6  169.0    0.5    1.3  0.0  4.0    0.0   22.3   0 100 4/md1
    0.0    1.6    0.0    0.0  0.0  0.1    0.0   49.0   0   5 1/md100
   85.0   34.6    1.1    0.3  0.0  2.9    0.0   24.1   0  82 ssd81
   89.6   40.4    1.1    0.3  0.0  2.8    0.0   21.6   0  85 ssd82
    0.0    1.6    0.0    0.0  0.0  0.1    0.0   49.0   0   5 ssd91
   12.6  169.0    0.5    1.3  0.0  4.0    0.0   22.2   0 100 ssd114
    0.4   67.2    0.0    0.5  0.0  1.2    0.0   18.0   0  83 ssd121
    1.0   30.4    0.0    0.2  0.0  0.9    0.0   30.1   0  70 ssd122

Noticed all the disk with 100% busy? Are there any storage expert out there who can confirm this?

UPDATE:
As a DBA, I am on a need to know basis; hence, not having all the required information.
From my understanding now, the migration was to a new sever connected to the existing storage.
The system admin believes this could be a cabling issue and the migration to new storage is yet to come.
This is on Solaris 10.

For comparison, this is for a healthy system

                    extended device statistics
    r/s    w/s   Mr/s   Mw/s wait actv wsvc_t asvc_t  %w  %b device
    0.0    0.2    0.0    0.0  0.0  0.0    0.0   10.1   0   0 md0
    0.0    0.2    0.0    0.0  0.0  0.0    0.0    9.6   0   0 md4
    0.0    0.2    0.0    0.0  0.0  0.0    0.0    0.3   0   0 md5
    0.0    0.2    0.0    0.0  0.0  0.0    0.0    9.5   0   0 md6
    0.0    0.2    0.0    0.0  0.0  0.0    0.0    0.5   0   0 md7
    0.0    0.2    0.0    0.0  0.0  0.0    0.0   10.0   0   0 md8
    0.0    0.8    0.0    0.0  0.0  0.0    0.0    0.3   0   0 sd1
    0.0    0.8    0.0    0.0  0.0  0.0    0.0    8.2   0   1 sd2
    7.7    0.0    5.6    0.0  0.0  0.1    0.0   10.0   0   8 ssd41
    6.5    0.2    1.6    0.0  0.0  0.0    0.0    4.8   0   3 ssd42
    0.0    0.8    0.0    0.0  0.0  0.0    0.0    0.7   0   0 ssd43
    0.2    0.0    0.0    0.0  0.0  0.0    0.0    1.7   0   0 ssd44
   10.7    0.2    6.4    0.0  0.0  0.1    0.0    7.1   0   8 ssd45
   35.1    0.2    0.3    0.0  0.0  0.2    0.0    4.7   0  17 ssd46
   27.2    0.2    0.2    0.0  0.0  0.1    0.0    2.0   0   5 ssd49
    1.4    0.2    0.0    0.0  0.0  0.0    0.0    3.4   0   1 ssd50
    5.7    1.0    0.0    0.0  0.0  0.0    0.0    2.6   0   2 ssd51
    0.0    0.8    0.0    0.0  0.0  0.0    0.0    0.6   0   0 ssd52
    3.0    6.7    0.8    0.2  0.0  0.0    0.0    2.3   0   2 ssd53
    0.6    1.0    0.0    0.0  0.0  0.0    0.0    0.9   0   0 ssd58
    0.8    1.4    0.0    0.0  0.0  0.0    0.0    1.0   0   0 ssd61
    0.0    1.8    0.0    0.0  0.0  0.0    0.0    0.6   0   0 ssd63
    0.0    0.2    0.0    0.0  0.0  0.0    0.0    0.5   0   0 ssd65
    0.4    0.0    0.0    0.0  0.0  0.0    0.0    3.5   0   0 ssd66
    4.7    0.0    4.6    0.0  0.0  0.0    0.0    7.5   0   3 ssd68
    0.6    0.0    0.6    0.0  0.0  0.0    0.0    7.2   0   0 ssd69
    6.1    0.8    4.4    0.0  0.0  0.0    0.0    4.5   0   3 ssd70
    7.5    0.2    6.2    0.0  0.0  0.0    0.0    6.2   0   5 ssd71
    0.2    0.0    0.0    0.0  0.0  0.0    0.0   10.8   0   0 ssd72
    6.3    0.6    6.2    0.0  0.0  0.0    0.0    6.4   0   4 ssd73
    8.9    0.0    5.8    0.0  0.0  0.1    0.0    8.1   0   7 ssd121
    5.9    0.4    1.9    0.0  0.0  0.0    0.0    4.1   0   3 ssd122
    0.0    1.6    0.0    0.0  0.0  0.0    0.0    0.8   0   0 ssd123
   10.1    0.2    6.3    0.0  0.0  0.1    0.0    7.5   0   8 ssd125
   38.5    0.0    0.5    0.0  0.0  0.2    0.0    5.1   0  20 ssd126

July 19, 2012

Know Filter Data

Filed under: 11g,oracle,performance — mdinh @ 2:12 am

I got a request on how to improve performance for table update with 33,617,240 rows running for 2.5 hours as shown:

UPDATE a
SET a.status =
(CASE WHEN a.dt1 IS NOT NULL THEN ‘PROCESSED’ WHEN a.dt2 IS NULL THEN NULL END);

32935613 rows updated.
Elapsed: 02:23:39.85

As written, 32,935,613 of 33,617,240 rows updated.

This is a good demonstration on how filtering early and understanding the data is a HUGE benefit.

If status is already ‘PROCESSED’, then is it necessary to update?
select count(*) from a where dt1 IS NOT NULL and status<>’PROCESSED’;

COUNT(*)
———-
14032

If status is already NULL, then is it necessary to update?
select count(*) from a where dt2 IS NULL and status IS NOT NULL;

COUNT(*)
———-
572

The answer to both questions is NO and we don’t want to peform blanket update.

The SQL can be written as:

UPDATE (
SELECT /*+ PARALLEL(a,4) FULL(a) */
status,
(CASE
WHEN dt1 IS NOT NULL THEN ‘PROCESSED’
WHEN dt2 IS NULL THEN NULL
END) new_status
FROM a
WHERE (dt1 IS NOT NULL AND status <> ‘PROCESSED’) OR (dt2 IS NULL AND status IS NOT NULL)
)
SET status=new_status;

14604 rows updated.
Elapsed: 00:04:43.04

The result is only 14,604 versus 32,935,613 rows updated.
Huge difference in performnace.

Without hints: 47s direct path read + 28s CPU
With hints: 17s direct path read + 13s CPU

Create a free website or blog at WordPress.com.