Archive for the ‘11gR1’ Category.

« Previous Entries

Increasing Priority of lgwr Process using _high_priority_processes

March 16, 2010, 9:10 pm

At the Hotsos Symposium last week, Tanel Poder pointed out an interesting hidden parameter (_high_priority_processes). We were discussing the occasional need to increase priority on the LGWR process when dealing with “log file sync” issues. The aforementioned parameter is used to tell Oracle to automatically set background processes to a higher than normal priority when the database is started. On linux this means putting them in the RR class, in Solaris, the RT class is used. The parameter defaults to LMS* in 10.2 and to LMS*||VKTM in 11gR1. (LMS* are the Lock Manager Server processes in a RAC instance and VKTM is the new Time Keeper process in 11g). So it makes sense that these processes would run at a higher priority.

Recently I have been working on a system that was spending a significant amount of time waiting on “log file sync”. Increasing the priority of the LGWR process made a significant improvement. It’s not a silver bullet though. When the system becomes really busy, the “log file sync” times still suck, just not as much. But I digress, this post is just to point out that Oracle has a built in mechanism to increase priority on background processes, not to tell you how to reduce long “log file sync” times. There are numerous posts on that subject already. Here a couple of pretty good ones:


Manly-Men Only Use Solid State Disk for Redo Logging - Kevin Closson
Tuning Log File Sync Wait Events - Riyaj Shamsudeen
Hotsos 2010 - Day 4 - Doug Burns (see the comments section)

Back to my story. Unfortunately, every time the database is bounced, LGWR goes back to his original priority and class and we have to get the Unix guys to reset it. So I got to wondering if the _high_priority_processes parameter could be used to do this automatically. And sure enough it looks like it can. First here’s a look at the class and priority info of the standard high priority processes on a Solaris system and a Linux system.

Continue reading ‘Increasing Priority of lgwr Process using _high_priority_processes’ »

Category: 10gR2, 11gR1, 11gR2, Oracle  |  19 Comments

Temp Usage

January 20, 2010, 2:38 pm

There was a post on the oracle-l list the other day regarding disk space usage in the temp tablespace. Rich Jesse posted a neat little script for capturing large consumers of temp space along with the user and SQL statement responsible. I started playing around with the script and noticed that it wasn’t reporting the correct SQL Statement. Turns out there is a bug having to do with the definition of v$sort_usage. It’s apparently been around since 10gR1.

There is a listing on Metalink (sorry,… I mean My Oracle Support). I’ve pasted in the interesting bits here:

Bug 7210183: SQL_ID VALUE IS NOT SAME IN V$TEMPSEG_USAGE AND OTHER VIEWS.
~~~~~~~~~~~
The actual problem here looks to be in the definition of
GV$SORT_USAGE thus:

In RDBMS_MAIN_LINUX_080825:
GV$TEMPSEG_USAGE is a synonym resolving to GV_$SORT_USAGE.

GV_$SORT_USAGE is a VIEW which selects various columns from
GV$SORT_USAGE.

GV$SORT_USAGE has a definition like this:
select x$ktsso.inst_id, username, username, ktssoses, ktssosno,
prev_sql_addr, prev_hash_value, prev_sql_id, <<<<< NOTICE HERE
ktssotsn, ....
from x$ktsso, v$session
where ktssoses = v$session.saddr and ktssosno = v$session.serial#

So GV$SORT_USAGE SQL_ID etc.. are just taken from the sessions
current PREV_* columns. This is not correct. The sort usage
in a session could be tied to any of the open cursors for that
session. The above definition just looks plain wrong.
*** 09/09/08 05:45 pm ***
*** 09/10/08 12:06 am *** (DEL: Impact/Symptom->WRONG RESULTS )
*** 09/10/08 12:06 am *** (ADD: Impact/Symptom->WRONG RESULTS )
*** 09/10/08 12:06 am ***
I am not sure that in all cases using V$SESSION.SQL_ADDRESS
etc.. would be correct.
eg: Consider that the session does something like the
following:
Open cursor 1
Parse , execute and fetch one row from a SQL that needs
temp space in part of the plan
Open cursor 2
select * from dual (no sorting)
At this point V$SESSION.SQL_ID would be for the
select from dual

I do not believe that we actually have the information
available to return the correct SQL_ID etc.. this view.

eg: a. Consider this simple test:
create global temporary table foo ( a number );
insert into foo values(10);
b. In a second session do:
select sql_id from v$sort_usage;
c. Now in the first session issue any old select:
select 99 from dual;
d. And check V$SORT_USAGE:
select sql_id from v$sort_usage;
e. Repeat c and d over using a different SQL each time
in c . eg: select 77 from dual A;

In this example V$SORT_USAGE will show us which session
has the temp space (SESSION_ADDR) but the SQL which
created the temp space is not even available as an open
cursor against that session (the insert created the temp
space).

It looks like this really needs a larger change - something
like capturing the SQL_ID etc.. at the time that the temp seg
gets created and then exposing that information through some
new X$ colums in x$ktsso ?

So I modified the script to report the current sql_id instead.
Here’s a quick replay of the investigation starting with the original query:

?View Code SQL
SYS@LAB1024> -- original query
SYS@LAB1024> l      
  1  SELECT sysdate "TIME_STAMP", vsu.username, vsu.sql_id, vsu.tablespace,
  2  vsu.usage_mb, vst.sql_text, vp.spid
  3          FROM
  4          (
  5                  SELECT username, sqladdr, sqlhash, sql_id, tablespace, session_addr,
  6  sum(blocks)*8192/1024/1024 "USAGE_MB"
  7                  FROM v$sort_usage
  8                  HAVING SUM(blocks)> 10000 -- 80MB
  9                  GROUP BY username, sqladdr, sqlhash, sql_id, tablespace, session_addr
 10          ) "VSU",
 11          v$sqltext vst,
 12          v$session vs,
 13          v$process vp
 14  WHERE vsu.sql_id = vst.sql_id
 15          AND vsu.sqladdr = vst.address
 16          AND vsu.sqlhash = vst.hash_value
 17          AND vsu.session_addr = vs.saddr
 18          AND vs.paddr = vp.addr
 19          AND vst.piece = 0;
 
no rows selected
 
SYS@LAB1024> -- note the filter to only get sorts using more than 10000 blocks of temp space
SYS@LAB1024> -- (that's why no records were returned)
SYS@LAB1024> -- go to another session and start a big sort, then check again
 
SYS@LAB1024> /
 
TIME_STAM USERNAME        SQL_ID        TABLESPACE        USAGE_MB SQL_TEXT                                                         SPID
--------- --------------- ------------- --------------- ---------- ---------------------------------------------------------------- ------------
19-JAN-10 SYS             9babjv8yq8ru3 TEMP                   145 BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;             31514
 
SYS@LAB1024> -- so now we have a big one
SYS@LAB1024> -- but obviously the get_lines procedure is not causing the big sort
SYS@LAB1024> -- hmmm, what's actually active right now?
SYS@LAB1024> @as
 
no rows selected
 
SYS@LAB1024> l
  1  select sid, substr(program,1,19) prog, address, hash_value, b.sql_id, sql_child_number child, plan_hash_value, executions execs,
  2  (elapsed_time/decode(nvl(executions,0),0,1,executions))/1000000 avg_etime,
  3  sql_text
  4  from v$session a, v$sqlarea b
  5  where status = 'ACTIVE'
  6  and username is not null
  7  and a.sql_id = b.sql_id
  8* and audsid != SYS_CONTEXT('userenv','sessionid')
SYS@LAB1024> del
SYS@LAB1024> /
 
 SID PROG       ADDRESS  HASH_VALUE SQL_ID         CHILD PLAN_HASH_VALUE      EXECS   AVG_ETIME SQL_TEXT
---- ---------- -------- ---------- ------------- ------ --------------- ---------- ----------- -----------------------------------------
 410 sqlplus@ho 74AAF3D0 1572343862 1mkjj1tfvh41q      0      1304592819          5       67.42 SELECT COL1,COL2 FROM KSO.SKEW ORDER BY C
 433 sqlplus@ho 74BB6C20 1310159811 bn8b1wp71fwy3      0       187045271          4         .01 select sid, substr(program,1,19) prog, ad
 
SYS@LAB1024> -- yep, SID 410 is running the query I started in the other session
SYS@LAB1024> -- let' check the prev_sql_id as indicated by the Metalink note
SYS@LAB1024>
SYS@LAB1024> select sid, sql_id, prev_sql_id from v$session where sid=410;
 
 SID SQL_ID        PREV_SQL_ID
---- ------------- -------------
 410 1mkjj1tfvh41q 9babjv8yq8ru3
 
SYS@LAB1024> -- aha, it is prev_sql_id that is exposed in v$sort_usage
SYS@LAB1024> -- just to prove it let's look at the fixed view definition
SYS@LAB1024> get fixed_view_def
  1  select * from v$fixed_view_definition
  2* where view_name like upper('&view_name')
SYS@LAB1024> /
Enter value for view_name: GV$SORT_USAGE
 
VIEW_NAME
------------------------------
VIEW_DEFINITION
-----------------------------------------------------------------------------------------------------------------------------------------------------------
GV$SORT_USAGE
select x$ktsso.inst_id, username, username, ktssoses, ktssosno, prev_sql_addr, prev_hash_value, prev_sql_id, ktssotsn, decode(ktssocnt, 0, 'PERMANENT', 1,
'TEMPORARY'), decode(ktssosegt, 1, 'SORT', 2, 'HASH', 3, 'DATA', 4, 'INDEX', 5, 'LOB_DATA', 6, 'LOB_INDEX' , 'UNDEFINED'), ktssofno, ktssobno, ktssoexts, k
tssoblks, ktssorfno from x$ktsso, v$session where ktssoses = v$session.saddr and ktssosno = v$session.serial#
 
SYS@LAB1024> -- notice the prev_sql_id ...
SYS@LAB1024>
SYS@LAB1024> -- so now let's look at a fixed version (only modified slightly)
SYS@LAB1024> -- since we're already joining to v$session, we can just get the sql_id from there
SYS@LAB1024> -- I rearranged the columns a bit as well 
SYS@LAB1024> -- and joined to dba_tablespaces to allow for various block sizes
SYS@LAB1024> 
SYS@LAB1024> @temp_usage
 
no rows selected
 
SYS@LAB1024> l
  1  SELECT sysdate "TIME_STAMP", vsu.username, vs.sid, vp.spid, vs.sql_id, vst.sql_text, vsu.tablespace,
  2         sum_blocks*dt.block_size/1024/1024 usage_mb
  3     FROM
  4     (
  5             SELECT username, sqladdr, sqlhash, sql_id, tablespace, session_addr,
  6  -- sum(blocks)*8192/1024/1024 "USAGE_MB",
  7                  sum(blocks) sum_blocks
  8             FROM v$sort_usage
  9             HAVING SUM(blocks)> 1000
 10             GROUP BY username, sqladdr, sqlhash, sql_id, tablespace, session_addr
 11     ) "VSU",
 12     v$sqltext vst,
 13     v$session vs,
 14     v$process vp,
 15     dba_tablespaces dt
 16  WHERE vs.sql_id = vst.sql_id
 17  -- AND vsu.sqladdr = vst.address
 18  -- AND vsu.sqlhash = vst.hash_value
 19     AND vsu.session_addr = vs.saddr
 20     AND vs.paddr = vp.addr
 21     AND vst.piece = 0
 22     AND dt.tablespace_name = vsu.tablespace
 23* order by usage_mb
 
SYS@LAB1024> -- start the sort again, and recheck
SYS@LAB1024> /
 
TIME_STAM USERNAME         SID SPID         SQL_ID        SQL_TEXT                                           TABLESPACE        USAGE_MB
--------- --------------- ---- ------------ ------------- -------------------------------------------------- --------------- ----------
19-JAN-10 SYS              410 31514        1mkjj1tfvh41q SELECT COL1,COL2 FROM KSO.SKEW ORDER BY COL3, COL2 TEMP                   191
 
SYS@LAB1024> -- that looks better, that's the statement that is causing the big sort and temp usage

As pointed out in the Metalink note, it’s possible that the current sql_id may not have the correct value in some situations. One specific case that was mentioned was when a statement has completed and the session has issued another statement, but the temp space has not yet been cleaned up. It seems to work pretty well in most situations though.

One other note, it appears that this issue (bug) has not been addressed as of 11.2.0.1.

Category: 10gR2, 11gR1, 11gR2, Oracle  |  1 Comment

Autotuned DB_FILE_MULTIBLOCK_READ_COUNT

January 12, 2010, 9:03 pm

This topic keeps coming up so I decided to write a little blurb on it. Mainly so I could stop repeating myself (I think I’ve written the same email about 10 times now). So here goes:

First the short version:

  • If you are on 10gR2 or later you should probably not set DB_FILE_MULTIBLOCK_READ_COUNT.

Now the long winded discussion:

A fairly significant change to the Oracle optimizer was rolled out in 10gR2 with regards to the parameter DB_FILE_MULTIBLOCK_READ_COUNT (from here on out I’ll just refer to as DBFMBRC as it’s too long to type). Prior to 10gR2, this parameter was used for multiple purposes - namely:

  • Representing the avg number of blocks that a multi-block read would return (used in the costing estimate when parsing)
  • The number of blocks to attempt to read when a multi-block read is executed

Unfortunately, these two uses often worked against each other. Increasing the DBFMBRC in order to improve the throughput of multi-block reads generated by operations such as full table scans, would have the side effect of causing the optimizer to favor access paths that resulted in multi-block reads. I think that was probably the main driver for the introduction of the OPTIMIZER_INDEX_COST_ADJ and OPTIMIZER_INDEX_CACHING parameters - which allowed the optimizer’s preference for full table scans to be dialed down, but that’s a topic for another post.

The costing value and the execution value need not necessarily be related (and in 10gR2 they aren’t unless you make it so). For example, the estimate for costing purposes could be set to a relatively low value (so that the optimizer would not unduly favor multi-block read type operations) while the value used when actually requesting a multi-block i/o could be set relatively high (to maximize throughput in cases where a multi-block read was actually performed). 10gR2 accomplishes this by adding a couple of so called hidden parameters (you know, the one’s that start with an underscore). The parameters are:

  • _db_file_optimizer_read_count - this is the one that’s used for costing
  • _db_file_exec_read_count - this is the one that’s used when an i/o request is issued

If you leave DB_FILE_MULTIBLOCK_READ_COUNT set to its default value, these two parameters will be set to two different values. For example, on my 10.2.0.4 database using 8K blocks, the values are 8 and 128 respectively. So the optimizer should do it’s costing with a value of 8 but attempt to read 128 blocks, if it does indeed choose a full table scan. Let’s take a look:

?View Code NONE
 
> !sql
sqlplus "/ as sysdba"
 
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jan 13 17:21:55 2010
 
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
 
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SYS@LAB1024> select     pname, pval1
  2  from       sys.aux_stats$
  3  /
 
PNAME                               PVAL1
------------------------------ ----------
STATUS
DSTART
DSTOP
FLAGS                                   1
CPUSPEEDNW                     526.069569
IOSEEKTIM                              10
IOTFRSPEED                           4096
SREADTIM
MREADTIM
CPUSPEED
MBRC
MAXTHR
SLAVETHR
 
13 rows selected.
 
SYS@LAB1024> -- default values
SYS@LAB1024> 
SYS@LAB1024> select name, value, isdefault, ismodified, isset
  2  from
  3  (
  4  select flag,name,value,isdefault,ismodified,
  5  case when isdefault||ismodified = 'TRUEFALSE' then 'FALSE' else 'TRUE' end isset
  6  from
  7     (
  8         select
  9              decode(substr(i.ksppinm,1,1),'_',2,1) flag
 10              , i.ksppinm name
 11              , sv.ksppstvl value
 12              , sv.ksppstdf  isdefault
 13  --            , decode(bitand(sv.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismodified
 14              , decode(bitand(sv.ksppstvf,7),1,'TRUE',4,'TRUE','FALSE') ismodified
 15           from x$ksppi  i
 16              , x$ksppsv sv
 17          where i.indx = sv.indx
 18     )
 19  )
 20  where name like nvl('%&parameter%',name)
 21  and upper(isset) like upper(nvl('%&isset%',isset))
 22  and flag not in (decode('&show_hidden','Y',3,2))
 23  order by flag,replace(name,'_','')
 24  /
Enter value for parameter: read_count
Enter value for isset: 
Enter value for show_hidden: Y
 
NAME                                               VALUE                                                                  ISDEFAUL ISMODIFIED ISSET
-------------------------------------------------- ---------------------------------------------------------------------- -------- ---------- ----------
db_file_multiblock_read_count                      128                                                                    TRUE     FALSE      FALSE
_db_file_exec_read_count                           128                                                                    TRUE     FALSE      FALSE
_db_file_noncontig_mblock_read_count               11                                                                     TRUE     FALSE      FALSE
_db_file_optimizer_read_count                      8                                                                      TRUE     FALSE      FALSE
_sort_multiblock_read_count                        2                                                                      TRUE     FALSE      FALSE
 
SYS@LAB1024> -- default values again
SYS@LAB1024> 
SYS@LAB1024> -- let's turn on 10046 trace
SYS@LAB1024> 
SYS@LAB1024> set echo on 
SYS@LAB1024> @find_trace
SYS@LAB1024> col tracefile_name for a120
SYS@LAB1024> SELECT rtrim(k.value,'/')||'/'||LOWER(d.instance_name)||'_ora_'||p.spid
  2  ||DECODE(p.value,'','','_'||p.value)||'.trc' tracefile_name
  3  FROM v$parameter k, v$parameter p, v$instance d,
  4       sys.v_$session s, sys.v_$process p,
  5       (SELECT sid FROM v$mystat WHERE rownum=1) m
  6  WHERE p.name = 'tracefile_identifier'
  7    AND k.name = 'user_dump_dest'
  8    AND s.paddr = p.addr
  9    AND s.sid = m.sid
 10  /
 
TRACEFILE_NAME
------------------------------------------------------------------------------------------------------------------------
/u01/app/admin/LAB1024/udump/lab1024_ora_24004.trc
 
 
SYS@LAB1024> alter session set events '10046 trace name context forever, level 8';
 
Session altered.
 
SYS@LAB1024> set echo off
SYS@LAB1024> select avg(pk_col) from kso.skew a where col1 > 0;
select avg(pk_col) from kso.skew a where col1 > 0
                            *
ERROR at line 1:
ORA-01013: user requested cancel of current operation
 
 
SYS@LAB1024> -- bombed out with ctl-C 
SYS@LAB1024> -- now find the sql_id and verify the plan
SYS@LAB1024>
SYS@LAB1024> @find_sql
Enter value for sql_text: select avg(pk_col) from kso.skew a where col1 > 0
Enter value for sql_id: 
 
SQL_ID         CHILD  PLAN_HASH      EXECS     AVG_ETIME      AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
05cq2hb1r37tr      0  568322376          1        183.30       35,082 select avg(pk_col) from kso.skew a where col1 > 0
 
 
SYS@LAB1024> @dplan
Enter value for sql_id: 05cq2hb1r37tr
Enter value for child_no: 0
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  05cq2hb1r37tr, child number 1
-------------------------------------
select avg(pk_col) from kso.skew a where col1 > 0
 
Plan hash value: 568322376
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       | 45254 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    11 |            |          |
|*  2 |   TABLE ACCESS FULL| SKEW |    32M|   335M| 45254   (3)| 00:09:04 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("COL1">0)
 
SYS@LAB1024> -- we can verify that it read 128 blocks at a time by looking at the trace file
SYS@LAB1024>
SYS@LAB1024> !cat /u01/app/admin/LAB1024/udump/lab1024_ora_24004.trc
 
...
 
PARSING IN CURSOR #6 len=49 dep=0 uid=0 oct=3 lid=0 tim=1233806813773567 hv=3279003447 ad='7e81b9b4'
select avg(pk_col) from kso.skew a where col1 > 0
END OF STMT
PARSE #6:c=213967,e=209976,p=27,cr=287,cu=0,mis=1,r=0,dep=0,og=2,tim=1233806813773550
EXEC #6:c=0,e=77,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=2,tim=1233806813773842
WAIT #6: nam='SQL*Net message to client' ela= 10 driver id=1650815232 #bytes=1 p3=0 obj#=472 tim=1233806813773941
WAIT #6: nam='db file sequential read' ela= 81 file#=4 block#=181290 blocks=1 obj#=54141 tim=1233806813791873
WAIT #6: nam='db file scattered read' ela= 5218 file#=4 block#=181291 blocks=128 obj#=54141 tim=1233806813800264
WAIT #6: nam='db file scattered read' ela= 3239 file#=4 block#=181419 blocks=128 obj#=54141 tim=1233806814404594

Note 1: Very nice! Maximum i/o without making the optimizer think that full table scans are better than index access paths.

Note 2: Setting the parameter to the default value is not the same as resetting it. If you were to change the value to 16 for example and then set it back to 8 (which was the value it defaulted to) you would see the costing and the execution done with 8. That is, the _db_file_optimizer_read_count and the _db_file_exec_read_count would both be set to 8. You have to unset the DBFMBRC parameter to get it to go back to automatic split of 8 and 128. See my previous post on that here: Resetting Oracle Parameters Here’s another quick demonstration:

?View Code NONE
SYS@LAB1024> @parms
Enter value for parameter: read_count
Enter value for isset: 
Enter value for show_hidden: Y
 
NAME                                               VALUE                                                                  ISDEFAUL ISMODIFIED ISSET
-------------------------------------------------- ---------------------------------------------------------------------- -------- ---------- ----------
db_file_multiblock_read_count                      128                                                                    TRUE     FALSE      FALSE
_db_file_exec_read_count                           128                                                                    TRUE     FALSE      FALSE
_db_file_noncontig_mblock_read_count               11                                                                     TRUE     FALSE      FALSE
_db_file_optimizer_read_count                      8                                                                      TRUE     FALSE      FALSE
_sort_multiblock_read_count                        2                                                                      TRUE     FALSE      FALSE
 
SYS@LAB1024> alter system set db_file_multiblock_read_count=16;
 
System altered.
 
SYS@LAB1024> @parms
Enter value for parameter: read_count
Enter value for isset: 
Enter value for show_hidden: Y
 
NAME                                               VALUE                                                                  ISDEFAUL ISMODIFIED ISSET
-------------------------------------------------- ---------------------------------------------------------------------- -------- ---------- ----------
db_file_multiblock_read_count                      16                                                                     TRUE     TRUE       TRUE
_db_file_exec_read_count                           16                                                                     TRUE     FALSE      FALSE
_db_file_noncontig_mblock_read_count               11                                                                     TRUE     FALSE      FALSE
_db_file_optimizer_read_count                      16                                                                     TRUE     FALSE      FALSE
_sort_multiblock_read_count                        2                                                                      TRUE     FALSE      FALSE
 
SYS@LAB1024> alter system set db_file_multiblock_read_count=128;
 
System altered.
 
SYS@LAB1024> @parms
Enter value for parameter: read_count
Enter value for isset: 
Enter value for show_hidden: Y
 
NAME                                               VALUE                                                                  ISDEFAUL ISMODIFIED ISSET
-------------------------------------------------- ---------------------------------------------------------------------- -------- ---------- ----------
db_file_multiblock_read_count                      128                                                                    TRUE     TRUE       TRUE
_db_file_exec_read_count                           128                                                                    TRUE     FALSE      FALSE
_db_file_noncontig_mblock_read_count               11                                                                     TRUE     FALSE      FALSE
_db_file_optimizer_read_count                      128                                                                    TRUE     FALSE      FALSE
_sort_multiblock_read_count                        2                                                                      TRUE     FALSE      FALSE
 
SYS@LAB1024> alter system set db_file_multiblock_read_count=0;
 
System altered.
 
SYS@LAB1024> @parms
Enter value for parameter: read_count
Enter value for isset: 
Enter value for show_hidden: Y
 
NAME                                               VALUE                                                                  ISDEFAUL ISMODIFIED ISSET
-------------------------------------------------- ---------------------------------------------------------------------- -------- ---------- ----------
db_file_multiblock_read_count                      128                                                                    TRUE     FALSE      FALSE
_db_file_exec_read_count                           128                                                                    TRUE     FALSE      FALSE
_db_file_noncontig_mblock_read_count               11                                                                     TRUE     FALSE      FALSE
_db_file_optimizer_read_count                      128                                                                    TRUE     FALSE      FALSE
_sort_multiblock_read_count                        2                                                                      TRUE     FALSE      FALSE

So once you set the value of DBFMBRC, the exec and read parms will be the same.

Note 3: The _db_file_noncontig_mblock_read_count has to do with index block pre-fetching. There is an excellent description on this oracle-l post by Tanel Poder

Note 4: Everything I’ve said so far assumes that System Statistics have not been set or gathered. If System Stats are set, then a completely different set of rules kick in - basically DBFMBRC is ignored.

And speaking of System Stats, I must say that I think the default no-workload System Stats work pretty well. In most cases, I prefer not to gather or set System Statistics, for a couple of reasons:

In the first place, I generally don’t believe in fixing things that aren’t broken. You could argue that System Statistics are not meant to “fix” anything, but are merely designed to give the optimizer more specific information about the particular system. And this is true, but the standard method of gathering Workload System Statistics often results in less than accurate results and at best they only represent an average over some period of time which may or may not be representative of what happens when the system is really under stress. And I guess we could have a few sets of values and change them regularly (like one set for batch processing and one for day time on-line processing), but that seems like a lot of complexity for little potential benefit. So unless there really is a specific problem that needs to be solved, I probably wouldn’t do that (in fact I have not ever done that).

Second, I would venture to guess that the majority of databases out there have not gathered or set System Statistics. I could be way off on that assumption, but most of the systems that I’ve looked at over the last couple of years have not had anything other than the default NOWORKLOAD values. Staying in the middle of the pack means we’re less likely to run into weird behavior that someone else hasn’t already run across.

Third, the interaction between the various flavors of System Statistics and the DBFMBRC are complicated. Randolf Geist has an excellent series of posts which discusses the interaction between System Stats and DFMBRC here: RG on System Stats
But it takes 3 fairly long and detailed posts to cover the topic. I am not going to discuss it here since he has already done a very thorough job.

Fourth, System Statistics is (are) a really big knob. What I mean by that is that a small change can have a profound effect. Since they control the costing of every single statement that runs through your system, changes to them could potentially affect every statement. So from a stability stand point, this is not something that you would want to change without careful thought and certainly not something that you would want to change on a regular basis.

Now for a couple of references:

Jonathan Lewis has several posts on his blog about System Stats. Here are links to a couple:

JL on System Stats
JL on System Stats 2

Be sure and read through the comments as there is some excellent information there including how the default values for the read and exec parameters are arrived at.

Finally, Tom Kyte has a good discussion on the subject here: TK on DBFMBRC

Category: 10gR2, 11gR1, 11gR2, Oracle  |  2 Comments

Fixing Bad Index Hints in SQL Profiles (automatically)

November 18, 2009, 10:30 am

I’ve written before on the change Oracle made to their Hint based mechanisms (Outlines/Profiles/Baselines) in 10g here: Why Isn’t Oracle Using My Outline / Profile / Baseline. To quickly recap, prior to 10g, the design goal for Outlines appears to have been to “lock” execution plans. That is to allow the optimizer as little flexibility as possible. With 10g and 11g, it appears the goal has swung away from the “locking” concept and towards allowing the optimizer more flexibility. I’ll show you an example of what I mean in a minute, but I must say that I find this decision to be irritating at best. It seems to me that the whole reason for implementing one of these objects is to try to keep the optimizer from changing its mind. After all, it was originally called “Plan Stability” by the Oracle marketing guys.

One of the main offenders in this regard is the use of a new format available for index hints as of 10g. Prior to 10g, the index looked basically like this:

INDEX(TABLE_NAME INDEX_NAME)

Translation: If possible, use this index on this object.

As of 10gR2, there is a new possible format which appears to be used (at least most of the time) when Outlines/Profiles/Baselines are created. The new format looks like this:

INDEX(TABLE_NAME.COLUMN_NAME TABLE.NAME.COLUMN_NAME …)

Translation: If possible, use any available index on any of these columns.

As you can see, this format leaves a lot more to the optimizer’s discretion than the older format. (Note: the older format is still valid)

In my previous post I published a script for changing an individual SQL Profile hint, but recently I had a situation where there was a SQL Profile that had 20+ index hints where the statement was suffering from plan instability, despite the fact that it was using a SQL Profile. Rather than manually look up the correct index names and change the hints one by one (a very error prone proposition), I decided to write a script that would automatically change all the INDEX hints from the “non-specific column oriented” format to the “specific index name” format.

But before I give you the script - a little history. Here are Outline hints for the same statement from 9.2.0.8, 10.1.0.?, 10.2.0.4, and 11.2.0.1:

Continue reading ‘Fixing Bad Index Hints in SQL Profiles (automatically)’ »

Category: 10gR2, 11gR1, 11gR2, Oracle, Plan Stability, Tuning  |  6 Comments

How to Attach a SQL Profile to a Different Statement - Take 2

October 13, 2009, 10:00 am

I posted on this topic a while back (How to Attach a SQL Profile to a Different Statement - Take 1), but wasn’t really happy with my solution. So here’s another shot at it. The basic idea is to be able to create a profile on one statement using hints or whatever other tricks you can come up with, and then attach the profile to a production statement on which you cannot modify the code. The basic steps of this technique are as follows:

The main reason I became dissatisfied with my previous approach was that it’s often necessary to fix one or more of the hints (see this post for the most common reason: Why Isn’t Oracle Using My Outline / Profile / Baseline?). The preceding steps allow the Profile to be tweaked before attaching it to the target statement. My previous approach created the profile and moved it all in one step. So there was no chance to modify the Profile before it was attached to the production statement. Anyways, here’s an example (note the examples are on a 10.2.0.4 database, but all the SQL should work on 11g as well):
Continue reading ‘How to Attach a SQL Profile to a Different Statement - Take 2’ »

Category: 10gR2, 11gR1, 11gR2, Oracle, Plan Stability  |  14 Comments
« Previous Entries