Home » RDBMS Server » Performance Tuning » TUNNING
TUNNING [message #259894] |
Thu, 16 August 2007 14:45 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
qasim845
Messages: 95 Registered: March 2007 Location: Philadelphia
|
Member |
|
|
iS anybody HELP ME AFTER LOOKING THIS QUERY AND EXPLAIN TO INCREASE THE PERFORMANCE OF THIS QUERY ...
SELECT /*+ index(e EM_PK, o OEM_PK tl SYSC002258) */
e.event_id AS event_id,
e.app_id AS system_id,
e.event_type AS event_type_code,
to_date(tl.TIME_STRING, 'hh24:mi:ss') AS execution_timestamp,
e.SYMBOL as ISSUE_SYMBOL_ID,
e.terminal_id,
e.buy_sell_code AS buy_sell_code,
NULL AS act_branch_sequence,
e.ext_order AS order_id,
o.received_method_code AS received_method_code,
e.shares_quantity AS executed_shares_quantity,
NULL AS execution_leaves_quantity,
NULL AS market_center_id,
NULL AS execution_type_indicator,
NULL AS bid_px,
NULL AS bid_qty,
NULL AS ask_px,
NULL AS ask_qty,
NULL AS execution_price,
NULL AS last_market,
NULL AS contrabroker,
e.capacity AS execution_capacity,
NULL AS execution_reason,
NULL AS street_exec_only_flag,
e.parent_order AS parent_order_id,
e.firm_id ,
NULL AS execution_id,
NULL AS act_report_time,
r.trade_through_exempt,
r.reason_tt_exemp_lvl2,
r.extended_hour_sold_lvl3,
r.unus_trade_detail_lvl4
FROM event_main e, order_event_main o, event_reg_nms_main r, time_lookup tl,
(SELECT DATE_ID FROM DATE_LOOKUP a where DATE_STRING = to_char(to_date('23-Jul-2007','dd-Mon-yyyy'),'YYYYMMDD')) dl
WHERE
e.event_id = o.event_id
AND e.event_date_id = dl.date_id
AND tl.time_id = e.event_time_id
AND r.EVENT_ID = e.EVENT_ID
and e.event_date_id=o.event_date_id
and e.event_date_id=r.event_date_id
and (r.TRADE_THROUGH_EXEMPT = 'Y' OR r.REASON_TT_EXEMP_LVL2 is not NULL)
EXPLAIN
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9310 | 1272K| 1622K| | |
|* 1 | HASH JOIN | | 9310 | 1272K| 1622K| | |
| 2 | NESTED LOOPS | | 9310 | 1172K| 1622K| | |
| 3 | NESTED LOOPS | | 28912 | 3275K| 1564K| | |
| 4 | NESTED LOOPS | | 780K| 34M| 3689 | | |
| 5 | TABLE ACCESS BY INDEX ROWID | DATE_LOOKUP | 1 | 12 | 2 | | |
|* 6 | INDEX UNIQUE SCAN | DL_U1 | 1 | | 1 | | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
| 7 | PARTITION RANGE ALL | | | | | 1 | 32 |
|* 8 | TABLE ACCESS FULL | EVENT_REG_NMS_MAIN | 780K| 25M| 3687 | 1 | 32 |
| 9 | PARTITION RANGE ITERATOR | | | | | KEY | KEY |
| 10 | TABLE ACCESS BY LOCAL INDEX ROWID| EVENT_MAIN | 1 | 70 | 2 | KEY | KEY |
|* 11 | INDEX UNIQUE SCAN | EM_PK | 1 | | 1 | KEY | KEY |
| 12 | PARTITION RANGE ITERATOR | | | | | KEY | KEY |
| 13 | TABLE ACCESS BY LOCAL INDEX ROWID | ORDER_EVENT_MAIN | 1 | 13 | 2 | KEY | KEY |
|* 14 | INDEX UNIQUE SCAN | OEM_PK | 1 | | 1 | KEY | KEY |
| 15 | TABLE ACCESS FULL | TIME_LOOKUP | 86688 | 931K| 8 | | |
-------------------------------------------------------------------------------------------------------------
THANKS IN ADVANCE
[Updated on: Thu, 16 August 2007 14:56] Report message to a moderator
|
|
|
Re: TUNNING [message #259901 is a reply to message #259894] |
Thu, 16 August 2007 15:58 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
Could you also please post the plan and the necessary statistics information by doing tkprof on the trace file. I have seen quite a number of times the plans were totally different when using explain plan and the query execution plan obtained from the trace file. Also the basic questions like
a) Are the stats upto date ?
b) What is the expected rowcount ?
c) Information about tables and their existing indexes.
|
|
|
Re: TUNNING [message #259907 is a reply to message #259901] |
Thu, 16 August 2007 16:55 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
qasim845
Messages: 95 Registered: March 2007 Location: Philadelphia
|
Member |
|
|
Ok i ask my question in this way
SELECT
e.event_id AS event_id,
e.app_id AS system_id,
e.event_type AS event_type_code,
to_date(tl.TIME_STRING, 'hh24:mi:ss') AS execution_timestamp,
e.SYMBOL as ISSUE_SYMBOL_ID,
e.terminal_id,
e.buy_sell_code AS buy_sell_code,
NULL AS act_branch_sequence,
e.ext_order AS order_id,
o.received_method_code AS received_method_code,
e.shares_quantity AS executed_shares_quantity,
NULL AS execution_leaves_quantity,
NULL AS market_center_id,
NULL AS execution_type_indicator,
NULL AS bid_px,
NULL AS bid_qty,
NULL AS ask_px,
NULL AS ask_qty,
NULL AS execution_price,
NULL AS last_market,
NULL AS contrabroker,
e.capacity AS execution_capacity,
NULL AS execution_reason,
NULL AS street_exec_only_flag,
e.parent_order AS parent_order_id,
e.firm_id ,
NULL AS execution_id,
NULL AS act_report_time,
r.trade_through_exempt,
r.reason_tt_exemp_lvl2,
r.extended_hour_sold_lvl3,
r.unus_trade_detail_lvl4
FROM event_main e, order_event_main o, event_reg_nms_main r, time_lookup tl
WHERE
e.event_date_id in (SELECT dl.DATE_ID FROM DATE_LOOKUP dl where dl.DATE_STRING = to_char(to_date('23-Jul-2007','dd-Mon-yyyy'),'YYYYMMDD'))
AND e.event_id = o.event_id
AND tl.time_id = e.event_time_id
AND r.EVENT_ID = e.EVENT_ID
and e.event_date_id=o.event_date_id
and e.event_date_id=r.event_date_id
and (r.TRADE_THROUGH_EXEMPT = 'Y' OR r.REASON_TT_EXEMP_LVL2 is not NULL)
The join in the query which is causing problem ..
e.event_date_id in (SELECT dl.DATE_ID FROM DATE_LOOKUP dl where dl.DATE_STRING = to_char(to_date('23-Jul-2007','dd-Mon-yyyy'),'YYYYMMDD'))
If i harcode the value directly with out using the inline view in the above join, it execute in couple of second.. But if i used the inline view like i wrote above, it took atleast 5 mints to execute.
Much much appreciated.
Thanks again
[Updated on: Thu, 16 August 2007 16:55] Report message to a moderator
|
|
|
|
|
|
|
Re: TUNNING [message #259941 is a reply to message #259922] |
Thu, 16 August 2007 21:49 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
The function will force the subquery to be evaluated as if it were a scalar sub-query.
You could try WHERE val = (subquery), but the CBO will often merge these. There are sometime more subtle ways to force CBO to use a scalar sub-query, but it can sometimes be pretty obstinate as well. A function is a sure-fire cure to get it to execute once-only.
Ross Leishman
|
|
|
Re: TUNNING [message #260104 is a reply to message #259941] |
Fri, 17 August 2007 07:51 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
qasim845
Messages: 95 Registered: March 2007 Location: Philadelphia
|
Member |
|
|
Hey rleishman ..
Is there anyway can you please mention any other way, which you have in your mind could be possible, so it will execute the inline view only once.
|
|
|
Goto Forum:
Current Time: Thu Sep 12 08:36:23 CDT 2024
|