I am trying to devise a strategy for efficient tracing of stored
procedures and triggers that is switchable at Oracle instance startup.
I know of the DBMS_PIPE package. I am currently using the UTL_FILE
package. We have a package and a set of stored procedures that we use
to print the source file name and source code revision number, line
number, variable name and value for numbers, strings and dates. We
print the parameter values, local variables, and return values for IN
OUT parameters.
Currently, we have about 15 or 20 or more calls per stored procedure
that dump tracing via UTL_FILE package to a single text file. We
believe that this has an unacceptable performance impact for two
reasons:
- every stored procedure writes at least 15 lines or more of tracing
to a text file
- stored procedure calls require additional CPU regardless of any
input/output performed within the database or operating system due to
additional call stack traversals (ie: calling a stored procedure, only
to return from it without any useful work performed)
We would like to make tracing switchable at Oracle instance startup.
- We could determine the switch value from the init.ora file. Using
DBMS_UTILITY.GET_PARAMETER_VALUE we could determine if utl_file_dir is
set or unset. However, modification of init.ora is a security,
instance integrity and availability concern.
- We could use the UTL_FILE package to determine the switch value from
an operating system file. We could embed this test in the lowest level
trace procedure. However, this would require that an operating system
file is read for every line of tracing. This is expensive.
- We could use an AFTER LOGON trigger to test the operating system
file upon creation of a session. The trigger would set a public
package variable to store the switch setting. This variable could be
referenced within any stored procedure or trigger. It could be used
explicitly within a stored procedure to skip over consecutive tracing
calls, such as at the top of a stored procedure where all the
parameters are dumped. The public package variable would be referenced
in the tracing procedure so that tracing is turned off. Each session
would write tracing via the UTL_FILE package. This means that when
tracing is enabled, every session has the bottleneck of writing to the
operating system text file. However, no such performance impact is
incurred if tracing is disabled. The only performance overhead when
tracing is turned off in this method is CPU-related from call stack
traversal and repeatedly testing the public package variable.
- We could use the DBMS_PIPE package to write all tracing on an Oracle
pipe. When tracing is enabled, a "daemon" process must read from the
pipe and write the output to an operating system file. This has the
advantage of queuing all operating system file output to a separate
Oracle session and maximizing the performance of Oracle sessions from
which tracing is being generated. This method seems to be the most
widely recommended. However, a daemon process must be started and
monitored for failure.
I have disqualified (a security) & (b expensive). Would anyone be able
to compare the merits of (c) & (d)?
--
--
Philip Bondi, Database Administrator, pjbondi_at_SystemDatabase.com
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Tue Aug 29 2000 - 15:51:03 CDT
