| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: long runtimes cured by cutting hash_area_size by 90% - ???
Attached are 3 PLANs output by tkprof after SQL_TRACE. This is Oracle 8.1.7.4 on Sun Solaris, NO parallel query.
Attached also are
Queries are generated by a tool, Cognos.
Note: table RI_XLR_T has 1.8M rows, others are ALL under 60k.
tkprof PLANS -
13 secs., fastest w/ 1Meg HASH_AREA_SIZE.
(Similar times seen for 2Meg, 500k)
6 mins. 35 secs., slowest w/ 100Meg HASH_AREA_SIZE. There’s a killer HASH JOIN at the innermost point of the PLAN .. (Similar times seen for 8Meg, 10Meg, 20Meg)
Hash_Area_Size runtime (faster when smaller):
> 10,000,000 6:16 ( 6+ mins )
> 1,000,000 0:15 ( 16 secs)
This does NOT use parallel query.
It is repeatable.
Table Rowcounts:
xlr: 1.8M xcp: 61k mcp: 44k xru: 30k xrx: 30k
In advance thanks if you can shed light on the mysteries of CBO, etc. etc.
TKPROF: Release 8.1.7.4.0 - Production on Fri Apr 11 14:47:29 2003
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Trace file: rkr_uat_ora_20156.trc
Sort options: default
count = number of times OCI procedure was executed cpu = cpu time in seconds executing elapsed = elapsed time in seconds executing disk = number of physical reads of buffers from disk query = number of buffers gotten for consistent read current = number of buffers gotten in current mode (usually for update) rows = number of rows processed by the fetch or execute call ********************************************************************************
alter session set SQL_TRACE = TRUE
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 18
select 'CHRISBARR kq2 count: '||count(1) from (
select T1.RRSHNA,
T2.LONA,
T1.PSNA,
T5.UDDA,
T6.RITYNA,
T6.AMTYNA,
T6.AM,
T7.UDTYNA,
T7.UDDA,
T8.EVAM,
TO_DATE(RTRIM(T8.EVDA) ,'YYYY-MM-DD' ),
SUBSTR(T5.UDDA,1 ,INSTR(T5.UDDA,'#' )-1),
T1.TKSHNA,
T6.FRDA,
T6.TODA
from RICOS.RI_REX_T T12,
RICOS.RI_REX_T T13,
RICOS.RI_XRX_T T2,
RICOS.RI_XRU_T T5,
RICOS.RI_XCP_T T1,
RICOS.RI_XLR_T T6,
RICOS.T_LEV_TEMP T8,
RICOS.RI_MCP_T T11,
RICOS.RI_UDL_T T7
and T1.SU=T6.CPSU(+) and T6.CPSU=T8.SU(+) and T6.RITYNA=T8.NNDXKE2(+)
and T2.TYNA='CPY'
and T2.TYNA='CPY'
and T2.TODA=99991231
and T1.SCTYNA='FLEET1'
and T1.TODA=99991231
and T6.CFFL='Y'
and T6.RITYNA in ('TCE', 'DDL')
and T5.UDTYNA='Primary Officer'
and T1.CPTYNA='OBE-CPY-FAC '
and T13.RIENGEID=T11.RRRIENGEID
and T12.RIENGEID=T11.TKRIENGEID
and T13.RIENSHNA=T1.RRSHNA
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0 Execute 1 0.13 0.12 0 0 0 0 Fetch 2 395.43 395.43 0 204595 41 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 395.56 395.56 0 204595 41 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 18
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
12348 NESTED LOOPS OUTER 12348 NESTED LOOPS 12350 NESTED LOOPS 12350 NESTED LOOPS 31485 HASH JOIN 15449 TABLE ACCESS FULL RI_REX_T 94289063 HASH JOIN 12349 TABLE ACCESS FULL RI_XCP_T 44817 TABLE ACCESS FULL RI_MCP_T 43833 TABLE ACCESS BY INDEX ROWID RI_REX_T 62968 INDEX UNIQUE SCAN (object id 82245) 24698 TABLE ACCESS BY INDEX ROWID RI_XRX_T 24698 INDEX RANGE SCAN (object id 82223) 24696 INDEX RANGE SCAN (object id 82197) 7039 INDEX RANGE SCAN (object id 82228) 91292 INLIST ITERATOR 91292 INDEX RANGE SCAN (object id 82166)8386 TABLE ACCESS FULL T_LEV_TEMP
alter session set SQL_TRACE = FALSE
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.00 0 0 0 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 18
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.01 0.01 0 0 0 0 Execute 3 0.13 0.12 0 0 0 0 Fetch 2 395.43 395.43 0 204595 41 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 395.57 395.56 0 204595 41 1
Misses in library cache during parse: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0 Execute 0 0.00 0.00 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 0 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
3 user SQL statements in session.
0 internal SQL statements in session.
3 SQL statements in session.
1 session in tracefile.
3 user SQL statements in trace file.
0 internal SQL statements in trace file.
3 SQL statements in trace file.
3 unique SQL statements in trace file.
109 lines in trace file.
TKPROF: Release 8.1.7.4.0 - Production on Fri Apr 11 14:47:42 2003
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Trace file: rkr_uat_ora_18217.trc
Sort options: default
count = number of times OCI procedure was executed cpu = cpu time in seconds executing elapsed = elapsed time in seconds executing disk = number of physical reads of buffers from disk query = number of buffers gotten for consistent read current = number of buffers gotten in current mode (usually for update) rows = number of rows processed by the fetch or execute call ********************************************************************************
alter session set SQL_TRACE = TRUE
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0 Execute 1 0.00 0.01 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.01 0 0 0 0
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 18
select 'CHRISBARR kq2 count: '||count(1) from (
select T1.RRSHNA,
T2.LONA,
T1.PSNA,
T5.UDDA,
T6.RITYNA,
T6.AMTYNA,
T6.AM,
T7.UDTYNA,
T7.UDDA,
T8.EVAM,
TO_DATE(RTRIM(T8.EVDA) ,'YYYY-MM-DD' ),
SUBSTR(T5.UDDA,1 ,INSTR(T5.UDDA,'#' )-1),
T1.TKSHNA,
T6.FRDA,
T6.TODA
from RICOS.RI_REX_T T12,
RICOS.RI_REX_T T13,
RICOS.RI_XRX_T T2,
RICOS.RI_XRU_T T5,
RICOS.RI_XCP_T T1,
RICOS.RI_XLR_T T6,
RICOS.T_LEV_TEMP T8,
RICOS.RI_MCP_T T11,
RICOS.RI_UDL_T T7
and T1.SU=T6.CPSU(+) and T6.CPSU=T8.SU(+) and T6.RITYNA=T8.NNDXKE2(+)
and T2.TYNA='CPY'
and T2.TYNA='CPY'
and T2.TODA=99991231
and T1.SCTYNA='FLEET1'
and T1.TODA=99991231
and T6.CFFL='Y'
and T6.RITYNA in ('TCE', 'DDL')
and T5.UDTYNA='Primary Officer'
and T1.CPTYNA='OBE-CPY-FAC '
and T13.RIENGEID=T11.RRRIENGEID
and T12.RIENGEID=T11.TKRIENGEID
and T13.RIENSHNA=T1.RRSHNA
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 2 12.86 13.55 5809 204595 41 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 12.86 13.55 5809 204595 41 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 18
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
12348 NESTED LOOPS OUTER 12348 NESTED LOOPS 12350 NESTED LOOPS 12350 NESTED LOOPS 31485 HASH JOIN 12349 TABLE ACCESS FULL RI_XCP_T 42951 HASH JOIN 15449 TABLE ACCESS FULL RI_REX_T 44817 TABLE ACCESS FULL RI_MCP_T 43833 TABLE ACCESS BY INDEX ROWID RI_REX_T 62968 INDEX UNIQUE SCAN (object id 82245) 24698 TABLE ACCESS BY INDEX ROWID RI_XRX_T 24698 INDEX RANGE SCAN (object id 82223) 24696 INDEX RANGE SCAN (object id 82197) 7039 INDEX RANGE SCAN (object id 82228) 91292 INLIST ITERATOR 91292 INDEX RANGE SCAN (object id 82166)8386 TABLE ACCESS FULL T_LEV_TEMP
alter session set SQL_TRACE = FALSE
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 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 18
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0 Execute 3 0.00 0.01 0 0 0 0 Fetch 2 12.86 13.55 5809 204595 41 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 12.86 13.56 5809 204595 41 1
Misses in library cache during parse: 0
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0 Execute 0 0.00 0.00 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 0 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
3 user SQL statements in session.
0 internal SQL statements in session.
3 SQL statements in session.
1 session in tracefile.
3 user SQL statements in trace file.
0 internal SQL statements in trace file.
3 SQL statements in trace file.
3 unique SQL statements in trace file.
109 lines in trace file.
SQL> select name,value from v$parameter where value not like '%/users/oracle/%' order by name;
NAME VALUE
----------------------------------- -----------------------------------
O7_DICTIONARY_ACCESSIBILITY TRUE
always_anti_join NESTED_LOOPS
always_semi_join STANDARD
aq_tm_processes 0
audit_file_dest ?/rdbms/audit
audit_trail NONE
background_core_dump partial
backup_tape_io_slaves FALSE
bitmap_merge_area_size 1048576
blank_trimming FALSE
commit_point_strength 1
compatible 8.1.7
control_file_record_keep_time 7
create_bitmap_area_size 8388608
cursor_sharing EXACT
cursor_space_for_time FALSE
db_block_buffers 123070
db_block_checking FALSE
db_block_checksum FALSE
db_block_lru_latches 4
db_block_max_dirty_target 123070
db_block_size 8192
db_file_direct_io_count 64
db_file_multiblock_read_count 4
db_files 400
db_writer_processes 1
dblink_encrypt_login FALSE
dbwr_io_slaves 0
disk_asynch_io TRUE
distributed_transactions 61
dml_locks 988
enqueue_resources 1408
fast_start_io_target 123070
fast_start_parallel_rollback LOW
gc_defer_time 10
gc_releasable_locks 0
gc_rollback_locks 0-1024=32!8REACH
global_names TRUE
hash_area_size 100000000
hash_join_enabled TRUE
hash_multiblock_io_count 8
hi_shared_memory_address 0
hs_autoregister TRUE
instance_number 0
java_max_sessionspace_size 0
java_pool_size 20000K
java_soft_sessionspace_limit 0
job_queue_interval 60
job_queue_processes 0
large_pool_size 100000000
license_max_sessions 0
license_max_users 0
license_sessions_warning 0
lm_locks 12000
lm_ress 6000
lock_sga FALSE
log_archive_dest_1 location=/rkr_data/rkr_prd/arch/par
ch1 mandatory
log_archive_dest_2 SERVICE=rkr_prd_stby OPTIONAL REOPE
N=120
log_archive_dest_state_1 ENABLE
log_archive_dest_state_2 ENABLE
log_archive_dest_state_3 enable
log_archive_dest_state_4 enable
log_archive_dest_state_5 enable
log_archive_format arch_log%t_%s.dbf
log_archive_max_processes 1
log_archive_min_succeed_dest 1
log_archive_start TRUE
log_archive_trace 1
log_buffer 3276800
log_checkpoint_interval 220000000
log_checkpoint_timeout 0
log_checkpoints_to_alert FALSE
max_commit_propagation_delay 700
max_dump_file_size 10240
max_enabled_roles 30
max_rollback_segments 49
mts_circuits 0
mts_max_dispatchers 5
mts_max_servers 20
mts_multiple_listeners FALSE
mts_servers 0
mts_sessions 0
nls_language AMERICAN
nls_territory AMERICA
object_cache_max_size_percent 10
object_cache_optimal_size 102400
open_cursors 300
open_links 4
open_links_per_instance 4
optimizer_features_enable 8.1.7
optimizer_index_caching 0
optimizer_index_cost_adj 100
optimizer_max_permutations 80000
optimizer_mode CHOOSE
optimizer_percent_parallel 0
oracle_trace_collection_path ?/otrace/admin/cdf
oracle_trace_collection_size 5242880
oracle_trace_enable FALSE
oracle_trace_facility_name oracled
oracle_trace_facility_path ?/otrace/admin/fdf
os_roles FALSE
parallel_adaptive_multi_user FALSE
parallel_automatic_tuning FALSE
parallel_broadcast_enabled FALSE
parallel_execution_message_size 2148
parallel_max_servers 8
parallel_min_percent 0
parallel_min_servers 0
parallel_server FALSE
parallel_server_instances 1
parallel_threads_per_cpu 2
partition_view_enabled FALSE
plsql_v2_compatibility FALSE
pre_page_sga FALSE
processes 200
query_rewrite_enabled FALSE
query_rewrite_integrity enforced
read_only_open_delayed FALSE
recovery_parallelism 0
remote_dependencies_mode TIMESTAMP
remote_login_passwordfile NONE
remote_os_authent FALSE
remote_os_roles FALSE
replication_dependency_tracking TRUE
resource_limit FALSE
rollback_segments r01, r02, r03, r04, rbsbig
row_locking always
serial_reuse DISABLE
serializable FALSE
session_cached_cursors 0
session_max_open_files 10
sessions 225
shadow_core_dump partial
shared_memory_address 0
shared_pool_reserved_size 51200000
shared_pool_size 1024000000
sort_area_retained_size 2048000
sort_area_size 88064000
sort_multiblock_read_count 2
sql92_security FALSE
sql_trace FALSE
sql_version NATIVE
standby_archive_dest ?/dbs/arch
star_transformation_enabled FALSE
tape_asynch_io TRUE
text_enable FALSE
thread 0
timed_os_statistics 0
timed_statistics TRUE
transaction_auditing TRUE
transactions 247
transactions_per_rollback_segment 5
use_indirect_data_buffers FALSE
158 rows selected.
SQL> quit
select 'kq1 count: '||count(1) from (
select T1.RRSHNA,
T2.LONA,
T1.PSNA,
T5.UDDA,
T6.RITYNA,
T6.AMTYNA,
T6.AM,
T7.UDTYNA,
T7.UDDA,
T8.EVAM,
TO_DATE(RTRIM(T8.EVDA) , 'YYYY-MM-DD' ),
SUBSTR(T5.UDDA, 1 , INSTR(T5.UDDA, '#' )-1),
T1.TKSHNA,
T6.FRDA,
T6.TODA
from REX_T T12,
REX_T T13,
XRX_T T2,
XRU_T T5,
XCP_T T1,
XLR_T T6,
T_LEV_TEMP T8,
MCP_T T11,
UDL_T T7
and T1.SU = T6.CPSU(+) and T6.CPSU = T8.SU(+) and T6.RITYNA = T8.NNDXKE2(+)
and T2.TYNA = 'AGY'
and T2.TYNA = 'AGY'
and T2.TODA = 99991231
and T1.SCTYNA = 'HASP1'
and T1.TODA = 99991231
and T6.CFFL = 'Y'
and T6.RITYNA in ('COR','AGL','GEN')
and T5.UDTYNA = 'Primary Contact'
and T1.CPTYNA = 'YR-AGY-FAC '
and T13.RIENGEID = T11.RRRIENGEID
and T12.RIENGEID = T11.TKRIENGEID
and T13.RIENSHNA = T1.RRSHNA
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: C. Barr INET: cbarr_at_hotpop.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Fri Apr 11 2003 - 16:43:54 CDT
 |
 |