OracleFunda

2008-06-18T09:02:00.000-07:00

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.

2008-05-21T09:06:00.000-07:00

Troubleshooting the dreaded "ORA-07445" and "ORA-00600" errors

ORA-07445 and ORA-00600 core dump errors are very generic and it does not give users any specific error message. It becomes the task of a DBA to find out the cause and the solution of the error.The million dollar question is how to find the cause of the error? There are no direct messages printed on the screen to start with.

In such case, we start with alert_SID.log file. Every ORA-07445 and ORA-00600 errors will get recorded in the alert_SID.log file with proper time and related trace file information.

We will use the information in trace file to find out the actual cause of the error and the solution through metalink. Metalink provides a very useful "ORA-600/ORA-7445 Troubleshooter tool (Metalink Note:153788.1)"

Login into the Metalink account and search for doc id "153788.1" in the Quick find search bar and open the document. Now navigate to the error in alert_SID.log file and find the related trace file from UDUMP directory.

There are 2 ways to lookup the error:

1. Lookup by code

a. Select the correct Error code (ORA-00600 or ORA-07445)

b. Provide the first argument or failing function

c. Select the appropriate database version

d. Click Lookup Error button

2. Search Call Stack

a. Select the correct Error code (ORA-00600 or ORA-07445)

b. Select the appropriate database version

c. Enter the Call Stack info in the "Search Call Stack box"

d. Click Search Call Stack button

Metalink will search it's database and will show all the possibilities that can cause the problem and also it's solutions. In case the error is not in the metalink database, they make a note of this new error too.

This tool plays important role in identifying bugs in the database.

Aalap Sharma :)

2008-04-25T08:00:00.000-07:00

How to change oc4jadmin password for Application Server 10gR3

Scenario:

We have a fresh installation of Oracle Application Server 10gR3 (10.1.3.x). We perform following tasks:

1. Create new OC4J instances
2. Deploy Applications on these instances
3. Change password for oc4jadmin user using setup link in Application server console

Problem:

After performing above tasks, when we try to access any OC4J instances other than HOME, the Application Server will deny the access and gives following error on the screen:

Unable to make a connection to OC4J instance instance_name on Application
Server application_server_name. A common cause for this failure is an
authentication error. The administrator password for each OC4J instance in the
Cluster must be the same as the administrator password for the OC4J instance on
which Application Server Control is running.

Reason:

The administrator password for all OC4J instances in the Cluster must be the same as the administrator password for the OC4J instance on which Application Server Control (by default it is HOME instance) is running.
Changing the password through the Setup link does not change the oc4jadmin password used by any remote OC4J instances. A remote OC4J instance is any OC4J instance in a cluster topology that is not hosting the active Application Server Control.
This means that all the OC4J instances should have same oc4jadmin password.

Solution:

Before changing the oc4jadmin password for HOME instance, we must change the oc4jadmin password for remote instances using the following steps:

1. Click on OC4J instance
2. Click on Administration tab
3. Edit sub-task Security Providers under Security task
4. Edit any application name under Application Level Security
5. Click on Realms tab
6. For realm name - jazn.com, click the value under column Users
7. Click on oc4jadmin under User Name column
8. Provide the old Password for oc4jadmin and new password for oc4jadmin and click Apply

Repeat these steps for all the OC4J instances except HOME instance.
After changing the administrator passwords for all OC4J instances, we can then change the oc4jadmin password for HOME instance using setup link on Application server console.

Now all the oc4jadmin passwords are in sync and the application server will be functioning fine.

2008-02-27T06:41:00.000-08:00

Oracle Application Server - Basics

This post is for those people who are new to Application Server domain and need simple answers to basic questions i.e., a couple of What is ? kind of questions. Read on...

What is?

1. What is Application Server?

An application server is a software platform that delivers content to the Web. In our case, it is a layer between the clients and the database called middle tier. Common Application logic, business rules, etc are coded in applications in middle tier

2. What is OC4J Instance?

Oracle Containers for Java EE (OC4J) is the core Java EE runtime component of Oracle Application Server.It is a program that serves the J2EE requests of the clients. An OC4J instance is the copy of 1 such program.

3. What is (Middle tier) Application?

The packaged code deployed on the middle tier which contains common application logic, business rules, etc is a Middle tier Application.

4. What is Data Source?

Object to connect to a database. A data source is a Java object and offers a portable, vendor-independent method for creating connections to databases. A data source object's properties are set so that it represents a particular database. An application can use a different database by changing the data source's properties; no change in the application code is required.
The data sources for the OC4J instance are referenced as Java Naming and Directory Interface (JNDI) locations.

5. What is Connection Pooling?

A Data Source (Managed) uses Connection pool to efficiently manage connections. OC4J provides the connection pool feature to increase efficiency by maintaining a cache of physical connections that can be reused. When a client closes a connection, the connection gets placed back into the pool so that another client can use it. A connection pool improves performance and scalability by allowing multiple clients to share a small number of physical connections.

6. What is Deployment?

In Simple terms, Deployment means installing the application. When you deploy application, the Application Server will unpack the deployment file (EAR/WAR/JAR/RAR) and create the directory structures with files, just like they were packed by the developer.

7. What is RMI Port?

Remote Method Invocation (RMI) is a technology that allows an object running in one Java virtual machine (JVM) to invoke methods on an object running in a different Java virtual machine (JVM).

The complete Application Server documentation is available here:

http://download.oracle.com/docs/cd/B31017_01/index.htm

Best Luck :)

2008-01-17T07:19:00.000-08:00

"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

2008-01-08T07:01:00.001-08:00

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