• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 572
  • Last Modified:

Oracle SQL Performance

Hello, I am simply looking for any good preformance advice on this query, it works now, I just want to see what can be done to make it run better outside the indexes. Oracle 10g
thanks,
- Jacy-
select substr(to_char(numtodsinterval(avg(diff),'day'),'HH:MI:SS.FF3'),12,12) "AvgOnBook"  
    , pubcode "Code"
    , avg(diff) secdiff
    , sum(order_vol)
    , firm
    , count(*) "NumOrders"
    , numtodsinterval(min(diff),'day') "MinOnBook"
    , numtodsinterval(max(diff),'day') "MaxOnBook"
  from (select u.FIRM_id Firm, f.public_reporting_code pubcode, o.bids_order_id, o.order_vol
        , substr(c2.description,12,10) act, c2.code
        , o.final_state fin
        , substr(c.description,13,10) state
        , o.last_update_time
        , (cast(o.last_update_time as date)-
            lag(cast(o.last_update_time as date),1,cast(o.last_update_time as date))
            over (partition by o.bids_order_id order by o.bids_order_id, o.last_update_action)) diff
          from bids3.orders_history o, part.users u, bids3.code_val c, bids3.code_val c2, sym.symbol s, part.firms f
         where o.trader=u.user_id(+)
           and u.firm_id=f.firm_id(+)
           and o.order_state=c.code(+)
           and o.last_update_action=c2.code(+)
           and o.last_update_time > trunc(sysdate)
           and (o.last_update_action=50001 or o.final_state=1)
           and (o.symbol = s.symbol(+) and nvl(s.test_symbol,0)<>1)
           order by o.bids_order_id, o.last_update_action)
where fin=1
group by pubcode, firm
order by 4 desc
;
0
Extreme66
Asked:
Extreme66
  • 2
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
Are the database statistics current?

Please post the execution plan.  The easiest way is from SQL*Plus:  set autotrace traceonly;

then run the query.

Please post the output.
0
 
Extreme66Author Commented:
The statistics are current, I cannot run this query until later today or early tomorrow. Once I do I will post hte results, thanks
0
 
Extreme66Author Commented:
SELECT STATEMENT, GOAL = ALL_ROWS                  Cost=18      Cardinality=2      Bytes=62
 SORT ORDER BY                  Cost=18      Cardinality=2      Bytes=62
  HASH GROUP BY                  Cost=18      Cardinality=2      Bytes=62
   VIEW      Object owner=JACY_CISZKOWSKI            Cost=16      Cardinality=2      Bytes=62
    WINDOW SORT                  Cost=16      Cardinality=2      Bytes=300
     NESTED LOOPS OUTER                  Cost=15      Cardinality=2      Bytes=300
      NESTED LOOPS OUTER                  Cost=13      Cardinality=2      Bytes=248
       FILTER                              
        NESTED LOOPS OUTER                  Cost=11      Cardinality=2      Bytes=196
         NESTED LOOPS OUTER                  Cost=9      Cardinality=2      Bytes=182
          NESTED LOOPS OUTER                  Cost=7      Cardinality=2      Bytes=166
           TABLE ACCESS BY INDEX ROWID      Object owner=BIDS3      Object name=ORDERS_HISTORY      Cost=5      Cardinality=2      Bytes=128
            INDEX RANGE SCAN      Object owner=BIDS3      Object name=ORDERS_HISTORY_I2      Cost=3      Cardinality=2      
           TABLE ACCESS BY INDEX ROWID      Object owner=PART      Object name=USERS      Cost=1      Cardinality=1      Bytes=19
            INDEX UNIQUE SCAN      Object owner=PART      Object name=USERS_PK      Cost=0      Cardinality=1      
          TABLE ACCESS BY INDEX ROWID      Object owner=PART      Object name=FIRMS      Cost=1      Cardinality=1      Bytes=8
           INDEX UNIQUE SCAN      Object owner=PART      Object name=FIRMS_PK      Cost=0      Cardinality=1      
         TABLE ACCESS BY INDEX ROWID      Object owner=SYM      Object name=SYMBOL      Cost=1      Cardinality=1      Bytes=7
          INDEX UNIQUE SCAN      Object owner=SYM      Object name=SYMBOL_PK      Cost=0      Cardinality=1      
       TABLE ACCESS BY INDEX ROWID      Object owner=BIDS3      Object name=CODE_VAL      Cost=1      Cardinality=1      Bytes=26
        INDEX UNIQUE SCAN      Object owner=BIDS3      Object name=CODE_VAL_PK      Cost=0      Cardinality=1      
      TABLE ACCESS BY INDEX ROWID      Object owner=BIDS3      Object name=CODE_VAL      Cost=1      Cardinality=1      Bytes=26
       INDEX UNIQUE SCAN      Object owner=BIDS3      Object name=CODE_VAL_PK      Cost=0      Cardinality=1      
0
 
slightwv (䄆 Netminder) Commented:
The plan looks pretty good.  There's only 1 thing in the SQL that I might change and I'm not sure it will make any difference.

The where clause:  fin = 1
I'd try moving it into the inline view.

change
--------------------------------
...
          and (o.last_update_action=50001 or o.final_state=1)
           and (o.symbol = s.symbol(+) and nvl(s.test_symbol,0)<>1)
           order by o.bids_order_id, o.last_update_action)
where fin=1
group by pubcode, firm

to
----------------------------------------
...
          and (o.last_update_action=50001 or o.final_state=1)
          and (o.symbol = s.symbol(+) and nvl(s.test_symbol,0)<>1)
          o.final_state = 1
           order by o.bids_order_id, o.last_update_action)
group by pubcode, firm
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now