Oracle performance Tuning

Oracle Performance:

This single script  provides the overall picture of the database in terms of Waits events, Active/Inactive killed sessions, Top Processes (physical i/o, logical i/o, memory and CPU processes),Top CPU usage by users Etc

set serveroutput on

declare 

cursor c1 is select version

from v$instance;

cursor c2 is

    select

          host_name

       ,  instance_name

       ,  to_char(sysdate, 'HH24:MI:SS DD-MON-YY') currtime

       ,  to_char(startup_time, 'HH24:MI:SS DD-MON-YY') starttime

     from v$instance;

cursor c4 is

select * from (SELECT count(*) cnt, substr(event,1,50) event

FROM v$session_wait

WHERE wait_time = 0

AND event NOT IN ('smon timer','pipe get','wakeup time manager','pmon timer','rdbms ipc message',

'SQL*Net message from client')

GROUP BY event

ORDER BY 1 DESC) where rownum <6;

cursor c5 is

select round(sum(value)/1048576) as sgasize from v$sga;

cursor c6 is select round(sum(bytes)/1048576) as dbsize

from v$datafile;

cursor c7 is select 'top physical i/o process' category, sid,

       username, total_user_io amt_used,

       round(100 * total_user_io/total_io,2) pct_used

from (select b.sid sid, nvl(b.username, p.name) username,

             sum(value) total_user_io

      from v$statname c, v$sesstat a,

           v$session b, v$bgprocess p

      where a.statistic# = c.statistic#

      and p.paddr (+) = b.paddr

      and b.sid = a.sid

      and c.name in ('physical reads', 'physical writes',

                     'physical reads direct',

                     'physical reads direct (lob)',

                     'physical writes direct',

                     'physical writes direct (lob)')

      and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')

      group by b.sid, nvl(b.username, p.name)

      order by 3 desc),

     (select sum(value) total_io

      from v$statname c, v$sesstat a

      where a.statistic# = c.statistic#

      and c.name in ('physical reads', 'physical writes',

                       'physical reads direct',

                       'physical reads direct (lob)',

                       'physical writes direct',

                       'physical writes direct (lob)'))

where rownum < 2

union all

select 'top logical i/o process', sid, username,

       total_user_io amt_used,

       round(100 * total_user_io/total_io,2) pct_used

from (select b.sid sid, nvl(b.username, p.name) username,

             sum(value) total_user_io

      from v$statname c, v$sesstat a,

           v$session b, v$bgprocess p

      where a.statistic# = c.statistic#

      and p.paddr (+) = b.paddr

      and b.sid = a.sid

      and c.name in ('consistent gets', 'db block gets')

      and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')

      group by b.sid, nvl(b.username, p.name)

      order by 3 desc),

     (select sum(value) total_io

      from v$statname c, v$sesstat a,

           v$session b, v$bgprocess p

      where a.statistic# = c.statistic#

      and p.paddr (+) = b.paddr

 and b.sid = a.sid

      and c.name in ('consistent gets', 'db block gets'))

where rownum < 2

union all

select 'top memory process', sid,

       username, total_user_mem,

       round(100 * total_user_mem/total_mem,2)

from (select b.sid sid, nvl(b.username, p.name) username,

             sum(value) total_user_mem

      from v$statname c, v$sesstat a,

           v$session b, v$bgprocess p

      where a.statistic# = c.statistic#

      and p.paddr (+) = b.paddr

      and b.sid = a.sid

      and c.name in ('session pga memory', 'session uga memory')

      and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')

      group by b.sid, nvl(b.username, p.name)

      order by 3 desc),

     (select sum(value) total_mem

      from v$statname c, v$sesstat a

      where a.statistic# = c.statistic#

      and c.name in ('session pga memory', 'session uga memory'))

where rownum < 2

union all

select 'top cpu process', sid, username,

       total_user_cpu,

       round(100 * total_user_cpu/greatest(total_cpu,1),2)

from (select b.sid sid, nvl(b.username, p.name) username,

             sum(value) total_user_cpu

      from v$statname c, v$sesstat a,

           v$session b, v$bgprocess p

      where a.statistic# = c.statistic#

      and p.paddr (+) = b.paddr

      and b.sid = a.sid

      and c.name = 'CPU used by this session'

      and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')

      group by b.sid, nvl(b.username, p.name)

      order by 3 desc),

     (select sum(value) total_cpu

      from v$statname c, v$sesstat a,

           v$session b, v$bgprocess p

      where a.statistic# = c.statistic#

      and p.paddr (+) = b.paddr

      and b.sid = a.sid

      and c.name = 'CPU used by this session')

where rownum < 2;

cursor c8 is select username, sum(VALUE/100) cpu_usage_sec

from v$session ss, v$sesstat se, v$statname sn

where se.statistic# = sn.statistic#

and name like '%CPU used by this session%'

and se.sid = ss.sid

and username is not null

and username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')

group by username

order by 2 desc;

begin

dbms_output.put_line ('Database Version');

dbms_output.put_line ('-----------------');

for rec in c1

loop

dbms_output.put_line(rec.version);

end loop;

dbms_output.put_line( chr(13) );

dbms_output.put_line('Hostname');

dbms_output.put_line ('----------');

for rec in c2

loop

     dbms_output.put_line(rec.host_name);

end loop;

dbms_output.put_line( chr(13) );

dbms_output.put_line('SGA Size (MB)');

dbms_output.put_line ('-------------');

for rec in c5

loop

     dbms_output.put_line(rec.sgasize);

end loop;

dbms_output.put_line( chr(13) );

dbms_output.put_line('Database Size (MB)');

dbms_output.put_line ('-----------------');

for rec in c6

loop

     dbms_output.put_line(rec.dbsize);

end loop;

dbms_output.put_line( chr(13) );

dbms_output.put_line('Instance start-up time');

dbms_output.put_line ('-----------------------');

for rec in c2 loop

 dbms_output.put_line( rec.starttime );

  end loop;

dbms_output.put_line( chr(13) );

  for b in

    (select total, active, inactive, system, killed

    from

       (select count(*) total from v$session)

     , (select count(*) system from v$session where username is null)

     , (select count(*) active from v$session where status = 'ACTIVE' and username is not null)

     , (select count(*) inactive from v$session where status = 'INACTIVE')

     , (select count(*) killed from v$session where status = 'KILLED')) loop

dbms_output.put_line('Active Sessions');

dbms_output.put_line ('---------------');

dbms_output.put_line(b.total || ' sessions: ' || b.inactive || ' inactive,' || b.active || ' active, ' || b.system || ' system, ' || b.killed || ' killed ');

  end loop;

  dbms_output.put_line( chr(13) );

 dbms_output.put_line( 'Sessions Waiting' );

  dbms_output.put_line( chr(13) );

dbms_output.put_line('Count      Event Name');

dbms_output.put_line('-----      -----------------------------------------------------');

for rec in c4 

loop

dbms_output.put_line(rec.cnt||'          '||rec.event);

end loop;

dbms_output.put_line( chr(13) );

dbms_output.put_line('-----      -----------------------------------------------------');

dbms_output.put_line('TOP Physical i/o, logical i/o, memory and CPU processes');

dbms_output.put_line ('---------------');

for rec in c7

loop

dbms_output.put_line (rec.category||': SID '||rec.sid||' User : '||rec.username||': Amount used : '||rec.amt_used||': Percent used: '||rec.pct_used);

end loop;

dbms_output.put_line('------------------------------------------------------------------');

dbms_output.put_line('TOP CPU users by usage');

dbms_output.put_line ('---------------');

for rec in c8

loop

dbms_output.put_line (rec.username||'--'||rec.cpu_usage_sec);

dbms_output.put_line ('---------------');

end loop;

end;

Top Recent Wait Events

col EVENT format a60 

select * from (
select active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history
where active_session_history.event is not null
group by active_session_history.event
order by 2 desc)
where rownum < 6
/

Top Wait Events Since Instance Startup

col event format a60

select event, total_waits, time_waited
from v$system_event e, v$event_name n
where n.event_id = e.event_id
and n.wait_class !='Idle'
and n.wait_class = (select wait_class from v$session_wait_class
 where wait_class !='Idle'
 group by wait_class having
sum(time_waited) = (select max(sum(time_waited)) from v$session_wait_class
where wait_class !='Idle'
group by (wait_class)))
order by 3;

List Of Users Currently Waiting

col username format a12
col sid format 9999
col state format a15
col event format a50
col wait_time format 99999999
set pagesize 100
set linesize 120

select s.sid, s.username, se.event, se.state, se.wait_time
from v$session s, v$session_wait se
where s.sid=se.sid
and se.event not like 'SQL*Net%'
and se.event not like '%rdbms%'
and s.username is not null
order by se.wait_time;

Find The Main Database Wait Events In A Particular Time Interval

First determine the snapshot id values for the period in question.

In this example we need to find the SNAP_ID for the period 10 PM to 11 PM on the 14th of November, 2012.

select snap_id,begin_interval_time,end_interval_time
from dba_hist_snapshot
where to_char(begin_interval_time,'DD-MON-YYYY')='14-NOV-2012'
and EXTRACT(HOUR FROM begin_interval_time) between 22 and 23;

set verify off
select * from (
select active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from dba_hist_active_sess_history active_session_history
where event is not null
and SNAP_ID between &ssnapid and &esnapid
group by active_session_history.event
order by 2 desc)
where rownum

Top CPU Consuming SQL During A Certain Time Period

Note – in this case we are finding the Top 5 CPU intensive SQL statements executed between 9.00 AM and 11.00 AM

select * from (
select
SQL_ID,
 sum(CPU_TIME_DELTA),
sum(DISK_READS_DELTA),
count(*)
from
DBA_HIST_SQLSTAT a, dba_hist_snapshot s
where
s.snap_id = a.snap_id
and s.begin_interval_time > sysdate -1
and EXTRACT(HOUR FROM S.END_INTERVAL_TIME) between 9 and 11
group by
SQL_ID
order by
sum(CPU_TIME_DELTA) desc)
where rownum

Which Database Objects Experienced the Most Number of Waits in the Past One Hour

set linesize 120
col event format a40
col object_name format a40

select * from 
(
  select dba_objects.object_name,
 dba_objects.object_type,
active_session_history.event,
 sum(active_session_history.wait_time +
  active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history,
    dba_objects
 where 
active_session_history.sample_time between sysdate - 1/24 and sysdate
and active_session_history.current_obj# = dba_objects.object_id
 group by dba_objects.object_name, dba_objects.object_type, active_session_history.event
 order by 4 desc)
where rownum < 6;

Top Segments ordered by Physical Reads

col segment_name format a20
col owner format a10 
select segment_name,object_type,total_physical_reads
 from ( select owner||'.'||object_name as segment_name,object_type,
value as total_physical_reads
from v$segment_statistics
 where statistic_name in ('physical reads')
 order by total_physical_reads desc)
 where rownum

Top 5 SQL statements in the past one hour

select * from (
select active_session_history.sql_id,
 dba_users.username,
 sqlarea.sql_text,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history,
v$sqlarea sqlarea,
 dba_users
where 
active_session_history.sample_time between sysdate -  1/24  and sysdate
  and active_session_history.sql_id = sqlarea.sql_id
and active_session_history.user_id = dba_users.user_id
 group by active_session_history.sql_id,sqlarea.sql_text, dba_users.username
 order by 4 desc )
where rownum

SQL with the highest I/O in the past one day

select * from 
(
SELECT /*+LEADING(x h) USE_NL(h)*/ 
       h.sql_id
,      SUM(10) ash_secs
FROM   dba_hist_snapshot x
,      dba_hist_active_sess_history h
WHERE   x.begin_interval_time > sysdate -1
AND    h.SNAP_id = X.SNAP_id
AND    h.dbid = x.dbid
AND    h.instance_number = x.instance_number
AND    h.event in  ('db file sequential read','db file scattered read')
GROUP BY h.sql_id
ORDER BY ash_secs desc )
where rownum

Top CPU consuming queries since past one day

select * from (
select 
 SQL_ID, 
 sum(CPU_TIME_DELTA), 
 sum(DISK_READS_DELTA),
 count(*)
from 
 DBA_HIST_SQLSTAT a, dba_hist_snapshot s
where
 s.snap_id = a.snap_id
 and s.begin_interval_time > sysdate -1
 group by 
 SQL_ID
order by 
 sum(CPU_TIME_DELTA) desc)
where rownum

Find what the top SQL was at a particular reported time of day

First determine the snapshot id values for the period in question.

In thos example we need to find the SNAP_ID for the period 10 PM to 11 PM on the 14th of November, 2012.

select snap_id,begin_interval_time,end_interval_time
from dba_hist_snapshot
where to_char(begin_interval_time,'DD-MON-YYYY')='14-NOV-2012'
and EXTRACT(HOUR FROM begin_interval_time) between 22 and 23;

select * from
 (
select
 sql.sql_id c1,
sql.buffer_gets_delta c2,
sql.disk_reads_delta c3,
sql.iowait_delta c4
 from
dba_hist_sqlstat sql,
dba_hist_snapshot s
 where
 s.snap_id = sql.snap_id
and
 s.snap_id= &snapid
 order by
 c3 desc)
 where rownum < 6 
/

Analyse a particular SQL ID and see the trends for the past day

select
 s.snap_id,
 to_char(s.begin_interval_time,'HH24:MI') c1,
 sql.executions_delta c2,
 sql.buffer_gets_delta c3,
 sql.disk_reads_delta c4,
 sql.iowait_delta c5,
sql.cpu_time_delta c6,
 sql.elapsed_time_delta c7
 from
 dba_hist_sqlstat sql,
 dba_hist_snapshot s
 where
 s.snap_id = sql.snap_id
 and s.begin_interval_time > sysdate -1
 and
sql.sql_id='&sqlid'
 order by c7
 /

Do we have multiple plan hash values for the same SQL ID – in that case may be changed plan is causing bad performance

select 
  SQL_ID 
, PLAN_HASH_VALUE 
, sum(EXECUTIONS_DELTA) EXECUTIONS
, sum(ROWS_PROCESSED_DELTA) CROWS
, trunc(sum(CPU_TIME_DELTA)/1000000/60) CPU_MINS
, trunc(sum(ELAPSED_TIME_DELTA)/1000000/60)  ELA_MINS
from DBA_HIST_SQLSTAT 
where SQL_ID in (
'&sqlid') 
group by SQL_ID , PLAN_HASH_VALUE
order by SQL_ID, CPU_MINS;

Top 5 Queries for past week based on ADDM recommendations

/*
Top 10 SQL_ID's for the last 7 days as identified by ADDM
from DBA_ADVISOR_RECOMMENDATIONS and dba_advisor_log
*/

col SQL_ID form a16
col Benefit form 9999999999999
select * from (
select b.ATTR1 as SQL_ID, max(a.BENEFIT) as "Benefit" 
from DBA_ADVISOR_RECOMMENDATIONS a, DBA_ADVISOR_OBJECTS b 
where a.REC_ID = b.OBJECT_ID
and a.TASK_ID = b.TASK_ID
and a.TASK_ID in (select distinct b.task_id
from dba_hist_snapshot a, dba_advisor_tasks b, dba_advisor_log l
where a.begin_interval_time > sysdate - 7 
and  a.dbid = (select dbid from v$database) 
and a.INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance) 
and to_char(a.begin_interval_time, 'yyyymmddHH24') = to_char(b.created, 'yyyymmddHH24') 
and b.advisor_name = 'ADDM' 
and b.task_id = l.task_id 
and l.status = 'COMPLETED') 
and length(b.ATTR4) > 1 group by b.ATTR1
order by max(a.BENEFIT) desc) where rownum < 6;

— Use Longops To Check The Estimation Runtime

SELECT sid, serial#, opname, target, sofar, totalwork, units, start_time,

last_update_time, time_remaining “REMAIN SEC”, round(time_remaining/60,2) “REMAIN MINS”,

elapsed_seconds “ELAPSED SEC”, round(elapsed_seconds/60,2) “ELAPSED MINS”,

round((time_remaining+elapsed_seconds)/60,2)”TOTAL MINS”, message TIME

FROM v$session_longops

WHERE sofar<>totalwork

AND time_remaining <> ‘0’

-- build script to load profile

@coe_xfr_sql_profile.sql gpm6wzuy8yabq 1950795681

-- run generated script

@coe_xfr_sql_profile_gpm6wzuy8yabq_1950795681.sql

-- ORALCE COE

SPO coe_xfr_sql_profile.log;
SET DEF ON TERM OFF ECHO ON FEED OFF VER OFF HEA ON LIN 2000 PAGES 100 LONG 8000000 LONGC 800000 TRIMS ON TI OFF TIMI OFF SERVEROUT ON SIZE 1000000 NUMF "" SQLP SQL>;
REM
REM $Header: 215187.1 coe_xfr_sql_profile.sql 11.4.1.4 2010/07/12 csierra $
REM
REM Copyright (c) 2000-2010, Oracle Corporation. All rights reserved.
REM
REM AUTHOR
REM   carlos.sierra@oracle.com
REM
REM SCRIPT
REM   coe_xfr_sql_profile.sql
REM
REM DESCRIPTION
REM   This script generates another that contains the commands to
REM   create a manual custom SQL Profile out of a known plan from
REM   memory or AWR. The manual custom profile can be implemented
REM   into the same SOURCE system where the plan was retrieved,
REM   or into another similar TARGET system that has same schema
REM   objects referenced by the SQL that generated the known plan.
REM
REM PRE-REQUISITES
REM   1. Oracle Tuning Pack license.
REM
REM PARAMETERS
REM   1. SQL_ID (required)
REM   2. Plan Hash Value for which a manual custom SQL Profile is
REM      needed (required). A list of known plans is presented.
REM
REM EXECUTION
REM   1. Connect into SQL*Plus as SYSDBA or user with access to
REM      data dictionary.
REM   2. Execute script coe_xfr_sql_profile.sql passing SQL_ID and
REM      plan hash value (parameters can be passed inline or until
REM      requested).
REM
REM EXAMPLE
REM   # sqlplus system
REM   SQL> START coe_xfr_sql_profile.sql [SQL_ID] [PLAN_HASH_VALUE];
REM   SQL> START coe_xfr_sql_profile.sql gnjy0mn4y9pbm 2055843663;
REM   SQL> START coe_xfr_sql_profile.sql gnjy0mn4y9pbm;
REM   SQL> START coe_xfr_sql_profile.sql;
REM
REM NOTES
REM   1. For possible errors see coe_xfr_sql_profile.log
REM   2. If SQLT is installed in SOURCE, you can use instead:
REM      sqlt/utl/sqltprofile.sql
REM   3. Be aware that using DBMS_SQLTUNE requires a license for
REM      Oracle Tuning Pack.
REM
SET TERM ON ECHO OFF;
PRO
PRO Parameter 1:
PRO SQL_ID (required)
PRO
DEF sql_id = '&1';
PRO
WITH
p AS (
SELECT plan_hash_value
  FROM gv$sql_plan
 WHERE sql_id = TRIM('&&sql_id.')
   AND other_xml IS NOT NULL
 UNION
SELECT plan_hash_value
  FROM dba_hist_sql_plan
 WHERE sql_id = TRIM('&&sql_id.')
   AND other_xml IS NOT NULL ),
m AS (
SELECT plan_hash_value,
       SUM(elapsed_time)/SUM(executions) avg_et_secs
  FROM gv$sql
 WHERE sql_id = TRIM('&&sql_id.')
   AND executions > 0
 GROUP BY
       plan_hash_value ),
a AS (
SELECT plan_hash_value,
       SUM(elapsed_time_total)/SUM(executions_total) avg_et_secs
  FROM dba_hist_sqlstat
 WHERE sql_id = TRIM('&&sql_id.')
   AND executions_total > 0
 GROUP BY
       plan_hash_value )
SELECT p.plan_hash_value,
       ROUND(NVL(m.avg_et_secs, a.avg_et_secs)/1e6, 3) avg_et_secs
  FROM p, m, a
 WHERE p.plan_hash_value = m.plan_hash_value(+)
   AND p.plan_hash_value = a.plan_hash_value(+)
 ORDER BY
       avg_et_secs NULLS LAST;
PRO
PRO Parameter 2:
PRO PLAN_HASH_VALUE (required)
PRO
DEF plan_hash_value = '&2';
PRO
PRO Values passed:
PRO ~~~~~~~~~~~~~
PRO SQL_ID         : "&&sql_id."
PRO PLAN_HASH_VALUE: "&&plan_hash_value."
PRO
SET TERM OFF ECHO ON;
WHENEVER SQLERROR EXIT SQL.SQLCODE;

VAR sql_text CLOB;
VAR other_xml CLOB;
EXEC :sql_text := NULL;
EXEC :other_xml := NULL;

-- get sql_text from memory
DECLARE
  l_sql_text VARCHAR2(32767);
BEGIN -- 10g see bug 5017909
  FOR i IN (SELECT DISTINCT piece, sql_text
              FROM gv$sqltext_with_newlines
             WHERE sql_id = TRIM('&&sql_id.')
             ORDER BY 1, 2)
  LOOP
    IF :sql_text IS NULL THEN
      DBMS_LOB.CREATETEMPORARY(:sql_text, TRUE);
      DBMS_LOB.OPEN(:sql_text, DBMS_LOB.LOB_READWRITE);
    END IF;
    l_sql_text := REPLACE(i.sql_text, CHR(00), ' ');
    DBMS_LOB.WRITEAPPEND(:sql_text, LENGTH(l_sql_text), l_sql_text);
  END LOOP;
  IF :sql_text IS NOT NULL THEN
    DBMS_LOB.CLOSE(:sql_text);
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('getting sql_text from memory: '||SQLERRM);
    :sql_text := NULL;
END;
/

-- get sql_text from awr
BEGIN
  IF :sql_text IS NULL OR NVL(DBMS_LOB.GETLENGTH(:sql_text), 0) = 0 THEN
    SELECT REPLACE(sql_text, CHR(00), ' ')
      INTO :sql_text
      FROM dba_hist_sqltext
     WHERE sql_id = TRIM('&&sql_id.')
       AND sql_text IS NOT NULL
       AND ROWNUM = 1;
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('getting sql_text from awr: '||SQLERRM);
    :sql_text := NULL;
END;
/

SELECT :sql_text FROM DUAL;

-- get other_xml from memory
BEGIN
  FOR i IN (SELECT other_xml
              FROM gv$sql_plan
             WHERE sql_id = TRIM('&&sql_id.')
               AND plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.'))
               AND other_xml IS NOT NULL
             ORDER BY
                   child_number, id)
  LOOP
    :other_xml := i.other_xml;
    EXIT; -- 1st
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('getting other_xml from memory: '||SQLERRM);
    :other_xml := NULL;
END;
/

-- get other_xml from awr
BEGIN
  IF :other_xml IS NULL OR NVL(DBMS_LOB.GETLENGTH(:other_xml), 0) = 0 THEN
    FOR i IN (SELECT other_xml
                FROM dba_hist_sql_plan
               WHERE sql_id = TRIM('&&sql_id.')
                 AND plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.'))
                 AND other_xml IS NOT NULL
               ORDER BY
                     id)
    LOOP
      :other_xml := i.other_xml;
      EXIT; -- 1st
    END LOOP;
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('getting other_xml from awr: '||SQLERRM);
    :other_xml := NULL;
END;
/

SELECT :other_xml FROM DUAL;

-- generates script that creates sql profile in target system:
SET ECHO OFF;
PRO coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql.
SET FEED OFF LIN 666 TRIMS ON TI OFF TIMI OFF SERVEROUT ON SIZE 1000000 FOR WOR;
SPO OFF;
SPO coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql;
DECLARE
  l_pos NUMBER;
  l_hint VARCHAR2(32767);
BEGIN
  DBMS_OUTPUT.PUT_LINE('SPO coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..log;');
  DBMS_OUTPUT.PUT_LINE('SET ECHO ON TERM ON LIN 2000 TRIMS ON NUMF 99999999999999999999;');
  DBMS_OUTPUT.PUT_LINE('REM');
  DBMS_OUTPUT.PUT_LINE('REM $Header: 215187.1 coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql 11.4.1.4 '||TO_CHAR(SYSDATE, 'YYYY/MM/DD')||' csierra $');
  DBMS_OUTPUT.PUT_LINE('REM');
  DBMS_OUTPUT.PUT_LINE('REM Copyright (c) 2000-2010, Oracle Corporation. All rights reserved.');
  DBMS_OUTPUT.PUT_LINE('REM');
  DBMS_OUTPUT.PUT_LINE('REM AUTHOR');
  DBMS_OUTPUT.PUT_LINE('REM   carlos.sierra@oracle.com');
  DBMS_OUTPUT.PUT_LINE('REM');
  DBMS_OUTPUT.PUT_LINE('REM SCRIPT');
  DBMS_OUTPUT.PUT_LINE('REM   coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql');
  DBMS_OUTPUT.PUT_LINE('REM');
  DBMS_OUTPUT.PUT_LINE('REM DESCRIPTION');
  DBMS_OUTPUT.PUT_LINE('REM   This script is generated by coe_xfr_sql_profile.sql');
  DBMS_OUTPUT.PUT_LINE('REM   It contains the SQL*Plus commands to create a custom');
  DBMS_OUTPUT.PUT_LINE('REM   SQL Profile for SQL_ID &&sql_id. based on plan hash');
  DBMS_OUTPUT.PUT_LINE('REM   value &&plan_hash_value..');
  DBMS_OUTPUT.PUT_LINE('REM   The custom SQL Profile to be created by this script');
  DBMS_OUTPUT.PUT_LINE('REM   will affect plans for SQL commands with signature');
  DBMS_OUTPUT.PUT_LINE('REM   matching the one for SQL Text below.');
  DBMS_OUTPUT.PUT_LINE('REM   Review SQL Text and adjust accordingly.');
  DBMS_OUTPUT.PUT_LINE('REM');
  DBMS_OUTPUT.PUT_LINE('REM PARAMETERS');
  DBMS_OUTPUT.PUT_LINE('REM   None.');
  DBMS_OUTPUT.PUT_LINE('REM');
  DBMS_OUTPUT.PUT_LINE('REM EXAMPLE');
  DBMS_OUTPUT.PUT_LINE('REM   SQL> START coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql;');
  DBMS_OUTPUT.PUT_LINE('REM');
  DBMS_OUTPUT.PUT_LINE('REM NOTES');
  DBMS_OUTPUT.PUT_LINE('REM   1. Should be run as SYSTEM or SYSDBA.');
  DBMS_OUTPUT.PUT_LINE('REM   2. User must have CREATE ANY SQL PROFILE privilege.');
  DBMS_OUTPUT.PUT_LINE('REM   3. SOURCE and TARGET systems can be the same or similar.');
  DBMS_OUTPUT.PUT_LINE('REM   4. To drop this custom SQL Profile after it has been created:');
  DBMS_OUTPUT.PUT_LINE('REM      EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE(''coe_&&sql_id._&&plan_hash_value.'');');
  DBMS_OUTPUT.PUT_LINE('REM   5. Be aware that using DBMS_SQLTUNE requires a license');
  DBMS_OUTPUT.PUT_LINE('REM      for the Oracle Tuning Pack.');
  DBMS_OUTPUT.PUT_LINE('REM');
  DBMS_OUTPUT.PUT_LINE('WHENEVER SQLERROR EXIT SQL.SQLCODE;');
  DBMS_OUTPUT.PUT_LINE('REM');
  DBMS_OUTPUT.PUT_LINE('VAR signature NUMBER;');
  DBMS_OUTPUT.PUT_LINE('REM');
  DBMS_OUTPUT.PUT_LINE('DECLARE');
  DBMS_OUTPUT.PUT_LINE('sql_txt CLOB;');
  DBMS_OUTPUT.PUT_LINE('h       SYS.SQLPROF_ATTR;');
  DBMS_OUTPUT.PUT_LINE('BEGIN');
  DBMS_OUTPUT.PUT_LINE('sql_txt := q''[');
  WHILE NVL(LENGTH(:sql_text), 0) > 0
  LOOP
    l_pos := INSTR(:sql_text, CHR(10));
    IF l_pos > 0 THEN
      DBMS_OUTPUT.PUT_LINE(SUBSTR(:sql_text, 1, l_pos - 1));
      :sql_text := SUBSTR(:sql_text, l_pos + 1);
    ELSE
      DBMS_OUTPUT.PUT_LINE(:sql_text);
      :sql_text := NULL;
    END IF;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(']'';');
  DBMS_OUTPUT.PUT_LINE('h := SYS.SQLPROF_ATTR(');
  DBMS_OUTPUT.PUT_LINE('q''[BEGIN_OUTLINE_DATA]'',');
  FOR i IN (SELECT /*+ opt_param('parallel_execution_enabled', 'false') */
                   SUBSTR(EXTRACTVALUE(VALUE(d), '/hint'), 1, 4000) hint
              FROM TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(:other_xml), '/*/outline_data/hint'))) d)
  LOOP
    l_hint := i.hint;
    WHILE NVL(LENGTH(l_hint), 0) > 0
    LOOP
      IF LENGTH(l_hint) <= 500 THEN
        DBMS_OUTPUT.PUT_LINE('q''['||l_hint||']'',');
        l_hint := NULL;
      ELSE
        l_pos := INSTR(SUBSTR(l_hint, 1, 500), ' ', -1);
        DBMS_OUTPUT.PUT_LINE('q''['||SUBSTR(l_hint, 1, l_pos)||']'',');
        l_hint := '   '||SUBSTR(l_hint, l_pos);
      END IF;
    END LOOP;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('q''[END_OUTLINE_DATA]'');');
  DBMS_OUTPUT.PUT_LINE(':signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);');
  DBMS_OUTPUT.PUT_LINE('DBMS_SQLTUNE.IMPORT_SQL_PROFILE (');
  DBMS_OUTPUT.PUT_LINE('sql_text    => sql_txt,');
  DBMS_OUTPUT.PUT_LINE('profile     => h,');
  DBMS_OUTPUT.PUT_LINE('name        => ''coe_&&sql_id._&&plan_hash_value.'',');
  DBMS_OUTPUT.PUT_LINE('description => ''coe &&sql_id. &&plan_hash_value. ''||:signature||'''',');
  DBMS_OUTPUT.PUT_LINE('category    => ''DEFAULT'',');
  DBMS_OUTPUT.PUT_LINE('validate    => TRUE,');
  DBMS_OUTPUT.PUT_LINE('replace     => TRUE,');
  DBMS_OUTPUT.PUT_LINE('force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );');
  DBMS_OUTPUT.PUT_LINE('END;');
  DBMS_OUTPUT.PUT_LINE('/');
  DBMS_OUTPUT.PUT_LINE('WHENEVER SQLERROR CONTINUE');
  DBMS_OUTPUT.PUT_LINE('SET ECHO OFF;');
  DBMS_OUTPUT.PUT_LINE('PRINT signature');
  DBMS_OUTPUT.PUT_LINE('PRO');
  DBMS_OUTPUT.PUT_LINE('PRO ... manual custom SQL Profile has been created');
  DBMS_OUTPUT.PUT_LINE('PRO');
  DBMS_OUTPUT.PUT_LINE('SET TERM ON ECHO OFF LIN 80 TRIMS OFF NUMF "";');
  DBMS_OUTPUT.PUT_LINE('SPO OFF;');
  DBMS_OUTPUT.PUT_LINE('PRO');
  DBMS_OUTPUT.PUT_LINE('PRO COE_XFR_SQL_PROFILE_&&sql_id._&&plan_hash_value. completed');
END;
/
SPO OFF;
SET DEF ON TERM ON ECHO OFF FEED 6 VER ON HEA ON LIN 80 PAGES 14 LONG 80 LONGC 80 TRIMS OFF TI OFF TIMI OFF SERVEROUT OFF NUMF "" SQLP SQL>;
PRO
PRO Execute coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql
PRO on TARGET system in order to create a custom SQL Profile
PRO with plan &&plan_hash_value linked to adjusted sql_text.
PRO
UNDEFINE 1 2 sql_id plan_hash_value
PRO
PRO COE_XFR_SQL_PROFILE completed.
 

cloud training

INDIAN PASSPORT DETAILS FOR MINOR

PASSPORT FOR MINOR'S

Services Available

Passport Services Overview Q1: What are the various passport services and which form has to be filled in? A: The various passport services are: Issue of Fresh Passport: You can apply for fresh passport if applying for the first time. Re-issue of Passport: You can apply for re-issue of passport if you want another passport in lieu of an existing passport for any of the following reasons: Change in existing personal particulars. Validity expired within 3 years/ Due to expire. Validity expired more than 3 years ago. Exhaustion of pages. Damaged passport. Lost passport. If you ever held a passport in the past, no matter how much long back or at what age, you should choose the Re-Issue category only. Miscellaneous Service: Issue of Police Clearance Certificate (PCC) Q2: What types of passports are issued in India? A: Following three types of passports are issued in India: Ordinary Passport: An ordinary passport has blue cover, and consists of 36 or 60 pages. It is valid for 10 years from the date of issue and can be renewed for another 10 years. An Indian citizen can use this passport for ordinary vacation or business travel. Diplomatic Passport: Has maroon cover ; Issued to designated members of the national government, judiciary , statutory authorities, diplomats, official public couriers and any other person specifically authorised by the Govt Official Passport: Has grey cover ; Issued to designated Non-gazetted govt servants or any other person specifically authorised by the Govt deputed abroad on government business. Q3: How do I apply for a passport in the new system? A: For issue of fresh passport and re-issue of passport, you need to fill the application form online via e-Form Submission (preferred) or via Online Form Submission. For Police Clearance Certificate (PCC) also you need to fill the Police Clearance Certificate (PCC) Application Form online via e-Form Submission (preferred) or via Online Form Submission. Under the new system it is mandatory for all applicants, including infants, to be physically present at the Passport Seva Kendra (PSK) to give their biometrics(fingerprints) and photographs. Minor Q4: What is the definition of minor for issue of passport? A: Applicants less than 18 years of age are considered as minor for issuance of passport. Q5: I have a one-month old baby? Does the baby require a separate passport or his/her name can be endorsed in one of the parents passport? A: Endorsement of child's name in parent's passports is not allowed anymore. As per the amended instructions , a minor should have a separate passport. Q6: In case of minor's passport, whose father is abroad, is it mandatory to obtain father's consent to apply for the child's passport? If so, what is the procedure? A: If parent(s) of the minor holds a valid passport, then attested photocopy of passport needs to be submitted along with the application form. Spouse name should be endorsed on the parent's passport. If parent(s) hold a valid passport, but spouse name is not endorsed, then they must get the spouse name added in their passport. For this, they have to apply for re-issue of passport and get the specified change done in personal particulars. Q7: We are planning to apply for passports for our kids (minors). Is it necessary that my name should be included in my wife's passport? A: If either parent of a minor holds a valid passport with spouse name endorsed, passport will be issued to the minor without any police verification. Original passport of parent(s) should be presented for the verification of particulars. If parent(s) hold a valid passport, but spouse name is not endorsed, then they must get the spouse name added in their passport. For this, they have to apply for re-issue of passport and get the specified change done in personal particulars. Thus, it is necessary that spouse name should be endorsed on the parent's passport. Q8: Is it necessary that both parents should have passport before applying for their child's passport? A: It is not necessary that both parents should have a passport before applying for their child's passport. The advantage of either parent holding a valid passport with spouse name endorsed is that it exempts their child from the Police Verification process. Q9: I am a minor who is 16 years old. Is it possible for me to obtain a full validity passport of 10 years? A: Minors between 15 to 18 years of age can apply either for a 10-year validity passport or they can apply for a passport, which is valid till they attain the age of 18 years. Fee for a 10-year validity passport is higher than fee for a passport, which is valid till they attain the age of 18 years. A 10-year validity passport cannot be issued to applicants without police verification on submission of parent's passport. If passport is required on an urgent basis, they can apply under Tatkaal scheme. Q10: What would be the validity of a minor's passport? A: The validity of a minor's passport is restricted to five years or till they attain the age of 18, whichever is earlier. But the minors aged between 15 to 18 years can apply either for a 10-year validity passport or for a passport which is valid till they attain the age of 18 years. Different fees are applicable depending upon which category they are applying for. Q11: Who can apply for my minor daughter's passport in India as I am on ship at Singapore as second engineer? A: Either parent/guardian can apply for minor's passport. You would however need to get Annexure H duly attested by the Consular section of the nearest Indian Mission. To check the complete list of documents to be submitted along with the application form, please click on "Documents Required" link on Home page. Q12: Is it true that passport applications for minors under 18 require consent of either both parents or legal guardian? A: It is necessary that either parent/legal guardian should give his / her consent while applying for minor's passport. Following documents have to be submitted in the given cases: S.No. Case Documents to be submitted 1. Passport application of a minor with consent of both parents Annexure "H" 2. Applied by Legal Guardian Annexure "H" 3. Minors with single parent (One parent deceased) Annexure "H" 4. Applied by one parent/guardian when consent of one or both parents is not possible Annexure "G" 5. Parents are separated but not divorced Annexure "C" 6. Single parent of the child born out of wedlock Annexure "C" Q13: My spouse is living in another country. How do I get his/her signature for consent while applying for my child's passport? A: Consent of the spouse, who is abroad, is required for submitting the application of their child. It should be in the form of a Sworn Affidavit duly attested by the Indian Embassy/Consulate abroad (as per Annexure "H"). To check the complete list of documents to be submitted along with the application form, please click on"Documents Required" link on Home page. Q14: I want to apply for my child's passport. Are signatures of both parents mandatory on Annexure H to be submitted along with the application? What if my spouse is refusing to sign the form? A: Yes, the signature of both the parents is mandatory on Annexure H to be submitted along with the application as it signifies that both parents are giving consent for the issuance of passport to the child. Annexure H is the declaration of applicant's parents or guardian for the issuance of passport to the minor. If either of the parents is not giving consent, then the parent applying for the minor's passport needs to submit Annexure G. To see the format of Annexure H and G, please click on "Annexures/Affidavits" link on Home page. Q15: I wish to apply for a passport for my child who is 12 months old. Can I apply for a 60 pages booklet? A: No. Only 36 pages passport booklet are issued to minors. Q16: If parents have passport with adverse Police Verification Report or they have a criminal background, can passport be issued to minor on an urgent basis? A: In such cases, minors can be issued passport under Tatkaal scheme subject to the approval of the RPO / Competent Authority Q17: I want to apply for my minor child's passport. I hold a valid passport but spouse name is not endorsed. Can I apply for my child's passport without submitting my passport copy? A: If parent(s) of the minor holds a valid passport, then attested photocopy of passport needs to be submitted along with the application form. Spouse name should be endorsed on the parent's passport. If parent(s) hold a valid passport, but spouse name is not endorsed, then they must get the spouse name added in their passport. For this, they have to apply for re-issue of passport and get the specified change done in personal particulars. Q18: Do I need to carry photographs of my 3 months old child while visiting the Passport Seva Kendra (PSK)/Passport Office (PO)? A: Minor applicants below the age of 4 years need to carry a passport size photograph for the processing of application at the PSK/PO. For applicants above the age of 4 years, photographs will be captured at the PSK/PO itself. S.No. DOs DON'Ts 1. Paste ONLY ONE COLOUR photograph as per the specifications given below Do not paste black and white photographs 2. Paste your recent passport size photograph (4.5 cm length x 3.5 cm width) in colour in the box meant for affixing the photograph Dimensions of photograph should not be smaller than the box (i.e. 4.5 cm length x 3.5 cm width) provided in the application form 3. Background of the photograph should be plain white and the dress should be in dark colour Photograph with dark background or in uniform, or with eyes hidden under coloured or dark glasses will not be accepted 4. Photograph should fit within the given box Photograph in computer print will not be accepted 5. Frontal view of the full face should be visible in the photograph Photograph is NOT to be signed 6. Photograph should be printed on good quality photo paper Distracting shadows on the face or on the background should not be there 7. Print of the photograph should be clear and with a continuous-tone quality Eyes must not be covered by hair. Glares on eyeglasses should be avoided with a slight upward or downward tilt of the head 8. Expression of the face should be natural (no grinning, frowning or raised eyebrows) Photograph should not be damaged, for example: torn, creased, or marked 9. Eyes must be open and both edges of face must be clearly visible Head coverings are not permitted except for religious reasons, but the facial features from bottom of chin to top of forehead and both edges of the face must be clearly visible 10. Head should be in the centre of the frame and both ears should be visible Photographs cut from group photographs are not acceptable

The Department of Posts on Friday launched ‘Selvamagal Semipu’, a new deposit scheme for the girl child.

The department, which already has a facility for savings bank accounts for children, has started this scheme exclusively for girls below 10 years of age, said officials. Parents or guardians can deposit up to Rs. 1.5 lakh a year in this scheme, which provides an interest rate of 9.1 per cent.

The scheme allows customers to pay in multiples of Rs. 100, and the deposit amount need not be a constant every month. While the account can be closed only after the account-holder turns 21, the amount can be partially withdrawn for reasons including higher education, when the account-holder is 18 years old.

At present, nearly 20 lakh people hold various types of savings accounts in 230 post offices across the city and its fringes. Of these, nearly 10 per cent are in the name of minors aged above 10 years.

The new scheme, referred to as ‘Sukanya Samriddhi’ in other states, will be available at all post offices.

The department plans to create awareness among customers, and targets opening one lakh accounts this fiscal.