How to flush single SQL Statement from Shared Pool?

It is well known that the entire shared pool can be flushed with a simple ALTER SYSTEM statement.

SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.

If a single SQL statement has to be flushed out of the object library cache, the first step is to find the address of the handle and the hash value of the cursor that has to go away. Name of the object [to be purged] is the concatenation of the ADDRESS and HASH_VALUE columns from the V$SQLAREA view. Here is an example:

SYS@mydb>SELECT ADDRESS, HASH_VALUE FROM GV$SQLAREA WHERE SQL_ID = 'gj3nt7pjvs5bw';

ADDRESS                 HASH_VALUE

----------------          ----------------

000000034DA0E428    1673270652

SYS@mydb>exec DBMS_SHARED_POOL.PURGE ('000000034DA0E428, 1673270652', 'C');

PL/SQL procedure successfully completed.

SYS@mydb>

That set, your sql is gone from shared pool, this is very useful while to tune the query, I frequently use this technique. Enjoy.....

-Asif Pathan

Trace your session

Trace your session:

Enabling trace:
Enable the SQL Trace facility for the session by using one of the following:

1)  EXEC DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE, binds => FALSE);

2)  ALTER SESSION SET SQL_TRACE = TRUE;

3) DBMS_SESSION.SET_SQL_TRACE 

Disabling trace:
EXEC DBMS_SESSION.SESSION_TRACE_DISABLE();

Easily identifying your trace files:
alter session set tracefile_identifier='mysession';

Session/Database Level Tracing

To enable tracing for a specific session ID and serial number, determine the values for the session to trace:

SELECT SID, SERIAL#, USERNAME FROM V$SESSION;

       SID    SERIAL# USERNAME

---------- ---------- ------------------------------

        120       23   ERP

To trace particular session:

EXEC dbms_monitor.SESSION_TRACE_ENABLE(session_id=> 120, serial_num=>23, waits=>TRUE, binds=>FALSE);

EXEC dbms_monitor.SESSION_TRACE_DISABLE(session_id =>120, serial_num=>23);

To trace all sessions in the database:

EXEC dbms_monitor.DATABASE_TRACE_ENABLE(TRUE,TRUE);

EXEC dbms_monitor.DATABASE_TRACE_DISABLE();

--AsifKhan

Service Level Tracing

The SERV_MOD_ACT_TRACE_ENABLE procedure enables SQL tracing for a given combination of service name, module, and action globally for a database.

To Trace all sessions that log in under the ERP service are traced. A trace file is created for each session that uses the service, regardless of the module and action.

exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE('ERP');

To Trace service along with particular module, and action:

exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE('ERP', 'PAYMENTS_MODULE', 'SALES_ACTION');

Trace a particular client identifier:
exec DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE (client_id=>'C1', waits => TRUE, binds => FALSE);

--AsifKhan

Identify Trace File

Different methods to identify your trace file:

     1) From v$diag_info
     2) By Joining v$session, v$process, and v$parameter
     3) Set TRACEFILE_IDENTIFIER parameter

1) Identify the current trace file.
SQL> SELECT value FROM v$diag_info WHERE name = 'Default Trace File';
VALUE
-----------------------------------------------------------------------------
d:\app\diag\rdbms\obieedb\obieedb\trace\obieedb_ora_7728.trc


2)  You can get trace file using this query:

SELECT s.sid, s.serial#, pa.value || '/' || LOWER(SYS_CONTEXT('userenv','instance_name')) ||   '_ora_' || p.spid || '.trc' AS trace_file
FROM   v$session s,
           v$process p,
           v$parameter pa
WHERE  pa.name = 'user_dump_dest'
AND    s.paddr = p.addr

AND    s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');


3) You can set the TRACEFILE_IDENTIFIER parameter at session level to allow you to include some recognizable text into the trace file name:


ALTER SESSION SET TRACEFILE_IDENTIFIER = "POST_FIX_TEXT";

trcsess utility

The trcsess utility is for combining and filtering trace files to extract the statistics for a single session, service, or module across multiple trace files. 

The trcsess utility consolidates trace output from selected trace files based on several
criteria: session ID, client ID, service name, action name, and module name. After trcsess
merges the trace information into a single output file, the output file can be processed by

tkprof.

trcsess  syntax:

trcsess  [output=output_file_name]
         [session=session_id]
         [clientid=client_id]
         [service=service_name]
         [action=action_name]
         [module=module_name]
         [trace_files]
where

output=<output file name> output destination default being standard output.
session=<session Id> session to be traced.
Session id is a combination of session Index & session serial number 
clientid=<clientid> clientid to be traced.
service=<service name> service to be traced.
action=<action name> action to be traced.
module=<module name> module to be traced.
<trace_file_names> Space separated list of trace files with wild card '*' supported.

How to Trace Procedure ??

  I wanted to share procedure tracing method. Here are the steps to trace...

1. Give tracefile identifier within the procedure, so that everything will be dumped into that trace file.
2. Enable 10046 event to that session.
3. Write your code  
4. Disable 10046 event.
5. Navigate to "user_dump_dest" location
6. Search for "postfix_to_trace" trace files
7. Use tkprof to convert into human readable format.

1 to 4 )

CREATE OR REPLACE PROCEDURE SAMPLE_PROC AS
V_STRING VARCHAR2(2000);
BEGIN
V_STRING:='ALTER SESSION SET TRACEFILE_IDENTIFIER = "postfix_to_trace" ';
EXECUTE IMMEDIATE V_STRING;

V_STRING:='ALTER SESSION SET EVENTS ''10046 trace name context forever, level 8'' ';
EXECUTE IMMEDIATE V_STRING;

...

V_STRING:='ALTER SESSION SET EVENTS ''10046 trace name context off'' ';
EXECUTE IMMEDIATE V_STRING;

EXCEPTION
...
END  SAMPLE_PROC;

5) 
SYS>show parameter user_dump_dest
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest                       string      /xyz/oracle/diag/rdbms/mydb/mydb/trace       

6)
bash-3.2$ cd /xyz/oracle/diag/rdbms/mydb/mydb/trace

bash-3.2$ ls *postfix_to_trace*

mydb_ora_14490_postfix_to_trace.trc

bash-3.2$

7)

bash-3.2$ tkprof mydb_ora_14490_postfix_to_trace.trc  mydb_ora_14490_postfix_to_trace.txt

TKPROF: Release 11.2.0.2.0 - Development on Sun Apr 13 01:17:40 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

bash-3.2$

Tuning : From where to start ??

     It is very difficult to decide What to tune first? or from Where we need to start? Even well tuned database also shows a set of top wait events. The Oracle server provides a set of wait event statistics for processes that are idle or waiting. The Oracle server also records CPU utilization for processes that are running. 

Request time consisting of Wait time  (+) Service time(CPU time). 

CPU Time : Time spent actively working on request. 

Wait Time:  Time spent on waiting for particular resource for some reason.  

    Both Service time and wait time may be tuned. To tune the Service time something has to change(processing, SQL, access path etc). Wait times can be tuned by reducing contention for the resource where the wait is occurring.

Each server process is typically in one of three states:

Idle : waiting for something to do(sleeping)

Running : Using the CPU or in run queue

Waiting : for some resource to become available or for a requested activity to complete.

So by examining CPU Time/Wait Time you can decide where is the problem, then you can dig into that area. 


-Asifkhan P.