This discussion is archived
7 Replies Latest reply: Jan 28, 2013 1:38 PM by damorgan RSS

querying effects on physical reads

636309 Newbie
Currently Being Moderated
Hi, Running Oracle Database 10g Enterprise Edition Release 10.2.0.3.0. I have 2 tables created and populated with 10 million records each.

create table bigtable(col1 varchar2(30), col2 varchar2(30))
create table bigtablechild(col1 varchar2(30), col2 varchar2(30))

Here's my experiment.
1. run 2-table joining query twice and note physical reads.
2. Then, run small 1-table query on child table.
3. Lastly, check physical reads on 2-table joining query.
SQL> alter system flush buffer_cache;

System altered.

SQL> select b.col2
  2  from bigtable a, bigtablechild b
  3  where a.col1 = b.col1
  4  and a.col1 = 'ABC776543';


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      93672  consistent gets
      93643  physical reads
          0  redo size
        465  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed

SQL> /


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      93672  consistent gets
      93641  physical reads
          0  redo size
        465  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed

SQL> select col2 from bigtablechild where col1 = 'ABC776543';


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      35873  consistent gets
          0  physical reads
          0  redo size
        465  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed

SQL> select b.col2
  2  from bigtable a, bigtablechild b
  3  where a.col1 = b.col1
  4  and a.col1 = 'ABC776543';


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      93672  consistent gets
      67563  physical reads
          0  redo size
        465  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed

SQL> 
Expected result: Physical reads of the 2-table joining query would drop on the 2nd execution.
Actual Result: The physical reads of the 2-table joining query DID NOT decrease on the 2nd execution. Instead, the affect of querying the 1-table child query has caused physical reads of the 2-table joining query to decrease on the 3rd execution!

Could someone please help me understand what's happening here?
  • 1. Re: querying effects on physical reads
    636309 Newbie
    Currently Being Moderated
    Does anyone have any ideas about this?
  • 2. Re: querying effects on physical reads
    damorgan Oracle ACE Director
    Currently Being Moderated
    Four thoughts in no particular order.

    1. Without an explain plan report generated using DBMS_XPLAN I am reserving comment.

    2. 10.2.0.3? Why? First off you have software so old it has been in desupport mode for years. Then you haven't even patched to the terminal release so there could be lots of things going on we can not see from here.

    3. Is this a single user database?

    4. Did you flush the pools or restart and try this at least three times to see if what you saw once was a fluke or is a consistent behaviour?
  • 3. Re: querying effects on physical reads
    636309 Newbie
    Currently Being Moderated
    damorgan wrote:
    Four thoughts in no particular order.

    1. Without an explain plan report generated using DBMS_XPLAN I am reserving comment.
    SQL> explain plan for select b.col2 from bigtable a, bigtablechild b where a.col1 = b.col1 and a.col
    1 = 'ABC776543';
    
    Explained.
    
    SQL> select * from table(dbms_xplan.display);
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
    Plan hash value: 4210396901
    
    ------------------------------------------------------------------------------------
    | Id  | Operation          | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |               |     5 |   150 | 16964   (2)| 00:04:40 |
    |*  1 |  HASH JOIN         |               |     5 |   150 | 16964   (2)| 00:04:40 |
    |*  2 |   TABLE ACCESS FULL| BIGTABLE      |     1 |    10 | 10373   (2)| 00:02:52 |
    |*  3 |   TABLE ACCESS FULL| BIGTABLECHILD |     5 |   100 |  6590   (3)| 00:01:49 |
    ------------------------------------------------------------------------------------
    
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - access("A"."COL1"="B"."COL1")
       2 - filter("A"."COL1"='ABC776543')
       3 - filter("B"."COL1"='ABC776543')
    
    17 rows selected.
    
    SQL> 
    >
    2. 10.2.0.3? Why? First off you have software so old it has been in desupport mode for years. Then you haven't even patched to the terminal release so there could be lots of things going on we can not see from here.
    This is just a test database that I have set up on my home computer for learning purposes. Guess I'll update to 11g.

    >
    3. Is this a single user database?
    Yes. It's my own personal database set up for learning purposes.

    >
    4. Did you flush the pools or restart and trying this at least three times to see if what you saw once was a fluke or consistent?
    I've tried this a few times and it keeps repeating.
  • 4. Re: querying effects on physical reads
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    arizona9952 wrote:

    create table bigtable(col1 varchar2(30), col2 varchar2(30))
    create table bigtablechild(col1 varchar2(30), col2 varchar2(30))

    Could someone please help me understand what's happening here?
    Hard to say exactly why you get the results - some tests like this have very obvious explanations, others require very close examination.
    Given that your child table appears to be about 60% of the size of the other table (as seen in you execution plan), it's probably something like this:

    First join - scans parent table through buffer cache to build a hash table, scans child table through buffer cache to build hash table
    Second join - repeats above, but the parent scan flushes the child table from memory as it goes
    Simple scan of child - finds that the child is in memory so does not physical reads, and increments the touch count on the buffers
    Third join - scans parent table through cache, but child blocks that get to the end of the LRU are promoted because of their touch count, and stay in the buffer

    Regards
    Jonathan Lewis
  • 5. Re: querying effects on physical reads
    636309 Newbie
    Currently Being Moderated
    Jonathan Lewis wrote:
    Simple scan of child - finds that the child is in memory so does not physical reads, and increments the touch count on the buffers
    How can you tell that the touch count on the buffers has been incremented? What value are you looking at to see this? While I'm at it, what is meant by touch count?
  • 6. Re: querying effects on physical reads
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    arizona9952 wrote:
    Jonathan Lewis wrote:
    Simple scan of child - finds that the child is in memory so does not physical reads, and increments the touch count on the buffers
    How can you tell that the touch count on the buffers has been incremented? What value are you looking at to see this? While I'm at it, what is meant by touch count?
    I infer that from the fact that the child table is not flushed from the buffer cache as the parent is scanned again. This suggests that the child buffers have been promoted to the popular end of the LRU. For some information about how the caching works you can always read my book (Oracle Core): at present pages 103 onwards are visible on Google books:

    http://books.google.co.uk/books?id=G9AJA91PL54C&lpg=PA103&dq=%22touch%20count%22&pg=PA103#v=onepage&q=%22touch%20count%22&f=false

    Regards
    Jonathan Lewis
  • 7. Re: querying effects on physical reads
    damorgan Oracle ACE Director
    Currently Being Moderated
    I definitely recommend the book.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points