RE: 10g System statistics - single and multi

From: Martic Zoran <zoran_martic_at_yahoo.com>
Date: Tue, 24 May 2005 01:13:33 -0700 (PDT)
Message-ID: <20050524081333.26164.qmail@web52605.mail.yahoo.com>

And to not count what is the algorithm on the SAN cache level or other levels when doing multiple single I/O reqs, .....
Oracle is very much blind what is going on down there on the I/O subsystem level. To not say that the OS level is blind about I/O operations too with all that SAN/others virtualization going on.

So what is Oracle missing then.
>From my point of view it is not calculating properly
the IO and CPU cost because of luck of statistics. As Wolfgang noticed about RAC instances system statistics.

Also Oracle should have MBRC on the object level (dba_tables, dba_indexes - that is probably calculated anyway, just the code doing it in the lower layer does not have a clue which object is that based on, or it is only calculated per MBR removing any notion which FTS is that based on), MBR time and SBR time on the tablespace/datafile level, ...

I just refuse to have all important/non-important tables/indexes or data files with the same I/O characteristics. It is just out of mind to me.

It is not all SANE and RAID 1+0 for everything. I/O subsystems have simple non-priority non-realtime I/O requests without possibility to say OK this I/O request is more important then the other.

But of course, it is much easier to say like I am saying. We will need to wait for a long time for something more advanced to come.

Maybe Oracle will do something smart on the ASM level where he has a little bit more control.

At the end, Oracle is going the correct way, but is dissapointing to see that nothing is going on with the system statistics having in mind that the IO subsystems are probably the most critical part when it comes to the Oracle deployment (not to say just the optimizer cost calculation).

Of course somebody will say if you did the design wrong no Oracle optimizer will help you. But "I" am paying to Oracle for the optimizer while the design is my stupid brain responsibility.

Regards,
Zoran

• Christian Antognini <Christian.Antognini_at_trivadis.com> wrote:
  >
  > >No way Oracle is going to play this game because
  > the
  > >head on the disk(s) are who knows where, your
  > >"sequential" requests on the I/O sub queue(s) are
  > who
  > >knows how apart one from each other.
  >
  > Notice that on real systems, very often, data is
  > tripped as well... =
  > Therefore even if from an Oracle point of view some
  > reads are =
  > "sequential", perhaps they aren't.
  >
  >
  > Have a nice weekend,
  > Chris
  > --
  > http://www.freelists.org/webpage/oracle-l
  >
  

  ---

Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com

--
http://www.freelists.org/webpage/oracle-l

Received on Tue May 24 2005 - 04:18:37 CDT