-
When I execute this Query, its pretty fast. It takes just a second.
SELECT
count(*)
FROM TSW_CODES CD1,
TSW_CODES CD2,
TSW_CODES CD3,
TSW_CODES CD4,
TSW_CODES CD5,
TSW_CODES CD6,
TSW_CODES CD7,
TSW_PERSONNEL EMP,
EAM_COMPONENTS CMP,
EWM_WO_AUDITS WOA
WHERE ( CD1.CODE_ID (+) = WOA.WO_STATUS_ID ) and
( CD2.CODE_ID (+) = WOA.ACTION_ID ) and
( CD3.CODE_ID (+) = WOA.CATEGORY_ID ) and
( CD4.CODE_ID (+) = WOA.AREA_ID ) and
( CD5.CODE_ID (+) = WOA.ASSET_STATUS_ID ) and
( CD6.CODE_ID (+) = WOA.PRIORITY_ID ) and
( CD7.CODE_ID (+) = WOA.SHUTDOWN_ID ) and
( EMP.EMP_NO = WOA.EMP_NO ) and
( CMP.SERIAL_ID (+) = WOA.COMPONENT_SERIAL_ID) and
WOA.WORK_ORDER_NO = '02-000695-000'
ORDER BY WOA.DATE_TIME DESC ;
But the same Command if I choose columns instead of count(*), it takes 5-6 minutes. Heres the SQL:
SELECT
CD1.USER_CODE WO_STATUS_CODE,
CD2.USER_CODE ACTION_CODE,
CD3.USER_CODE CATEGORY_CODE ,
CD4.USER_CODE AREA_CODE,
CD5.USER_CODE ASSET_STATUS_CODE ,
CD6.USER_CODE WO_PRIORITY_CODE,
CD7.USER_CODE SHUTDOWN_CODE
FROM TSW_CODES CD1,
TSW_CODES CD2,
TSW_CODES CD3,
TSW_CODES CD4,
TSW_CODES CD5,
TSW_CODES CD6,
TSW_CODES CD7,
TSW_PERSONNEL EMP,
EAM_COMPONENTS CMP,
EWM_WO_AUDITS WOA
WHERE ( CD1.CODE_ID (+) = WOA.WO_STATUS_ID ) and
( CD2.CODE_ID (+) = WOA.ACTION_ID ) and
( CD3.CODE_ID (+) = WOA.CATEGORY_ID ) and
( CD4.CODE_ID (+) = WOA.AREA_ID ) and
( CD5.CODE_ID (+) = WOA.ASSET_STATUS_ID ) and
( CD6.CODE_ID (+) = WOA.PRIORITY_ID ) and
( CD7.CODE_ID (+) = WOA.SHUTDOWN_ID ) and
( EMP.EMP_NO = WOA.EMP_NO ) and
( CMP.SERIAL_ID (+) = WOA.COMPONENT_SERIAL_ID) and
WOA.WORK_ORDER_NO = '02-000695-000'
ORDER BY WOA.DATE_TIME DESC ;
I checked for Chained Rows. No chained rows. I checked for block fragments. No block fragments. What could be the problem. Please advise as it is very urgent for the next release we have here.
-
Did you check explain plan?
If you check it you will see that in first case you access only index blocks in second case you access index & data blocks to return result.
[Edited by kgb on 04-08-2002 at 06:33 AM]
Best wishes!
Dmitri
-
Okay. But how does this help me now? I even re-built all the indexes. Nothing seems to help. How to trouble shoot this problem.
Another thing: The same query performs very fast on another server with similar data. Infact, the other database fetches more rows than this one. Its all very confusing. I see the same indexes created on the tables on both the databases. Please help.
[Edited by dbafreak on 04-08-2002 at 06:39 AM]
-
-
Originally posted by pando
do you run it in local?
I am running this from the client. From SQL * PLUS.
-
Does your servers hace the same configurations?
Did you try to review indexes, I mean WOA.WORK_ORDER_NO = '02-000695-000' looks like a number, you could decrease index size around in 3,5 times, if you use number instead of char.
Best wishes!
Dmitri
-
how about try run it in local with both query, count(*) and columns and compare
-
Originally posted by pando
how about try run it in local with both query, count(*) and columns and compare
Okay. Let me try that. Meanwhile here's what EXPLAIN PLAN outputted into the PLAN_TABLE:
ID PARENT_ID OPERATION SUBSTR(OBJECT_NAME,1,25)
---------- ---------- ------------------------------------------------------------ -----------------
0 SELECT STATEMENT
1 0 NESTED LOOPS OUTER
2 1 NESTED LOOPS OUTER
3 2 NESTED LOOPS OUTER
4 3 NESTED LOOPS OUTER
5 4 NESTED LOOPS OUTER
6 5 NESTED LOOPS OUTER
7 6 NESTED LOOPS OUTER
8 7 NESTED LOOPS
9 8 NESTED LOOPS OUTER
10 9 TABLE ACCESS BY INDEX ROWID EWM_WO_AUDITS
11 10 INDEX RANGE SCAN DESCENDING EWM_CON_WOU_PK
12 9 TABLE ACCESS BY INDEX ROWID TSW_CODES
13 12 INDEX UNIQUE SCAN TSW_CON_COD_PK
14 8 TABLE ACCESS BY INDEX ROWID TSW_PERSONNEL
15 14 INDEX UNIQUE SCAN TSW_CON_EMP_PK
16 7 TABLE ACCESS BY INDEX ROWID TSW_CODES
17 16 INDEX UNIQUE SCAN TSW_CON_COD_PK
18 6 TABLE ACCESS BY INDEX ROWID TSW_CODES
19 18 INDEX UNIQUE SCAN TSW_CON_COD_PK
20 5 TABLE ACCESS BY INDEX ROWID EAM_COMPONENTS
21 20 INDEX UNIQUE SCAN EAM_CON_CMP_PK
22 4 TABLE ACCESS BY INDEX ROWID TSW_CODES
23 22 INDEX UNIQUE SCAN TSW_CON_COD_PK
24 3 TABLE ACCESS BY INDEX ROWID TSW_CODES
25 24 INDEX UNIQUE SCAN TSW_CON_COD_PK
26 2 TABLE ACCESS BY INDEX ROWID TSW_CODES
27 26 INDEX UNIQUE SCAN TSW_CON_COD_PK
28 1 TABLE ACCESS BY INDEX ROWID TSW_CODES
29 28 INDEX UNIQUE SCAN TSW_CON_COD_PK
-
Pando: I tried from the Server. Same problem. COunt takes a second and colums fetch takes about 4-5 minutes.
-
Originally posted by kgb
Does your servers hace the same configurations?
Did you try to review indexes, I mean WOA.WORK_ORDER_NO = '02-000695-000' looks like a number, you could decrease index size around in 3,5 times, if you use number instead of char.
It is supposed to be a VARCHAR unfortunately.
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
|