-
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?
-
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?
-
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
-
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...
-
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 06:46 AM.
-
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.
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
|