OraFAQ Forum: Performance Tuning » Tuning help needed

Home » RDBMS Server » Performance Tuning » Tuning help needed (Oracle 11.1.0.7.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Tuning help needed [message #522446]

Fri, 09 September 2011 13:04

naveenreddy
Messages: 10
Registered: August 2011

Junior Member

/* Formatted on 9/8/2011 1:42:10 PM (QP5 v5.115.810.9015) */
WITH replist
       AS (SELECT   DISTINCT rep_set.FILTER_TOKEN
             FROM   (SELECT   DISTINCT
                              wdsd.data_setting_id, wdsd.FILTER_TOKEN  --- RR2
                       FROM   WEB_DATA_SETTING_DETAIL wdsd,
                              (SELECT   inst_query_id,
                                        NVL (y.data_setting_id,
                                             X.data_setting_id)
                                           data_setting_id
                                 FROM   WEB_INSTANTIABLE_QUERY y,
                                        (SELECT   DATA_SETTING_ID
                                           FROM   WEB_DATA_SETTING
                                          WHERE   BROKER_ID = 48270
                                                  AND DEFAULT_YN = 'Y') X
                                WHERE   inst_query_id = 1385776) wiq
                      WHERE   wiq.data_setting_id = wdsd.data_setting_id)
                    rep_set),
    actlist
       AS (SELECT                                   /*+ use_hash(wbxt wtxa) */
                 account_id
             FROM   web_team_x_account wtxa, web_broker_x_team wbxt
            WHERE   wbxt.broker_id = 48270 AND wtxa.team_id = wbxt.team_id),
    rslt AS (SELECT   act_id, cash_value
               FROM   account_summary am
              WHERE   attr2 IN (SELECT   FILTER_TOKEN FROM replist))
SELECT                                                    /* use_hash(rslt) */
      SUM (cash_value)
  FROM   rslt
 WHERE   EXISTS (SELECT   '1'
                   FROM   actlist
                  WHERE   account_id = act_id);
                  
                  
                  
PLAN_TABLE_OUTPUT

Plan hash value: 900710482
 
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                               |     1 |   131 |  7906   (7)| 00:01:35 |
|   1 |  SORT AGGREGATE                    |                               |     1 |   131 |            |          |
|   2 |   NESTED LOOPS                     |                               |       |       |            |          |
|   3 |    NESTED LOOPS                    |                               |   104 | 13624 |   105   (1)| 00:00:02 |
|   4 |     VIEW                           | VW_NSO_1                      |    12 |  1224 |     6   (0)| 00:00:01 |
|   5 |      SORT UNIQUE                   |                               |    12 |   504 |            |          |
|   6 |       NESTED LOOPS                 |                               |    12 |   504 |     6   (0)| 00:00:01 |
|   7 |        NESTED LOOPS                |                               |     1 |    23 |     4   (0)| 00:00:01 |
|*  8 |         TABLE ACCESS BY INDEX ROWID| WEB_DATA_SETTING              |     1 |    12 |     2   (0)| 00:00:01 |
|*  9 |          INDEX RANGE SCAN          | WEB_DATASETNG_I_BROKER_ID     |     1 |       |     1   (0)| 00:00:01 |
|* 10 |         INDEX RANGE SCAN           | WEB_INST_QRY_N_DSID           |     1 |    11 |     2   (0)| 00:00:01 |
|* 11 |        INDEX RANGE SCAN            | WEBDATASETDTL_PK_TEAM         |    12 |   228 |     2   (0)| 00:00:01 |
|* 12 |     INDEX RANGE SCAN               | ACCOUNT_SUMM_I_ATT_MAR_ACT_ID |     9 |       |     2   (0)| 00:00:01 |
|* 13 |      HASH JOIN                     |                               |     1 |    31 |     8  (13)| 00:00:01 |
|* 14 |       INDEX RANGE SCAN             | WTT_PK                        |     3 |    57 |     4   (0)| 00:00:01 |
|* 15 |       INDEX RANGE SCAN             | WBXT_PK_BROKER_TEAM_ID1       |    13 |   156 |     3   (0)| 00:00:01 |
|  16 |    TABLE ACCESS BY INDEX ROWID     | ACCOUNT_SUMMARY               |     9 |   261 |    11   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   8 - filter("DEFAULT_YN"='Y')
   9 - access("BROKER_ID"=48270)
  10 - access("INST_QUERY_ID"=1385776)
  11 - access("WDSD"."DATA_SETTING_ID"=NVL("Y"."DATA_SETTING_ID","DATA_SETTING_ID"))
  12 - access("ATTR2"="FILTER_TOKEN")
       filter( EXISTS (SELECT /*+ USE_HASH ("WBXT") USE_HASH ("BO_TEAM_X_ACCOUNT") */ 0 FROM 
              "WEB_OWNER"."WEB_BROKER_X_TEAM" "WBXT",BO_OWNER_INST_10274_1."BO_TEAM_X_ACCOUNT" "BO_TEAM_X_ACCOUNT" WHERE 
              "ACCOUNT_ID"=:B1 AND "WBXT"."BROKER_ID"=48270 AND "TEAM_ID"="WBXT"."TEAM_ID"))
  13 - access("TEAM_ID"="WBXT"."TEAM_ID")
  14 - access("ACCOUNT_ID"=:B1)
  15 - access("WBXT"."BROKER_ID"=48270)

The tables in the query has the following cardinality

web_team_x_account -- 60Million
web_broker_x_team -- 1Million
account_summary -- 9Million

and replist WITH clause returns 25 rows and that is against metadata tables and that part of the query is pretty quick

The whole query is taking more than minute if actlist WITH clause returns more than 1million rows. I mean for all the brokers where
there are more accounts the query is really slow.

Can someone please help?

[update by JW: I've added code tags to make it readable, Naveen; please do this yourself in future, BlackSwan has given you the link with instructions]

[Updated on: Fri, 09 September 2011 13:46] by Moderator