Home » RDBMS Server » Performance Tuning » TUNNING
TUNNING [message #259894] Thu, 16 August 2007 14:45 Go to next message
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 messageGo to next message
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 messageGo to next message
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 #259908 is a reply to message #259894] Thu, 16 August 2007 17:04 Go to previous messageGo to next message
BlackSwan
Messages: 25475
Registered: January 2009
Location: SoCal
Senior Member
SELECT dl.DATE_ID FROM DATE_LOOKUP dl where dl.DATE_STRING = to_char(to_date('23-Jul-2007','dd-Mon-yyyy'),'YYYYMMDD')
How many rows are returned by this SQL?

use CUT & PASTE to show results from below
SQL> DESC DATE_LOOKUP
Re: TUNNING [message #259910 is a reply to message #259908] Thu, 16 August 2007 17:10 Go to previous messageGo to next message
qasim845
Messages: 95
Registered: March 2007
Location: Philadelphia
Member

it returns only one value, which is 900.
(
  DATE_ID      INTEGER,
  DATE_STRING  VARCHAR2(8 BYTE)                 NOT NULL
)


Thanks alot

[Updated on: Thu, 16 August 2007 17:12]

Report message to a moderator

Re: TUNNING [message #259911 is a reply to message #259894] Thu, 16 August 2007 17:27 Go to previous messageGo to next message
BlackSwan
Messages: 25475
Registered: January 2009
Location: SoCal
Senior Member
Create a function & use it in the WHERE clause
WHERRE e.event_date_id = LOOKUP_DATE('20070723')


CREATE OR REPLACE FUNCTION LOOKUP_DATE (DT_STRING VARCHAR2)
RETURN INTEGER IS
OUT_VAL INTEGER;
BEGIN
   SELECT DATE_ID INTO OUT_VAL FROM DATE_LOOKUP WHERE DATE_STRING = DT_STRING;
   RETURN OUT_VAL;
END LOOKUP_DATE;
Re: TUNNING [message #259922 is a reply to message #259911] Thu, 16 August 2007 19:00 Go to previous messageGo to next message
shoaib_143
Messages: 1
Registered: August 2007
Location: BANGLORE
Junior Member
is there any particular reason why do we need this function ....
Re: TUNNING [message #259941 is a reply to message #259922] Thu, 16 August 2007 21:49 Go to previous messageGo to next message
rleishman
Messages: 3727
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
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.
Previous Topic: Some missing concepts and definitions
Next Topic: SQL Tuning
Goto Forum:
  


Current Time: Sun Jun 25 12:36:14 CDT 2017

Total time taken to generate the page: 0.12610 seconds