Re: Select parallel understanding

From: Dion Cho <ukja.dion_at_gmail.com>
Date: Tue, 24 Nov 2009 10:52:30 +0900
Message-ID: <43c2e3d60911231752o3313dbecl2c0c449bc348cf36_at_mail.gmail.com>

Might be the effect of _small_table_threshold parameter?

UKJA_at_ukja1021> _at_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_at_gmail.com>

> 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 ?
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Nov 23 2009 - 19:52:30 CST