Archive
Archive for December, 2010
Initrans
December 27, 2010
Leave a comment
I was looking at Anand Blog and thought to mention one thing.
I am using 11.2.0.2 Version and here default initrans is 1. Also, the thing i want to mention is when you increase this parameters older blocks are not affected only newly created blocks after this change would be affected.
Taral@Taral1> drop table test1 purge;
Table dropped.
Taral@Taral1> create table test1(col1 number);
Table created.
Taral@Taral1> select owner,table_name,INI_TRANS,MAX_TRANS from dba_tables where owner='TARAL';
OWNER TABLE_NAME INI_TRANS MAX_TRANS
------------------------------ ------------------------------ ---------- ----------
TARAL TEST1 1 255
Taral@Taral1> insert into test1 select rownum r from dual connect by level<=10000; 10000 rows created. Taral@Taral1> commit;
Commit complete.
Taral@Taral1> select segment_name,tablespace_name,header_file, header_block from dba_segments where segment_name='TEST1';
SEGMENT_NAME TABLESPACE_NAME HEADER_FILE HEADER_BLOCK
--------------------------------------------------------------------------------- ------------------------------ ----------- ------------
TEST1 USERS 4 522
Taral@Taral1> select SEGMENT_NAME,BLOCK_ID,FILE_ID,TABLESPACE_NAME from dba_extents where SEGMENT_NAME='TEST1';
SEGMENT_NAME BLOCK_ID FILE_ID TABLESPACE_NAME
--------------------------------------------------------------------------------- ---------- ---------- ------------------------------
TEST1 520 4 USERS
TEST1 528 4 USERS
TEST1 536 4 USERS
Taral@Taral1>
Taral@Taral1> alter system dump datafile 4 block 528;
System altered.
Block header dump: 0x01000210
Object id on Block? Y
seg/obj: 0x127c1 csc: 0x00.d0319 itc: 2 flg: E typ: 1 - DATA
brn: 1 bdba: 0x1000208 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0001.002.000002ee 0x00c02ec2.008d.02 --U- 660 fsc 0x0000.000d037d
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x01000210
data_block_dump,data header at 0xf298264
Taral@Taral1> alter table test1 initrans 5;
Table altered.
Taral@Taral1> insert into test1 select rownum r from dual connect by level<=10000; 10000 rows created. Taral@Taral1> commit;
Commit complete.
Taral@Taral1> select SEGMENT_NAME,BLOCK_ID,FILE_ID,TABLESPACE_NAME from dba_extents where SEGMENT_NAME='TEST1';
SEGMENT_NAME BLOCK_ID FILE_ID TABLESPACE_NAME
--------------------------------------------------------------------------------- ---------- ---------- ------------------------------
TEST1 520 4 USERS
TEST1 528 4 USERS
TEST1 536 4 USERS
TEST1 544 4 USERS
TEST1 552 4 USERS
Taral@Taral1> alter system dump datafile 4 block 544;
System altered.
Block header dump: 0x01000220
Object id on Block? Y
seg/obj: 0x127c2 csc: 0x00.db29d itc: 5 flg: E typ: 1 - DATA
brn: 1 bdba: 0x1000218 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0007.00b.00000306 0x00c00861.009a.05 --U- 654 fsc 0x0000.000db2a5
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x04 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x05 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x01000220
data_block_dump,data header at 0xf2982ac
Taral@Taral1> alter system dump datafile 4 block 528;
System altered.
Block header dump: 0x01000210
Object id on Block? Y
seg/obj: 0x127c2 csc: 0x00.d03e4 itc: 2 flg: E typ: 1 - DATA
brn: 1 bdba: 0x1000208 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.015.000002fd 0x00c00112.00a4.0c --U- 660 fsc 0x0000.000d03ec
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x01000210
data_block_dump,data header at 0xf298264
Blocks already allocated are not affected but newly created blocks will take new settings.
Categories: Internal
How To Provide Hint
December 27, 2010
Leave a comment
Today Learned new way to provide hint in oracle. May be other guys know this but i didn’t know this and one of our developer showed me. Thought to give a shot on this. Anyways, generally we provide hint with /*+..*/ Format. But see this
Taral@Taral1> drop table test1 purge;
Table dropped.
Taral@Taral1> create table test1 as select * from dba_objects;
Table created.
Taral@Taral1> insert --+ append
2 into test1 select * from dba_objects;
73253 rows created.
Taral@Taral1> select count(*) from test1;
select count(*) from test1
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
Taral@Taral1> commit;
Commit complete.
Taral@Taral1> select count(*) from test1;
COUNT(*)
----------
146506
============
Plan Table
============
------------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------------------+-----------------------------------+
| 0 | INSERT STATEMENT | | | | 261 | |
| 1 | LOAD AS SELECT | | | | | |
| 2 | VIEW | DBA_OBJECTS| 68K | 14M | 261 | 00:00:04 |
| 3 | UNION-ALL | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID | SUM$ | 1 | 9 | 1 | 00:00:01 |
| 5 | INDEX UNIQUE SCAN | I_SUM$_1 | 1 | | 0 | |
| 6 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 30 | 3 | 00:00:01 |
| 7 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 2 | 00:00:01 |
| 8 | FILTER | | | | | |
| 9 | HASH JOIN | | 72K | 8828K | 258 | 00:00:04 |
| 10 | TABLE ACCESS FULL | USER$ | 93 | 1581 | 3 | 00:00:01 |
| 11 | HASH JOIN | | 72K | 7598K | 255 | 00:00:04 |
| 12 | INDEX FULL SCAN | I_USER2 | 93 | 2046 | 1 | 00:00:01 |
| 13 | TABLE ACCESS FULL | OBJ$ | 72K | 6006K | 253 | 00:00:04 |
| 14 | TABLE ACCESS BY INDEX ROWID | IND$ | 1 | 8 | 2 | 00:00:01 |
| 15 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 1 | 00:00:01 |
| 16 | NESTED LOOPS | | 1 | 29 | 2 | 00:00:01 |
| 17 | INDEX SKIP SCAN | I_USER2 | 1 | 20 | 1 | 00:00:01 |
| 18 | INDEX RANGE SCAN | I_OBJ4 | 1 | 9 | 1 | 00:00:01 |
| 19 | NESTED LOOPS | | 1 | 105 | 3 | 00:00:01 |
| 20 | TABLE ACCESS FULL | LINK$ | 1 | 88 | 2 | 00:00:01 |
| 21 | TABLE ACCESS CLUSTER | USER$ | 1 | 17 | 1 | 00:00:01 |
| 22 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 | |
------------------------------------------------------+-----------------------------------+
Dumping Hints
=============
atom_hint=(@=29F46C58 err=0 resol=0 used=1 token=814 org=1 lvl=1 txt=APPEND ())
To Further confirm this hint is been used. See the PLAN and last part. It says it’s used. And this will work with mostly all kind of hints. Everyday new learning.
Categories: Troubleshooting
