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))