Oracle - SQL Tuning

Basic Methods

1. Using Oracle Hints
2. Splitting the queries from the inner select and debugging
3. Using explain plan and remove full table scan
4. Using Materialized Views
5. In from clause put the tables with less records in the last,
In where clause put the index columns at the first ,
In where clause put the columns with less records in the first

Using Hints to tune SQL

There are many hints available to the developer for use in tuning SQL statements that are embedded in PL/SQL. See Oracle Good Hints List - Sql Query Tuning.

You should first get the explain plan of your SQL and determine what changes can be done to make the code operate without using hints if possible. However, hints such as ORDERED, LEADING, INDEX, FULL, and the various AJ and SJ hints can tame a wild optimizer and give you optimal performance.

Hints are enclosed within comments to the SQL commands DELETE, SELECT or UPDATE or are designated by two dashes and a plus sign. To show the format the SELECT statement only will be used, but the format is identical for all three commands.



SELECT /*+ hint --or-- text */
statement body
-- or --
SELECT --+ hint --or-- text
statement body




Where:

/*, */ — These are the comment delimiters for multi-line comments
-- — This is the comment delimiter for a single line comment (not usually used for hints)
+ — This tells Oracle a hint follows, it must come immediately after the /*
hint — This is one of the allowed hints
text — This is the comment text


Hint Meaning
+ Must be immediately after comment indicator, tells Oracle this is a list of hints.
ALL_ROWS Use the cost based approach for best throughput.
CHOOSE Default, if statistics are available will use cost, if not, rule.
FIRST_ROWS Use the cost based approach for best response time.
RULE Use rules based approach; this cancels any other hints specified for this statement.
Access Method Hints:
CLUSTER(table) This tells Oracle to do a cluster scan to access the table.
FULL(table) This tells the optimizer to do a full scan of the specified table.
HASH(table) Tells Oracle to explicitly choose the hash access method for the table.
HASH_AJ(table) Transforms a NOT IN subquery to a hash anti-join.
ROWID(table) Forces a rowid scan of the specified table.
INDEX(table [index]) Forces an index scan of the specified table using the specified index(s). If a list of indexes is specified, the optimizer chooses the one with the lowest cost. If no index is specified then the optimizer chooses the available index for the table with the lowest cost.
INDEX_ASC (table [index]) Same as INDEX only performs an ascending search of the index chosen, this is functionally identical to the INDEX statement.
INDEX_DESC(table [index]) Same as INDEX except performs a descending search. If more than one table is accessed, this is ignored.
INDEX_COMBINE(table index) Combines the bitmapped indexes on the table if the cost shows that to do so would give better performance.
INDEX_FFS(table index) Perform a fast full index scan rather than a table scan.
MERGE_AJ (table) Transforms a NOT IN subquery into a merge anti-join.

EJB Basics

EJB --- OVERVIEW
Service -- A group of related components that carry out a given business process function
SOA - a Service Oriented Architecture is a process of focusing on the development of services rather than piecemeal components such that these services provide a higher level of abstraction from the fuctional point

One Characteristic of SOA is they are autonomous in nature. These independent entities can interact with others inspite of differences in the way they have been implemented and the platform they have been deployed.

EJB is a standard for building server side components in JAVA. It defines an agreement b/w components and application server that enables any component to run in any application server.

EJB components can perform the following tasks

Perform Business Logic, Access the Database, Access another system (like cobol - cics, erp -- using J2ee connector Architecture -- JCA)

Types of Beans :

Session Beans : Session bean models business processes.
Entity Beans : Entity bean models business data.
Message Driven Beans : MDB are similar to session beans in that they model business process
the difference is that you can call a MDB by explicitly sending a message to the bean.

In Java any object the implements java.rmi.remote is a remote object that is callable from a different JVM.

The Java files needed for a EJB program are
1. Remote Interface
2. Home Interface
3. Bean Business Logic
4. A Client that calls the bean using JNDI

In EJB2.0, Local Objects (stubs) implement a local interface rather than a remote interface as done in EJB 1.0
Thus local objects are fast and make high performance enterprise beans.

Steps in EJB 1.0
1. The Client calls a local stub
2. The stub marshalls the parameters into a form suitable for a network.
3. The stub then sends the marshalled data to the skeletons
4. The skeleton demarshalls the parameters
5. the skeleton sends the data to the EjbObject
6. The EjbOject performs the middleware functions as connection-pooling, transaction, security...
7. EjbObject calls the enterprise beans instance and it does its work ( Business Logic )


Steps in EJB 2.0
1. The Client calls the local Object
2. Local Object performs needed middleware functions
3. Once enterprise bean does its work, it returns control to local Object

In EJB2.0 when we write local Object, we extend javax.ejb.EJBLocalObject and when we extend a Local home interface you extend javax.ejb.EjbLocalHome

Drawbacks of Local Interfaces
1. They only work when you are calling beans in the same process. -- You cannot call a bean remotely if your code relies on a local interface. If you decide to switch b/w a local and a remote call, you must change your code from using the local interface to using the remote interface.
2. They marshall parameters by reference rather than by value. whille this may speed up our application since parameters are not copied, it also changes the symantics of the application.


Writing EJB starters ---------- ;)

1. Remote Interface --- The remote interface supports every method that our bean expose.
a) Extend javax.ejb.EjbObject
b) We declare all the business methods

2. Local Interface -- Local Clients will use our local Interface.to call bean methods
If the Client is Local then we can use local interface instead of Remote Interface ( Supported in EJB 2.0)

3. Home Interface -- The Home Interface had methods to create and destory EJB Objects.
a) the create() method is a Factory method that clients use to get a reference to the EJB Object. The create method is also used to initialize the bean.
b) the create() method throws two exception java.rmi.RemoteException and javax.ejb.CreateException
c) Home Interface extends javax.ejb.EJBHome

4. Local Home Interface --- The performance of local-home interface is more and is used by local Clients
a) It extends EJBLocalHome interface instead of EJBHome interface
b) The EJBLocalHome interface does not extend java.rmi.Remote Exception

There are two different types of Clients
a) Java RMI-IIOP based Clients -- - These Clients use a JNDI to look up objects over the network
b) CORBA Clients --- This enables EJB components to be invoked from C++, or other platform.