Home » RDBMS Server » Performance Tuning » Query Speed Slow.
Query Speed Slow. [message #165643] Fri, 31 March 2006 04:30 Go to next message
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 Go to previous messageGo to next message
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 #165734 is a reply to message #165727] Sat, 01 April 2006 00:04 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
In addition to Ross's reply:

give us the definition of the CE_DD_ORDERS view.
Re: Query Speed Slow. [message #165881 is a reply to message #165643] Mon, 03 April 2006 03:22 Go to previous message
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
Previous Topic: Merge Cartesian Join in Plan and More Reads
Next Topic: Clearing Blocks from Memory
Goto Forum:
  


Current Time: Fri Mar 29 06:05:05 CDT 2024