With North India Oracle User Group (NIOUG) coming into existence we have moved this blog to NIOUG website. The new URL for this blog is http://orablog.nioug.org.
Please point your feeds to the new URL !
Change is always welcome. Its the sign that some thing is moving, some thing is getting improved. With change, there are always chances for new ideas to get born and groom. By constantly changing the way things are working, we always can make our work, life more better. To sum up, to grow, become more error-free, a change is required.
But as it is said, there is nothing always black or white. There are always exceptions to every rule. I mentioned above change as its need in our lives. Does it mean we should get married to a new person every other year to make our lives more better ? Heck no! And there are some rules which work forever. One out of those rules is, if some thing is not broken , don’t try to fix it. We need change but it should be done if there is a need to bring it. It should not be done just to make sure that no one sits idle and there should be some work for people to do, doesn’t matter its useless work too.
When OTN Forums( http://forums.oracle.com) got upgraded( or degraded, whatever you may want to call it, suit yourself) and the new point system got introduced, there was a huge hue and cry among many people. Some liked it , some hated it, some didn’t bother. Many blog posts were written talking about its pros/cons, many threads were created over Forums to understand/protest/suggest about it. I am not going to write another post talking about the same thing. I chose to be neutral about it. There is no need to shout over something which we can’t change. I like/dislike point system, it won’t make any difference to OTN team and in their decision to bring it or discard it. Infact, I guess with some exceptions like repeated posts to get points, people not bothering about giving points to right answers, this point system have proved to be good. I know couple of posters whose question count is in thousands. With the previous method of making someone a top user with only number of posts, they could had become top user as well with no benefit to others. They just ask questions, don’t contribute by helping others and that’s what a top user(s) should do. So with point system, only “noisy” people got sacked from the top user listing. Though its not the NYSE listing ,dropping from which can give some one a heart attack but still, now the list is having those who really spend time to give answers. Okay, there are still debates, arguements about some posters that they don’t do the right thing and have attained points by repeated posts. But I guess, there is no system which is totally error-free. So to sum up , this change over the OTN Forums was a welcome change, atleast for me( and if I don’t mention the sluggish performance of forums afterwards, another topic and may be another day).
But one ketchup doesn’t suit all the dishes. I was browsing over Twitter and I read a link which Eddie posted,
http://www.dba-oracle.com/oracle_news/news_oracle_support_community.htm
WTH! So it means that now over Metalink too, there will be point system? So what would be the metalink support analysts will do? What if some one who is not from Oracle support attains more points than anyone ?Should we start listening to him/her and leave what Support people say as they don’t have “enough points” to prove that they are right? There is so much debate that people are striving to become top users over OTN forums. Now what would happen, if some one from Support or not from Support would come and try the same?I have not seen the interface of this MOSC yet but if its the same like OTN than what if someone finds an answer from someone rude and reports it as abuse? Would that person be banned from Metalink as one can be from OTN? Not to forget about the requirement to use Flash to use the new interface! Some thing which didn’t require any fix got “fixed” and now everyone is in a “fix” what would happen next?
I don’t think that this was needed at all. There may be something better in someone’s mind who suggested , implemented all this. But I believe, some times , simple is better. I still like my normal Nokia8210 the most, doesn’t matter what Iphone has brought up? Not so modern may be I am!
Cheers
Aman….
Posted in General | Tagged forums, upgrade | 3 Comments »
Securefiles in Oracle 11gR1 is a complete reengineered thing. Traditional lobs didn’t present so well themselves in the long run of business due to the limiation of their sizes and other factors which are pretty well documented in Oracle docs. So Oracle has given us in 11g Securefiles, LOBs on steroids if I can say so.
Well this blog post is not about Securefiles. Official docs do a pretty good job in explaining their working and other stuff and there are tons of other web sites which have done that already a good job explaining them eg you can read Tim Hall’s excellent article about the same here . This blog post is about a parameter that Oracle has given for the better working of Securefiles, yup correct, Shared_io_pool.
Shared_io_pool is added in the Oracle architecture to support large IOs. Normally large IO’s and if I can say, sort of like private IOs are best done using PGA with direct path access. When Securefiles are created with the options Cache which is actually borrowed from older cousin Basicfile aka LOBs only , they are read into the Buffer Cache which makes the access of these lobs more faster. This option is not there in Securefiles actually and hence is the reason for the 10g doc link. Using Buffer Cache is a good option but it has couple of issues as well. First and foremost is that due to the large size of lobs, they may go and kick the heck out all other tiny miny buffers from the buffer cache. Now if we are really using Lobs so we won’t mind doing this too I guess. But still, this can be not-so-good issue for the other small lookup table’s buffers which are thrown out. Another issue can be when we actually don’t use the Cache at all and go with plain, Nocache option which means , no buffer cache access. So now zero chance of any memory based access for lobs.
In 11g, for Securefiles, Oracle has tried to remove this issue with the introduction of Shared_IO_Pool parameter. This can be used as a shared region to support cached IOs for Securefiles. This parameter’s default size is mentioned as zero but I couldn’t find it as zero. I found it of actually some size. I guess the benefit is clear cut. As opposed to private memory given for PGA and to be allocated , this is a system level stuff so its more easy to give to everyone.
Lets see this parameter, starting from “normal” views
SQL> select * from V$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
SQL> select pool,name,bytes from V$sgastat where lower(name) like '%shared%';
POOL NAME BYTES
------------ -------------------------- ----------
shared_io_pool 4194304
shared pool SHARED SERVERS INFO 3108
generic process shared st 12
array 2 for shared redo b 96
array 1 for shared redo b 96
ksfd shared pool recovery 24
6 rows selected.
SQL> startup force
ORACLE instance started.
Total System Global Area 188313600 bytes
Fixed Size 1332048 bytes
Variable Size 134220976 bytes
Database Buffers 46137344 bytes
Redo Buffers 6623232 bytes
Database mounted.
Database opened.
SQL> select pool,name,bytes from V$sgastat where lower(name) like '%shared%';
POOL NAME BYTES
------------ -------------------------- ----------
shared_io_pool 4194304
shared pool SHARED SERVERS INFO 3108
generic process shared st 12
array 2 for shared redo b 96
array 1 for shared redo b 96
ksfd shared pool recovery 24
6 rows selected.
SQL>
The default of this parameter’s value in my system is coming out to be 4m. I am not sure that its actually correct or not. I shall install a vanila install of Oracle db and will recheck it. Let’s try to see a little more deeper about this parameter.
I blogged about learning a new trick to find the info about the fixed table structures some time ago. Using the same, let’s see what comes out for this parameter,
SQL> desc v$fixed_view_definition
Name Null? Type
----------------------------------------- -------- ----------------------------
VIEW_NAME VARCHAR2(30)
VIEW_DEFINITION VARCHAR2(4000)
SQL> set long 500000
SQL> select view_definition from V$fixed_view_definition where view_name like 'V$SGASTAT';
VIEW_DEFINITION
--------------------------------------------------------------------------------
select POOL, NAME , BYTES from GV$SGASTAT where inst_id = USERENV('Instance')
SQL> set long 500000000
SQL> select view_definition from V$fixed_view_definition where view_name like 'GV$SGASTAT';
VIEW_DEFINITION
--------------------------------------------------------------------------------
select inst_id,'',ksmssnam,ksmsslen from x$ksmfs where ksmsslen>1 union all se
lect inst_id,'shared pool',ksmssnam, sum(ksmsslen) from x$ksmss where ksmssle
n>1 group by inst_id, 'shared pool', ksmssnam union all select inst_id,'large
pool',ksmssnam, sum(ksmsslen) from x$ksmls where ksmsslen>1 group by inst_id,
'large pool', ksmssnam union all select inst_id,'java pool',ksmssnam, sum(ksm
sslen) from x$ksmjs where ksmsslen>1 group by inst_id, 'java pool', ksmssnam
union all select inst_id,'streams pool',ksmssnam, sum(ksmsslen) from x$ksmstrs
where ksmsslen>1 group by inst_id, 'streams pool', ksmssnam
So there are couple of fixed table structures which are involved here. The structure, X$KSMFS(Kernel Services , Memory Fixed SGA) shows the info of fixed value area in the sga which includes shared_io_pool also,
SQL> select ksmssnam,ksmsslen from x$ksmfs 2 / KSMSSNAM KSMSSLEN -------------------------- ---------- fixed_sga 1332048 buffer_cache 46137344 log_buffer 6623232 shared_io_pool 4194304
So the size shown here is also 4m only.
Let’s see how many other parameters are there related to this?
SQL> select ksppinm,ksppstvl,ksppstdvl 2 from x$ksppcv a,x$ksppi b 3 where a.indx=b.indx and b.ksppinm like '%shared_io%'; KSPPINM KSPPSTVL KSPPSTDVL ------------------------------ ------------------------------ ---------------- __shared_io_pool_size 4194304 4M _shared_io_pool_size 4194304 4M _shared_iop_max_size 536870912 512M _shared_io_pool_buf_size 1048576 1M _shared_io_pool_debug_trc 0 0 _shared_io_set_value FALSE FALSE 6 rows selected. SQL>
So I guess the default size is indeed 4m only. I may be wrong so if you know some thing correct about this, do let me know and I shall correct it. It seems that this parameter can go maximumly to 512m. I am still searching for the other parameter’s description. Though look straight forward, I shall still wait to get the exact details about them before speaking about them.
Reading this article from Arup Nanda, it seems oracle has introduced some thing called Lob Cache specifically. Though I am not actually sure that there is some thing like this but it seems that this shared io pool is going to be lined with that in some way. It may be correct or may be not but this is my best guess about it so far.
It seems that Oracle has done some real thought-process before coming out with Securefiles. Let’s see what else we would see about them in future.
Aman….
Posted in Uncategorized | 2 Comments »
Whenever I used to find out the info of any fixed table(x$) I used to do this by a workaround. I used to set the trace, run the query and see the table’s name. For example, if we are looking for the V$log’s fixed table name so I would do something like that ,
<code>
SQL> select * from V$log;
Execution Plan
----------------------------------------------------------
Plan hash value: 2536105608
--------------------------------------------------------------------------------
------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
| Time |
--------------------------------------------------------------------------------
------------
| 0 | SELECT STATEMENT | | 1 | 185 | 0 (0)
| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 185 | 0 (0)
| 00:00:01 |
|* 2 | FIXED TABLE FULL | X$KCCLE | 1 | 136 | 0 (0)
| 00:00:01 |
|* 3 | FIXED TABLE FIXED INDEX| X$KCCRT (ind:1) | 1 | 49 | 0 (0)
| 00:00:01 |
--------------------------------------------------------------------------------
------------
(output trimmed)
</code>
So I got that x$kccle and x$kccrt are driving it. Well not a bad way IMO.
Till today….
When I found this note,
So there is actually a view which tells us the description of the fixed tables, cool! So doing the same from here,
SQL> select view_definition
2 from v$fixed_view_definition where view_name='V$LOG';
VIEW_DEFINITION
--------------------------------------------------------------------------------
select GROUP# , THREAD# , SEQUENCE# , BYTES , MEMBERS , ARCHIVED , STATUS , FI
RST_CHANGE# , FIRST_TIME from GV$LOG where inst_id = USERENV('Instance')
SQL> select view_definition
2 from v$fixed_view_definition where view_name='GV$LOG';
VIEW_DEFINITION
--------------------------------------------------------------------------------
select le.inst_id, le.lenum, le.lethr, le.leseq, le.lesiz*le.lebsz, ledup, decod
e(bitand(le.leflg,1),0,'NO','YES'), decode(bitand(le.leflg,24), 8, 'CURRENT',
16,'CLEARING', 24,'CLEARING_
CURRENT', decode(sign(leseq),0,'UNUSED', decode(sign((to_number(rt
.rtckp_scn)-to_number(le.lenxs))* bitand(rt.rtsta,2)),-1,'ACTIVE','INACTI
VE'))), to_number(le.lelos), to_date(le.lelot,'MM/DD/RR HH24:MI:SS','NLS_CALENDA
R=Gregorian') from x$kccle le, x$kccrt rt where le.ledup!=0 and le.lethr=rt.rtnu
m and le.inst_id = rt.inst_id
SQL>
Voila! We got the fixed table’s names and much more info about how they are being used. Cool! Its always better to take a straight way rather than a workaround. Learned some thing new today 
.
.Aman….
Posted in Uncategorized | 1 Comment »
Errors are not really a welcome stuff 
. Yesterday, I was importing a dump file (from 8.1.7.4) into 10.2.0.3 and hit
IMP-00020: long column too large for column buffer size (number).
After doing some googling and searching on metalink came to know (not sure though) that it was some bug in 8.1.7.4. And the solution was to try with different values of buffer parameter in import (which actually didn’t help) . Thats what oerr also has to say:
- *Cause: The column buffer is too small. This usually occurs when importing LONG data.
*Action: Increase the insert buffer size 10,000 bytes at a time (for example). Use this step-by-step approach because a buffer size that is too large may cause a similar problem.
And also probably the issue was that the export was run with compress=N. So the guys again ran the export of the table with compress=Y (which is the default). Hopes got some oxygen and we again ran the import. That IMP-00020 was gone and a new baby struck:
IMP-00058:Oracle Error 1438 encountered.
ORA-01438:Value Larger Than Specified precision allow for this Column
Again started the googling and metalink’ing session and found that it was some bug 
. Import itself creating the table and then uttering ORA-01438. Complete non-sense. Isn’t it ? Just hitting and trying what we did was pre-created the table with all the NUMBER columns having their data types defined simply as NUMBER without any precision and wow it completed without any errors. Now its stupid seriously.
So then we did some research on the data in the table and found that there was one row which was causing the whole shit. There were two columns defined as NUMBER(12) but the values in them were of length 30 and 60. So what it was ? Probably a data corruption or what ? Otherwise how the table definition would have allowed such crap to enter the table. Couldn’t ascertain the exact reason but happy ending, it was
Posted in Troubleshooting | Tagged imp-00020, imp-00058, ora-01438, oracle 8.1.7.4 | Leave a Comment »
Well this was not supposed to be a post but it was asked over Forums.oracle.com that can we do the transport of the tablespace in the same database after some testing?So the answer is yes. Now I was originally going to post it over there only but thanks to new Jive software, I couldn’t so I had to post it here. Have a read,
C:\Documents and Settings\Administrator>sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.1.0 - Production on Wed Sep 17 09:31:00
2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights
reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> select name from V$database;
NAME
---------
MUMMYORA
SQL> create tablespace test_tt datafile 'd:\test.dbf' size 2m;
Tablespace created.
SQL> create user test identified by test default tablespace
test_tt
quota un
ted on test_tt;
User created.
SQL> grant create table ,create session to test;
Grant succeeded.
SQL> conn test/test
Connected.
SQL> create table tt_tab(a number);
Table created.
SQL> insert into tt_tab values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> conn / as sysdba
Connected.
SQL> alter tablespace test_tt read only;
Tablespace altered.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0
-
Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
C:\Documents and Settings\Administrator>sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.1.0 - Production on Wed Sep 17 09:33:44
2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights
reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> alter user sys identified by oracle;
User altered.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0
-
Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
C:\Documents and Settings\Administrator>sqlplus "sys/oracle as
sysdba"
file=
est_tt.dmp tablespaces=TEST_TT TRANSPORT_TABLESPACE=y
Usage: SQLPLUS [ [<option>] [<logon>] [<start>] ]
where <option> ::= -H | -V | [ [-L] [-M <o>] [-R <n>] [-S] ]
<logon> ::= <username>[/<password>][@<connect_string>] | /
|
/NOLOG
<start> ::= @<URI>|<filename>[.<ext>] [<parameter> ...]
"-H" displays the SQL*Plus version banner and usage
syntax
"-V" displays the SQL*Plus version banner
"-L" attempts log on just once
"-M <o>" uses HTML markup options <o>
"-R <n>" uses restricted mode <n>
"-S" uses silent mode
C:\Documents and Settings\Administrator>exp "sys/oracle as
sysdba"
file=d:\t
tt.dmp tablespaces=TEST_TT TRANSPORT_TABLESPACE=y
LRM-00108: invalid positional parameter value 'as'
EXP-00019: failed to process parameters, type 'EXP HELP=Y' for
help
EXP-00000: Export terminated unsuccessfully
C:\Documents and Settings\Administrator>exp 'sys/oracle as
sysdba'
file=d:\t
tt.dmp tablespaces=TEST_TT TRANSPORT_TABLESPACE=y
Export: Release 9.2.0.1.0 - Production on Wed Sep 17 09:35:09
2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights
reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 -
Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR
character set
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace TEST_TT ...
. exporting cluster definitions
. exporting table definitions
. . exporting table TT_TAB
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.
C:\Documents and Settings\Administrator>sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.1.0 - Production on Wed Sep 17 09:40:22
2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights
reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0
-
Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
C:\Documents and Settings\Administrator>d:
D:\>mkdir d:\bkup
D:\>copy TEST.DBF d:\bkup
1 file(s) copied.
D:\>sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.1.0 - Production on Wed Sep 17 09:40:50
2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights
reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> alter tablespace test_tt read write;
Tablespace altered.
SQL> insert into test.tt_tab values(2);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test.tt_tab;
A
----------
1
2
SQL> rem this was after export ;
SQL> drop tablespace test_tt including contents and datafiles;
Tablespace dropped.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0
-
Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
D:\>imp 'sys/oracle as sysdba' file=d:\test_tt.dmp
tablespaces=TEST_TT
TRANS
_TABLESPACE=y datafiles='d:\test.dbf'
Import: Release 9.2.0.1.0 - Production on Wed Sep 17 09:43:03
2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights
reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 -
Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
About to import transportable tablespace(s) metadata...
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR
character set
. importing SYS's objects into SYS
IMP-00017: following statement failed with ORACLE error 1565:
"BEGIN
sys.dbms_plugts.beginImpTablespace('TEST_TT',12,'SYS',1,0,8192,1,3
"710620,1,2147483645,8,128,8,0,1,0,8,1151101578,1,1,35710182,NULL,0,0
,NULL,
"ULL); END;"
IMP-00003: ORACLE error 1565 encountered
ORA-01565: error in identifying file 'd:\test.dbf'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
ORA-06512: at "SYS.DBMS_PLUGTS", line 1441
ORA-06512: at line 1
IMP-00000: Import terminated unsuccessfully
D:\>copy d:\bkup\TEST.DBF d:\
1 file(s) copied.
D:\>imp 'sys/oracle as sysdba' file=d:\test_tt.dmp
tablespaces=TEST_TT
TRANS
_TABLESPACE=y datafiles='d:\test.dbf'
Import: Release 9.2.0.1.0 - Production on Wed Sep 17 09:43:18
2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights
reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 -
Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
About to import transportable tablespace(s) metadata...
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR
character set
. importing SYS's objects into SYS
. importing TEST's objects into TEST
. . importing table "TT_TAB"
Import terminated successfully without warnings.
D:\>sqlplus test/test
SQL*Plus: Release 9.2.0.1.0 - Production on Wed Sep 17 09:43:26
2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights
reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> select * from tt_tab;
A
----------
1
SQL>
What we have done is that we made a user Test owning a tablespace
Test_tt. We put one value in it(our original work) and we exported the ”good time” tablespace. We did some more work,inserted one more
value(can call it changes or testing) and once we finished,we dropped the
tablespace. We didn’t drop the user. We imported back the tablespace
and we were back to value 1 from where we started. More or less,same like tablespace attached and detached.
Aman….
Posted in Uncategorized | 5 Comments »
Well Oracle is changing and I believe that there are alot changes which are happening “under-the-hood”. I just got a question over Forums that can Shared Pool shrink if we are using ASMM?Well the docs say no that wont be possible. Well the answer as per docs is No,its not possible that oracle will shrink the shared pool. Another fellow poster over forums, gave this link where Tanel Poder revealed one more “hidden” thing that Oracle from 10.2 onwards , is keeping Database buffer cache chunks in the Shared Pool heap. Now my first reponse what WTH! Why they would want to do that? But Tanel explains it well. I am posting the entire entry here with the reference. Things are not changing in a “big time”.
Well, believe or not, in addition to keeping private undo and redo buffers in shared pool, Oracle can nowadays hold some of the buffer cache there as well.
Sounds crazy? Check this!
SQL> select
2 s.ksmchptr SP_CHUNK,
3 s.ksmchsiz CH_SIZE,
4 b.obj DATAOBJ#,
5 b.ba BLOCKADDR,
6 b.blsiz BLKSIZE,
7 decode(b.class,
8 1,'data block',
9 2,'sort block',
10 3,'save undo block',
11 4,'segment header',
12 5,'save undo header',
13 6,'free list',
14 7,'extent map',
15 8,'1st level bmb',
16 9,'2nd level bmb',
17 10,'3rd level bmb',
18 11,'bitmap block',
19 12,'bitmap index block',
20 13,'file header block',
21 14,'unused',
22 15,'system undo header',
23 16,'system undo block',
24 17,'undo header',
25 18,'undo block',
26 class) BLKTYPE,
27 decode (b.state,
28 0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',
29 5,'mrec',6,'irec',7,'write',8,'pi', 9,'memory',
30 10,'mwrite',11,'donated',b.state) BLKSTATE
31 from
32 x$bh b,
33 x$ksmsp s
34 where (
35 b.ba >= s.ksmchptr
36 and to_number(b.ba, 'XXXXXXXXXXXXXXXX') + b.blsiz <
to_number(ksmchptr, 'XXXXXXXXXXXXXXXX') + ksmchsiz
37 )
38 and s.ksmchcom = 'KGH: NO ACCESS'
39 order by s.ksmchptr, b.ba;
SP_CHUNK CH_SIZE DATAOBJ# BLOCKADDR BLKSIZE BLKTYPE BLKSTATE
---------------- ---------- ---------- ---------------- -------
-------------------- ----------
0000000387C01FE0 1269792 9001 0000000387C26000 8192 data block
xcur
9001 0000000387C28000 8192 data block
xcur
9001 0000000387C2A000 8192 data block
xcur
2 0000000387C2C000 8192 data block
xcur
9001 0000000387C2E000 8192 1st level
bmb xcur
9001 0000000387C30000 8192 2nd level
bmb xcur
9001 0000000387C32000 8192 segment
header xcur
4294967295 0000000387C34000 8192 36
xcur
4294967295 0000000387C36000 8192 36
xcur
51673 0000000387C38000 8192 data block
xcur
4294967295 0000000387C3A000 8192 36
xcur
4294967295 0000000387C3C000 8192 22
xcur
4294967295 0000000387C3E000 8192 22
xcur
37 0000000387C40000 8192 data block
xcur
4294967295 0000000387C42000 8192 22
xcur
4294967295 0000000387C44000 8192 30
xcur
4294967295 0000000387C46000 8192 30
xcur
4294967295 0000000387C48000 8192 30
xcur
573 0000000387C4A000 8192 data block
xcur
From matching SP_CHUNK and BLOCKADDR values you see that there are cache buffers which actually reside in shared pool heap.
When MMAN tries to get rid of a shared pool granule it obviously can’t just flush and throw away all the object in it. As long as anybody references chunks in this granule, it cannot be completely deallocated.
Oracle has faced a decision, what to do in this case: 1) wait until all chunks aren’t in use anymore – this might never happen 2) suspend the instance, relocate chunks somewhere else and update all SGA/PGA/UGA/CGA structures for all processes accordingly – this would get very complex
3) flush as many chunks from this shared pool granule as possible, mark them as “KGH: NO ACCESS” that nobody else would touch them, mark corresponding entry to DEFERRED in V$SGA_RESIZE_OPS and notify buffer cache manager, about the new memory locations being available for use.
Oracle has gone with option 3 as option 1 wouldn’t satisfy us and 2 would be very complex to implement, and it would mean a complete instance hang for seconds to minutes.
So, Oracle can share a granule between shared pool and buffer cache data. This sounds like a mess, but there is not really a better way to do it (if leaving the question, why the heck do you want to continuously reduce your shared pool size anyway, out).
This was tested on Oracle 10.2.0.2 on Solaris 10/x64
Tanel.
And the link is,
http://www.orafaq.com/maillist/oracle-l/2006/08/22/0958.htm
My head is spinning :-S.
Aman….
Posted in Research | 1 Comment »
Today in my program, we hit with an almost uncertain error. We were trying to create listener using EM console but it was throwing us an error that the location is not the right one when the location was perfectly alright. I am not sure what actually is the error but despite the best effort, I couldn’t get any clue.
Now , this was not solved but in the meanwhile some one asked me that what is the use of NetProperties file which is available in $ORACLE_HOME/network/tools. This file is used by the network related tools, for example Net manager, Net Configuration Assistant etc. Despite searching alot, I couldn’t find anything about it either. Grrr!
Well , both the things didn’t lead to no where but some how in the search, I stumbled upon a workaround that we need to do these changes in the Netproperties file to make EM work,
1)Go to $oracle_home/nework/tools
2)In the file Netproperties, comment the line INSTALLEDCOMPONENTS=ORACLENET
And it did work. Now the million dollar question is what does this setting means and why did comment it? And the zillion dollar question( since the starting) remains yet a mystery that what does Netproperties file control?
Search continues….
Aman….
Posted in Uncategorized | Tagged EM, Netproperties | 1 Comment »
Just read from Justin’s blog, Oracle is going to put an Oracle Listen promotion over its web site. Check this, http://blogs.oracle.com/otn/2008/09/oracle_listens_1.html
So it means that we would actually get a chance to tell Oracle what we want? Cool, now that’s what I call real close to the customers and listening to their heartbeat! Good work. It was supposed to be coming out today but due to some tech issues ( as mentioned by Justin) , it may get delayed. Nevertheless, its a great effort and highly appreciable! Let’s see what sort of suggestions would come up from it?
Posted in Uncategorized | Leave a Comment »
There are very few things that I know but there are tons of things which I don’t know. I came across one more such thing like this few days ago when this question (http://forums.oracle.com/forums/thread.jspa?messageID=2713561�) came over OTN. Interestingly, what the OP mentioned was indeed true and I was not having a clue what’s going on. Like most of the times, JL had an answer to the puzzle and he gave a lead. This blog post is not about any “new” invention that I have done. Its merely a collection of my understanding about a new mechanism in >10g SGAs. So with this note ,let’s get the party started.
Dirty Buffers & Redo Log Buffer
In Oracle buffer cache, buffers stay in multiple states. When a buffer is not changed and is just available for use, it’s a clean buffer. When the buffer encounters a change, the buffer status is marked as Dirty buffer. This status is used by Oracle to provide read-consistancy for the other users who would access the same buffer but when the transaction is alive. In simple terms, a dirty buffer is that one who got an image in it which is not the one when it was brought into the cache. Oracle maintains for this dirty buffer, CR(consistent read) buffers aka snapshot blocks which provide read conistancy for the others.
The change vectors for this buffer are also logged in the redo log buffer before they get applied to the actual buffers(write ahead logging) , using which Oracle maintains this assurance that no matter what, if we have the data in the log buffer, its safe. The data in the log buffer is written by LGWR to the redo log files. Once there, its permanently saved and hence protected against any crash.
In order to log changes in the log buffer, oracle typically follows this process
1) A calculation is done that how much space would be needed in the log buffer.
2) Server process acquires Redo Copy Latch. This is used for making the announcement that some redo will be copied into the log buffer.
3) Acquires Redo Allocation Latch. This is the actual latch which will allocate the space in the log buffer.
4) Redo Allocation Latch is released after the space is acquired in the log buffer.
5) Redo copy latch is used to copy the redo change vectors in the log buffers.
6) Redo copy latch is released.
After the copy is copy is complete and applying them over the buffers in the buffer cache, depending upon various conditions, LGWR may start writing the contents into the log files. How many latches we have got have at the moment for our database can be seen from V$latch_childeren. Here is an output from my 11gr1 machine.
1 select latch#,child#,name from V$latch_children
2* where name like ‘%redo%’ order by 1,2
SQL> /
LATCH# CHILD# NAME
———- ———- —————————————————————-
169 1 redo copy
169 2 redo copy
169 3 redo copy
169 4 redo copy
170 1 redo allocation
170 2 redo allocation
170 3 redo allocation
170 4 redo allocation
170 5 redo allocation
170 6 redo allocation
170 7 redo allocation
170 8 redo allocation
170 9 redo allocation
170 10 redo allocation
170 11 redo allocation
170 12 redo allocation
170 13 redo allocation
170 14 redo allocation
170 15 redo allocation
170 16 redo allocation
170 17 redo allocation
170 18 redo allocation
170 19 redo allocation
170 20 redo allocation
24 rows selected.
With heavy processing, lots of redo being generated, there may be a contention over the redo allocation latches. So for this purpose, there was a parameter Log_parallelism( http://download.oracle.com/docs/cd/B10501_01/server.920/a96536/ch1105.htm#REFRN10099) in 9i which could be used to make the redo generation go more faster. This would separate the log buffer into further small buffers. This parameter is only till 9i and from 10.1 onwards its deprecated(http://download.oracle.com/docs/cd/B28359_01/server.111/b28300/changes.htm#UPGRD12575 ) as in 10g, the mechanism is changed ( and is the actual topic of this post) which handles the excessive redo workload.
So the moral of the story so far comes up is that when we change some thing in the buffer cache, the change is protected in the log buffer. To do this two latches are majorly used. The changes in the log buffer are protected in the physical log files by LGWR.
You must be thinking that that is this the replica of docs or what? Well certainly, it’s the basics and I thought before I forget all this, I must write it down and this talks about the terms which will be coming up a little later in the post. So chill J. Told you its not any new invention but collection .
What’s Wrong With All This
Well actually nothing much. This is some this which is happening since the inception of Oracle I believe. Some things came and go but in nutshell things remained the same. This allocation of latches and other stuff did prove to be fine but as they all good things come to an end, this also needed some modifications. As with the workloads being more heavy and with much bigger databases and higher transaction rates, it becomes too tough to manage the redo allocations using latches. Clearly for the reasons that are well documented in PT guide that with lots of redo, the latches required to copy the redo data in the log buffer come under contention and so does the LGWR process too. There is a workaround to invoke multiple slaves for LGWR for heavy systems but that too is not some thing which is not recommended as long as not fully benchmarked. Surely there are tons of guidelines that are there for users in order to make transactions small, have a good media where redo can stay and so on but they can be applied to a certain level. So there was a need for things to be modified from the grass root level and that’s what happened in 10g(10.1) onwards.
A Strange Output
With the Dirty Buffers discussion given above, you must be clear about the idea about them. So here is small table creation and modification and we would see some dirty buffers getting created,
SQL> select * from V$version;
BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
PL/SQL Release 11.1.0.6.0 – Production
CORE 11.1.0.6.0 Production
TNS for 32-bit Windows: Version 11.1.0.6.0 – Production
NLSRTL Version 11.1.0.6.0 – Production
SQL> alter system flush buffer_cache;
System altered.
SQL> create table t(a number)
2 /
Table created.
SQL> SELECT b.status, object_name, object_type, dirty “Dirty” FROM v$bh b, dba_objects o
2 WHERE b.objd = o.data_object_id AND o.owner = ‘AMAN’ and object_name=’T';
STATUS OBJECT_NAME
———- ———————————————————————————–
———
OBJECT_TYPE D
——————- -
xcur T
TABLE Y
xcur T
TABLE Y
xcur T
TABLE Y
SQL> insert into t values(1);
1 row created.
SQL> SELECT b.status, object_name, object_type, dirty “Dirty” FROM v$bh b, dba_objects o
2 WHERE b.objd = o.data_object_id AND o.owner = ‘AMAN’ and object_name=’T';
STATUS OBJECT_NAME
———- ———————————————————————————–
———
OBJECT_TYPE D
——————- -
xcur T
TABLE Y
xcur T
TABLE N
xcur T
TABLE N
xcur T
TABLE Y
xcur T
TABLE N
xcur T
TABLE Y
xcur T
TABLE N
xcur T
TABLE N
8 rows selected.
SQL>
SQL> commit;
Commit complete.
SQL> SELECT b.status, object_name, object_type, dirty “Dirty” FROM v$bh b, dba_objects o
2 WHERE b.objd = o.data_object_id AND o.owner = ‘AMAN’ and object_name=’T';
STATUS OBJECT_NAME
———- —————————————————————————–
———
OBJECT_TYPE D
——————- -
xcur T
TABLE Y
xcur T
TABLE Y
xcur T
TABLE Y
xcur T
TABLE Y
xcur T
TABLE Y
xcur T
TABLE Y
xcur T
TABLE Y
xcur T
TABLE Y
8 rows selected.
SQL>
Well if you have seen the output with a little care, you must have seen that even when we have given the dmp(insert in this case) there were no buffers which were marked dirty. Oracle did acquire some buffers to keep our changes but they were not marked dirty. But when we issued commit, all the buffers were marked dirty. Why so?
The New SGA Of IMU/ Private Redo Strands
Due to the reasons mentioned above for the allocation and redo latch contentions, from 10.1 onwards, Oracle has introduced private redo allocation areas and In memory Undo.
IMU(In Memory Undo)
With the same reason that Oracle needs to protect redo, it also has to protect Undo to make sure that the block’s old contents are also saved. This would require an access to the blocks and than later on change the transaction table to make the changes logged. The transaction records can be seen from V$transaction. In order to access undo segment header blocks directly, we can also check X$KTUXE and for the base table of V$transaction, X$KTCXB can be used. From oracle 10g onwards, Oracle does use the Undo blocks but makes them available in private memory areas which are specifically created in the Shared Pool. Each pool is containing Undo data of a transaction. Undo data is updated into the segment header but not into the undo blocks. For maintaining them, in the shared pool , memory areas are configured which are used to protect the undo that is coming up. These are controlled by two parameters _in_memory_undo and _imu_pools. Both can be seen from the base tables x$ksppi and x$ksppcv2
SQL> l
1 select KSPFTCTXDVL,kspftctxdf,ksppinm
2 from x$ksppcv2 a,x$ksppi b
3* where a.indx=b.indx and b.ksppinm like ‘%imu%’
SQL> column KSPFTCTXDVL format a10
SQL> /
KSPFTCTXDV KSPFTC KSPPINM
———- —— —————————————————
2 TRUE _simulator_upper_bound_multiple
16 TRUE _simulator_pin_inval_maxcnt
10240 TRUE _simulator_lru_rebalance_thresh
5 TRUE _simulator_lru_rebalance_sizthr
8192 TRUE _simulator_bucket_mindelta
8 TRUE _simulator_lru_scan_count
10 TRUE _simulator_internal_bound
1024 TRUE _simulator_reserved_obj_count
4096 TRUE _simulator_reserved_heap_count
2 TRUE _simulator_sampling_factor
0 TRUE _simulate_mem_transfer
0 TRUE _db_writer_flush_imu
0 TRUE _minimum_giga_scn
1800 TRUE _log_simultaneous_copies
d:\oracle\ FALSE _allow_error_simulation
flash_reco
very_area
SLFLUSH TRUE _gc_policy_minimum
FALSE TRUE _gc_maximum_bids
TRUE TRUE _minimum_db_flashback_retention
600 TRUE _recursive_imu_transactions
TRUE TRUE _smu_error_simulation_site
TRUE TRUE _smu_error_simulation_type
0 TRUE _imu_pools
10 TRUE _minimum_blocks_to_shrink
TRUE TRUE _minimum_extents_to_shrink
RUN_TIME TRUE _plsql_minimum_cache_hit_percent
25 rows selected.
And
SQL> l
1 select KSPFTCTXDVL,kspftctxdf,KSPFTCTXVL,KSPFTCTXIS,ksppinm
2 from x$ksppcv2 a,x$ksppi b
3* where a.indx=b.indx and b.ksppinm like ‘%undo%’
SQL> /
KSPFTCTXDV KSPFTC KSPFTCTXVL KSPFTC KSPPINM
———- —— ———- —— ——————–
FALSE TRUE FALSE FALSE _gc_undo_affinity
AUTO TRUE AUTO FALSE _gc_dissolve_undo_af
finity
TRUE TRUE TRUE FALSE _gc_initiate_undo_af
finity
0 TRUE 0 FALSE _gc_undo_block_disk_
reads
TRUE FALSE _undo_block_compress
ion
TRUE FALSE undo_management
TRUE FALSE undo_tablespace
100 TRUE 100 FALSE _collect_undo_stats
0 TRUE 0 FALSE _undo_debug_mode
0 TRUE 0 FALSE _verify_undo_quota
TRUE FALSE _in_memory_undo
FALSE TRUE FALSE FALSE _undo_autotune
FALSE TRUE FALSE FALSE undo_retention
0 TRUE 0 FALSE _undo_debug_usage
0 TRUE 0 FALSE _smon_undo_seg_resca
n_limit
0 TRUE 0 FALSE _optimizer_undo_cost
_change
TRUE TRUE TRUE FALSE _optimizer_undo_chan
ges
17 rows selected.
Each pool is maintained by a latch and that is called “in memory undo latch”. They can be seen from V$latch_children
SQL> select name from V$latch_children where name like ‘%undo%’;
NAME
—————————————————————-
undo global data
undo global data
undo global data
In memory undo latch
In memory undo latch
In memory undo latch
In memory undo latch
In memory undo latch
In memory undo latch
In memory undo latch
In memory undo latch
In memory undo latch
In memory undo latch
In memory undo latch
In memory undo latch
In memory undo latch
In memory undo latch
In memory undo latch
In memory undo latch
In memory undo latch
In memory undo latch
21 rows selected.
We can see that in the shared pool, regarding Undo,there are several structures available.
SQL> select pool,name from V$sgastat where pool=’shared pool’ and lower(name) like ‘%undo%’;
POOL NAME
———— ————————–
shared pool UNDO INFO SEGMENTED ARRAY
KCB undo bitvec
Undo Meta Data
Auto tune undo info
UNDO INFO
KTI-UNDO
UNDO INFO HASH
UNDO STAT INFO
The IMU pools are allocated in the shared pool. The data that we change gets copied to these pools from a latch called In memory Undo latch. We can see it here from V$latch,
SQL> select name from V$latch where name like ‘%undo%’;
NAME
—————————————————————-
undo global data
corrupted undo seg lock
In memory undo latch
When we modify the data, oracle doesn’t apply the data to the data blocks immediately but It allocates the data in the IMU pools in the shared pool with the help of this IMU latch. This mechanism avoids the pinning of the Undo segment header block and also to the undo data blocks which are otherwise used immediately when the transaction starts. Now they are only used when the transaction commits. The data is in the pool and from there only it gets copied to the buffer cache. As each pool is assigned to one transaction and is maintained by an individual latch, the mechanism works well to reduce the contention. I am still not clear that with a very very heavy OLTP environment, what would happen? Are the pools increased at that time or not, or some thing else happens, I am not sure.
The IMU pools are also described in the X$ktifp. The size of the pools are 65kb and can be seen as follows,
SQL> select ktifpno,ktifppsi from x$ktifp;
KTIFPNO KTIFPPSI
———- ———-
0 65535
1 65535
2 65535
3 65535
4 65535
5 65535
6 65535
7 65535
8 65535
9 65535
10 65535
KTIFPNO KTIFPPSI
———- ———-
11 65535
12 65535
13 65535
14 65535
15 65535
16 65535
17 65535
18 rows selected.
So there are 18 pools allocated and are of 65kb each in size. This undo data is “flushed” to the undo segments when the pools are full. This write happens in the batch format. There are events when the data in the IMU pools gets flushed. These events are mentioned in the base table x$ktiff. We can see the reasons here,
SQL> l
1 select KTIFFCAT, KTIFFFLC
2* from x$ktiff
SQL> /
KTIFFCAT KTIFFFLC
—————————————- ———-
Undo pool overflow flushes 1
Stack cv flushes 9
Multi-block undo flushes 0
Max. chgs flushes 2
NTP flushes 0
Contention flushes 9
Redo pool overflow flushes 0
Logfile space flushes 0
Multiple persistent buffer flushes 0
Bind time flushes 0
Rollback flushes 335
Commit flushes 1570
Recursive txn flushes 2
Redo only CR flushes 0
Ditributed txn flushes 0
Set txn use rbs flushes 0
Bitmap state change flushes 21
Presumed commit violation 0
Securefile direct-write lob update flush 0
es
19 rows selected.
We can see with simple DML that data is being changed and IMU is being flushed.
In session 1, I made a table,
C:\Documents and Settings\Aman>sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 – Production on Sat Aug 30 11:42:21 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> create table a as select * from dba_objects;
Table created.
SQL>
And in session 2(the same session from where we saw the events),
SQL> /
KTIFFCAT KTIFFFLC
—————————————- ———-
Undo pool overflow flushes 1
Stack cv flushes 9
Multi-block undo flushes 0
Max. chgs flushes 2
NTP flushes 0
Contention flushes 9
Redo pool overflow flushes 0
Logfile space flushes 0
Multiple persistent buffer flushes 0
Bind time flushes 0
Rollback flushes 338
Commit flushes 1592
Recursive txn flushes 2
Redo only CR flushes 0
Ditributed txn flushes 0
Set txn use rbs flushes 0
Bitmap state change flushes 21
Presumed commit violation 0
Securefile direct-write lob update flush 0
es
19 rows selected.
And in session 1,
SQL> select n.name, s.value from v$statname n, v$sesstat s
2 where n.statistic# = s.statistic#
3 and s.sid = (select sid from v$mystat where rownum = 1)
4 and n.name in (‘IMU Flushes’,'IMU commits’);
NAME VALUE
—————————————————————- ———-
IMU commits 0
IMU Flushes 0
SQL>
We do some change in the data in session 1,
SQL> insert into a select * from a;
68864 rows created.
SQL> select n.name, s.value from v$statname n, v$sesstat s
2 where n.statistic# = s.statistic#
3 and s.sid = (select sid from v$mystat where rownum = 1)
4 and n.name in (‘IMU Flushes’,'IMU commits’);
NAME VALUE
—————————————————————- ———-
IMU commits 0
IMU Flushes 1
And we have got a flush happened as the data generated is too much big. Lets see in session 2,
SQL> /
KTIFFCAT KTIFFFLC
—————————————- ———-
Undo pool overflow flushes 1
Stack cv flushes 9
Multi-block undo flushes 0
Max. chgs flushes 2
NTP flushes 0
Contention flushes 9
Redo pool overflow flushes 1
Logfile space flushes 0
Multiple persistent buffer flushes 0
Bind time flushes 0
Rollback flushes 353
Commit flushes 1654
Recursive txn flushes 2
Redo only CR flushes 0
Ditributed txn flushes 0
Set txn use rbs flushes 0
Bitmap state change flushes 21
Presumed commit violation 0
Securefile direct-write lob update flush 0
es
19 rows selected.
After some more changes in session 1,
SQL> insert into a select * from a;
137728 rows created.
SQL> where n.statistic# = s.statistic#
SP2-0734: unknown command beginning “where n.st…” – rest of line ignored.
SQL> select n.name, s.value from v$statname n, v$sesstat s
2 where n.statistic# = s.statistic#
3 and s.sid = (select sid from v$mystat where rownum = 1)
4 and n.name in (‘IMU Flushes’,'IMU commits’);
NAME VALUE
—————————————————————- ———-
IMU commits 0
IMU Flushes 1
SQL> commit;
Commit complete.
SQL> select n.name, s.value from v$statname n, v$sesstat s
2 where n.statistic# = s.statistic#
3 and s.sid = (select sid from v$mystat where rownum = 1)
4 and n.name in (‘IMU Flushes’,'IMU commits’);
NAME VALUE
—————————————————————- ———-
IMU commits 0
IMU Flushes 1
SQL>
We see again session 1,
SQL> /
KTIFFCAT KTIFFFLC
—————————————- ———-
Undo pool overflow flushes 1
Stack cv flushes 9
Multi-block undo flushes 0
Max. chgs flushes 2
NTP flushes 0
Contention flushes 10
Redo pool overflow flushes 1
Logfile space flushes 0
Multiple persistent buffer flushes 0
Bind time flushes 0
Rollback flushes 359
Commit flushes 1688
Recursive txn flushes 2
Redo only CR flushes 0
Ditributed txn flushes 0
Set txn use rbs flushes 0
Bitmap state change flushes 21
Presumed commit violation 0
Securefile direct-write lob update flush 0
es
19 rows selected.
SQL>
This is worth to mention that the same kind of mechanism was introduced by Oracle earlier with the help of Discrete Transaction Management, http://download.oracle.com/docs/cd/B10501_01/server.920/a96524/c17trans.htm#421
But this didn’t work out too well as the DTM used to pin the data blocks during the transaction. When the transaction used to get committed than the data was written to the redo logs. In private redo, no pinning of the data blocks is done. Hence they are much better than DTM. DTM is obsolete now.
Private Redo Strands( Private Redo )
Earlier there was a single redo allocation latch available for the entire log buffer. This was a major contention point as till the time one won’t be able to pin the latch, he wont be able to write his changed redo vectors in the log buffer. From 10.1 onwards, the log buffer is broken in to smaller sub-buffers called Private Redo Strands(shown in the x$kcrfstrand). These are also allocated in the shared pool. These all are written by redo allocation latches. There are allocation latches now for each private strand. The data when changed now, doesn’t contend to go into the log buffer as like before but goes into the private redo strands and from there, it goes into the log buffer when the buffer pools become full or when the transaction is committed. We can see the private strands shown in the shared pool from the V$sgastat
SQL> select name,pool from V$sgastat
2 where name like ‘%private%’;
NAME POOL
————————– ————
private strands shared pool
KCB private handles shared pool
At log file switch , when the data is still is in the Private redo strands, lgwr may wait to get the flush done by DBWR. Hence the event, log file switch (private strand flush incomplete) may occur. See the doc note for it,
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/waitevents003.htm#sthref3117
This is controlled by the parameter _log_parallelism which is a dynamic and hidden parameter in 10g. The value depends on the cpu_count parameter. Oracle starts with 2 strands.
The benefit of private redo strand is also for the same reason that this would make the LGWR less busy and also the contention over the redo allocation latches will decrease.
Reference:
www.tanelpoder.com/files/Oracle_Performance_Scalability_10g_11g.pdf
http://www.fortunecity.com/skyscraper/oracle/699/orahtml/hayden/discrete.html
Posted in Uncategorized | 2 Comments »
