system statistics
Parag Jayant Patankar, August 23, 2005 - 5:25 am UTC
Hi Tom,
thanks for your great answers as usual to my question. I have not understood your following following explanation in this thread
==>
4) you don't really import "to and from users" you either import them to the 'system'. User "a" doesn't have private stats, user "b" doesn't have private stats. The system has system stats (or not)
Q1. For point 4, My understanding suppose I want to use system_statistics table to both database users a and b, ( only two users ) and having same workload then import system statistics into only "sys" user !!! No need to have private statistics for user a and b. Am I correct ?
Q2. If answer to my Q1 is yes, then does it will affect the performance of Oracle ?
Q3. Suppose I am looking for Oracle database for which absolutley no information available then how can I decide system statistics is applied to database or not How can I find out ?
regards & thanks
pjp
August 24, 2005 - 4:10 am UTC
your data dictionary either
a) has system stats
b) does not have system stats
individuals don't have their own private stats.
You would query your dictionary to see if stats are there. select * from sys.aux_stats$;
system stats
Shankar, August 23, 2005 - 6:35 pm UTC
From above,
Re the values for SREADTIM, MREADTIM: Do these values represent reading from os cache or from disk? Thanks.
August 24, 2005 - 8:56 am UTC
physical IOs
SREADTIM Time to read single-block I/O, in milliseconds
MREADTIM Time to read multiple blocks, in milliseconds
CPUSPEED Available cycles per second, in millions (relative CPU speed)
MBRC Average observed multiblock read count
MAXTHR Maximum I/O system throughput in bytes/second
SLAVETHR Average IO Slave IO through put in bytes/second
A reader, March 23, 2006 - 9:42 am UTC
Tom,
I am about to re-read you book (Effective Oracle by Design).
I am reading Chapter 6 now. My questions are:
1. Can you explain more how to gather system statistics. You did this exellent with for e.g. Statspack - short reports (15-30) minutes... What is about sytem statistics?
Typical load - okay, but how long/how oft? Should I start gathering stats for 15-30 min or for e.g. the whole day (8a.m. - 18 p.m.) would be more represantative?
We have OLTP sytem with about 200 users and we did not run any batch jobs nightly or so.
Is it appropriate to start/stop the job manuel by typing execute dbms_stats.gather_system_stats('Start/Stop')?
2. My second question is the following one.
You says that gathering system statistics is alternative to optimizer_index_* parameter. Another source (Jonathan Lewis) says that:
"It is still meaningful, by the way, to use optimizer_index_cost_adj as a clue to table caching effects (specifically, what percentage of single block table reads are likely to turn into real read requests) even when using system statistics. There are some indications in 10g, though, that even this clue will become unnecessary in the not too distant future..."
So if I think about optimizer_index_cost_adj as MREADTIM/SREADTIM, what is a reason to set it manuel? I thought that the system statistics are there to provide the posibility NOT TO GUESS, but to messure the actual system?
So how should I set the two optimizer_index_* parameters if I already collected system statistics (MREADTIM/SREADTIM is
0.954/2.235) in Oracle 9i AND 10g?
How is CBO effected if it has both (system stats and optimizer_* params)?
Thanks in advance
March 23, 2006 - 11:14 am UTC
1) during peak load - need not be long (all day, no. 15 minutes, sure), and again only if something radically changes. The only goal with statistics gathering is to CHANGE PLANS. If nothing about your hardware/way you use the hardware changes, the statistics are still appropriate.
2) I would not set the optimizer_index* parameters unless and until I was "forced" to (and I haven't been yet)
Peak load?
A reader, March 23, 2006 - 11:29 am UTC
Tom,
In your book you writes "...when the system runs representative load...". This means for me average load.
1. I know the system pretty well. So we have some short peaks during the day when CPU load up to 100 %. The rest of the day we have something about 80 % idle. Do you means I should gather statistiks during this short peaks?
Won't this "mislead" the optimizer (applying statistiks gathered during peak load to the normal activity)?
2. Does this mean simple not to set both optimizer_* parameters (living at it default).
Thanks
March 23, 2006 - 1:34 pm UTC
1) it is mostly about IO - measuring your single and multi-block IO speeds (it'll get the cpu speed right pretty much regardless) and multi-block IO sizes
2) My choice is to leave them all at defaults unless really hard pressed not to for some reason (and it has been a while since I've felt compelled to)
System Statistics --C program
Reader, November 19, 2006 - 2:13 pm UTC
About gathering System Stats --
Some say use Orion to get an idea about how the disk I/O will be OR some say use a simple C program .
What is this C program -Can you help me in understanding .
Even Jonathan Lewis points in his presentation that one can use a C program to see the time taken for a single read and multiblock read .
What is this program or How to write this program.
THANKS
November 20, 2006 - 2:48 am UTC
are you asking me how to write a C program to read from disk to test disk timings?
if so, please
a) get a C programmer who knows their stuff
b) use the program you were pointed to
If you know how to program C, you would know how to write a C program to read from disk. It is pretty easy.
Great practical info on system stats!
Charlie B., December 01, 2006 - 3:58 pm UTC
Changing System Statistics
Shivdeep Modi, February 05, 2007 - 9:12 am UTC
Hi,
I've read a couple of articles wherein it is showed that we can set the system statistics. In Cost-Based Oracle Fundamentals by Jonathan Lewis, he demonstrates the optimizer behavior by setting system statistics.
begin
dbms_stats.set_system_stats('CPUSPEED',500);
dbms_stats.set_system_stats('SREADTIM',5.0);
dbms_stats.set_system_stats('MREADTIM',30.0);
dbms_stats.set_system_stats('MBRC',12);
end;
/
So the question is:
Is there anyway to know the current values of the above settings so that I can revert them after testing. Setting of these statistics will surely have far reaching effects for all the queries I guess. I would'nt set them in a production system without testing. But even for a development system I need to know the values that I would be changing. Is it really relevent setting these statistics apart from gathering statistics on objects or should they be used when nothing else work?
Thanks in advance.
Regards,
Shivdeep Modi
February 05, 2007 - 9:43 am UTC
select * from sys.aux_stats$;
Development Database
Andrew Markiewicz, May 14, 2007 - 10:46 am UTC
Tom.
We are in the process of upgrading from 9i to 10g and starting to use system statistics. Since the system statistics are used per database instance and not per user/schema, what approach do you recommend for tuning within a development environment?
A few issues we are contending with,
1) Different machines for production and development have different system performance stats. We are planning to collect system statistics on the production machines and import those system statistics to the development database so programmers will be tuning execution plans to run on a production system. That may make tuning a bit more confusing since it may not be using the optimal plan for that machine, but we are mainly concerned with production performance. (If the stats for the test and production machines are close enough, this is a non-issue, but we do not know that at the moment.)
2) OLTP stats and batch processing stats seem to be different enough that we may need to use a different set of statistics in production at each of these times. But since the database is only allowed one set of system statistics, how do we allow developers that may be working concurrently on both OLTP and batch programs to use the system statistics each will need to tune each of their respective SQL?
Thanks
Andrew
System Statistics?
Anonymous, June 28, 2007 - 2:05 pm UTC
Tom, we're running mostly 9i and a few instances of 10g(we're still carving out a stone wheel for testing). Using system stats sounds great and very appealing for the systems I support and I'm ready to go forward with it.
On the other hand, other individuals that have more say are against it because of the potential slowness that can be incurred in making changes. Test and dev would be good environments to experiment in, but their argument is the fact is that production is never the same and a realistic production load is hard to replicate in test. The well has also been poisoned as I've heard that Oracle support has even said that relatively few people are using system stats because of the negligible gains and increased headache incurred maintaining multiple sets of stats for different load conditions.
Bottom line, is this a true statement about the amount of systems actually using system stats?
Second, is there that much of a threat that performance will actually degrade by capturing and supplying more stats to the CBO or is this just a wives tale?
July 02, 2007 - 10:34 am UTC
if the statistics are representative - good - then the CBO will use the good information to come up with realistic plans.
if the stats are not representative - then the CBO will use incorrect information and develop non-realistic plans.
What do system stats give us:
a) cpu speed
b) single block IO speed
c) multiblock IO speed
d) a true multi-block read count - based on actual observed values. You might set db_file_multiblock_read_count to 64 - but find that the most blocks ever read in bulk was 12 on your system - the optimizer would use 12, not 64, when costing the scan.
If those numbers are representative for your system, reflect reality, then the cbo will use them to better estimate the true cost of doing the single block IO's versus full scanning.
you can always just SET THEM to what is representative for your system as well.
Alexander the ok, July 11, 2007 - 9:55 am UTC
Hi Tom,
I we are noticing poor performance querying v$ views, dba_ views, would it be wise to gather statistics on sys? You always say don't touch sys so I was wondering if that could be dangerous.
We have a large 700 gb database that we cannot run our normal scripts to gather information about the database that we can on smaller databases. Thanks.
July 11, 2007 - 1:01 pm UTC
in 8i/9i - test it first (you would gather on fixed tables as well, v$ views would depend on those)
and for the dictionary, you only typically need do it once in production - for fixed views, after parameter changes that could affect them.
you could dbms-stats.export the existing (if any) statistics so if something goes awry, you can get right back to where you are at least by putting them back (test that process out first to become comfortable with it)
in 10g, it happens by default and should be done, yes.
Alexander the ok, July 11, 2007 - 4:03 pm UTC
D'oh I always forget the version, 10g yes.
(As a side note it might be cool to have a version drop down like the rating and on the submit new question page so we are forced to provide that.)
Are you sure Oracle gathers it by default like it does for CBO stats? I queried aux_stats and saw that it only has them from when I first created that database.
SQL> select * from sys.aux_stats$;
SNAME PNAME PVAL1 PVAL2
------------------------------ ------------------------------ ---------- --------------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 02-16-2006 09:43
SYSSTATS_INFO DSTOP 02-16-2006 09:43
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 770.046388
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM
SYSSTATS_MAIN MREADTIM
SYSSTATS_MAIN CPUSPEED
SYSSTATS_MAIN MBRC
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN SLAVETHR
I ran the gather_system_stats procedure and all those numbers changed.
July 11, 2007 - 10:26 pm UTC
if you install 10g fresh, it'll have a default job that does this....
but, you asked about gathering stats on SYS (the owner of some tables), but what you queried was system statistics (info about the BOX you are running on)
do you know they are very very different things?
I switched subjects above sorry sys stats vs system
Alexander the ok, July 11, 2007 - 4:16 pm UTC
July 11, 2007 - 10:27 pm UTC
no, there are stats on SYS owned objects
there are system statistics
very very different..
system statistics on virtualised systems
markus, April 11, 2008 - 5:41 am UTC
hi tom,
we have a shared and virtualised environment. several OLTP databases share a virtual host which is a logical partition (IBM LPAR) of a physical one (IBM P570). some of the virtual hosts have dedicated host bus adapters others share a physical one (VIO). the data resides on some EMC DMX san's where we even don't know the distribution.
would you use (gather) system statistics in that case? the resources available in this virtualised environment might ever change. so I don't know if system statistics makes sense in that case?
April 11, 2008 - 8:11 am UTC
system statistics, while the machines are under load, would give you a baseline - one we would use to derive plans.
Else, there would be no system statistics, no baseline, nothing to work from
which would be worse - I'd rather have "pretty good" system statistics than "no idea"
Auto gathering of system statistics
Deepak, March 30, 2012 - 3:30 am UTC
Hi Tom,
We have an environment where the application would trigger the statistics collection process.
As per my my understanding "system statistics" need to be collected when the system is under peak load. Please correct me if my understanding is wrong.
Since our application does not have the intelligence to determine the time period for peak load we may not be able to trigger system statistics collection during peak load.
1> Will it be a good idea to use the system statistics collection in the following fashion?
begin
dbms_stats.gather_system_stats('START');
end;
<SOME INTERVAL, SAY 8 HOURS>
begin
dbms_stats.gather_system_stats('STOP');
end;
<SOME INTERVAL, SAY 8 HOURS>
begin
dbms_stats.gather_system_stats('START');
end;
<SOME INTERVAL, SAY 8 HOURS>
begin
dbms_stats.gather_system_stats('STOP');
end;
2> What does Oracle do internally when the system statistics collection process is started.
3> Does system statistics collection process affects system performance?
Need your guidance on this. Please help.
MBRC in SYS.AUX_STATS$ dictionary.
Rajeshwaran, Jeyabal, March 27, 2018 - 1:47 pm UTC
Team,
Was working on WORKLOAD stats gathering for MBRC parameter and found that MBRC value set in Oracle 11g (11.2.0.4)
rajesh@ORA11G> select * from sys.aux_stats$;
SNAME PNAME PVAL1 PVAL2
------------------------------ ------------------------------ ---------- --------------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 03-27-2018 18:53
SYSSTATS_INFO DSTOP 03-27-2018 18:54
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 2086
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM
SYSSTATS_MAIN MREADTIM
SYSSTATS_MAIN CPUSPEED 2087
SYSSTATS_MAIN MBRC
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN SLAVETHR
13 rows selected.
rajesh@ORA11G> exec dbms_stats.gather_system_stats(gathering_mode=>'START');
PL/SQL procedure successfully completed.
rajesh@ORA11G>
rajesh@ORA11G> declare
2 n number;
3 begin
4 dbms_job.submit( n, 'dw_style;');
5 dbms_job.submit( n, 'dw_style;');
6 dbms_job.submit( n, 'dw_style;');
7 commit;
8 end;
9 /
PL/SQL procedure successfully completed.
rajesh@ORA11G> exec dbms_stats.gather_system_stats(gathering_mode=>'STOP');
PL/SQL procedure successfully completed.
rajesh@ORA11G> select * from sys.aux_stats$;
SNAME PNAME PVAL1 PVAL2
------------------------------ ------------------------------ ---------- --------------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 03-27-2018 18:54
SYSSTATS_INFO DSTOP 03-27-2018 18:55
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 2086
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM 23.45
SYSSTATS_MAIN MREADTIM 7.696
SYSSTATS_MAIN CPUSPEED 2087
SYSSTATS_MAIN MBRC 16
SYSSTATS_MAIN MAXTHR 49162240
SYSSTATS_MAIN SLAVETHR 1480704
13 rows selected.
rajesh@ORA11G>
but on 12.2 that is not properly set yet.
rajesh@ORA12C> create or replace procedure dw_style
2 as
3 c int;
4 begin
5 select /*+ noparallel(b1) noparallel(b2) noparallel(b3) full(b1) full(b2) full(b3) */ count(*) into c
6 from demo.big_table b1,
7 demo.big_table b2,
8 demo.big_table b3
9 where b1.id = b2.id
10 and b2.id =b3.id;
11 end;
12 /
Procedure created.
rajesh@ORA12C> exec dbms_stats.delete_system_stats;
PL/SQL procedure successfully completed.
rajesh@ORA12C> select * from sys.aux_stats$;
SNAME PNAME PVAL1 PVAL2
------------------------------ ------------------------------ ---------- --------------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 03-27-2018 18:56
SYSSTATS_INFO DSTOP 03-27-2018 18:56
SYSSTATS_INFO FLAGS 0
SYSSTATS_MAIN CPUSPEEDNW 1134
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM
SYSSTATS_MAIN MREADTIM
SYSSTATS_MAIN CPUSPEED
SYSSTATS_MAIN MBRC
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN SLAVETHR
13 rows selected.
rajesh@ORA12C> exec dbms_stats.gather_system_stats(gathering_mode=>'START');
PL/SQL procedure successfully completed.
rajesh@ORA12C>
rajesh@ORA12C> declare
2 n number;
3 begin
4 dbms_job.submit( n, 'dw_style;');
5 dbms_job.submit( n, 'dw_style;');
6 dbms_job.submit( n, 'dw_style;');
7 commit;
8 end;
9 /
PL/SQL procedure successfully completed.
rajesh@ORA12C> exec dbms_stats.gather_system_stats(gathering_mode=>'STOP');
PL/SQL procedure successfully completed.
rajesh@ORA12C> select * from sys.aux_stats$;
SNAME PNAME PVAL1 PVAL2
------------------------------ ------------------------------ ---------- --------------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 03-27-2018 18:56
SYSSTATS_INFO DSTOP 03-27-2018 18:58
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 1134
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM 10.624
SYSSTATS_MAIN MREADTIM 19.788
SYSSTATS_MAIN CPUSPEED 1135
SYSSTATS_MAIN MBRC 0
SYSSTATS_MAIN MAXTHR 86280192
SYSSTATS_MAIN SLAVETHR 2920448
13 rows selected.
rajesh@ORA12C>
Is that MBRC got depreciated or not supported in Oracle 12c (12.2)? please confirm.
March 28, 2018 - 2:19 am UTC
My hypothesis is that we are measuring the multiblock reads achieved by 'db file scattered read' (ie, a read into the buffer cache).
You are most probably not doing any, because you'll be using serial direct read.
Try repeating your exercise with:
alter session set "_serial_direct_read" = NEVER;
SQL> exec dbms_stats.delete_system_stats;
PL/SQL procedure successfully completed.
SQL> select * from sys.aux_stats$;
SNAME PNAME PVAL1
------------------------------ ------------------------------ ----------
SYSSTATS_INFO STATUS
SYSSTATS_INFO DSTART
SYSSTATS_INFO DSTOP
SYSSTATS_INFO FLAGS 0
SYSSTATS_MAIN CPUSPEEDNW 3375
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM
SYSSTATS_MAIN MREADTIM
SYSSTATS_MAIN CPUSPEED
SYSSTATS_MAIN MBRC
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN SLAVETHR
13 rows selected.
SQL> exec dbms_stats.gather_system_stats(gathering_mode=>'START');
PL/SQL procedure successfully completed.
SQL> alter session set "_serial_direct_read" = NEVER;
Session altered.
SQL> select count(*) from t;
COUNT(*)
----------
10000000
1 row selected.
SQL> select min(x) from t;
MIN(X)
----------
1
1 row selected.
SQL> select count(*) from t;
COUNT(*)
----------
10000000
1 row selected.
SQL> select count(*) from t;
COUNT(*)
----------
10000000
1 row selected.
SQL> select max(x) from t;
MAX(X)
----------
10000000
1 row selected.
SQL> exec dbms_stats.gather_system_stats(gathering_mode=>'STOP');
PL/SQL procedure successfully completed.
SQL>
SQL> select * from sys.aux_stats$;
SNAME PNAME PVAL1
------------------------------ ------------------------------ ----------
SYSSTATS_INFO STATUS
SYSSTATS_INFO DSTART
SYSSTATS_INFO DSTOP
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 3375
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM
SYSSTATS_MAIN MREADTIM 1.952
SYSSTATS_MAIN CPUSPEED 3375
SYSSTATS_MAIN MBRC 119
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN SLAVETHR
13 rows selected.
SQL>
SQL>
SQL>
SQL>
Effective Oracle by Design
Asheish, November 25, 2020 - 9:47 am UTC
Hi Tom, "Effective Oracle by Design", Published on Aug 2003,
Is this book still relevant / worth for Oracle 12c and /or 19c database. ? Or any new release available please guide.
Thanks
November 27, 2020 - 7:12 am UTC
I have the book. Yes there will be a few things in there that are out of date, but it is still an excellent baseline reference for building great applications