In this blog I’ll show a bunch of direct read and smart scan test cases. I’m doing this mostly because of this – the “_small_table_threshold” * 5 formula actually hasn’t quite worked out as expected based on what all the blogs, books, and documentation say. Hopefully these cases can stand on their own, but if anyone finds anything “off” with them let me know …
And before getting started, a couple of great references:
#######################
Test Query
#######################
APPS @ visx1> @exq.sql
APPS @ visx1> select count(*) from
2 (select code_combination_id,
3 sum(entered_dr), sum(entered_cr)
4 from xla_ae_lines
5 group by code_combination_id);
COUNT(*)
———-
5573
1 row selected.
APPS @ visx1> set echo off
PLAN_TABLE_OUTPUT
————————————————————————————–
SQL_ID ct0t92t7f0a99, child number 0
————————————-
select count(*) from (select code_combination_id, sum(entered_dr),
sum(entered_cr) from xla_ae_lines group by code_combination_id)
Plan hash value: 3427813926
—————————————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
—————————————————————————————————————
| 0 | SELECT STATEMENT | | | | 4245 (100)| | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | VIEW | | 4542 | | 4245 (29)| 00:00:01 | | |
| 3 | HASH GROUP BY | | 4542 | 49962 | 4245 (29)| 00:00:01 | | |
| 4 | PARTITION LIST ALL | | 6645K| 69M| 3369 (10)| 00:00:01 | 1 | 22 |
| 5 | PARTITION RANGE ALL | | 6645K| 69M| 3369 (10)| 00:00:01 | 1 | LAST |
| 6 | TABLE ACCESS STORAGE FULL| XLA_AE_LINES | 6645K| 69M| 3369 (10)| 00:00:01 | 1 | 610 |
—————————————————————————————————————
#######################
Smart Scan Stats
#######################
NAME VALUE
——————————————————-
cell physical IO interconnect bytes 111,206,400
cell flash cache read hits 1,312
CPU used by this session 237
#######################
Smart Scan Efficiency *
#######################
no rows selected
#######################
Waits
#######################
STAT TIME_WAITED TOTAL_WAITS
————————– ———– ————
events in waitclass Other 200 5
cell multiblock physical read 97 751
cell single block physical read 44 904
SQL*Net message from client 6 26
gc cr multi block request 6 238
gc cr grant 2-way 3 394
row cache lock 3 893
#######################
Test Query
#######################
APPS @ visx1> alter session set “_serial_direct_read”=true;
Session altered.
APPS @ visx1> @exq.sql
APPS @ visx1> select count(*) from
2 (select code_combination_id,
3 sum(entered_dr), sum(entered_cr)
4 from xla_ae_lines
5 group by code_combination_id);
COUNT(*)
———-
5573
1 row selected.
APPS @ visx1> set echo off
PLAN_TABLE_OUTPUT
————————————————————————————–
SQL_ID ct0t92t7f0a99, child number 0
————————————-
select count(*) from (select code_combination_id, sum(entered_dr),
sum(entered_cr) from xla_ae_lines group by code_combination_id)
Plan hash value: 3427813926
—————————————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
—————————————————————————————————————
| 0 | SELECT STATEMENT | | | | 4245 (100)| | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | VIEW | | 4542 | | 4245 (29)| 00:00:01 | | |
| 3 | HASH GROUP BY | | 4542 | 49962 | 4245 (29)| 00:00:01 | | |
| 4 | PARTITION LIST ALL | | 6645K| 69M| 3369 (10)| 00:00:01 | 1 | 22 |
| 5 | PARTITION RANGE ALL | | 6645K| 69M| 3369 (10)| 00:00:01 | 1 | LAST |
| 6 | TABLE ACCESS STORAGE FULL | XLA_AE_LINES | 6645K| 69M| 3369 (10)| 00:00:01 | 1 | 610 |
—————————————————————————————————————
#######################
Smart Scan Stats
#######################
NAME VALUE
———————————————————————- ——————–
cell physical IO bytes eligible for predicate offload 87,965,696
cell physical IO interconnect bytes 74,826,560
cell physical IO interconnect bytes returned by smart scan 67,756,864
cell flash cache read hits 832
CPU used by this session 196
#######################
Smart Scan Efficiency *
#######################
Eligible MB SS Returned MB Pct Saved
——————– ——————– ———
84 71 14.93
#######################
Waits
#######################
STAT TIME_WAITED TOTAL_WAITS
———————————– —————- —————-
SQL*Net message from client 49 28
cell single block physical read 42 835
cell smart table scan 27 445
events in waitclass Other 24 72
row cache lock 5 873
gc cr grant 2-way 3 357
enq: KO – fast object checkpoint 1 134
#######################
Test Query
#######################
APPS @ visx1> @exq.sql
APPS @ visx1> select count(*) from
2 (select /*+ parallel (8) */ code_combination_id,
3 sum(entered_dr), sum(entered_cr)
4 from xla_ae_lines
5 group by code_combination_id);
COUNT(*)
———-
5573
APPS @ visx1> set echo off
PLAN_TABLE_OUTPUT
————————————————————————————–
SQL_ID cmpbrw3vw826m, child number 0
————————————-
select count(*) from (select /*+ parallel (8) */ code_combination_id,
sum(entered_dr), sum(entered_cr) from xla_ae_lines group by
code_combination_id)
Plan hash value: 4167058233
————————————————————————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
————————————————————————————————————————————————-
| 0 | SELECT STATEMENT | | | | 573 (100)| | | | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 1 | | | | | | Q1,01 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | | | | Q1,01 | PCWP | |
| 5 | VIEW | | 4542 | | 573 (27)| 00:00:01 | | | Q1,01 | PCWP | |
| 6 | HASH GROUP BY | | 4542 | 49962 | 573 (27)| 00:00:01 | | | Q1,01 | PCWP | |
| 7 | PX RECEIVE | | 4542 | 49962 | 573 (27)| 00:00:01 | | | Q1,01 | PCWP | |
| 8 | PX SEND HASH | :TQ10000 | 4542 | 49962 | 573 (27)| 00:00:01 | | | Q1,00 | P->P | HASH |
| 9 | HASH GROUP BY | | 4542 | 49962 | 573 (27)| 00:00:01 | | | Q1,00 | PCWP | |
| 10 | PX PARTITION RANGE ALL | | 6645K| 69M | 467 (10)| 00:00:01 | 1 | LAST | Q1,00 | PCWC | |
| 11 | TABLE ACCESS STORAGE FULL| XLA_AE_LINES | 6645K| 69M | 467 (10)| 00:00:01 | 1 | 610 | Q1,00 | PCWP | |
————————————————————————————————————————————————-
Note
—–
– Degree of Parallelism is 8 because of hint
#######################
Smart Scan Stats
#######################
NAME VALUE
————————————————- ———-
cell physical IO interconnect bytes 93,569,024
cell flash cache read hits 1,053
CPU used by this session 283
#######################
Smart Scan Efficiency *
#######################
no rows selected
#######################
Waits
#######################
STAT TIME_WAITED TOTAL_WAITS
———————————– —————- —————-
events in waitclass Other 352 70
SQL*Net message from client 107 26
PX Deq: Execute Reply 55 640
cell single block physical read 29 589
row cache lock 2 706
gc cr grant 2-way 2 237
PX Deq: Parse Reply 2 16
cell multiblock physical read 1 5
PX Deq: Join ACK 1 16
1 select a.ksppinm name, b.ksppstvl value, b.ksppstdf isdefault
2 from x$ksppi a, x$ksppsv b
3 where a.indx = b.indx
4 and a.ksppinm in (‘parallel_degree_policy’,’_parallel_statement_queuing’,
5 ‘_parallel_cluster_cache_policy’)
6 –and b.ksppstdf=’FALSE’
7* order by 1,2
SYS @ visx1> /
_parallel_cluster_cache_policy ADAPTIVE TRUE
_parallel_statement_queuing FALSE FALSE
parallel_degree_policy MANUAL FALSE
APPS @ visx1> select a.ksppinm name, b.ksppstvl value, b.ksppstdf isdefault
2 from x$ksppi a, x$ksppsv b
3 where a.indx = b.indx
4 and 1=1
5 and a.ksppinm like ‘%’||’&parm’||’%’
6 –and b.ksppstdf=’FALSE’
7 order by 1,2
8 /
Enter value for parm: small_table
old 5: and a.ksppinm like ‘%’||’&parm’||’%’
new 5: and a.ksppinm like ‘%’||’small_table’||’%’
_small_table_threshold 27284 TRUE
APPS @ visx1> select 5 * 27284
2 from dual;
136420
1 row selected.
SYS @ visx1> select blocks from dba_tables
2 where table_name=’XLA_AE_LINES’;
BLOCKS
———-
11225
1 row selected.
SYS @ visx1>
SYS @ visx1> select sum(blocks) from dba_tab_partitions
2 where table_name=’XLA_AE_LINES’;
SUM(BLOCKS)
———–
11225
1 row selected.
SYS @ visx1>
SYS @ visx1> select sum(blocks) from dba_tab_subpartitions
2 where table_name=’XLA_AE_LINES’;
SUM(BLOCKS)
———–
11225
1 row selected.
APPS @ visx1> select count(*) from xla_ae_lines;
COUNT(*)
———-
6638073
652 subpartition psb_2011 values less than (to_date(’01-JAN-2012′,’dd-MON-yyyy’)),
653 subpartition psb_max values less than (maxvalue)
654 ), partition partother values (default))
655 tablespace apps_ts_tx_data nologging
656 as
657 select * from xla.xla_ae_lines
658 /
Table created.
Elapsed: 00:01:01.41
SQL>
1 create table xla.xla_ae_lines_unpart
2 nologging
3* as select * from xla_ae_lines
SQL> /
Table created.
Elapsed: 00:00:21.74
SQL> select count(*) from xla.xla_ae_lines_unpart;
COUNT(*)
———-
6638073
Elapsed: 00:00:00.94
SQL> select count(*) from xla.xla_ae_lines_part;
COUNT(*)
———-
6638073
SQL> select count(*) from xla.xla_ae_lines_part;
COUNT(*)
———-
106209168
Elapsed: 00:00:10.70
SQL> select count(*) from xla.xla_ae_lines_unpart;
COUNT(*)
———-
106209168
#######################
Test Query
#######################
APPS @ visx1> @exq.sql
APPS @ visx1> select count(*) from
2 (select /*+ parallel (8) */
3 code_combination_id,
4 sum(entered_dr), sum(entered_cr)
5 from xla.xla_ae_lines_part
6 group by code_combination_id
7 );
COUNT(*)
———-
5573
APPS @ visx1> set echo off
(dbms_xplan.display_cursor(‘&_sqlid’,&_cnum,’TYPICAL’))
new 1: select * from table (dbms_xplan.display_cursor(‘a7zk1u1w63w00′, 0,’TYPICAL’))
PLAN_TABLE_OUTPUT
——————————————————————————————————————————————————————————————————–
SQL_ID a7zk1u1w63w00, child number 0
————————————-
select count(*) from (select /*+ parallel (8) */ code_combination_id,
sum(entered_dr), sum(entered_cr) from xla.xla_ae_lines_part group by
code_combination_id )
Plan hash value: 2859844103
——————————————————————————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
——————————————————————————————————————————————————
| 0 | SELECT STATEMENT | | | | 9070 (100)| | | | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 1 | | | | | | Q1,01 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | | | | Q1,01 | PCWP | |
| 5 | VIEW | | 5573 | | 9070 (30)| 00:00:01 | | | Q1,01 | PCWP | |
| 6 | HASH GROUP BY | | 5573 | 61303 | 9070 (30)| 00:00:01 | | | Q1,01 | PCWP | |
| 7 | PX RECEIVE | | 5573 | 61303 | 9070 (30)| 00:00:01 | | | Q1,01 | PCWP | |
| 8 | PX SEND HASH | :TQ10000 | 5573 | 61303 | 9070 (30)| 00:00:01 | | | Q1,00 | P->P | HASH |
| 9 | HASH GROUP BY | | 5573 | 61303 | 9070 (30)| 00:00:01 | | | Q1,00 | PCWP | |
| 10 | PX PARTITION RANGE ALL | | 106M| 1114M| 7073 (10)| 00:00:01 | 1 | LAST | Q1,00 | PCWC | |
| 11 | TABLE ACCESS STORAGE FULL| XLA_AE_LINES_PART | 106M| 1114M| 7073 (10)| 00:00:01 | 1 | 610 | Q1,00 | PCWP | |
——————————————————————————————————————————————————
Note
—–
– Degree of Parallelism is 8 because of hint
#######################
Smart Scan Stats
#######################
NAME VALUE
———————————————————————- ——————–
cell physical IO bytes eligible for predicate offload 1,366,065,152
cell physical IO interconnect bytes 1,088,400,208
cell physical IO interconnect bytes returned by smart scan 1,082,813,264
physical reads 167,438
physical reads direct 166,756
CPU used by this session 2,269
cell flash cache read hits 647
#######################
Smart Scan Efficiency *
#######################
Eligible MB SS Returned MB Pct Saved
——————– ——————– ———
1,303 1,038 20.33
#######################
Waits
#######################
STAT TIME_WAITED TOTAL_WAITS
———————————– ———————-
PX Deq: Execute Reply 497 645
SQL*Net message from client 73 27
events in waitclass Other 50 61
cell single block physical read 36 567
cursor: pin S wait on X 2 1
PX Deq: Parse Reply 2 16
gc cr grant 2-way 2 216
row cache lock 2 578
cell multiblock physical read 1 5
cell list of blocks physical read 1 1
#######################
Test Query
#######################
APPS @ visx1> @exq.sql
APPS @ visx1> select count(*) from
2 (select /*+ parallel (8) */
3 code_combination_id,
4 sum(entered_dr), sum(entered_cr)
5 from xla.xla_ae_lines_unpart
6 group by code_combination_id
7 );
COUNT(*)
———-
5573
APPS @ visx1> set echo off
old 1: select * from table (dbms_xplan.display_cursor(‘&_sqlid’,&_cnum,’TYPICAL’))
new 1: select * from table (dbms_xplan.display_cursor(‘8k1wkzavyambg’, 0,’TYPICAL’))
PLAN_TABLE_OUTPUT
——————————————————————————————————————————————————————————————————–
SQL_ID 8k1wkzavyambg, child number 0
————————————-
select count(*) from (select /*+ parallel (8) */ code_combination_id,
sum(entered_dr), sum(entered_cr) from xla.xla_ae_lines_unpart group by
code_combination_id )
Plan hash value: 1164237767
—————————————————————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
—————————————————————————————————————————————-
| 0 | SELECT STATEMENT | | | | 108K(100)| | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 1 | | | | Q1,01 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | | Q1,01 | PCWP | |
| 5 | VIEW | | 5573 | | 108K (3)| 00:00:05 | Q1,01 | PCWP | |
| 6 | HASH GROUP BY | | 5573 | 61303 | 108K (3)| 00:00:05 | Q1,01 | PCWP | |
| 7 | PX RECEIVE | | 5573 | 61303 | 108K (3)| 00:00:05 | Q1,01 | PCWP | |
| 8 | PX SEND HASH | :TQ10000 | 5573 | 61303 | 108K (3)| 00:00:05 | Q1,00 | P->P | HASH |
| 9 | HASH GROUP BY | | 5573 | 61303 | 108K (3)| 00:00:05 | Q1,00 | PCWP | |
| 10 | PX BLOCK ITERATOR | | 106M| 1114M| 106K (1)| 00:00:05 | Q1,00 | PCWC | |
|* 11 | TABLE ACCESS STORAGE FULL| XLA_AE_LINES_UNPART | 106M| 1114M| 106K (1)| 00:00:05 | Q1,00 | PCWP | |
—————————————————————————————————————————————-
Predicate Information (identified by operation id):
—————————————————
11 – storage(:Z>=:Z AND :Z<=:Z)
Note
—–
– Degree of Parallelism is 8 because of hint
#######################
Smart Scan Stats
#######################
NAME VALUE
———————————————————————- ——————–
cell physical IO bytes eligible for predicate offload 23,030,530,048
cell physical IO interconnect bytes 1,873,421,824
cell physical IO interconnect bytes returned by smart scan 1,867,032,064
physical reads 2,812,124
physical reads direct 2,811,344
CPU used by this session 2,482
cell flash cache read hits 749
#######################
Smart Scan Efficiency *
#######################
Eligible MB SS Returned MB Pct Saved
——————– ——————– ———
21,964 1,787 91.87
1 row selected.
#######################
Waits
#######################
STAT TIME_WAITED TOTAL_WAITS
———————————– —————- —————-
PX Deq: Execute Reply 579 123
SQL*Net message from client 313 27
events in waitclass Other 43 57
cell single block physical read 38 751
row cache lock 2 299
gc cr grant 2-way 2 187
PX Deq: Parse Reply 1 16
cell multiblock physical read 1 5
select
IO_CELL_OFFLOAD_ELIGIBLE_BYTES/1024/1024 val1,
IO_INTERCONNECT_BYTES/10241/024 val2,
round(100*(1-(IO_INTERCONNECT_BYTES/IO_CELL_OFFLOAD_ELIGIBLE_BYTES)),2) efficiency
from v$sql
where sql_id=’&_sqlid’
APPS @ visx1> show sga
Total System Global Area 8551575552 bytes
Fixed Size 2245480 bytes
Variable Size 4412411032 bytes
Database Buffers 3992977408 bytes
Redo Buffers 143941632 bytes
APPS @ visx1> @sysparm
APPS @ visx1> select a.ksppinm name, b.ksppstvl value, b.ksppstdf isdefault
2 from x$ksppi a, x$ksppsv b
3 where a.indx = b.indx
4 and 1=1
5 and a.ksppinm like ‘%’||’&parm’||’%’
6 –and b.ksppstdf=’FALSE’
7 order by 1,2
8 /
Enter value for parm: small_table
old 5: and a.ksppinm like ‘%’||’&parm’||’%’
new 5: and a.ksppinm like ‘%’||’small_table’||’%’
_small_table_threshold 9062 TRUE
APPS @ visx1> select a.ksppinm name, b.ksppstvl value, b.ksppstdf isdefault
2 from x$ksppi a, x$ksppsv b
3 where a.indx = b.indx
4 and a.ksppinm like ‘%’||’&parm’||’%’
5 and b.ksppstdf=’FALSE’
6 order by 1,2
7 /
__db_cache_size 3976200192 FALSE
__java_pool_size 16777216 FALSE
__large_pool_size 117440512 FALSE
__pga_aggregate_target 8589934592 FALSE
__sga_target 8589934592 FALSE
__shared_io_pool_size 0 FALSE
__shared_pool_size 4294967296 FALSE
__streams_pool_size 0 FALSE
_file_size_increase_increment 143289344 FALSE
_kill_diagnostics_timeout 140 FALSE
_lm_rcvr_hang_allow_time 140 FALSE
_optimizer_ignore_hints FALSE FALSE
_parallel_statement_queuing FALSE FALSE
_serial_direct_read AUTO FALSE
cluster_database TRUE FALSE
cluster_interconnects 192.168.10.1 FALSE
compatible 11.2.0.3.0 FALSE
db_block_size 8192 FALSE
filesystemio_options SETALL FALSE
instance_number 1 FALSE
open_cursors 1000 FALSE
parallel_adaptive_multi_user FALSE FALSE
parallel_degree_policy MANUAL FALSE
parallel_execution_message_size 16384 FALSE
parallel_max_servers 128 FALSE
parallel_min_servers 32 FALSE
pga_aggregate_target 8589934592 FALSE
pre_page_sga FALSE FALSE
processes 1024 FALSE
recyclebin OFF FALSE
sessions 1560 FALSE
sga_max_size 8589934592 FALSE
sga_target 8589934592 FALSE
shared_pool_size 4294967296 FALSE
sql92_security TRUE FALSE
SYS @ visx1> create tablespace dr_mssm
2 datafile ‘+DATA_CM01’ size 64m segment space management manual
3 /
Tablespace created.
Elapsed: 00:00:00.52
SYS @ visx1
1 create tablespace dr_assm
2* datafile ‘+DATA_CM01’ size 64m segment space management auto
SYS @ visx1>
SYS @ visx1> /
Tablespace created.
Elapsed: 00:00:00.35
APPS @ visx1> create table t_mssm
2 (col1 varchar2(100))
3 pctused 1
4 pctfree 99
5 tablespace dr_mssm;
Table created.
1 create table t_assm
2 (col1 varchar2(100))
3* tablespace dr_assm
APPS @ visx1> /
Table created..02
APPS @ visx1>
APPS @ visx1> create or replace function calc_dr_mssm return number is
2 l_prd number;
3 l_blocks number:=0;
4 l_cnt number:=0;
5 begin
6 execute immediate ‘truncate table t_mssm’;
7 loop
8 insert /*+ append */ into t_mssm
9 select rpad(‘*’, 100, ‘*’)
10 from dual;
11 commit;
12 l_blocks:=l_blocks + 1;
13 execute immediate ‘alter system flush buffer_cache’;
14 select /*+ full(t) */ count(*) into l_cnt from t_mssm;
15 select value into l_prd
16 from v$segment_statistics
17 where owner=user
18 and object_name=’T_MSSM’
19 and statistic_name=’physical reads direct’;
20 exit when l_prd > 0;
21 end loop;
22 return l_blocks;
23 end;
24 /
Function created.
APPS @ visx1> declare
2 l_th number;
3 begin
4 l_th:=calc_dr_mssm;
5 dbms_output.put_line(‘Started doing direct reads at: ‘||l_th||’ blocks’);
6 end;
7 /
Started doing direct reads at: 9063 blocks
PL/SQL procedure successfully completed.
APPS @ visx1> select sql_id,io_cell_offload_eligible_bytes
2 from v$sql
3 where sql_id=’960bzw5qrmp86′;
SQL_ID IO_CELL_OFFLOAD_ELIGIBLE_BYTES
————- ——————————
960bzw5qrmp86 74244096
1 row selected.
Elapsed: 00:00:00.01
APPS @ visx1> select bytes, blocks
2 from dba_segments
3 where segment_name=’T_MSSM’;
BYTES BLOCKS
———- ———-
75497472 9216
SQL> show sga
Total System Global Area 1068937216 bytes
Fixed Size 2233336 bytes
Variable Size 704646152 bytes
Database Buffers 348127232 bytes
Redo Buffers 13930496 bytes
SQL> @sysparm
SQL> select a.ksppinm name, b.ksppstvl value, b.ksppstdf isdefault
2 from x$ksppi a, x$ksppsv b
3 where a.indx = b.indx
4 and 1=1
5 and a.ksppinm like ‘%’||’&parm’||’%’
6 –and b.ksppstdf=’FALSE’
7 order by 1,2
8 /
Enter value for parm: small_table
old 5: and a.ksppinm like ‘%’||’&parm’||’%’
new 5: and a.ksppinm like ‘%’||’small_table’||’%’
APPS @ visx1> create or replace function calc_dr_assm return number is
2 l_prd number;
3 l_blocks number:=0;
4 l_cnt number:=0;
5 begin
6 execute immediate ‘truncate table t_assm’;
7 loop
8 insert /*+ append */ into t_assm
9 select rpad(‘*’, 100, ‘*’)
10 from dual;
11 commit;
12 l_blocks:=l_blocks + 1;
13 execute immediate ‘alter system flush buffer_cache’;
14 select /*+ full(t) */ count(*) into l_cnt from t_assm;
15 select value into l_prd
16 from v$segment_statistics
17 where owner=user
18 and object_name=’T_ASSM’
19 and statistic_name=’physical reads direct’;
20 exit when l_prd > 0;
21 end loop;
22 return l_blocks;
23 end;
24 /
Function created.
APPS @ visx1> declare
2 l_th number;
3 begin
4 l_th:=calc_dr_assm;
5 dbms_output.put_line(‘Started doing direct reads at: ‘||l_th||’ inserts’);
6 end;
7 /
Started doing direct reads at: 8924 inserts
APPS @ visx1> select blocks
2 from dba_segments
3 where segment_name=’T_ASSM’;
BLOCKS
———-
9216
1 row selected.
Elapsed: 00:00:02.54
APPS @ visx1> select count(*) from t_assm;
COUNT(*)
———-
8924
APPS @ visx1> create or replace function calc_dr_assm return number is
2 l_prd number;
3 l_blocks number:=0;
4 l_cnt number:=0;
5 begin
6 execute immediate ‘truncate table t_assm’;
7 loop
8 insert into t_assm
9 select rpad(‘*’, 100, ‘*’)
10 from dual connect by level<=500;
11 commit;
12 l_blocks:=l_blocks + 1;
13 execute immediate ‘alter system flush buffer_cache’;
14 select /*+ full(t) */ count(*) into l_cnt from t_assm;
15 select value into l_prd
16 from v$segment_statistics
17 where owner=user
18 and object_name=’T_ASSM’
19 and statistic_name=’physical reads direct’;
20 exit when l_prd > 0;
21 end loop;
22 return l_blocks;
23 end;
24 /
Function created.
APPS @ visx1> declare
2 l_th number;
3 begin
4 l_th:=calc_dr_assm;
5 dbms_output.put_line(‘Started doing direct reads at: ‘||l_th||’ inserts’);
6 end;
7 /
Started doing direct reads at: 1096 inserts
APPS @ visx1> exec dbms_stats.gather_table_stats(‘APPS’,’T_ASSM’);
APPS @ visx1> l
1 select num_rows,blocks,num_rows/blocks
2 from dba_tables
3* where table_name=’T_ASSM’
APPS @ visx1> /
548000 9077 60.3723697
APPS @ visx1> select table_name,num_rows,blocks,num_rows/blocks
2 from dba_tables
3 where table_name in (‘T_MSSM’,’T_ASSM’);
T_MSSM 9063 9063 1
T_ASSM 548000 9077 60.3723697
APPS @ visx1> create index i_t_mssm
2 on t_mssm (1);
Index created.
Elapsed: 00:00:00.11
APPS @ visx1> create index i_t_assm
2 on t_assm (1);
Index created.
APPS @ visx1> create or replace function calc_cached_mssm
2 return number is
3 cursor l_cur is select /*+ index(t_mssm i_t_mssm) */ * from t_mssm;
4 l_v varchar2(100);
5 l_execs number:=0;
6 l_cnt number:=0;
7 l_prv number:=0;
8 begin
9 execute immediate ‘alter system flush buffer_cache’;
10 open l_cur;
11 loop
12 fetch l_cur into l_v;
13 l_execs:=l_execs+1;
14 select /*+ full(t) */ count(*) into l_cnt from t_mssm t;
15 select value into l_cnt
16 from v$segment_statistics
17 where owner=user
18 and object_name=’T_MSSM’
19 and statistic_name=’physical reads direct’;
20 exit when l_cnt = l_prv or l_cur%notfound;
21 l_prv:=l_cnt;
22 end loop;
23 close l_cur;
24 return l_execs;
25 end;
26 /
Function created.
APPS @ visx1> declare
2 l_th number;
3 begin
4 l_th:=calc_cached_mssm;
5 dbms_output.put_line(‘Stopped doing direct reads at: ‘||l_th||’. ‘);
6
7 end;
8 /
Stopped doing direct reads at: 8772.
APPS @ visx1> declare
2 l_th number;
3 begin
4 l_th:=calc_cached_assm;
5 dbms_output.put_line(‘Stopped doing direct reads at: ‘||l_th||’. ‘);
6
7 end;
8 /
Stopped doing direct reads at: 548001.
SQL> create or replace function calc_cached_assm_t2
2 return number is
3 cursor l_cur is select /*+ index(t_assm i_t_assm) */ * from t_assm;
4 l_v varchar2(100);
5 l_execs number:=0;
6 l_cnt number:=0;
7 l_prv number:=0;
8 begin
9 execute immediate ‘alter system flush buffer_cache’;
10 open l_cur;
11 loop
12 fetch l_cur into l_v;
13 l_execs:=l_execs+1;
14 exit when l_cur%notfound;
15 end loop;
16 close l_cur;
17 return l_execs;
18 end;
19 /
SQL> set serveroutput on size 20000
SQL> alter session set events ‘10046 trace name context forever, level 12’;
Session altered.
SQL> set echo on
SQL> declare
2 l_th number;
3 begin
4 l_th:=calc_cached_assm_t2;
5 dbms_output.put_line(‘Finished reads at: ‘||l_th||’. ‘);
6 end;
7 /
Finished reads at: 548001.
PL/SQL procedure successfully completed.
SQL> alter session set events ‘10046 trace name context off’;
Session altered.
SELECT /*+ index(t_assm i_t_assm) */ *
FROM
T_ASSM
call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 548001 3.14 7.30 9132 1096002 0 548000
——- —— ——– ———- ———- ———- ———- ———-
total 548003 3.14 7.31 9132 1096002 0 548000
SQL> set autotrace on
SQL> select count(*) from t_assm;
COUNT(*)
———-
548000
Execution Plan
———————————————————-
Plan hash value: 2636023310
———————————————————————————-
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
———————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 301 (3)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX STORAGE FAST FULL SCAN| I_T_ASSM | 548K| 301 (3)| 00:00:01 |
———————————————————————————-
Statistics
———————————————————-
0 recursive calls
0 db block gets
1091 consistent gets
0 physical reads
0 redo size
527 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set autotrace off
SQL> @../sqlmon/mywaits
STAT TIME_WAITED TOTAL_WAITS
———————————– —————- —————-
SQL*Net message from client 10,538 37
Disk file operations I/O 0 70
SQL*Net message to client 0 38
events in waitclass Other 0 2
SQL> create or replace function calc_dirty_mssm
2 return number is
3 l_v varchar2(100);
4 l_execs number:=0;
5 l_cnt number:=0;
6 l_prv number:=0;
7 begin
8 execute immediate ‘alter system flush buffer_cache’;
9 loop
10 update t_mssm set col1=col1 where rownum < l_execs;
11 commit;
12 l_execs:=l_execs+1;
13 select /*+ full(t) */ count(*) into l_cnt from t_mssm t;
14 select value into l_cnt
15 from v$segment_statistics
16 where owner=user
17 and object_name=’T_MSSM’
18 and statistic_name=’physical reads direct’;
19 exit when l_cnt = l_prv or l_execs > 9063;
20 l_prv:=l_cnt;
21 end loop;
22 return l_execs;
23 end;
24 /
Function created.
SQL> @mssm_test3
SQL> set serveroutput on size 20000
SQL> set echo on
SQL> declare
2 l_th number;
3 begin
4 l_th:=calc_dirty_mssm;
5 dbms_output.put_line(‘Stopped doing direct reads at: ‘||l_th||’. ‘);
6 end;
7 /
Stopped doing direct reads at: 3888.
PL/SQL procedure successfully completed.
SQL>
SQL> select stat,sum(time_waited) time_waited,
2 sum(total_waits) total_waits
3 from
4 (select c.event stat
5 ,c.time_waited,c.total_waits,
6 b.sid,b.serial#,nvl(b.module,b.program) what
7 from v$session b, v$session_event c
8 where c.sid=b.sid
9 and b.sid=userenv(‘sid’))
10 group by stat
11 having sum(time_waited) > 0
12 order by 2 desc
13 /
STAT TIME_WAITED TOTAL_WAITS
—————————————- —————- —————-
SQL*Net message from client 15,914 26
enq: KO – fast object checkpoint 12,754 7,759
cell smart table scan 9,697 160,595
events in waitclass Other 535 3,909
log file switch (checkpoint incomplete) 88 21
cell single block physical read 45 232
cell multiblock physical read 22 86
log file switch completion 4 1
gc cr multi block request 4 104
log buffer space 2 3
SQL> create or replace function calc_dirty_assm
2 return number is
3 l_v varchar2(100);
4 l_execs number:=0;
5 l_bs number:=100;
6 l_cnt number:=0;
7 l_prv number:=0;
8 begin
9 execute immediate ‘alter system flush buffer_cache’;
10 loop
11 update t_assm set col1=col1 where rownum < l_execs+l_bs;
12 commit;
13 l_execs:=l_execs+l_bs;
14 select /*+ full(t) */ count(*) into l_cnt from t_assm t;
15 select value into l_cnt
16 from v$segment_statistics
17 where owner=user
18 and object_name=’T_ASSM’
19 and statistic_name=’physical reads direct’;
20 exit when l_cnt = l_prv or l_execs > 548000;
21 l_prv:=l_cnt;
22 end loop;
23 return l_execs;
24 end;
25 /
Function created.
SQL> declare
2 l_th number;
3 begin
4 l_th:=calc_dirty_assm;
5 dbms_output.put_line(‘Stopped doing direct reads at: ‘||l_th||’. ‘);
6 end;
7 /
Stopped doing direct reads at: 255600.
PL/SQL procedure successfully completed.
APPS @ visx1> create table oel_test
2 tablespace apps_ts_tx_data
3 as select * from ont.oe_order_lines_all
4 where 1=2;
Table created.
Elapsed: 00:00:00.29
APPS @ visx1>
APPS @ visx1> create or replace function calc_dr_apps return number is
2 l_prd number;
3 l_blocks number:=0;
4 l_cnt number:=0;
5 begin
6 execute immediate ‘truncate table oel_test’;
7 loop
8 insert into oel_test
9 select * from
10 (select * from oe_order_lines_all
11 where line_id=388282)
12 connect by level<=500;
13 commit;
14 l_blocks:=l_blocks + 1;
15 execute immediate ‘alter system flush buffer_cache’;
16 select /*+ full(t) */ count(*) into l_cnt from oel_test;
17 select value into l_prd
18 from v$segment_statistics
19 where owner=user
20 and object_name=’OEL_TEST’
21 and statistic_name=’physical reads direct’;
22 exit when l_prd > 0;
23 end loop;
24 return l_blocks;
25 end;
26 /
Function created.
SQL> declare
2 l_th number;
3 begin
4 l_th:=calc_dr_apps;
5 dbms_output.put_line(‘Started doing direct reads at: ‘||l_th||’ inserts’);
6
7 end;
8 /
Started doing direct reads at: 180 inserts
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(‘APPS’,’OEL_TEST’);
PL/SQL procedure successfully completed.
SQL> select blocks, num_rows, num_rows/blocks
2 from dba_tables
3 where table_name=’OEL_TEST’;
9012 90000 9.98668442
SQL> create index i_oel_test on oel_test (1);
Index created.
SQL> exec dbms_stats.gather_table_stats(‘APPS’,’OEL_TEST’);
PL/SQL procedure successfully completed.
SQL> create or replace function calc_cached_apps
2 return number is
3 cursor l_cur is select /*+ index(oel_test i_oel_test) */ * from oel_test;
4 l_v oel_test%rowtype;
5 l_execs number:=0;
6 l_cnt number:=0;
7 l_cnt2 number:=0;
8 l_prv number:=0;
9 begin
10 execute immediate ‘alter system flush buffer_cache’;
11 open l_cur;
12 loop
13 fetch l_cur into l_v;
14 l_execs:=l_execs+1;
15 select /*+ full(t) */ count(*) into l_cnt from oel_test t;
16 select value into l_cnt
17 from v$segment_statistics
18 where owner=user
19 and object_name=’OEL_TEST’
20 and statistic_name=’physical reads direct’;
21 exit when l_cnt = l_prv or l_cur%notfound;
22 l_prv:=l_cnt;
23 end loop;
24 close l_cur;
25 return l_execs;
26 end;
27 /
Function created.
APPS @ visx1> declare
2 l_th number;
3 begin
4 l_th:=calc_cached_assm;
5 dbms_output.put_line(‘Stopped doing direct reads at: ‘||l_th||’. ‘);
6
7 end;
8 /
Stopped doing direct reads at: 89421.
SQL> create or replace function calc_dirty_apps
2 return number is
3 l_v varchar2(100);
4 l_execs number:=0;
5 l_bs number:=100;
6 l_cnt number:=0;
7 l_prv number:=0;
8 begin
9 execute immediate ‘alter system flush buffer_cache’;
10 loop
11 update oel_test set line_number=line_number,
12 open_flag=open_flag
13 where rownum < l_execs+l_bs;
14 commit;
15 l_execs:=l_execs+l_bs;
16 select /*+ full(t) */ count(*) into l_cnt from oel_test t;
17 select value into l_cnt
18 from v$segment_statistics
19 where owner=user
20 and object_name=’OEL_TEST’
21 and statistic_name=’physical reads direct’;
22 exit when l_cnt = l_prv or l_execs > 90000;
23 l_prv:=l_cnt;
24 end loop;
25 return l_execs;
26 end;
27 /
Function created.
SQL> declare
2 l_th number;
3 begin
4 l_th:=calc_dirty_apps;
5 dbms_output.put_line(‘Stopped doing direct reads at: ‘||l_th||’. ‘);
6 end;
7 /
Stopped doing direct reads at: 39800.
PL/SQL procedure successfully completed.
SQL> alter system reset “_serial_direct_read” sid=’visx1′;
System altered.
SQL> ed
Wrote file afiedt.buf
1* alter system reset “_serial_direct_read” sid=’visx2′
SQL> /
System altered.
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
[[email protected] direct_reads]$ srvctl stop database -d visx
[[email protected] direct_reads]$ srvctl start database -d visx
[[email protected] direct_reads]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sat Mar 10 12:04:21 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> @../sqlmon/sysparm
SQL> select a.ksppinm name, b.ksppstvl value, b.ksppstdf isdefault
2 from x$ksppi a, x$ksppsv b
3 where a.indx = b.indx
4 and a.ksppinm like ‘%’||’&parm’||’%’
5 and b.ksppstdf=’FALSE’
6 order by 1,2
7 /
Enter value for parm: serial_dir
old 4: and a.ksppinm like ‘%’||’&parm’||’%’
new 4: and a.ksppinm like ‘%’||’serial_dir’||’%’
no rows selected
APPS @ visx1> declare
2 l_th number;
3 begin
4 l_th:=calc_dr_mssm;
5 dbms_output.put_line(‘Started doing direct reads at: ‘||l_th||’ blocks’);
6 end;
7 /
Started doing direct reads at: 8950 blocks
PL/SQL procedure successfully completed.
APPS @ visx1> create table xla_temp
2 as select * from xla_ae_lines
3 where 1=2;
select ‘insert into xla_temp select * from xla_ae_lines subpartition (‘||subpartition_name||’) where rownum=1;’
from dba_tab_subpartitions
where table_name=’XLA_AE_LINES’
/
APPS @ visx1> select count(*) from xla_temp;
67
1 row selected.
Elapsed: 00:00:00.00
APPS @ visx1> insert into xla_temp
2 select * from xla_temp;
67 rows created.
Elapsed: 00:00:00.01
APPS @ visx1> select count(*) from xla_temp;
134
1 row selected.
Elapsed: 00:00:00.00
APPS @ visx1> commit;
SQL> prompt Dropping existing partitioned table
Dropping existing partitioned table
SQL> drop table xla.xla_ae_lines_part;
Table dropped.
Elapsed: 00:00:00.45
SQL> prompt Creating partitioned table
Creating partitioned table
SQL>
SQL> CREATE TABLE XLA.XLA_AE_LINES_PART
2 PARTITION BY list (application_id)
3 subpartition by range (accounting_date)
4 (partition AP values (200)
5 (subpartition ap_1995 values less than (to_date(’01-JAN-1996′,’dd-MON-yyyy’)) compress for query high,
6 subpartition ap_1996 values less than (to_date(’01-JAN-1997′,’dd-MON-yyyy’)) compress for query high,
<< lines deleted >>
653 subpartition psb_max values less than (maxvalue)
654 ), partition partother values (default))
655 tablespace apps_ts_tx_data nologging
656 as
657 select * from xla.xla_ae_lines
658 where 1=2
659 /
Table created.
APPS @ visx1> create or replace function calc_pdr_xla return number is
2 l_prd number;
3 l_blocks number:=0;
4 l_cnt number:=0;
5 begin
6 execute immediate ‘truncate table xla.xla_ae_lines_part’;
7 loop
8 insert into xla.xla_ae_lines_part
9 select * from xla_temp;
10 commit;
11 l_blocks:=l_blocks + 1;
12 execute immediate ‘alter system flush buffer_cache’;
13 select /*+ full(t) parallel (8) */ count(*) into l_cnt from xla.xla_ae_lines_part t;
14 select sum(value) into l_prd
15 from v$segment_statistics
16 where owner=’XLA’
17 and object_name=’XLA_AE_LINES_PART’
18 and statistic_name=’physical reads direct’;
19 exit when l_prd > 0;
20 end loop;
21 return l_blocks;
22 end;
23 /
Function created.
APPS @ visx1> declare
2 l_th number;
3 begin
4 l_th:=calc_pdr_xla;
5 dbms_output.put_line(‘Started doing direct reads at: ‘||l_th||’ inserts’);
6 end;
7 /
Started doing direct reads at: 3790 inserts
PL/SQL procedure successfully completed.
APPS @ visx1> @b
SUM(BLOCKS)
———–
9616
APPS @ visx1> select count(*) from xla.xla_ae_lines_part;
COUNT(*)
———-
507860
1 select table_name,partition_name,blocks
2 from dba_tab_partitions
3* where table_name=’XLA_AE_LINES_PART’
APPS @ visx1> /
TABLE_NAME PARTITION_NAME BLOCKS
—————————— —————————— ———-
XLA_AE_LINES_PART AP 2363
XLA_AE_LINES_PART AR 933
XLA_AE_LINES_PART CE 0
XLA_AE_LINES_PART COREBANK 836
XLA_AE_LINES_PART CST 503
XLA_AE_LINES_PART DPP 0
XLA_AE_LINES_PART FUN 0
XLA_AE_LINES_PART FV 925
XLA_AE_LINES_PART GMF 134
XLA_AE_LINES_PART IGC 0
XLA_AE_LINES_PART IGI 0
XLA_AE_LINES_PART INSITBANK 73
XLA_AE_LINES_PART LNS 523
XLA_AE_LINES_PART OFA 972
XLA_AE_LINES_PART OKL 402
XLA_AE_LINES_PART OZF 0
XLA_AE_LINES_PART PA 770
XLA_AE_LINES_PART PARTOTHER 0
XLA_AE_LINES_PART PAY 0
XLA_AE_LINES_PART PN 0
XLA_AE_LINES_PART PO 568
XLA_AE_LINES_PART PSB 0
APPS @ visx1> select sum(value )
2 from v$segment_statistics
3 where owner=’XLA’
4 and object_name=’XLA_AE_LINES_PART’
5 and subobject_name like ‘AP%’
6 and statistic_name=’physical reads direct’
7 /
SUM(VALUE)
———-
4726
APPS @ visx1> select count(*) from xla.xla_ae_lines_part
2 partition (AP);
COUNT(*)
———-
98540
APPS @ visx1> select sum(value )
2 from v$segment_statistics
3 where owner=’XLA’
4 and object_name=’XLA_AE_LINES_PART’
5 and subobject_name like ‘AP%’
6 and statistic_name=’physical reads direct’
7 /
SUM(VALUE)
———-
4726
SQL> show sga
Total System Global Area 4275781632 bytes
Fixed Size 2226472 bytes
Variable Size 2650801880 bytes
Database Buffers 1610612736 bytes
Redo Buffers 12140544 bytes
SQL>
SQL> select a.ksppinm name, b.ksppstvl value, b.ksppstdf isdefault
2 from x$ksppi a, x$ksppsv b
3 where a.indx = b.indx
4 and a.ksppinm in (‘_small_table_threshold’,’_serial_direct_read’)
5 /
_small_table_threshold 3744 TRUE
_serial_direct_read auto TRUE
SQL> select owner,table_name,blocks,
2 (case when blocks > (3744*5) then ‘Y’
3 else ‘N’
4 end) dr
5 from dba_tables
6 where blocks > 3200
7 and table_name not like ‘MLOG%’
8 and owner in
9 (select oracle_username from applsys.fnd_oracle_userid)
10 /
Owner Table Blocks Expect DR
————— ———————————– ——— ———-
APPLSYS AD_FILES 7425 N
APPLSYS AD_FILE_VERSIONS 13123 N
APPLSYS AD_PATCH_COMMON_ACTIONS 13633 N
AK AK_REGION_ITEMS 6969 N
APPLSYS AD_PATCH_RUN_BUGS 3379 N
APPLSYS AD_PATCH_RUN_BUG_ACTIONS 10446 N
APPLSYS AD_SNAPSHOT_BUGFIXES 13696 N
ALR ALR_ALERT_CHECKS 3394 N
APPLSYS AD_SNAPSHOT_FILES 51416 Y
APPLSYS AD_TASK_TIMING 7929 N
APPLSYS FND_COLUMNS 11339 N
APPLSYS FND_CONCURRENT_REQUESTS 6181 N
APPLSYS FND_ENV_CONTEXT 10271 N
AR HZ_HIERARCHY_NODES 10191 N
AR HZ_RELATIONSHIPS 51098 Y
APPLSYS FND_LOBS 10558 N
AX AX_RULE_LINES 9060 N
BEN BEN_ELIG_PER_ELCTBL_CHC 16549 N
BEN BEN_ELIG_PER_F 16055 N
BEN BEN_ELIG_PER_OPT_F 29616 Y
BEN BEN_ENRT_RT 12008 N
BEN BEN_PERSON_ACTIONS 7284 N
BEN BEN_PRTT_ENRT_RSLT_F 3835 N
BEN BEN_REPORTING 19051 Y
XXXX XX_AS400_IIMTM 3258 N
CZ CZ_IMP_PS_NODES 8358 N
AZ AZ_DIFF_RESULTS 721535 Y
AZ AZ_REPORTER_DATA 3351285 Y
ICX ICX_SESSION_ATTRIBUTES 4705 N
BOM BOM_STRUCTURES_B 40261 Y
BOM BOM_COMPONENTS_B 4072 N
BOM BOM_INVENTORY_COMPS_INTERFACE 5709 N
BOM CST_ITEM_COSTS 21648 Y
INV MTL_INTERFACE_ERRORS 3520 N
INV MTL_ITEM_CATEGORIES 73312 Y
INV MTL_ITEM_REVISIONS_B 31544 Y
INV MTL_ITEM_REVISIONS_INTERFACE 39273 Y
INV MTL_ITEM_REVISIONS_TL 14222 N
INV MTL_PENDING_ITEM_STATUS 17744 N
INV MTL_SYSTEM_ITEMS_B 218803 Y
INV MTL_SYSTEM_ITEMS_INTERFACE 253048 Y
INV MTL_SYSTEM_ITEMS_TL 26366 Y
APPLSYS FND_NEW_MESSAGES 5236 N
APPLSYS FND_VIEWS 7110 N
APPLSYS FND_VIEW_COLUMNS 5709 N
APPLSYS JDR_ATTRIBUTES 20008 Y
APPLSYS JDR_COMPONENTS 8008 N
APPLSYS WF_DEFERRED 6228 N
APPLSYS WF_ITEM_ACTIVITY_STATUSES 3237 N
APPLSYS WF_ITEM_ATTRIBUTE_VALUES 27975 Y
APPLSYS AD_PROGRAM_RUN_TASK_JOBS 14270 N
AR HZ_GEOGRAPHIES 4308 N
AR HZ_GEOGRAPHY_IDENTIFIERS 4749 N
EGO EGO_ITEM_TEXT_TL 23449 Y
ENI ENI_OLTP_ITEM_STAR 43289 Y
APPLSYS DR$FND_LOBS_CTX$I 214198 Y
APPLSYS WF_JAVA_DEFERRED 4056 N
ICX DR$ICX_CAT_ITEMSCTXDESC_HDRS$I 6323 N
ICX ICX_CAT_ITEMS_CTX_DTLS_TLP 22493 Y
ICX ICX_CAT_ITEMS_CTX_HDRS_TLP 4749 N
ICX ICX_CAT_ATTRIBUTE_VALUES 10813 N
ICX ICX_CAT_ATTRIBUTE_VALUES_TLP 7725 N
INV MTL_ITEM_BULKLOAD_RECS 38715 Y
PO PO_ATTRIBUTE_VALUES 10462 N
PO PO_ATTRIBUTE_VALUES_TLP 7677 N
XXXX XX_MTL_SYSTEM_ITEMS_21APR09 6732 N
XXXX XX_MTL_SYSTEM_ITEMS_INTERFACE 20406 Y
INV MMT_AAM_NOCOMPRESS 26366 Y
INV MMT_AAM_COMPRESS 4473 N
XXXX XX_ITEMS_IIM_CONV 3473 N
XXXX XX_ITEM_ATTRIBUTES_IIM_CONV 3552 N
XXXX XX_ITEMS_CONV 26796 Y
XXXX XX_ITEM_ATTRIBUTES_CONV 36963 Y
XXXX XX_ITEM_PINS_CONV 9522 N
XXXX TABLE_SITE_PART 20764 Y
XXXX TABLE_CONTRACT_ITM_LST 27265 Y
XXXX TABLE_SCM_VIEW 22274 Y
XXXX TABLE_C_SCHED_PD_AMT 55245 Y
XXXX XX_AS400_ISMTM_SERIAL 24565 Y
XXXX TABLE_CONDITION 12638 N
SQL> create table my_segstat_1
2 as select * from v$segment_statistics
3 where (owner,object_name) in
4 (
5 select owner,table_name
6 from dba_tables
7 where blocks > 3200
8 and table_name not like ‘MLOG%’
9 and owner in
10 (select oracle_username from applsys.fnd_oracle_userid))
11 and statistic_name=’physical reads direct’
12 /
Table created.
SQL> spool scans.sql
SQL> select ‘select /*+ full (X) */ count(*) from ‘||owner||’.’||table_name||’ x ;’ q
2 from dba_tables
3 where blocks > 3200
4 and table_name not like ‘MLOG%’
5 and owner in
6 (select oracle_username from applsys.fnd_oracle_userid)
7 /
Query
——————————————————————————– ——————–
select /*+ full (X) */ count(*) from APPLSYS.AD_PATCH_COMMON_ACTIONS x;
select /*+ full (X) */ count(*) from AK.AK_REGION_ITEMS x;
select /*+ full (X) */ count(*) from ALR.ALR_ALERT_CHECKS x;
select /*+ full (X) */ count(*) from APPLSYS.AD_SNAPSHOT_FILES x;
select /*+ full (X) */ count(*) from APPLSYS.AD_TASK_TIMING x;
select /*+ full (X) */ count(*) from APPLSYS.FND_COLUMNS x;
select /*+ full (X) */ count(*) from APPLSYS.FND_CONCURRENT_REQUESTS x;
select /*+ full (X) */ count(*) from AR.HZ_HIERARCHY_NODES x;
select /*+ full (X) */ count(*) from AR.HZ_RELATIONSHIPS x;
select /*+ full (X) */ count(*) from APPLSYS.FND_LOBS x;
select /*+ full (X) */ count(*) from AX.AX_RULE_LINES x;
After flushing the buffer cache, I’ll run “scans.sql” to full-scan each of the 80 tables. I’m doing this in a production environment because I want to understand the boundary at which Oracle decides to flip on and off direct reads in a real-world scenario:
SQL> @scans
SQL> select /*+ full (X) */ count(*) from APPLSYS.AD_PATCH_COMMON_ACTIONS x;
COUNT(*)
———-
311051
SQL> select /*+ full (X) */ count(*) from AK.AK_REGION_ITEMS x;
COUNT(*)
———-
214997
SQL> select /*+ full (X) */ count(*) from ALR.ALR_ALERT_CHECKS x;
COUNT(*)
———-
459527
SQL> select /*+ full (X) */ count(*) from APPLSYS.AD_SNAPSHOT_FILES x;
COUNT(*)
———-
4590722
SQL> select /*+ full (X) */ count(*) from APPLSYS.AD_TASK_TIMING x;
COUNT(*)
———-
24780
<< many more examples deleted >>
SQL> create table my_segstat_2
2 as select * from v$segment_statistics
3 where (owner,object_name) in
4 (
5 select owner,table_name
6 from dba_tables
7 where blocks > 3200
8 and table_name not like ‘MLOG%’
9 and owner in
10 (select oracle_username from applsys.fnd_oracle_userid))
11 and statistic_name=’physical reads direct’
12 /
Table created.
SQL> select a.owner,a.object_name,a.value v1,b.value v2,
2 c.blocks ,
3 (case when b.value > a.value then ‘Direct’
4 else ‘Buffered’
5 end) dr
6 from my_segstat_1 a, my_segstat_2 b,
7 dba_tables c
8 where a.owner=b.owner
9 and a.object_name=b.object_name
10 and a.owner=c.owner
11 and a.object_name=c.table_name
12 order by c.blocks asc
13 /
Direct
Owner Object Direct Reads Start Direct Reads Start Blocks Buffered
————— —————————— —————— —————— ——— ———-
APPLSYS WF_ITEM_ACTIVITY_STATUSES 0 0 3237 Buffered
XXXX XX_AS400_IIMTM 0 0 3258 Buffered
APPLSYS AD_PATCH_RUN_BUGS 0 0 3379 Buffered
ALR ALR_ALERT_CHECKS 0 0 3394 Buffered
XXXX XX_ITEMS_IIM_CONV 0 0 3473 Buffered
INV MTL_INTERFACE_ERRORS 0 0 3520 Buffered
XXXX XX_ITEM_ATTRIBUTES_IIM_CONV 0 0 3552 Buffered
BEN BEN_PRTT_ENRT_RSLT_F 68925 72760 3835 Direct
APPLSYS WF_JAVA_DEFERRED 28392 28392 4056 Buffered
BOM BOM_COMPONENTS_B 20328 24400 4072 Direct
AR HZ_GEOGRAPHIES 21540 25848 4308 Direct
INV MMT_AAM_COMPRESS 43838 48233 4473 Direct
ICX ICX_SESSION_ATTRIBUTES 28196 32898 4705 Direct
AR HZ_GEOGRAPHY_IDENTIFIERS 23732 28481 4749 Direct
ICX ICX_CAT_ITEMS_CTX_HDRS_TLP 23745 28494 4749 Direct
APPLSYS FND_NEW_MESSAGES 57596 62832 5236 Direct
BOM BOM_INVENTORY_COMPS_INTERFACE 28545 34254 5709 Direct
APPLSYS FND_VIEW_COLUMNS 28458 34167 5709 Direct
APPLSYS FND_CONCURRENT_REQUESTS 67991 74172 6181 Direct
APPLSYS WF_DEFERRED 43596 43596 6228 Buffered
ICX DR$ICX_CAT_ITEMSCTXDESC_HDRS$I 31599 37922 6323 Direct
XXXX XX_MTL_SYSTEM_ITEMS_21APR09 100898 107630 6732 Direct
AK AK_REGION_ITEMS 76659 83628 6969 Direct
APPLSYS FND_VIEWS 35518 42628 7110 Direct
BEN BEN_PERSON_ACTIONS 36136 43420 7284 Direct
APPLSYS AD_FILES 37125 44550 7425 Direct
PO PO_ATTRIBUTE_VALUES_TLP 76698 84375 7677 Direct
ICX ICX_CAT_ATTRIBUTE_VALUES_TLP 77186 84911 7725 Direct
APPLSYS AD_TASK_TIMING 39604 47533 7929 Direct
APPLSYS JDR_COMPONENTS 39993 48001 8008 Direct
CZ CZ_IMP_PS_NODES 41646 50004 8358 Direct
AX AX_RULE_LINES 45300 54360 9060 Direct
XXXX XX_ITEM_PINS_CONV 47566 57088 9522 Direct
AR HZ_HIERARCHY_NODES 50955 61146 10191 Direct
APPLSYS FND_ENV_CONTEXT 61626 71897 10271 Direct
APPLSYS AD_PATCH_RUN_BUG_ACTIONS 52230 62676 10446 Direct
PO PO_ATTRIBUTE_VALUES 104588 115050 10462 Direct
APPLSYS FND_LOBS 52790 63348 10558 Direct
ICX ICX_CAT_ATTRIBUTE_VALUES 108107 118920 10813 Direct
APPLSYS FND_COLUMNS 56695 68034 11339 Direct
BEN BEN_ENRT_RT 598305 610313 12008 Direct
XXXX TABLE_CONDITION 62420 74904 12638 Direct
APPLSYS AD_FILE_VERSIONS 65615 78738 13123 Direct
APPLSYS AD_PATCH_COMMON_ACTIONS 68165 81798 13633 Direct
APPLSYS AD_SNAPSHOT_BUGFIXES 68465 82161 13696 Direct
INV MTL_ITEM_REVISIONS_TL 71058 85280 14222 Direct
APPLSYS AD_PROGRAM_RUN_TASK_JOBS 71350 85620 14270 Direct
BEN BEN_ELIG_PER_F 1683491 1699546 16055 Direct
BEN BEN_ELIG_PER_ELCTBL_CHC 742229 758778 16549 Direct
INV MTL_PENDING_ITEM_STATUS 106371 124115 17744 Direct
BEN BEN_REPORTING 133357 152408 19051 Direct
APPLSYS JDR_ATTRIBUTES 99912 119920 20008 Direct
XXXX XX_MTL_SYSTEM_ITEMS_INTERFACE 306090 326496 20406 Direct
XXXX TABLE_SITE_PART 102885 123462 20764 Direct
BOM CST_ITEM_COSTS 108160 129808 21648 Direct
XXXX TABLE_SCM_VIEW 110400 132480 22274 Direct
ICX ICX_CAT_ITEMS_CTX_DTLS_TLP 112433 134926 22493 Direct
EGO EGO_ITEM_TEXT_TL 117149 140598 23449 Direct
XXXX XX_AS400_ISMTM_SERIAL 121810 146172 24565 Direct
INV MMT_AAM_NOCOMPRESS 263660 290026 26366 Direct
INV MTL_SYSTEM_ITEMS_TL 365062 391428 26366 Direct
XXXX XX_ITEMS_CONV 133980 160776 26796 Direct
XXXX TABLE_CONTRACT_ITM_LST 135260 162312 27265 Direct
APPLSYS WF_ITEM_ATTRIBUTE_VALUES 167850 195825 27975 Direct
BEN BEN_ELIG_PER_OPT_F 2366120 2411138 29616 Direct
INV MTL_ITEM_REVISIONS_B 157594 189138 31544 Direct
XXXX XX_ITEM_ATTRIBUTES_CONV 184815 221778 36963 Direct
INV MTL_ITEM_BULKLOAD_RECS 193575 232290 38715 Direct
INV MTL_ITEM_REVISIONS_INTERFACE 196365 235638 39273 Direct
BOM BOM_STRUCTURES_B 286904 327165 40261 Direct
ENI ENI_OLTP_ITEM_STAR 216287 259576 43289 Direct
AR HZ_RELATIONSHIPS 613176 715372 51098 Direct
APPLSYS AD_SNAPSHOT_FILES 257080 359912 51416 Direct
XXXX TABLE_C_SCHED_PD_AMT 274595 329514 55245 Direct
INV MTL_ITEM_CATEGORIES 366272 439584 73312 Direct
APPLSYS DR$FND_LOBS_CTX$I 1070990 1285188 214198 Direct
INV MTL_SYSTEM_ITEMS_B 3279494 3498297 218803 Direct
INV MTL_SYSTEM_ITEMS_INTERFACE 3795720 4048768 253048 Direct
AZ AZ_DIFF_RESULTS 3607327 4328862 721535 Direct
AZ AZ_REPORTER_DATA 16843627 20194912 3351285 Direct
80 rows selected.
1050 Wilshire Drive,
Suite 170,
Troy, MI 48084
Phone: (248) 465-9533
Toll free: 1-877-868-1753
Email: [email protected]
© Centroid, Inc. All rights reserved. Contact Privacy Policy Terms of Use CCPA Policy
Centroid is a cloud services and technology company that provides Oracle enterprise workload consulting and managed services across Oracle, Azure, Amazon, Google, and private cloud. From applications to technology to infrastructure, Centroid’s depth of Oracle expertise and breadth of cloud capabilities helps clients modernize, transform, and grow their business to the next level.
© Centroid, Inc. All rights reserved. Contact Privacy Policy Terms of Use CCPA Policy