1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Why are 2 queries quicker than 1

Discussion in 'SQL PL/SQL' started by stubbo, Sep 15, 2009.

  1. stubbo

    stubbo Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    I'm trying to help out our developers who are struggling with the performance of a very simple select statement that for some reason is exhibiting some very inexplicable results.

    I'll show the SQL as a single statement first.....

    select * from transaction1 t where t.hid in (select distinct h.hid from history1 h,filter1 f where h.fid = f.fid and f.match='value');

    transaction 1 has 250k records, history1 has about 100k as does filter1. We know generally the subselect will only return a total of half a dozen or less records (only 1 in our testing here).

    Running the above query takes around 7 seconds.

    Changing this to a with clause.....

    WITH a as (select distinct h.hid from history1 h,filter1 f where h.fid = f.fid and f.match='value') select * from transaction1 t where t,hid in (select a.hid from a);

    and the runtime drops to 3 seconds. But then re-running the first statement again also now takes 3 seconds (so I guess there's some caching going on there).

    Now what really puzzles is if we split this into two queries.....

    select distinct h.hid from history1 h,filter1 f where h.fid = f.fid and f.match='value';
    This takes .1 of a second,

    select * from transaction1 t where t.hid in (12345);
    And this takes .1 of a second.

    So why when run seperately are they so fast, yet combined they take so long. I'm a bit baffled by this. We've rewritten the same SQL in half a dozen different ways with the same result and also done the same thing with other tables as well. Is it an optimisation issue?
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Where are the query plans for all of these queries? Those may explain much in this situation. If you have yet to create the PLUSTRACE role you should do so and grant it to the user executing these queries:

    Code (SQL):
    SQL> DROP ROLE plustrace;
    DROP ROLE plustrace
              *
    ERROR at line 1:
    ORA-01919: ROLE 'PLUSTRACE' does NOT exist
     
     
    SQL> CREATE ROLE plustrace;
     
    ROLE created.
     
    SQL>
    SQL> GRANT SELECT ON v_$sesstat TO plustrace;
     
    GRANT succeeded.
     
    SQL> GRANT SELECT ON v_$statname TO plustrace;
     
    GRANT succeeded.
     
    SQL> GRANT SELECT ON v_$mystat TO plustrace;
     
    GRANT succeeded.
     
    SQL> GRANT plustrace TO dba WITH admin OPTION;
     
    GRANT succeeded.
     
    SQL> GRANT plustrace TO appuser;

    GRANT succeeded.

    SQL>
     
    Ensure this user has access to PLAN_TABLE either by creating a copy in the user's schema or by granting access to a common copy of it. Now, as 'appuser' you can do this:

    Code (SQL):
    SQL> SET autotrace ON
    SQL> SELECT * FROM transaction1 t WHERE t.hid IN (SELECT DISTINCT h.hid FROM history1 h,filter1 f WHERE h.fid = f.fid AND f.MATCH='value');
     
    After the results are returned you'll see the query plan and the per-query statistics:

    Code (SQL):
    SQL> SET autotrace ON
    SQL> SELECT * FROM dual;
     
    D
    -
    X
     
     
    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 3543395131
     
    --------------------------------------------------------------------------
    | Id  | Operation         | Name | ROWS  | Bytes | Cost (%CPU)| TIME     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
    --------------------------------------------------------------------------
     
     
    Statistics
    ----------------------------------------------------------
             24  recursive calls
              0  db block gets
              6  consistent gets
              2  physical reads
              0  redo SIZE
            511  bytes sent via SQL*Net TO client
            492  bytes received via SQL*Net FROM client
              2  SQL*Net roundtrips TO/FROM client
              0  sorts (memory)
              0  sorts (disk)
              1  ROWS processed
     
    SQL>
    Such data can tell you much about how a query is executed and what I/O loads it generates so are better equipped to determine if the query needs tuning or not. You should run all of your listed queries through autotrace and see where the differences arise.
     
  3. stubbo

    stubbo Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    We don't have a lot of access to the server in question, it's a clients machine and is heavily secured. I've managed to get the plan table out though.
    Code (Text):

    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    Plan hash value: 898894568
     
    ------------------------------------------------------------------------------------------
    | Id  | Operation            | Name              | Rows  | Bytes | Cost (%CPU)|Time     |
    ------------------------------------------------------------------------------------------
     
     
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |                   | 49553 |    37M|  7075   (2)|00:01:25 |
    |*  1 |  HASH JOIN RIGHT SEMI|                   | 49553 |    37M|  7075   (2)|00:01:25 |
    |   2 |   VIEW               | VW_NSO_1          | 26131 |   331K|   476   (4)|00:00:06 |
    |*  3 |    HASH JOIN         |                   | 26131 |   791K|   476   (4)|00:00:06 |
    |*  4 |     TABLE ACCESS FULL| FILTER1           | 26131 |   484K|   246   (4)|00:00:03 |
    |   5 |     TABLE ACCESS FULL| HISTORY1          |   104K|  1225K|   227   (3)|00:00:03 |
    |   6 |   TABLE ACCESS FULL  | TRANSACTION1      |   199K|   150M|  6593   (1)|00:01:20 |
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       1 - access("T"."TID"="$nso_col_1")
       3 - access("H"."HID"="F"."ID")
       4 - filter("F"."MATCH"='value')
     
    20 rows selected
     
    All the columns being matched on have indexes so we should have optimal conditions but we just can't understand why it is taking so long as a combined query.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I see three full table scans with no index access listed. If you're certain you have indexes on the columns in your WHERE clause then I'd suspect you have stale statistics. I would verify that you do have indexes created; remember, too, that for traditional B-Tree indexes Oracle will use one per table to speed data access, and the optimizer will select the most selective index per table to use. Also, when using subquery factoring (the WITH clause) you're creating a materialized 'table' to be used throughout the query and this 'phantom' table has no indexes.

    All indicators present in this execution plan point to one of three situations: stale statistics, poor statistics due to skewed data, or no existing indexes. A fourth condition, no statistics, can also exist but in that case you'd see dynamic sampling occurring, and that has not been reported in the execution plan. I'm hoping you do not have optimizer_mode set to CHOOSE; that is a deprecated setting and Oracle advises against its use. You could set optimiser_mode to RULE at the session level and see if the indexes are used (the rule-based optimizer uses indexes when they exist). A better plan is to have the DBA verify the freshness and quality of the statistics and regenerate them if necessary. Run the queries with fresh statistics and see if the query time decreases. Apparently you did not post the actual query being run (the predicates in the query plan do not match those in your posted query) so it's difficult to analyze the query text against this plan to see where improvements might be made. Of course it may not be possible to post the exact query due to their proprietary nature; do your best, then, to create a similar example showing similar results so we can duplicate the issue and test on our own.