Friday, 28 October 2011

Oracle Metalink script Note 131704.1

Database Scripts Library Index [ID 131704.1]

Oracle Database growth per month

col Month format a30
select to_char(creation_time, 'RRRR Month') "Month",sum(bytes)/1024/1024 "Growth in Meg" from sys.v_$datafile
where creation_time > SYSDATE-365
group by creation_time

col year format a20
select to_char(CREATION_TIME,'RRRR') year, to_char(CREATION_TIME,'MM') month, round(sum(bytes)/1024/1024/1024) GB
from v$datafile
group by to_char(CREATION_TIME,'RRRR'), to_char(CREATION_TIME,'MM')
order by 1, 2;

YEAR MONTH GB
-------------------- ------------------------------ ----------
2005 02 6
2005 04 21
2006 04 1
2006 09 2
2007 01 8
2007 03 2
2007 07 2
2008 01 2
2008 06 2
2008 10 2
2009 07 0
2009 12 2
2010 09 2
2010 12 2
2011 05 2
2011 08 2
2011 10 2

Oracle Active Session dump and loader

Advanced ASH Usage
ASH Dump data

SQL> alter system set events 'immediate trace name ashdump level 10';
SQL> oradebug setmypid
SQL> oradebug dump ashdump 10;

SQL> oradebug unlimit

SQL> oradebug setmypid
Statement processed.

SQL> oradebug dump ashdump 10;
Statement processed.

or

SQL> alter session set events 'immediate trace name ashdump level 10';

SQL> oradebug tracefile_name
/opt/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_30527.trc

it's structure is slightly different than that of the V$ACTIVE_SESSION_HISTORY view).
So, here is the DDL to create the ashdump table:

Drop table ashdump;

SQL> connect system/sys
Connected.

SQL>

========================

Release 11.2.0.1.0

SQL> CREATE TABLE ashdump AS SELECT * FROM SYS.WRH$_ACTIVE_SESSION_HISTORY WHERE rownum < 0 ----<<<<<<<<<<<<<< Table created.

sqlldr userid=system/sys control='/home/oracle/ashldr.ctl' data='/opt/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_30527.trc'

Release 11.2.0.1.0

control file ashldr.ctl <<<<<<

load data
infile * "str '\n####\n'"
append
into table ashdump
fields terminated by ',' optionally enclosed by '"'
(
SNAP_ID CONSTANT 0 ,
DBID ,
INSTANCE_NUMBER ,
SAMPLE_ID ,
SAMPLE_TIME TIMESTAMP ENCLOSED BY '"' AND '"' "TO_TIMESTAMP(:SAMPLE_TIME ,'MM-DD-YYYY HH24:MI:SSXFF')" ,
SESSION_ID ,
SESSION_SERIAL# ,
SESSION_TYPE ,
USER_ID ,
SQL_ID ,
SQL_CHILD_NUMBER ,
SQL_OPCODE ,
FORCE_MATCHING_SIGNATURE ,
TOP_LEVEL_SQL_ID ,
TOP_LEVEL_SQL_OPCODE ,
SQL_PLAN_HASH_VALUE ,
SQL_PLAN_LINE_ID ,
SQL_PLAN_OPERATION# ,
SQL_PLAN_OPTIONS# ,
SQL_EXEC_ID ,
SQL_EXEC_START DATE 'MM/DD/YYYY HH24:MI:SS' ENCLOSED BY '"' AND '"' ":SQL_EXEC_START" ,
PLSQL_ENTRY_OBJECT_ID ,
PLSQL_ENTRY_SUBPROGRAM_ID ,
PLSQL_OBJECT_ID ,
PLSQL_SUBPROGRAM_ID ,
QC_INSTANCE_ID ,
QC_SESSION_ID ,
QC_SESSION_SERIAL# ,
EVENT_ID ,
SEQ# ,
P1 ,
P2 ,
P3 ,
WAIT_TIME ,
TIME_WAITED ,
BLOCKING_SESSION ,
BLOCKING_SESSION_SERIAL# ,
BLOCKING_INST_ID ,
CURRENT_OBJ# ,
CURRENT_FILE# ,
CURRENT_BLOCK# ,
CURRENT_ROW# ,
TOP_LEVEL_CALL# ,
CONSUMER_GROUP_ID ,
XID ,
REMOTE_INSTANCE# ,
TIME_MODEL ,
SERVICE_HASH ,
PROGRAM ,
MODULE ,
ACTION ,
CLIENT_ID ,
MACHINE ,
PORT ,
ECID
)

SQL> oradebug tracefile_name
/opt/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_30527.trc

Having created the table, you will need to edit the trace file before you can actually load it, removing the header and footer records (or you can change the ashldr.ctl file, whichever you prefer). After removing the header, you then use SQL*Loader to load the trace file contents into the physical file as seen in this example:

edit this file /opt/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_30527.trc

delete the line

oracle@apt-amd-02:~> sqlldr userid=system/sys control='/home/oracle/ashldr.ctl' data='/opt/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_30527.trc'

SQL*Loader: Release 11.2.0.1.0 - Production on Mon Sep 12 15:15:24 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Commit point reached - logical record count 18
Commit point reached - logical record count 27

Oracle Standby parameter check

Oracle Standby Parameter
Standby parameter check

set linesize 200
col name format a30
col value format a80
select NAME,nvl(VALUE,'Not Set') value from v$parameter where
name in (
'standby_file_management',
'log_file_name_convert',
'db_file_name_convert',
'fal_client',
'fal_server',
'log_archive_format',
'remote_login_passwordfile',
'log_archive_dest_state_n',
'log_archive_dest_n',
'control_files',
'log_archive_config',
'service_names',
'db_unique_name',
'db_name',
'db_unique_name','dg_broker_start') or name like 'log_archive_dest%'
/

NAME VALUE
------------------------------ --------------------------------------------------------------------------------
control_files /opt/oracle/oradata/db/control01.ctl, /opt/oracle/oradata/db/control02.ctl
, /opt/oracle/oradata/db/control03.ctl

db_file_name_convert Not Set
log_file_name_convert Not Set
log_archive_config Not Set
log_archive_dest Not Set
log_archive_dest_1 LOCATION=/opt/oracle/admin/db/arch/ MANDATORY
log_archive_dest_2 service=prod lgwr net_timeout=30
log_archive_dest_3 Not Set
log_archive_dest_4 Not Set
log_archive_dest_5 Not Set
log_archive_dest_6 Not Set
log_archive_dest_7 Not Set
log_archive_dest_8 Not Set
log_archive_dest_9 Not Set
log_archive_dest_10 Not Set
log_archive_dest_state_1 enable
log_archive_dest_state_2 enable
log_archive_dest_state_3 enable
log_archive_dest_state_4 enable
log_archive_dest_state_5 enable
log_archive_dest_state_6 enable
log_archive_dest_state_7 enable
log_archive_dest_state_8 enable
log_archive_dest_state_9 enable
log_archive_dest_state_10 enable
log_archive_format log%d_%t_%s.arc
fal_client STANDBY
fal_server Not Set
standby_file_management MANUAL
remote_login_passwordfile EXCLUSIVE
service_names db
db_name db
db_unique_name db
dg_broker_start FALSE

34 rows selected.

Tuesday, 25 October 2011

Oracle PGA report

Oracle PGA REPORT ...

pga report

PGA Diagnostics

from web http://oracle-cookies.blogspot.com/2007/08/pga-diagnostics.html
-- PGA Report
-- Displays various statistics regarding the PGA usage

set linesize 300 pagesize 400 heading off  feedback off

-- general statistics
select 'SECTION 1: GENERAL STATISTICS FOR THE PGA' from dual;
select '==================================================================' || '=========================' from dual;

set heading on
select name,
       decode(unit, 'bytes', round(value / 1024 / 1024, 2), value) value,
       decode(unit, 'bytes', 'MB', '') unit
  from v$pgastat;

set heading off
select 'Check the following:' from dual;
select '  - "aggregate PGA auto target" should not be too small in comparison '  || 'with the "aggregate PGA target parameter"' from dual;
select '  - "global memory bound" should not be lower than 1M' from dual;
select '  - "over allocation count" should be near 0 or should not increasing in time' from dual;
select '  - the bigger "cache hit percentage", the better' from dual;
select '' from dual;

set heading on
SELECT name profile, cnt, decode(total, 0, 0, round(cnt*100/total)) percentage
    FROM (SELECT name, value cnt, (sum(value) over ()) total   FROM V$SYSSTAT
    WHERE name like 'workarea exec%');
set heading off

-- PGA used by all DB processes
select 'SECTION 2: PGA USED BY CURRENT DB PROCESSES (IN MB), ORDER BY PGA_ALLOC_MEM' from dual;
select '==================================================================' || '=========================' from dual;
set heading on

break on report;
compute sum label '--> TOTAL' of pga_used_mem on report;
compute sum label '--> TOTAL' of pga_alloc_mem on report;
compute sum label '--> TOTAL' of pga_freeable_mem on report;
compute sum label '--> TOTAL' of pga_max_mem on report;

SELECT PROGRAM,
       round(PGA_USED_MEM / 1024 / 1024, 2)  pga_used_mem,
       round(PGA_ALLOC_MEM / 1024 / 1024, 2)  pga_alloc_mem,
       round(PGA_FREEABLE_MEM / 1024 / 1024, 2) pga_freeable_mem,
       round(PGA_MAX_MEM / 1024 / 1024, 2)  pga_max_mem
  FROM V$PROCESS
 order by pga_alloc_mem desc;


set heading off
select 'The columns have the following meaning:' from dual;
select '  - PGA_USED_MEM = PGA memory currently used by the process' from dual;
select '  - PGA_ALLOC_MEM = PGA memory currently allocated by the process (including free '   || 'PGA memory not yet released to the operating system by the server process)' from dual;
select '  - PGA_FREEABLE_MEM = Allocated PGA memory which can be freed' from dual;
select '  - PGA_MAX_MEM = Maximum PGA memory ever allocated by the process' from dual;
select '' from dual;

set feedback off;
select 'SECTION 3: USED PGA MEMORY BY CATHEGORIES (VALUES IN MB ORDER DESC BY ALLOCATED_MB).' from dual;
select '=================================================================='  || '=========================' from dual;
set heading on
select category,
       round(sum(allocated) / 1024 / 1024, 2) allocated_mb,
       round(sum(used) / 1024 / 1024, 2) used_mb,
       round(sum(max_allocated) / 1024 / 1024, 2) max_allocated_mb
  from v$process_memory
 group by category
 order by 2 desc;

set heading off
select '' from dual;

-- workareas histogram
select 'SECTION 4: SQL WORKAREAS HISTOGRAM' from dual;
select '==================================================================' || '=========================' from dual;
set heading on
SELECT LOW_OPTIMAL_SIZE/1024 low_kb,
       (HIGH_OPTIMAL_SIZE+1)/1024 high_kb,
       OPTIMAL_EXECUTIONS, ONEPASS_EXECUTIONS, MULTIPASSES_EXECUTIONS
  FROM V$SQL_WORKAREA_HISTOGRAM
 WHERE TOTAL_EXECUTIONS != 0;

set heading off
select '' from dual;

-- active workareas
select 'SECTION 5: CURRENTLY ACTIVE WORKAREAS' from dual;
select '==================================================================' || '=========================' from dual;
set heading on  feedback on
col OPERATION for a20
SELECT to_number(decode(SID, 65535, NULL, SID)) sid,
       operation_type OPERATION,
       trunc(EXPECTED_SIZE/1024) ESIZE,
       trunc(ACTUAL_MEM_USED/1024) MEM,
       trunc(MAX_MEM_USED/1024) "MAX MEM",
       NUMBER_PASSES PASS,
       trunc(TEMPSEG_SIZE/1024/1024) TSIZE_MB
  FROM V$SQL_WORKAREA_ACTIVE
 ORDER BY 1,2;

set heading off feedback off
select 'The meaning of the above columns is:' from dual;
select '  - SID = the active session identifier' from dual;
select '  - OPERATION = the type of the operation' from dual;
select '  - ESIZE = the expected size for the sql workarea' from dual;
select '  - MEM = Amount of PGA memory (in KB) currently allocated on behalf of this work area.' from dual;
select '  - MAX MEM = Maximum memory amount (in KB) used by this work area' from dual;
select '  - PASS = Number of passes corresponding to this work area (0 if running in OPTIMAL mode)' from dual;
select '  - TSIZE_MB = Size (in megabytes) of the temporary segment used on behalf of this work area. '  || 'This column is NULL if this work area has not (yet) spilled to disk.' from dual;
select '' from dual;

-- top 10 sql with gurmand sql areas
select 'SECTION 6: OP 10 WORK AREAS REQUIRING MOST CACHE MEMORY' from dual;
select '==================================================================' || '=========================' from dual;
set heading on linesize 200
col sql_text        for a50
col operation_type  for a20
SELECT *
  FROM (SELECT distinct s.sql_id,
                        operation_type,
                        estimated_optimal_size,
                        max_tempseg_size,
                        substr(s.SQL_TEXT, 1, 50) sql_text
          FROM V$SQL_WORKAREA a, V$SQLSTATS s
         WHERE a.SQL_ID = s.SQL_ID
         ORDER BY estimated_optimal_size)
 WHERE ROWNUM <= 10;

set heading off pagesize 300
select 'SECTION 7: SQLs WITH WORK AREAS THAT HAVE BEEN EXECUTED IN ONE OR EVEN MULTIPLE PASSES' from dual;
select '==================================================================' || '=========================' from dual;
set heading on  feedback on
col SQL_TEXT for a100
SELECT s.sql_id,sql_text, sum(ONEPASS_EXECUTIONS) onepass_cnt,  sum(MULTIPASSES_EXECUTIONS) mpass_cnt FROM V$SQL s, V$SQL_WORKAREA wa
WHERE s.address = wa.address
GROUP BY s.sql_id,sql_text
HAVING sum(ONEPASS_EXECUTIONS+MULTIPASSES_EXECUTIONS)>0
order by 3;

set feedback off heading off

select 'SECTION 8: PGA TARGET ADVCE' from dual;
select '==================================================================' || '=========================' from dual;
show parameter pga_aggregate_target
set heading on
SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
       ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,
       ESTD_OVERALLOC_COUNT
  FROM V$PGA_TARGET_ADVICE;

prompt PGA_TARGET_ADVICE
set linesize 300 pagesize 300
col PGA_TARGET_FOR_ESTIMATE   for 999,999,999,990  heading 'PGA_TARGET'
col PGA_TARGET_FACTOR    for 99.0   heading 'Factor'
col BYTES_PROCESSED    for 999,999,999,999,990 heading 'Bytes Processed'
col ESTD_EXTRA_BYTES_RW   for 999,999,999,999,990 heading 'Est. Bytes|r/w'
col ESTD_PGA_CACHE_HIT_PERCENTAGE  for 9,990   heading 'Est. PGA|hit rate'
col ESTD_OVERALLOC_COUNT   for 999,990   heading 'Est.|overalloc'
select PGA_TARGET_FOR_ESTIMATE
, PGA_TARGET_FACTOR
, BYTES_PROCESSED
, ESTD_EXTRA_BYTES_RW
, ESTD_PGA_CACHE_HIT_PERCENTAGE 
, ESTD_OVERALLOC_COUNT
from v$pga_target_advice
order by PGA_TARGET_FACTOR
/



set linesize 300 pagesize 300
col "O/1/M" format a10
col name format a20
SELECT operation, options, object_name name, trunc(bytes/1024/1024) "input(MB)",trunc(last_memory_used/1024) last_mem,
trunc(estimated_optimal_size/1024) optimal_mem, trunc(estimated_onepass_size/1024) onepass_mem, 
decode(optimal_executions, null, null, optimal_executions||'/'||onepass_executions||'/'||multipasses_executions) "O/1/M"
FROM V$SQL_PLAN p, V$SQL_WORKAREA w 
WHERE p.address=w.address(+) 
AND p.hash_value=w.hash_value(+) 
AND p.id=w.operation_id(+) 
and p.sql_id='&sql_id'
/

COL mem_component HEAD COMPONENT FOR A35

SELECT

component mem_component

, ROUND(current_size/1048576) cur_mb

, ROUND(min_size/1048576) min_mb

, ROUND(max_size/1048576) max_mb

, ROUND(user_specified_size/1048576) spec_mb

, oper_count

, last_oper_type last_optype

, last_oper_mode last_opmode

, last_oper_time last_optime

, granule_size/1048576 gran_mb

FROM

v$sga_dynamic_components

set lines 200 pages 100

col parameter format a50

col description format a80 word_wrapped

col "session value" format a10

col "instance value" format a10

select a.ksppinm "parameter",

a.ksppdesc "description",

b.ksppstvl "session value",

c.ksppstvl "instance value"

from x$ksppi a,x$ksppcv b,x$ksppsv c

where a.indx = b.indx

and a.indx = c.indx

and a.ksppinm like '/_pga%' escape '/'

order by a.ksppinm;

parameter description session va instance v

-------------------------------------------------- -------------------------------------------------------------------------------- ---------- ----------

_pga_aggregate_xmem_limit limit of aggregate PGA XMEM memory consumed by the instance 0 0

_pga_auto_snapshot_percentage percent growth of PGA memory for additional snapshots 20 20

_pga_auto_snapshot_threshold bytes of PGA memory in one process to trigger detail snapshot 524288000 524288000

_pga_in_sga_param1 pga in sga param1

_pga_in_sga_param2 pga in sga param2

_pga_in_sga_param3 pga in sga param4

_pga_in_sga_param4 pga in sga param4

_pga_in_sga_param5 pga in sga param4

_pga_large_extent_size PGA large extent size 1048576 1048576

_pga_limit_check_wait_time microseconds to wait for over limit confirmation 1000000 1000000

_pga_limit_dump_summary dump PGA summary when signalling ORA-4036 TRUE TRUE

_pga_limit_interrupt_smaller whether to interrupt smaller eligible processes FALSE FALSE

_pga_limit_min_req_size bytes of PGA usage below which process will not get ORA-4036 4194304 4194304

_pga_limit_per_process_minimum pga_aggregate_limit per-process minimum 3145728 3145728

_pga_limit_physmem_perc default percent of physical memory for pga_aggregate_limit and SGA 90 90

_pga_limit_simulated_physmem_size bytes of physical memory to determine pga_aggregate_limit with 0 0

_pga_limit_target_perc default percent of pga_aggregate_target for pga_aggregate_limit 200 200

_pga_limit_time_to_interrupt seconds to wait until direct interrupt 2 2

_pga_limit_time_until_idle seconds to wait before treating process as idle 15 15

_pga_limit_time_until_killed seconds to wait before killing session over limit 30 30

_pga_limit_tracing trace pga_aggregate_limit activity 0 0

_pga_limit_use_immediate_kill use immediate kill for sessions over limit TRUE TRUE

_pga_limit_watch_perc percentage of limit to have processes watch 50 50

_pga_limit_watch_size bytes of PGA usage at which process will begin watching limit 104857600 104857600

_pga_max_size Maximum size of the PGA memory for one process 209715200 209715200

Oracle function TRANSLATE

function TRANSLATE

select TRANSLATE('12123 3434 4535 5656','0123456789 ','0123456789') from dual
^
|
TRANSLATE('121233
-----------------
12123343445355656

in this case we have not given space translate information so it will trim space

select replace('I am vihaan','vihaan','Anuj') result from dual;

RESULT
---------
I am Anuj

Oracle Object being Access

Oracle Object being access
identify objects being accessed by sessions.
object access
Oracle access

set feed off
set pagesize 10000
set wrap off
set linesize 200;
set heading on
set tab on
set scan on
set verify off
col "Object Being Accessed" format a25
col "sid-serial" format a15
col "User" format a12
col "Owner" like "User"
col sql_text format a40
--
ttitle left 'Who uses what objects' -
skip 2

select a.sid||','||serial# "sid-serial",substr(s.username,1,10) "User",substr(a.owner,1,10) "Owner",
substr(a.object,1,30) "Object Being Accessed",s.sql_id,substr(sql_text,1,40) sql_text
from V$ACCESS a, V$SESSION s ,v$sql sq
where a.sid = s.sid
and a.owner !='SYS'
and sq.sql_id (+) = s.sql_id;

sid-serial User Owner Object Being Accessed SQL_ID SQL_TEXT
--------------- ------------ ------------ ------------------------- ------------- ----------------------------------------
13,1 PUBLIC DBA_INDEXES
41,9032 SYS PUBLIC V$PARAMETER 2h6wst2nza2wv select a.sid||','||serial# "sid-serial",
41,9032 SYS GGATE GGS_SETUP 2h6wst2nza2wv select a.sid||','||serial# "sid-serial",
13,1 PUBLIC DBA_TAB_PARTITIONS
13,1 GGATE GGS_TEMP_COLS
13,1 PUBLIC DBMS_UTILITY
13,1 PUBLIC DBMS_SESSION
13,1 GGATE GGS_SETUP
41,9032 SYS PUBLIC DBA_IND_COLUMNS 2h6wst2nza2wv select a.sid||','||serial# "sid-serial",
41,9032 SYS CTXSYS DRIERR 2h6wst2nza2wv select a.sid||','||serial# "sid-serial",
41,9032 SYS CTXSYS DR$OBJECT_ATTRIBUTE 2h6wst2nza2wv select a.sid||','||serial# "sid-serial",

Anuj Singh Oracle DBA

Search This Blog

Total Pageviews