topic Re: reading blocks in Operating System - HP-UX

reading blocks

mangor — Thu, 03 Feb 2005 16:03:08 GMT

Hello All,
I'm trying to understand how Oracle and the OS work together when retrieving data. I have an Oracle DB. The developer is running a query select 3 columns from a 20 column table. Oracle needs to perform disk reads instead of finding the data in memory. I'm thinking Oracle sends the request to the OS. Which then looks in it's cache. If the data is there does the OS send the entire record? Or just the OS just send the data for those 3 columns? Next if the OS doesn't find the data in it's cache it then must read the data from the hard drive. Does the OS return the entire record or just the 3 columns?

Thanks in advanced.

Re: reading blocks

A. Clay Stephenson — Thu, 03 Feb 2005 17:08:03 GMT

The OS doesn't have a clue about rows or columns; it simply reads chunks of data starting at a specified offset. It is completely up to the application to define the size of those reads and the starting offset and the number of bytes to read. If the number of bytes to reads exceeds an OS determined limit, the OS silently breaks up the read() into multiple physical reads but that is invisible to the application.

I'll assume you are using cooked files for the database:

When a read request is made (select), Oracle first looks in its cache (SGA) to see if the blocks are there; if not a read() system call is made (specifying the number of bytes and starting offset), the OS then determines if these blocks are in the UNIX buffer cache; if not, the data are read from disk -- which itself might have onboard cache.

Re: reading blocks

mangor — Fri, 04 Feb 2005 10:10:15 GMT

Thanks Clay.