Home » RDBMS Server » Performance Tuning » Problem in Trace
Problem in Trace [message #65272] Wed, 14 July 2004 20:49 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.
how to convert inr to pcx format [message #65279 is a reply to message #65273] Sat, 17 July 2004 04:45 Go to previous message
venkatram
Messages: 1
Registered: July 2004
Junior Member
an image which is stored in .inr format should be changed to .pcx
oracle 7.3.4 is used here
how can convert plz help me
Previous Topic: EXECUTE IMMEDIATE Command (Oracle 8i)
Next Topic: what is perfomance tunning in oracle?
Goto Forum:
  


Current Time: Thu Mar 28 13:47:45 CDT 2024