performance issue with indexing
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: performance issue with indexing

  1. #1
    Join Date
    Sep 2006
    Location
    London
    Posts
    58

    performance issue with indexing

    Our users are experiencing the performance problem using Oracle 8.1.7 database. We are using Business object to display the data as required.

    This is the below script using BO.

    Can you please suggest me technically how to get resolved this issue?

    Please let me know if you need any more information.

    Thanks


    This is an example of the typical type of query I use, it works o.k. but I think some indexing could make it run much quicker.

    I think the TEST.SH_TYS____COMPL.CLOSED_TIME & TEST.SH_TYS____COMPL.CANCELLED_TIME are key fields that could be indexed to speed up the report.

    Also I tend to do a lot of reports using the TEST.TYS__COMPL_CONFIGURATION.OWNING_CENTRE as a filter, this isn't that large a table but
    uses a table join on TEST.TYS____COMPL.CID = TEST.TYS__COMPL_CONFIGURATION.CID so indexing the TEST.TYS____COMPL.CID field could
    provide an improvement.

    The TEST.TYS____COMPL.CREATE_DATE is another key field when we want to identify tickets raised during a period.

    The TEST.TYS____COMPL.SUBCASE_OF is another key field we filter by.

    The TEST.TYS____COMPL.CLEAR_CODE_OBJECT_FAMILY,TEST.TYS____COMPL.CLEAR_CODE_OBJECT & TEST.TYS____COMPL.CLEAR_CODE_ACTION fields may also be useful.




    SELECT TEST.TYS____COMPL.COMPL_ID, TEST.TYS____COMPL.STATUS, TEST.TYS____COMPL.MODIFIED_DATE,
    TEST.TYS____COMPL.CREATE_DATE, TEST.TYS____COMPL.SUBMITTER, TEST.TYS____COMPL.LAST_MODIFIED_BY,
    TEST.TYS____COMPL.COMPL_ENQUIRY_DESCRIPTION, TEST.TYS____COMPL.COMPL_CLEAR_TARGET_TIME,
    TEST.TYS____COMPL.CURRENT_ACTION, TEST.TYS____COMPL.RESTORE_UNITS, TEST.TYS____COMPL.CID,
    TEST.TYS____COMPL.ELEMENT_ID, TEST.TYS____COMPL.SITE_LOCATION_ID, TEST.TYS____COMPL.SITE,
    TEST.TYS____COMPL.PROACTIVITY, TEST.TYS____COMPL.INCIDENT_START_TIME,
    TEST.TYS____COMPL.TOTAL_OUTAGE_TIME__SECS_, TEST.TYS____COMPL.TOTAL_TICKET_TIME__SECS_,
    TEST.TYS____COMPL.TOTAL_COMPL_TIME__SECS_, TEST.TYS____COMPL.SEVERITY, TEST.TYS____COMPL.BACKUP_METHOD,
    TEST.TYS____COMPL.ELEMENT, TEST.TYS____COMPL.SERVICE_TYPE, TEST.TYS____COMPL.ELEMENT_DESCRIPTION,
    TEST.TYS____COMPL.ELEMENT_CATEGORY, TEST.TYS____COMPL.BACK_UP_END_DATE, TEST.TYS____COMPL.BACK_UP_START_DATE,
    TEST.TYS____COMPL.INCIDENT_END_TIME, TEST.TYS____COMPL.CUST_ACCEPTED_CLOSURE_TIME,
    TEST.TYS____COMPL.CLOSING_COMMENTS, TEST.TYS____COMPL.CLEAR_CODE, TEST.TYS____COMPL.ASSIGNED_TO,
    TEST.TYS____COMPL.ASSIGNEE_LOGIN, TEST.TYS____COMPL.RESTORE_AMOUNT, TEST.TYS____COMPL.COMPL_REPORTABLE,
    TEST.TYS____COMPL.SLA_ATTRIBUTABLE, TEST.TYS____COMPL.CDSP_OUTAGE__SECS_, TEST.TYS____COMPL.FORMAT,
    TEST.TYS____COMPL.CONTRACT_SERVICE_LEVEL, TEST.TYS____COMPL.ASSIGNED_TEAM,
    TEST.TYS____COMPL.REASON_FOR_CANCELLATION, TEST.TYS____COMPL.CLEAR_CODE_OBJECT,
    TEST.TYS____COMPL.CLEAR_CODE_ACTION, TEST.TYS____COMPL.CLOSE_CODE_OPTION,
    TEST.TYS____COMPL.CLEAR_CODE_OBJECT_FAMILY, TEST.TYS____COMPL.SERVICE_IMPACT, TEST.SH_TYS____COMPL.CLOSED_TIME,
    TEST.SH_TYS____COMPL.CLOSED_USER, TEST.SH_TYS____COMPL.CANCELLED_TIME, TEST.SH_TYS____COMPL.CANCELLED_USER,
    TEST.TYS__COMPL_CONFIGURATION.OWNING_CENTRE, TEST.TYS__COMPL_CONFIGURATION.CONTRACT,
    TEST.TYS____COMPL.SOURCE, TEST.TYS____COMPL.SUBCASE_OF, TEST.SH_TYS____COMPL.OLD_COMPL_ID,
    TEST.TYS____COMPL.SUPPLIER_1, TEST.TYS____COMPL.SUPPLIER_4, TEST.TYS____COMPL.SUPPLIER_3,
    TEST.TYS____COMPL.SUPPLIER_2, TEST.TYS____COMPL.TIME_SUPPLIER_ADVISED_BTSS_1,
    TEST.TYS____COMPL.TIME_SUPPLIER_ADVISED_BTSS_2, TEST.TYS____COMPL.TIME_SUPPLIER_ADVISED_BTSS_3,
    TEST.TYS____COMPL.TIME_SUPPLIER_ADVISED_BTSS_4, TEST.TYS____COMPL.SUPPLIER_1_REF,
    TEST.TYS____COMPL.SUPPLIER_2_REF, TEST.TYS____COMPL.SUPPLIER_3_REF, TEST.TYS____COMPL.SUPPLIER_4_REF,
    TEST.TYS____COMPL.PASSED_TO_SUPPLIER_AT_1, TEST.TYS____COMPL.PASSED_TO_SUPPLIER_AT_2,
    TEST.TYS____COMPL.PASSED_TO_SUPPLIER_AT_3, TEST.TYS____COMPL.PASSED_TO_SUPPLIER_AT_4,
    TEST.TYS____COMPL.SUPPLIER_RESOLVED_TIME_1, TEST.TYS____COMPL.SUPPLIER_RESOLVED_TIME_2,
    TEST.TYS____COMPL.SUPPLIER_RESOLVED_TIME_3, TEST.TYS____COMPL.SUPPLIER_RESOLVED_TIME_4,
    TEST.TYS____COMPL.FAILURE, TEST.TYS____COMPL.COUNTRY, TEST.TYS____COMPL.CUSTOMERS_NAME,
    TEST.TYS____COMPL.CUSTOMERS_TEL_NO, TEST.TYS____COMPL.CUSTOMER_E_MAIL_ADDRESS
    FROM TEST.TYS____COMPL, TEST.SH_TYS____COMPL, TEST.TYS__COMPL_CONFIGURATION
    WHERE TEST.TYS____COMPL.OLD_COMPL_ID = TEST.SH_TYS____COMPL.OLD_COMPL_ID AND
    TEST.TYS____COMPL.CID = TEST.TYS__COMPL_CONFIGURATION.CID AND
    ((TEST.SH_TYS____COMPL.CLOSED_TIME < (TO_DATE(TRUNC(next_day(SYSDATE - 7, 'MONDAY')), 'DD/MM/YY HH24:MI:SS')
    - TO_DATE('01/01/1970 01:00:00', 'DD/MM/YY HH24:MI:SS')) * 24 * 3600 + 1) AND
    (TEST.SH_TYS____COMPL.CLOSED_TIME > (TO_DATE(TRUNC(next_day(SYSDATE - 14, 'MONDAY')), 'DD/MM/YY HH24:MI:SS')
    - TO_DATE('01/01/1970 01:00:00', 'DD/MM/YY HH24:MI:SS')) * 24 * 3600) OR
    (TEST.SH_TYS____COMPL.CANCELLED_TIME < (TO_DATE(TRUNC(next_day(SYSDATE - 7, 'MONDAY')), 'DD/MM/YY HH24:MI:SS')
    - TO_DATE('01/01/1970 01:00:00', 'DD/MM/YY HH24:MI:SS')) * 24 * 3600 + 1) AND
    (TEST.SH_TYS____COMPL.CANCELLED_TIME > (TO_DATE(TRUNC(next_day(SYSDATE - 14, 'MONDAY')), 'DD/MM/YY HH24:MI:SS')
    - TO_DATE('01/01/1970 01:00:00', 'DD/MM/YY HH24:MI:SS')) * 24 * 3600))

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    It would depend on how selective the filter clauses are and how scattered in the table the values of closed and cancelled time are.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Enable 10046 trace with level 8 and post the tkprof output here.

    Tamil

  4. #4
    Join Date
    Sep 2006
    Location
    London
    Posts
    58
    It would depend on how selective the filter clauses are and how scattered in the table the values of closed and cancelled time are.

    What actions or suggestion do you provide here to resolve this issue?

    How to Enable 10046 trace with level 8 ?

    Can you please suugest how to create explain through toad?

  5. #5
    Join Date
    Sep 2006
    Location
    London
    Posts
    58
    Quote Originally Posted by slimdave
    It would depend on how selective the filter clauses are and how scattered in the table the values of closed and cancelled time are.
    can you please explain me what to do and how to do?

  6. #6
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    Quote Originally Posted by dbajay
    Can you please suugest how to create explain through toad?
    Haaaa Haaaa.

    I'm sorry but I have too. It's just too good to pass up.
    explain_plan
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  7. #7
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    There is a section in that application form that asks if the applicant has any infectous diseases.... It must be a prerequisite for getting a job there now. The more diseases you have the quicker you can move from mopping the floor to wrapping the burgers.

    DBAJAY - The answer to your question is, leave toad alone for a minute. Open up a SQL*Plus window. If you are tracing your own session:

    alter session set events '10046 trace name context forever, level 8';

    If you are tracing another session:

    exec dbms_system.set_ev(sid,serial#,10046,8,);

    If there is a means to uniquely identify the session (something username\program\osuser related) that sets it apart from other sessions, you could create a logon trigger to ensure that you managed to catch the whole...

    ...now you're going to ask me for the exact code for creating a logon trigger aren't you?... wheres that application form...
    Assistance is Futile...

  8. #8
    Join Date
    Sep 2006
    Location
    London
    Posts
    58
    I am sending herewith the execution plan for the current issue.

    Can you please suggest me further?

    Thanks

    Code:
    COUNT(*)
    ----------
    13892
    
    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=18936 Card=1 Bytes=39)
    1 0 SORT (AGGREGATE)
    2 1 CONCATENATION
    3 2 HASH JOIN (Cost=10834 Card=3792 Bytes=147888)
    4 3 NESTED LOOPS (Cost=10822 Card=3787 Bytes=128758)
    5 4 TABLE ACCESS (BY INDEX ROWID) OF 'H35' (Cost=3248 Card=3787 Bytes=75740)
    6 5 INDEX (RANGE SCAN) OF 'STF_CANT' (NON-UNIQUE) (Cost=48 Card=3787)
    7 4 TABLE ACCESS (BY INDEX ROWID) OF 'T35' (Cost=2 Card=1518461 Bytes=21258454)
    8 7 INDEX (UNIQUE SCAN) OF 'IT35' (UNIQUE) (Cost=1 Card=1518461)
    9 3 INDEX (FAST FULL SCAN) OF 'TFC_CID' (NON-UNIQUE) (Cost=8 Card=11019 Bytes=55095)
    10 2 HASH JOIN (Cost=10834 Card=3792 Bytes=147888)
    11 10 NESTED LOOPS (Cost=10822 Card=3787 Bytes=128758)
    12 11 TABLE ACCESS (BY INDEX ROWID) OF 'H35' (Cost=3248 Card=3787 Bytes=75740)
    13 12 INDEX (RANGE SCAN) OF 'STF_CLOT' (NON-UNIQUE) (Cost=48 Card=3787)
    14 11 TABLE ACCESS (BY INDEX ROWID) OF 'T35' (Cost=2 Card=1518461 Bytes=21258454)
    15 14 INDEX (UNIQUE SCAN) OF 'IT35' (UNIQUE) (Cost=1 Card=1518461)
    16 10 INDEX (FAST FULL SCAN) OF 'TFC_CID' (NON-UNIQUE) (Cost=8 Card=11019 Bytes=55095)
    Last edited by davey23uk; 12-11-2006 at 05:46 AM.

  9. #9
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    are the cardinalities correct?

  10. #10
    Join Date
    Sep 2006
    Location
    London
    Posts
    58
    It would depend on how selective the filter clauses are and how scattered in the table the values of closed and cancelled time are.

    Do you require any output or data files to predicate or achieve something beter.

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