| |
 |
|
When to use Oracle 10046 Trace
Oracle Tips by
Robert Freeman |
Note: Here are related Oracle
10046 trace file notes:
Shortcomings of the 10046 Event
So, the 10046 event has some very
interesting sounding information in it, doesn't it? We can track the
execution of a whole session, find out what the bind variables being
used are, we can track and aggregate wait times and so on. So what
is the problem? Let's just use it all the time.
Wrong. First of all,
interpretation of a 10046 trace file can take time, even if you run
it through TKPROF and take the canned results it gives you. Culling
through a 10046 with hundreds of SQL statements, and the related
recursive SQL, parse, execute, fetch, wait and stat records is not a
fun thing. While we provide a little tool at the end of this paper
to try to help you do so, it's still not something that you want to
be doing every day.
So, if we don't use the 10046 all
the time, when do we use it? Here is my point of view. If you are a
consultant coming in to fix a system that has a number of global
performance problems, the first thing to do is to actually look over
the database/instance from a top down approach.
Look at the memory
allocations, look at parameters like db_file_multiblock_read_count
and quickly sanity check them.
Most experienced DBAs can quickly tell if a parameter is set
completely out of whack. I've had a few engagements where one
parameter was totally and completely set wrong and it just almost
immobilized the entire system (for example, databases setup using
the old default Oracle init.ora files with the small or medium SGA
settings in place). This is something that you can quickly do as a
DBA, and reap some potential low cost rewards for your client.
After checking the database, run a
few statspack (or AWR if I'm running Oracle Database 10g, any
reference to Statspack should also include AWR) reports. I like to
run them during normal load, and during reported problem times. I'll
generally run snaps 15 minutes apart for an hour or two. Statspack
provides a wealth of information that can, again, point to some
immediate problems that stick out like a sore thumb.
It might be
that materialized view that takes 45 minutes to refresh (and was
originally scheduled to run every 15 minutes because at one time it
only took an hour to run). You might find some pitifully bad SQL
statements that clearly need help. Latching issues, IO contention,
are all easily detected by statspack without having to generate
10046 trace files, and in a fairly short amount of time. Also, when
reviewing statspack, look at the system statistics. How many rows
are gotten through index lookups vs. full table scans? If there is
an imbalance, you might need to look at indexing opportunities.
10046 traces can be troublesome in
environments that use connection pooling. Particularly when a single
user experience can float over several connections. I've seen
application code that does database disconnect/connect operations
while the user is still active, or even during batch processing
operations. This makes finding the problem very hard, 10046 or no.
Related to connection pooling
issues are issues with RAC clusters where sessions will switch
between different nodes of the cluster during the same process.
Also, I've had issues when a single record flows through a system
very quickly, and it is passed from process to process rapidly. Each
process creates a new connection, to a new node if you are running
RAC. This makes 10046 tracing difficult.
As you might expect, 10046 tracing
can have performance impacts, and depending on the load profile of
the system, and the number of sessions you are tracing, you can
cause serious problems. This is, perhaps, the Oracle interpretation
of Heisenbergs Uncertainty principle. The more we try to figure out
what our system is doing, the more we really can't tell because we
start to impact the system itself with our observation.
The Ion tool is
the easiest way to analyze Oracle load profiles and Ion
allows you to spot hidden performance trends.
When do I start running 10046
events then? I run them when a process is having problems that can
be identified, and if I can control which session (or sessions) need
to be traced without impacting other things. When users can clearly
point to processes that need help, and I cant clearly detect
wayward SQL via other means (statspack, v$sqlarea, etc) I'll start
running 10046 traces.
Click here
to read next section
Click here
to read previous section
 |
If you like Oracle tuning, see the book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
|
|
|
and include the URL for the page.
