-
Performance Issue
Currently one of the query is taking about 32 secs to retrive 18 rows from mutiple tables, two of which has more than 15 million records and one has 47 million record. 32 seconds is not acceptable to business/application group. I have tried my best to tune this sql and I am providing all the information regarding the query, explain plan, database parameters and would apprecite if someone can suggest something very obvious which I have over looked. At this point all I can think of is we might have a IO bottleneck on our SAN environment...but would that give us any huge performance improvement ?? I have also suggested for partioning which is not there.
Your suggestions will be highly appreciated. I will provide more information next in this same thread.
SELECT
T13.LAST_UPD_BY,
T13.ROW_ID,
T13.CONFLICT_ID,
T13.CREATED_BY,
T13.CREATED,
T13.LAST_UPD,
T13.MODIFICATION_NUM,
T13.ACD_CALL_DURATION,
T9.LAST_UPD,
T13.OWNER_PER_ID,
T13.OWNER_LOGIN,
T13.APPT_START_DT,
T4.ROW_ID,
T13.TEMPLATE_FLG,
T11.LAST_UPD,
T6.MID_NAME,
T13.APPT_REPT_APPT_ID,
T11.LAST_UPD_BY,
T10.ATTRIB_34,
T4.ACT_APPT_START_DT,
T9.PAR_ROW_ID,
T1.LOGIN,
T9.ROW_ID,
T4.ACT_TODO_PLNEND_DT,
T10.ATTRIB_05,
T5.NAME,
T11.PAR_ROW_ID,
T10.LAST_UPD,
T10.LAST_UPD_BY,
T5.PROJ_NUM,
T12.ROW_ID,
T11.INTEGRATION_ID,
T10.PAR_ROW_ID,
T13.CAL_DISP_FLG,
T13.PROJ_ID,
T10.ATTRIB_07,
T9.CNTNT_CTG_DB_ID,
T13.TARGET_OU_ID,
T10.ROW_ID,
T9.CREATED,
T13.X_SUB_TYPE,
T13.SRA_SR_ID,
T6.LAST_NAME,
T10.CONFLICT_ID,
T13.APPT_REPT_FLG,
T13.PR_CON_ID,
T13.TODO_CD,
T9.CONFLICT_ID,
T10.CREATED,
T13.X_DEPARTMENT,
T13.X_SUB_TYPE,
T10.ATTRIB_06,
T13.OPTY_ID,
T6.PERSON_UID,
T4.ACT_TEMPLATE_FLG,
T13.APPT_REPT_END_DT,
T13.EVT_STAT_CD,
T7.ROW_ID,
T9.MODIFICATION_NUM,
T9.LAST_UPD_BY,
T13.ALARM_FLAG,
T13.TODO_PLAN_END_DT,
T11.CONFLICT_ID,
T2.NAME,
T11.CREATED_BY,
T3.CURCY_CD,
T4.ACT_CAL_DISP_FLG,
T4.ACT_APPT_REPT_FLG,
T8.WORK_PH_NUM,
T4.EMP_ID,
T12.ROW_ID,
T7.ROW_ID,
T2.LOC,
T11.OWN_INST_ID,
T10.MODIFICATION_NUM,
T4.ACT_ALARM_FLG,
T13.PR_CON_ID,
T4.ACT_APPT_RPTEND_DT,
T4.ACT_TODO_PLNSTRTDT,
T4.ACT_EVT_STAT_CD,
T13.BILLABLE_FLG,
T11.ROW_ID,
T9.CREATED_BY,
T13.APPT_REPT_REPL_CD,
T13.TODO_PLAN_START_DT,
T13.NAME,
T3.NAME,
T13.APPT_DURATION_MIN,
T5.BL_CURCY_CD,
T13.APPT_REPT_TYPE,
T13.PR_SYMPTOM_CD,
T2.BASE_CURCY_CD,
T11.CREATED,
T11.MODIFICATION_NUM,
T10.CREATED_BY
FROM
SIEBEL.S_USER T1,
SIEBEL.S_ORG_EXT T2,
SIEBEL.S_OPTY T3,
SIEBEL.S_ACT_EMP T4,
SIEBEL.S_PROJ T5,
SIEBEL.S_CONTACT T6,
SIEBEL.S_PARTY T7,
SIEBEL.S_CONTACT T8,
SIEBEL.S_EVT_MAIL T9,
SIEBEL.S_EVT_ACT_X T10,
SIEBEL.S_EVT_ACT_SS T11,
SIEBEL.S_PARTY T12,
SIEBEL.S_EVT_ACT T13
WHERE
T13.PR_CON_ID = T6.ROW_ID (+) AND
T13.OPTY_ID = T3.ROW_ID (+) AND
T13.TARGET_OU_ID = T2.PAR_ROW_ID (+) AND
T13.PROJ_ID = T5.ROW_ID (+) AND
T13.ROW_ID = T10.PAR_ROW_ID (+) AND
T13.ROW_ID = T9.PAR_ROW_ID (+) AND
T13.ROW_ID = T11.PAR_ROW_ID (+) AND
T4.EMP_ID = '1-46XD-1107' AND T13.ROW_ID = T4.ACTIVITY_ID AND
T4.EMP_ID = T7.ROW_ID AND
T4.EMP_ID = T1.PAR_ROW_ID (+) AND
T13.PR_CON_ID = T12.ROW_ID (+) AND
T13.PR_CON_ID = T8.PAR_ROW_ID (+) AND
((T4.ACT_TEMPLATE_FLG != 'Y' AND T4.ACT_TEMPLATE_FLG != 'P' OR T4.ACT_TEMPLATE_FLG IS NULL) AND
(T13.CREATED >= TO_DATE('04/26/2005 04:00:00','MM/DD/YYYY HH24:MI:SS') AND T13.TODO_CD = 'NCIC Call - Inbound') AND
(T13.APPT_REPT_REPL_CD IS NULL))
ORDER BY
T13.CREATED DESC
call count cpu elapsed disk query current rows
------- ----- ----- ------- ---- ------ ------- ----------
Parse 1 0.05 0.04 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3 2.76 39.48 13949 41969 0 18
------- ---- ----- ------- ----- ------ ------- ----------
total 5 2.81 39.52 13949 41969 0 18
Misses in library cache during parse: 1
Optimizer goal: RULE
Parsing user id: 1225 (JWEBB)
Rows Row Source Operation
------- ---------------------------------------------------
18 SORT ORDER BY
18 NESTED LOOPS OUTER
19 NESTED LOOPS OUTER
19 NESTED LOOPS OUTER
19 NESTED LOOPS OUTER
19 NESTED LOOPS OUTER
19 NESTED LOOPS OUTER
19 NESTED LOOPS OUTER
19 NESTED LOOPS OUTER
19 NESTED LOOPS
19 NESTED LOOPS OUTER
19 NESTED LOOPS OUTER
19 NESTED LOOPS
10672 TABLE ACCESS BY INDEX ROWID S_ACT_EMP
10672 INDEX RANGE SCAN (object id 66298)
10689 TABLE ACCESS BY INDEX ROWID S_EVT_ACT
21341 INDEX UNIQUE SCAN (object id 69073)
7 INDEX UNIQUE SCAN (object id 71214)
7 TABLE ACCESS BY INDEX ROWID S_CONTACT
25 INDEX UNIQUE SCAN (object id 84429)
36 INDEX UNIQUE SCAN (object id 71214)
7 TABLE ACCESS BY INDEX ROWID S_CONTACT
25 INDEX UNIQUE SCAN (object id 84427)
0 TABLE ACCESS BY INDEX ROWID S_PROJ
18 INDEX UNIQUE SCAN (object id 82809)
0 TABLE ACCESS BY INDEX ROWID S_OPTY
18 INDEX UNIQUE SCAN (object id 81411)
0 TABLE ACCESS BY INDEX ROWID S_ORG_EXT
18 INDEX UNIQUE SCAN (object id 81768)
18 TABLE ACCESS BY INDEX ROWID S_USER
36 INDEX UNIQUE SCAN (object id 84163)
0 TABLE ACCESS BY INDEX ROWID S_EVT_ACT_SS
18 INDEX RANGE SCAN (object id 79840)
18 TABLE ACCESS BY INDEX ROWID S_EVT_ACT_X
36 INDEX RANGE SCAN (object id 79843)
0 TABLE ACCESS BY INDEX ROWID S_EVT_MAIL
18 INDEX RANGE SCAN (object id 79866)
-
RAW TRACE FILE....
Most wait is on 'db file sequential read'
FROM
SIEBEL.S_USER T1,
SIEBEL.S_ORG_EXT T2,
SIEBEL.S_OPTY T3,
SIEBEL.S_ACT_EMP T4,
SIEBEL.S_PROJ T5,
SIEBEL.S_CONTACT T6,
SIEBEL.S_PARTY T7,
SIEBEL.S_CONTACT T8,
SIEBEL.S_EVT_MAIL T9,
SIEBEL.S_EVT_ACT_X T10,
SIEBEL.S_EVT_ACT_SS T11,
SIEBEL.S_PARTY T12,
SIEBEL.S_EVT_ACT T13
WHERE
T13.PR_CON_ID = T6.ROW_ID (+) AND
T13.OPTY_ID = T3.ROW_ID (+) AND
T13.TARGET_OU_ID = T2.PAR_ROW_ID (+) AND
T13.PROJ_ID = T5.ROW_ID (+) AND
T13.ROW_ID = T10.PAR_ROW_ID (+) AND
T13.ROW_ID = T9.PAR_ROW_ID (+) AND
T13.ROW_ID = T11.PAR_ROW_ID (+) AND
T4.EMP_ID = '1-46XD-1107' AND T13.ROW_ID = T4.ACTIVITY_ID AND
T4.EMP_ID = T7.ROW_ID AND
T4.EMP_ID = T1.PAR_ROW_ID (+) AND
T13.PR_CON_ID = T12.ROW_ID (+) AND
T13.PR_CON_ID = T8.PAR_ROW_ID (+) AND
((T4.ACT_TEMPLATE_FLG != 'Y' AND T4.ACT_TEMPLATE_FLG != 'P' OR T4.ACT_TEMPLATE_FLG IS NULL) AND
(T13.CREATED >= TO_DATE('04/26/2005 04:00:00','MM/DD/YYYY HH24:MI:SS') AND T13.TODO_CD = 'NCIC Call - Inbound') AND
(T13.APPT_REPT_REPL_CD IS NULL))
ORDER BY
T13.CREATED DESC
END OF STMT
PARSE #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3,tim=4139962036
BINDS #1:
EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3,tim=4139962036
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net more data to client' ela= 0 p1=1413697536 p2=2013 p3=0
WAIT #1: nam='db file sequential read' ela= 2 p1=6 p2=177782 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=53 p2=168893 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=53 p2=59611 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=55 p2=171527 p3=1
WAIT #1: nam='latch free' ela= 2 p1=-4611686010064640536 p2=66 p3=0
WAIT #1: nam='db file sequential read' ela= 1 p1=12 p2=182222 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=55 p2=175313 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=55 p2=175383 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=57 p2=173913 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=57 p2=174205 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=57 p2=174259 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=57 p2=175158 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=59 p2=178421 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=24 p2=177302 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=59 p2=178941 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=57 p2=181396 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=57 p2=181642 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=57 p2=182805 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=57 p2=184079 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=59 p2=184954 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=59 p2=185785 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=59 p2=185951 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=29 p2=261826 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=59 p2=187684 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=59 p2=187831 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=59 p2=189905 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=59 p2=190023 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=59 p2=190431 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=59 p2=190485 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=29 p2=262785 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=29 p2=262839 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=59 p2=191094 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=59 p2=191445 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=59 p2=191451 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=59 p2=191483 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=29 p2=263012 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=29 p2=263015 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=59 p2=192002 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=59 p2=192449 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=59 p2=192480 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=59 p2=192496 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=59 p2=192507 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=53 p2=177212 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=29 p2=263174 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=29 p2=263190 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=53 p2=177404 p3=1
WAIT #1: nam='db file sequential read' ela= 2 p1=29 p2=263228 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=53 p2=178107 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=29 p2=263368 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=29 p2=263439 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=53 p2=178845 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=29 p2=263547 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=53 p2=179275 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=29 p2=263677 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=53 p2=180178 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=53 p2=180447 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=29 p2=263945 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=29 p2=263954 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=53 p2=180974 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=53 p2=181343 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=53 p2=181503 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=53 p2=181571 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=29 p2=264090 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=29 p2=264199 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=53 p2=182595 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=53 p2=184236 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=29 p2=264763 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=29 p2=264766 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=53 p2=184926 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=53 p2=184966 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=53 p2=185209 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=29 p2=264843 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=53 p2=185327 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=29 p2=264942 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=55 p2=184760 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=55 p2=184762 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=55 p2=184855 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=55 p2=185551 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=55 p2=186225 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=55 p2=186507 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=29 p2=265330 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=55 p2=186618 p3=1
WAIT #1: nam='db file sequential read' ela= 2 p1=55 p2=186980 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=29 p2=265604 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=59 p2=170049 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=59 p2=168044 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=59 p2=168079 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=59 p2=168586 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=59 p2=168882 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=59 p2=169167 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=59 p2=169241 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=59 p2=170135 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=53 p2=62867 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=53 p2=165615 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=53 p2=165677 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=53 p2=165894 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=55 p2=188718 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=151 p2=118986 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=55 p2=188830 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=55 p2=189171 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=55 p2=189236 p3=1
WAIT #1: nam='db file sequential read' ela= 2 p1=55 p2=189670 p3=1
WAIT #1: nam='db file sequential read' ela= 2 p1=55 p2=189824 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=55 p2=190337 p3=1
WAIT #1: nam='db file sequential read' ela= 3 p1=151 p2=119310 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=55 p2=190356 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=55 p2=190915 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=55 p2=191139 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=55 p2=191178 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=151 p2=119579 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=151 p2=119588 p3=1
-
DB PARAMETER. Siebel Apps HU UX 12 CPU 16 G RAM, Oracle 8i RULE OPTMIZER
processes 3200
sessions 3525
timed_statistics TRUE
timed_os_statistics 0
resource_limit TRUE
license_max_sessions 0
license_sessions_warning 0
cpu_count 12
instance_groups
event
shared_pool_size 671088640
shared_pool_reserved_size 67108864
large_pool_size 104857600
java_pool_size 32768
java_soft_sessionspace_limit 0
java_max_sessionspace_size 0
pre_page_sga TRUE
shared_memory_address 0
hi_shared_memory_address 0
use_indirect_data_buffers FALSE
lock_sga TRUE
lock_name_space
enqueue_resources 15778
nls_language AMERICAN
nls_territory AMERICA
nls_sort BINARY
nls_date_language
nls_date_format DD-MM-YYYY
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
resource_manager_plan
hpux_sched_noage
lm_ress 6000
lm_locks 12000
active_instance_count
control_files /oracle/or
db_file_name_convert
log_file_name_convert
db_block_buffers 983040
db_block_checksum FALSE
db_block_size 8192
db_block_lru_latches 60
db_writer_processes 8
db_block_max_dirty_target 983040
buffer_pool_keep buffers:12
buffer_pool_recycle buffers:26
max_commit_propagation_delay 700
compatible 8.1.7
log_archive_start TRUE
log_archive_dest
log_archive_duplex_dest
log_archive_dest_1 location=/
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 2
log_archive_min_succeed_dest 1
standby_archive_dest ?/dbs/arch
log_archive_trace 0
log_archive_format arch_%t_%s
log_buffer 2048000
log_checkpoint_interval 51360000
log_checkpoint_timeout 1800
db_files 250
db_file_multiblock_read_count 32
read_only_open_delayed FALSE
parallel_server FALSE
parallel_server_instances 1
gc_releasable_locks 0
gc_rollback_locks 0-1024=32!
gc_files_to_locks
gc_defer_time 10
thread 0
fast_start_io_target 327680
log_checkpoints_to_alert TRUE
recovery_parallelism 0
control_file_record_keep_time 7
dml_locks 15508
row_locking always
serializable FALSE
replication_dependency_trackin TRUE
instance_number 0
transactions 3877
transactions_per_rollback_segm 5
max_rollback_segments 201
rollback_segments RBS0, RBS1
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 FALSE
remote_os_roles FALSE
O7_DICTIONARY_ACCESSIBILITY TRUE
remote_login_passwordfile EXCLUSIVE
dblink_encrypt_login FALSE
license_max_users 0
db_domain
global_names FALSE
distributed_transactions 969
commit_point_strength 1
instance_name sblprod
service_names sblprod
mts_dispatchers
mts_servers 0
mts_max_servers 20
mts_max_dispatchers 5
mts_sessions 0
mts_circuits 0
local_listener
mts_service sblprod
mts_listener_address
mts_multiple_listeners FALSE
open_links 4
open_links_per_instance 4
optimizer_features_enable 8.1.7
fixed_date
audit_trail NONE
sort_area_size 8097152
sort_area_retained_size 8097152
sort_multiblock_read_count 32
db_name sblprod
open_cursors 3000
ifile
sql_trace FALSE
os_authent_prefix
optimizer_mode RULE
sql92_security FALSE
blank_trimming FALSE
always_anti_join NESTED_LOO
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 50
text_enable FALSE
remote_dependencies_mode TIMESTAMP
utl_file_dir /interface
plsql_v2_compatibility FALSE
job_queue_processes 2
job_queue_interval 60
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_siz 2152
hash_join_enabled TRUE
hash_area_size 16194304
hash_multiblock_io_count 0
shadow_core_dump partial
background_core_dump partial
background_dump_dest /oracle/ad
user_dump_dest /oracle/ad
max_dump_file_size UNLIMITED
core_dump_dest /oracle/ad
tracefile_identifier
audit_file_dest ?/rdbms/au
oracle_trace_enable FALSE
oracle_trace_facility_path ?/otrace/a
oracle_trace_collection_path ?/otrace/a
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
-
STATISTICS FOR THE QUERY
Statistic Name
Statistic Type (S=stat, L=Latch) Value
---------------------------------------- - --------------
active checkpoint queue latch L 16
cache buffer handles L 14
checkpoint queue latch L 338
dml lock allocation L 2
list of block allocation L 2
messages L 56
mostly latch-free SCN L 3
redo writing L 31
session allocation L 1
session idle bit L 18
transaction allocation L 3
undo global data L 3
buffer is not pinned count S 62,202
bytes received via SQL*Net from client S 3,842
calls to get snapshot scn: kcmgss S 1
CPU used by this session S 1,379
CPU used when call started S 1,379
cursor authentications S 1
no work - consistent read gets S 19,540
opened cursors cumulative S 4
parse time cpu S 1,334
parse time elapsed S 1,334
rows fetched via callback S 21,149
session pga memory S -49,728
session uga memory S 72,368
table fetch continued row S 206
user calls S 9
cache buffers chains L 92,897
cache buffers lru chain L 2
enqueue hash chains L 6
enqueues L 8
library cache L 398
redo allocation L 15
redo copy L 9
row cache objects L 67,927
shared pool L 563
bytes sent via SQL*Net to client S 8,894
execute count S 4
parse count (hard) S 2
parse count (total) S 4
redo size S 0
SQL*Net roundtrips to/from client S 9
table fetch by rowid S 42,715
table scan blocks gotten S 0
table scan rows gotten S 0
buffer is pinned count S 44,846
consistent gets S 73,046
db block gets S 0
physical reads S 0
physical writes S 0
session logical reads S 73,046
sorts (disk) S 0
sorts (memory) S 92
sorts (rows) S 907
elapsed time (centiseconds) S 1,388
-
super - now learn how to post it properly so it is readable
-
What is not readable ?
This is the best it can get when you paste, since the forum doesn't allow more than 10000 words and also large attachement this is all it can get unless you can give me some better trick.
-
Hi,
Your query might be doing lot of HOT BLOCK reads. This means, it might be reading lot of blocks which might not be required to get the actual results.
1 .You can think of Purging or archiving old data from the table so that the table has as much data required by the application.
2. Do partitioning so that data is read only from the required partition.
regards
anandkl
anandkl
-
since it´s using RBO you should try to play with the FROM clause and decide the driving table however since it´s Siebel I am not sure if you can modify the queries
-
Originally posted by dbasupuser
What is not readable ?
This is the best it can get when you paste, since the forum doesn't allow more than 10000 words and also large attachement this is all it can get unless you can give me some better trick.
Use [ code ] & [ /code ]
Note : Remove the spaces.
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|