Re: Select parallel understanding
- From: Dion Cho <ukja.dion@xxxxxxxxx>
- To: taral.desai@xxxxxxxxx
- Date: Tue, 24 Nov 2009 10:52:30 +0900
Might be the effect of _small_table_threshold parameter?
UKJA@ukja1021> @para small_table
old 9: and i.ksppinm like '%&1%'
new 9: and i.ksppinm like '%small_table%'
NAME VALUE IS_DEFAUL SES_MODIFI
------------------------------ -------------------- --------- ----------
SYS_MODIFI
----------
DESCRIPTION
------------------------------------------------------------------------
_small_table_threshold 1217 TRUE true
deferred
threshold level of table size for direct reads
Can you check the value of "_small_table_threshold" parameter?
================================
Dion Cho - Oracle Performance Storyteller
http://dioncho.wordpress.com (english)
http://ukja.tistory.com (korean)
http://dioncho.blogspot.com (japanese)
http://ask.ex-em.com (q&a)
================================
2009/11/24 Taral Desai <taral.desai@xxxxxxxxx>
> 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:
