Oracle PLSQL Language- P27

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

Thêm vào BST

Báo xấu

73
lượt xem 6
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Tham khảo tài liệu 'oracle plsql language- p27', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Oracle PLSQL Language- P27

THEN IF right_now < hire_date THEN hdate := right_now; ELSIF ADD_MONTHS (right_now, -120) > hire_date THEN MESSAGE (' Hire date ' || TO_CHAR (hire_date, 'MM/DD/YY') || ' more than ten years past.'); RAISE FORM_TRIGGER_FAILURE; END IF; END IF; END; The performance gain from each of these conversions may be slight. If they were the only tuning steps you took (or the only ones left to take) in your PL/SQL programs, I doubt that you would notice a difference. Combined with all the other tuning tips, however, avoidance of host variables in PL/ SQL blocks will contribute to a more efficient PL/SQL layer in your application. 25.4.4 Use Package Data to Avoid Passing "Bulky" Parameter Values With the exception of cursor variables, PL/SQL passes arguments by value, instead of reference, in the parameter lists of procedures and functions. If the argument can be changed (i.e., it is an OUT or IN OUT parameter), then the runtime engine makes a local copy of your structure and applies changes to that copy. If the program terminates without error, the local data is copied to your structure, which is then returned to the calling program. This approach preserves the values of actual parameters against the possibility of program failure, but it introduces some potential performance problems. This is particularly the case when your OUT or IN OUT parameters are complex data structures such as records and PL/SQL tables. Suppose that a record has 15 columns in it. Every time you pass that record into a procedure or function as an OUT or IN OUT parameter, PL/SQL copies the entire record into an internal record structure, column by column. Suppose that a PL/SQL table has 100 rows defined in it. Every time you pass that table into a procedure or function as an OUT or IN OUT parameter, PL/SQL copies the entire table into an internal table structure, row by row. Suppose, now, that you have a record with 15 columns in it, three of which are PL/SQL tables, each with 100 rows. Then, every time you pass that record into a procedure or function as an OUT or IN OUT parameter, PL/SQL copies the entire record into an internal record structure, column by column and row by row -- for a total of 312 copies! And if that procedure calls another procedure, passing the record down to that inner procedure, well, PL/SQL executes the same copy process within the second procedure. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
As you can easily imagine, this copying could consume a noticeable amount of memory and CPU cycles in your application. So should you avoid using records and PL/SQL tables as parameters in programs? Absolutely not! These advanced, multi-component data structures are powerful constructs in your program -- you definitely should take advantage of them. However, you should be aware of the kinds of problems you might encounter when using these kinds of parameters. And you should be ready to implement a workaround if you find that performance in your application is dragged down as a result of record or PL/SQL table copying. The workaround for this problem is straightforward enough: don't pass the record or table as a parameter. But then how can you get your data inside your program? Replacing a record with a parameter list of 20 different parameters isn't really an answer, since PL/SQL will continue to execute 20 copies to internal variables. No, the answer is to stop PL/SQL from executing the copies altogether. To accomplish this, make use of package-level data, as explained in Chapter 16, Packages. Here's an example of the steps you could take to avoid passing these data structures as parameters. Suppose you have a procedure which accepts both a record and a PL/SQL table as parameters, as follows: PROCEDURE massage_mucho_data (io_columns_galore_rec IN OUT columns_galore%ROWTYPE, io_lotsarows_tab IN OUT lotsarows_tabtype); To use this procedure, I first declare and populate both the row and table. Then, I call the procedure: DECLARE galore_rec columns_galore%rowtype; TYPE lotsarows_tabtype IS TABLE OF VARCHAR2 INDEX BY BINARY_INTEGER; my_table lotsarows_tabtype; BEGIN fill_table (my_table); massage_mucho_data (galore_rec, my_table); END; As PL/SQL executes the statements in massage_mucho_data, the contents of the record and PL/SQL table are copied into local structures. Upon successful termination of the program, the data in those local structures is copied back to galore_rec and my_table. If, on the other hand, I create a package with the record and table types and instances in the specification as shown below, those data structures are "global" in my session. I can manipulate them directly in any procedure or function without passing them into those programs as parameters. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
PACKAGE mucho_data_pkg IS /* Declare the record in the package specification. */ galore_rec columns_galore%rowtype; /* Define the table structure and declare the table. */ TYPE lotsarows_tabtype IS TABLE OF VARCHAR2 INDEX BY BINARY_INTEGER; my_table lotsarows_tabtype; END mucho_data_pkg; After I populate the table, I can call the revised version of massage_mucho_data -- which no longer has any parameter at all! BEGIN fill_table; massage_mucho_data; END; I do not need to declare the package table. Nor do I need to declare the record. I simply modify the internals of massage_mucho_data to append the package name "mucho_data_pkg" to the names of the record and PL/SQL table. In other words, whereas a line in the original massage_mucho_data procedure might have looked like this: FOR row_count IN 1 .. 100 LOOP io_lotsarows_tab (row_count) := row_count ** 2; END LOOP; this same loop in the new, parameter-less version of massage_mucho_data would appear as follows: FOR row_count IN 1 .. 100 LOOP mucho_data_pkg.my_table (row_count) := row_count ** 2; END LOOP; I no longer pass the complex data structures as parameters. I reference them directly in the procedure. PL/SQL no longer copies the values to internal variables. There are, by the way, a number of downsides to globalizing your data as shown above. When you use global data structures, you increase the dependencies between modules in your system. The massage_mucho_data is no longer a black box, completely independent of other elements of the Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
application. Instead, it requires the data structures in the mucho_data_pkg. When I passed in the table as a parameter to fill_table, I gave myself the flexibility to call fill_table for any table of the correct table type. If I need this flexibility, then I cannot push the data structure down inside the package. If, on the other hand, these programs really will be run only for that record and that table, then package- level data would be the way to go. The power of packages, particularly to provide package-level data, should make you want to place most, if not all, of your modules inside a package. You may not need to group additional modules into that package immediately. You may not need package-level data at this time. By using a package right from the start, however, you ensure that all references to the procedure or function are already prefaced with the package name. Otherwise, when you decide you need to wrap a package around a procedure, you either have to create a synonym for the newly-packaged object or change all references to the unqualified program name. Given these drawbacks, you should convert from parameters to global variables only when you have verified that the parameter-copying of these data structures has an unacceptable impact on your application. Document clearly the changes you have to make and, most importantly, the data structures which have become globals in your system. A later release of PL/SQL might fix this problem by no longer insisting on performing the copies for OUT and IN OUT parameters. When this is the case, you will want to consider converting back to a parameter interface. This will be practical only if you have documented your workarounds. 25.4.5 Use PLS_INTEGER for All Integer Operations When you need an integer variable, use the PLS_INTEGER type and not BINARY_INTEGER, INTEGER, or NUMBER. PLS_INTEGER is the most efficient implementation for integer types (available in PL/SQL 2.3 and above). Numeric types such as INTEGER and NUMBER are represented in the 22-byte Oracle number format. Arithmetic operations on these types are implemented using Oracle number libraries. Furthermore, the INTEGER type is a constrained subtype of NUMBER with a precision of 0. On assignments to INTEGER variables precision checking is done at runtime. Both PLS_INTEGER and BINARY_INTEGER are represented as a signed 4-byte quantity (sb4). But BINARY_INTEGER arithmetic is costly; the operands are first converted to an Oracle number, and then the Oracle number library is used to compute the result as another Oracle number. This results in increased use of temporaries, data conversion, and, hence, poor performance. On the other hand, arithmetic operations on PLS_INTEGERs are efficiently implemented using native integer arithmetic. Unfortunately, it is not possible to fix all the implementation inefficiencies with INTEGER and BINARY_INTEGER datatypes without breaking backward compatibility of old applications. For instance, Oracle cannot simply implement these types the same way as PLS_INTEGER without Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
changing the overflow/underflow semantics. (The sum of two BINARY_INTEGERs will result in an overflow only if the result exceeds the maximum value storable in an Oracle number. The sum of two PLS_INTEGERs will result in an overflow when the sb4 limit is exceeded.) The numeric types NATURAL, NATURALN, POSITIVE, POSITIVEN, and SIGNTYPE are subtypes of BINARY_INTEGER with narrower range constraints. There is considerable overhead (about three or four byte-code instructions) in the enforcement of these range constraints on every assignment (or parameter passing) to variables of these types. One caution about the use of PLS_INTEGER: this is a PL/SQL-specific datatype, so if you are constructing a function or procedure to be used in an environment which does not support that datatype, you could run into trouble. For example, a function which will be called inside SQL cannot have a PLS_INTEGER parameter. Instead, declare the parameter to be INTEGER, but then (if there is sufficient internal integer arithmetic) copy that value to a local variable of type PLS_INTEGER and perform computations on that variable. 25.4.6 Avoid NOT NULL Constraints Using NOT NULL constraints in PL/SQL comes with a performance penalty. Consider the program fragment below: procedure foo is m number not null; a number; b number; begin m := a + b; m := m * 1.2; m := m * m; .. end; Since "m" is a NOT NULL constrained number, the result of the expression "a+b" is first computed into a temporary, and the temporary is then tested for NULLity, If the temporary is NULL an exception is raised; otherwise the value of the temporary is moved to "m". On the other hand, if "m" were not constrained, then the result of the expression "a+b" could directly be computed into "m". So a more efficient way to rewrite the above fragment with reduced use of temporaries is: procedure foo is m number; -- Note: m doesn't have NOT NULL constraint a number; b number; begin Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
m := a + b; m := m * 1.2; m := m * m; -- enforce constraint programmatically if (m is null) then -- raise appropriate error end if; .. end; Another thing to note is that the types NATURALN and POSITIVEN are defined to be NOT NULL subtypes of NATURAL and POSITIVE, respectively; thus, you will incur the performance penalty described above when you use them. 25.4.7 Avoid Type Conversions When Possible PL/SQL does implicit conversions between structurally different types at runtime. Currently, this is true even when the source item is a literal constant. A common case where implicit conversions result in a performance penalty, but can be avoided, is with numeric types. For instance, assigning a PLS_INTEGER variable to a NUMBER variable or vice-versa will result in a conversion since their representations are different. Such implicit conversions can happen during parameter passing as well. Some examples of inefficient code and suggestions to fix them are shown below: 1. Prevent conversions between numeric types. Consider: number_variable := number_variable + 1; The literal constant 1 is represented as an sb4; it gets converted to Oracle number format before the addition. Instead, use: number_variable := number_variable + 1.0; The above is more efficient because literal floats (like 1.0) are represented as Oracle numbers, so no type conversion happens at runtime. Or better still, when dealing with integer data, use: pls_integer_variable := pls_integer_variable + 1; 2. Prevent numeric to character type conversion. Consider: char_variable := 10; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
The literal 10 is converted to CHAR at runtime, and then copied. Instead use: char_variable := '10'; 25.4.8 Use Index-By Tables of Records and Objects In Oracle 7.3, PL/SQL added support for index-by tables of records. (Index-by tables were formerly known as PL/SQL tables.) Prior to that, users modeled the same as a bunch of index-by tables of scalars (one for each record attribute). Users should strongly consider rewriting such applications to use table of records (or objects). The effort will pay off in two major ways: q Better application performance: The number of index-by table lookup, insert, copy, and similar operations will be reduced dramatically if the data is packaged into an index-by table of records. q Improved memory utilization: There is memory overhead associated with each index-by table (primarily due to its tree structured implementation). Having fewer index-by tables will help cut this overhead, and also reduce memory fragmentation caused due to the varying size allocations in different tables. Previous: 25.3 Tuning Oracle PL/SQL Next: 25.5 Overview of PL/ Access to Your Data Programming, 2nd Edition SQL8 Enhancements 25.3 Tuning Access to Your Book Index 25.5 Overview of PL/SQL8 Data Enhancements The Oracle Library Navigation Copyright (c) 2000 O'Reilly & Associates. All rights reserved. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous: 25.4 Tuning Chapter 25 Next: 26. Tracing PL/SQL Your Algorithms Tuning PL/SQL Applications Execution 25.5 Overview of PL/SQL8 Enhancements Oracle Corporation has improved the peformance of PL/SQL programs with the release of Oracle8 and PL/SQL8. Without delving into detailed descriptions of the internal changes made to the engine, it is worth noting which elements of the technology have improved. This knowledge may well affect your choice of implementation for algorithms and your willingness to employ some features which in the past have come with a noticeable penalty. PL/SQL8 offers improved performance in the following areas:[2] [2] This information has been provided by Oracle Corporation and has not been independently confirmed. q The memory required for PL/SQL code in the SGA has been reduced by about 20-25%. This will be especially important for large packages. In addition, Oracle8 uses a paging algorithm for code segments and data segments, leading to less fragmentation in the SGA. q Large VARCHAR2 and RAW variables are dynamically allocated and resized as needed. For example, you might declare a package variable to be length 32,000 to handle all possible scenarios, but in most cases that variable only holds 255 bytes of data. You will no longer pay the penalty of all that extra memory. q Anonymous blocks with bind variables execute much more rapidly. This is of special importance when you are working with DBMS_SQL or executing PL/SQL functions from within SQL. Oracle claims that the overhead of calling PL/SQL functions in SQL now is "negligible." If true, this will have significant implications for the deployment of PL/SQL code throughout SQL implementations. q The use of temporary data structures has been minimized, resulting in performance improvements for such operations as concatenations and implicit conversions. q PL/SQL records are now supported more efficiently for runtime operations. Prior to Oracle8, the compiler exploded records into individual scalar fields. Now, as you would expect with support for objects, PL/SQL provides native support for the RECORD composite type. q The performance of index-by tables (formerly known as PL/SQL tables) has improved significantly due to a change in implementation from B*trees to a paged-array representation. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous: 25.4 Tuning Oracle PL/SQL Next: 26. Tracing PL/SQL Your Algorithms Programming, 2nd Edition Execution 25.4 Tuning Your Algorithms Book Index 26. Tracing PL/SQL Execution The Oracle Library Navigation Copyright (c) 2000 O'Reilly & Associates. All rights reserved. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous: 25.5 Overview of Chapter 26 Next: 26.2 Tracing for PL/SQL8 Enhancements Production Support 26. Tracing PL/SQL Execution Contents: The PL/SQL Trace Facility Tracing for Production Support Free Format Filtering Structured Interface Filtering Quick-and-Dirty Tracing As you build more and more complex applications, it can be very difficult to keep track of which procedure calls which function; execution call stacks grow deep and bewildering. Yet there are times when it is very important to be able to trace the activity in your PL/SQL code base. Oracle offers a trace facility for your PL/SQL code which allows you to generate voluminous information about the particular paths your programs take to get their job done. Of course, Oracle has for years offered a SQL trace facility which provides extensive data on the processing of your SQL statements. See Oracle Performance Tuning for more information on this feature, as well as other tuning/tracing utilities like TKPROF. In addition to these standard Oracle facilities, you can build your own tracing utilities; the final section in this chapter offers an architecture and some implementational ideas for a utility designed specifically to trace execution within a running application. (Such a utility is particularly useful for production support.) 26.1 The PL/SQL Trace Facility PL/SQL8 offers a tracing tool for server-side PL/SQL. You can use this tool to trace the execution of PL/SQL programs and the raising of exceptions within those programs. The output from the trace is written to the Oracle Server trace file. On Windows NT, you can find this trace file in the \OraNT \RDBMS80\TRACE directory. In UNIX, check the $ORACLE_HOME\rdbms\trace directory. The name of the file has the format ORANNNNN.TRC, where NNNNN is a left zero-padded number assigned internally by the Oracle Trace facility. Order your directory by date to find the latest trace file. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
NOTE: You cannot use the PL/SQL tracing tool with the multi-threaded server option (MTS). 26.1.1 Enabling Program Units for Tracing In order to trace the execution of a program, you will first have to enable that program by recompiling it with the debug option. You can do this either by altering your session and then issuing a CREATE OR REPLACE statement, or by directly recompiling an existing program unit with the debug option. To alter your session to turn on PL/SQL debug mode for compilation, issue this command: SQL> ALTER SESSION SET PLSQL_DEBUG=TRUE; Then compile your program unit with a CREATE OR REPLACE statement. That program unit will then be available for tracing. You can also recompile your existing, stored program with debug mode as follows: SQL> ALTER [PROCEDURE|FUNCTION|PACKAGE] COMPILE DEBUG; So if you wanted to enable the emp_pkg package for tracing, you would issue this command: SQL> ALTER PACKAGE emp_pkg COMPILE DEBUG; From within a PL/SQL program you can also turn on debug mode for a module by using DBMS_SQL as follows: CREATE OR REPLACE PROCEDURE debugpkg (name IN VARCHAR2) IS cur INTEGER := DBMS_SQL.OPEN_CURSOR; fdbk INTEGER; BEGIN DBMS_SQL.PARSE (cur, 'ALTER PACKAGE ' || name || ' COMPILE DEBUG', DBMS_SQL.NATIVE); fdbk := DBMS_SQL.EXECUTE (cur); DBMS_SQL.CLOSE_CURSOR (cur); END; / Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
For more information on DBMS_SQL, see Appendix C, Built-In Packages. 26.1.2 Turning On the Trace Once you have enabled the desired program units for tracing, you can execute those programs (or the application code which makes use of those programs). To get trace output, however, you must turn on tracing for your session. You can request tracing of program calls and/or exceptions raised in programs. You do this with the ALTER SESSION command as follows: SQL> ALTER SESSION SET EVENTS='10938 TRACE NAME CONTEXT LEVEL '; where 10938 is the event number for PL/SQL tracing and is a number indicating the level of tracing you desire. Valid tracing levels are: Level Description 1 Trace all calls 2 Trace calls to enabled programs only 4 Trace all exceptions 8 Trace exceptions in enabled program units only You can activate multiple event levels for tracing by adding the level values. For example, the following statement sets tracing for levels 2 and 8: SQL> ALTER SESSION SET EVENTS='10938 TRACE NAME CONTEXT LEVEL 10'; while this next command activates tracing for levels 2, 4, and 8: SQL> ALTER SESSION SET EVENTS='10938 TRACE NAME CONTEXT LEVEL 14'; Lower trace levels supersede higher levels. So if you activate tracing for level combination 12, level Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
8 will be ignored and trace output will be produced for all exceptions, not just exceptions in enabled program units. As you can see, you have some control over the granularity of tracing. It is not possible, however, to activate tracing just within a specific program. It is either all programs or all programs in which tracing has been enabled with a debug-mode compile. NOTE: You cannot turn on tracing for remote procedure calls (RPCs) -- that is, programs which are stored in remote databases. 26.1.3 A Sample Tracing Session To make it easier for me to test and use this facility I created the following scripts: alter package &1 compile debug; alter session set events='10938 trace name context level &1'; So I can now prepare a package for tracing with the following statement: SQL> @compdbg PKGNAME where PKGNAME is the name of my package. In the following session, I turn on tracing for all levels by passing 14 (2 + 4 + 8); then I call my PL/Vision substitute for DBMS_OUTPUT.PUT followed by the raising of an exception. The following code: SQL> @trace 14 SQL> BEGIN 2 p.l (1); 3 raise no_data_found; 4 END; 5 / begin * ERROR at line 1: SQL> resulted in this trace file: Dump file D:\ORANT\RDBMS80\trace\ORA00089.TRC Wed Jun 11 13:22:52 1997 ORACLE V8.0.2.0.2 - Beta vsnsta=1 vsnsql=c vsnxtr=3 Windows NT V4.0, OS V5.101, CPU type 586 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Oracle8 Server Release 8.0.2.0.2 - Beta With the distributed, heterogeneous, replication, objects and parallel query options PL/SQL Release 3.0.2.0.2 - Beta Windows NT V4.0, OS V5.101, CPU type 586 Instance name: orcl Redo thread mounted by this instance: 1 Oracle process number: 11 pid: 59 Wed Jun 11 13:22:52 1997 *** SESSION ID:(11.18) 1997.06.11.13.22.52.431 ------------ PL/SQL TRACE INFORMATION ----------- Levels set : 2 4 8 ------------ PL/SQL TRACE INFORMATION ----------- Levels set : 2 4 8 Trace: PACKAGE PLVPRO.P: P Stack depth = 2 Trace: PACKAGE BODY PLVPRO.P: P Stack depth = 2 Trace: PACKAGE BODY PLVPRO.P: L Stack depth = 2 Trace: PACKAGE BODY PLVPRO.P: DISPLAY_LINE Stack depth = 3 Trace: PACKAGE BODY PLVPRO.P: LINELEN Stack depth = 4 Trace: PACKAGE BODY PLVPRO.P: PUT_LINE Stack depth = 4 Trace: Pre-defined exception - OER 1403 at line 0 of ANONYMOUS BLOCK: As you can see, trace files can get big fast, but they contain some extremely useful information. Previous: 25.5 Overview of Oracle PL/SQL Next: 26.2 Tracing for PL/SQL8 Enhancements Programming, 2nd Edition Production Support 25.5 Overview of PL/SQL8 Book Index 26.2 Tracing for Production Enhancements Support The Oracle Library Navigation Copyright (c) 2000 O'Reilly & Associates. All rights reserved. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous: 26.1 The PL/SQL Chapter 26 Next: 26.3 Free Format Trace Facility Tracing PL/SQL Execution Filtering 26.2 Tracing for Production Support You build a PL/SQL-based application. You debug it, probably by placing calls to some kind of trace procedure throughout your code.[1] Finally, after very careful QA and extensive testing with users, the application is deployed out into the field. You now have hundreds of people working with your code across the country (or maybe just in a single building). [1] Unless you are lucky enough to be using one of the few PL/SQL debuggers out there in the marketplace like Platinum's SQL-Station or Oracle's Procedure Builder. And, of course, problems rear their ugly heads. A user calls the support line and says that his program is behaving in a certain way. You cannot easily reproduce the problem. You are not entirely sure that the environment in which you are running your code is the same as that of your user. What you would really like to do is connect into his session and watch what he is doing as he is doing it. This section explores an implementation of a package architecture which allows you to "hook" into running programs and perform production support. You can also adapt it for use as a debugger/ execution trace mechanism. 26.2.1 Features of a Real-Time Support Mechanism First, let's come up with a concise definition of "real-time" support for PL/SQL-based applications: the ability to watch or analyze the activity of a currently connected Oracle user without disturbing that user's activity or otherwise affecting the behavior of the application. This is, obviously, very different from simply running the "same" code under the "same" circumstances -- that is a simulation of what your users are doing. To achieve this real-time support, you need to be able to get information out of your PL/SQL program and place it in some kind of repository where you can analyze what is going on and come up with fixes. You also need to be able to enable and disable the support mechanism while the user session is executing. Based on my discussions with customers and my own experience, here are the kinds of features Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
developers need to effectively support their applications: q The mechanism should be completely transparent to the user -- both when it is enabled and when it is disabled. q A support person can enable the mechanism "remotely," that is, she can connect into a running session, turn on (or turn off) the real-time trace, and then analyze the application behavior. q When placing the trace inside the PL/SQL application code, the developer can specify a filter of some kind (nested levels, numeric ranges, categories, or some other type of filter) so that the support trace can be turned on selectively within the application. q Output from the support mechanism can be changed and specified for each support session. In other words, sometimes you might want to write the trace information to a file, sometimes to a database pipe, and sometimes to a database table. In this chapter, I can't provide a detailed implementation of all these features. Instead, I will discuss some of the more interesting challenges, and offer you ideas on building such a mechanism yourself. 26.2.2 Starting and Stopping a Support Session How do you tell an Oracle session which is already up and running that you want to take a look around at its internal processing? How do you tell it that you are done and that it should stop feeding you information? Well, let's assume that you have put a call to the trace startup procedure at the beginning of each program. It would look something like this: CREATE OR REPLACE PROCEDURE calctotals IS BEGIN trace.startup ('calctotals'); . . . END; / I could then have trace.startup check something somehow to see whether the trace mechanism should be turned on or off. I could do the same thing in any other trace procedure which is placed inside application code. Let's take the simplest and most direct approach: using a database table. I create a table as follows: CREATE TABLE tracetab_activate (username VARCHAR2(60), action VARCHAR2(20)); Then I add these constants and function to the trace package: CREATE OR REPLACE PACKAGE trace IS Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
/* Just showing the part of the package that is relevant */ /* for this functionality. */ c_start CONSTANT CHAR(1) := 'Y'; c_stop CONSTANT CHAR(1) := 'N'; FUNCTION activated (username_in IN VARCHAR2) RETURN BOOLEAN; END trace; / CREATE OR REPLACE PACKAGE BODY trace IS /* Just showing the part of the package that is relevant for this functionality. */ FUNCTION activated (username_in IN VARCHAR2) RETURN BOOLEAN IS CURSOR act_cur IS SELECT action FROM tracetab_activate WHERE username = UPPER (username_in); act_rec act_cur%ROWTYPE; BEGIN OPEN act_cur; FETCH act_cur INTO act_rec; RETURN (act_rec.action = c_start); END activated; END trace; / With this function in place, I can modify my trace.startup procedure as follows: PROCEDURE startup (context_in IN VARCHAR2) IS BEGIN IF activated (USER) THEN log; ELSE nolog; END IF; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
/* Then the rest of the procedure activity. */ END startup; In other words, if the user should be activated, I call the trace.log procedure to turn on logging for that session. All calls to execution trace programs will then write their information out to the log specified by the PLVlog package (it can be an operating system file, database table, database pipe, etc.). Normally, the activation table would be kept empty. If a user calls with a problem, the support person can get that user's Oracle account name and issue an insert (I hope through some sort of GUI interface) as follows: INSERT INTO tracetab_activate VALUES ('SAM_I_AM', 'Y'); The next time Sam I Am's session executes trace.startup, the trace will be activated and the analysis can commence. To deactivate the real-time trace, the support person can simply delete that record from the table. Then the next time trace.startup is executed, logging will be turned off. This approach provides a straightforward mechanism to "get inside" an already-running session. One concern about using the trace.activated function in this way, however, is that a query against the tracetab_activate must be performed every time that trace.startup (and other trace "show" programs) is encountered. This could turn into an unacceptable amount of overhead if an application is well- modularized. That's the difference between a prototype and a production-quality utility. To really make this architecture successful, it would very likely need to be fine-tuned. One refinement is to maintain a counter in the trace package and be able to specify that you want to check for activation of the trace logging only every 50 or 100 or 1000 calls to trace programs. This would cut down on the overhead by skipping lots of queries, but it would also mean that it might take longer to activate the trace. 26.2.3 Filtering Trace Information Another critical element of a successful support mechanism is the ability to specify which trace information you want to see. If a system contains four main subsystems (with lots of interactions) and hundreds of programs, you're probably going to want to look at trace information from a particular area of functionality, based on the clues provided by the user. There are many different approaches one can take to filtering out trace messages so the support person or developer (let's call this person the analyzer) sees only what is relevant at that time. Here are the ideas I have thought of: Free format Allow the analyzer to provide a wildcard string which any trace message must be LIKE in Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
order to actively logged during this connection. Knowing this in advance, a developer could concatenate a "context" or level or category to the beginning of the message string. This is the most primitive form of filtering and requires all developers to know about the way in which trace performs its filter check. Structured interface Provide a separate argument in calls to programs like trace.startup and trace.show which would allow a developer to explicitly associate a level (integer) or category (string) with a given trace. With this approach, the trace message is kept distinct from the filter criteria. This is probably easier for developers to work with, but it requires more work within trace to provide an interface through which the analyzer can request a trace just for specific levels, ranges of levels, or one or more categories. If you are going to try to design your own package to handle this kind of functionality, you will find yourself at a crossroads of sorts. How much effort do you want to put into something like this versus how much effort are you going to require that developers/analyzers make in order to take advantage of your package? It can sometimes be hard to justify the resources required to "do it right." Obviously, I can't make that decision for you. In fact, I had trouble making that decision for myself as I designed the PLVxmn package of PL/Vision. I can, however, review what I think an appropriate interface would be to support these different approaches. Previous: 26.1 The PL/SQL Oracle PL/SQL Next: 26.3 Free Format Trace Facility Programming, 2nd Edition Filtering 26.1 The PL/SQL Trace Book Index 26.3 Free Format Filtering Facility The Oracle Library Navigation Copyright (c) 2000 O'Reilly & Associates. All rights reserved. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous: 26.2 Tracing for Chapter 26 Next: 26.4 Structured Production Support Tracing PL/SQL Execution Interface Filtering 26.3 Free Format Filtering With the free format approach, you could simply add a column onto the tracetab_activate table which would contain the wildcard string: CREATE TABLE tracetab_activate (username VARCHAR2(60), action VARCHAR2(20), matchto VARCHAR2(100)); Then when you insert into the table to "turn on" logging of trace information, you provide the string: INSERT INTO tracetab_activate VALUES ('SAM_I_AM', 'Y', '% INVOICE%'); In this case, I am looking for trace messages which contain the word INVOICE. I would then modify the activated function shown earlier in the article to a procedure which returns a TRUE/FALSE value, to indicate whether or not the trace is activated for this user, as well as the match string. Here is the header for such a procedure: PACKAGE trace IS . . . PROCEDURE check_activation (turnon_out OUT BOOLEAN, match_out OUT VARCHAR2); . . . END trace; My trace.startup procedure would call the check_activation routine as follows: PROCEDURE startup IS v_turnon BOOLEAN; v_match VARCHAR2(100); Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.