| Inner join query tuning [message #571168] |
Thu, 22 November 2012 00:07  |
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 #571241 is a reply to message #571193] |
Thu, 22 November 2012 06:51  |
cookiemonster
Messages: 13958
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 |
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 #571350 is a reply to message #571325] |
Sat, 24 November 2012 00:14 |
 |
Kevin Meade
Messages: 2103
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 |
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 .
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
