Wednesday, June 20, 2007

HP-UX Perl

This is a quick guide to using Perl on HP-UX and installing the interfaces to an Oracle database and MS Excel output.
In some environments it may be difficult to have additional Perl modules installed by the system administrators. This guide will take advantage of the default Perl installation, and install additional module as a non-root end user.

Perl source code can be downloaded and compiled. It is often more convenient to download a precompiled binary. Recent versions (11 and 11i) of HP-UX include Perl, which is a sufficient start for most scripting.

Determine your version of HP-UX with the uname command:
$ uname -a
HP-UX hostname B.11.11 U 9000/800 2155931687 unlimited-user license

The Perl interpreter may already be in your PATH environment variable:
$ which perl

/usr/bin/perl is a soft link to /opt/perl.
$ ls -l /usr/bin/perl
lrwxr-xr-x 1 bin bin 18 Sep 28 2005 /usr/bin/perl -> /opt/perl/bin/perl

There is also an /opt/perl_64.
$ ls -ld /opt/perl*
dr-xr-xr-x 6 bin bin 96 Sep 28 2005 /opt/perl
dr-xr-xr-x 6 bin bin 96 Sep 28 2005 /opt/perl_64

Determine the version.

$ perl -v

This is perl, v5.8.2 built for PA-RISC1.1-thread-multi
(with 25 registered patches, see perl -V for more detail)

Copyright 1987-2003, Larry Wall

Binary build 808 provided by ActiveState Corp.
ActiveState is a division of Sophos.
Built May 3 2005 14:06:55

"perl -V" will display details of what was compiled.

Wednesday, March 28, 2007

Oracle9i Database: Performance Tuning, 1Z0-033

The Oracle9i Database: Performance Tuning is the second of two required tests to achieve Oracle Certified Professional status.

These notes are from OCP Oracle 9i Database: Performance Tuning Exam Guide by Charles Pack.

This view is a summary of waits for an event by an instance. While V$SESSION_WAIT shows the current waits on the system, V$SYSTEM_EVENT provides a summary of all the event waits on the instance since it started. It is useful to get a historical picture of waits on the system. By taking two snapshots and doing the delta on the waits, you can determine the waits on the system in a given time interval.

This view keeps a summary all buffer waits since instance startup. It is useful for breaking down the waits by class if you see a large number of buffer busy waits on the system.

This contains overall statistics for many different parts of Oracle, including rollback, logical and physical I/O, and parse data. Data from V$SYSSTAT is used to compute ratios, such as the buffer cache hit ratio.

This contains detailed file I/O statistics for each file, including the number of I/Os for each file and the average read time.
This view contains information about file read/write statistics.

This contains detailed latch usage statistics for each latch, including the number of times each latch was requested and the number of times the latch was waited for.
V$LATCH_PARENT contains statistics about parent latches. The columns of V$LATCH_PARENT are identical to those in V$LATCH.
There are multiple latches in the database for some type of latches. V$LATCH provides aggregate summary for each type of latch. To look at individual latches, query the V$LATCH_CHILDREN view.

Methods for collecting statistics:

This view contains detailed information on the system global area (SGA).
SGA contains shared pool (library cache, dictionary cache, sometimes UGA).
If using MTS, configuring large pool can reduce library cache fragmentation.
Each row in this view contains statistics for one type of item kept in the library cache. The item described by each row is identified by the value of the NAMESPACE column. Rows with the following NAMESPACE values reflect library cache activity for SQL statements and PL/SQL blocks:

This view provides object level statistics for objects in the library cache (shared pool). This view provides more details than V$LIBRARYCACHE and is useful for finding active objects in the shared pool.

V$SQLAREA contains up to 1000 characters of the SQL, along with execution statistics.
V$SQLTEXT contains the entire SQL.
V$SQL_PLAN contains the actual plan. It could be useful to capture v$sql_plan for troubleshooting purposes when a SQL stops performing as expected.

DBMS_SHARED_POOL.KEEP will pin objects in the shared pool so they do not age out. alter system flush shared_pool will not clear pinned objects.
SHARED_POOL_RESERVED_SIZE provides space for larger memory allocations and defaults to 5% of the shared pool.
V$SHARED_POOL_RESERVED shows space used, misses, etc. If request_failures > 0 then increase shared_pool_size.

V$ROWCACHE dictionary cache statistics. Usually tuned by increasing shared_pool_size.
UGA, User Global Area. Contains user session data and cursor state. When using shared server, this moves from PGA to shared pool.
LARGE_POOL_SIZE Large pool, when configured, is used for RMAN IO buffers, parallel query message buffers, and UGA when using shared server. Does not have an LRU. Setting parallel_automatic_tuning=true will enable large pool.
If library cache hit is 100%, may set cursor_space_for_time=true.
When using star transformations or DSS queries, use cursor_sharing=exact.

SQL sorts and parallel reads bypass buffer cache, which implies the buffer cache does checkpoints to keep the buffer cache in sync.
Full table scan blocks are placed at end of LRU list.
Buffer cache read of block:
- reading process uses hash function to look for block in buffer cache
-- if found, block moves to MRU
-- if not found, LRU is searched for free buffer, block is read from disk and placed in free buffer
-- if block read from disk is too recent for query read consistency, look in rollback
DBWn writes when:
- dirty list length exceeds threshold
- free block not found in LRU
- every 3 seconds
- LGWR checkpoint
- alter tablespace begin backup / offline
- object is dropped
- shutdown normal / immediate / transactional

DB_CACHE_ADVICE enables statistics gathering for V$DB_CACHE_ADVICE.
SGA_MAX_SIZE is upper bound of dynamic SGA growth.
V$BUFFER_POOL general info for all buffer pools.
V$BUFFER_POOL_STATISTICS statistics for all buffer pools.

LRU latch contention:
cache buffer lru chain
The cache buffer lru chain latches protect the lists of buffers in the cache. When adding, moving, or removing a buffer from a list, a latch must be obtained. Set to 1/2 number of CPUs. Monitor with v$system_event.
Cache buffer chains protect the list and are used when using the list. Monitor v$latch_children for high misses or sleeps.

Redo Log Buffer is a circular buffer in the SGA that holds information about changes made to the database.
- set to at least 1MB
- no real penalty for setting a little too high
- v$sysstat "redo buffer allocation retries" should be near zero
- v$session_wait "log buffer space" should be zero

JAVA_SOFT_SESSIONSPACE_LIMIT writes warning to trace file.
JAVA_MAX_SESSIONSPACE_SIZE hard limit that kills session.
DBWR_IO_SLAVES can simulate async IO.
DB_WRITER_PROCESSES is useful for systems that modify data heavily. It specifies the initial number of database writer processes for an instance. Multiple db writer processes are highly recommended for systems with multiple CPUs (at least one db writer for every 8 CPUs) or multiple processor groups (at least as many db writers as processor groups).

When using workarea_size_policy=auto and pga_aggregate_target, do not set:
V$PGASTAT monitors PGA usage.
V$SYSSTAT where name like 'sort%' for sorts to memory/disk.
V$SORT_SEGMENT info on every sort segment.
V$SORT_USAGE.username shows usage by user.

V$LATCH stats for SGA latching.
Wait event "latch free" is when a process tries for a latch and can not get it, then it sleeps. It spins on multi-cpu machines.
If "willing to wait", the process tries and then spins.
Latch request is "immediate" when it already has one or more latches and needs another. If this latch is not obtained, it releases all latches and starts over.

V$ROLLNAME has names of online rollback segments.
V$ROLLSTAT rollback segments statistics.
V$TRANSACTION useful for joining on UndoSegmentNumber.

What generates undo:
delete generates the most
inserts only writes the rowid
update writes row and index.

V$UNDOSTAT undo consumption.

Automatic undo pfile parameters:

How to switch to a new undo tablespace:
alter system set undo_tablespace=___________
Existing transactions will continue to use the previous tablespace.

PMON Process MONitor cleans up aborted transactions and their locks.

DML_LOCKS=0. Enqueues are disabled and performance is slightly increased. However, you cannot use DROP TABLE, CREATE INDEX, or explicit lock statements such as LOCK TABLE IN EXCLUSIVE MODE.

delayed block cleanout - lock on the row is cleaned by the next transaction doing DML on the row. The lock is not cleaned from the row when the original locking transaction does a commit.

V$LOCK - This view lists the locks currently held by the Oracle database server and outstanding requests for a lock or latch.
V$ENQUEUE_LOCK - This view displays all locks owned by enqueue state objects. The columns in this view are identical to the columns in V$LOCK.
V$LOCKED_OBJECT - This view lists all locks acquired by every transaction on the system.
DBA_BLOCKERS - DBA_BLOCKERS displays a session if it is not waiting for a locked object but is holding a lock on an object for which another session is waiting.
DBA_WAITERS - DBA_WAITERS shows all the sessions that are waiting for a lock, but do not hold locks for which another session is waiting.
utlloctt.sql shows locks in tree display.
DBMS_LOCK allows explicit sequential processing.
breakable parse lock A SQL statement (or PL/SQL program unit) in the shared pool holds a parse lock for each schema object it references. Parse locks are acquired so that the associated shared SQL area can be invalidated if a referenced object is altered or dropped. A parse lock does not disallow any DDL operation and can be broken to allow conflicting DDL operations, hence the name breakable parse lock.

A parse lock is acquired during the parse phase of SQL statement execution and held as long as the shared SQL area for that statement remains in the shared pool.

Shared Server
Oracle Net - tnsnames or other naming service.
init.ora - circuits, dispatchers, max_dispatchers, shared_servers, max_shared_servers, shared_server_sessions

v$queue contains information on the shared server message queues.

User Global Area moves to the shared pool or large pool.
Dispatchers are limited to ~1000 connections each.
Startup/shutdown still requires a dedicated server connection.

Application Tuning
Index cluster contains nulls. No index columns are specified, because the index is automatically built on all the columns of the cluster key. For cluster indexes, all rows are indexed.
hash cluster - direct row access via algorithm.
cluster good for - minimal key updates, tables frequently joined.

B tree index - balanced, when leaf block fills it is split in half, optionally can compress repeated values.
B tree reverse key index - can be useful with sequentially increasing keys to avoid last page contention and excessive index heights. Only useful for exact key lookups, can not be used for range scans.
Function based index - B tree or bitmap. Need query rewrite to use it.
Index Organized Table - requires primary key.
ROW OVERFLOW can split out non-PK columns.
contains logical row ID that is guess to row overflow.
may be partitioned.
may have bitmap index by using a mapping table.

Materialized View
complete - truncate and run query.
fast - apply changes via materialized view log.
force - uses fast if possible, else uses complete.
on commit - automatic refresh after transaction commit.
on demand - manually call dbms_mview.

segment <- extent <- block <- os block
Initial and next extent defined at time of object creation or defaults to tablespace settings.

dbms_space_admin works on locally managed tablespaces.

High water mark is stored in segment header.
High water mark is raised in five block increments.
alter table table_name deallocate unused will drop high water mark if possible.
Temporary segments must use default block size.
PCTFREE how much table space to reserve for future row updates.
PCTUSED when to put table block on free list.
row chaining - row does not fit in an empty block so it is split across blocks.
row migration - when updated row won't fit in it's block and entire row is moved with a pointer remaining.
row migration - seen in statspack as "table fetch continued row" and in v$sysstat.
alter index index_name coalesce consolidates leaf blocks.
alter index index_name rebuild online moves the index so new storage parameters may be specified.
v$object_usage stores results of monitoring table and index access.

Outlines are stored in schema OUTLN.
Outlines are grouped into categories.
DBA_OUTLINES describes all outlines in the database.
DBA_OUTLINE_HINTS describes the set of hints that make up the all the outlines in the database.

Create outlines for all SQL:
alter system set create_stored_outlines=
alter system set create_stored_outlines=FALSE; # Turns it off.

create or replace outline myoutline on SELECT ...


Use the ALTER OUTLINE statement to rename a stored outline, reassign it to a different category, or regenerate it by compiling the outline's SQL statement and replacing the old outline data with the outline created under current conditions.
alter outline myoutline rebuild;

Private outlines:
exc dbms_outln_edit.created_edit_tables
create private outline newoutln from oldoutln; # Clones the outline.

alter session set use_private_outlines=true;
create or replace outline ____ from private ____; # Publish it.

histogram good for skewed data.


dbms_system.set_sql_trace_in_session(SID=>' ', serial#=>' ', sql_trace=>TRUE)

explain plan set statement_id = ' ' for SELECT ...
set autotrace traceonly explain;

v$sql_plan has actual execution plan.
V$SQL_PLAN contains the execution plan information for each child cursor loaded in the library cache.
V$SQL_PLAN_STATISTICS provides execution statistics at the row source level for each child cursor.

Consumer groups contain like users.
One plan directive may be active in the instance at a time.

The DBMS_RESOURCE_MANAGER package maintains plans, consumer groups, and plan directives.
create_simple_plan (does it all in one step)


dbms_resource_manager_privs.grant_switch_consumer_group (grant to user not role)

alter system set resource_manager_plan= ____;
In pfile, the parm is resource_manager_plan.

v$rsrc_plan displays the names of all currently active resource plans.
v$rsrc_consumer_group displays data related to currently active resource consumer groups.
DBA_RSRC_PLANS lists all resource plans in the database.

Saturday, March 24, 2007

Sun Grid Engine

The April edition of Sys Admin magazine provides an introduction to Sun's open source job scheduler, Grid Engine. While able to effectively coordinate compute tasks across many machines, it also handles job dependencies and could provide pressure to vendors such as AppWorx.

Monday, March 19, 2007

HP-UX Network Interface Check Duplex

How to check duplex setting of network interface on HP-UX 11.11. "-x" is the interface.

bash-3.00# uname -a; lanscan -q ; lanadmin -x 13;lanadmin -x 15
HP-UX host001 B.11.11 U 9000/800 1288319306 unlimited-user license
900 11 0
901 15 13
Speed = 1000 Full-Duplex.
Autonegotiation = On.

Speed = 1000 Full-Duplex.
Autonegotiation = On.

$ /usr/sbin/lanadmin

Mon, Mar 19,2007 09:04:59

Copyright 1994 Hewlett Packard Company.
All rights are reserved.

Test Selection mode.

lan = LAN Interface Administration
menu = Display this menu
quit = Terminate the Administration
terse = Do not display command menu
verbose = Display command menu

Sunday, March 18, 2007

Turn Off Oracle Index Parallel Query

Parallel query can be useful for reducing elapsed run time of Oracle index scans. In some circumstances, parallel query can cause longer elapsed run times.

As the schema owner, determine the index parallel query settings:
SQL> l
1 select degree, instances , count(*)
2 from user_indexes
3* group by degree, instances
SQL> /

--------- ---------------------------------------- ----------
0 0 4
1 1 1303
6 1 8
10 1 3

Run SQL to create a SQL file that will set noparallel:
$ cat bill_ix_alter_nopq_gen.sql
set pages 0 lines 100 feedback off
spool bill_ix_alter_nopq.sql
prompt spool bill_ix_alter_nopq

select 'alter index ' || index_name || ' noparallel;' from user_indexes where degree > '1'
order by 1
spool off

Run the generated SQL file (bill_ix_alter_nopq.sql).

Verify the new settings:
SQL> l
1 select degree, instances , count(*)
2 from user_indexes
3* group by degree, instances
SQL> /

--------- ---------------------------------------- ----------
0 0 4
1 1 1352

Wednesday, March 07, 2007

Monitoring Oracle Data File For Can't Extend

There are several pitfalls when creating SQL that will monitor for a can't extend condition in the Oracle database.

This appears to show a data file larger than the maximum size.

SQL> select substr(FILE_NAME,22,23) as data_file_name, bytes, maxbytes, maxblocks, USER_BYTES, AUTOEXTENSIBLE from dba_data_files where tablespace_name='CUST_BILL_INV_TAX'

------------------------- ---------------- ---------------- ------------ ---------------- ---
cust_bill_inv_tax02.dbf 34,129,051,648 34,359,721,984 4,194,302 34,078,720,000 YES
cust_bill_inv_tax01.dbf 34,358,689,792 34,358,689,792 4,194,176 34,078,720,000 YES
cust_bill_inv_tax03.dbf 16,532,635,648 2,147,483,648 262,144 16,252,928,000 YES

SQL> select * from v$version;

Oracle9i Enterprise Edition Release - 64bit Production
PL/SQL Release - Production
CORE Production
TNS for HPUX: Version - Production
NLSRTL Version - Production

Oracle Note:197244.1 explains this situation.
The MAXBYTES column in DBA_DATA_FILES is not updated when the datafile has been resized. The only column that is updated is BYTES. The MAXBYTES is updated by the ALTER DATABASE command with MAXSIZE option as described above.

Thursday, February 15, 2007

Alternative To SELECT_CATALOG_ROLE In Oracle

It may be useful to allow developers to look at the plsql (views, procs, etc) for a schema without granting the SELECT_CATALOG_ROLE role. This view will provide access, with the possible drawback of adding an object to the SYS schema.

Run this as sys, like:
sqlplus "/ as sysdba" @script.sql

from sys.dba_source
where owner='ASCHEMAOWNER';

create or replace public synonym plsql_ASCHEMAOWNER for sys.plsql_ASCHEMAOWNER;
grant select on sys.plsql_ASCHEMAOWNER to public;

desc plsql_aschemaowner
Name Null? Type
----------------------------------------- -------- ----------------------------

Basic Oracle Role For All Users

Prior to changes in Oracle 10, an "enduser" role was useful in every database. The role alllows a connecting and a few harmless though useful grants. Every new user would have this role.


Monday, January 29, 2007

Oracle Database Go-Live Check List

Items to review before production go-live of an Oracle database system.

+) Security
accounts (check users with roles: DBA, import any, ...)
listener password
(if non-DBAs can log on to DB machine) check file Oracle directory and permissions
check for default DB accounts that can be locked or have the passwords changed
change OS passwords
change at least DB system password

+) Automate instance/listener start and stop with the operating system startup "rc" scripts.

+) Backup/recovery
verify archive log mode is on
verify rman jobs are scheduled and running correctly
in case backup system is down, ensure a couple days of disk space for archive_log_dest
export schema rows=n weekly or monthly
depending on requirements and performance considerations, enable db_block checksum

+) Performance
configure resource governor to guide CPU usage and parallel query usage
configure resource governor to limit undo usage by non-application owner accounts
schedule statspack snap every twenty or thirty minutes
enable monitoring on application tables
schedule dbms_stats.gather_schema_stats to gather stale (usually weekly or monthly)
use dbms_stats to create and export a statistics table (useful to quickly restore older version of statistics)

+) Checks
tablespace that will not be able to grow
undo tablespace growth is capped at less than the size of the file system
alert log error messages
users with default tablespace of system
operating system error log "syslog"
network error (netstat -i)

+) Multiple temp tablespaces
one tablespace that is only available to application owner
other tablespaces to segregate other users from interfering with application temp usage

Saturday, January 27, 2007

Disk Usage By User

In shared unix filesystems, like /tmp, it is often useful to find who is using the most disk space.

du -sk *
will find large files,
find . -user mc -type f -exec du -k {} \; | awk '{ s = s+$1 } END { print "Total used: ",s }'
will show which user is consuming the most space.

IBM has an article describing this combination of find, du, and awk.

Monday, January 15, 2007

Locating Perl Modules

If perl modules are installed and perl is complaining of not being able to find the modules, try one of these:

1) create environment PERL5LIB, which works similar to the PATH environment variable

2) in the perl code, add:
BEGIN {unshift @INC, "/home/user/perl/modules/lib"};

3) in the perl code, add:
use lib '/home/user/perl/modules/lib';

Saturday, January 13, 2007

Oracle User Logon Trigger

It can be useful to set session settings for a user in a logon trigger. A user may want to override the setting for a specific parameter such as star_transformation_enabled, or it may be necessary to turn on sql_trace for a user.

create or replace trigger logontrigger after logon on schema
p_session_user varchar2(30);
p_host varchar2(200);
p_sql varchar2(230);
p_ip_address varchar2(30);
p_sessionid number(30);
select translate(sys_context('userenv', 'host'), '-\ /', '____') into p_host from dual;
select sys_context('userenv', 'session_user') into p_session_user from dual;
select sys_context('userenv', 'sessionid') into p_sessionid from dual;
select sys_context('userenv', 'ip_address') into p_ip_address from dual;

-- Create trace file with a recognizable name.
p_sql:='alter session set tracefile_identifier=' || p_session_user || '_' || p_host;
execute immediate p_sql;

-- Choose one of the following trace levels. The second form provides bind variables and the result set.
p_sql:='alter session set sql_trace=true';
--select 'alter session set events ' || '''10046 trace name context forever, level 12''' into p_sql from dual;
execute immediate p_sql;


show err
prompt Remember the user needs 'alter session' privilege.
prompt To remove: drop trigger logontrigger
After creating the logon trigger, log on as the user. If it does not generate a file in the user jump destination, then check the alert.log for errors.

Tuesday, January 09, 2007

Oracle Data Migration Via SQL Script

Data changes are often promoted to production environments via scripts that are stored in a change control system.

If using a SQL script to apply data changes, these are useful to include:

-- The output file name. DO NOT include a path nor a drive letter.
spool outputFileName

prompt Include info here about defect tracking number, developer, etc.

-- On error rollback and exit. This is generally only needed for scripts that will be scheduled. For an ad-hoc migration, this is not needed.
whenever sqlerror exit failure rollback
whenever oserror exit failure rollback

-- Set the date format to avoid relying on defaults that are platform-specific.
alter session set nls_date_format='MM/DD/YYYY';

-- set echo on will print the SQL to the screen. This is optional and may not be useful if there is too much output.
set echo on

-- set define off will disable interpreting of the ampersand character.
set define off

-- Select the current time to show when script was implemented.
select systimestamp from dual;

-- set timing on will print elapsed time of each command.
set timing on

-- place script contents here.
UPDATE tablename set columna=value1;

-- spool off will stop logging to the output file.
spool off

For change control purposes, the migrations to production may run via sqlplus. Be aware that sqlplus has a limit to the number of lines per SQL, while tools such as Benthic appear to not have a reasonable limit to the number of lines per SQL. What may be encountered is a developer testing a script via Benthic with 5000 or more lines per statement (typically a large IN list) that will not run via sqlplus. The error message will be:
ERROR at line 565:
ORA-00933: SQL command not properly ended

Wednesday, January 03, 2007

Become Another Oracle User

This technique is useful to log in as another user to troubleshoot a problem they may be having. Oracle DBA privileges are needed.

Open two windows.

In the first window:
select password from dba_users where username='THEUSER'
alter user THEUSER identified by pass

In the second window:
sqlplus THEUSER/pass

In the first window:
alter user THEUSER identified by values 'password values from first query'

In the second window you will be logged on as the user. If the password can be set and reset quickly, this can be used for troubleshooting on a live production system.

Tuesday, January 02, 2007

Multi-Platform File Salvage

The PhotoRec program is multiplatform and can recover dozens of file types on corrupted or "formatted" file systems.