Home » RDBMS Server » Performance Tuning » Disk reads
Disk reads [message #545871] Fri, 02 March 2012 22:33 Go to next message
gkrishn
Messages: 487
Registered: December 2005
Location: Putty a dark screen
Senior Member
Hi ,

I did flush buffer cache before i executed this query , but tkprof still shows 43M consistent reads ( query=43920949 ).

Can somebody explain this concept .

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.12       0.16          3          3          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       71    490.42     600.17     119079   43920946         18        1044
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       73    490.54     600.34     119082   43920949         18        1044
Re: Disk reads [message #545872 is a reply to message #545871] Fri, 02 March 2012 23:02 Go to previous messageGo to next message
BlackSwan
Messages: 22537
Registered: January 2009
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

It would have been helpful if you had actually posted the code that generated these trace details.

Notice Fetch=71!
Could it be for Fetch 2-71 that some of the data may have been already in the SGA?
Re: Disk reads [message #545877 is a reply to message #545872] Fri, 02 March 2012 23:10 Go to previous messageGo to next message
gkrishn
Messages: 487
Registered: December 2005
Location: Putty a dark screen
Senior Member
Fetch begins only after Oracle finish its execution, and ready with records right ? Or is it like execution can continue with intermediate fetch back to client ? Thanks
Re: Disk reads [message #545878 is a reply to message #545877] Fri, 02 March 2012 23:11 Go to previous messageGo to next message
gkrishn
Messages: 487
Registered: December 2005
Location: Putty a dark screen
Senior Member
Query - Sorry security reasons i m unable to paste the query here. its a SELECT with multiple sort and joins.Thanks
Re: Disk reads [message #545879 is a reply to message #545877] Fri, 02 March 2012 23:14 Go to previous messageGo to next message
BlackSwan
Messages: 22537
Registered: January 2009
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

http://www.oracle.com/pls/db112/search?word=fetch&partno=
Re: Disk reads [message #545880 is a reply to message #545879] Fri, 02 March 2012 23:24 Go to previous messageGo to next message
gkrishn
Messages: 487
Registered: December 2005
Location: Putty a dark screen
Senior Member
Oracle version 11.2.0.2 , Linux x86_64.
Re: Disk reads [message #545882 is a reply to message #545880] Fri, 02 March 2012 23:50 Go to previous messageGo to next message
BlackSwan
Messages: 22537
Registered: January 2009
Senior Member
what problem are you trying to solve?
Re: Disk reads [message #545884 is a reply to message #545871] Sat, 03 March 2012 00:18 Go to previous messageGo to next message
Michel Cadot
Messages: 58625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The consistent reads do not depend on the content of the cache, the disk accesses do.

Regards
Michel

[Updated on: Sat, 03 March 2012 00:18]

Report message to a moderator

Re: Disk reads [message #546024 is a reply to message #545871] Sun, 04 March 2012 11:52 Go to previous messageGo to next message
LNossov
Messages: 284
Registered: July 2011
Location: Germany
Senior Member
What a query? You didn't upload it. Do you use any plsql functions in your sql?
Re: Disk reads [message #546821 is a reply to message #546024] Fri, 09 March 2012 02:33 Go to previous messageGo to next message
rleishman
Messages: 3689
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Say you are performing an indexed nested loops join. i.e. For each row in table A, lookup matching rows in table B via an index.

Assume the buffer cache is completely empty.

1. Read block #1 in A (call this A1) from disk into the buffer cache.
2. Pick the first row from block A1

Now that we have our 1st row, we are ready to scan the index.
3. Read the root block of the index from disk into the buffer cache
4. Determine the address of the next branch block in the index and read that from disk into the buffer cache. Repeat until you get to a leaf block that points to our 1st matching row in B.
5. Read the block of B from disk into the buffer cache that contains the rowid found in the index
6. Pick the matching row of B out of that block and join it to the A row from Step 2.

Now we have our first row returned; the contents of the buffer cache are:
- The 1st block of A
- The root block of the index on B
- Several branch blocks of the index on B
- A leaf block of the index on B
- One block from table B.

Then Oracle goes on to get another row, it will again scan the index on B, but this time some of the blocks will be cached - they won't need to be read from disk. And if we are lucky, some of the other matching rows in B will be in blocks we have already read into cache.

Since index blocks and table blocks contain many rows, every time we read a single block from disk we get 1 row that we want, and dozens that we don't want (yet). If it turns out that we DO want one of those rows later in the query, it will be there in the buffer cache.

Ross Leishman
Re: Disk reads [message #546822 is a reply to message #546821] Fri, 09 March 2012 02:39 Go to previous message
gkrishn
Messages: 487
Registered: December 2005
Location: Putty a dark screen
Senior Member
Ross very good explanation of the concept.Much appreciate it .Thank you !
Previous Topic: undo tbalespace full, which is rectified but now having big WAIT
Next Topic: how oracle organises records in partitions
Goto Forum:
  


Current Time: Wed Jul 30 19:12:50 CDT 2014

Total time taken to generate the page: 0.08738 seconds