Showing posts with label Oracle Database 10g. Show all posts
Showing posts with label Oracle Database 10g. Show all posts

Wednesday, June 18, 2008

PL/SQL enhancements in Oracle database 10g

Oracle 10g Database has a lot new things for everybody... May it be Database Administrators or the Developers. A lot of people have been discussing the DBA features that Oracle 10g Database provides, but a very few have said anything about PL/SQL features in 10g.

So, what’s New in PL/SQL in Oracle Database 10g?

Well, a lot of things are new in PL/SQL in 10g db. In this post we will just list some of them and in the next post:

A. Changes in 10g PL/SQL compiler

1. Freedom in order of evaluation of operands
eg: operators of equal precedence a+b+c can be evaluated in any order like: a+b+c or a+c+b or a+c+b or any other combination that the compiler feels correct

2. Freedom of not raising an exception
eg: if some operation can be performed in two ways, 1 of which may raise and exception, the compiler can choose not to select that option.

3. Freedom to raise the exception
eg: if an operation raises two exceptions, compiler has freedom to choose which exception it wants to raise

4. Freedom of computing correct result
eg: A*null = null; no matter A is of any datatype

.5 Takes care of the side effects
eg: RANDOM + 1/BSD
If RANDOM is a function call manipulating BSD, then sequence is taken care of

B. Freedom and Side Effects

Though Oracle 10g Database gives the compiler a lot of freedoms, the DB has to take care of the side effects arising out of such freedoms:

1. PL/SQL Compiler will not reorder, add or remove explicit procedure, function or method calls appearing in sepereate statements
2. PL/SQL Compiler has the freedom not to do an operation whose only effect is to raise and exception or to initialize a package
3. IF a PL/SQL subprogram requires initialization of the package, then PL/SQL is free to perform the initialization at any time between the beginning of the call to the subprogram which immediately encloses the package element reference and the moment when the package element is actually used
4. If the PL/SQL compiler can determine that a use of a package element does not require the initialization of the package, then PL/SQL is not required to initialize the package when the element is used
5. If an operation may raise an exception when executed, but there is an alternate possible method of execution which will not raise that exception, the PL/SQL compiler is free to choose the alternate method
6. The PL/SQL compiler may not introduce a computation as an alternate which may raise an exception which otherwise would not have been raised during the execution of a program

Example 1:

- A + B …
-
- A + B …

can be changed to

T := A + B;
… T …
…
… T …

Example 2:

for i in 1 .. 10 loop
A := B + C;
…
end loop;

can be changed to
A := B + C;
for i in 1 .. 10 loop
…
end loop;

Example 3:

if pkg.A = null then null; end if;


The entire if statement may be eliminated if A is not a procedure or method call because the PL/SQL compiler knows that the result of the comparison is always null.

These were some of the features/enhancements in PL/SQL compiler of Oracle 10g Database. So the next time you write a piece of code for Oracle database 10g, keep these things in mind and the database will give you the best output. In the subsequent post, we will try to debunk few performance myths in Oracle database 10g.

Thursday, January 17, 2008

"How to avoid Memory Notification Warning in Oracle 10g"


The following messages were reported in alert.log very frequently in our Oracle Database 10g (10.2.0.1.0)


Memory Notification: Library Cache Object loaded into SGA
Heap size 2907K exceeds notification threshold (2048K)


These are not error messages, but are just the warnings. They appear as a result of new event messaging mechanism and memory manager in 10g Release 2.


These messages means that the process is spending a lot of time in finding free memory extents during an a llocate as the memory may be heavily
fragmented. Continued messages of large allocations in memory indicate there are tuning opportunities on the application.
To prevent these messages from being written, set "_kgl_large_heap_warning_threshold" to a reasonable high value or zero to prevent these warning messages. Value needs to be set in bytes.


(logged in as "/ as sysdba")

SQL> alter system set "_kgl_large_heap_warning_threshold"=8388608 scope=spfile ;

SQL> shutdown immediate

SQL> startup

For detailed explanations, please refer to the Metalink Doc Id 330239.1

Aalap Sharma


Tuesday, January 08, 2008

Session Tracing in Oracle 10G

With the advent of Oracle 10g, the SQL tracing options have been
centralized and extended using the dbms_monitor package.
The example below shows how we can trace the sessions for a particular
schema/user without generating unwanted or junk trace files.


STEP 1
------
Before starting the trace, we need to identify the schema/user which
needs to be traced. For this, we shall create a logon trigger which would
attach a particular CLIENT_IDENTIFIER to the session logging into the
schema.

CREATE OR REPLACE TRIGGER "SCOTT".LOGON_TRIGGER
AFTER LOGON ON schema
DECLARE
v_user_identifier varchar2(64);
BEGIN
v_user_identifier:='SCOTT_ID';
DBMS_SESSION.SET_IDENTIFIER(v_user_identifier);
END;

All the sessions connecting to the SCOTT schema will be have the
CLIENT_IDENTIFIER set to "SCOTT_ID". This enables the database to identify all
the sessions connected to a schema, with just one name/id.

STEP 2
------
Now we shall enable the tracing for the attached client id using
following command:

EXEC
DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE('SCOTT_ID',WAITS=>TRUE,BINDS=>TRUE);

This will start tracing for all the sessions that has SCOTT_ID as
CLIENT_ID.
The trace files will be generated in the directory specified in the
value of parameter "USER_DUMP_DEST".
Mostly it is set to UDUMP directory.

STEP 3
------
Tracing can be disabled using the following command

EXEC DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE('SCOTT_ID');

STEP 4
------
Activating trace on multiple sessions means that trace information is
spread throughout many trace files. For this reason Oracle 10g
introduced the trcsess utility, allowing trace information from multiple trace
files to be identified and consolidated into a single trace file.
Go to the udump directory and execute the following command.

TRCSESS OUTPUT=OUTPUT.TRC CLIENTID=SCOTT_ID *
SYNTAX:
trcsess [output=] [session=]
[clientid=] [service=] [action=]
[module=]
output= output destination default being standard
output.
session= session to be traced.
Session id is a combination of session Index & session serial number
e.g. 8.13.
clientid= clientid to be traced.
service= service to be traced.
action= action to be traced.
module= module to be traced.
Space separated list of trace files with wild card
'*' supported.

This command will create with filename specified in parameter "OUTPUT"
i.e,. OUTPUT.TRC in this case.

STEP 5
------
We have a consolidated tracefile OUTPUT.TRC which contains traces for
all the sessions that had connected to SCOTT schema. We must now use
TKPROF utility to create a human readable file from this raw trace file.

TKPROF OUTPUT.TRC SCOTT_SESSIONS.TXT

STEP 6
------
The human readable form of the tracefile can be used to identify
problems with the application, database, etc.
There is a thrid party tool available (developed by Siva Dirisala)
which analyzes the SCOTT_SESSIONS.TXT file query wise.

http://www.dirisala.net/oracle/tkprof_trace_viewer.do

For detailed descriptions please refer to the Oracle documentation
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sqltrace.htm#sthref1994

"Happy Tracing"

Aalap Sharma