Check out

• http://dboptimizer.com/2011/01/12/best-oracle-peformance-tools/

for a list of industry tools some free some pay for.

Tracefile analyzer tools: http://ba6.us/node/177

My notes on how to trace: https://sites.google.com/site/oraclemonitor/tracing

Tom Kyte’s Show Space

Notes for JDBC connections over firewalls

ORA-600 decoder ring

Wait Events

• http://docs.oracle.com/cd/E11882_01/server.112/e17110/waitevents003.htm
• https://sites.google.com/site/embtdbo/wait-event-documentation
• http://www.saptechies.com/oracle-wait-events/
• http://venzi.wordpress.com/2009/05/08/oracle-10g-enqueue-waits/
• http://www.saptechies.com/faq-oracle-enqueues/

ASH Queries

My favorite ASH query is aveactn.sql which shows the load on the database graphically like the performance page in OEM but the output is in ASCII. Here is a blog entry on an improved version

• http://dboptimizer.com/2011/02/09/aas-on-awr/

ASH queries at  Github

• http://ashmasters.com/ash-queries/

SQL Execution times from ASH

1. http://dboptimizer.com/2011/05/04/sql-execution-times-from-ash/
2. http://dboptimizer.com/2011/05/06/sql-timings-for-ash-ii/
3. http://dboptimizer.com/2011/05/06/sql-ash-timings-iii/

OEM Performance page in ascii art

• http://oracleprof.blogspot.ie/2012/07/average-active-session-in-sqlplus-with.html

 MOATS.sql has some ASH collection as well

• overview: http://blog.tanelpoder.com/2011/03/29/moats-the-mother-of-all-tuning-scripts/
• download  http://www.oracle-developer.net/content/utilities/moats.zip?p=1.06

S-ASH

S-ASH is a plsql package that collects v$active_session_history on Oracle 7-11 and versions such as Standard Edition and best of all it runs without an additional Oracle licenses.

ASHMON

Active Session History can be difficult to browse and mine for data,  but with graphics it becomes easy. ASHMON is a graphical program that reads ASH data (or SASH data) and visualizes it. ASHMON also reads data from v$sysstat and v$system_event and visualizes it. ASHMON is  written by me and only reads ASH from the databsase but can use S-ASH. For tools that both collect and read ASH check out  ASHVIEWER and LAB128. LAB128 is pay for but does more than just collect ASH and ASHViewer is free but only collects ASH and or views already collect ASH data.

AWR diff’er

I often have to compare to AWR reports and it can be time consuming, thus I wrote a script that will compare the data called udf.sh (the u stands for u in utlbstat/utlestat !)

One problem though is AWR reports in HTML. My scripts are for TEXT. In the case of HTML I use http://www.nirsoft.net/utils/htmlastext.html to convert them to TEXT.

SGA Direct Memory Attach

I haven’t worked on SGA access stuff in several years but it’s one of those subjects that seems to fascinate people. For a good introduction, read Oracle Insights, Tales from the Oaktable. I wrote chapter 6 on how I got started working on SGA attach and how to get started yourself.

• SGA Access
  • sgaid.sh
  • xksuse.sql
  • xksuse.c

statspack shell script

Before AWR came along, I disliked useing statspack and creating tables and inserting data into my monitored database, so I wrote a script that collected the data I want into flatfiles. That script is called “collect.sh“

These scripts were originally hosted at http://oraperf.sourceforge.net/seminar/collect.html

I dug up this script recently to do some monitoring and it’s a work in progress: https://sites.google.com/site/oraclemonitor/collect-sh-system

Mon.sh

The script mon.sh is a shell script to provide a screen view of Oracle statistics like “top.” I last touch this code about12 years ago, but it still seems like a good idea.

ASH Report

@?/rdbms/admin/ashrpt

or

select output
from table(
   dbms_workload_repository.ash_report_text(
        (select dbid from v$database),
        1, -- instance id
        sysdate - 2/24, -- start date
        sysdate - 1/24  -- end date
        )) ;

AWR Report

List of AWR views

There are two ways to get AWR reports. The easiest is to connect with sqlplus as sys or system and run ?/rdbms/admin/awrrpt.sql :

$ sqlplus / as sysdba
sqlplus> @?/rdbms/admin/awrrpt.sql

The script will prompt you for input. Choose “TEXT” report – it’s easier to parse with udf.sh. (Clive Bostock  also has a AWR to CSV converter based on text format). If you won’t be parsing AWR reports then the HTML version is easier to read and navigate.  The awrrpt.sql will also prompt for the time period to cover. The time period could be a full day or if there are particular hours that have typical workload, then an hour report would be good. The constraint here is that the script “?/rdbms/admin/awrrpt.sql” is on the database machine. If you are not on the database machine and are connecting with SQL*Plus over SQL*Net, then you can run

SELECT * FROM TABLE(
dbms_workload_repository.awr_report_text(
    (select dbid from v$database),
    1, -- instance id
    124, -- begin id
    125 -- end id ));

The trick here is that you have to know the snapshot ids, which in this example are 124 and 125. To find out snapshot ids you can query

col begin_interval_time for A30
select snap_id, Begin_interval_time from DBA_HIST_SNAPSHOT
order by begin_interval_time ;

   SNAP_ID BEGIN_INTERVAL_TIME
---------- ----------------------------
       538 02-MAR-11 03.00.35.219 PM
       539 02-MAR-11 04.00.46.572 PM
       540 02-MAR-11 05.00.57.371 PM

BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
END;
/

Check out the AWR formatter plugin for Chrome: http://tylermuth.wordpress.com/2011/04/20/awr-formatter/

AWR Diff’er

Set long 100000
SELECT * FROM TABLE(
    dbms_workload_repository.awr_diff_report_text(
        (select dbid from v$database),
         1,  -- instance id
         120,
         121,
        (select dbid from v$database),
         1 , -- instance id
         122,
         123)
);

ADDM Report Manually

 Set long 100000
select dbms_advisor.get_task_report(task_name)
from   dba_advisor_tasks
where task_id = (
                  select max(t.task_id)
                  from   dba_advisor_tasks t,
                         dba_advisor_log l
                  where t.task_id = l.task_id and
                        l.status = 'COMPLETED' and
                        l.execution_end < sysdate - 2/24 and -- time period
                        t.advisor_name = 'ADDM');

Autotrace

     @?/rdbms/admin/utlxplan.sql
     -- run as system creates plustrace
     @?/sqlplus/admin/plustrce.sql
     grant plustrace to [user]

     set autotrace off
     set autotrace on   -- explain and statistics

     set autotrace traceonly -- runs query, shows explain and stats but doesn't print query results 

     set autotrace on explain
     set autotrace on statistics

     set autotrace traceonly explain -- doesn't run the query
     set autotrace traceonly statistics -- runs query but no results shown

Tracing

oradebug setmypid
oradebug event 10046 trace name context forever, level 12;
oradebug unlimit
oradebug tracefile_name

alter session set events '10046 trace name context forever, level 12';
alter session set max_dump_file_size = unlimited;

-- to get session id from from current session
SELECT sys_context('USERENV', 'SID') FROM DUAL;

NOTE: oradebug uses ospid (setospid) or pid (setorapid) which come from v$process

More on 10046 http://www.dicka.eclipse.co.uk/oracle_trace_event_10046_notes.htm

select
          p.pid pid,   -- oradebug setorapid
          p.spid ospid -- oradebug setospid
from v$process p, v$session s
where s.sid = &1
and p.addr = s.paddr
/

Rather than using oradebug, you can use DBMS_MONITOR

dbms_monitor.session_trace_enable(session_id => 127,
serial_num => 29,
waits => TRUE,
binds => FALSE)

select sid, serial#,sql_trace,sql_trace_waits, sql_trace_binds from v$session;

DBMS_MONITOR can use client_id, but client_id has to be turned on and TRCSESS has to be use extract trace information from multiple trace files

dbms_session.set_identifier(client_id)
dbms_monitor.client_id_trace_enable (client_id, TRUE, FALSE)
dbms_monitor.client_id_stat_enable(client_id)
Trcsess output=<name> client_id=<name>

On logon trigger can be use to trace a session from the beginning

create or replace trigger sys.set_trace
after logon on database
when (user = 'trace_user')
begin
execute immediate 'alter session set statistics_level=all';
execute immediate 'alter session set max_dump_file_size=unlimited';
execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
end set_trace;
/

Here is a process to trace the Oracle batch from a second process so the trace can be started and stopped without interfering with the batch,

In the batch session, run

      SELECT sys_context('USERENV', 'SID') FROM DUAL;

to get the sessions SID. After that the batch can be kicked off.
In a second session as sysdba, run

select
          p.spid ospid -- oradebug setospid
from v$process p, v$session s
where s.sid = &1
and p.addr = s.paddr
/

inputting the sessions SID.
Now, with this return value of OSPID, tracing can be set on and off from another process:

oradebug setospid   [ospid]
oradebug unlimit
oradebug event 10046 trace name context forever, level 12;
-- let batch run for a while
oradebug event 10046 trace name context off;
oradebug tracefile_name

after tracing try 10046.pl to parse the file

Flushing buffer cache

 alter session set events 'immediate trace name flush_cache’;
 alter system flush buffer_cache;

awrsqrpt.sql

needs level 6 snapshot

statspack

@?/rdbms/admin/spcreate.sql  -- Install, Connect as SYSDBA
Exec statspack.snap; -- take snapshots before, after, and during load
@?/rdbms/admin/spreport.sql -- Generate Reports

Average IO sizes from ASH

col event for a25
select event,round(min(p3)) mn,
round(avg(p3)) av,
round(max(p3)) mx,
count(*)  cnt
-- from dba_hist_active_sess_history
from v$active_session_history
where  (event like 'db file%' or event like 'direct %')
and event not like '%parallel%'
group by event
order by event

NOTE: because ASH samples, these I/O sizes are skewed towards the larger end but that is not so bad as often I want to know what the largest read I/O sizes are. The skew is much more of a problem for timings, but for IO sizes this can be good information. The above query on I/O sizes is useful. The below query on I/O times is of questionable use (though I include it here as I have used it)

col event for a25
col event for a25
select event,round(min(time_waited)) mn,
round(avg(time_waited)) av,
round(max(time_waited)) mx,
count(*)  cnt
-- from dba_hist_active_sess_history
from v$active_session_history
where  (event like 'db file%' or event like 'direct %')
and event not like '%parallel%'
group by event
order by event

execution plan from display_cursor

use /* + gather_plan_statistics */  hint go gather statistics and use ALLSTATS as last parameter

select * from table(dbms_xplan.display_cursor(sql_id,child_number,options));
select * from table(dbms_xplan.display_cursor('0zh4k4hzzby20'));
select * from table(dbms_xplan.display_cursor('0zh4k4hzzby20',1));
select * from table(dbms_xplan.display_cursor('0zh4k4hzzby20',null,'ADVANCED'));

execution plan from AWR

select * from table(dbms_xplan.display_awr(sql_id, null,null,'ADVANCED'));

Bind Variables

extracting bind variables is something I’ve meant to look into but not spent much time. See http://jonathanlewis.wordpress.com/2008/07/24/bind-capture/

variable x varchar2(10);
exec :x:='X';
col f_sqlid new_value v_sqlid
col f_sqlcn new_value v_sqlcn
col f_binddata new_value v_binddata
select * from dual where dummy=:x;
select
PREV_SQL_ID f_sqlid,
PREV_CHILD_NUMBER f_sqlcn
from v$session
where sid = ( Select sid from v$mystat where rownum=1 ) ;
select bind_data f_binddata from v$sql
where sql_id = '&v_sqlid'
and child_number = &v_sqlcn
/
SELECT * FROM TABLE ( DBMS_SQLTUNE.EXTRACT_BINDS ( '&v_binddata' )) ;
SELECT xmltype(other_xml) AS xmlval
FROM v$sql_plan
WHERE
sql_id = '&v_sqlid' AND
child_number = &v_sqlcn AND
other_xml IS NOT NULL
/

Tracing a SQL statement – 11g
see:

• http://oraclue.com/2009/03/24/oracle-event-sql_trace-in-11g/
• http://blog.tanelpoder.com/2010/06/23/the-full-power-of-oracles-diagnostic-events-part-2-oradebug-doc-and-11g-improvements/
• http://tech.e2sn.com/oracle/troubleshooting/oradebug-doc

 alter session set events 'sql_trace [sql_id]';
--or
alter session set events 'sql_trace [sql_id|sql_id]';
--example
alter session set events 'sql_trace [707wu2umpfas7],wait=true,bind=true';
-- run query
alter session set events 'sql_trace [707wu2umpfas7]'off;

Rollback of a killed process

see http://www.oracle-base.com/dba/Script.php?category=monitoring&file=session_undo.sql

watch used_urec

-- -----------------------------------------------------------------------------------
-- File Name    : http://www.oracle-base.com/dba/monitoring/session_undo.sql
-- Author       : DR Timothy S Hall
-- Description  : Displays undo information on relevant database sessions.
-- Requirements : Access to the V$ views.
-- Call Syntax  : @session_undo
-- Last Modified: 29/03/2005
-- -----------------------------------------------------------------------------------
SET LINESIZE 200

COLUMN username FORMAT A15

SELECT s.username,
       s.sid,
       s.serial#,
       t.used_ublk,
       t.used_urec,
       rs.segment_name,
       r.rssize,
       r.status
FROM   v$transaction t,
       v$session s,
       v$rollstat r,
       dba_rollback_segs rs
WHERE  s.saddr = t.ses_addr
AND    t.xidusn = r.usn
AND    rs.segment_id = t.xidusn
ORDER BY t.used_ublk DESC;

Pfile or SPfile

from: http://www.orafaq.com/node/5

SELECT DECODE(value, NULL, ‘PFILE’, ‘SPFILE’) “Init File Type” FROM sys.v_$parameter WHERE name = ‘spfile‘;

Connect strings other than via tnsnames.ora

see: ezconnect

CONNECT username/password@[//]host[:port][/service_name]

or long form

sqlplus dbUser/dbPassword@'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=remoteServer)(PORT=1521)))(CONNECT_DATA=(SID=dbSid)))’

Tracking specific SQL in AWR (coloring SQL)

exec dbms_workload_repository.add_colored_sql( sql_id );

see http://oracleprof.blogspot.com/2011/06/how-to-color-mark-sql-for-awr-snapshots.html

Generating Dummy data

idea from Dion Cho

create table cacher(c1 char(2000), c2 char(2000), c3 char(2000)) ;
insert into redo_gen
select ‘x’, ‘x’, ‘x’
from dual
connect by level <= 10000;

Clearing Archives

WARNING:  this is for clearning logs on test databases that will never need recovery:

rman target /

allocate channel for maintenance type disk;
delete force noprompt copy of archivelog all completed before ‘sysdate‘;

SQLPLUS / as sysdba

archive log stop;
archive log start;

archive log all;

Flashback query vs Total recal

• flashback – uses what ever UNDO you have available, flashback limited to 5 days via Tom Kyte – the farther back in time, the slower the query
• total recall – saves changes for specified tables, issues with total recall such as performance, space and prohibited operations, total recall lab, flashback examples, PPT presentation, PDF presentation, trivadis PDF review and limits
• 12c Temporal, examples on 12c , Tom Kyte on 12c
• also see workspace manager

Creating Database link old style

create database link  "SASHREPO"
CONNECT TO "SASH"
IDENTIFIED BY "SASH"
USING
'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=REPO_HOST)(PORT=1521)))(CONNECT_DATA=(SID=REPO_SID)))'
/

Documentation on v$ and DBA views:

• http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/index.htm
• https://sites.google.com/site/oraclemonitor DBA_HIST views

 Shell Vars

Finally a reference for those shell variables that are a problem to search for in google $!, $*.$@ etc

XMLDB :

http://www.idevelopment.info/data/Oracle/DBA_tips/xmldb/XMLDB_2.shtml

    dispatchers='(PROTOCOL=TCP) (SERVICE=<ORACLE_SID>XDB)'

Jobs

http://it.toolbox.com/blogs/database-solutions/killing-the-oracle-dbms_job-6498