| Query Speed Slow. [message #165643] |
Fri, 31 March 2006 04:30  |
rvnair123
Messages: 26
Registered: January 2006
|
Junior Member |
|
|
Hello,
I have a query with explain plan as follows
SELECT CUSTOMER_NAME, AGE, ORDER_NUM, SVC_TAG, CURR_FACILITY, LOB, WORKCENTER_ID, LOC, TIME, ACTUAL_SYSTEM_QTY QTY, OPR, IP, WIP, ISSUES, COMMENTS, IN_PROCESS, TIE_NUMBER, FAMILY_PARENT_DESC, SEGMENT, CHANNEL_DESC, CUSTOMER_NUM, STC_VARIANCE, PROCESS_AGE, PAY_CODE, MERGE_FACILITY, ILLEGAL_STATUS, STAGGERED, SHIP_CODE, IP_TIMESTAMP, MFG_FACILITY, ROUTE_DATE, PROCESS_LOCATION FROM DPM_REPORTING_CODE.CE_DD_ORDERS WHERE ROWNUM <= 3000 AND (WIP_STATUS = 'B') AND MACRO_LOCATION = 'IP' AND RECORD_TYPE IN ('S', 'T')
/
Elapsed: 00:02:09.20
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=28 Card=10
0 Bytes=48200)
1 0 COUNT (STOPKEY)
2 1 NESTED LOOPS (OUTER) (Cost=28 Card=100 Bytes=48200)
3 2 NESTED LOOPS (Cost=27 Card=100 Bytes=45200)
4 3 NESTED LOOPS (Cost=26 Card=100 Bytes=42200)
5 4 NESTED LOOPS (Cost=25 Card=100 Bytes=39500)
6 5 NESTED LOOPS (OUTER) (Cost=4 Card=104 Bytes=37544)
7 6 NESTED LOOPS (OUTER) (Cost=3 Card=104 Bytes=36712)
8 7 VIEW (Cost=2 Card=115510 Bytes=40081970)
9 8 UNION-ALL
10 9 TABLE ACCESS (BY INDEX ROWID) OF 'BACKLOG_INTRADAY_SKU_REPORT_2' (TABLE) (Cost=1 Card=1 Bytes=107)
11 10 BITMAP CONVERSION (TO ROWIDS)
12 11 BITMAP INDEX (RANGE SCAN) OF 'BACKLOGSKU_WIP_IDX' (INDEX (BITMAP))
13 9 TABLE ACCESS (BY INDEX ROWID) OF 'BACKLOG_EXPAND_CSR_INTRA_RPT_1' (TABLE) (Cost=2469 Card=132489
Bytes=19740861)
14 13 BITMAP CONVERSION (TO ROWIDS)
15 14 BITMAP INDEX (RANGE SCAN) OF 'BACKLOGINTRA_WIP1_IDX' (INDEX (BITMAP))
16 7 INDEX (UNIQUE SCAN) OF 'SALES_REP_INFO_IDX'
(INDEX (UNIQUE)) (Cost=0 Card=1 Bytes=6)
17 6 INDEX (UNIQUE SCAN) OF 'LAST_ROUTE_POSITION_PK
' (INDEX (UNIQUE)) (Cost=0 Card=1 Bytes=8)
18 5 INDEX (UNIQUE SCAN) OF 'ORDER_COMMENT_STORAGE_PK
' (INDEX (UNIQUE)) (Cost=1 Card=1 Bytes=34)
19 4 TABLE ACCESS (BY INDEX ROWID) OF 'CHANNELS' (TABLE
) (Cost=1 Card=1 Bytes=27)
20 19 INDEX (UNIQUE SCAN) OF 'CHANNELS_IDX' (INDEX (UNIQUE)) (Cost=0 Card=1)
21 3 INDEX (UNIQUE SCAN) OF 'SYS_IOT_TOP_42626' (INDEX (UNIQUE)) (Cost=0 Card=1 Bytes=30)
22 2 TABLE ACCESS (BY INDEX ROWID) OF 'COMMENT_ROOT_CAUSE'
(TABLE) (Cost=1 Card=1 Bytes=30)
23 22 INDEX (RANGE SCAN) OF 'RCID_IDX' (INDEX) (Cost=0 Card=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
29619 consistent gets
2215 physical reads
0 redo size
337669 bytes sent via SQL*Net to client
2693 bytes received via SQL*Net from client
201 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3000 rows processed
The query takes 2 mins to execute,i need the ouptput in secs.How do i go about doing the same.
All table & index stats are up to date.Its using proper index.
Tables 'BACKLOG_INTRADAY_SKU_REPORT_2 & BACKLOG_EXPAND_CSR_INTRA_RPT_1 have nearly 20,00,000 records in it.
Thanx in advance.
Rekha
|
|
|
|
| Re: Query Speed Slow. [message #165727 is a reply to message #165643] |
Fri, 31 March 2006 22:43  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Go back to your previous post and hit the EDIT button. Enclose the SQL and the plan in [CODE] / [/CODE] tags so that we can read it.
Ross Leishman
|
|
|
|
|
|
| Re: Query Speed Slow. [message #165881 is a reply to message #165643] |
Mon, 03 April 2006 03:22 |
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
|
Senior Member |
|
|
It sounds like you are accessing Oracle Financials from outside Oracle Applications?
Try using the plan table and check the cost of each part of the statement. Add or drop an index if necessary. Put the used tables in RAM if they fit.
ROWNUM <= 3000 gathers all the results(could be millions?) before giving you the first 3000.
Set 'first_rows' in the init.ora file.
Insted of RECORD_TYPE IN ('S', 'T')
try (record_type = 'S' and record_type = 'T')
Either way I think you are looking at a full table scan unless record_type is part of a multi-segment index.
Neil
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
