Re: Q: strategy for efficient tracing of stored procedures and triggers

On Tue, 29 Aug 2000 20:51:03 GMT, Philip J. Bondi <pjbondi_at_SystemDatabase.com> wrote:

>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:
>
>1. every stored procedure writes at least 15 lines or more of tracing
>to a text file
>2. 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.
>
>a. 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.
>
>b. 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.
>
>c. 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.
>
>d. 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)?
>
>--

Why on earth don't you use the standard tracing facilities? You are aware of them?

Regards,

Sybrand Bakker, Oracle DBA Received on Wed Aug 30 2000 - 00:57:49 CDT