A few days ago I received an email about system statistics. I decided it was worth replying to, provided I wrote my response up as a series of questions and answers on the blog. (I don’t tend to respond to individual questions – it’s not an effective use of my time – so there has to be a good reason for replying).
The italicised text below is from the original email, the rest is my response.
I hope you don’t mind me emailing you. However, I would like to get your opinion on Oracle System Statistics (CPU Costing).
I regard system statistics as a critical item in 9i and believe that missing system statistics was the number one reason behind poor performance after migrating from 8i to 9i.
I agree – although setting optimizer_index_cost_adj to a suitable value had a similar effect in 8i; especially if you used the single and multi-block read wait times as a guideline.
However, there are a few items in regards to system statistics that I am not sure about:
1. My understanding is that to make effective use out of system statistics MREADTIM must be greater than SREADTIM and in fact be at least 1.2 times greater. Therefore after gathering stats I would often manually update them to set MREADTIM to a greater setting than SREADTIM (often I will set SREADTIM to 2 and MREADTIM to 5). Is this actually a good thing to be doing?
Things may have changed in the latest releases, but initially if mreadtim was less than sreadtim (which could happen with SANs and other caching mechanisms) then the traditional I/O costing applied – although a CPU component would still be added to the I/O cost.
I approve of your ‘fake’ settings as they are probably a reaonable indicator of relative disk response times for single block reads and multi-block reads of about 8 blocks. I would be perfectly happy, in an OLTP system particularly, to do the same.
2. Some authors suggest that we should be gathering different levels of system statistics at different times of the day; where as my school of thought is is best to gather them once then leave them alone (if stats are producing optimal explain plans why change them). What are your thoughts on this?
This “different times of day” approach is in principle correct – and an approach that I originally thought to be quite reasonable.
There is a problem, however, with caching mechanisms and bad code. You can get into a feedback loop where bad code is protected by (say) a file-system cache which makes multiblock reads appear to be very fast.
Collecting system stats at the wrong time therefore encourages the optimizer to believe that multi-block reads are always really fast – so it does more of them. This is why I now lean towards simply setting some times that reflect the actual disk speeds when tested in the absence of Oracle.
3. In 10g CPU Costing is enabled by default so the big drop in performance we witnessed when going from 8i to 9i is not so apparent when going from 9i to 10g. I therefore tended not to gather system stats manually as performance seemed to behave well enough without doing this. However after recently reading your book “Cost-Based Oracle Fundamentals” I discovered the impact of what “DB_FILE_MULTIBLOCK_READ_COUNT” does without system stats being gathered. Would you say gathering system stats in 10g is as critical as it is in 9i?
There have been some changes since I wrote the book. In 10.2, I would experiment with NOT setting the db_file_multiblock_read_count at all. Oracle then uses two internal read counts, one for the optimizer calculations (which becomes the assumed MBRC) and one for the run-time physical read attempt.
On many platforms the defaults leave Oracle deriving values of sreadtim and mreadtim that are quite reasonable – and happen to match quite well the ratio of the values that you’ve been using.
4. Lastly with system statistics gathered is there any benefit to be gained of still using “DB_FILE_MULTIBLOCK_READ_COUNT”?
See (3) – if you set the parameter, check what Oracle does with the hidden parameters _db_file_exec_read_count and _db_file_optimizer_read_count (if they exist in your version). If they don’t exist (and you’ve gathered system statistics) the value you set is the value that Oracle tries to use for reads at run-time, but it has no effect on the cost.
Oracle Scratchpad 
Hi Jonathan,
There seems to be varying levels of importance assigned to system statistics, and I for one am currently still trying to gather enough information before forming an opinion. If you have time would you mind commenting on my comment and the reply at http://www.oraclemusings.com/?p=55
Thanks
Graham
Comment by Graham Oakes — May 21, 2007 @ 11:19 am BST May 21,2007 |
Graham,
I think system statistics are very important but, as you can see from the notes above, I have changed my views about the safest way to use them.
Two thoughts: In general the impact of the CPU cost component is small relative to the I/O cost, so I don’t think you often see much difference in execution plans due to disk and CPU speeds getting ‘out of sync’ compared to the production box. A slightly more serious threat could come from the slower disk speeds – if dbms_stats.gather_system_stats manages to record realistic times.
Say you go from a 15000 rpm disk with a seek time of 6ms to a 7200 rpm disk with a seek time of 10ms.
A single (8KB) block read on the fast disk will, in principle, take about 7ms, a multiblock read of 8 blocks will take about 14ms so if the optimizer has picked these figures up in its MBRC, sreadtim and mreadtim it will trade one multiblock read for two single block reads.
A single (8KB) block read on the slow disk will take about 12ms, a multiblock read of 8 blocks will take about 26ms, so the optimizer will trade one multiblock read for a little over two single block reads – which means there will be a small bias against segment scans introduced by the slower disc.
Now the way in which the trade-off takes place depends on the size of the multiblock reads (at 16 blocks, there will be a small bias in favour of segment scans introduced by the slower disc), but there will be a change. And that constitutes one argument in favour of copying the system stats from the production system to the development system to stabilise the behaviour of the optimizer.
Comment by Jonathan Lewis — May 21, 2007 @ 9:12 pm BST May 21,2007 |
Jonathan,
While experimenting with collection of system stats, I noticed that the type of IO performed during the collection period influences the values computed for sreadtim and mreadtim.
For example, If the collection period is an hour and if you perform lot of full tablescans over single block reads (using an index) during this time, I noticed that the value of mreadtim is much lower than sreadtim. The only to address this anomoly is to have both kinds of IO (under true load conditions) and increase the duration of collection period.
In our case, I noticed the default values computed by optimizer in the absense of system statistics are not working well.
Is there a guide line as to what the values should be if the load varies throughout the day?
Also, Is there any impact on optimizer plans, if I set mreadtim = 5 * sreadtim vs. mreadtim = 10 * sreadtim?
Comment by Ranga — May 21, 2007 @ 10:01 pm BST May 21,2007 |
Jonathan,
I have recently posted on the comp.databases.oracle.server group regarding system statistics to which you responded. There you indicated that the default NOWORKLOAD system stats might well be a good enough approximation while providing stability.
(From your reply)
“The instance estimates an I/O seek time and an I/O transfer
rate (which default to 10m/s and 4.096 bytes per millisecond
respectively) then calculates the sreadtim and mreadtim based
on your initial setting for the db_file_multiblock_read_count ”
While I agree this will be stable, but is it representative of the system? The main purpose of the CPU stats was to represent the system as accurately as possible. (Perhaps a handful of parameters is not sufficient enough to do that.)
I have been sampling stats on our 9i db for a few weeks now at 1/2 hr intervals to try to get a good average for sreadtim and mreadtim while maintaining the data for specific times (online vs batch) to see if there are differences. From the discussion in your blog, I am under the assumption that mreadtim should logically be larger (at least 1.5 times?) than sreadtim. But we have an EMC Symmetrix which I believe is causing the mreadtim to be smaller than expected.
SREADTIM MREADTIM 5.354 5.15271946 batch 4.94325967 3.26359116 onlineSoon I will get a 10g clone of our production system to test on, but my thoughts are that if I were to use these numbers I would see a change in plans towards FTS. Would that be correct or is that just an oversimplification?
But again, if this is truly represnetative of our system, these are the numbers I should be using (theoretically).
One last point/question.
“This is why I now lean towards simply setting some times that reflect the actual disk speeds when tested in the absence of Oracle.”
Would not the Symmetrix cache also effect the test of the C programs you provided? That is assuming the Symmetrix causes a reduction in the mreadtim.
Thanks for your time and input.
Andrew
Comment by Andrew Markiewicz — May 23, 2007 @ 9:25 pm BST May 23,2007 |
Andrew,
The problem with the Symmetrix is that it starts doing readahead when you request a read in excess of 32KB. So if you have a tablescan of a few MB, the first “db file scattered read” might operate at a ‘realistic’ speed; but when Oracle requests all subsequent reads the Symmetrix will have preloaded its cache. So Oracle gets a very fast average read time for the table scan – which is where you get your suspect mreadtim.
This will (probably) have been nice for the process doing the tablescan, but it will have been overloading the discs and causing any concurrent random I/Os to take longer because of queueing effects. It’s a case of local vs. global optimisation – a single extreme process can perform well, cripple the system, and cause the system statistics to encourage other processes to start behaving badly as a consequence.
Ideally, therefore, you want an mreadtim that is a real measure of the impact a tablescan has on the way that everyone else can use the discs so that you can share the disc resource fairly.
The C progam approach bypasses the readahead problem because you aim to simulate randomly scattered multiblock reads. This means every multiblock read you issue will push the Symmetrix into readahead; but the next multiblock read will not want the prefetched blocks, which means you don’t end up seeing a spuriously low average read time.
Comment by Jonathan Lewis — May 23, 2007 @ 9:52 pm BST May 23,2007 |
Hi Jonathan,
With system statistics gathered does the parameter db_file_multiblock_read_count still do anything or does this then become obsolete.
For example will it actually speed up full table scans by reading more blocks at a time while not affecting the calculation of explain plans?
Kind regards, Col.
Comment by Colin Smith — June 6, 2007 @ 11:23 am BST Jun 6,2007 |
Jonathan,
Would increasing the time during which statistics are gathered not permit to reduce the caching issue? If the system statistics are gathered for, say, 7 hours, the average would prevail instead of possible peak usage due to bad code or others.
Regards
Donat
Comment by Donat Callens — June 18, 2007 @ 8:53 am BST Jun 18,2007 |
If the system statistics are gathered for, say, 7 hours, the average would prevail instead of possible peak usage due to bad code or others.
Unless it’s the bad code that prevails.
Comment by Alex Gorbachev — June 18, 2007 @ 12:30 pm BST Jun 18,2007 |
Col,
Sorry about the late follow-up. If you have system statistics set then the MBRC value is used by Oracle for calculating a cost figure, but the setting of the db_file_multiblock_read_count is used at run-time to execute the tablescan. The major benefit of system stats is that you can safely have a large db_file_multiblock_read_count value, but Oracle will only use it when the optimizer calculations show that it is sensible to do so.
Donat,
Alex has raised the obvious reply to your question. Bear in mind, also, that it’s not just the bad code, it’s the constant threat that a few ‘unlucky’ tablescans can produce a misleading mreadtim that can persist indefinitely however many other reads you do by single block read.
Comment by Jonathan Lewis — June 18, 2007 @ 6:23 pm BST Jun 18,2007 |
Hi All,
I have a test case (see viveklsharma.blogspot.com) which says that
in Oracle 9i
“If the value of MReadtim is less than SReadtim, the IO Cost component is calculated based on Traditional Costing Methodology and the value of db_file_multiblock_read_count (dfmrc) does have an impact on the cost calculation.
Whereas, if MReadtim is more than SReadtim, the IO Cost is calculated based on CPU Costing
methodology and the value of dfmrc does not have any impact on the cost.”
In Oracle 10g Release 2,
“10g Release 2 does not consider traditional IO Costing methodology. But, it seems that in 10g release 2, if MReadtim is less than SReadtim, than the IO Cost is calculated based on IO Seektim & IO Transfer Speed
and #MRds is calculated based on stored value of MBRC in sys.aux_stats$. Hence, the value of dfmrc does not have an impact on cost calculation.
whereas, if MReadtim is larger than SReadtim, than IO Seektim and IO Transfer speeds are not used to calculate SReadtim and Mreadtim and again, the value of dfmrc does not have any impact on the cost. ”
In Oracle 10g, the value of dfmrc only affects the Cost calculation, if System Statistics are not gathered and Optimizer derives this based on CPUSPEEDNW (No workload) values.
Regards
Vivek
Comment by Vivek Sharma — June 26, 2007 @ 4:10 am BST Jun 26,2007 |
JL,
The cost column printed in an explain plan is irrelevant to response time as of now.What iam confused is what is the purpose of printing such a number,when i can’t infer anything regarding response time or what is the use of such number which is internal to oracle .Should i consider only the cardinality reported and ignore the number(wolfgang breitling).
Regards
karthik
Comment by karthik — July 8, 2007 @ 1:18 pm BST Jul 8,2007 |
Karthik.
Your opening statement is incorrect, see https://jonathanlewis.wordpress.com/2006/12/11/cost-is-time/ and its following comments.
You may argue that the cost is irrelevant because you don’t understand it, but the same argument could apply to the cardinality, viz: it can be wrong, so it’s irrelevant.
To write good SQL for complex queries you design the code by understanding the data, access paths, and reasonable caching expectation – and that usually makes all the numbers in the plan “irrelevant”.
To do trouble-shooting AFTER you’ve made a mistake, it is very convenient to compare the cardinality (plan prediction) with the row source counts (tkprof rows) – if you know how. And that’s what makes Wolfgang’s approach sensible.
Comment by Jonathan Lewis — July 8, 2007 @ 1:45 pm BST Jul 8,2007 |
Jonathan,
Thanks a lot. Please give me a clarification. tom kyte says “The cost is a set a numbers assigned by the optimizer to various steps of a query based on available statistics. These costs are then factored together to come up with an over all query cost — a single number that assigns a relative “cost” to a query plan.”—So should i concentrate on reducing the relative cost of steps which i found unapplicable(eg:FTS ove IR scan in a scenario favoring IR) in a plan or the “relative cost of sql statement” itself in comparison with an identical statement.
Comment by karthik — July 8, 2007 @ 5:49 pm BST Jul 8,2007 |
I found something similar to my question in http://www.freelists.org/archives/oracle-l/01-2005/msg00539.html regarding the comparison of cost.Please enlight me whether we cannot compare cost of 2 different sql statements with same cost or compare cost of 2 different plans for sql statements
Comment by karthik — July 8, 2007 @ 5:59 pm BST Jul 8,2007 |
Karthik,
If you check the item on freelists, you will note that I wrote it. It explains why we should in principle be allowed to compare costs; it explains why in practice it does not make sense to; and it closes with a few rhetorical questions demonstrating how much uncertainty there should be in saying “you cannot compare costs”.
The basic answer to your question is this:
Every single predictive number you see ANYWHERE from an execution plan could be complete rubbish. The figures you get by using the actual values from the v$sql_plan_statistics_all tell you (with some limitations) how much work Oracle did (and sql_trace/tkprof may report the row counts). Comparing predictions with actuals may allow you to work your way towards a better plan – which is the Wolfgang Breitling method. Understanding what the SQL is supposed to represent and knowing the data is the best way of identifying the correct path.
I see you have raised this question on AskTom and have an odd comment there about ‘CPU costing being more appropriate if CPU time is in your “Top 5 timed events”. That’s probably one of these fairytales that gets around because someone has read too much into a name. “CPU Costing” isn’t just about CPU – it also changes the cost of multiblock I/Os (sometimes quite dramatically).
If you are still uncertain, you will have to read the first couple of chapters of Cost Based Oracle – Fundamentals.
Comment by Jonathan Lewis — July 10, 2007 @ 5:52 am BST Jul 10,2007 |
hi mr lewis :
in your book Cost-Based Oracle Fundamentals you have said cost in 10g is caculated according following formula
cost=
(
#SRds * sreadtim +
#MRds * mreadtim +
#CPUCycles / cpuspeed
) / sreadtim
i want to check an example for this formula but my answer is wrong.
please look at this :
execute dbms_random.seed(0)
create table t1
pctfree 99
pctused 1
as
select
rownum id,
trunc(100 * dbms_random.normal) val,
rpad(‘x’,120) padding
from
all_objects
where
rownum is zero for table scan
#MRds * mreadtim / sreadtim + => (10000/12)*(30/5)= 5000 ->5000+1=5001
#CPUCycles / (cpuspeed * sreadtim) => 73932766/(500*(5*1000))=29.5731064->30
)
so cost = 5001+30=5031
but we know cost is 5103 !!!
i think io_cost is wrong !!!
best regards
Comment by BAHMAN MIRHOSSEINI — October 18, 2007 @ 11:15 am BST Oct 18,2007 |
Bahman,
Some of your text has disappeared because you have used a ‘less than’ sign in the code. I assume from your comment “we know cost is 5013” that you have included an execution plan showing this cost.
Based on that figure, I believe you have created the table in a tablespace using ASSM – which means it has (probably) reached a high water mark of 10,143 blocks, rather than 10,000.
Comment by Jonathan Lewis — October 18, 2007 @ 5:40 pm BST Oct 18,2007 |
Hi Jonathan,
For Oracle9i, if there are no system statistics, cpu costing would be off, right? In that case, is there any system related parameter (cpu, I/O) that could affect cost calculation of the execution plan? Cost calculation would be the same as in Oracle 8i? ( _optimizer_cost_model=choose )
Thank you very much!
Best regards,
Mirjana
Comment by Mirjana — October 31, 2007 @ 12:12 pm GMT Oct 31,2007 |
Hi Jonathan,
How do I gather system statistics for a 2 Node RAC ? I Gathered these on Node 1 and some queries plan changed, taking lot of time for execution from this node, whereas the plan remained unchanged and good on Node 2. When I gathered Stats from Node 2, the plan changed from this node and good plan remained on Node 1.
In both the cases, shared pool was flushed from both the nodes after gathering stats.
What should be the strategy for this ?
Comment by VSharma — February 24, 2008 @ 7:12 am GMT Feb 24,2008 |
VSharma,
You’ve picked up a flaw in system statistics that I mentioned in my book – you get one set of statistics that is supposed to be good for every node.
It is a little odd that with the same object statistics and the same system statistics you should get different execution plans.
However, if other parameters vary between the two nodes, plans can differ.
It’s also possible that the effects of other activity, and automatic memory management could have an impact which has not yet been documented. For example, the cost of a hash join depends intially on the _smm_min_size parameter – but possibly this value is dynamic and could change if a lot of PGA memory was in use (I don’t think it does – but I haven’t really tried to find out yet).
At the moment, your best bet is to run a 10053 trace on a (preferably simple) statement that produces different plans in the different instances, and see if you can spot where – hence why – the calculations are different. Start by looking at the parameter listing sections and the ‘single table access’ sections.
Comment by Jonathan Lewis — February 24, 2008 @ 2:11 pm GMT Feb 24,2008 |
As a quick reference point – there is a bug in versions of 10g prior to 10.2.0.4.
The manuals say that setting db_file_multiblock_read_count to zero will have the same effect unsetting it – i.e. allow Oracle to choose the optimal value to user for the _db_file_exec_read_count.
In fact, if you set the parameter to zero, Oracle will silently set it to one – which is likely to do strange things to execution plans and make serial tablescans et. al. use single block reads all the time.
This is reported as bug 5768025, fixed in 10.2.0.4
Comment by Jonathan Lewis — July 19, 2008 @ 3:31 pm BST Jul 19,2008 |
Hi Jonathan,
It’s me Pavan Kumar from oracle forums. Can check the test case.
11:54:18 zug7_real>show parameter db_file
NAME TYPE VALUE
———————————— ———– ——-
db_file_multiblock_read_count integer 128
db_file_name_convert string
db_files integer 200
12:06:29 zug7_real>select num_rows, blocks from user_tables where table_name=’T1
‘;
NUM_ROWS BLOCKS
———- ———-
7956 8086
12:24:34 zug7_real>explain plan for select max(object_id) from t1;
12:24:46 zug7_real>select options, operation, cost, cpu_cost, io_cost from plan_
table;
OPTIONS
——————————————————————————–
OPERATION COST CPU_COST IO_COST
—————————— ———- ———- ———-
SELECT STATEMENT 2196 59254724 2192
AGGREGATE
SORT
FULL
TABLE ACCESS 2196 59254724 2192
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=3 Bytes=96)
1 0 TABLE ACCESS (FULL) OF ‘PLAN_TABLE’ (TABLE) (Cost=3 Card=3
Bytes=96)
Statistics
———————————————————-
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
644 bytes sent via SQL*Net to client
496 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
As per the formula which you stated,
Cost = (#SRds * SReadtim + #MRds * MReadtim + #CPUCycle / CPUSpeed ) / SReadtim
where #MRds = Num_Blocks / MBRC
I think, it won’t effect since, System Statistics, so my concern is how the IO_COST 2192 is reflected and CPU cost.
Can you check the test case and revert back on that..
– Pavan Kumar N
Comment by Pavna Kumar N — January 9, 2009 @ 7:20 am GMT Jan 9,2009 |
Pavan,
I’ve posted a comment on the OTN thread where this question came up.
Comment by Jonathan Lewis — January 12, 2009 @ 5:04 pm GMT Jan 12,2009 |
Hi Jonathan,
I attended your one day training in last HOTSOS. There you suggest not to set db_file_multiblock_read_count parameter.
In my case I am on AIX 5.3 and Oracle 10204. It is a big server and with RAID.
Default value in our case it looks small and does not take 128 in FTS test.
Here are my test cases with same DB and table.
1. Leaving db_file_multiblock_read_count to default, which Oracle somehow takes 16.
In my 10046 trace it is 16 for FTS.
2. If I set it to 32 then Oracle takes 32 for FTS in 10046 trace. This faster then db_file_multiblock_read_count 16.
3. If I set it to 128 then Oracle takes 128 for FTS in 10046 trace. This fastest then previous two tests.
Problem with third and second approach is optimizer will favor FTS which we do not want.
How can I get best of both worlds that Optimizer should not favor FTS , but when it goes for FTS then it should use MAX_IO 128?
Comment by Darshan — May 1, 2009 @ 10:31 pm BST May 1,2009 |
Darshan,
I am surprised that you get a default of 16 when you don’t set the value.
Possibilities are:
a) change in behaviour in 10.2.0.4,
b) somehow you failed to clear the db_file_multiblock_read_count from the parameter file
c) your tests that showed 16 blocks as the maximum read size were on a table that used 16 block extents.
I don’t have a 10.2.0.4 installed yet, so can’t confirm your observations. Two checks:
a) when you see the 16 block reads, can you check the values for parameters _db_file_exec_read_count and _db_file_optimizer_read_count – if they are the same then the db_file_multiblock_read_count has been set.
b) on your version of 10.2 you could try ‘alter session set db_file_multiblock_read_count = 0’; to change the behaviour. (On previous versions, this would set the parameter to 1).
Comment by Jonathan Lewis — May 2, 2009 @ 6:45 am BST May 2,2009 |
I have 10.2.0.3 production db and I have not set db_file_multiblock_read_count parameter as recomanded but by default oracle takes 16.
Comment by newbee — May 4, 2009 @ 3:58 pm BST May 4,2009 |
Newbee,
What are you doing to determine that Oracle is using 16 as the value for db_file_multiblock_read_count ?
What platform ? What mechanism are you using to start the database ? What type of parameter file are using ? Have you checked for login triggers (or application startup code) ?
Comment by Jonathan Lewis — May 5, 2009 @ 5:51 pm BST May 5,2009 |
Following values are after reset at instance level on oracle 10204 AIX 5.3. In my case it goes for 16 only.
Parameter Session Value Instance Value
------------------------------------------------------------------
_db_file_direct_io_count 1048576 1048576
_db_file_exec_read_count 16 16
_db_file_format_io_buffers 4 4
_db_file_noncontig_mblock_read_count 11 11
_db_file_optimizer_read_count 8 8
db_file_multiblock_read_count 16 16
8 rows selected.
sys@strmat> alter session set db_file_multiblock_read_count = 0;
Session altered.
Elapsed: 00:00:00.00
sys@strmat> @1
Parameter Session Value Instance Value
------------------------------------------------------------------
_db_file_direct_io_count 1048576 1048576
_db_file_exec_read_count 16 16
_db_file_format_io_buffers 4 4
_db_file_noncontig_mblock_read_count 11 11
_db_file_optimizer_read_count 16 8
db_file_multiblock_read_count 16 16
Comment by Darshan — May 7, 2009 @ 5:16 am BST May 7,2009 |
Darshan,
Thanks for the example.
I have two problems – I don’t have 10.2.0.4 installed, and I don’t have an AIX machine handy.
Here’s a couple of guesses though.
I’ve been cheerfully talking about 128 blocks at 8KB, forgetting that some operating systems may (by default) have a maximum I/O size that is set to less than 1MB – possibly your AIX system (and newbee above) have an operating system limit of 128KB. (Oracle negotiates with the O/S at startup to discover the largest legal I/O request).
There was a bug in 10.2.0.3 where setting the db_file_multiblock_read_count to zero would actually set it to 1, and then set the exec read count and optimizer read count to the same value. It’s possible that the corrected code sets it back to the default – but then calls the subroutine to set the other read counts to the same value. That’s a question you might want to raise with Oracle support because it doesn’t seem to be appropriate. (It’s also what happens on my 11.1.0.6 – although my read_count is 128, rather than 16)
Comment by Jonathan Lewis — May 11, 2009 @ 2:07 pm BST May 11,2009 |
possibly your AIX system (and newbee above) have an operating system limit of 128KB.
I just left an AIX 5.3 site and we were achieving 1MB I/Os on a DW proof-of-concept.
Comment by Doug Burns — May 11, 2009 @ 3:17 pm BST May 11,2009 |
Doug,
I’m not surprised that the limit can be 1MB – but my thought was that it might not be the default limit, and perhaps some O/S parameter (MAX_IO_SIZE or some such) has to be set to allow it to appear.
Comment by Jonathan Lewis — May 11, 2009 @ 4:00 pm BST May 11,2009 |
My misunderstanding. I still tend towards the view that it was the default but as I wasn’t deeply involved in the O/S or Storage teams, I can’t say for sure.
Then again, looking at the likes of this SAS-specific document, there would appear to be quite a few relevant parameters!
Click to access AIXTuningGuide.pdf
Comment by Doug Burns — May 11, 2009 @ 5:14 pm BST May 11,2009 |
Hi Jonathan,
New findings on AIX 10204 Env. I have a RAC setup with ASM. There if I setup db_file_multiblock_read_count to 0 in current session then it goes for 128.
So it is same OS, with same version and kernel parameter settings and oracle goes for 128 here.
WAIT #2: nam=’db file scattered read’ ela= 34409 file#=245 block#=81166 blocks=128 obj#=5621 tim=9350535038347
WAIT #2: nam=’db file scattered read’ ela= 42598 file#=245 block#=81294 blocks=128 obj#=5621 tim=9350535088866
WAIT #2: nam=’db file scattered read’ ela= 37140 file#=245 block#=81422 blocks=128 obj#=5621 tim=9350535134878
WAIT #2: nam=’db file scattered read’ ela= 25689 file#=245 block#=81550 blocks=128 obj#=5621 tim=9350535173691
Comment by Darshan — May 16, 2009 @ 1:04 am BST May 16,2009 |
AIX 5.3 Oracle 10204 with ASM RAC, test..
Parameter Session Value Instance Value
——————————————————————————– —————————— ——————————
_db_file_direct_io_count 1048576 1048576
_db_file_exec_read_count 8 8
_db_file_format_io_buffers 4 4
_db_file_noncontig_mblock_read_count 11 11
_db_file_optimizer_read_count 8 8
db_file_multiblock_read_count 8 8
8 rows selected.
SQL> alter session set db_file_multiblock_read_count=0;
Session altered.
Parameter Session Value Instance Value
——————————————————————————– —————————— ——————————
_db_file_direct_io_count 1048576 1048576
_db_file_exec_read_count 128 8
_db_file_format_io_buffers 4 4
_db_file_noncontig_mblock_read_count 11 11
_db_file_optimizer_read_count 128 8
db_file_multiblock_read_count 128 8
Comment by Darshan — May 16, 2009 @ 1:09 am BST May 16,2009 |
32. Hi Jonathan,
Windows 2003 64-bit. Oracle 10.2.0.4 64-bit.
Db_file_multiblock_read_count=16 in spfile.
block size 8192 bytes.
Max I/O =1 Mb
There is no WORKLOAD system staistics, we use NOWORKLOAD statistics.
Parameter Session Value Instance Value
_db_file_exec_read_count 16 16
_db_file_optimizer_read_count 16 16
db_file_multiblock_read_count 16 16
SQL> alter session set db_file_multiblock_read_count=0;
Session altered.
Parameter Session Value Instance Value
_db_file_exec_read_count 128 16
_db_file_optimizer_read_count 128 16
db_file_multiblock_read_count 128 16
Why _db_file_optimizer_read_count=128 ?
I expected (as of Oracle 10gR2) that Oracle automatically would set _db_file_exec_read_count to enough big value for efficient scan operation (yes, 128) and _db_file_optimizer_read_count to reasonably small value for optimizer like 8 (no, it is also 128, and 128 would be used by optimizer ). Does it mean this feature ( “autotuned MBRC”) is not working on this version Oracle?
Comment by Nadezhda — June 2, 2010 @ 8:19 am BST Jun 2,2010 |
Nadezhda,
My observation says that when you set it to 0.
It will take depending on your Max I/O size.
So in your case 128*8192 = 1M
Comment by Rameez — February 11, 2011 @ 4:37 pm GMT Feb 11,2011 |
Rameez,
Your comment alerted me to the fact that I had not responded to either of the previous two comments.
There are some inconsistencies in what happens when you set db_file_mutliblock_read_count, and the inconsistencies may be highly dependent on version.
I’ve just run up a quick test on 11.1.0.6 (as an example) on Windows 32-bit.
If I use ‘alter session’ or ‘alter system’ to set the db_file_multiblock_read_count then both the _db_file_optimizer_read_count and the _db_file_exec_read_count follow the value. In the special case where I set the parameter to zero it actually sets itself to 128 (the max_IO for the O/S setting) – and both the other parameters still follow it, rather then setting themselves back to the default.
Unfortunately “alter system reset db_file_multiblock_read_count scope = memory” is not allowed on this version of Oracle; so the only way of correcting the problem is to clear the parameter from the spfile and restarting the instance. (In this version, on my machine).
Comment by Jonathan Lewis — February 13, 2011 @ 11:26 am GMT Feb 13,2011 |
Jonathan, I have realized that there is no chance to reset dbmbrc on session level to the default. Setting db_file_multiblock_read_count to zero on session level doesn’t mean that these two hidden parameters will have default settings. Probably I was curious because of the documentation Performance Tuning Guide 10g Release 2 (10.2) B14211-03 “If this parameter is not set explicitly (or is set is 0), the optimizer will use a default value of 8 when costing full table scans” . See, “or is set is 0”, so I expected to see 8. May be my treatment of the statement was wrong. Thank you for the answer.
Comment by Nadezhda — February 14, 2011 @ 8:54 am GMT Feb 14,2011 |
I tested on 2 different database with same Oracle(10.2.0.4) and OS (OEL)
1)On first db block_size=16k after setting dbmbrc to 0 following is the result
NAME VALUE
—— —–
db_file_multiblock_read_count 64
_db_file_exec_read_count 64
_db_file_optimizer_read_count 64
2) On second db block_size=8k after setting dbmbrc to 0 following is the result
NAME VALUE
—— —–
db_file_multiblock_read_count 128
_db_file_exec_read_count 128
_db_file_optimizer_read_count 128
Then on 2nd db
SQL> alter system set db_file_multiblock_read_count=64 scope=memory;
NAME VALUE
—— —–
db_file_multiblock_read_count 64
_db_file_exec_read_count 64
_db_file_optimizer_read_count 64
So it seems it works on 10.2.0.4 without bouncing the db.
Comment by Rameez — February 14, 2011 @ 1:07 pm GMT Feb 14,2011 |
Rameez,
The important point, though, is that if you set db_file_multiblock_read_count to zero (in 10.2.0.4+) in the spfile and restart the database then the _db_file_optimizer_read_count will default to 8, and not follow. i.e. “alter system” and “bounce database” have different effects.
Comment by Jonathan Lewis — February 14, 2011 @ 1:20 pm GMT Feb 14,2011 |
Thanks for your quick reply, But after bouncing I’m receiving same value.
SQL> alter system set db_file_multiblock_read_count=0 scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1207959552 bytes
Fixed Size 2083528 bytes
Variable Size 771753272 bytes
Database Buffers 419430400 bytes
Redo Buffers 14692352 bytes
Database mounted.
Database opened.
NAME VALUE
—— —–
db_file_multiblock_read_count 128
_db_file_exec_read_count 128
_db_file_optimizer_read_count 128
Comment by Rameez — February 14, 2011 @ 3:21 pm GMT Feb 14,2011 |
Rameez,
You’re absolutely right – I should have checked it sooner.
I’ve just run up a 10.2.0.4, and if I set db_file_multiblock_read_count to zero in the pfile and restart then the optimizer read count follows the db_file_multiblock_read_count, which sets itself the the largest it can. (In fact in my case it went to 79 because of my settings for db_cache_size and sessions, rather than 128).
Comment by Jonathan Lewis — February 14, 2011 @ 9:58 pm GMT Feb 14,2011 |
Rameez, I think the only way to receive the default values the hidden parameters is to unset mbrc with the ALTER SYSTEM RESET command: alter system reset db_file_multiblock_read_count scope=spfile sid=’*’;
And restart .
Or (that’s the same)
Actually remove db_file_multiblock_read_count from the parameter file and restart using it.
It seems to me that when explicitly set db_file_multiblock_read_count to anything (including zero) this will then over-ride the default hidden parameters. In spite of the documetation (see my post above) said it may be zero or reset , but it maybe that setting zero is not the same thing as unsetting it with the ALTER SYSTEM RESET command
Comment by Nadezhda — February 15, 2011 @ 6:41 am GMT Feb 15,2011 |
Correct after reset and bounce it will set _db_file_optimizer_read_count to 8.
So when we collect NOWORKLOAD it will use _db_file_optimizer_read_count for costing and when we collect WORKLOAD stats then it will use db_file_mutliblock_read_count for costing.
So could you please throw some light as what is the difference and how it will calculate the cost in both cases?
Comment by Rameez — February 15, 2011 @ 4:32 pm GMT Feb 15,2011 |
Hi Jonathan,
Could you please explain above differences.
Also while I was finding Adjusted dbf_mbrc for different mbrc it is found that when using “alter system” for mbrc it is not effecting and have to bounce the db in order to use changed value.
Though using “alter session” works as expected.
So that means the value taken by db_file_mbrc at startup is being applied unless db is bounced after change. (10.2.0.4)
Comment by Rameez — February 16, 2011 @ 1:07 pm GMT Feb 16,2011 |
Rameez,
Sorry about the delay responding.
When you gather NOWORKLOAD statistics, I believe Oracle uses the _db_file_optimizer_read_count in place of the MBRC to derive a value for mreadtim. But I haven’t done enough experiments with using alter system and alter session to confirm all the details of how settting db_file_multiblock_read_count can affect the value of _db_file_optimizer_read_count, and whether this will then echo on to the calculation of mreadtim.
Comment by Jonathan Lewis — February 18, 2011 @ 7:09 pm GMT Feb 18,2011 |
After tracing using 10046 its been noticed that “alter system” works as expected (i.e use _db_file_exec_read_count value for execution) but the costing will be done using the value set for _db_file_optimizer_read_count at startup unless its done at “alter session”
Comment by Rameez — February 17, 2011 @ 6:20 pm GMT Feb 17,2011 |
As the costing will be done using _db_file_optimizer_read_count which is set at startup that means it is same as the costing done using system statistics (Standard cpu_costing) and there is no variation when the cost changes with the size of the multiblock read with NWLOAD(noworkload cpu_costing)
The only difference between noworkload stats and workload stats (for mbrc) is that, in noworkload “_db_file_optimizer_read_count” can be altered at session level which will be only useful for testing and won’t work at system level.
So it means the “cost is dictated by a fixed MBRC and therefore does not
change as we modify the multiblock read size” in both.
Could you please crosscheck and correct me if I’m missing something.
Comment by Rameez — February 21, 2011 @ 11:11 am GMT Feb 21,2011 |