Select parallel understanding
- From: Taral Desai <taral.desai@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 23 Nov 2009 17:33:22 -0600
Hi All,
Correct me if i am wrong
I have confusion regarding buffer cache. We are using multiple buffer cache
(this is another issue with management but for now we have to deal with
this). Now, db_nk_block_size cache will be used for all 32k block size
tablespaces and there object when they are accessed.
Now, for parallel these buffers are bypass. But here are the details. Am, I
understand this wrong.
DB: 10.2.0.3
OS: Solaris SPARC(64)
1.
Created four table with same structure and data (except 1 or 2 rows more) in
32k block size TS.
create table test_32_01 tablespace xyz_32k as select * from dba_objects;
create table test_32_02 tablespace xyz_32k as select * from dba_objects;
create table test_32_03 tablespace xyz_32k as select * from dba_objects;
create table test_32_04 tablespace xyz_32k as select * from dba_objects;
This is from segments
SUBSTR(SEGMENT_NAME,1,15) BYTES BLOCKS
------------------------- ---------- ----------
TEST_32_01 7340032 224
TEST_32_02 7340032 224
TEST_32_03 7340032 224
TEST_32_04 7340032 224
Now when check buffer pool there are some initial details of this
POOL OBJECT OBJECT_TYPE
BLOCK_SIZE BLOCKS
-------------------- ------------------------------ -------------------
---------- ----------
DEFAULT TEST_32_01 TABLE
32768 7
DEFAULT TEST_32_02 TABLE
32768 7
DEFAULT TEST_32_03 TABLE
32768 7
DEFAULT TEST_32_04 TABLE
32768 7
insert /*+ append */ into TEST_32_01 select * from TEST_32_02;
insert /*+ append parallel(x,2) */ into TEST_32_03 x select /*+
parallel(y,2) */ * from TEST_32_04 y;
POOL OBJECT OBJECT_TYPE
BLOCK_SIZE BLOCKS
-------------------- ------------------------------ -------------------
---------- ----------
DEFAULT TEST_32_01 TABLE
32768 10
DEFAULT TEST_32_02 TABLE
32768 209
DEFAULT TEST_32_03 TABLE
32768 22
DEFAULT TEST_32_04 TABLE
32768 209
Now if you can see above that for _01 & _03 there is little or very less
blocks in buffer. I was expecting that _04 would also have same thing as
it's running in parallel. Yes, i did make session parallel.
insert into TEST_32_01 select * from TEST_32_02;
POOL OBJECT OBJECT_TYPE
BLOCK_SIZE BLOCKS
-------------------- ------------------------------ -------------------
---------- ----------
DEFAULT TEST_32_01 TABLE
32768 236
DEFAULT TEST_32_02 TABLE
32768 209
DEFAULT TEST_32_03 TABLE
32768 22
DEFAULT TEST_32_04 TABLE
32768 209
Now from above test without append _01 will read all blocks into memory
(buffer) this is expected behavior.
So, if i am not understanding wrong then insert had worked with direct load
but not select ?
Other related posts:
