AWK script to analyze statspack report

I have written a small awk script to help to analyze statspack report. The content of the script is as follows:

.

The content of the script is as follows:

$ cat printload.awk
{
  if ( $0 ~ /Load Profile/ ) {
     print "===>"  FILENAME
     print $0
     getline
     print $0
     getline
     print $0
     getline
     print $0
     getline
     print $0
   }
}


Here is an example of its usage:

$ awk -f printload.awk *_1114*.lst

===>sp_P1ROCCS_1114_00:00_01:00.lst
Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:           -530,710.25        -39,825,381.33
              Logical reads:              5,346.96            401,244.58
===>sp_P1ROCCS_1114_01:00_02:00.lst
Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:            661,687.44         62,668,765.37
              Logical reads:              5,337.23            505,492.05
===>sp_P1ROCCS_1114_04:00_05:00.lst
Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:           -261,353.97            -16,558.50
              Logical reads:             34,913.84              2,212.02
===>sp_P1ROCCS_1114_05:00_06:00.lst
Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:            679,480.45            635,181.85
              Logical reads:              9,648.79              9,019.74
===>sp_P1ROCCS_1114_06:00_07:00.lst
Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:           -531,582.88        -11,387,897.48
              Logical reads:              7,055.82            151,154.21
===>sp_P1ROCCS_1114_07:00_08:00.lst
Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:            632,586.85         26,531,869.35
              Logical reads:              6,800.79            285,237.83
===>sp_P1ROCCS_1114_08:00_09:00.lst
Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:           -535,177.88        -18,857,150.16
              Logical reads:              6,451.35            227,315.36
===>sp_P1ROCCS_1114_09:00_10:00.lst
Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:            657,449.22         15,364,673.58
              Logical reads:              5,490.71            128,318.66
===>sp_P1ROCCS_1114_10:00_11:00.lst
Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:            542,321.35            648,839.10
              Logical reads:             14,599.49             17,466.99
===>sp_P1ROCCS_1114_11:00_12:03.lst
Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:            268,331.93            319,353.99
              Logical reads:             19,272.63             22,937.23
===>sp_P1ROCCS_1114_12:03_13:07.lst
Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:            316,435.43            342,552.49
              Logical reads:             22,151.92             23,980.23
===>sp_P1ROCCS_1114_14:00_15:00.lst
Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:            149,285.73            391,643.98
              Logical reads:              6,620.19             17,367.75
===>sp_P1ROCCS_1114_15:00_16:00.lst
Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:            158,509.15            256,437.01
              Logical reads:              7,857.87             12,712.51
===>sp_P1ROCCS_1114_16:00_16:58.lst
Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:            197,935.84            243,677.31
              Logical reads:             10,099.65             12,433.60
===>sp_P1ROCCS_1114_16:58_17:00.lst
Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:            799,700.92            948,216.80
              Logical reads:             37,202.29             44,111.29
===>sp_P1ROCCS_1114_17:00_18:00.lst
Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:            541,252.15            441,183.58
              Logical reads:             26,538.76             21,632.18

I know under cygwin I can use grep -A option to achieve same goal.
I can do something as follows:
grep -A5 "Load Profile" *_1114*.lst

Note:
-A NUM, --after-context=NUM
Print NUM lines of trailing context after matching lines. Places a line
containing -- between contiguous groups of matches.

However, the version of grep on our server does not have this option.

startup open restrict

Not familar with this command before. Did a test as follows:

sys@TEST10G> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@TEST10G> startup open restrict
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             163580804 bytes
Database Buffers          440401920 bytes
Redo Buffers                7135232 bytes
Database mounted.
Database opened.

sys@TEST10G> select logins from v$instance;

LOGINS
----------
RESTRICTED

sys@TEST10G> alter system disable restricted session;

System altered.

sys@TEST10G> select logins from v$instance;

LOGINS
----------
ALLOWED

Copy optimizer table statistics between different schema and databases

This test case is to simulate the situation in which we need to copy a table stats from one schema to anohter schema in a different database.

Summary of Steps:
1. Set up the initial tables and collect stats
2. Export table stats using dbms_stats package in the source database and schema
3. Exp the stats table from source database
4. Imp the stats table to the target database and target schema
5. Import table stats using dbms_stats package in target database

Note: Pay attention to the neccessity of updating c5 in the stats tab to replace source schema name with target schema name.


1. set up the inital tables
Note: Table t is in hr, we want to copy its stats to table t in scott

scott@TEST10G> @1_setup
scott@TEST10G> connect hr/hr;
Connected.
hr@TEST10G>
hr@TEST10G> drop table t;

Table dropped.

hr@TEST10G>
hr@TEST10G> create table t
  2  as
  3  select * from all_objects;

Table created.

hr@TEST10G> exec dbms_stats.gather_table_stats(user, 'T');

PL/SQL procedure successfully completed.

hr@TEST10G>
hr@TEST10G> grant select on t to scott;

Grant succeeded.

hr@TEST10G>
hr@TEST10G> connect scott/tiger;
Connected.
scott@TEST10G> drop table t;

Table dropped.

scott@TEST10G> create table t
  2  as
  3  select * from hr.t;

Table created.

2. Export table stats

hr@TEST10G> conn hr/hr
Connected.
hr@TEST10G>
hr@TEST10G> drop table t_stats;

Table dropped.

hr@TEST10G> exec dbms_stats.create_stat_table(user, 'T_STATS');

PL/SQL procedure successfully completed.

hr@TEST10G> exec dbms_stats.export_table_stats(user, 'T', stattab => 'T_STATS');

PL/SQL procedure successfully completed.

3. Exp the stats table: T_STATS

exp hr/hr tables=t_stats file=t_stats.dmp log=3_exp_t_stats.log

$ 3_exp_t_stats.sh

Export: Release 10.2.0.1.0 - Production on Mon Nov 10 18:51:38 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                        T_STATS         14 rows exported
Export terminated successfully without warnings.

4. Imp the stats table: T_STATS
imp system/oracle fromuser=hr touser=scott tables=t_stats file=t_stats.dmp log=4_imp_t_stats.log

Import: Release 10.2.0.1.0 - Production on Mon Nov 10 18:52:22 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by HR, not by you

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing HR's objects into SCOTT
. . importing table "T_STATS" 14 rows imported
Import terminated successfully without warnings.

5. Import table stats
Note: to update c5 in stats table is necessary !

system@TEST10G> @5_imp_tab_stats.sql
system@TEST10G> connect scott/tiger
Connected.
scott@TEST10G>
scott@TEST10G> -- must update column c5
scott@TEST10G>
scott@TEST10G> Prompt c5 stores schema name
c5 stores schema name
scott@TEST10G> select c5 from t_stats;

C5
------------------------------
HR
HR
HR
HR
HR
HR
HR
HR
HR
HR
HR
HR
HR
HR

14 rows selected.

scott@TEST10G>
scott@TEST10G>
scott@TEST10G> --  see what will happen if we don't update c5
scott@TEST10G> exec dbms_stats.import_table_stats(user, 'T', stattab=> 'T_STATS');

PL/SQL procedure successfully completed.

scott@TEST10G>
scott@TEST10G>
scott@TEST10G> select owner,table_name, num_rows, last_analyzed
  2    from dba_tables where table_name='T' and owner in ('HR', 'SCOTT');

OWNER             TABLE_NAME      NUM_ROWS LAST_ANALYZED
----------------- ------------- ---------- -------------------
HR                T                  40733 2008-11-10 18:59:31
SCOTT             T

scott@TEST10G>
scott@TEST10G>
scott@TEST10G> -- update c5
scott@TEST10G>
scott@TEST10G> update t_stats set c5='SCOTT';

14 rows updated.

scott@TEST10G>
scott@TEST10G> exec dbms_stats.import_table_stats(user, 'T', stattab=> 'T_STATS');

PL/SQL procedure successfully completed.

scott@TEST10G>
scott@TEST10G> select owner,table_name, num_rows, last_analyzed
  2    from dba_tables where table_name='T' and owner in ('HR', 'SCOTT');

OWNER          TABLE_NAME       NUM_ROWS LAST_ANALYZED
-------------- -------------- ---------- -------------------
HR             T                   40733 2008-11-10 18:59:31
SCOTT          T                   40733 2008-11-10 18:59:31

Some Basic RAC Concepts

I found previously I have developed some reading notes about RAC, I am reviewing them today, becuase I will have a technical phone screen tonight which is said about Oracle DBA with RAC experiences. Hopefully review those notes will refresh my mind.

Below are part of the notes about some basic RAC concepts:

13. RAC concepts

13.1 What is RAC ?
RAC architecture consisted of:
(1) Clustered hardware:
- Multiple instances accessing the same database
- One instance per node
- Interconnected nodes act as a single server

(2) Real Application Clusters software:
- Manage coordinated data access and data changes among instances
- Each instance sees a consistent image of the database
- Pass coordination information and data images among each interconnected nodes.

13.2 Why use RAC
- High availability
- Scalability

13.3 RAC specific processes
At RAC instance level
* LMON – GES Monitor
* LMD0 – GES Daemon
* LMSx – GCS processes: manages resources and provides inter-instance resource control.
* LCK0: lock process
* DIAG: Diagnosability process
At cluster level:
* CRSD and RACGIMON: are engines for high-availability operations
* OCSSD: Provides access to node membership and group services
* EVMD: Scan callout directory and invokes callouts in reactions to detected events
* OPRROCD: Is a process monitor for the cluster: (not in Windows and Linux)

13.4 Global Resource Coordination

- Global Resource Directory (GRD):
* Each instance maintains a part of GRD
* Each particular resource has a master (the instance that manages all the information about this resource)
- Global Cache Service (GCS)
* buffer cache
* cache coherency – Cache Fusion algorithm
- Global Enqueue Service (GED)
* dictionary cache locks
* library cache locks
* deadlock

13.5 Object Affinity and Dynamic Remastering

- GCS enables the database to automatically adapt and migrate resources in the GRD
- GCS keeps track of the number of GCS requests on a per-instance and per-object basis.
- Master a buffer cache resource on the instance where it is mostly accesse

13.6 RAC database files
- Must be on local storage: Oracle cluster software (ORA_CRS_HOME)
- Can be on shared but better on local storage: Oracle RDBMS and ASM software
- Must be on shared storage (e.g. ASM disk groups, CFS )

RAW devices:
* OCR files - on raw devices
* Voting files (disks) – on raw devices

ASM Disk group: +DATADG
* Data files
* Temp files
* Control files
* SPFILE
* Undo tablespace files ( each instance has its own dedicated ones)
* Redo log files ( at least two groups for each instance)

ASM DISK group: +FLADG
* Flash recovery area files
* Change tracking file
* Archived log files* Control file (duplicate

KSH expr command integer overflow

Last night we have recived false alerts about a filesystem space issue. It turns out there is a bug in the script related to integer overflow in the "expr" command. I found this overflow error can be see on our HP-UX system, but not on Solaris or Linux machines. Here are the testing results:

----------- test script: test.ksh begins next line -----------------
#!/bin/ksh
## Bad##
FS=2840644712
FS=`expr $FS / 1024` ;
echo $FS
## Good##
FS=2840644712
FS=`echo $FS/1024 bc `
echo $FS
----------- test script ends last line -----------------


HP-UX
~~~~~~~~

$ uname -a
HP-UX dhpnbe5 B.11.23 U ia64 3928363565 unlimited-user license
$ ./test.ksh
-1420236
2774067


Linux
~~~~~~

$ uname -a
Linux wrpe2e06.edc.mycompany.net 2.6.9-55.0.9.ELlargesmp #1 SMP Tue Sep 25 02:25:48 EDT 2007 x86_64 x86_64 x86_64 GNU/Linux
oracle@wrpe2e06.edc.mycompany.net(!) ph1etem3 /tmp
$ ./test.ksh
2774067
2774067


Solaris
~~~~~~~

$ uname -a
SunOS wspebp06 5.10 Generic_127111-11 sun4u sparc SUNW,Sun-Fire
$ ./test.ksh
2774067
2774067