Forum Stats

  • 3,758,423 Users
  • 2,251,387 Discussions
  • 7,870,188 Comments

Discussions

B-tree index traversal slow

1007769
1007769 Member Posts: 1
edited May 10, 2013 6:40AM in General Database Discussions
Why is b-tree traversal
of in-memory working set slow?

select extract(second from (systimestamp - timestamp '1970-01-01 00:00:00')) unix_time from dual;
select * from
( select a.*, ROWNUM rnum from
( select m.id from hugehugetable.offer m where m.id > [some_id_from_the_middle] order by m.id) a
where ROWNUM <= 2000005 )
where rnum >= 2000000;
select extract(second from (systimestamp - timestamp '1970-01-01 00:00:00')) unix_time from dual;

We reach response times of ~ 0.3 secs
(idle server, repeated querying, everything in RAM).

According to my understanding of b-tree traversal
it should be 1000 times FASTER.

if L3-Cache would be used the least,
then performance should be even higher.


Regards
Peter
Tagged:

Answers

  • user639304
    user639304 Member Posts: 301 Blue Ribbon
    What is hugehugetable?
  • Nikolay Savvinov
    Nikolay Savvinov Member Posts: 1,860 Silver Trophy
    Hi,

    1) ALTER SESSION SET STATISTICS_LEVEL = ALL;

    2) run your query with a unique comment, e.g.:

    select /* testrun1 */ extract(second ...

    3) identify the sql_id:

    select sql_id from v$sql where sql_text like 'select /* testrun1 */ extract(second%';

    4) select * from table(dbms_xplan.display_cursor(<sql_id>, null, 'iostats last'));

    5) post the output between {post} tags

    Best regards,
    Nikolay
  • Welcome to the forum!

    Whenever you post provide your 4 digit Oracle version.
    >
    Why is b-tree traversal
    of in-memory working set slow?

    select extract(second from (systimestamp - timestamp '1970-01-01 00:00:00')) unix_time from dual;
    select * from
    ( select a.*, ROWNUM rnum from
    ( select m.id from hugehugetable.offer m where m.id > [some_id_from_the_middle] order by m.id) a
    where ROWNUM <= 2000005 )
    where rnum >= 2000000;
    select extract(second from (systimestamp - timestamp '1970-01-01 00:00:00')) unix_time from dual;

    We reach response times of ~ 0.3 secs
    (idle server, repeated querying, everything in RAM).

    According to my understanding of b-tree traversal
    it should be 1000 times FASTER.

    if L3-Cache would be used the least,
    then performance should be even higher.
    >
    Well you really haven't posted any information at all that shows:

    1. the actual time that was spent. For example, how can you measure a response time of .3 seconds when you are extracting 'seconds' in your statements?
    2. the execution plan that shows what, exactly, Oracle did to satisfy the query
    3. that any index was even used at all.
    4. the number of rows in the table, the number that satisfy the query predicates and the number of rows in the result set.
    5. the table and index ddl that shows that Oracle even could have used the index and whether that index might be effective.

    Post the information above so we can see what Oracle was actually doing.
  • Hemant K Chitale
    Hemant K Chitale Member Posts: 15,759 Blue Diamond
    How do you compute that it should be 1000 times faster ?

    How does the L3 cache (which cache are you really referring to ?) be relevant ?


    Hemant K Chitale
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,756 Gold Crown
    user10788945 wrote:
    Why is b-tree traversal
    of in-memory working set slow?

    According to my understanding of b-tree traversal
    it should be 1000 times FASTER.
    Peter,

    I'd guess you're probably accessing about 4,000 to 5,000 leaf blocks in your query; and my guideline on buffer gets is 10,000 per 100MHz of CPU per second. If we assume 4,000 buffers and 2.0 GHz then the buffer visits should account for about (4,000/10000) / 20 seconds = 0.02 seconds; so your '1000 times faster' is way over the top.

    On top of this, the guideline is only for "simple" buffer visits. If we assume 4,000 buffers then we have 500 index entries per buffer which means you follow a pointer from the row directory to the index entry 500 times for each buffer - and chasing pointers is relatively slow work for a CPU.

    Then your execution plan requires you to pass 2M rows through (probably) three layers of execution plans - which is a lot of function calls to pass an item from layer to layer before the final stage where you discard all but a couple of rows. For an indication of the CPU utilisation on such things take a look at the following: http://jonathanlewis.wordpress.com/2012/03/04/count-2/

    It's likely that only a small fraction of your CPU is spent in the accessing the blocks.
    If you want a better test you might create an index on two columns and then do a range scan with a predicate on the first column that identifies 2M index entries and with a predicate on the second column that discards them very cheaply.

    Regards
    Jonathan Lewis
This discussion has been closed.