Home » RDBMS Server » Performance Tuning » Performance Tuning (Aix)
Performance Tuning [message #528223] Sun, 23 October 2011 05:11 Go to next message
sher297
Messages: 3
Registered: December 2009
Location: uae
Junior Member
Dear All,

thanks,

I have one problem , I have one expensive update statment.

update caps set curoutbaltotal = 0 where substr(crnno, 8, 2) <> '00';

Problem:
If i execute first time, it will execute within 1min. If I execute the same query 2nd time, it will take 10-15min.

Workaround: before runing the update query 2nd time, I flush the shared_pool and db_cache, and than run the query it worked fine.

It means once alloact memory to db_cache not freeing the space for next needed blocks...

Please need your help to solve it.



Thanks

Re: Performance Tuning [message #528225 is a reply to message #528223] Sun, 23 October 2011 05:32 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Hello. For this sort of problem, you must provide a lot more information.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Also always post your Oracle version, with 4 decimals.

Now to be more specific: The most obvious explanation is that you are getting a different execution plan for the second run. Please can you do this in SQL*Plus:

select * from v$version;
set timing on
set autotrace on
update caps set curoutbaltotal = 0 where substr(crnno, 8, 2) <> '00';
update caps set curoutbaltotal = 0 where substr(crnno, 8, 2) <> '00';
set timing off
set autotrace off


That will give the necessary information to start investigations.
Use copy/paste to post the entire SQL*Plus session, and you MUST format it with [code] tags.
Re: Performance Tuning [message #528274 is a reply to message #528225] Mon, 24 October 2011 01:17 Go to previous messageGo to next message
sher297
Messages: 3
Registered: December 2009
Location: uae
Junior Member
Hi,

Thanks, please find the updates.
Database version is 11.2.0.2

sql>update caps set curoutbaltotal = 0 where substr(crnno, 8, 2) <> '00';

61943 rows updated.

Elapsed: 00:04:49.90

Execution Plan
----------------------------------------------------------
Plan hash value: 1935097324

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 30140 | 941K| 54845 (1)| 00:10:59 |
| 1 | UPDATE | CAPS | | | | |
|* 2 | TABLE ACCESS FULL| CAPS | 30140 | 941K| 54845 (1)| 00:10:59 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter(SUBSTR("CRNNO",8,2)<>U'00')


Statistics
----------------------------------------------------------
456 recursive calls
371202 db block gets
645768 consistent gets
324271 physical reads
44369716 redo size
531 bytes sent via SQL*Net to client
379 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
61943 rows processed

sql>update caps set curoutbaltotal = 0 where substr(crnno, 8, 2) <> '00';

61943 rows updated.

Elapsed: 00:01:07.73

Execution Plan
----------------------------------------------------------
Plan hash value: 1935097324

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 30140 | 941K| 54845 (1)| 00:10:59 |
| 1 | UPDATE | CAPS | | | | |
|* 2 | TABLE ACCESS FULL| CAPS | 30140 | 941K| 54845 (1)| 00:10:59 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter(SUBSTR("CRNNO",8,2)<>U'00')


Statistics
----------------------------------------------------------
54 recursive calls
297385 db block gets
563924 consistent gets
324343 physical reads
19833672 redo size
554 bytes sent via SQL*Net to client
379 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
61943 rows processed

[Updated on: Mon, 24 October 2011 01:24]

Report message to a moderator

Re: Performance Tuning [message #528285 is a reply to message #528274] Mon, 24 October 2011 02:00 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
The elapsed time for the second query is much faster there than the first and I'd expect that from the data put out by the autot).

Also, although those xplans are easy to read in that format, that's rarely the case - please put them in code tags.
Re: Performance Tuning [message #528286 is a reply to message #528274] Mon, 24 October 2011 02:05 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Your output is unreadable, because you didn't format it with code tags as I asked you to. Please do that next time.

However, it is obvious that the timings are very different from those stated in your first post. You have 4:49 then 1:07. You said 1m then 10m to 15m. So I don't know what your problem is, apart from an inability to say "thank you".

(update by JW: I see that you have added a "thank you" and the release information to your reply. Good move. Smile )

[Updated on: Mon, 24 October 2011 02:11]

Report message to a moderator

Re: Performance Tuning [message #528649 is a reply to message #528286] Wed, 26 October 2011 04:53 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
1) it would appear that your query updates the same rows over and over since you don't change any of the data used to identify rows. I might suggest you alter you query a little to something like this:

update caps set
        curoutbaltotal = 0
where substr(crnno, 8, 2) <> '00'
AND NVL(curoutbaltotal,1) != 0
/


This way you won't be updating rows that do not need updating.

2) in your example it appears you run the same update in the same transaction twice. It is not clear if you do a commit anywhere between the first and second beacuse if you do or not, the nature of what you are doing changes and lead us to #3.

3) I can think of these reasons whay the update second time around might take longer that the first time:

.....A) You may be experiencing locking if someone else has updated one of your rows before you got to it. In which case you would hang till the other transaction either commited or rolled back.

.....B) on EXADATA you might be doing parallel DML and you could be queued waiting for parallel servers. Does not happen often but can happen if your box is busy. You can try downgrading the query or increaseing parallel servers if you have capacity.

.....C) the second query is updating lots more rows because someone added or changed rows to meet your criteria between your updates.

.....D) how you tested was somehow flawed and you got erroneous results.

But I agree with others, you have not shown any evidence that your second update is actually slower than the first. This suggests that your issue is D.

Kevin
Previous Topic: executions
Next Topic: DECODE in WHERE CLAUSE Performance
Goto Forum:
  


Current Time: Thu Mar 28 05:05:10 CDT 2024