| Performance of Update Statement [message #245913] |
Tue, 19 June 2007 04:20  |
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 #246090 is a reply to message #245921] |
Tue, 19 June 2007 21:35  |
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 |
rleishman
Messages: 3728
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 |
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 |
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 #246172 is a reply to message #246101] |
Wed, 20 June 2007 03:25 |
rleishman
Messages: 3728
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
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
