Re: Select parallel understanding

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: