Home » RDBMS Server » Performance Tuning » Performance of Update Statement
Performance of Update Statement [message #245913] Tue, 19 June 2007 04:20 Go to next message
chintu25
Messages: 4
Registered: June 2007
Location: Pune
Junior Member
Hi,

CAN ANYONE SUGGEST ME WHY FOLLOWING UPDATE STATEMENT TAKING FULL SCAN ON TXN_TEMP TABLE .

UPDATE TXN_TEMP SET USERCODE = 'BESPR', APPDATE = to_date('06/19/2007 11:11:07','mm/dd/yyyy hh24:mi:ss')where UID = (select UID from TXN_TEMP where objid='1418')

THERE WAS UNIQUE INDEX ON OBJID AND NONUNIQUE INDEX ON UID.

STILL I AM NOT GETTING CONSISTENT GETS LESS THAN 50.
Re: Performance of Update Statement [message #245921 is a reply to message #245913] Tue, 19 June 2007 05:33 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
1. Do you have stats collected for all tables?
2. How many distinct values has UID column?
3. How many rows are in both tables?
4. Post explain.

Michael
Re: Performance of Update Statement [message #246090 is a reply to message #245921] Tue, 19 June 2007 21:35 Go to previous messageGo to next message
chintu25
Messages: 4
Registered: June 2007
Location: Pune
Junior Member
A> Following are execution plan and statistics:

ecution Plan
--------------------------------------------------------
0 UPDATE STATEMENT Optimizer=CHOOSE (Cost=1 Card=2 Bytes=138)
1 0 UPDATE OF 'TXN_TEMP'
2 1 FILTER
3 2 PARTITION RANGE (ALL)
4 3 TABLE ACCESS (FULL) OF 'TXN_TEMP(Cost=1 Card=2
Bytes=138)

5 2 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'TXN_TEMP
(Cost=1 Card=1 Bytes=30)

6 5 INDEX (UNIQUE SCAN) OF 'IDX_TXNTEMP_OBJID' (UNIQUE
)

Statistics
--------------------------------------------------------
0 recursive calls
27 db block gets
826 consistent gets
0 physical reads
1044 redo size
855 bytes sent via SQL*Net to client
823 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
2 rows processed

B> In TXN_TEMP UID HAS 8904 DISTINCT UID

C> IN TXN_TEMP THERE ARE 8969 ROWS
Re: Performance of Update Statement [message #246100 is a reply to message #246090] Tue, 19 June 2007 22:26 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I think the problem is that your table is so small. 8000 rows will fit into a small number of blocks. With a FTS, Oracle can read many blocks at a time (DB_MULTI_BLOCK_READ_COUNT) so it may be able to read the entire table with one or maybe two round trips to the disk.

Index scans work differently - you don't get multi-block reads, so if Oracle thinks you will get 2 or more rows, it thinks it is a fair bet it will be doing multiple trips to the disk in addition to the multiple blocks it has to read from the index.

You can force index usage with a hint if you want, but it might not out-perform the FTS in real terms until the table grows significantly.

Ross Leishman
Re: Performance of Update Statement [message #246101 is a reply to message #246100] Tue, 19 June 2007 22:51 Go to previous messageGo to next message
chintu25
Messages: 4
Registered: June 2007
Location: Pune
Junior Member
But Other tables with less than 8000 rows are producing
consistent gets at max 10.

So , if there is a problem with FTS it must be problematic for other table also

Please elaborate what i should do in this case.

Thanks in Advance
Re: Performance of Update Statement [message #246105 is a reply to message #246100] Tue, 19 June 2007 23:37 Go to previous messageGo to next message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
Quote:
I think the problem is that your table is so small. 8000 rows will fit into a small number of blocks. With a FTS, Oracle can read many blocks at a time (DB_MULTI_BLOCK_READ_COUNT) so it may be able to read the entire table with one or maybe two round trips to the disk.


Is DB_MULTI_BLOCK_READ_COUNT some esoteric new parameter I haven't ever heard of or was it db_file_multiblock_read_count
that you were meaning, as I suspect?
Re: Performance of Update Statement [message #246116 is a reply to message #245913] Wed, 20 June 2007 00:48 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
I wrote once an article about such case (tuning update/delete statements with subqueries).
Look a http://www.jlcomp.demon.co.uk/faq/UPD_DEL_with_subq.html

HTH.
Michael
Re: Performance of Update Statement [message #246172 is a reply to message #246101] Wed, 20 June 2007 03:25 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Stats like Consistent Gets can be a guide in tuning, but your goal should be to make a statement faster, not reduce its consistent gets (or any other measure). Such a reduction may or may not help - there are lots of other things going on that need to be taken into account - don't get hung-up on one measure.

If its fast, leave it alone. If its slow, sure it may be because of the full scan. Try it with an index scan (using hints). If its not faster, try something else or leave it alone.

When you've established that another method is faster, try to work out why it chose the crappy method, or why it ignored the good method.

In your case, Oracle thinks a full table scan of TXN_TEMP will cost next-to-nothing (cost=1), which brings it out as the preferred access method over other plans. One possible explanation of such a low cost may be my theory above. If the cost is inaccurate (it is actually expensive to full scan the table) then gather stats and try again. If no joy, just go with hint and forget about it.

Also, it's pretty difficult to tell a lot from AUTOTRACE output, because the numbers shown include recursive SQL.

If you really want to know the IO caused directly by the statement and compare it to alternative plans, you should use TK*Prof.

Ross Leishman
Re: Performance of Update Statement [message #246181 is a reply to message #246172] Wed, 20 June 2007 03:47 Go to previous messageGo to next message
chintu25
Messages: 4
Registered: June 2007
Location: Pune
Junior Member
Thanks,

I have got d reason of slow performance.

Thanks for ur suggestion.

Can u Please Tell What is the meaning of this

TK*Prof



icon7.gif  Re: Performance of Update Statement [message #246222 is a reply to message #246181] Wed, 20 June 2007 05:57 Go to previous messageGo to next message
gaur_poonam
Messages: 20
Registered: April 2007
Location: India
Junior Member

hi,
just have a look at following given link for information regarding TKprof.....


http://www.orafaq.com/faq/what_tools_utilities_does_oracle_provide_to_assist_with_performance_tuning



cheers....
Re: Performance of Update Statement [message #246403 is a reply to message #245913] Wed, 20 June 2007 15:56 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
It would be nice to know the reason for bad performance as well.

Michael
Previous Topic: Want to use Alias
Next Topic: 10.1 much slower than 10.2? help!
Goto Forum:
  


Current Time: Fri Dec 02 22:51:24 CST 2016

Total time taken to generate the page: 0.09041 seconds