Home » RDBMS Server » Performance Tuning » TKPROF & Statspack question
TKPROF & Statspack question [message #204240] Sun, 19 November 2006 15:41 Go to next message
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 #204256 is a reply to message #204240] Sun, 19 November 2006 21:31 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The FULL TABLE SCAN of TBL read 25850 rows, of which only 12800 were LIKE '%567%'.

I don't know why the CPU times differ.

Ross Leishman
Re: TKPROF & Statspack question [message #204348 is a reply to message #204240] Mon, 20 November 2006 04:27 Go to previous messageGo to next message
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 #204366 is a reply to message #204348] Mon, 20 November 2006 06:02 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Where the statistics on tables/indexes updated lately?
Re: TKPROF & Statspack question [message #204460 is a reply to message #204366] Mon, 20 November 2006 20:16 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous message
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
Previous Topic: # VERY URGENT # -Monitoring memory utilization in oracle 9i
Next Topic: Hierarchical queries with rownum pseudocolumn in 10g vs. 9i
Goto Forum:
  


Current Time: Wed Dec 07 11:00:48 CST 2016

Total time taken to generate the page: 0.16197 seconds