| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Optimizing Programs for ORACLE Apps (RULE Based)
Hi,
We have a client system running on Oracle Apps 10 and the optimizer mode is RULE based. For the past fewer months one of the interfaces (Outbound) is running long. It was earlier finished on 4hrs now its taking 11+ hrs.The row processed are almost same for each run. Quest Central is showing TOP sql but it is a part of common code which runs fine on 11i.
OS - Sun Solaris
DB - Oracle 8.1.7
Below are the init.ora parameters
_trace_files_public TRUE processes 250 sessions 280 timed_statistics TRUE timed_os_statistics 0 resource_limit FALSE license_max_sessions 0 license_sessions_warning 0 cpu_count 24 instance_groups event 10929 trace name context forever, 10932 trace name context level 2 shared_pool_size 200000000 shared_pool_reserved_size 10000000 large_pool_size 0 java_pool_size 20000K java_soft_sessionspace_limit 0 java_max_sessionspace_size 0 pre_page_sga FALSE shared_memory_address 0 hi_shared_memory_address 0 use_indirect_data_buffers FALSE lock_sga FALSE lock_name_space enqueue_resources 5000 nls_language AMERICAN nls_territory AMERICA nls_sort BINARY nls_date_language nls_date_format DD-MON-RR nls_currency nls_numeric_characters ., nls_iso_currency nls_calendar nls_time_format nls_timestamp_format nls_time_tz_format nls_timestamp_tz_format
disk_asynch_io TRUE tape_asynch_io TRUE dbwr_io_slaves 0 backup_tape_io_slaves FALSE ops_interconnects db_file_direct_io_count 64
lm_ress 6000 lm_locks 12000
log_file_name_convert db_block_buffers 50000 db_block_checksum FALSE db_block_size 8192 db_block_lru_latches 24 db_writer_processes 1 db_block_max_dirty_target 50000
max_commit_propagation_delay 700 compatible 8.1.7 log_archive_start TRUE log_archive_dest log_archive_duplex_dest log_archive_dest_1 log_archive_dest_2 log_archive_dest_3 log_archive_dest_4 log_archive_dest_5 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_max_processes 1 log_archive_min_succeed_dest 1 standby_archive_dest ?/dbs/arch log_archive_trace 0 log_archive_format %t_%s.log log_buffer 1310720 log_checkpoint_interval 1000000 log_checkpoint_timeout 3600 db_files 1500 db_file_multiblock_read_count 64 read_only_open_delayed FALSE parallel_server FALSE parallel_server_instances 1 gc_releasable_locks 0 gc_rollback_locks 0-1024=32!8REACH gc_files_to_locks gc_defer_time 10 thread 0 fast_start_io_target 50000 log_checkpoints_to_alert TRUE recovery_parallelism 0 control_file_record_keep_time 7 dml_locks 1000 row_locking ALWAYS serializable FALSE replication_dependency_tracking TRUE instance_number 0 transactions 308 transactions_per_rollback_segment 5 max_rollback_segments 35 transaction_auditing TRUE fast_start_parallel_rollback LOW db_block_checking FALSE os_roles FALSE rdbms_server_dn max_enabled_roles 30 remote_os_authent TRUE remote_os_roles FALSE O7_DICTIONARY_ACCESSIBILITY TRUE remote_login_passwordfile NONE dblink_encrypt_login FALSE license_max_users 0 db_domain global_names FALSE distributed_transactions 1000 commit_point_strength 1 mts_dispatchers mts_servers 0 mts_max_servers 20 mts_max_dispatchers 5 mts_sessions 0 mts_circuits 0 local_listener open_links 200 open_links_per_instance 4 optimizer_features_enable 8.1.7 fixed_date audit_trail NONE sort_area_size 524288 sort_area_retained_size 0 sort_multiblock_read_count 2 db_name HECHRW open_cursors 1000 ifile sql_trace FALSE _optimizer_undo_changes TRUE os_authent_prefix optimizer_mode RULE sql92_security FALSE blank_trimming FALSE always_anti_join HASH partition_view_enabled FALSE star_transformation_enabled FALSE parallel_broadcast_enabled FALSE parallel_adaptive_multi_user FALSE parallel_threads_per_cpu 2 parallel_automatic_tuning FALSE always_semi_join standard optimizer_max_permutations 80000 optimizer_index_cost_adj 100 optimizer_index_caching 0 query_rewrite_enabled FALSE query_rewrite_integrity enforced sql_version NATIVE serial_reuse DISABLE cursor_space_for_time FALSE session_cached_cursors 0 text_enable FALSE remote_dependencies_mode TIMESTAMP utl_file_dir plsql_v2_compatibility FALSE job_queue_processes 10 job_queue_interval 20 optimizer_percent_parallel 0 parallel_min_percent 0 create_bitmap_area_size 8388608 bitmap_merge_area_size 1048576 cursor_sharing EXACT parallel_min_servers 0 parallel_max_servers 5 parallel_instance_group parallel_execution_message_size 2148 hash_join_enabled TRUE hash_area_size 1048576 hash_multiblock_io_count 0 shadow_core_dump partial background_core_dump partial oracle_trace_enable FALSE oracle_trace_facility_path ?/otrace/admin/fdf oracle_trace_collection_path ?/otrace/admin/cdf oracle_trace_facility_name oracled oracle_trace_collection_name oracle_trace_collection_size 5242880 object_cache_optimal_size 102400 object_cache_max_size_percent 10 session_max_open_files 10 aq_tm_processes 0 hs_autoregister TRUE
Below are the session level statistics while the program was in execution ..
221 1 logons cumulative 221 1 logons current 221 177 opened cursors cumulative 221 82 opened cursors current 221 210 user commits 221 40 user calls 221 222894 recursive calls 221 823374 recursive cpu usage 221 230780779 session logical reads 221 8 CPU used when call started 221 8 CPU used by this session 221 1120914541 session connect time 221 1120914541 process last non-idle time 221 1527552 session uga memory 221 1553112 session uga memory max 221 43674 messages sent 221 5577588 session pga memory 221 5577588 session pga memory max 221 697 enqueue requests 221 695 enqueue releases 221 59 total file opens 221 23197 db block gets 221 230757608 consistent gets 221 857443 physical reads 221 3865 db block changes 221 220 consistent changes 221 993 physical writes 221 993 physical writes non checkpoint 221 23 change write time 221 2 redo synch writes 221 856712 free buffer requested 221 12415 dirty buffers inspected 221 282 pinned buffers inspected 221 461775 hot buffers moved to head of LRU 221 13853 free buffer inspected 221 65 commit cleanout failures: block lost 221 1107 commit cleanouts 221 1042 commit cleanouts successfully completed 221 12 CR blocks created 221 5002 prefetched blocks 221 306 prefetched blocks aged out before use 221 764 physical reads direct 221 993 physical writes direct 221 123 calls to kcmgcs 221 211 calls to kcmgas 221 72853 calls to get snapshot scn: kcmgss 221 1995 redo entries 221 1482180 redo size 221 220 data blocks consistent reads - undo records applied 221 122397036 no work - consistent read gets 221 13 cleanouts only - consistent read gets 221 5 rollbacks only - consistent read gets 221 7 cleanouts and rollbacks - consistent read gets 221 50 immediate (CURRENT) block cleanout applications 221 20 immediate (CR) block cleanout applications 221 453 deferred (CURRENT) block cleanout applications 221 4863 table scans (short tables) 221 1 table scans (long tables) 221 345353 table scan rows gotten 221 18283 table scan blocks gotten 221 514419241 table fetch by rowid 221 21767 table fetch continued row 221 4 cluster key scans 221 4 cluster key scan block gets 221 928326 rows fetched via callback 221 197 parse time cpu 221 409 parse time elapsed 221 272 parse count (total) 221 36 parse count (hard) 221 78094 execute count 221 2172 bytes sent via SQL*Net to client 221 85382 bytes received via SQL*Net from client 221 43 SQL*Net roundtrips to/from client 221 21137 sorts (memory) 221 2 sorts (disk) 221 183884 sorts (rows) 221 5 cursor authentications 221 908885278 buffer is pinned count 221 156608482 buffer is not pinned count
as per quest central following are the long running sql with the tkprof (for 50rows processed)
SELECT TO_DATE(PEV.SCREEN_ENTRY_VALUE)
FROM
PAY_ELEMENT_ENTRY_VALUES_F PEV,PAY_INPUT_VALUES_F PIV,PAY_ELEMENT_ENTRIES_F
PEE,PAY_ELEMENT_LINKS_F PEL,PAY_ELEMENT_TYPES_F PET,PAY_ACCRUAL_PLANS PAP
WHERE PEV.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID AND PIV.ELEMENT_TYPE_ID =
PET.ELEMENT_TYPE_ID AND PEV.ELEMENT_ENTRY_ID = PEE.ELEMENT_ENTRY_ID AND PEE.ELEMENT_LINK_ID = PEL.ELEMENT_LINK_ID AND PEL.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID AND PET.ELEMENT_TYPE_ID = PAP.ACCRUAL_PLAN_ELEMENT_TYPE_ID AND PEE.ENTRY_TYPE = 'E' AND PEE.ASSIGNMENT_ID = :b1 AND PAP.ACCRUAL_PLAN_ID = :b2 AND :b3 BETWEEN PEV.EFFECTIVE_START_DATE AND PEV.EFFECTIVE_END_DATE AND :b3 BETWEEN PIV.EFFECTIVE_START_DATE AND PIV.EFFECTIVE_END_DATE AND :b3 BETWEEN PEE.EFFECTIVE_START_DATE AND PEE.EFFECTIVE_END_DATE AND :b3 BETWEEN PEL.EFFECTIVE_START_DATE AND PEL.EFFECTIVE_END_DATE AND :b3 BETWEEN PET.EFFECTIVE_START_DATE AND PET.EFFECTIVE_END_DATE AND PIV.NAME =(SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'NAME_TRANSLATIONS' AND LOOKUP_CODE = 'PTO_CONTINUOUS_SD' ) call count cpu elapsed disk query current rows
Parse 2 0.00 0.00 0 0 0 0 Execute 49 0.02 0.04 0 0 0 0 Fetch 49 9.90 22.35 5609 201501 0 49
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 100 9.92 22.39 5609 201501 0 49
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer goal: RULE
Parsing user id: 80 (recursive depth: 2)
Rows Row Source Operation
------- ---------------------------------------------------
36 FILTER
72 NESTED LOOPS
72 NESTED LOOPS
36 NESTED LOOPS
36 NESTED LOOPS
36 NESTED LOOPS
36 TABLE ACCESS BY INDEX ROWID PAY_ACCRUAL_PLANS
36 INDEX UNIQUE SCAN (object id 249081)
36 INDEX RANGE SCAN (object id 249068)
36 TABLE ACCESS BY INDEX ROWID PAY_ELEMENT_LINKS_F
36 INDEX RANGE SCAN (object id 88719)
36 TABLE ACCESS BY INDEX ROWID PAY_ELEMENT_ENTRIES_F
36 INDEX RANGE SCAN (object id 88592)
72 TABLE ACCESS BY INDEX ROWID PAY_INPUT_VALUES_F
72 INDEX RANGE SCAN (object id 88745)
108 TABLE ACCESS BY INDEX ROWID PAY_ELEMENT_ENTRY_VALUES_F
352738 INDEX RANGE SCAN (object id 88829)
72 TABLE ACCESS BY INDEX ROWID FND_COMMON_LOOKUPS
72 INDEX UNIQUE SCAN (object id 34271)
SELECT TO_NUMBER(NVL(PEV.SCREEN_ENTRY_VALUE,0)) CARRYOVER,
TO_DATE(NVL(PEV2.SCREEN_ENTRY_VALUE,:b1),'DD-MON-YYYY') EXPIRY_DATE
FROM
PAY_ACCRUAL_PLANS PAP,PAY_ELEMENT_ENTRY_VALUES_F PEV,
PAY_ELEMENT_ENTRY_VALUES_F PEV1,PAY_ELEMENT_ENTRY_VALUES_F PEV2,
PAY_INPUT_VALUES_F PIV,PAY_ELEMENT_ENTRIES_F PEE WHERE PAP.ACCRUAL_PLAN_ID
Parse 1 0.00 0.00 0 0 0 0 Execute 360 0.06 0.07 0 0 0 0 Fetch 720 50.12 269.79 25537 1312928 0 360
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1081 50.18 269.86 25537 1312928 0 360
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer goal: RULE
Parsing user id: 80 (recursive depth: 3)
Rows Row Source Operation
------- ---------------------------------------------------
360 SORT ORDER BY
360 NESTED LOOPS
720 NESTED LOOPS
1707 NESTED LOOPS
1707 NESTED LOOPS
196282 NESTED LOOPS
720 TABLE ACCESS BY INDEX ROWID PAY_ACCRUAL_PLANS
720 INDEX UNIQUE SCAN (object id 249081)
196282 TABLE ACCESS BY INDEX ROWID PAY_ELEMENT_ENTRIES_F
196282 INDEX RANGE SCAN (object id 88595)
197269 TABLE ACCESS BY INDEX ROWID PAY_ELEMENT_ENTRY_VALUES_F
2814019 INDEX RANGE SCAN (object id 88823)
2694 TABLE ACCESS BY INDEX ROWID PAY_ELEMENT_ENTRY_VALUES_F
5388 INDEX RANGE SCAN (object id 88823)
1707 TABLE ACCESS BY INDEX ROWID PAY_INPUT_VALUES_F
2694 INDEX RANGE SCAN (object id 249160)
360 TABLE ACCESS BY INDEX ROWID PAY_ELEMENT_ENTRY_VALUES_F
1440 INDEX RANGE SCAN (object id 88823)
SELECT PEV.SCREEN_ENTRY_VALUE AMOUNT,NCR.ADD_OR_SUBTRACT ADD_OR_SUBTRACT,
TO_DATE(PEV1.SCREEN_ENTRY_VALUE,'DD-MON-YYYY') EFFECTIVE_DATE
FROM
PAY_ACCRUAL_PLANS PAP,PAY_NET_CALCULATION_RULES NCR,
PAY_ELEMENT_ENTRY_VALUES_F PEV,PAY_ELEMENT_ENTRY_VALUES_F PEV1,
PAY_ELEMENT_ENTRIES_F PEE WHERE PAP.ACCRUAL_PLAN_ID = :b1 AND
PEE.ASSIGNMENT_ID = :b2 AND PEE.ELEMENT_ENTRY_ID = PEV.ELEMENT_ENTRY_ID
AND PEV.INPUT_VALUE_ID + 0 = NCR.INPUT_VALUE_ID AND PAP.ACCRUAL_PLAN_ID =
NCR.ACCRUAL_PLAN_ID AND NCR.INPUT_VALUE_ID != PAP.CO_INPUT_VALUE_ID AND NCR.INPUT_VALUE_ID != PAP.PTO_INPUT_VALUE_ID AND PEV1.ELEMENT_ENTRY_ID = PEE.ELEMENT_ENTRY_ID AND PEV1.INPUT_VALUE_ID + 0 = NCR.DATE_INPUT_VALUE_ID AND ((:b3 IS NOT NULL AND :b3 = NCR.INPUT_VALUE_ID ) OR :b3 IS NULL ) call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0 Execute 360 0.04 0.02 0 0 0 0 Fetch 2536 448.07 448.25 11 14424448 0 2176
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2897 448.11 448.28 11 14424448 0 2176
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer goal: RULE
Parsing user id: 80 (recursive depth: 3)
Rows Row Source Operation
------- ---------------------------------------------------
2176 NESTED LOOPS
2536 NESTED LOOPS
2187333 NESTED LOOPS
4475 NESTED LOOPS
720 TABLE ACCESS BY INDEX ROWID PAY_ACCRUAL_PLANS
720 INDEX UNIQUE SCAN (object id 249081)
4475 TABLE ACCESS BY INDEX ROWID PAY_NET_CALCULATION_RULES
5195 INDEX RANGE SCAN (object id 88760)
2191088 TABLE ACCESS BY INDEX ROWID PAY_ELEMENT_ENTRIES_F
2191088 INDEX RANGE SCAN (object id 88595)
2189149 TABLE ACCESS BY INDEX ROWID PAY_ELEMENT_ENTRY_VALUES_F
31112896 INDEX RANGE SCAN (object id 88823)
2176 TABLE ACCESS BY INDEX ROWID PAY_ELEMENT_ENTRY_VALUES_F 19252 INDEX RANGE SCAN (object id 88823)
Any suggestions regarding this or what should be my approach from the developer point of view will be highly appreciated.
-- Regards, Tanmoy -- "Time is the coin of life. Only you can determine how it will be spent." -- http://www.freelists.org/webpage/oracle-lReceived on Sun Jul 10 2005 - 10:21:22 CDT
 |
 |