Test Around Small Table Threshold
[中文]
Author:
fuyuncat
Source:
www.HelloDBA.com
Date:
2011-12-29 08:53:12
Share to
We know, the parameter "_small_table_threshold" decides a table is a small table or not. When full scan on large tables, in LRU algorithm, the data blocks will be put in a different position in LRU list other than small tables; while when performing some other data-intensive operations, such as parallel query or direct load, data blocks will be read directly from disk bypassing buffer cache.
Defaultly, it's automatically set by oracle as 2% of "_db_block_buffers" when instance start up. For instance, in my testing db (10.2.0.4)
SQL代码
- HELLODBA.COM>select name, value from all_parameters where name = '_db_block_buffers';
- NAME VALUE
- ------------------------------ ------------------------------
- _db_block_buffers 15918
- HELLODBA.COM>select name, value from all_parameters where name = '_small_table_threshold';
- NAME VALUE
- ------------------------------ ------------------------------
- _small_table_threshold 318
Here, "_db_block_buffers" is 15918, thereby, "_small_table_threshold"=15918*0.02=318.
To show how it becomes effective, we can simply run a parallel query on a testing table.
SQL代码
- HELLODBA.COM>create table dummy_tab as select a.* from dba_tables a, dba_tables b where rownum<=10650;
- Table created.
- HELLODBA.COM>exec dba_seg_info('DEMO','TABLE','DUMMY_TAB');
- Free Blocks.............................
- Total Blocks............................384
- Total Bytes.............................3145728
- Unused Blocks...........................52
- Unused Bytes............................425984
- Last Used Ext FileId....................5
- Last Used Ext BlockId...................265097
- Last Used Block.........................76
- *************************************************
- The segment is analyzed
- 0% -- 25% free space blocks.............0
- 0% -- 25% free space bytes..............0
- 25% -- 50% free space blocks............0
- 25% -- 50% free space bytes.............0
- 50% -- 75% free space blocks............0
- 50% -- 75% free space bytes.............0
- 75% -- 100% free space blocks...........0
- 75% -- 100% free space bytes............0
- Unused Blocks...........................0
- Unused Bytes............................0
- Total Blocks............................318
- Total bytes.............................2605056
- PL/SQL procedure successfully completed.
We just created a test table, whose number of blocks is equal to the threshold. And now parallel query it,
SQL代码
- HELLODBA.COM>set autot trace
- HELLODBA.COM>select /*+parallel(t 2)*/* from dummy_tab t;
- 10650 rows selected.
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 2889160364
- ---------------------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
- ---------------------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 12237 | 5963K| 372 (1)| 00:00:02 | | | |
- | 1 | PX COORDINATOR | | | | | | | | |
- | 2 | PX SEND QC (RANDOM)| :TQ10000 | 12237 | 5963K| 372 (1)| 00:00:02 | Q1,00 | P->S | QC (RAND) |
- | 3 | PX BLOCK ITERATOR | | 12237 | 5963K| 372 (1)| 00:00:02 | Q1,00 | PCWC | |
- | 4 | TABLE ACCESS FULL| DUMMY_TAB | 12237 | 5963K| 372 (1)| 00:00:02 | Q1,00 | PCWP | |
- ---------------------------------------------------------------------------------------------------------------
- Note
- -----
- - dynamic sampling used for this statement
- Statistics
- ----------------------------------------------------------
- 1587 recursive calls
- 3 db block gets
- 718 consistent gets
- 318 physical reads
- 780 redo size
- 254481 bytes sent via SQL*Net to client
- 8291 bytes received via SQL*Net from client
- 711 SQL*Net roundtrips to/from client
- 7 sorts (memory)
- 0 sorts (disk)
- 10650 rows processed
- HELLODBA.COM>select /*+parallel(t 2)*/* from dummy_tab t;
- 10650 rows selected.
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 2889160364
- ---------------------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
- ---------------------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 12237 | 5963K| 372 (1)| 00:00:02 | | | |
- | 1 | PX COORDINATOR | | | | | | | | |
- | 2 | PX SEND QC (RANDOM)| :TQ10000 | 12237 | 5963K| 372 (1)| 00:00:02 | Q1,00 | P->S | QC (RAND) |
- | 3 | PX BLOCK ITERATOR | | 12237 | 5963K| 372 (1)| 00:00:02 | Q1,00 | PCWC | |
- | 4 | TABLE ACCESS FULL| DUMMY_TAB | 12237 | 5963K| 372 (1)| 00:00:02 | Q1,00 | PCWP | |
- ---------------------------------------------------------------------------------------------------------------
- Note
- -----
- - dynamic sampling used for this statement
- Statistics
- ----------------------------------------------------------
- 6 recursive calls
- 0 db block gets
- 421 consistent gets
- 0 physical reads
- 0 redo size
- 256926 bytes sent via SQL*Net to client
- 8291 bytes received via SQL*Net from client
- 711 SQL*Net roundtrips to/from client
- 1 sorts (memory)
- 0 sorts (disk)
- 10650 rows processed
In the 2nd round, there is no physical reads reported, which means the data blocks read in the 1st round has been cached and data blocks were read from buffer cache in the 2nd round, and also saying, it's small table, so far.
Now, we append some data into it, raise the HWM up.
SQL代码
- HELLODBA.COM>insert /*+append*/into dummy_tab select * from t_tables where rownum<=30;
- 30 rows created.
- HELLODBA.COM>commit;
- Commit complete.
- HELLODBA.COM>exec dba_seg_info('DEMO','TABLE','DUMMY_TAB');
- Free Blocks.............................
- Total Blocks............................384
- Total Bytes.............................3145728
- Unused Blocks...........................51
- Unused Bytes............................417792
- Last Used Ext FileId....................5
- Last Used Ext BlockId...................265481
- Last Used Block.........................77
- *************************************************
- The segment is analyzed
- 0% -- 25% free space blocks.............0
- 0% -- 25% free space bytes..............0
- 25% -- 50% free space blocks............0
- 25% -- 50% free space bytes.............0
- 50% -- 75% free space blocks............0
- 50% -- 75% free space bytes.............0
- 75% -- 100% free space blocks...........0
- 75% -- 100% free space bytes............0
- Unused Blocks...........................0
- Unused Bytes............................0
- Total Blocks............................319
- Total bytes.............................2613248
- PL/SQL procedure successfully completed.
Noted that 1 block was added, just over threshold. Now, run parallel query again,
SQL代码
- HELLODBA.COM>set autot trace statistics
- HELLODBA.COM>select /*+parallel(t 2)*/* from dummy_tab t;
- 10680 rows selected.
- Statistics
- ----------------------------------------------------------
- 6 recursive calls
- 0 db block gets
- 373 consistent gets
- 319 physical reads
- 0 redo size
- 259280 bytes sent via SQL*Net to client
- 8313 bytes received via SQL*Net from client
- 713 SQL*Net roundtrips to/from client
- 1 sorts (memory)
- 0 sorts (disk)
- 10680 rows processed
- HELLODBA.COM>select /*+parallel(t 2)*/* from dummy_tab t;
- 10680 rows selected.
- Statistics
- ----------------------------------------------------------
- 6 recursive calls
- 0 db block gets
- 373 consistent gets
- 319 physical reads
- 0 redo size
- 256259 bytes sent via SQL*Net to client
- 8313 bytes received via SQL*Net from client
- 713 SQL*Net roundtrips to/from client
- 1 sorts (memory)
- 0 sorts (disk)
- 10680 rows processed
physical reads never disappear, that means data blocks are read direct from disk bypassing buffer cache. Statistic data "Physical Reads Direct" shows this activity evidently.
SQL代码
- D:\HelloDBA.COM\perl scripts>perl getsqlstats.pl
- tnsname (default is orcl):
- user name (default is demo):
- password:
- Array size (Default is 32):
- Execution plan format (Default is TYPICAL):
- SQL to be executed (without ;):
- select /*+parallel(t 2)*/* from dummy_tab t
- Seesion ID: 299 Process ID: 101596
- SQL Prepared. Process paused, press ENTER to continue...
- SQL is executing ...
- SQL Executed. Process Paused, press ENTER to continue...
- SQL_ID 9fjrktd5gkmmh, child number 0
- -------------------------------------
- select /*+parallel(t 2)*/* from dummy_tab t
- Plan hash value: 2889160364
- ---------------------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
- ---------------------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | | | 372 (100)| | | | |
- | 1 | PX COORDINATOR | | | | | | | | |
- | 2 | PX SEND QC (RANDOM)| :TQ10000 | 12237 | 5963K| 372 (1)| 00:00:02 | Q1,00 | P->S | QC (RAND) |
- | 3 | PX BLOCK ITERATOR | | 12237 | 5963K| 372 (1)| 00:00:02 | Q1,00 | PCWC | |
- |* 4 | TABLE ACCESS FULL| DUMMY_TAB | 12237 | 5963K| 372 (1)| 00:00:02 | Q1,00 | PCWP | |
- ---------------------------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 4 - access(:Z>=:Z AND :Z<=:Z)
- Note
- -----
- - dynamic sampling used for this statement
- statistics
- -----------------------------------------
- consistent changes: 0
- consistent gets: 373
- consistent gets direct: 319
- consistent gets from cache: 54
- db block changes: 0
- db block gets: 0
- db block gets direct: 0
- db block gets from cache: 0
- physical reads: 319
- physical reads cache: 0
- physical reads direct: 319
- physical reads direct temporary tablespace: 0
- session logical reads: 373
Another message beyond this topic is that, there are 6 recursive calls for parallel query, which involved CR from caches. By tracing 10046 event, the actual CR number of the query itselt is just 3, which were reading the segment head to get data block number of the table.
--- Fuyuncat ---