Home » RDBMS Server » Performance Tuning » Rollback and trace/tkprof
Rollback and trace/tkprof [message #294907] Sun, 20 January 2008 13:09 Go to next message
psynaps3
Messages: 3
Registered: January 2008
Junior Member
Hi,

I have two similar flows for a test case - one which works as expected and the other which which does not. In both these flows, a rollback statement is called.

The only difference I can see from the trace/tkprof is that the flow which works has an execute pass for it.

For the flow that fails,
Trace:
PARSING IN CURSOR #76 len=24 dep=0 uid=44 oct=45 lid=44 tim=5735956337428 hv=269090973 ad='288a5888'
rollback to point1
END OF STMT
PARSE #76:c=0,e=202,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=5735956337425
WAIT #76: nam='SQL*Net break/reset to client' ela= 11 p1=1952673792 p2=1 p3=0
...

For the flow that works,
PARSING IN CURSOR #52 len=24 dep=0 uid=44 oct=45 lid=44 tim=5736121941127 hv=269090973 ad='288a5888'
rollback to point1
END OF STMT
PARSE #52:c=0,e=52,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=5736121941124
EXEC #52:c=10000,e=3603,p=0,cr=0,cu=462,mis=0,r=0,dep=0,og=4,tim=5736121944808
WAIT #52: nam='SQL*Net message to client' ela= 1 p1=1952673792 p2=1 p3=0
...

TKprof for working flow,
rollback to point1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 44

TKprof for the flow which fails,
rollback to point1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.00 0 0 462 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.00 0 0 462 0

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 44


Can someone help me with understanding if the rollback statement is actually getting fired or not. And if not, then why is the case? What exactly does the execute call signify for the rollback statement.

Many thanks.
Re: Rollback and trace/tkprof [message #294917 is a reply to message #294907] Sun, 20 January 2008 20:05 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Could it be that you are trying to rollback to a non-existent savepoint?

Are you trapping errors raised by the ROLLBACK?

Ross Leishman
Re: Rollback and trace/tkprof [message #295028 is a reply to message #294907] Mon, 21 January 2008 03:04 Go to previous messageGo to next message
psynaps3
Messages: 3
Registered: January 2008
Junior Member
The savepoint does exist. Besides the rollback call does not result in any error. Its as if the rollback didn't get executed at all even though it was called.
Re: Rollback and trace/tkprof [message #295031 is a reply to message #295028] Mon, 21 January 2008 03:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How can we say something with partial trace files omitting some important and with conflicting assertions.

Quote:
For the flow that works,
EXEC #52:c=10000,e=3603,p=0,cr=0,cu=462,mis=0,r=0,dep=0,og=4,tim=5736121944808

Quote:
TKprof for working flow,
total 1 0.00 0.00 0 0 0 0

Quote:
TKprof for the flow which fails,
total 2 0.01 0.00 0 0 462 0

The figures has been messed up.

Regards
Michel
Re: Rollback and trace/tkprof [message #296389 is a reply to message #294907] Sat, 26 January 2008 09:05 Go to previous message
psynaps3
Messages: 3
Registered: January 2008
Junior Member
@Michel

I am sorry, but I had messed up the tkprof.

It should read as,
TKprof for the flow which *works*,
total 2 0.01 0.00 0 0 462 0

Basically, all I need to understand is if the rollback statement is actually getting fired or not for the case where it fails. And if not, then why so? What exactly does the execute call signify for the rollback statement.

Many thanks...
Previous Topic: Insert using DB Link (merged)
Next Topic: Help- slow running sql
Goto Forum:
  


Current Time: Mon Dec 05 19:23:52 CST 2016

Total time taken to generate the page: 0.08908 seconds