Re: Question on gathering System Statistics

Your original note says "our application" - but now you're citing the E-Business suite. So which is it?
If "your" application is E-Business suite why did you ask the question?

The reason why you see the figures you do is because that's what "NOWORKLOAD" does. It deletes any existing system stats, sets the seek time and transfer rates to their (1970's) defaults and measures the CPU speed (of a single CPU) by running some baseline non-database CPU load. There's likely to be a small variation in this captured CPU speed over a series of calls because there's always likely to be some activity going on and a little scheduling time lost. (See also this note for running with the 'EXADATA' option, which explains something of how the arithmetic works: https://jonathanlewis.wordpress.com/2019/08/14/gather_system_stats/

In your case, with the E-Business directive, one of two things will happen when Oracle calculates the cost of a tablescan:

1. by default Oracle will assume that a multiblock read count will be "_db_file_optimizer_read_count" blocks (default 8) and use the ioseektim and iotfrspeed to calculate the size and time for a multiblock read - though it will try to use the largest possible size for a multiblock read at run-time.
2. if you've set the db_file_multiblock_read_count Oracle will copy that to "_db_file_optimizer_read_count" and use it in the calculation, and limit the run-time multiblock read size to that value.

Note - the default value for db_file_multiblock_read_count depends on several installation details but typically equates to 1MB / db_block_size, which usually means 128.

In terms of SSD a "seek time" means nothing. But the parameters are just numbers that go into a calculation.

If you wanted to create a meaningful pair of values for the two I/O times you could run up a simple C program that did thousands of 8KB direct I/O reads from a very large file, and then did thousands 1MB reads, and checked the average times for the two sizes of read. Then you could work out your own seek and transfer rates based on the simultaneous equations: seek + 1 transfer = time1
seek + 128 transfer = time2

transfer = (time2 - time1)/127 -- that's the time to transfer 8KB, from which you need to derive bytes per millisecond seek = time1 - transfer

Then use dbms_stats.set_system_stats to set the MBRC to 128, the ioseektim to seek, and the iotfrspeed to the derived bytes per millisecond.

Regards
Jonathan Lewis

On Fri, 29 Jan 2021 at 02:43, Hameed, Amir <Amir.Hameed_at_xerox.com> wrote:

> Thanks Jonathan.
>
> System statistics were collected in the past and also recently with the
> NOWROKLOAD option, which is what Oracle suggests for Oracle E-Business
> Suite R12 databases. The MRBC value was not set. What I find interesting is
> that the IO statistics numbers on both storage frames (VMAX250F versus
> PowerMax) are identical. I would also like to understand what the value of
> IOSEEKTIM represents in the world of SSDs or All Flash Array storage.
>
>
>
> SaveTime Stat Name Value
>
> ------------------------- --------------- ------------------------------
>
> 26-JAN-2021 23:07:15 CPUSPEED
>
> 26-JAN-2021 23:07:15 CPUSPEEDNW 3,528
>
> 26-JAN-2021 23:07:15 IOSEEKTIM 10
>
> 26-JAN-2021 23:07:15 IOTFRSPEED 4,096
>
> 26-JAN-2021 23:07:15 MAXTHR
>
> 26-JAN-2021 23:07:15 MBRC
>
> 26-JAN-2021 23:07:15 MREADTIM
>
> 26-JAN-2021 23:07:15 SLAVETHR
>
> 26-JAN-2021 23:07:15 SREADTIM
>
> Current CPUSPEED
>
> Current CPUSPEEDNW 3,518
>
> Current IOSEEKTIM 10
>
> Current IOTFRSPEED 4,096
>
> Current MAXTHR
>
> Current MBRC
>
> Current MREADTIM
>
> Current SLAVETHR
>
> Current SREADTIM
>
>
>
> Thank you,
>
> Amir
>
>
>
> *From:* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> *On
> Behalf Of *Jonathan Lewis
> *Sent:* Thursday, January 28, 2021 6:08 AM
> *To:* oracle-l_at_freelists.org
> *Subject:* Re: Question on gathering System Statistics
>
>
>
> CAUTION: This email originated from outside the organization. Do not
> click links or open attachments unless you recognize the sender and know
> the content is safe.
>
>
>
> A key question to ask first is whether you had system stats set/collected
> on the vmax250 and whether this was done in combination with setting the
> db_file_multiblock read count or leaving it to default, and had you done
> anything with the calibrate_io option?
>
> Ideally you probably want to believe that if you get the same plans with
> the Powermax you'll get the same, or better, performance. But if you do
> something that changes the optimizer's arithmetic you may get plan changes
> that result in random variations in performance.
>
>
>
> If you don't know what you've done about system stats in the past there's
> a script at the end of this blog note that will report them for you:
>
> https://jonathanlewis.wordpress.com/2019/08/14/gather_system_stats/
>
>
>
>
>
> Regards
>
> Jonathan Lewis
>
>
>
>
>
>
>
> On Tue, 26 Jan 2021 at 19:30, Hameed, Amir <Amir.Hameed_at_xerox.com> wrote:
>
> Hi,
>
> We are in the process of moving our applications from current storage
> array (VMAX250) to a new array (Powermax). From best practices standpoint,
> should we collect System Stats after moving to the new storage? If we
> should then is there a recommended way of gathering System Stats?
>
>
>
> Thanks,
>
> Amir
>
>

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