| Oracle ROWID RANGE SCAN [message #647146] |
Wed, 20 January 2016 09:01  |
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 #647258 is a reply to message #647257] |
Sun, 24 January 2016 16:06  |
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 #647600 is a reply to message #647264] |
Wed, 03 February 2016 20:14 |
 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
