Home » RDBMS Server » Performance Tuning » Oracle ROWID RANGE SCAN (AIX 6)
Oracle ROWID RANGE SCAN [message #647146] Wed, 20 January 2016 09:01 Go to next message
sinida1984
Messages: 83
Registered: September 2007
Location: India
Member
Hi All,

Application query is getting very slow and is been running for more than 12 hours now. It has come in Long running query, only .6 % is completed. Could you please advise if there is something we can do to this query

SQL
UPDATE /*+ PARALLEL(A) */
mdq_tmp_mpan_mth_uc076 A
SET ( tmp_imp_exp_flag, tmp_llfc_dt ) = (SELECT DISTINCT B.tmp_imp_exp_flag,
B.tmp_llfc_dt
FROM mdq_tmp_mpan_uc076_proc B
WHERE B.tmp_mpan = A.tmp_mpan
AND
B.tmp_mth_dt = A.tmp_mth_dt
AND A.cal_dt = B.cal_dt)
WHERE EXISTS (SELECT B.tmp_imp_exp_flag,
B.tmp_llfc_dt
FROM mdq_tmp_mpan_uc076_proc B
WHERE B.tmp_mpan = A.tmp_mpan
AND B.tmp_mth_dt = A.tmp_mth_dt
AND A.cal_dt = B.cal_dt);

EXPLAIN PLAN is attached.

Long running sessions as shown below.

511 11:12:21 Rowid Range Scan: MDQA_OWNER.MDQ_TMP_MPAN_MTH_UC076: 635 out of 182624 Blocks done 0.347708953916243
505 11:12:21 Rowid Range Scan: MDQA_OWNER.MDQ_TMP_MPAN_MTH_UC076: 681 out of 130113 Blocks done 0.523391206105462
529 11:12:21 Rowid Range Scan: MDQA_OWNER.MDQ_TMP_MPAN_MTH_UC076: 661 out of 126177 Blocks done 0.523867265824992
513 11:12:21 Rowid Range Scan: MDQA_OWNER.MDQ_TMP_MPAN_MTH_UC076: 651 out of 297944 Blocks done 0.21849743575974
502 11:12:21 Rowid Range Scan: MDQA_OWNER.MDQ_TMP_MPAN_MTH_UC076: 669 out of 123911 Blocks done 0.539903640516177
504 11:12:21 Rowid Range Scan: MDQA_OWNER.MDQ_TMP_MPAN_MTH_UC076: 650 out of 166082 Blocks done 0.391372936260401
510 11:12:21 Rowid Range Scan: MDQA_OWNER.MDQ_TMP_MPAN_MTH_UC076: 674 out of 102254 Blocks done 0.659142918614431

Thanks And Regards
Sinida
Re: Oracle ROWID RANGE SCAN [message #647147 is a reply to message #647146] Wed, 20 January 2016 09:05 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member


ORAFAQ tuning below -
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/mv/msg/84315/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: Oracle ROWID RANGE SCAN [message #647185 is a reply to message #647147] Thu, 21 January 2016 07:33 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
Dear,

You can read following URL.Really there is very good post by Kevin Meade.Please read it.

http://www.orafaq.com/forum/t/84315/
Re: Oracle ROWID RANGE SCAN [message #647230 is a reply to message #647146] Sat, 23 January 2016 10:55 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
What is your Oracle Release?
Re: Oracle ROWID RANGE SCAN [message #647231 is a reply to message #647230] Sat, 23 January 2016 11:24 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
What is the main wait event your session waiting on?
Re: Oracle ROWID RANGE SCAN [message #647257 is a reply to message #647231] Sun, 24 January 2016 12:51 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
If you have Diagnostic Pack license, run please the following sql and upload the result:

select * from (select sql_id, count(*) from dba_hist_active_sess_history where event='db file sequential read' and sql_plan_operation||' '||sql_plan_options = 'TABLE ACCESS FULL' group by sql_id order by 2 desc) where rownum <= 50;
Re: Oracle ROWID RANGE SCAN [message #647258 is a reply to message #647257] Sun, 24 January 2016 16:06 Go to previous messageGo to next message
sinida1984
Messages: 83
Registered: September 2007
Location: India
Member
Hi,

The version of oracle is Oracle 10g, hence not able to execute above SQL, since those ( sql_plan_operation, sql_plan_options ) columns are not available in the datadictionary table.

The table we are updating has around 18760331 rows.

Thanks
Sinida
Re: Oracle ROWID RANGE SCAN [message #647260 is a reply to message #647258] Sun, 24 January 2016 23:48 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
OK, I didn't expect, that you use 10g. What is about wait events?

You can use this script for finding them.
Re: Oracle ROWID RANGE SCAN [message #647264 is a reply to message #647260] Mon, 25 January 2016 00:57 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
Send please the details requested of BlackSwan.
Re: Oracle ROWID RANGE SCAN [message #647600 is a reply to message #647264] Wed, 03 February 2016 20:14 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Here is some free material to help you get started with SQL Tuning, and to help you interact here with the team at ORAFaq better. These materials all are related to my book on SQL Tuning. If after previewing the materials you want the book, it is available on Amazon (now with a cheap Kindle version).

Provided below are:

1. the first chapter of the book.  Reading this will help you decide if the book is something you are interested in before you spend money on it.
2. the scripts from the book.  You can use these in your tuning work regardless of it you purchase the book or not later.  These are free.
3. an organizational document that will help you record the significant events of a SQL Tuning session so you can explain to someone else later how you solved a problem.
4. a brief description of the kind of info you will want to provide to ORAFaq, when you are looking for detailed tuning help.

These artifacts are free, you do not need to buy the book to use them, and you can give them to others freely as well. Good luck. Kevin
Previous Topic: Oracle SQL performance analysis
Next Topic: Sql Query Tuning
Goto Forum:
  


Current Time: Fri Mar 29 08:46:25 CDT 2024