| TKPROF & Statspack question [message #204240] |
Sun, 19 November 2006 15:41  |
MajorGlory
Messages: 4
Registered: March 2006
Location: Lodz, Poland
|
Junior Member |
|
|
Hi there!
I've experimenting with TKPROF & Statspack utilities and I've encountered 2 issues that puzzle me - hope you can shed some light on them for me. It's a test configuration, nothing fancy.
I. Pure TKPROF
Here I issue a humble update statement, observe tkprof's report (truncated to what I believe are the relevant elements):
UPDATE TBL SET
DESCRIPTION_1 = 'E..E'
WHERE Description_2 LIKE '%567%'
call count rows
------- ------ ----------
Parse 1 0
Execute 1 12800
Fetch 0 0
------- ------ ----------
total 2 12800
OK, the query indeed updates 12800 rows, so that works out, but...
Rows Execution Plan
------- ---------------------------------------------------
0 UPDATE STATEMENT MODE: ALL_ROWS
0 UPDATE OF 'TBL'
25850 TABLE ACCESS (FULL) OF 'TBL' (TABLE)
... why does the execution plan say 25850 rows?!
II. TKPROF vs Statspack
I'm running the same query and observe the output in TKPROF and statspack. TKPROF says:
call count cpu elapsed
------- ------ -------- ----------
Parse 1 0.00 0.00
Execute 1 0.00 0.00
Fetch 6401 12.48 28.87
------- ------ -------- ----------
total 6403 12.48 28.87
And Statspack claims:
Elapsed Elap per CPU
Time (s) Executions Exec (s) Time (s)
---------- ------------ ---------- ----------
28.81 1 28.81 24.16
The elapsed value is similar in both cases and realistic, but I'm interested in the reported differnce of CPU Times. Where does it come from?
Any ideas or should I paste more of the reports (if so, let me know which parts)?
Thanks,
MG
|
|
|
|
|
|
| Re: TKPROF & Statspack question [message #204348 is a reply to message #204240] |
Mon, 20 November 2006 04:27  |
MajorGlory
Messages: 4
Registered: March 2006
Location: Lodz, Poland
|
Junior Member |
|
|
Okay, fair enough.
However, to resolve the issue completely, I should add this extra information: the TBL table has 128 000 rows - is there any reason why particular 25850 rows would be chosen by a full table scan?
BR,
MG
|
|
|
|
|
|
| Re: TKPROF & Statspack question [message #204460 is a reply to message #204366] |
Mon, 20 November 2006 20:16 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Statistics should not matter for TK*Prof - it shows what actually happened, not what Oracle thinks will happen.
Look, it's not that I don't trust you, but I do not have any experience of the rowcounts in TK*Prof being anything but dead accurate.
Is it possible that you did not close your trace or exit the session, and perhaps the trace had not finished writing?
Is it possible that your DBA has a maximum file size on .trc files, so the trace was truncated?
Ross Leishman
|
|
|
|
| Re: TKPROF & Statspack question [message #206110 is a reply to message #204460] |
Tue, 28 November 2006 16:54 |
orasaket
Messages: 70
Registered: November 2006
|
Member |
|
|
Hi,
One query here?
" I should add this extra information: the TBL table has 128 000 rows "
12,800 or 128000?
Is anything related to HWM?
Say direct path insert has been done which has reset the HWM with total rowcount as 25850
Then some DML was done after which this update took place?
I may be wrong but just taking an opportunity for clearing my query.
Thanks in Advance,
Saket
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
