| Disk reads [message #545871] |
Fri, 02 March 2012 22:33  |
gkrishn
Messages: 506
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 #545877 is a reply to message #545872] |
Fri, 02 March 2012 23:10  |
gkrishn
Messages: 506
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 |
gkrishn
Messages: 506
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 #546821 is a reply to message #546024] |
Fri, 09 March 2012 02:33 |
rleishman
Messages: 3728
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
