Please explain how WITH Clause working in a sql query?

An inline view or a query uses subquery in it.
Subquery execution done more efficiently rather by using the query repetadly.
On case by case the performance varies with WITH clause in a sql query.

Below example shows how to reduce repetition of query and simplify complex SQL statements.

SELECT e.ename AS employee_name,
       dc.d_count AS emp_dept_count
FROM   emp e,
       (SELECT deptid, COUNT(*) AS d_count
        FROM   emp
        GROUP BY deptid) dc
WHERE  e.deptid = dc.deptid;

Query will be like as below by using a WITH clause.

WITH d_count AS (
  SELECT deptid, COUNT(*) AS dept_count
  FROM   emp
  GROUP BY deptid)
SELECT e.ename AS employee_name,
       dc.dept_count AS emp_dept_count
FROM   emp e,
       d_count dc
WHERE  e.deptid = dc.deptid;

This way we can make multiple references to the subquery more efficient.

Sometime,WITH clause can be used for a subquery to use as a global temporary table or the query to be use as inline query.

What are Private procedure / functions in a Package ?

A function or procedure in a package which is not declared in package specification, its called as private function / procedure.

Scope of private function /procedure is very limited - on the package itself only.

While in declaring the private procedure/function in package, need to take care that it must be declare first in package declaration part of the package.

create or replace package pack_p1 is
    procedure proc_1;
end pack_p1;

create or replace package body pack_p1 is

    procedure test_proc_1 is
    begin
        dbms_output.put_line('package pack_p1 private procedure');
    end test_proc_1;

    function func_1 is
    begin
        dbms_output.put_line('package pack_p1 private function');
    end func_1;

    procedure proc_1 is
    begin
        dbms_output.put_line('package pack_p1 - proc 1');
        test_proc_1;
    end proc_1;

end pack_p1;

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