Home » RDBMS Server » Performance Tuning » Tune long running query (Oracle 10.2.0.4.0, SunOS)
Tune long running query [message #534295] Mon, 05 December 2011 02:38 Go to next message
Database admin
Messages: 365
Registered: September 2006
Location: india
Senior Member

Hi,

Can somebody help me in tuning the long running query which ran for 13733 seconds ?
I have attached the explain plan for the same.


Thanks

Re: Tune long running query [message #534296 is a reply to message #534295] Mon, 05 December 2011 02:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For any peformances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Many of us can't or don't want to download files, so post them inline in text mode and post them formatted.

Regards
Michel
Re: Tune long running query [message #534300 is a reply to message #534296] Mon, 05 December 2011 02:55 Go to previous messageGo to next message
Database admin
Messages: 365
Registered: September 2006
Location: india
Senior Member

Michel,

Thanks for your advice.


Quote:

SELECT NVL(SUM (CLAIM_DEALER_TOT_AMT_LOC), 0), NVL(SUM (CLAIM_DEALER_TOT_AMT_REG), 0), NVL(SUM (CLAIM_DEALER_TOT_AMT_GLOB), 0), NVL(SUM (FLAG_COUNT), 0), COUNT(CLAIM_NO) FROM(SELECT CV.CLAIM_DEALER_TOT_AMT_LOC, CV.CLAIM_DEALER_TOT_AMT_REG, CV.CLAIM_DEALER_TOT_AMT_GLOB, dbo.f_get_user_trans_flags_cnt (CV.claim_no, 'ZZPKMK') "FLAG_COUNT", CV.CLAIM_NO, CV.active_version_indicator, CV.repairing_business_unit_id, CV.process_status, CV.process_datetime, CV.repairing_service_agent_id_ic, ch.claim_type, CV.change_by_toolkit_ind, CH.LABOR_OPERATION_CD, CH.SERIALIZED_PART_ID, CH.VIN, CV.JOB_CARD_NO, CV.REPAIR_COMPLETION_DATE, CH.JOB_CARD_OPEN_DATE, CH.TECHNICIAN_NO, CH.CLAIM_SUBMISSION_DATE, CH.PARTS_RETURN_PROJECT_ID, CH.PARTS_RETURN_CREATE_DATE, CH.CAUSAL_PART_NO, CV.REPAIR_GROUP, CV.WEEK_ENDING, CV.INVOICE_NUMBER FROM CLAIM_HEADER CH, CLAIM_VERSION CV, (SELECT * FROM claim_reassign A WHERE ASSIGNED_DATE IN (SELECT MAX(ASSIGNED_DATE) FROM claim_reassign B WHERE B.claim_no = A.claim_no)) CR, (SELECT BUSINESS_UNIT_ID, SERVICE_AGENT_ID, USER_ID FROM DATA_SCOPE WHERE USER_ID = 'ZZPKMK') DS WHERE DS.SERVICE_AGENT_ID = CV.REPAIRING_SERVICE_AGENT_ID_IC AND DS.BUSINESS_UNIT_ID = CV.REPAIRING_BUSINESS_UNIT_ID AND CR.CLAIM_NO (+)= CV.CLAIM_NO AND CH.CLAIM_NO = CV.CLAIM_NO)CV


Explain plan for the above query

Execution Plan
Id Operation Name Rows Bytes TempSpc Cost (%CPU) Time Pstart Pstop TQ IN-OUT PQ Distrib
0 SELECT STATEMENT 2152 (100)
1 SORT AGGREGATE 1 94
2 PX COORDINATOR
3 PX SEND QC (RANDOM) :TQ10002 1 94 Q1,02 P->S QC (RAND)
4 SORT AGGREGATE 1 94 Q1,02 PCWP
5 HASH JOIN RIGHT OUTER 17M 1582M 2152 (3) 00:00:26 Q1,02 PCWP
6 BUFFER SORT Q1,02 PCWC
7 PX RECEIVE 194K 4946K 1216 (3) 00:00:15 Q1,02 PCWP
8 PX SEND BROADCAST :TQ10000 194K 4946K 1216 (3) 00:00:15 S->P BROADCAST
9 VIEW 194K 4946K 1216 (3) 00:00:15
10 HASH JOIN 194K 10M 7080K 1216 (3) 00:00:15
11 INDEX FAST FULL SCAN XPKCLAIM_REASSIGN 219K 4501K 158 (4) 00:00:02
12 VIEW VW_SQ_1 194K 6659K 267 (4) 00:00:04
13 HASH GROUP BY 194K 6468K 17M 267 (4) 00:00:04
14 INDEX FULL SCAN XPKCLAIM_REASSIGN 219K 7288K 267 (4) 00:00:04
15 TABLE ACCESS BY GLOBAL INDEX ROWID CLAIM_VERSION 205 9020 2 (0) 00:00:01 ROW L ROW L Q1,02 PCWP
16 NESTED LOOPS 17M 1145M 915 (1) 00:00:11 Q1,02 PCWP
17 BUFFER SORT Q1,02 PCWC
18 PX RECEIVE Q1,02 PCWP
19 PX SEND ROUND-ROBIN :TQ10001 S->P RND-ROBIN
20 INDEX RANGE SCAN XPKDATA_SCOPE 1756 42144 6 (0) 00:00:01
21 INDEX RANGE SCAN CLAIM_VERSION_SA_BU_PS_ACI_IDX 721 0 (0) Q1,02 PCWP



I hope it is clear now.
Re: Tune long running query [message #534301 is a reply to message #534300] Mon, 05 December 2011 03:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please follow what is explained and described in the link.

Regards
Michel
Re: Tune long running query [message #534305 is a reply to message #534301] Mon, 05 December 2011 03:46 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
That query is unreadable. You've been posting here long enough to know that code should be posted formatted in code tags.
Re: Tune long running query [message #534322 is a reply to message #534300] Mon, 05 December 2011 05:45 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
SELECT Nvl(SUM (claim_dealer_tot_amt_loc), 0), 
       Nvl(SUM (claim_dealer_tot_amt_reg), 0), 
       Nvl(SUM (claim_dealer_tot_amt_glob), 0), 
       Nvl(SUM (flag_count), 0), 
       COUNT(claim_no) 
FROM  (SELECT cv.claim_dealer_tot_amt_loc, 
              cv.claim_dealer_tot_amt_reg, 
              cv.claim_dealer_tot_amt_glob, 
              dbo.F_get_user_trans_flags_cnt (cv.claim_no, 'ZZPKMK') 
              "FLAG_COUNT", 
              cv.claim_no, 
              cv.active_version_indicator, 
              cv.repairing_business_unit_id, 
              cv.process_status, 
              cv.process_datetime, 
              cv.repairing_service_agent_id_ic, 
              ch.claim_type, 
              cv.change_by_toolkit_ind, 
              ch.labor_operation_cd, 
              ch.serialized_part_id, 
              ch.vin, 
              cv.job_card_no, 
              cv.repair_completion_date, 
              ch.job_card_open_date, 
              ch.technician_no, 
              ch.claim_submission_date, 
              ch.parts_return_project_id, 
              ch.parts_return_create_date, 
              ch.causal_part_no, 
              cv.repair_group, 
              cv.week_ending, 
              cv.invoice_number 
       FROM   claim_header ch, 
              claim_version cv, 
              (SELECT * 
               FROM   claim_reassign a 
               WHERE  assigned_date IN (SELECT MAX(assigned_date) 
                                        FROM   claim_reassign b 
                                        WHERE  b.claim_no = a.claim_no)) cr, 
              (SELECT business_unit_id, 
                      service_agent_id, 
                      user_id 
               FROM   data_scope 
               WHERE  user_id = 'ZZPKMK') ds 
       WHERE  ds.service_agent_id = cv.repairing_service_agent_id_ic 
              AND ds.business_unit_id = cv.repairing_business_unit_id 
              AND cr.claim_no (+) = cv.claim_no 
              AND ch.claim_no = cv.claim_no)cv 
Re: Tune long running query [message #534323 is a reply to message #534322] Mon, 05 December 2011 05:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And the explain plan? Smile

Regards
Michel
Re: Tune long running query [message #534330 is a reply to message #534323] Mon, 05 December 2011 06:24 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
That query is selecting a lot of columns that it's not using in the outer select. Removing them all, as well as the cr inline view, will probably help.
Re: Tune long running query [message #534332 is a reply to message #534330] Mon, 05 December 2011 06:32 Go to previous messageGo to next message
Database admin
Messages: 365
Registered: September 2006
Location: india
Senior Member

Ram,

Can you tell me how did you format that sql query ? and also please tell me how to format the explain plan .


Thanks
Re: Tune long running query [message #534336 is a reply to message #534332] Mon, 05 December 2011 06:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
224 posts and we already told you about 100 times how to do it.
It is useless to repeat it once more, if you are still unable to do it then you will NEVER be able to do it.

Regards
Michel

[Updated on: Mon, 05 December 2011 06:40]

Report message to a moderator

Re: Tune long running query [message #534339 is a reply to message #534332] Mon, 05 December 2011 06:48 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
balaji14 wrote on Mon, 05 December 2011 18:02
Ram,

Can you tell me how did you format that sql query ? and also please tell me how to format the explain plan .


Thanks



http://www.orafaq.com/forum/t/174502/136607/

http://www.dpriver.com/pp/sqlformat.htm


Sriram
Re: Tune long running query [message #534343 is a reply to message #534336] Mon, 05 December 2011 07:33 Go to previous messageGo to next message
Database admin
Messages: 365
Registered: September 2006
Location: india
Senior Member

I explored this time and learnt how to use the SQL formatter.

Quote:
SELECT Nvl(SUM (claim_dealer_tot_amt_loc), 0),
Nvl(SUM (claim_dealer_tot_amt_reg), 0),
Nvl(SUM (claim_dealer_tot_amt_glob), 0),
Nvl(SUM (flag_count), 0),
COUNT(claim_no)
FROM (SELECT cv.claim_dealer_tot_amt_loc,
cv.claim_dealer_tot_amt_reg,
cv.claim_dealer_tot_amt_glob,
dbo.F_get_user_trans_flags_cnt (cv.claim_no, 'ZZPKMK')
"FLAG_COUNT",
cv.claim_no,
cv.active_version_indicator,
cv.repairing_business_unit_id,
cv.process_status,
cv.process_datetime,
cv.repairing_service_agent_id_ic,
ch.claim_type,
cv.change_by_toolkit_ind,
ch.labor_operation_cd,
ch.serialized_part_id,
ch.vin,
cv.job_card_no,
cv.repair_completion_date,
ch.job_card_open_date,
ch.technician_no,
ch.claim_submission_date,
ch.parts_return_project_id,
ch.parts_return_create_date,
ch.causal_part_no,
cv.repair_group,
cv.week_ending,
cv.invoice_number
FROM claim_header ch,
claim_version cv,
(SELECT *
FROM claim_reassign a
WHERE assigned_date IN (SELECT MAX(assigned_date)
FROM claim_reassign b
WHERE b.claim_no = a.claim_no)) cr,
(SELECT business_unit_id,
service_agent_id,
user_id
FROM data_scope
WHERE user_id = 'ZZPKMK') ds
WHERE ds.service_agent_id = cv.repairing_service_agent_id_ic
AND ds.business_unit_id = cv.repairing_business_unit_id
AND cr.claim_no (+) = cv.claim_no
AND ch.claim_no = cv.claim_no)cv



Explain plan for the above query
--------------------


Operation   NAME   ROWS   bytes   tempspc  cost (%cpu)  TIME   pstart  pstop   tq   IN-OUT  pq distrib
   SELECT statement               2152 (100)
      sort aggregate      1   94
        px coordinator
          px send qc (random)   :TQ10002   1   94                  q1,02   p->s   qc (rand)
            sort aggregate      1   94                  q1,02   pcwp
              hash join right outer      17m  1582m     2152 (3)  00:00:26         q1,02   pcwp
                buffer sort                           q1,02   pcwc
                  px receive      194k  4946k     1216 (3)  00:00:15         q1,02   pcwp
                    px send broadcast   :TQ10000   194k  4946k     1216 (3)  00:00:15            s->p   broadcast
                      VIEW      194k  4946k     1216 (3)  00:00:15
                        hash join      194k  10m  7080k  1216 (3)  00:00:15
                          INDEX fast full scan   xpkclaim_reassign   219k  4501k     158 (4)  00:00:02
                          VIEW   vw_sq_1   194k  6659k     267 (4)  00:00:04
                            hash GROUP BY      194k  6468k  17m  267 (4)  00:00:04
                              INDEX full scan   xpkclaim_reassign   219k  7288k     267 (4)  00:00:04
                TABLE access BY global INDEX ROWID  claim_version   205   9020      2 (0)  00:00:01   ROW l   ROW l   q1,02   pcwp
                  nested loops      17m  1145m     915 (1)  00:00:11         q1,02   pcwp
                    buffer sort                           q1,02   pcwc
                      px receive                           q1,02   pcwp
                        px send round-robin   :TQ10001                           s->p   rnd-robin
                          INDEX RANGE scan   xpkdata_scope   1756   42144      6 (0)  00:00:01
                    INDEX RANGE scan   claim_version_sa_bu_ps_aci_idx   721         0 (0)           q1,02   pcwp



Can you provide the solution for this long running query?

I wasted my time in following your guidelines.

Anyways Thanks Ram ..

Thanks
Re: Tune long running query [message #534345 is a reply to message #534343] Mon, 05 December 2011 07:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I explored this time and learnt how to use the SQL formatter.

But you failed to put it formatted in your post.

Once agan read the link and post accordingly.

Regards
Michel
Re: Tune long running query [message #534347 is a reply to message #534343] Mon, 05 December 2011 07:46 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
balaji14 wrote on Mon, 05 December 2011 13:33

I wasted my time in following your guidelines.


You wasted your time? What about our time? We don't get paid to help you out, we're volunteers with day jobs, and you're not the only one asking for help.
The guidelines exist so that people can post information about problems in a way that everyone can follow, if you don't format an unreadable query we have to and we do have other things to do.

Do the following:
1) Follow my advice above about removing columns that aren't actually being used.
2) Post the revised query, formatted in code tags, not quote tags. Code tags give a monospaced font that makes code easier to read, that's why we tell people to use them.
3) Get the explain for the revised query using the following method:

SQL> explain plan for select * from dual;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3543395131

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.

SQL> 

Then post it here in code tags. The one you've provided above is unreadable.
Re: Tune long running query [message #534429 is a reply to message #534347] Tue, 06 December 2011 00:46 Go to previous messageGo to next message
Database admin
Messages: 365
Registered: September 2006
Location: india
Senior Member


Can you tell me how to find the columns that it's not using in the outer select as well as the cr inline view ?


SELECT Nvl(SUM (claim_dealer_tot_amt_loc), 0),
       Nvl(SUM (claim_dealer_tot_amt_reg), 0),
       Nvl(SUM (claim_dealer_tot_amt_glob), 0),
       Nvl(SUM (flag_count), 0),
       COUNT(claim_no)
FROM  (SELECT cv.claim_dealer_tot_amt_loc,
              cv.claim_dealer_tot_amt_reg,
              cv.claim_dealer_tot_amt_glob,
              dbo.F_get_user_trans_flags_cnt (cv.claim_no, 'ZZPKMK')
              "FLAG_COUNT",
              cv.claim_no,
              cv.active_version_indicator,
              cv.repairing_business_unit_id,
              cv.process_status,
              cv.process_datetime,
              cv.repairing_service_agent_id_ic,
              ch.claim_type,
              cv.change_by_toolkit_ind,
              ch.labor_operation_cd,
              ch.serialized_part_id,
              ch.vin,
              cv.job_card_no,
              cv.repair_completion_date,
              ch.job_card_open_date,
              ch.technician_no,
              ch.claim_submission_date,
              ch.parts_return_project_id,
              ch.parts_return_create_date,
              ch.causal_part_no,
              cv.repair_group,
              cv.week_ending,
              cv.invoice_number
       FROM   claim_header ch,
              claim_version cv,
              (SELECT *
               FROM   claim_reassign a
               WHERE  assigned_date IN (SELECT MAX(assigned_date)
                                        FROM   claim_reassign b
                                        WHERE  b.claim_no = a.claim_no)) cr,
              (SELECT business_unit_id,
                      service_agent_id,
                      user_id
               FROM   data_scope
               WHERE  user_id = 'ZZPKMK') ds
       WHERE  ds.service_agent_id = cv.repairing_service_agent_id_ic
              AND ds.business_unit_id = cv.repairing_business_unit_id
              AND cr.claim_no (+) = cv.claim_no
              AND ch.claim_no = cv.claim_no)cv 





Explain plan for the above query.


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
plan hash VALUE: 2733119754

--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------

| id  | operation                               | NAME
 | ROWS  | bytes |tempspc| cost (%cpu)| TIME     | pstart| pstop |    tq  |IN-ou
t| pq distrib |

--------------------------------------------------------------------------------

plan_table_output
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------

|   0 | SELECT statement                        |
 |     1 |    94 |       |  2183   (3)| 00:00:27 |       |       |        |
 |            |

|   1 |  sort aggregate                         |
 |     1 |    94 |       |            |          |       |       |        |
 |            |


plan_table_output
--------------------------------------------------------------------------------
|   2 |   px coordinator                        |
 |       |       |       |            |          |       |       |        |
 |            |

|   3 |    px send qc (random)                  | :TQ10002
 |     1 |    94 |       |            |          |       |       |  q1,02 | p->s
 | qc (rand)  |

|   4 |     sort aggregate                      |
 |     1 |    94 |       |            |          |       |       |  q1,02 | pcwp
 |            |

plan_table_output
--------------------------------------------------------------------------------

|*  5 |      hash join right outer              |
 |    20m|  1868m|       |  2183   (3)| 00:00:27 |       |       |  q1,02 | pcwp
 |            |

|   6 |       buffer sort                       |
 |       |       |       |            |          |       |       |  q1,02 | pcwc
 |            |

|   7 |        px receive                       |
 |   195k|  4957k|       |  1219   (3)| 00:00:15 |       |       |  q1,02 | pcwp

plan_table_output
--------------------------------------------------------------------------------
 |            |

|   8 |         px send broadcast               | :TQ10000
 |   195k|  4957k|       |  1219   (3)| 00:00:15 |       |       |        | s->p
 | broadcast  |

|   9 |          VIEW                           |
 |   195k|  4957k|       |  1219   (3)| 00:00:15 |       |       |        |
 |            |

|* 10 |           hash join                     |

plan_table_output
--------------------------------------------------------------------------------
 |   195k|    10m|  7096k|  1219   (3)| 00:00:15 |       |       |        |
 |            |

|  11 |            INDEX fast full scan         | xpkclaim_reassign
 |   219k|  4511k|       |   158   (4)| 00:00:02 |       |       |        |
 |            |

|  12 |            VIEW                         | vw_sq_1
 |   195k|  6673k|       |   268   (4)| 00:00:04 |       |       |        |
 |            |


plan_table_output
--------------------------------------------------------------------------------
|  13 |             hash GROUP BY               |
 |   195k|  6482k|    17m|   268   (4)| 00:00:04 |       |       |        |
 |            |

|  14 |              INDEX full scan            | xpkclaim_reassign
 |   219k|  7304k|       |   268   (4)| 00:00:04 |       |       |        |
 |            |

|  15 |       TABLE access BY global INDEX ROWID| claim_version
 |   243 | 10692 |       |     2   (0)| 00:00:01 | ROWID | ROWID |  q1,02 | pcwp
 |            |

plan_table_output
--------------------------------------------------------------------------------

|  16 |        nested loops                     |
 |    20m|  1351m|       |   939   (1)| 00:00:12 |       |       |  q1,02 | pcwp
 |            |

|  17 |         buffer sort                     |
 |       |       |       |            |          |       |       |  q1,02 | pcwc
 |            |

|  18 |          px receive                     |
 |       |       |       |            |          |       |       |  q1,02 | pcwp

plan_table_output
--------------------------------------------------------------------------------
 |            |

|  19 |           px send round-robin           | :TQ10001
 |       |       |       |            |          |       |       |        | s->p
 | rnd-robin  |

|* 20 |            INDEX RANGE scan             | xpkdata_scope
 |  1754 | 42096 |       |     7   (0)| 00:00:01 |       |       |        |
 |            |

|* 21 |         INDEX RANGE scan                | claim_version_sa_bu_ps_aci_idx

plan_table_output
--------------------------------------------------------------------------------
 |   726 |       |       |     0   (0)| 00:00:01 |       |       |  q1,02 | pcwp
 |            |

--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------


predicate information (IDENTIFIED BY operation id):
---------------------------------------------------


plan_table_output
--------------------------------------------------------------------------------
   5 - access("CR"."CLAIM_NO"(+)="CV"."CLAIM_NO")
  10 - access("ASSIGNED_DATE"="VW_COL_1" AND "CLAIM_NO"="A"."CLAIM_NO")
  20 - access("USER_ID"='ZZPKMK')
  21 - access("SERVICE_AGENT_ID"="CV"."REPAIRING_SERVICE_AGENT_ID_IC" AND "BUSIN
ESS_UNIT_ID"="CV"."REPAIRING_BUSINESS_UNIT_ID")


note
-----
   - SQL profile "SYS_SQLPROF_014d024b44550000" used FOR this statement




I hope the format is clear now.

Thanks

[Updated on: Tue, 06 December 2011 00:53]

Report message to a moderator

Re: Tune long running query [message #534431 is a reply to message #534429] Tue, 06 December 2011 01:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you REALLY think so?

Regards
Michel
Re: Tune long running query [message #534432 is a reply to message #534431] Tue, 06 December 2011 01:13 Go to previous messageGo to next message
Database admin
Messages: 365
Registered: September 2006
Location: india
Senior Member

Yes
Re: Tune long running query [message #534435 is a reply to message #534432] Tue, 06 December 2011 01:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe you didn't read and try to analyze your plan, then.

Regards
Michel

[Updated on: Tue, 06 December 2011 01:51]

Report message to a moderator

Re: Tune long running query [message #534448 is a reply to message #534429] Tue, 06 December 2011 03:57 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
balaji14 wrote on Tue, 06 December 2011 06:46

Can you tell me how to find the columns that it's not using in the outer select as well as the cr inline view ?

By reading it.
Look at each column in the inner select. See if it's name appears in the outer select, either in the actual select part or the where clause, if it's not mentioned then it's not used.
There's nothing complicated about this.

As for the explain plan - it is still hard to read. Use set lines in sqlplus to ensure that each line isn't split into two, or even worse three, which is what happened above.

EDIT: extended last sentence.

[Updated on: Tue, 06 December 2011 04:44]

Report message to a moderator

Re: Tune long running query [message #534554 is a reply to message #534448] Wed, 07 December 2011 04:27 Go to previous messageGo to next message
Database admin
Messages: 365
Registered: September 2006
Location: india
Senior Member

Thank you all
Re: Tune long running query [message #534561 is a reply to message #534554] Wed, 07 December 2011 04:45 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Does it mean You solved it ?

Sriram
Re: Tune long running query [message #534563 is a reply to message #534561] Wed, 07 December 2011 05:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
He also posted this in other forums but failed to mention that to share the answers he got elsewhere...

Regards
Michel
Re: Tune long running query [message #535025 is a reply to message #534563] Sun, 11 December 2011 08:21 Go to previous messageGo to next message
Database admin
Messages: 365
Registered: September 2006
Location: india
Senior Member

I didnt post the same question in other forums.

Michel,

Dont be smart and make others fool and spoil OP image.
I dont think i posted the same question in other forums.

[Updated on: Sun, 11 December 2011 08:22]

Report message to a moderator

Re: Tune long running query [message #535062 is a reply to message #535025] Mon, 12 December 2011 01:28 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
If you find the solution to your problem ..Please post it here ..It might help some other with similar problem.

Sriram
Re: Tune long running query [message #535225 is a reply to message #535062] Tue, 13 December 2011 00:05 Go to previous message
Database admin
Messages: 365
Registered: September 2006
Location: india
Senior Member

The problem isnt resolved.But no worries, the user hasnt come back for the update.

Thanks Sriram for your concern.
Previous Topic: Difficulty in using bind variable to check explain plan
Next Topic: Time Calculation in AWR report
Goto Forum:
  


Current Time: Thu Mar 28 17:04:22 CDT 2024