DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: SQL Tuning help

  1. #1
    Join Date
    Dec 2001
    Posts
    337

    SQL Tuning help

    Hi all,

    I Have a query below:

    Code:
    SQL> explain plan for
      2  select count(b.vrm) from overviewimage b, hotlistmatch h
      3  where b.vrm <> h.vrm
      4  and
      5  b.capturedate < '07-AUG-07'
    Basically this query is getting a count of vrm from one table (overviewimage which has ~45 mill rows) which are not part of another table (hotlistmatch which ~ 1 mill rows and are less than a certain date. Now this query takes an age (possibly because i am using <> operator?). Below is the explain plan:

    Code:
    SQL> select * from table(dbms_xplan.display);
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    Plan hash value: 1337561262
    
    --------------------------------------------------------------------------------
    -----------------
    
    | Id  | Operation              | Name                   | Rows  | Bytes | Cost (
    %CPU)| Time     |
    
    --------------------------------------------------------------------------------
    -----------------
    
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT       |                        |     1 |    24 |    44G
      (1)|147877:40:|
    
    |   1 |  SORT AGGREGATE        |                        |     1 |    24 |
         |          |
    
    |   2 |   NESTED LOOPS         |                        |    55T|  1219T|    44G
      (1)|147877:40:|
    
    |*  3 |    INDEX FAST FULL SCAN| OVERVIEW_CAPTURE_INDEX |    35M|   538M| 58162
      (1)| 00:11:38 |
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    
    |*  4 |    INDEX FAST FULL SCAN| HOTLISTMATCH_VRM_INDEX |  1584K|    12M|  1258
      (1)| 00:00:16 |
    
    --------------------------------------------------------------------------------
    -----------------
    
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
       3 - filter("B"."CAPTUREDATE"<'07-AUG-07')
       4 - filter("B"."VRM"<>"H"."VRM")
    
    17 rows selected.
    Now as can be seen it is making use of the available indexes but taking up huge cost and time in the select stmt. Is there anyway this can be re-written to avoid the <> and hence try to reduce to the cost?. I tried to rewrite the query using another subquery to get the vrm from the hotlistmatch table but ended up getting the same results.

    Any advice of this will be highly appreciated.

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Whats the output of

    Code:
    select count(b.vrm) 
    from overviewimage b, hotlistmatch h
    where b.vrm <> h.vrm
    and
    b.capturedate < to_date('07-AUG-07','DD-MON-YYYY')
    How many minutes does the query take to return the results

    regards
    Hrishy

  3. #3
    Join Date
    Dec 2001
    Posts
    337
    Hi Hrishy,

    I have only managed to run that query on a test server which has tables with less rows, this only take about 1 minute. On the live box it just takes too long hence i cancel it before it returns anything. Also i can see from EM that whilst the query is running it takes uses alot of CPU (about 70%), hence the other reason for cancelling it.

    From the plan table the select itself is estimated to take 147877 minutes!

    Thanks in advance

  4. #4
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Are those cardinalities right ?
    Are you sure of this 147877 minutes ?
    When were the tables last analyzed ?
    can you create a composite index on overviewimage(capturedate,vrm)
    and rewrite the query as

    Code:
    select count(b.vrm) 
    from overviewimage b, hotlistmatch h
    where b.capturedate < to_date('07-AUG-07','DD-MON-YYYY')
    and b.vrm <> h.vrm
    ANd the way you have stated your requirement makes me think that what you require is this no ?

    Code:
    select count(b.vrm) 
    from overviewimage b
    where b.capturedate < to_date('07-AUG-07','DD-MON-YYYY')
    and not exists (select 'X'
    		   from hotlistmatch h
    	             where b.vrm=h.vrm)
    Last edited by hrishy; 08-21-2007 at 07:30 AM.

  5. #5
    Join Date
    Mar 2002
    Posts
    534
    Quote Originally Posted by Chucks_k
    Hi all,

    I Have a query below:

    Code:
    SQL> explain plan for
      2  select count(b.vrm) from overviewimage b, hotlistmatch h
      3  where b.vrm <> h.vrm
      4  and
      5  b.capturedate < '07-AUG-07'
    Basically this query is getting a count of vrm from one table (overviewimage which has ~45 mill rows) which are not part of another table ...
    Are you sure that your query is correct? Are you not generating a cartesian product?

    Wouldn't somethink like

    Code:
    select count(b.vrm) 
    from overviewimage b, hotlistmatch h
    where b.capturedate < to_date('07-AUG-07','DD-MON-YYYY')
    and b.vrm = h.vrm(+)
    and h.vrm is null
    not be more correct?

  6. #6
    Join Date
    Dec 2001
    Posts
    337
    Hi,

    Thanks for that hrishy. I tried your second suggestion and got the results back within 2 minutes. Thank you for that. Just a question though, why select 'x' in the subquery rather than the vrm?

    Thanks again

  7. #7
    Join Date
    Dec 2001
    Posts
    337
    Hi Hrishy,

    Just as an aside, would the first query i have given not produce the same results as the second one which you have produced? I cant see the difference between the two?

    Thanks again

  8. #8
    Join Date
    May 2005
    Location
    AZ, USA
    Posts
    131

  9. #9
    Join Date
    Dec 2001
    Posts
    337
    Thanks for that link, made alot of things clearer. The query should be should be getting a count of vrms in the overviewimage table that are not in the hotlistmatch table (altough now am unsure if the query provided but hrishy does get this result?). Seems to be that both return different values (on my test system).

  10. #10
    Join Date
    May 2005
    Location
    AZ, USA
    Posts
    131
    The suggested SQL's should probably be considered an example, not an 'answer'. Use the suggestions to construct your own version, and verify the anticipated results.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width