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

It would depend on how selective the filter clauses are and how scattered in the table the values of closed and cancelled time are.

Enable 10046 trace with level 8 and post the tkprof output here.

Tamil

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?

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?

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

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

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)

are the cardinalities correct?

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.

Yes, the cardinality is correct.

I'm sending herewith the explain plan from toad as below for further invetigation

Code:

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop SELECT STATEMENT Optimizer Mode=CHOOSE 7 K 19360 CONCATENATION HASH JOIN 3 K 2 M 11048 TABLE ACCESS FULL ARADMIN.T60 11 K 473 K 200 NESTED LOOPS 3 K 1 M 10837 TABLE ACCESS BY INDEX ROWID ARADMIN.H35 3 K 111 K 3255 INDEX RANGE SCAN ARADMIN.STF_CANT 3 K 48 TABLE ACCESS BY INDEX ROWID ARADMIN.T35 1 M 698 M 2 INDEX UNIQUE SCAN ARADMIN.IT35 1 M 1 HASH JOIN 3 K 2 M 11048 TABLE ACCESS FULL ARADMIN.T60 11 K 473 K 200 NESTED LOOPS 3 K 1 M 10837 TABLE ACCESS BY INDEX ROWID ARADMIN.H35 3 K 111 K 3255 INDEX RANGE SCAN ARADMIN.STF_CLOT 3 K 48 TABLE ACCESS BY INDEX ROWID ARADMIN.T35 1 M 698 M 2 INDEX UNIQUE SCAN ARADMIN.IT35 1 M 1