Category Archives: trace

How to get dump or list parameters set at session level?

Posted on by
Reply

Using oradebug one can get a dump of sessions parameters that are modified at session level, like optimization parameters.

SQL> alter session set sql_trace=true;
Session altered.

SQL> alter session set optimizer_mode=first_rows;
Session altered.

-- connect to session
SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump modified_parameters 1;
Statement processed.
SQL> oradebug tracefile_name;
i:\db\oracle\testdb\diagnostic_dest\diag\rdbms\testdb_a\testdb\trace\testdb_ora_4908.trc

Contents of the tracefile:
…
*** 2012-05-28 14:35:25.005
Processing Oradebug command ‘dump modified_parameters 1’
DYNAMICALLY MODIFIED PARAMETERS:
sql_trace = TRUE
optimizer_mode = FIRST_ROWS

*** 2012-05-28 14:35:25.005
Oradebug command ‘dump modified_parameters 1’ console output:

Advertisements

Example of using tracefile_identifier

Posted on by
Reply

Using “tracefile_identifier” Oracle will add that to the name of the user’s trace filename so the file can easily be identified. For example if one needs to trace multiple users session who have different schema or OS user then using “tracefile_identifier” one can identify the trace file specific to a user.

Example: The following trigger will create a trace file with the user’s OSuser and machine part of the filename for any user who login with “SCOTT”

CREATE OR REPLACE TRIGGER sys.session_trace_on
— to be created by sys user
AFTER LOGON ON database
DECLARE
v_machinename VARCHAR2(64);
v_ora_username VARCHAR2(30) DEFAULT NULL;
v_os_username VARCHAR2(30);
v_sid NUMBER;
v_serial NUMBER;
v_program VARCHAR2(48);
v_numuser NUMBER;
CURSOR c1 IS
SELECT sid, serial#, osuser, machine, program
FROM v$session
WHERE sid = userenv('sid')
and username = 'SCOTT';
BEGIN
OPEN c1;
FETCH c1 INTO v_sid, v_serial, v_os_username, v_machinename, v_program;
IF c1%FOUND THEN
— DBMS_SESSION.set_sql_trace (TRUE);
v_machinename := replace(replace(v_machinename, '\', '_'), '/', '_');
v_os_username := replace(replace(v_os_username, '\', '_'), '/', '_');
EXECUTE IMMEDIATE 'alter session set tracefile_identifier=''' || trim(v_os_username) || '''';
EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12''';
END IF;
CLOSE c1;
END;
/

Trace file created:
ls testdb_ora_5480_CAL-AMIN_Administrator.trc

How to use dbms_xplan to display execution plan?

Posted on by
Reply

Using dbms_xplan.display_cursor one can see the execution plan of a SQL statement. The function takes following 3 parameters.

SQL_ID (VARCHAR2) – If passed NULL, then it would assume SQL_ID of the last SQL statement
CURSOR_CHILD_NO – If passed NULL, then it would assume child_number of last SQL Statement
FORMAT – Possible values are BASIC, TYPICAL, ALL. The default value in TYPICAL

SQL> select count(1) from table1;

COUNT(1)
———-
32

Show execution plan of last SQL statement
SQL> SELECT *FROM TABLE (DBMS_XPLAN.display_cursor());

PLAN_TABLE_OUTPUT
————————————————————————————————————————————————————————————————————————————————————————————————————
SQL_ID dtzn8k4hzc88d, child number 0
————————————-
select count(1) from table1

Plan hash value: 1869552569

——————————————————————–
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
——————————————————————–
| 0 | SELECT STATEMENT | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| I_TABLE1 | 32 | 1 (0)| 00:00:01 |
——————————————————————–

14 rows selected.

Using sql_id, sql_child_number of a SQL statement that was last executed
SQL> select sql_id, sql_child_number from v$session where sid = 130;

SQL_ID SQL_CHILD_NUMBER
————- —————-
5mf7bd0jnctjv 0

SQL> SELECT * FROM TABLE (DBMS_XPLAN.display_cursor(‘dtzn8k4hzc88d’, 0));
SQL_ID dtzn8k4hzc88d, child number 0
————————————-
select count(1) from table1

Plan hash value: 1869552569

——————————————————————–
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
——————————————————————–
| 0 | SELECT STATEMENT | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| I_TABLE1 | 32 | 1 (0)| 00:00:01 |
——————————————————————–

14 rows selected.

How to check if auditing is turned on and privilege auditing is turned on?

Posted on by
1

If AUDIT_TRAIL is not set to NONE then using the following SQL one can find the statements that have auditing turned on.

SQL> SELECT * FROM dba_stmt_audit_opts union SELECT * FROM dba_priv_audit_opts;

USER_NAME PROXY_NAME
—————————— ——————————
AUDIT_OPTION SUCCESS FAILURE
—————————————- ———- ———-

ALTER ANY PROCEDURE BY ACCESS BY ACCESS

ALTER ANY TABLE BY ACCESS BY ACCESS

ALTER DATABASE BY ACCESS BY ACCESS
..

How to get current session id, process id, client process id?

Posted on by
Reply

SQL> select b.sid, b.serial#, a.spid processid, b.process clientpid from v$process a, v$session b
where a.addr = b.paddr
and b.audsid = userenv(‘sessionid’);

SID SERIAL# PROCESSID CLIENTPID
———- ———- ——— ———
43 52612 420734 5852:5460

V$SESSION.SID and V$SESSION.SERIAL# are database process id
V$PROCESS.SPID – Shadow process id on the database server
V$SESSION.PROCESS – Client process id, on windows it is “:” separated the first # is the process id on the client and 2nd one is the thread id.

How to enable auditing?

Posted on by
Reply

By setting the parameter “AUDIT_TRAIL” to “DB” or “OS”, one can enable auditing in the database. To change the value of the parameter you would need to bounce the database for the new value to take into effect. The default value of the parameter is “NONE” in which database auditing is disabled. Even if “AUDIT_TRAIL” is enabled/disabled, Oracle will write OS audit trail at OS audit trail at startup and shutdown of instance, and connections made by SYSOPER and SYSDBA.

If “AUDIT_TRAIL” is set to “OS”, the parameter “AUDIT_FILE_DEST” is the location where audit files are created. The default value of this parameter is “$ORACLE_HOME/rdbms/audit”.

If “AUDIT_TRAIL” is set to “DB”, oracle writes the auditing information in “SYS.AUD$” table. By default the table is created in SYSTEM tablespace.

If one tries to open a database in read only mode and AUDIT_TRAIL is set to DB, Oracle will generate an error stating the audit_trail is incompatible with database open mode.
SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-16006: audit_trail destination incompatible with database open mode