9.3 Advanced Concepts

Renice LGWR Process on Linux

Renice the Redo log writer process in Linux DB servers: Change the Nice priority of the REDO LOG writer process to improve the performance:

Command : $ renice -20 -p <<spid>>

Avoid Automatic Memory Management

ZENworks give its best performance with the manual memory management. Total memory can be divided into 3 parts.

db_writer_processes: Change this initialization parameter to increase the number of processes. For ZENworks, the number of db_writer_processes can be the number of hard disks.

UNDO_RETENTION and UNDO Tablespace Size

UNDO tablespace should be large enough to handle the amount of UNDO generated by ZENworks. If both parameters are not configured properly, then a ORA-01555: Snapshot too old, rollback segment too small error will be raised.

To estimate the UNDO tablespace size and UNDO_RETENTION value, check the below links:

http://www.dba-oracle.com/t_undo_retention.htm

http://www.akadia.com/services/ora_optimize_undo.html

SQL Trace

Session trace:

To start a SQL trace for the current session, execute:

ALTER SESSION SET sql_trace = true;

ALTER SESSION SET tracefile_identifier = mysqltrace;

To stop SQL tracing for the current session, execute:

ALTER SESSION SET sql_trace = false;

Tracing an entire database:

To enable SQL tracing for the entire database, execute:

ALTER SYSTEM SET sql_trace = true SCOPE=MEMORY;

To stop SQL Tracing:

ALTER SYSTEM SET sql_trace = false SCOPE=MEMORY;

The following query gives the Folder location, File size and File name details of the Trace file:

SELECT * FROM V$PARAMETER WHERE NAME IN ('tracefile_identifier', 'user_dump_dest', 'max_dump_file_size', 'timed_statistics');

The default trace file name is “INSTANCE_PID_ora_TRACEID.trc”, where:

INSTANCE is the name of the Oracle instance.

PID is the operating system process ID (V$PROCESS.OSPID).

TRACEID is a character string of your choosing.

TKPROF & TRCSESS

TKPROF is used for formatting a trace file into a more readable format for performance analysis.

tkprof filename1 filename2 [waits=yes|no] [sort=option] [print=n]

[aggregate=yes|no] [insert=filename3] [sys=yes|no] [table=schema.table] [explain=user/password] [record=filename4] [width=n]

TRCSESS allows trace information from multiple trace files to be identified and consolidated into a single trace file.

trcsess [output=output_file_name] [session=session_id] [clientid=client_id] [service=service_name] [action=action_name]

[module=module_name] [trace_files]

For more information, see: http://docs.oracle.com/cd/E11882_01/server.112/e16638/sqltrace.htm#PFGRF01020.

Custom Script for Tracing

The scripts below should be created in the ZENworks user using SQL*PLUS or SQL Developer. This user should have alter session privileges. After the trace is completed, these triggers can be dropped or disabled.

CREATE OR REPLACE TRIGGER

ZENWORKS.after_logon_trg

AFTER LOGON ON

ZENWORKS.SCHEMA

DECLARE

V_NAME VARCHAR2(100);

BEGIN

DBMS_SESSION.set_identifier('ZENworks');

EXECUTE IMMEDIATE 'ALTER SESSION SET timed_statistics=TRUE';

EXECUTE IMMEDIATE 'ALTER SESSION SET MAX_DUMP_FILE_SIZE=UNLIMITED';

SELECT SYS_CONTEXT('USERENV','HOST') INTO V_NAME FROM dual;

EXECUTE IMMEDIATE 'ALTER SESSION SET TRACEFILE_IDENTIFIER='''||trim(substr(trim(v_name),1,10))||'_'||to_char(sysdate,'DD')||'''';

EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 12''';

END;

/

CREATE OR REPLACE TRIGGER ZENWORKS.before_logoff_trg

BEFORE LOGOFF

ON ZENWORKS.SCHEMA

BEGIN

EXECUTE IMMEDIATE 'ALTER SESSION SET timed_statistics=FALSE';

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

END;

/