What to do I have to do when query is too slow, which was earlier running fine?

You need to know why its became slow suddenly. Need to check through following ways, rewrite the query or applying oracle hints on query or using parallellization. So the first step of yours should be to investigate the root cause.

Widely used tools are:
- dbms_profiler
- Explain plan
- SQL*Trace / TKPROF
- Statspack

dbms_profiler:

Use dbms_profiler, if you want to know where time is being spent in PL/SQL code.
Statspack is also a good tool if you are a DBA and you want to check query performance for entire database.

For a single query , explain plan & SQL*Trace & TKPROF are useful tools for query execution.

Explain plan:

in SQL*Plus you have to type:

explain plan for ;
select * from table(dbms_xplan.display);

if you are using older version of plan_table, you have to run the script $ORACLE_HOME\RDBMS\ADMIN\utlxplan.sql.

To get plan table output on serial or parallel execution using below scripts:
$ORACLE_HOME\RDBMS\ADMIN\utlxpls.sql - plan table output with serial execution.
$ORACLE_HOME\RDBMS\ADMIN\utlxplp.sql - plan table output with parallel execution.

The output is shows you what the query optimzer expects.It gives us an idea on choosing correct access path.

SQL*Trace/TKPROF:

Open a new SQL*Plus:
- open a new db session
- alter session set sql_trace true;
- run your query
- disconnect session
- identify your trace file in the server directory as specified in the parameter - user_dump_dest
select value from v$parameter where name = 'user_dump_dest'; 
OR 
Show parameter user_dump_dest;
- on your operating system: tkprof query_op.txt
query_op.txt will now give you valuable information on what has actually happened.

By comparing the output from explain plan with the output from TKPROF, We can able to identify problematic areas of the query.

What actions-factors you need to take care while creating index?

Creation of index on table depends on size of table, volume of data.
If table size is large and we need only few data while selecting data then create index. How frequently column is used in where condition, its also important.

if table has already a primary key, then create a unique key for the required column. Creating a primary key or unique key automatically create unique index on the column.

It is also not advised to create so many indexes as it impacts on query performance of DML statments as every DML statment need to go through with various index and table segments simultaneously.

We can create index or rebulid online also. It enables us to update base tables on the sametime.

creating any index parallelly is not supported while creating and rebuilding index online.

CREATE INDEX idx_emp_mgr ON employee (empid,mgrid) ONLINE;

/*+ APPEND */ hint in serial & parallel mode in oracle.

/*+ APPEND */ hint enables the optimizer to use direct-path insert particularly in insert statements in oracle

In serial mode, direct path can be used only if you include the APPEND hint.

Conventional insert is the default in serial mode. 
It means that NO UNDO will be written. 

UNDO is backed by REDO, so the REDO volume will be accordingly reduced.

Direct path insert is the default in parallel mode. 
In parallel mode, conventional insert can be used only if you specify the /*+ NOAPPEND */ hint.
data is appended to the end of the table,rather than using existing space currently allocated to the table, means after HWM of the current block. 

Direct-path inserts are considerably faster than conventional inserts.

What are the Bind Variables & Substitution Variables?

Bind Variables are also called host variables, which is accessed even after the PL/SQL block is executed.
It can be create using VARIABLE keyword and referenced with a preceding colon-:.

ex.
VARIABLE sal number;
begin
select salary into :sal from emp where emp_id = 11;
end;
/
select firstname , lastname from emp where salary = :sal;

Substitution Variables are used to get user input at run time to avoid hard-coding of values that we can provide run time also.
We can reference the same with in PL/SQL block with symbol ampersand-&.

VARIABLE sal number;
accept empid prompt 'provide emp id' 
declare
v_empid number := &empid; 
begin
select salary into :sal from emp where emp_id = v_empid;
end;
/
select firstname , lastname from emp where salary = :sal;

New PL/SQL Features and Enhancements in Oracle 11g

The new PL/SQL features and enhancements in Oracle 11g are as follows:

• Enhancements to regular expression built-in SQL functions
• SIMPLE_INTEGER, SIMPLE_FLOAT, and SIMPLE_DOUBLE datatypes
• CONTINUE statement
• Sequences in PL/SQL expressions
• Dynamic SQL enhancements
• More control over triggers
• Compound triggers
• Database resident connection pool
• Automatic subprogram inlining
• PL/Scope
• PL/SQL hierarchical profiler
• PL/SQL native compiler generates native code directly

What are the major difference between Oracle9i and Oracle10g?

Oracle 9i has the following new features: 

1. Direct XML database support.
2. Oracle Data Guard and enhancements.
3. New data sharing and replication features.
4. Compresses keys in tables when loading data.
5. Security Improvements.
6. Manage system table spaces locally.
7. Automated DBA.

Oracle 10g has following new features:

1. New drop database and alter database begin backup syntax.
2. Oracle 10g Data Guard Broker and RAC to support Redo Log Transport.
3. SQL apply feature and regular expression support.
4. Supports HTML database.
5. Recycle bin for storing objects and new purge command.
6. SYSAUX table space and rename table space command.
7. Automated Storage Management (ASM).
8. Automatic Workload Repository (AWR).
9. Automatic Database Diagnostic Monitor (ADDM)
10. PLS_INTEGER, a new data type was added which enhances performance. ANYDATA data type was introduced to hold a data of variant feature.

How to get list of Top Performing SQLs in Oracle?

The V$SQLAREA is used for identifying the top most resource-consuming SQL statements with a variety of criteria. It is widely useful to Database Administrators, Application Developers, Oracle Support Engineers and generally anyone involved in an Oracle Database Performance Tuning activity.

The thresholds used are the same as used by default in Statspack:
Buffer Gets : 10,000
Physical Reads : 1,000
Executions : 100
Parse Calls : 1,000
Sharable Memory : 1,048576
Version Count : 20
For Top Buffer Gets >>>

SELECT * FROM(
SELECT substr(sql_text,1,40) sql,
buffer_gets, executions, buffer_gets/executions "Gets/Exec",hash_value,address
FROM V$SQLAREA
WHERE buffer_gets > 10000
ORDER BY buffer_gets DESC
)WHERE rownum <= 10;

For Top Physical Reads >>>

SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
disk_reads, executions, disk_reads/executions "Reads/Exec",
hash_value,address
FROM V$SQLAREA
WHERE disk_reads > 1000
ORDER BY disk_reads DESC)
WHERE rownum <= 10;

For Top Executions >>>

SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
executions, rows_processed, rows_processed/executions "Rows/Exec",
hash_value,address
FROM V$SQLAREA
WHERE executions > 100
ORDER BY executions DESC)
WHERE rownum <= 10;

For Top Parse Calls >>>

SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
parse_calls, executions, hash_value,address
FROM V$SQLAREA
WHERE parse_calls > 1000
ORDER BY parse_calls DESC)
WHERE rownum <= 10;