RE: cpu time and query column in tkprof output
- From: "Karen Morton" <karen.morton@xxxxxxxxxx>
- To: <yasbs@xxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 3 Feb 2005 11:26:23 -0800
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
