RE: cpu time and query column in tkprof output

From: Karen Morton <karen.morton_at_hotsos.com>
Date: Thu, 3 Feb 2005 11:26:23 -0800
Message-ID: <026001c50a26$43243ad0$6501a8c0@Dumbledore>

I'm just catching up on this thread and wanted to throw in my 2 cents = worth. This topic
is mainly about how to "know" which way to write a query is best (i.e. = which form provides
the best scalability as well as best overall performance). It is an = intriguing, important
and difficult topic....so much so that I spend several hours addressing = it in the
Optimizing Oracle SQL course we offer as part of our Hotsos education = curriculum.

Remember that your test results may differ from what you would see in = production due to
differences in sessions, data volume, data distribution, statistics, = index/table/view
definitions, synonyms and more. Also, as someone mentioned earlier, = latch statistics are
counted globally so that when you are testing, you need to realize that = the latch stats
you capture include all latching occurring during your test...not just = latches your test
query needs to acquire. If you are the only user in the instance, the = influence of other
user sessions on latching is not as much of an issue as when you're = testing on an instance
with other users. I don't recall reading anywhere exactly what your = test environment is,
but just keep the differences between your test and production = environments in mind as you
attempt to extrapolate meaning from your test results.

How many tests did you run? I'd advise that you run the same test = multiple times in order
to mitigate the "overhead" effects from things such as other sessions, = recursive SQL,
memory allocations, etc. If you have found that the results you posted = are the average of
many tests, that gives the numbers you're seeing a bit more weight in my = mind. I have ran
the same query test hundreds of times and seen wide swings in latching, = cpu and elapsed
time across the different executions.

But, the bottom-line of your question is which query is "better"? Given = the two original
queries you presented and the tkprof output you provided, I'd say that = you could try
another way to write it and likely get even better results. :) If I = had to pick between
the two, without more info, I'd likely go with the second. But if you = want to try another
way of writing it, perhaps using analytics would help. Something like:=20

select count(*)
from=20

	(
	select a.iteration,=20
              max(a.iteration) over (partition by a.jobno) as =
max_iteration,
	  from faxutil.t_kfs_actions a,=20
              faxutil.t_kfs_jobs j,=20
              faxutil.t_kfs_tabstatuses c
	 where a.pool_id =3D :poolname
	   and j.no =3D a.jobno
	   and c.tabno =3D :ptabno
	   and c.pool_status =3D a.status
	   and c.job_status =3D j.status=20
	) q

where iteration =3D max_iteration
/

Karen Morton
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events at http://www.hotsos.com/education/schedule.html

=20

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Feb 03 2005 - 14:29:08 CST