Oracle SQL Performance

Posted on 2007-08-08
Last Modified: 2013-12-18
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
- 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
Question by:Extreme66
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    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.

    Author Comment

    The statistics are current, I cannot run this query until later today or early tomorrow. Once I do I will post hte results, thanks

    Author Comment

    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
            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      
    LVL 76

    Accepted Solution

    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.

              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

              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

    Featured Post

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Create two table and a foreign key? 4 51
    NMAP shows service 1521 is closed 13 69
    Sql to get orphans 7 47
    Add 0 to end of Number 21 58
    Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now