DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Performance Issue

  1. #1
    Join Date
    Nov 2002
    Posts
    170

    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)

  2. #2
    Join Date
    Nov 2002
    Posts
    170
    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

  3. #3
    Join Date
    Nov 2002
    Posts
    170
    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

  4. #4
    Join Date
    Nov 2002
    Posts
    170
    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

  5. #5
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    super - now learn how to post it properly so it is readable

  6. #6
    Join Date
    Nov 2002
    Posts
    170
    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.

  7. #7
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    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

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  9. #9
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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
  •  


Click Here to Expand Forum to Full Width