Previous | Table of Contents | Next

Page 287

    TRACE.get_trace(TRACE.trace_rec, status);
    IF (status = 0) THEN     -- read a message
      DBMS_OUTPUT.put_line(  -- display message
        TRACE.trace_rec.user || ` ` ||
        TRACE.trace_rec.mod  || ` ` ||
        TO_CHAR(TRACE.trace_rec.seq) || ` ` ||
        TO_CHAR(TRACE.trace_rec.tstamp, `YYYY-MM-DD HH:MI:SS') || ` ` ||
        TRACE.trace_rec.var || ` ` ||
        TRACE.trace_rec.val || ` ` ||
        TRACE.trace_rec.com);
    ELSE  -- no message read
      DBMS_OUTPUT.put_line(`result of get_trace: ` || SQLERRM(status));
    END IF;  -- got a message?
  END LOOP;
EXCEPTION
WHEN OTHERS THEN
  status := SQLCODE;
  DBMS_OUTPUT.put_line(`consumer: ` || SQLERRM(status));
END;
/

You should then see something similar to the following:

SCOTT PIPE_TEST 1 1997-11-22 05:55:44 name1 val1 this is a test
SCOTT PIPE_TEST 2 1997-11-22 05:55:44 name2 val2 this is a test
SCOTT PIPE_TEST 3 1997-11-22 05:55:44 name3 val3 this is a test
SCOTT PIPE_TEST 4 1997-11-22 05:55:44 name4 val4 this is a test
SCOTT PIPE_TEST 5 1997-11-22 05:55:44 name5 val5 this is a test
result of get_trace: ORA-20000: GET_TRACE: TIMEOUT
PL/SQL procedure successfully completed.

Experiment with these Producer/Consumer scripts, trying, for example, to send many trace messages without the benefit of a consumer, to overflow the pipe.

The internal trace flag enables you to turn the feature on and off at will. Generally, for production runs one would leave it off. The toggle function allows the trace to be turned on at specific times during a run, such as when a particular set of rows is being examined.

Another good use for pipes is when you have a function that is called from an SQL statement. Since such a function cannot have any transaction-related side effects, such as writing a row to an error log, you could instead write the data to a pipe, and then have a listening program in a separate session write the error information.

Altering the Session with DBMS_SESSION

This package gives you programmatic control over your session. The subprograms contained herein are listed in Table 12.2.

Page 288

Table 12.2 Subprograms Available in DBMS_SESSION


Subprogram Description
PROCEDURE set_role( Pstmt VARCHAR2); Same as SET ROLE <stmt>;
Enables and disables roles for your current session.
PROCEDURE set_sql_trace( Ptrace BOOLEAN); Turns trace on/off for this session; same as ALTER SESSION SET SQL_TRACE=TRUE|FALSE.
PROCEDURE set_nls( Pparam VARCHAR2, Pvalue VARCHAR2); Same as ALTER SESSION SET <nls_parameter> = <value>.
PROCEDURE close_database_link( Plink VARCHAR2); Same as ALTER SESSION CLOSE DATABASE LINK <link>.
PROCEDURE reset_package; Resets package state, as it was at the start of a session.
FUNCTION unique_session_id RETURN VARCHAR2; Returns a string (up to 24 bytes) guaranteed to have a unique value among all sessions.
FUNCTION is_role_enabled( Prole VARCHAR2) RETURN BOOLEAN; Returns TRUE/FALSE whether the specified role has been enabled for this user.
PROCEDURE set_close_cached_open _cursors(Pclose BOOLEAN); Same as ALTER SESSION SET CLOSE_CACHED_OPEN_CURSORS=TRUE| FALSE, which controls whether cached, open cursors are automatically closed on COMMIT or ROLLBACK.
PROCEDURE free_unused_user_memory; Reclaims unused memory, such as after a very large sort (> 100KB), package compilations, or large PL/SQL tables.

You can use set_sql_trace() to turn on and off the trace facility for your session, such as when tuning a section of code. This is a nice feature to build into your debug environment.

If you call reset_package, for all packages you are accessing in memory (your private SGA memory), you reset all global variables (public and private) back to their original values. This might be an appropriate action when you wish to run the same packaged subprogram over and over, each time with the original variable values when it was first loaded or accessed.

Page 289

You can use unique_session_id to distinguish between multiple sessions for the same user. I'll open two sessions for user SCOTT and get unique session ID for each:

SET SERVEROUTPUT ON
BEGIN
DBMS_OUTPUT.put_line(DBMS_SESSION.unique_session_id);
END;
/

I get for the first session:

800070000000

and for the second:

700011000000

You can use this value for a return pipe name (prefixed with a short string), or otherwise distinguish sessions regardless of the user name. For debugging purposes, you could store the user name with this unique session number in a table, or send to another application via a pipe, perhaps along with the SID and SERIAL# from V$SESSION, to make killing or tracing the session easier.

Managing the Shared Pool with DBMS_SHARED_POOL

This package contains various routines for viewing the contents of the shared memory pool, and manipulating those objects.

First we'll see what's in memory:

SET SERVEROUTPUT ON SIZE 1000000
BEGIN
  DBMS_SHARED_POOL.sizes(0);  -- show everything over 0K in size
END;
/

Note we made the buffer size big because there may be quite a lot of objects in memory. Running this on a fresh SQL*Plus session, I get a whole bunch of stuff, which upon close examination reveals some packages, cursors, synonyms, views, and other database objects. These are ALL the shared memory objects, system-wide. If I set the size parameter to 50 (KB), all I get now is:

SIZE(K) KEPT   NAME
------ ------ ----------------------------------
125            SYS.STANDARD            (PACKAGE)
PL/SQL procedure successfully completed.

All shared memory objects are subject to aging, except those specifically marked to be kept. You can keep and unkeep packages with (mysteriously enough) the keep() and unkeep() procedures. In order to keep and unkeep things such as cursors, you must create a comma- delimited string containing the hexadecimal address and hash value (`hex_addr, hash'), as found in v$sqlarea or shown by the sizes() procedure.

Page 290

If you find your shared memory objects thrashing, where things are being aged out and reloaded too frequently, you could try (besides allocating more memory!) to localize the impact by setting the threshold beyond which a request for memory will fail. This impacts only the user attempting to get memory. When a lot of memory objects are being aged out, all users are impacted. This is similar to the effect when you perform an alter system flush shared_pool command. By setting the threshold, you cancel the request for memory when the request is
for more memory than you want any one session to have. You set this threshold with the aborted_request_threshold (Pnum) procedure. The size for Pnum must be between 5000 and 2GB. If the memory request exceeds this threshold, the user receives an exception. I'd be pretty careful with this.

Obtaining Segment Space Information with DBMS_SPACE

This package gives you information about segment space that you won't get using the standard views.

Want to know how much total, unused, and used space there is in a table, cluster, or index? Run Listing 12.6.

Listing 12.6 unspace.sql—Compute Total Blocks/Bytes, Unused Blocks/Bytes, Used Blocks/Bytes

DECLARE
  -- cursors
  CURSOR get_objs IS
    SELECT object_name, object_type
    FROM user_objects
    WHERE object_type = ANY (`TABLE', `INDEX', `CLUSTER');
  -- constants
  TB    CONSTANT VARCHAR2(2)  := CHR(9);
  OWNER CONSTANT VARCHAR2(30) := USER;
  -- variables
  get_objs_rec get_objs%ROWTYPE;
  status  NUMERIC;
  tblocks NUMBER;  -- total blocks  (per object)
  tbytes  NUMBER;  -- total bytes   (per object)
  ublocks NUMBER;  -- unused blocks (per object)
  ubytes  NUMBER;  -- unused bytes  (per object)
  luefil  NUMBER;  -- last used extent file id
  lueblk  NUMBER;  -- last used extent block id
  lblock  NUMBER;  -- last used block
  totblk  NUMBER := 0;  -- total blocks  (all objects)
  totbyt  NUMBER := 0;  -- total bytes   (all objects)
  totubk  NUMBER := 0;  -- total unused blocks (all objects)
  totuby  NUMBER := 0;  -- total unused bytes  (all objects)
BEGIN
  DBMS_OUTPUT.enable(1000000);
  DBMS_OUTPUT.put_line(`------------ TOTAL BLKS/BYTES' ||
                       `  UNUSED BLKS/BYTES  USED BLKS/BYTES');
   OPEN get_objs;
  LOOP

Previous | Table of Contents | Next