Home » RDBMS Server » Performance Tuning » sql performance issue.
sql performance issue. [message #274489] Tue, 16 October 2007 04:03 Go to next message
Messages: 24
Registered: September 2007
Location: Chennai,India
Junior Member
Is there anyway to improve this query...
do i need to drop/create the index on the table ps_pay_check,.
Will it improve the performance .. plz help.

SQL> select count(1) from ps_pay_check;




SQL> UPDATE ps_pay_check a

  2     SET (a.name, a.paycheck_name) =

  3            (SELECT b.new_name, b.new_name paycheck_name

  4               FROM hx_names_stg b

  5              WHERE b.old_name = a.name

  6                AND b.old_name = a.paycheck_name

  7                AND ROWNUM < 2)


  9                    FROM hx_names_stg b

 10                   WHERE b.old_name = a.name

 11                     AND b.old_name = a.paycheck_name

 12                     AND ROWNUM < 2)

 13  /

473273 rows updated.
Elapsed: 00:18:18.04

Execution Plan


   0      UPDATE STATEMENT Optimizer=CHOOSE (Cost=2131 Card=31904 Byte


   1    0   UPDATE OF 'PS_PAY_CHECK'
   2    1     FILTER
   3    2       TABLE ACCESS (FULL) OF 'PS_PAY_CHECK' (Cost=2131 Card=
          31904 Bytes=1020928)

   4    2       COUNT (STOPKEY)
   5    4         FILTER
   6    5           INDEX (RANGE SCAN) OF 'HX_NAMES_STG_IDX_003' (NON-

          UNIQUE) (Cost=1 Card=2 Bytes=32)

   7    0   COUNT (STOPKEY)
   8    7     FILTER

          3 Card=2 Bytes=62)

  10    9         INDEX (RANGE SCAN) OF 'HX_NAMES_STG_IDX_003' (NON-UN

          IQUE) (Cost=1 Card=2)



       2904  recursive calls
    3502430  db block gets
     639294  consistent gets
      30572  physical reads
  489720600  redo size
        865  bytes sent via SQL*Net to client
       1023  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
     473273  rows processed

[Updated on: Tue, 16 October 2007 07:30] by Moderator

Report message to a moderator

Re: sql performance issue. [message #274496 is a reply to message #274489] Tue, 16 October 2007 04:24 Go to previous messageGo to next message
Messages: 2844
Registered: May 2007
Location: Scotland
Senior Member
Read the guidelines, format your code and miss out the IM speak (e.g. plz is not a word)
Re: sql performance issue. [message #274511 is a reply to message #274489] Tue, 16 October 2007 04:46 Go to previous messageGo to next message
Michel Cadot
Messages: 65134
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Re: sql performance issue. [message #274561 is a reply to message #274511] Tue, 16 October 2007 07:53 Go to previous messageGo to next message
Messages: 3727
Registered: October 2005
Location: Melbourne, Australia
Senior Member
It is processing 400K rows, and every row runs the sub-query TWICE. That's 800K+ SQLs you're running. 18 minutes is SENSATIONAL performance.

If you hope to do better, you'll have to remove the nesting (ie. correlated sub-queries).

Since you didn't mention your version, I'll assume you're on 10g (although judging from the Explain Plan format, I doubt it is 10.2).
MERGE INTO ps_pay_check p
    SELECT a.rowid AS rid, b.new_name
    FROM ps_pay_check a
    JOIN (
        SELECT old_name, MAX(new_name) AS new_name
        FROM hx_names_stg
        WHERE old_name = paycheck_name
        GROUP BY old_name
    ) b ON (b.old_name = a.name)
    WHERE a.name = a.paycheck_name
) s
ON p.rowid = s.rid
SET name = s.new_name
,   paycheck_name = s.new_name

If you are on 9i, you will need to add a dummy WHEN NOT MATCHED clause - it should do nothing because of the ROWID join.

If you are on 8i, you could stage the inner query above in a global temporary table with a PK on old_name and perform an updateable join view (do a search).

Ross Leishman
Re: sql performance issue. [message #274580 is a reply to message #274489] Tue, 16 October 2007 08:41 Go to previous message
Messages: 32
Registered: June 2007
Would the result be different if you omit the where clause on the outer table? Your subquery has got the same conditions
Previous Topic: Reading DBMS_XPLAN output
Next Topic: Regarding sort optimsation (merged)
Goto Forum:

Current Time: Wed Aug 16 14:39:57 CDT 2017

Total time taken to generate the page: 0.08901 seconds