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 Go to next message
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 #549228 is a reply to message #549227] Wed, 28 March 2012 23:01 Go to previous messageGo to next message
BlackSwan
Messages: 21940
Registered: January 2009
Senior Member
Line #12 in EXPLAIN PLAN indicates that the majority of the data is being pulled across a REMOTE DBLINK.
Is this correct?

>Is there any way to reduce this consitent read?
Consistent Read is faster & less costly than doing actual I/O.
The way to reduce it is to have result set contain less data.
Re: loop questions [message #549229 is a reply to message #549228] Thu, 29 March 2012 00:23 Go to previous messageGo to next message
snowball
Messages: 229
Registered: April 2006
Location: China
Senior Member

Hi, blackswan
Yes, the dblink is using in this sql.
I notice that the cost mainly increaes at line #2, #3, #4(all about this is nested loop) , so that also may be where the consistent gets increased.

The result set, I am not sure if I can reduced. I am trying. Smile

Thanks very much.
Re: loop questions [message #551046 is a reply to message #549229] Sun, 15 April 2012 01:35 Go to previous message
Kevin Meade
Messages: 1907
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
Previous Topic: database statistics
Next Topic: ADDM report
Goto Forum:
  


Current Time: Thu Apr 17 14:26:06 CDT 2014

Total time taken to generate the page: 0.09500 seconds