I have the following stats about sorts.
NAME RUN1 RUN2
DIFF
-------------------------------------------------- ---------- ----------
----------
STAT...sorts (rows) 183031 182958
-73
RUN1 is also worse in sorts. I get the query to get lower number of
blocks but i get an increase in other areas (pysical reads, latches,
sorts). So the elapsed time increases.
-----Original Message-----
From: Martic Zoran [mailto:zoran_martic_at_yahoo.com]=20
Sent: Thursday, February 03, 2005 10:53 AM
To: Yasin Baskan; cary.millsap_at_hotsos.com; oracle-l_at_freelists.org
Subject: RE: cpu time and query column in tkprof output
Hi Yasin,
I said earlier that the CPU time is depending on many
things (that is why you have the COST of the SQL that
is optimizer providing to you trying the best he can
:)
Lets look only the CPU time and not bother about
latches and other possible waits causing elapsed time
to be far from the CPU time.
You said about 5 sorts are done in both SQL's.
But how much time each sort consumed? No way to know
:)
You need to think that probably the sorts in the first
and the second SQL are not the same at all.
If the second SQL needed to sort 10000 rows and the
first SQL 100 you will have 1 sort per each SQL, but
more CPU time spent on the second SQL.
Or if the type of sort is different, different
algorithms.
You need to look the explain plan for both SQL's that
are giving you totally different execution plan with
sorts on different things.
DIFFERENT AMOUNT OF SORTS =3D DIFFERENT CPU TIME
DIFFERENT TYPE OF SORTS =3D DIFFERENT CPU TIME
For example what do you have in stats for:
sorts (rows)
Probably other stats should count too.
These things you do not have in trace file, even with
10046 event.
Regards,
Zoran
- Yasin Baskan <yasbs_at_kocbank.com.tr> wrote:
> I looked at the latch usage in an isolated
> environment, but they do not
> seem to be parallel to the number of buffer gets.
>=20
> The one with lower buffer gets resulted in 5913
> hsecs, the one with
> higher buffer gets resulted in 825 hsecs for 100
> executions of each.
>=20
> Latch numbers are also worse for the one with lower
> buffer gets. RUN1 is
> the one with lower buffer gets,RUN2 is the one with
> higher buffer
> gets.As you see latch usage numbers for RUN1 are
> worse.
>=20
> NAME =20
> RUN1 RUN2
> DIFF
> --------------------------------------------------
> ---------- ----------
> ----------
> LATCH.job_queue_processes parameter latch =20
> 1 0
> -1
> LATCH.ktm global data =20
> 0 1
> 1
> LATCH.ncodef allocation latch =20
> 1 0
> -1
> LATCH.transaction branch allocation =20
> 1 0
> -1
> LATCH.session switching =20
> 1 0
> -1
> LATCH.loader state object freelist =20
> 204 202
> -2
> LATCH.longop free list =20
> 2 0
> -2
> LATCH.virtual circuit queues =20
> 3 0
> -3
> LATCH.event group latch =20
> 12 4
> -8
> LATCH.process allocation =20
> 12 4
> -8
> LATCH.channel handle pool latch =20
> 24 8
> -16
>=20
> NAME =20
> RUN1 RUN2
> DIFF
> --------------------------------------------------
> ---------- ----------
> ----------
> LATCH.process group creation =20
> 24 8
> -16
> LATCH.session timer =20
> 19 3
> -16
> LATCH.library cache load lock =20
> 20 0
> -20
> LATCH.Token Manager =20
> 23 0
> -23
> LATCH.channel operations parent latch =20
> 36 12
> -24
> LATCH.sequence cache =20
> 36 12
> -24
> LATCH.user lock =20
> 48 16
> -32
> LATCH.active checkpoint queue latch =20
> 40 6
> -34
> LATCH.multiblock read objects =20
> 200 2
> -198
> LATCH.sort extent pool =20
> 402 203
> -199
> LATCH.session allocation =20
> 322 82
> -240
>=20
> NAME =20
> RUN1 RUN2
> DIFF
> --------------------------------------------------
> ---------- ----------
> ----------
> LATCH.redo writing =20
> 712 351
> -361
> LATCH.checkpoint queue latch =20
> 598 164
> -434
> LATCH.session idle bit =20
> 746 166
> -580
> LATCH.messages =20
> 1292 590
> -702
> LATCH.list of block allocation =20
> 2254 1254
> -1000
> LATCH.transaction allocation =20
> 2871 1573
> -1298
> LATCH.dml lock allocation =20
> 6306 3506
> -2800
> LATCH.undo global data =20
> 7316 4231
> -3085
> LATCH.redo allocation =20
> 7445 4253
> -3192
> LATCH.shared pool =20
> 9310 4959
> -4351
> LATCH.enqueue hash chains =20
> 12333 6826
> -5507
>=20
> NAME =20
> RUN1 RUN2
> DIFF
> --------------------------------------------------
> ---------- ----------
> ----------
> LATCH.cache buffers lru chain =20
> 32883 25895
> -6988
> LATCH.enqueues =20
> 16234 8912
> -7322
> LATCH.row cache objects =20
> 57411 32360
> -25051
> LATCH.library cache =20
> 100992 55926
> -45066
> LATCH.cache buffers chains =20
> 811950 595768
> -216182
>=20
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of
> Cary Millsap
> Sent: Wednesday, February 02, 2005 4:45 PM
> To: oracle-l_at_freelists.org
> Subject: RE: cpu time and query column in tkprof
> output
>=20
> Yasin,
>=20
> A couple of things to consider:
>=20
> 1. Don't forget that Oracle gets its CPU consumption
> information from
> getrusage() (or times(), depending on the port),
> which on most systems
> acquires the rusage data by polling. See Optimizing
> Oracle Performance =3D
> =3D3D
> pages
> 161-165 for the details. Thus, when you have small
> CPU consumption =3D3D
> numbers
> (say, less than half a second), you can't really
> trust what you're =3D3D
> seeing in
> a single sample measurement. You'll get a better
> idea of what's really
> happening either by enabling microstate accounting
> (if you're using
> Solaris), or by taking several sample measurements
> for the same =3D3D
> statement.
>=20
> 2. Don't forget to look at latch acquisition
> numbers, too, like Tom =3D3D
> Kyte's
> runstats or our OP101 course tool kit gives you. The
> latch acquisition =3D
> =3D3D
> count
> is a high-quality indicator of how well the
> statement=20
=3D=3D=3D message truncated =3D=3D=3D
=09
__________________________________=20
Do you Yahoo!?=20
Meet the all-new My Yahoo! - Try it today!=20
http://my.yahoo.com=20
=20
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Feb 03 2005 - 04:32:14 CST
