Re: idea
Posted: Thu Jul 15, 2004 5:45 am
Just to post what we are finding....
when we run this sql whick looks for the current batch, gets the timestamps for start and stop extracts and uses them in a where clause against a remote database we get the following execution plan.
My DBA tells me that the plan is telling us that what is actually happening is that the entire source table is being transmitted across the LAN and checked on the DW machine....obviously not a good idea......
We may/may not be able to get a link defined on the remote machine in production...we can ask....I think if we get the remote link it might just transmit the small tables across the LAN....
Peter
Select Statement
select * from ap_bank_accounts_all@peters where last_update_date between
(select t1.last_extract_tstamp from hlp_batch_extract_range t1, dw_batch_control t2
where t1.pk_batch_extract_range = t2.pk_batch_number and t2.batch_complete_flag = 0)
and
(select t1.this_extract_tstamp from hlp_batch_extract_range t1, dw_batch_control t2
where t1.pk_batch_extract_range = t2.pk_batch_number and t2.batch_complete_flag = 0)
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 FILTER
2 1 REMOTE* PETERS
3 1 NESTED LOOPS
4 3 TABLE ACCESS (FULL) OF 'DW_BATCH_CONTROL'
5 3 TABLE ACCESS (BY INDEX ROWID) OF 'HLP_BATCH_EXTRACT_RANGE'
6 5 INDEX (UNIQUE SCAN) OF 'SYS_C004648' (UNIQUE)
7 1 NESTED LOOPS
8 7 TABLE ACCESS (FULL) OF 'DW_BATCH_CONTROL'
9 7 TABLE ACCESS (BY INDEX ROWID) OF 'HLP_BATCH_EXTRACT_RANGE'
10 9 INDEX (UNIQUE SCAN) OF 'SYS_C004648' (UNIQUE)
2 SERIAL_FROM_REMOTE SELECT "BANK_ACCOUNT_ID","BANK_ACCOUNT_NAME"
,"LAST_UPDATE_DATE","LAST_UPDATED_BY
Statistics
----------------------------------------------------------
7 recursive calls
1 db block gets
7 consistent gets
0 physical reads
308 redo size
3940 bytes sent via SQL*Net to client
213 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
when we run this sql whick looks for the current batch, gets the timestamps for start and stop extracts and uses them in a where clause against a remote database we get the following execution plan.
My DBA tells me that the plan is telling us that what is actually happening is that the entire source table is being transmitted across the LAN and checked on the DW machine....obviously not a good idea......
We may/may not be able to get a link defined on the remote machine in production...we can ask....I think if we get the remote link it might just transmit the small tables across the LAN....
Peter
Select Statement
select * from ap_bank_accounts_all@peters where last_update_date between
(select t1.last_extract_tstamp from hlp_batch_extract_range t1, dw_batch_control t2
where t1.pk_batch_extract_range = t2.pk_batch_number and t2.batch_complete_flag = 0)
and
(select t1.this_extract_tstamp from hlp_batch_extract_range t1, dw_batch_control t2
where t1.pk_batch_extract_range = t2.pk_batch_number and t2.batch_complete_flag = 0)
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 FILTER
2 1 REMOTE* PETERS
3 1 NESTED LOOPS
4 3 TABLE ACCESS (FULL) OF 'DW_BATCH_CONTROL'
5 3 TABLE ACCESS (BY INDEX ROWID) OF 'HLP_BATCH_EXTRACT_RANGE'
6 5 INDEX (UNIQUE SCAN) OF 'SYS_C004648' (UNIQUE)
7 1 NESTED LOOPS
8 7 TABLE ACCESS (FULL) OF 'DW_BATCH_CONTROL'
9 7 TABLE ACCESS (BY INDEX ROWID) OF 'HLP_BATCH_EXTRACT_RANGE'
10 9 INDEX (UNIQUE SCAN) OF 'SYS_C004648' (UNIQUE)
2 SERIAL_FROM_REMOTE SELECT "BANK_ACCOUNT_ID","BANK_ACCOUNT_NAME"
,"LAST_UPDATE_DATE","LAST_UPDATED_BY
Statistics
----------------------------------------------------------
7 recursive calls
1 db block gets
7 consistent gets
0 physical reads
308 redo size
3940 bytes sent via SQL*Net to client
213 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed