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.

USD VS INR Comparisons over Years

Year Value of one Rupee (units per US$)
1970 7.576
1975 8.409
1980 7.887
1985 12.369
1990 17.504
1995 32.427
1996 35.433
1997 36.313
1998 41.259
1999 43.055
2000 45.000
2006 48.336
2007 40.126
2007 (OCT) 39.6
2008 (NOV) 39.3
2009 (DEC) 39.6
2008 (JAN) 39.3
2008 (FEB) 39.3
2008 (MAR) 40.0

IT Highest/Lowest Pay Masters

Indian IT managers ahead only of Vietnam, Bulgaria and the Philippines.Want to earn more ? move to Switzerland

India, the global face of the information technology industry, is the fourth lowest paymaster, according to a global survey of IT managers.

Indian IT managers make an average of $25,000, ahead only of Vietnam, Bulgaria and the Philippines.
Switzerland ranked first in the survey with a salary of $140,960, followed by Denmark, Belgium and the United Kingdom. The United States ranked sixth with $107,500 in the global survey of IT staff at 6,545 companies in 35 countries conducted by Mercer.

IT Managers:

Lowest paymasters countries

1 Vietnam $15,470
2 Bulgaria $22,240
3 Philippines $22,280
4 India $25,000
5 Indonesia $31,720
6 China (Shanghai) $33,770
7 Malaysia $35,260
8 Czech Republic $35,880
9 China (Beijing) $36,220
10 Argentina $43,180


IT Managers:

Top paymasters countries

1 Switzerland $140,960
2 Denmark $123,080
3 Belgium $121,170
4 UK $118,190
5 Ireland $108,230
6 US $107,500
7 Germany $106,730
8 Canada $93,860
9 Hong Kong $90,340
10 Australia $88,850

IPL Match Schedule

TEMAS KOLKATA DELHI CHENNAI JAIPUR BANGALORE MUMBAI HYDERABAD MOHALI

FRI

18 APR

BANGALORE

v

KOLKATA

SAT

19 APR

MOHALI

v

CHENNAI

DELHI

v

JAIPUR

SUN

20 APR

MUMBAI

v

BANGALORE

KOLKATA

v

HYDERABAD

MON

21 APR

JAIPUR

v

MOHALI

TUE

22 APR

HYDERABAD

v

DELHI

WED

23 APR

CHENNAI

v

MUMBAI

THU

24 APR

HYDERABAD

v

JAIPUR

FRI

25 APR

MOHALI

v

MUMBAI

SAT

26 APR

BANGALORE

v

JAIPUR

CHENNAI

v

KOLKATA

SUN

27 APR

MOHALI

v

DELHI

MUMBAI

V

HYDERABAD

MON

28 APR

BANGALORE

v

CHENNAI

TUE

29 APR

KOLKATA

v

MUMBAI

WED

30 APR

DELHI

v

BANGALORE

THU

1 MAY

JAIPUR

v

KOLKATA

HYDERABAD

v

MOHALI

FRI

2 MAY

CHENNAI

v

DELHI

SAT

3 MAY

HYDERABAD

v

BANGALORE

MOHALI

v

KOLKATA

SUN

4 MAY

MUMBAI

v

DELHI

JAIPUR

v

CHENNAI

MON

5 MAY

BANGALORE

v

MOHALI

TUE

6 MAY

CHENNAI

v

HYDERABAD

WED

7 MAY

MUMBAI

v

JAIPUR

THU

8 MAY

DELHI

v

CHENNAI

KOLKATA

v

BANGALORE

FRI

9 MAY

JAIPUR

v

HYDERABAD

SAT

10 MAY

CHENNAI

v

MOHALI

BANGALORE

v

MUMBAI

SUN

11 MAY

JAIPUR

v

DELHI

HYDERABAD

v

KOLKATA

MON

12 MAY

MOHALI

v

BANGALORE

TUE

13 MAY

KOLKATA

v

DELHI

WED

14 MAY

MOHALI

v

JAIPUR

MUMBAI

v

CHENNAI

THU

15 MAY

DELHI

V

HYDERABAD

FRI

16 MAY

MUMBAI

v

MOHALI

MUMBAI

v

KOLKATA

SAT

17 MAY

DELHI

v

KOLKATA

JAIPUR

v

BANGALORE

DELHI

v

MOHALI

SUN

18 MAY

HYDERABAD

v

MUMBAI

KOLKATA

v

CHENNAI

MON

19 MAY

BANGALORE

v

DELHI

TUE

20 MAY

KOLKATA

v

MOHALI

KOLKATA

v

JAIPUR

WED

21 MAY

MUMBAI

v

MOHALI

CHENNAI

v

BANGALORE

THU

22 MAY

DELHI

v

KOLKATA

FRI

23 MAY

MOHALI

v

HYDERABAD

SAT

24 MAY

DELHI

v

MUMBAI

CHENNAI

v

JAIPUR

SUN

25 MAY

BANGALORE

v

HYDERABAD

BANGALORE

v

HYDERABAD

KOLKATA

v

MOHALI

SUN

26 MAY

JAIPUR

v

MUMBAI

MON

27 MAY

HYDERABAD

v

CHENNAI

TUE

28 MAY

WED

29 MAY

THU

30 MAY

Semi Final 1
MUMBAI

FRI

31 MAY
Semi Final 2
MUMBAI

SAT

1 JUN

FINAL

MUMBAI

Project Process in Level 5 Companies

I found this from a forum, very Interesting
------------------------------------------
A not techincally strong person is sent to onsite as a "coordinator" together with an experienced but recently recruited professional who is too excited to be onsite to realize what is he getting into.
They will agree to everything the customer ask without analyzing it and they write a big requirements document which the client barely understand. A delivery date is committed. Meanwhile, offshore, the team is assembled: a politically savvy but technically mediocre who has been long enough without doing anything important is appointed as the Project Leader and will happily show to everybody that now he's "the boss".
The unlucky employee who is capable and happens to be available is appointed as the technical lead (a.k.a. "the only who has a glimpse of a clue"), the team is completed with "freshers" which the only thing they're able to code without help is "hello world".
Of course, the team size is half of what would be required if all they were experts and the requirements were correct.
The offshore team is given even stricter deadlines, so everybody starts working 25hrs/day, the technical lead the only one achieving something barely close to what is expected, while the other team members struggle to create the only functionality that is so basic the technical lead feels he can assign to them. Except the less experienced of the team is the only one who really gets the programming "thing" and becomes the other team member that helps, but that is compensated with another member who is so useless that is assigned to testing.
Meanwhile the project leader realizes that the work is not progressing as it should and starts having long meetings to intimidate and try to coerce more work hours (which will be spent in more meetings). Of course, the only two members who really are doing something are the ones who look worse in the meetings.
After spending a full week working 24 hours, the team completes something that gets sent to the QA team (of course, we're talking about a CMMi level 7 organization). The QA team finds hundreds of irrelevant "bugs" not related to the real requirements. The Project Lead decrees that nobody would leave until all the bugs are solved, and somehow the team manages to fix them. The code finally is delivered to onsite missing the deadline by a couple of days.
The onsite technical person has to disentangle barely working the big ball of mud received from offshore and has to spend a full week to integrate it with the customers systems while the onsite coordinator haggles with the customer about trivial details in the requirements document.
The customer frustrated cancels the project and takes to another firm and the process starts again from the beginning until is too late an the systems goes into production as it is... sure some guys here experienced something similar... I'm missing something?
Don't ask me how I know... to painful to remember

Build a Bulletproof StartUP

Got a great Idea ? There's never been a better time to turn it into a great company Here's a 16-step guide to help you do it right more>>>