SQL Execution Plan

Optimizer creates the execution plan as per statistics available on database objects

Different types of internal joins

• nested loop joins (Both Small tables & Optimizer mode: First Rows)
• hash joins (Big Tables, Join type: Equality, Optimizer mode: All Rows)
• sort merge join (Big Tables, Join type: Inequality, Optimizer mode: All Rows)
• star transformation joins

The statements with the maximum level will be first executed and returned for the next level statement to work on it.

DBMS_STATS for CBO - replaced ANALYZE

Nice article explaining the transition to DBMS_STATS for CBO from old ANALYZE
http://www.dba-oracle.com/oracle_tips_dbms_stats1.htm

DBMS_STATS.GATHER_TABLE_STATS

exec  dbms_stats.gather_table_stats( -
      ownname          => 'PERFSTAT', -
      tabname          => ’STATS$SNAPSHOT’ -
      estimate_percent => dbms_stats.auto_sample_size, -
      method_opt       => 'for all columns size skewonly', -
      cascade          => true, -
      degree           => 7 -
   )

Options: (for gather_schema_stats)
gather —Reanalyzes the whole schema
gather empty —Only analyzes tables that have no existing statistics
gather stale —Only reanalyzes tables with more than 10% modifications (inserts, updates, deletes).
gather auto —Reanalyzes objects which currently have no statistics and objects with stale statistics

method_opt:
'for all indexed columns size skewonly' - examines the distribution of values for every column within every index for histogram
'for all columns size repeat' - only reanalyze indexes with existing histograms
'for columns size auto' - used when invoked using alter table xxx monitoring; data distribution and the access manner / load on column

DBMS_STATS.GATHER_SYSTEM_STATS

execute dbms_stats.gather_system_stats('Start');
-- one hour delay during high workload
execute dbms_stats.gather_system_stats('Stop');

The statistics collected are (sys.aux_stats$)
No Workload (NW) stats:
CPUSPEEDNW - CPU speed
IOSEEKTIM - The I/O seek time in milliseconds
IOTFRSPEED - I/O transfer speed in milliseconds

Workload-related stats:
SREADTIM - Single block read time in milliseconds
MREADTIM - Multiblock read time in ms
CPUSPEED - CPU speed
MBRC - Average blocks read per multiblock read (see db_file_multiblock_read_count)
MAXTHR - Maximum I/O throughput (for OPQ only)
SLAVETHR - OPQ Factotum (slave) throughput (OPQ only)

Statistics collected during different workloads may be stored and used for influencing the optimizer plan as per load times

/* e.g. activate the DAY statistics each day at 7:00 am */ 
DECLARE 
   I NUMBER; 
BEGIN 
   DBMS_JOB.SUBMIT (I, 'DBMS_STATS.IMPORT_SYSTEM_STATS(stattab => ''mystats'', s 
tatown => ''SYSTEM'', statid => ''DAY'');', trunc(sysdate) + 1 + 7/24, 'sysdate + 1'); 
END; 
/  
 
/* e.g. activate the NIGHT statistics each day at 9:00 pm */ 
DECLARE 
   I NUMBER; 
BEGIN 
   DBMS_JOB.SUBMIT (I, 'DBMS_STATS.IMPORT_SYSTEM_STATS(stattab => ''mystats'', s 
tatown => ''SYSTEM'', statid => ''NIGHT'');', trunc(sysdate) + 1 + 21/24, 'sysdate + 1'); 
END; 
/  
*** ******************************************************** 
*** Initialize the OLTP System Statistics for the CBO 
*** ******************************************************** 
 
1. Delete any existing system statistics from dictionary: 
    
   SQL> execute DBMS_STATS.DELETE_SYSTEM_STATS; 
   PL/SQL procedure successfully completed. 
 
2. Transfer the OLTP statistics from OLTP_STATS table to the dictionary tables: 
 
   SQL> execute DBMS_STATS.IMPORT_SYSTEM_STATS(stattab => 'OLTP_stats', statid => 'OLTP', statown => 'SYS');
   PL/SQL procedure successfully completed. 
 
3. All system statistics are now visible in the data dictionary table: 
 
   SQL> select * from sys.aux_stats$; 
   SNAME                PNAME                   PVAL1 PVAL2 
   -------------------- ------------------ ---------- --------------
   SYSSTATS_INFO        STATUS                        COMPLETED 
   SYSSTATS_INFO        DSTART                        08-09-2001 16:40 
   SYSSTATS_INFO        DSTOP                         08-09-2001 16:42 
   SYSSTATS_INFO        FLAGS                       0 
   SYSSTATS_MAIN        SREADTIM                7.581 
   SYSSTATS_MAIN        MREADTIM               56.842 
   SYSSTATS_MAIN        CPUSPEED                  117 
   SYSSTATS_MAIN        MBRC                        9 
 
   where  
   => sreadtim : wait time to read single block, in milliseconds 
   => mreadtim : wait time to read a multiblock, in milliseconds 
   => cpuspeed : cycles per second, in millions

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

SQL> exec runStats_pkg.rs_start;

PL/SQL procedure successfully completed.

SQL> select * from t1 where owner='PUBLIC';

OWNER                          OBJECT_NAME
------------------------------ ------------------------------
PUBLIC                         DUAL
PUBLIC                         SYSTEM_PRIVILEGE_MAP
PUBLIC                         TABLE_PRIVILEGE_MAP
PUBLIC                         STMT_AUDIT_OPTION_MAP
PUBLIC                         MAP_OBJECT
PUBLIC                         DBMS_STANDARD
PUBLIC                         V$MAP_LIBRARY
...

2765 rows selected.

SQL> exec runStats_pkg.rs_middle;

PL/SQL procedure successfully completed.

SQL> select /*+index(t2 inx_t2) */ * from t2 where owner='PUBLIC';

OWNER                          OBJECT_NAME
------------------------------ ------------------------------
PUBLIC                         DUAL
PUBLIC                         SYSTEM_PRIVILEGE_MAP
PUBLIC                         TABLE_PRIVILEGE_MAP
PUBLIC                         STMT_AUDIT_OPTION_MAP
PUBLIC                         MAP_OBJECT
PUBLIC                         DBMS_STANDARD
PUBLIC                         V$MAP_LIBRARY
...

2765 rows selected.

SQL> exec runStats_pkg.rs_stop;
Run1 ran in 304 hsecs
Run2 ran in 331 hsecs
run 1 ran in 91,84% of the time

Name                                  Run1        Run2        Diff
STAT...table scans (short tabl           1           0          -1
LATCH.resmgr:schema config               0           1           1
LATCH.job_queue_processes para           0           1           1
LATCH.undo global data                   7           6          -1
LATCH.resmgr:actses active lis           0           1           1
STAT...shared hash latch upgra           0           1           1
STAT...session cursor cache co           1           0          -1
STAT...heap block compress               6           7           1
STAT...index scans kdiixs1               0           1           1
LATCH.In memory undo latch               0           2           2
LATCH.library cache lock                 4           6           2
LATCH.redo allocation                   13          16           3
STAT...CPU used by this sessio          15          12          -3
STAT...calls to get snapshot s           5           2          -3
STAT...active txn count during           4           8           4
STAT...cleanout - number of kt           4           8           4
STAT...redo entries                      9          13           4
STAT...calls to kcmgcs                   4           8           4
LATCH.messages                          18          22           4
STAT...consistent gets - exami           4           9           5
LATCH.library cache pin                 38          44           6
STAT...recursive cpu usage              13           7          -6
LATCH.library cache                     48          55           7
LATCH.active service list                2          10           8
STAT...db block gets                    17          29          12
STAT...db block gets from cach          17          29          12
STAT...consistent changes               17          29          12
STAT...db block changes                 26          42          16
STAT...bytes received via SQL*       3,188       3,209          21
STAT...DB time                          36          15         -21
STAT...CPU used when call star          34          12         -22
STAT...Elapsed Time                    311         335          24
LATCH.simulator hash latch              11          44          33
LATCH.simulator lru latch               11          44          33
LATCH.JS queue state obj latch           0          36          36
LATCH.enqueues                           2          78          76
LATCH.enqueue hash chains                2          79          77
STAT...no work - consistent re         206         397         191
STAT...consistent gets                 218         415         197
STAT...consistent gets from ca         218         415         197
STAT...table scan blocks gotte         206           0        -206
STAT...session logical reads           235         444         209
STAT...undo change vector size       2,132       2,420         288
LATCH.cache buffers chains             550         933         383
STAT...buffer is not pinned co           0         576         576
STAT...redo size                     2,780       3,400         620
STAT...table fetch by rowid              0       2,765       2,765
STAT...buffer is pinned count            0       5,139       5,139
STAT...table scan rows gotten       51,831           0     -51,831

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
1,290       1,962         672     65.75%

PL/SQL procedure successfully completed.

ASH - Active Session History

Unlike AWR, the collection of session performance statistics is in-memory every second.
Can be queried from V$ACTIVE_SESSION_HISTORY view

MMON flushes the ASH buffer to AWR DBA_HIST_ACTIVE_SESS_HISTORY

To find out how many sessions waited for some event,

select session_id||','||session_serial# SID, n.name, wait_time, time_waited 
from v$active_session_history a, v$event_name n 
where n.event# = a.event#

• CURRENT_OBJ#, can then be joined with DBA_OBJECTS to get the segments in question
• SQL_ID, can be joined with V$SQL to get SQL Statement that caused the wait
• CLIENT_ID, which was set for any client application session using DBMS_SESSION.SET_IDENTIFIER may be used to identify the client.

ADDM - Automatic Data Diagnostics Monitor

ADDM’s goal is to improve the value of db_time

Prerequisite :Exec dbms_advisor.set_default_task_parameter(’ADDM’,’DBIO_EXPECTED’, 20000);(response time expected by Oracle from the disk I/O system, defaults to 10 milliseconds)

select sum(value) "DB time" from v$sess_time_model
where stat_name='DB time';

Analysis findings are ranked and catagorised into

• PROBLEM
• SYMPTOM
• INFORMATION
  

To get the analysis result manually:

Set pages 1000
Set lines 75
Select a.execution_end, b.type, b.impact, d.rank, d.type,
'Message           : 'b.message MESSAGE,
'Command To correct: 'c.command COMMAND,
'Action Message    : 'c.message ACTION_MESSAGE
From dba_advisor_tasks a, dba_advisor_findings b,
Dba_advisor_actions c, dba_advisor_recommendations d
Where a.owner=b.owner and a.task_id=b.task_id
And b.task_id=d.task_id and b.finding_id=d.finding_id
And a.task_id=c.task_id and d.rec_id=c.rec_Id
And a.task_name like 'ADDM%' and a.status='COMPLETED'
Order by b.impact, d.rank;

@addmrpt.sql - for detailed report

Another article - http://www.rampant-books.com/art_floss_addm.htm

AWR - Automatic Workload Repository reports

statistics_level parameter should be TYPICAL or ALL - for Automatic collection
BASIC - for manual snapshots
dba_hist_snapshot - for existing snapshots
dba_hist_baseline - for viewing baseline settings
awrrpt.sql - for Statspack style report (either text or html format)
awrrpti.sql - for single instance in RAC

EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

BEGIN
  DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (
    low_snap_id  => 22,
    high_snap_id => 32);
END;

BEGIN
  DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
    retention => 43200, -- Minutes (= 30 Days). Current value retained if NULL.
    interval  => 30);   -- Minutes. Current value retained if NULL.
END;

Setting baselines:
BEGIN
  DBMS_WORKLOAD_REPOSITORY.drop_baseline (
    baseline_name => 'batch baseline',
    cascade       => FALSE); -- Deletes associated snapshots if TRUE.
END;

This example creates a baseline (named 'batch baseline') between snapshots 105 and 107 for the local database:

EXECUTE DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (start_snap_id => 105,
end_snap_id => 107,
baseline_name => 'batch baseline');

Following views gets affected by AWR snapshots
v$active_session_history - Displays the active session history (ASH) sampled every second.
v$metric - Displays metric information.
v$metricname - Displays the metrics associated with each metric group.
v$metric_history - Displays historical metrics.
v$metricgroup - Displays all metrics groups.
dba_hist_active_sess_history - Displays the history contents of the active session history.
dba_hist_baseline - Displays baseline information.
dba_hist_database_instance - Displays database environment information.
dba_hist_snapshot - Displays snapshot information.
dba_hist_sql_plan - Displays SQL execution plans.
dba_hist_wr_control - Displays AWR settings.

Another interesting article on AWR, Time Model, Active Session History, Baseline, Snapshots is in http://www.rampant-books.com/art_nanda_awr.htm

Statspack - How to

SQL> CONN perfstat/perfstat
Connected.
SQL> EXEC STATSPACK.snap; 
PL/SQL procedure successfully completed.

@spauto.sql   < for taking snapshot automatically every hour
@sppurge.sql  < for deleting unwanted snaps
@spreport     < for report generation

Data Pump - Export Import

Found this spool useful as a onestop reference to actual export import done using DataPump utility

http://www.databasejournal.com/img/jsc_DataPump_Listing_1.html#List0101

X11 X-Windows Forwarding through SSH

Xming is a display server which can be used to display forwarded XWindow applications through SSH.

Assume, Unix server is having SSH service enabled. To bring up the display window of Unix in your local MS Windows PC; you need two softwares - one is a SSH client - putty and another is a X11 display server.

Download & install both from
http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html
http://www.straightrunning.com/XmingNotes/#head-121

Xming setting
Create a windows shortcut
"C:\Program Files\Xming\Xming.exe" :0 -clipboard -multiwindow
Execute the same

Putty setting
connection>SSH>X11>Check - Enable X11 forwarding > X Display location : localhost:0.0
Login to the Unix.
$ export DISPLAY=localhost:10.0
To check whether X11 forwarding is functioning
$ /usr/openwin/bin/xclock

Buffer Cache Hit Ratio

SQL> column hit_ratio format 990.99
SQL> select (1-(sum(decode(name,'physical reads',value,0)) /
     (sum(decode(name,'db block gets',value,0)) +
     sum(decode(name,'consistent gets',value,0))))) as hit_ratio
     from v$sysstat;

HIT_RATIO
---------
     0.88 

DBA - Career

Read one white paper on DBA as a career. Found concise and interesting
http://www.peasland.net/Grow%20That%20DBA%20Career.zip

Paging vs. Swapping

Paging refers to paging out individual
pages of memory. Swapping is when an entire process is swapped out.
Paging is normal. Swapping happens when the resource load is heavy
and the entire proces is written out to disk. If you're swapping
a lot, you might want to look deeper into looking at things like
adding memory.

See article

AUTOTRACE - Execution Plan for SQL statement

Note: Using Analyze is obsolete. Use dbms_stats features for CBO - Cost based Optimizer

STEPS FOR ENABLING AUTOTRACE

SQL> @utlxplan
SQL> create public synonym plan_table for plan_table
SQL> grant all on plan_table to public
exit sqlplus and cd $oracle_home/sqlplus/admin
log into sqlplus as SYS
SQL> @plustrce
SQL> grant plustrace to public

SQL> set autotrace traceonly explain

SQL> set autotrace traceonly explain

SQL> select * from np_trial.test1 where name like '%AHE%';

Execution Plan
----------------------------------------------------------
Plan hash value: 4122059633

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |  5075 | 55825 |   262   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| TEST1 |  5075 | 55825 |   262   (1)| 00:00:04 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("NAME" LIKE '%AHE%')

SQL> set autotrace OFF

WITHOUT AUTOTRACE

SQL> EXPLAIN PLAN FOR select * from np_trial.test1 where name like '%AHE%';

SQL> @?/rdbms/admin/utlxpls

   ##or - use the following manual query of plan_table##

COLUMN "SQL" FORMAT a56  
SELECT lpad(' ',2*level)||operation||''  
        ||options ||' '||object_name||
       decode(OBJECT_TYPE, '', '', 
           '('||object_type||')') "SQL",
       cost "Cost", cardinality "Num Rows"
  FROM   plan_table WHERE plan_id = (select max(plan_id) from plan_table)
CONNECT BY prior id = parent_id
  START WITH id = 0;

Wait Events Drilldown to user

find the code from Natalka Roshak's blog

SQL> select s1.username || '@' || s1.machine
    || ' ( SID=' || s1.sid || ' )  is blocking '
    || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
    from v$lock l1, v$session s1, v$lock l2, v$session s2
    where s1.sid=l1.sid and s2.sid=l2.sid
    and l1.BLOCK=1 and l2.request > 0
    and l1.id1 = l2.id1
    and l1.id2 = l2.id2 ;

SQL> Select wait_class, sum(time_waited), sum(time_waited)/sum(total_waits)
Sum_Waits
From v$system_wait_class
Group by wait_class
Order by 3 desc;

WAIT_CLASS                SUM(TIME_WAITED)  SUM_WAITS
------------------------- ---------------- ----------
Idle                             206281236 341.202664
Configuration                         3226 65.8367347
Concurrency                           1096 7.55862069
Other                                10177 7.48859455
Administrative                          11        5.5
Commit                                 834 5.27848101
User I/O                             14777 .937984004
System I/O                           23950 .243530429
Application                              7  .02991453
Network                                  1 .000126279

SQL> Select a.event, a.total_waits, a.time_waited, a.average_wait
From v$system_event a, v$event_name b, v$system_wait_class c
Where a.event_id=b.event_id
And b.wait_class#=c.wait_class#
And c.wait_class in ('Application','Concurrency')
order by average_wait desc;

EVENT                          TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
------------------------------ ----------- ----------- ------------
enq: TX - row lock contention          106       21011           30
library cache load lock                 20         186         9.31
os thread startup                      114         897         7.87
latch: library cache                     3           9         2.98
latch: shared pool                       5           4          .76
library cache pin                        1           0          .13
SQL*Net break/reset to client          162           6          .04
latch: row cache objects                 1           0          .04
enq: RO - fast object reuse             72           1          .01
cursor: mutex S                          1           0            0

Since the TX row lock contention is max
To drill down further into session level

SQL> select a.sid, a.event, a.total_waits, a.time_waited, a.average_wait
from v$session_event a, v$session b
where time_waited > 0
and a.sid=b.sid
and b.username is not NULL
and a.event='enq: TX - row lock contention';

To see whether someone is still blocking the session
SQL> Select blocking_session, sid, serial#, wait_class,
seconds_in_wait
From v$session
where blocking_session is not NULL
order by blocking_session;

To identify who the user is
SQL> select sid, serial#, username, osuser, machine from v$session
where username is not NULL;

Another script for finding the rows on which the process is hanging
http://www.remote-dba.cc/oracle_tips_hung.htm

Oracle 10g to 9i - Data migration from higher version to lower

Click on this post Title
or use the following link
http://dbataj.blogspot.com/2007/04/imp-00010.html

Also check the following article

SQL> SELECT PLATFORM_ID, PLATFORM_NAME, ENDIAN_FORMAT
2 FROM V$TRANSPORTABLE_PLATFORM;

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ------------------------------ --------------
1 Solaris[tm] OE (32-bit) Big
2 Solaris[tm] OE (64-bit) Big
3 HP-UX (64-bit) Big
4 HP-UX IA (64-bit) Big
5 HP Tru64 UNIX Little
6 AIX-Based Systems (64-bit) Big
7 Microsoft Windows NT Little
10 Linux IA (32-bit) Little
11 Linux IA (64-bit) Little

Operating System 64 bits or 32 bits

Refer to this link
http://oraforms.blogspot.com/2007/08/64-bit-or-32-bit-cpu-for-unix.html

Data Buffer Cache Advice

SQL> alter system set db_cache_advice =on;

After sufficient period: -

column c1 heading 'Cache Size (m)' format 999,999,999,999
column c2 heading
'Buffers' format 999,999,999
column c3 heading 'Estd PhysRead Factor' format
999.90
column c4 heading 'Estd Phys Reads' format 999,999,999

select
size_for_estimate c1,
buffers_for_estimate c2,
estd_physical_read_factor c3,
estd_physical_reads c4
from
v$db_cache_advice
where
name = 'DEFAULT'
and
block_size =
(SELECT value FROM V$PARAMETER
WHERE name = 'db_block_size')
and
advice_status = 'ON';

High Water Mark of a Table (HWM)

Use DBMS_SPACE package and calculate the HWM = TOTAL_BLOCKS - UNUSED_BLOCKS - 1

SELECT BLOCKS
FROM DBA_SEGMENTS
WHERE OWNER=UPPER(owner) AND SEGMENT_NAME = UPPER(table);

ANALYZE TABLE owner.table ESTIMATE STATISTICS;

SELECT EMPTY_BLOCKS
FROM DBA_TABLES
WHERE OWNER=UPPER(owner) AND TABLE_NAME = UPPER(table);

HWM = (query result 1) - (query result 2) - 1

Controlfiles - Multiplexing

When the instance is running use the following command for changing the controlfiles and then bounce. Before copying one control file to another location (for multiplexing) the database should be Shutdown

SQL> alter system set control_files='/oracle/data02/SDW/ora_control02.ctl' scope=spfile;
System altered.
SQL> startup force
ORACLE instance started.

When more than one file is mentioned, error encountered

SQL> alter system set control_files='/oracle/data02/SDW/ora_control02.ctl,/oracle/data01/SDW/ora_control01.ctl' scope=spfile;
ORA-00205: error in identifying control file, check alert log for more info

The cause was wrong statement - notice the comma outside the single quotes.

SQL> alter system set
control_files='/oracle/data02/SDW/ora_control02.ctl','/oracle/data01/SDW/ora_control01.ctl' scope=spfile;

If at all, a recreation of lost control file is required, the following details are essential.

SELECT NAME FROM V$DATAFILE
UNION ALL
SELECT MEMBER FROM V$LOGFILE
UNION ALL
SELECT NAME FROM V$CONTROLFILE;