| Problem in Trace [message #65272] |
Wed, 14 July 2004 20:49  |
Tom
Messages: 67
Registered: June 1998
|
Member |
|
|
Hi All,
I have a scenario.The query is a select from a view which has got some tables with nearly 100000 records in it.The query is something like this.
select a.name,a.number,a.description from details_v a where a.name like '%&1%' order by a.name.
The tables in the view are all analyzed.But still when the query is returning only 388 recotds it takes more than 2 min.And also the explain plan uses index row_id on the tables.
call count cpu elapsed disk query current rows
------- ------ ----- ------- ------ ------ ------- ----
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.01 0.00 0 0 0 0
Fetch 20 58.19 153.22 395102 919346 0 388
------- ------ ----- ------- ------ ------ ------- ----
total 22 58.21 153.23 395102 919346 0 388
Any pointers would be of immense help.
Thanks,
|
|
|
|
| Re: Problem in Trace [message #65273 is a reply to message #65272] |
Wed, 14 July 2004 21:16  |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
The "a.name like '%&1%'" clause will probably result in at least 1 Full Table Scan. (normally) No index can be used if the first character(s) are not known.
It all depends on the construction of the view. Tkprof without the query and the plan won't tell much.
hth
|
|
|
|
| Re: Problem in Trace [message #65275 is a reply to message #65273] |
Thu, 15 July 2004 00:39 |
Tom
Messages: 67
Registered: June 1998
|
Member |
|
|
The query is basically a Oracle APPS LOV query.
The like parameter is constructed at runtime by the Form.
select oec.customer_id, OEC.name, OEC.customer_number
from
OE_SOLD_TO_ORGS_V OEC WHERE UPPER(NAME) LIKE :1 AND (NAME LIKE :2 OR NAME
LIKE :3 OR NAME LIKE :4 OR NAME LIKE :5) order by oec.name.
call count cpu elapsed disk query current rows
------- ------ ----- ------- ------ ------ ------- ----
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.01 0.00 0 0 0 0
Fetch 20 58.19 153.22 395102 919346 0 388
------- ------ ----- ------- ------ ------ ------- ----
total 22 58.21 153.23 395102 919346 0 388
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 183 (APPS)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 SORT (ORDER BY)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'HZ_CUST_ACCOUNTS'
0 NESTED LOOPS
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'HZ_PARTIES'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'HZ_PARTIES_N9'
(NON-UNIQUE)
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'HZ_CUST_ACCOUNTS_N2' (NON-UNIQUE)
Thanks,
|
|
|
|
| Re: Problem in Trace [message #65277 is a reply to message #65275] |
Fri, 16 July 2004 01:42 |
Satheesh Babu.S
Messages: 35
Registered: July 2004
|
Member |
|
|
What for it is waiting. Since it's elasped time i am guessing it could be network problem. But can tell clearly once we the wait events.
Thanks and Regards,
Satheesh Babu.S
Bangalore.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
