Home » RDBMS Server » Performance Tuning » loop questions (Oracle 10.2.0.4.0 , AIX 5.3 64bit)
| loop questions [message #549227] |
Wed, 28 March 2012 22:55  |
snowball
Messages: 229
Registered: April 2006
Location: China
|
Senior Member |
|
|
Hi, folks
We have this sql, the consistent read is very large.
Here is the execution plan:
SQL> r
1 select a.id_no,
2 e.detail_type,
3 trim(e.detail_code),
4 to_char(c.eff_date,'YYYYMMDDHH24MISS') begin_time,
5 to_char(c.exp_date,'YYYYMMDDHH24MISS') end_time,
6 e.fav_order,
7 trim(to_char(d.offer_id)) mode_code,
8 a.phone_no,
9 decode(d.offer_type,'10','0','20','0','30','0','1') mode_flag
10 FROM
11 dcustmsg a,
12 dac_group_instance_member_acc b,
13 dac_group_instance_acc c,
14 product_offer d,
15 pricing_combine e,
16 user_price_rel f
17 where a.id_no=b.serv_id
18 and b.group_id=c.group_id
19 and length(trim(e.group_flag)) = 2
20 and c.offer_id=d.offer_id
21 and d.pricing_plan_id=e.price_plan_no
22 and e.detail_type=any('z','m')
23 and e.group_flag=f.group_flag
24 and a.sm_code=f.smcode
25 and substr(a.run_code,2,1)<'a'
26 and a.run_code<>'II'
27 and exists (select * from acct_host_route e where e.acct_home='home1' and e.route_seg=substr(a.phone_no,1,7))
28 and b.eff_date<add_months(to_date('201203'||'01000000','YYYYMMDDHH24MISS'),1)
29 and c.eff_date<add_months(to_date('201203'||'01000000','YYYYMMDDHH24MISS'),1)
30 and length(trim(a.phone_no))=11
31 and b.exp_date>to_date('201203'||'01000000','YYYYMMDDHH24MISS')
32 and c.exp_date>to_date('201203'||'01000000','YYYYMMDDHH24MISS')
33 AND e.BEGIN_TIME<add_months(to_date('201203'||'01000000','YYYYMMDDHH24MISS'),1)
34 AND e.END_TIME>to_date('201203'||'01000000','YYYYMMDDHH24MISS')
35* and substr(a.phone_no,1,7) between '0901000' and '0903618'
138 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3294587573
-------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | Inst |IN-OUT|
-------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 223 | 21099 (1)| 00:04:14 | | | | |
|* 1 | TABLE ACCESS BY INDEX ROWID | USER_PRICE_REL | 1 | 6 | 1 (0)| 00:00:01 | | | | |
| 2 | NESTED LOOPS | | 1 | 223 | 21099 (1)| 00:04:14 | | | | |
| 3 | NESTED LOOPS | | 1 | 217 | 21098 (1)| 00:04:14 | | | | |
| 4 | NESTED LOOPS | | 8400 | 1525K| 4288 (1)| 00:00:52 | | | | |
| 5 | NESTED LOOPS | | 1390 | 192K| 778 (2)| 00:00:10 | | | | |
|* 6 | HASH JOIN | | 123 | 12054 | 403 (2)| 00:00:05 | | | | |
|* 7 | TABLE ACCESS FULL | PRICING_COMBINE | 111 | 4551 | 248 (3)| 00:00:03 | | | | |
| 8 | MERGE JOIN CARTESIAN | | 24400 | 1358K| 212 (29)| 00:00:03 | | | | |
| 9 | SORT UNIQUE | | 20 | 360 | 4 (0)| 00:00:01 | | | | |
|* 10 | TABLE ACCESS FULL | ACCT_HOST_ROUTE | 20 | 360 | 4 (0)| 00:00:01 | | | | |
| 11 | BUFFER SORT | | 24400 | 929K| 208 (29)| 00:00:03 | | | | |
| 12 | REMOTE | PRODUCT_OFFER | 24400 | 929K| 149 (1)| 00:00:02 | | | LINK_~ | R->S |
|* 13 | TABLE ACCESS BY INDEX ROWID | DAC_GROUP_INSTANCE_ACC | 11 | 484 | 5 (0)| 00:00:01 | | | | |
|* 14 | INDEX RANGE SCAN | DAC_GROUP_INSTANCE_ACC_OI | 11 | | 2 (0)| 00:00:01 | | | | |
|* 15 | TABLE ACCESS BY INDEX ROWID | DAC_GROUP_INSTANCE_MEMBER_ACC | 6 | 264 | 5 (0)| 00:00:01 | | | | |
|* 16 | INDEX RANGE SCAN | DAC_GROUP_MEMBER_ACC_GI | 6 | | 2 (0)| 00:00:01 | | | | |
| 17 | PARTITION RANGE ITERATOR | | 1 | 31 | 2 (0)| 00:00:01 | KEY | KEY | | |
|* 18 | TABLE ACCESS BY LOCAL INDEX ROWID| DCUSTMSG | 1 | 31 | 2 (0)| 00:00:01 | KEY | KEY | | |
|* 19 | INDEX UNIQUE SCAN | DCUSTMSG_PK | 1 | | 1 (0)| 00:00:01 | KEY | KEY | | |
|* 20 | INDEX RANGE SCAN | USER_PRICE_REL_SC | 1 | | 0 (0)| 00:00:01 | | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("E"."GROUP_FLAG"="F"."GROUP_FLAG")
6 - access("D"."PRICING_PLAN_ID"="E"."PRICE_PLAN_NO")
7 - filter(LENGTH(TRIM("E"."GROUP_FLAG"))=2 AND ("E"."DETAIL_TYPE"='m' OR "E"."DETAIL_TYPE"='z') AND "E"."BEGIN_TIME"<TO_DATE(' 2012-04-01
00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "E"."END_TIME">TO_DATE(' 2012-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
10 - filter("E"."ROUTE_SEG"<='0903618' AND "E"."ACCT_HOME"='home1' AND "E"."ROUTE_SEG">='0901000')
13 - filter("C"."EFF_DATE"<TO_DATE(' 2012-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "C"."EXP_DATE">TO_DATE(' 2012-03-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))
14 - access("C"."OFFER_ID"="D"."OFFER_ID")
15 - filter("B"."EFF_DATE"<TO_DATE(' 2012-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "B"."EXP_DATE">TO_DATE(' 2012-03-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))
16 - access("B"."GROUP_ID"="C"."GROUP_ID")
18 - filter(SUBSTR("A"."RUN_CODE",2,1)<'a' AND SUBSTR("A"."PHONE_NO",1,7)>='0901000' AND SUBSTR("A"."PHONE_NO",1,7)<='0903618' AND
LENGTH(TRIM("A"."PHONE_NO"))=11 AND "A"."RUN_CODE"<>'II' AND "E"."ROUTE_SEG"=SUBSTR("A"."PHONE_NO",1,7))
19 - access("A"."ID_NO"="B"."SERV_ID")
20 - access("A"."SM_CODE"="F"."SMCODE")
Remote SQL Information (identified by operation id):
----------------------------------------------------
12 - SELECT "OFFER_ID","PRICING_PLAN_ID","OFFER_TYPE" FROM "PRODUCT"."PRODUCT_OFFER" "D" (accessing 'link1' )
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10881487 consistent gets
0 physical reads
260 redo size
8391 bytes sent via SQL*Net to client
591 bytes received via SQL*Net from client
11 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
138 rows processed
Here are the table records:
dcustmsg 4,739,081
dac_group_instance_member_acc 1,568,465
dac_group_instance_acc 314,989
product_offer 24,487
pricing_combine 82,419
user_price_rel 9
I found most of the cost comes from the nested loop and I am not sure if the consistent gets related with this.
Is there any way to reduce this consitent read?
Please give me some idea to tunning this sql.
Thanks very much.
|
|
|
|
|
|
|
|
| Re: loop questions [message #551046 is a reply to message #549229] |
Sun, 15 April 2012 01:35  |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
Forget about the term CONSISTENT GETS. It just tells you that Oracle is fetching data in a way that your query is seeing a consistent view of the data as of the time your query was issued. Unless you have transactions on your database changing the data while you are trying to read it, this means nothing significant from a performance perspective. So don't worry about that term.
Instead, try this simplified tuning strategy; it should help you through 95% of your tuning problems for long running queries.
1) If you are fetching < 2% of the rows in a table, you may consider using an index to fetch those rows. Otherwise, lean towards FULL TABLE SCAN as the way to get data from those tables.
2) Let Oracle dedice how you join two rowsets eg. if the plan should use NESTED LOOP w/ INDEX LOOKUP (see #1 above) to do a join, or if the join should be done using HASH JOIN instead. AS long as the CARDINALITY of the steps looks correct, then Oracle's choice of join strategy is likely correct too.
3) If the cardinality of the step feeding a NESTED LOOP JOIN or series of same, is way out of whack (an order of magnitude or more), then you likely have some issue that when corrected will change the join into a HASH JOIN and you will see an improvement in query times accordingly. One way to know is to run the query with full stats collection on and capture the actual rows returned so you can compare to estimated cardinalities in the plan. Another is to break the query up manually and run pieces to find out. I use both methods as circumstances dictate.
Good luck. Kevin
|
|
|
|
Goto Forum:
Current Time: Sun Jan 26 14:39:32 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
