Oracle traces including 10053 and 10046 trace

Running various oracle traces including 10053 and 10046 trace file

 

10053 trace file generation

trace 10053 enables you to give insight about the cost-based optimizer internal calculation. You can use the trace file to look at cardinality, selectivity and draw a good parallel with the way cost of table, index or sort etc may be calculated.

Below is the simple method to generate 10053 trace file using SQL*PLUS tool

SQL> alter session set max_dump_file_size=unlimited;
Session altered.

SQL> alter session set tracefile_identifier='RAF_OPT';
Session altered.

SQL> alter session set events '10053 trace name context forever, level 1';
Session altered.

SQL> explain plan for select * from XX_ALL_OBJECTS where owner='X';
Explained.

SQL> alter session set events '10053 trace name context off';
Session altered.

SQL> exit

the trace file will be generated in the trace directory of diagnostics dest location (in 11g).

10046 trace file genearion

Event 10046 is the standard method of gathering extended sql_trace information for Oracle sessions.

1. trace a particular session

alter session set tracefile_identifier='10046';

alter session set timed_statistics = true;
alter session set statistics_level=all;
alter session set max_dump_file_size = unlimited;

alter session set events '10046 trace name context forever,level 12';-- Execute the queries or operations to be traced here --

select * from dual;
alter session set events '10046 trace name context off';
exit;

2. Tracing a process after it has started

For example, in SQL*Plus, start a session to find the OS process id (spid) for the target session:

select p.PID,p.SPID,s.SID
from v$process p,v$session s
where s.paddr = p.addr
and s.sid = &SESSION_ID
/

SPID is the operating system Process identifier (os pid)
PID is the Oracle Process identifier (ora pid)

If you do not know the Session ID then you can use a select similar to the following to help you identify the target session:

column line format a79
set heading off
select 'ospid: ' || p.spid || ' # ''' ||s.sid||','||s.serial#||''' '||
  s.osuser || ' ' ||s.machine ||' '||s.username ||' '||s.program line
from v$session s , v$process p
where p.addr = s.paddr
and s.username <> ' ';

Document For multi threaded processes in 12c, use newly added column stid from v$process to find specific thread, as Oracle combines many processes into a single ospid.  To find the specific thread, use following sytax:

oradebug setospid <spid> <stid>

Lets assume that the process to be traced has an os pid of 9834.
Login to SQL*Plus as a dba and execute the following:

connect / as sysdba
oradebug setospid 9834
oradebug unlimit
oradebug event 10046 trace name context forever,level 12

Remember to replace the example '9834' value with the actual os pid.

The tracefile name will be something like <instance><spid>_<stid>.trc

Difference between 10g, 11g and 12c

Difference between 10g, 11g and 12c

Oracle Database 10g New Features:

NID utility has been introduced to change the database name and id.

Automated Storage Management (ASM). ASMB, RBAL, ARBx are the new background processes related to ASM.

New memory structure in SGA i.e. Streams pool (streams_pool_size parameter), useful for datapump activities & streams replication.

From Oracle 10g, the spool command can append to an existing one.
SQL> spool result.log append

Ability to rename tablespaces (except SYSTEM and SYSAUX), whether permanent or temporary, using the following command:

SQL> ALTER TABLESPACE oldname RENAME TO newname;

 

Oracle Database 11g New Features:

Ability to mark a table as read only.
SQL> alter table table-name read only;

Temporary tablespace or it's tempfile can be shrinked, up to specified size.
SQL> alter tablespace temp-tbs shrink space;
SQL> alter tablespace temp-tbs shrink space keep n{K|M|G|T|P|E};

Introduction of ADR, single location of all error and trace data in the database, diagnostic_dump parameter

A new parameter is added in 11g, called MEMORY_TARGET, automatic memory management for both the SGA and PGA.

A system privelege is introduced in 11g called SYSASM, and this is granted to users who need to perform ASM related tasks.


 

Oracle Database 12c New Features:

 

Moving and Renaming datafile is now ONLINE, no need to put datafile in offline.
SQL> alter database move datafile 'path' to 'new_path';

No need to shutdown database for changing archive log mode.

Like sysdba, sysoper & sysasm, we have new privileges, in Oracle 12.1.0.
sysbackup for backup operations
sysdg for Data Guard operations
syskm for key management

Patching:
Centralised patching.
We can retrieve OPatch information using sqlplus query, using DBMS_QOPATCH package

SQL> select dbms_qopatch.get_opatch_lsinventory() from dual;

We can test patches on database copies, rolling patches out centrally once testing is complete.

New Commands
create pluggable database ...
alter pluggable database ...
drop pluggable database ...


New background processes - LREG (Listener Registration), SA (SGA Allocator), RM.

Oracle Database 12c Data Pump will allow turning off redo for the import operation (only).
$ impdp ... transform=disable_archive_logging:y

There are lots of differences!

Some of the big ones are:

- Multitenant/pluggable databases
- In Memory (12.1.0.2
- JSON support (12.1.0.2)
- Adaptive queries
- New histogram types
- SQL enhancements (match_recognize, PL/SQL in the with clause, ...) 

Important Daemons in RAC

Important daemons:

S.No	Component	11.1 Clusterware		11.2 Grid Infrastructure
		Linux Process	Comment	Linux Process	Comment
1	CRS	crsd.bin	Runs as root	crsd.bin	Runs as root
2	CSS	init.cssd, ocssd and ocssd.bin	Except for ocssd.bin, rest 2 run as root	ocssd.bin, cssdmonitor and cssdagent
3	EVM	evmd, evmd.bin and evmlogger	Evmd runs as root	evmd.bin and evmlogger.bin
4	ONS	ons
5	ONS/eONS			ons/eons	ONS is Oracle Notification Service. eONS is a Java Process.
6	OPROCD	oprocd	Runs as root and provides node fencing instead of hangcheck timer kernel module
7	RACG	racgmain and racgimon
8	CTSS			octssd.bin	Runs as root
9	Oracle Agent			oraagent.bin
10	Oracle Root Agent			orarootagent	Runs as root
11	Oracle High Availability Service			ohasd.bin	Runs as root through init.