Home » RDBMS Server » Performance Tuning » Inner join query tuning (10.2.0.3, windows XP)
Inner join query tuning [message #571168] Thu, 22 November 2012 00:07 Go to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
Dear All,

SQL> explain plan for
  2  select
  3         iph.rih_suid,
  4         ml.lot_lc_suid,
  5         1 main_sr_no,
  6         1 sub_sr_no,
  7         max(mlc.lc_name||' - '||mlc.lc_code) location,
  8         ma.acc_suid,
  9         max(ma.acc_name) acc_name,
 10         0 dr_local,
 11         round(sum(ipd.rid_our_amountlc), 2) cr_local,
 12         0 dr_intl,
 13         round(sum(ipd.rid_our_amountusd), 2) cr_intl
 14  from
 15         i_return_h iph
 16         inner join i_return_d ipd on iph.rih_suid = ipd.rid_rih_suid
 17         inner join i_purchase_d pd on ipd.rid_invoice_detail_suid = pd.pid_suid
 18         inner join m_lot ml on pd.pid_lot_suid = ml.lot_suid
 19         inner join m_commodity mc on ml.lot_comm_suid = mc.comm_suid
 20         inner join m_account ma on mc.comm_purchase_acc_suid = ma.acc_suid
 21         inner join m_location mlc on ml.lot_lc_suid = mlc.lc_suid
 22         and ipd.isactive = 'Y'
 23         and iph.isactive = 'Y'
 24         and ml.lot_sect_suid = mc.comm_sect_suid
 25         and  mc.comm_sect_suid =  ma.acc_sect_suid
 26  where
 27         1 = 1
 28         and ipd.rid_invoice_suid = 1296--pih_suid
 29         and iph.rih_company_suid = 11--pih_company_suid
 30  group by
 31        iph.rih_suid,
 32        ma.acc_suid,
 33        ml.lot_lc_suid
 34  ;

Explained.

SQL>  @D:\oracle\product\10.20.2\DIS_A\RDBMS\ADMIN\utlxpls.sql;

PLAN_TABLE_OUTPUT                                                                                                                                                                                       
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 677203770                                                                                                                                                                              
                                                                                                                                                                                                        
------------------------------------------------------------------------------------------------                                                                                                        
| Id  | Operation                       | Name         | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                        
------------------------------------------------------------------------------------------------                                                                                                        
|   0 | SELECT STATEMENT                |              |     1 |    94 |    18  (17)| 00:00:01 |                                                                                                        
|   1 |  HASH GROUP BY                  |              |     1 |    94 |    18  (17)| 00:00:01 |                                                                                                        
|   2 |   NESTED LOOPS                  |              |     1 |    94 |    17  (12)| 00:00:01 |                                                                                                        
|   3 |    NESTED LOOPS                 |              |     1 |    85 |    16  (13)| 00:00:01 |                                                                                                        
|   4 |     NESTED LOOPS                |              |     1 |    67 |    15  (14)| 00:00:01 |                                                                                                        
|*  5 |      HASH JOIN                  |              |     1 |    53 |    14  (15)| 00:00:01 |                                                                                                        
|*  6 |       HASH JOIN                 |              |     4 |   168 |    10  (10)| 00:00:01 |                                                                                                        
|*  7 |        HASH JOIN                |              |     4 |   104 |     7  (15)| 00:00:01 |                                                                                                        
|*  8 |         TABLE ACCESS FULL       | I_RETURN_D   |     4 |    72 |     3   (0)| 00:00:01 |                                                                                                        
|   9 |         TABLE ACCESS FULL       | I_PURCHASE_D |    53 |   424 |     3   (0)| 00:00:01 |                                                                                                        
|  10 |        TABLE ACCESS FULL        | M_LOT        |   163 |  2608 |     3   (0)| 00:00:01 |                                                                                                        
|  11 |       TABLE ACCESS FULL         | M_COMMODITY  |    16 |   176 |     3   (0)| 00:00:01 |                                                                                                        
|  12 |      TABLE ACCESS BY INDEX ROWID| M_LOCATION   |     1 |    14 |     1   (0)| 00:00:01 |                                                                                                        
|* 13 |       INDEX UNIQUE SCAN         | LC_SUID_PK   |     1 |       |     0   (0)| 00:00:01 |                                                                                                        
|* 14 |     TABLE ACCESS BY INDEX ROWID | M_ACCOUNT    |     1 |    18 |     1   (0)| 00:00:01 |                                                                                                        
|* 15 |      INDEX UNIQUE SCAN          | ACC_SUID_PK  |     1 |       |     0   (0)| 00:00:01 |                                                                                                        
|* 16 |    TABLE ACCESS BY INDEX ROWID  | I_RETURN_H   |     1 |     9 |     1   (0)| 00:00:01 |                                                                                                        
|* 17 |     INDEX UNIQUE SCAN           | RIH_SUID_PK  |     1 |       |     0   (0)| 00:00:01 |                                                                                                        
------------------------------------------------------------------------------------------------                                                                                                        
                                                                                                                                                                                                        
Predicate Information (identified by operation id):                                                                                                                                                     
---------------------------------------------------                                                                                                                                                     
                                                                                                                                                                                                        
   5 - access("ML"."LOT_SECT_SUID"="MC"."COMM_SECT_SUID" AND                                                                                                                                            
              "ML"."LOT_COMM_SUID"="MC"."COMM_SUID")                                                                                                                                                    
   6 - access("PD"."PID_LOT_SUID"="ML"."LOT_SUID")                                                                                                                                                      
   7 - access("IPD"."RID_INVOICE_DETAIL_SUID"="PD"."PID_SUID")                                                                                                                                          
   8 - filter("IPD"."RID_INVOICE_SUID"=1296 AND "IPD"."ISACTIVE"='Y')                                                                                                                                   
  13 - access("ML"."LOT_LC_SUID"="MLC"."LC_SUID")                                                                                                                                                       
  14 - filter("MC"."COMM_SECT_SUID"="MA"."ACC_SECT_SUID")                                                                                                                                               
  15 - access("MC"."COMM_PURCHASE_ACC_SUID"="MA"."ACC_SUID")                                                                                                                                            
  16 - filter("IPH"."ISACTIVE"='Y' AND "IPH"."RIH_COMPANY_SUID"=11)                                                                                                                                     
  17 - access("IPD"."RID_RIH_SUID"="IPH"."RIH_SUID")                                                                                                                                                    

38 rows selected.



how to tune this query to reduce cpu% ?

Waiting for your reply...

Thanks,
Ishika
Re: Inner join query tuning [message #571193 is a reply to message #571168] Thu, 22 November 2012 02:28 Go to previous messageGo to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
please assist me on this...

Thanks
Re: Inner join query tuning [message #571241 is a reply to message #571193] Thu, 22 November 2012 06:51 Go to previous messageGo to next message
cookiemonster
Messages: 11203
Registered: September 2008
Location: Rainy Manchester
Senior Member
According to the explain plan that query is highly efficient and doesn't need tuning.
Did you run the explain plan on a relatively empty database?
If so you need to run the explain plan in a DB with a representative amount of data.
Re: Inner join query tuning [message #571305 is a reply to message #571241] Fri, 23 November 2012 03:21 Go to previous messageGo to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
Yes.
Here, database is having very less data. That's y i want to know how to reduce the CPU %? As of now, it is excellent. What would be the solution for reducing CPU%?

Waiting for your reply...
Re: Inner join query tuning [message #571307 is a reply to message #571305] Fri, 23 November 2012 03:42 Go to previous messageGo to next message
Roachcoach
Messages: 1226
Registered: May 2010
Location: UK
Senior Member
What problem are you trying to solve...?

I have a strong feeling you're misunderstanding the CPU% column...
Re: Inner join query tuning [message #571325 is a reply to message #571307] Fri, 23 November 2012 06:11 Go to previous messageGo to next message
cookiemonster
Messages: 11203
Registered: September 2008
Location: Rainy Manchester
Senior Member
To put it another way - the explain shows a sql that needs no tuning. Why do you think it does?
Re: Inner join query tuning [message #571350 is a reply to message #571325] Sat, 24 November 2012 00:14 Go to previous messageGo to next message
Kevin Meade
Messages: 1957
Registered: December 1999
Location: Connecticut USA
Senior Member
Ishika what is your question?

how to reduce CPU for the query plan you showed us? Can't answer that. Depends upon if he plan is right or not for you data volumes.

If you had millions of rows in the tables (location, account, return history?) then FULL TABLE SCAN and HASH JOIN will result in less CPU for such data volume.

But you have not told us that is the case, and even if you did, we would tell you to run it first and see if oracle did the right thing for you and thus there would be nothing for you to do anyway.

But I guess a lot becase while you have been "waiting for our reply" we have been trying to figure out what you want from this ambiguous post. So please give us more information.

Also, just my asking, I see from your other post (thanks for quickly clearing that up), you had triggers on the table in question. Will you be running triggers on these tables too?

Kevin

[Updated on: Sat, 24 November 2012 00:18]

Report message to a moderator

Re: Inner join query tuning [message #571355 is a reply to message #571350] Sat, 24 November 2012 05:55 Go to previous message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
Dear Kevin Meade,

My Query is very optimized. I have no problem with this query. Actually my post would confused you all.
I just want to know how to reduce CPU % of the query? Do this column suggest something about the performance?

I read the below link of orafaq and come to know that it will not suggest more about the plan.

http://orafaq.com/node/2746


Thanks kevin for your decent reply. Also thanks to all .
Previous Topic: Oracle memory parameters
Next Topic: TKprof generation for short duration
Goto Forum:
  


Current Time: Thu Nov 27 23:58:27 CST 2014

Total time taken to generate the page: 0.09686 seconds