| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Thanks: queries too slow
Hi All,
Thanks to all who replied, it's solved now. The
problem was: datatype for SKU is varchar, while the
bind variable datatype in UPDATE and SELECT statement
was number, so although execution plan said it's going
to use 'INDEX UNIQUE SCAN', i think it actually did
full table scan. I changed the comparison from
where sku = :b7
to sku = to_char(:b7)
It's pretty fast now(whole script run time has come
down from > 20 min to 15 sec for same no of rows)
Thanks again :),
Prakash
New Stats:
UPDATE EC_PRDITM SET DSCR=:b1 || ', ' || :b2 ,PRDITMNAME=:b1 || ', ' || :b2
|| '; ' || :b5 || ', ' || :b6
WHERE
SKU = TO_CHAR(:b7) AND DBSTS = 'A'
call count cpu elapsed disk query current rows
Parse 1 0.00 0.00 0 0 0 0 Execute 156 0.29 1.84 44 624 312 156 Fetch 0 0.00 0.00 0 0 0 0
total 157 0.29 1.84 44 624 312 156
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer goal: CHOOSE
Parsing user id: 31 (SDOSTLO20) (recursive depth:
1)
Rows Execution Plan
0 UPDATE STATEMENT GOAL: CHOOSE
0 UPDATE OF 'EC_PRDITM'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'EC_PRDITM_SKU_UK'
(UNIQUE)
********************************************************************************
SELECT PRDITMID
FROM
EC_PRDITM WHERE SKU = TO_CHAR(:b1) AND DBSTS = 'A'
call count cpu elapsed disk query current rows
Parse 1 0.00 0.00 0 0 0 0 Execute 156 0.00 0.00 0 0 0 156 Fetch 156 0.01 0.01 0 624 0 156
total 313 0.01 0.01 0 624 0 312
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 31 (SDOSTLO20) (recursive depth:
1)
Rows Execution Plan
0 SELECT STATEMENT GOAL: CHOOSE
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX
ROWID) OF 'EC_PRDITM'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'EC_PRDITM_SKU_UK'
(UNIQUE)
********************************************************************************
--- Gaja Krishna Vaidyanatha <gajav_at_yahoo.com> wrote:
> Steve, > > Comments embedded. > > Gaja. > > --- Steve Boyd <pimaco_oracle_at_yahoo.com> wrote: > > And after all that, what is your recommendation to > tune the > > query? > > > Pretty much what you have outlined here. Without > having a > chance to play with the query and investigating > everything else > that is related in the environment, it is difficult > to make > application tuning recommendations. The point I was > trying to > make in my earlier posting is that "the db buffer > cache hit > ratio" is not where we should be spending our time. > > > OK, here is a shot at remote application tuning: > > Cut and paste from original posting > ----------------------------------------------------------------- > UPDATE EC_PRDITM SET DSCR=:b1 || ', ' || :b2 > ,PRDITMNAME=:b1 || ', ' || :b2> > > || '; ' || > :b5 || ', ' > || :b6 > WHERE SKU = :b7 AND DBSTS = 'A'; > > call count cpu elapsed disk > query current rows > ------- ------ -------- ---------- ---------- > ---------- ---------- ---------- > Parse 1 0.00 0.00 0 > 0 0 0 > Execute 157 475.75 508.74 1441954 > 1453090 793 157 > Fetch 0 0.00 0.00 0 > 0 0 0 > ------- ------ -------- ---------- ---------- > ---------- ---------- ---------- > total 158 475.75 508.74 1441954 > 1453090 793 157 > > Misses in library cache during parse: 1 > Misses in library cache during execute: 1 > Optimizer goal: CHOOSE > > Parsing user id: 31 (SDOSTLO20) (recursive depth: > 1) > Rows Execution Plan ------- > --------------------------------------------------- > 0 UPDATE STATEMENT GOAL: CHOOSE > 0 UPDATE OF 'EC_PRDITM' > 0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF > 'EC_PRDITM_SKU_UK' (UNIQUE)>
> > > SELECT PRDITMID FROM EC_PRDITM WHERE SKU = :b1 AND > DBSTS = > 'A'; > > call count cpu elapsed disk > query current rows > ------- ------ -------- ---------- ---------- > ---------- ---------- ---------- > Parse 1 0.00 0.00 0 > > 0 0 0 > Execute 157 0.01 0.01 0 > > 0 0 157 > Fetch 157 373.98 393.26 1441790 > 1453035 471 157 > ------- ------ -------- ---------- ---------- > ---------- ---------- ---------- > total 315 373.99 393.27 1441790 > 1453035 471 314 > > > Misses in library cache during parse: 1 > Optimizer goal: CHOOSE > Parsing user id: 31 (SDOSTLO20) (recursive > depth:1) > > Rows Execution Plan ------- > --------------------------------------------------- > 0 SELECT STATEMENT GOAL: CHOOSE > 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX> > ROWID) > OF 'EC_PRDITM' 0 INDEX GOAL: ANALYZED > (UNIQUE SCAN) > OF 'EC_PRDITM_SKU_UK' (UNIQUE) > ----------------------------------------------------------------- > > OK, so we have an UPDATE and a SELECT. It looks > like this is > being called from some kind of iterative block of > code. The > UPDATE and the SELECT both are executed 157 times. > > The per execution statistics for the UPDATE is : > 1) CPU - 3.03 secs > 2) CPU Elapsed - 3.24 secs > 3) Physical I/O - 9184 blocks > 4) Logical I/O - 9260 blocks > 5) Rows updated - 1 > > The per execution statistics for the SELECT is : > 1) CPU - 2.38 secs > 2) CPU Elapsed - 2.50 secs > 3) Physical I/O - 9183 blocks > 4) Logical I/O - 9258 blocks > 5) Rows selected - 1 > > Total number of blocks in Table (per previous > posting) - 9255 > > Need I say more...;-) > > Some things to try out: > > a) Re-write the application to get rid of the > "iterative > nature" (if possible). It is ludicrous to perform > an entire > table worth of I/O each time to update/select 1 row. > > b) Try to perform the UPDATE one shot on the entire > table by > feeding the WHERE clause of the UPDATE by the > sub-query > (SELECT). > c) Create a concatenated index on > (SKU,DBSTS,PRDITMID), analyze > the index and re-run query. > d) If a-c does not help, go down on your knees and > pray that > you will not have to run this very often...;-) > > > > I do agree with you. I have seen plenty of > queries that were > > doing massive index scans, and were > > returning most of the rows. They had a great > hit-rate, but > > would have run much faster if they > > would have done a full table scan. > > > Thank you, as you mentioned full table scan has its > place. > > > Anyway, I'm sure the problem with the query is > something like > > it is using the wrong index, or > > there isn't a good index to use, or maybe even > that it should > > be doing a full table scan. > > Experiment, and find what works the best. > > > Bingo...hope the concatenated index helps. > > Cheers, > > Gaja. > > > --- Gaja Krishna Vaidyanatha <gajav_at_yahoo.com> > wrote: > > > Friends, > > > > > > Here we go again, the false correlation between > db cache hit > > > ratio and its effect on performance. We just > got done with > > a > > > similar thread a couple of weeks ago. Even > without seeing > > > anything in Prakash's environment, I would > probably guess > > that > > > the current size of the database buffer cache > may be OK. A > > 79% > > > cache hit ratio is by no means "WAY too low". > WAY too low > > > compared to what - 99%? Is there any guarantee > that at 99% > > > cache hit ratio that the queries will be > screaming? I > > strongly > > > doubt it.
 |
 |