Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2004
    Posts
    3

    Question Unanswered: Index Range scans exceeding table sizes

    We are currently running Oracle 9.2.0 on 3 machines with the same database schemas on each. There is a query that is used by a web page that takes a long time to complete on one of the databases (it runs fine on the others):

    SELECT log1.jobid, TO_CHAR (log1.dt_start, 'HH24:MIS DD-MM-YYYY'),
    log1.jexid, log1.state1
    FROM rcsysdata.txeventlog log1
    WHERE log1.TYPE = 0
    AND log1.dt_start = (SELECT MAX(log2.dt_start)
    FROM rcsysdata.txeventlog log2
    WHERE log2.jobid = log1.jobid AND log2.TYPE = 0)

    When running a trace on the execution plan I find the following:

    Rows Row Source Operation
    ------- ---------------------------------------------------
    11 FILTER
    19948 TABLE ACCESS FULL TXEVENTLOG
    2563 SORT AGGREGATE
    6537756 TABLE ACCESS BY INDEX ROWID TXEVENTLOG
    58554611 INDEX RANGE SCAN IDX_TXEVENTLOG_JOBID (object id 7507)

    However there are only 126609 records in that table. On all other systems the the Rows returned on an INDEX RANGE SCAN equals the number of records in the table. This seems to occur after the table reaches a certain size.

    I have tried dropping and recreating the table along with truncating the table to fix this problem.

    Any help would be greatly appreciated.

    Cheers

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    I think the reason for this is that it is doing multiple range scans of the index or it might have out of data statistics. Dont forget these are just guesstimates so it can easily get it wrong.

    To make it go faster you could try the following

    1) Make an index on jobid, type, dt_start in that order and analyze it.
    2) Try changing the sql and see if it generates a better plan, either use an analytic function to get the max or use in line views i.e.

    SELECT log1.jobid, TO_CHAR (log1.dt_start, 'HH24:MIS DD-MM-YYYY'),
    log1.jexid, log1.state1
    FROM rcsysdata.txeventlog log1,
    (SELECT jobid, MAX(log2.dt_start) max_dt_start
    FROM rcsysdata.txeventlog log2
    WHERE log2.jobid = log1.jobid AND log2.TYPE = 0 group by jobid) log2
    WHERE log1.job_id=log2.jobid
    AND log1.dt_start=log2.max_dt_start

    Alan

  3. #3
    Join Date
    Jun 2004
    Posts
    3
    Thanks for you help Alan, however I can't use this as a solution. I have tried creating the indexes mentioned and calculating statistics on the table and indexes and this results in the query taking even longer.

    If the query were doing multiple range scans on the index, shouldn't it stand to reason that it would do this on all systems. Unfortunately I am a sys admin in a software development company (with no oracle developers on staff). Therefore it would be difficult to get the query changed without absolute proof of it's ineficiency.

    Cheers,
    Damien

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Do the other databases have the same number of rows as your problem instance? If they do then you probaly want to check your init.ora parameters. If they dont, do they exhibit the same behaviour when you copy the problem table over and execute the query?

    The fact it the explain plan says its returning too many rows isnt important, what is important is the execution plan and the stats after you run the query. You can check these using tkprof or the autotrace facility in TOAD.

    You may also need to tune the sorts as if it goes to disk to do the sort then this may explain the slowness.

    Alan

  5. #5
    Join Date
    Jun 2004
    Posts
    3
    The other databases have more rows in this table. Also all database parameters match between the databases. No disk sorts are occuring.

    The problem does occur if I copy the table to one of the other databases via export/import. However I have dropped and recreated this table to no avail.

    Here are the execution plan/stats from autotrace:

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE
    1 0 FILTER
    2 1 TABLE ACCESS (FULL) OF 'TXEVENTLOG'
    3 1 SORT (AGGREGATE)
    4 3 TABLE ACCESS (BY INDEX ROWID) OF 'TXEVENTLOG'
    5 4 INDEX (RANGE SCAN) OF 'IDX_TXEVENTLOG_JOBID' (NON-UNIQUE)

    Statistics
    ----------------------------------------------------------
    0 recursive calls
    0 db block gets
    11183277 consistent gets
    0 physical reads
    0 redo size
    989 bytes sent via SQL*Net to client
    499 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    11 rows processed

Posting Permissions

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