James's Blog

Oracle: wait events

2006-02-22T15:34:00.000-08:00

log file sync: is a client wait event. It is the wait event your clients wait on when they say "commit". It is the wait for LGWR to actually write their redo to disk and return back to them. You can "tune" this by making lgwr faster (no raid 5 for example) and committing less frequently and generating less redo (BULK operations generate less redo than row by row do)

LGWR wait for redo copy: is a background wait. LGWR is waiting for forgrounds to finish a current copy affecting the data LGWR is about to process.

enqueue: is your wait and that is all about application design - those are heavyweight locks induced by the application logic itself. You would be best served by looking at the *application* not at the "system" at this point.
do a level 12 10046 trace event on your application and see what it is truly waiting for. it is all about tuning the application.

Oracle: v$bh - who is using buffer cache?

2006-02-19T22:29:00.000-08:00

column c0 heading 'Owner' format a15
column c1 heading 'ObjectName' format a30
column c2 heading 'NumberofBuffers' format 999,999
column c3 heading 'Percentageof DataBuffer' format 999,999,999

select
owner c0,
object_name c1,
count(1) c2,
(count(1)/(select count(*) from v$bh)) *100 c3
from
dba_objects o,
v$bh bh
where
o.object_id = bh.objd
and
o.owner not in ('SYS','SYSTEM','AURORA$JIS$UTILITY$')
group by
owner,
object_name
order by
count(1) desc
;

Oracle: Bulk Collect

2006-02-15T21:06:00.000-08:00

DECLARE
TYPE EmpCurTyp IS REF CURSOR;
TYPE NumList IS TABLE OF NUMBER;
TYPE NameList IS TABLE OF VARCHAR2(15);
emp_cv EmpCurTyp;
empnos NumList;
enames NameList;
sals NumList;
BEGIN
OPEN emp_cv FOR 'SELECT empno, ename FROM emp';
FETCH emp_cv BULK COLLECT INTO empnos, enames;
CLOSE emp_cv;

EXECUTE IMMEDIATE 'SELECT sal FROM emp'
BULK COLLECT INTO sals;
END;

--- only insert/update/delete can have output bind variables.
--- to bulk-bind them, use BULK RETURNING INTO clause in an EXECUTE IMMEDIATE.

DECLARE
TYPE NameList IS TABLE OF VARCHAR2(15);
enames NameList;
bonus_amt NUMBER := 500;
sql_stmt VARCHAR(200);
BEGIN
sql_stmt := 'UPDATE emp SET bonus = :1 RETURNING ename INTO :2';
EXECUTE IMMEDIATE sql_stmt
USING bonus_amt RETURNING BULK COLLECT INTO enames;
END;

Oracle: binding variable or non-binding variable

2006-02-15T20:38:00.000-08:00

No-bind
--------------
set timing on
declare
x number;
begin
for i in 1 .. 50000 loop
execute immediate
'select count(*) from dual where dummy = 'i into x;
end loop;
end;

with-bind
---------------
set timing on
declare
x number;
begin
for i in 1 .. 50000 loop
select count(*) into x from dual where dummy = i;
end loop;
end;

or

select count(*) into x from dual where dummy = :i using i;

Oracle: DBMS_SYSTEM Package

2006-02-14T16:06:00.000-08:00

1) ksdwrt
exec dbms_system.ksdwrt(n, message);
"n" indicates the destination
1 - write to trace file
2 - write to alert log
3 - write to both

2) set_sql_trace_in_session
exec dbms_system.set_sql_trace_in_session(sid, serial#, true);

3) set_ev
exec dbms_system.set_ev(sid,serial#, event, level, name);
exec dbms_system.set_ev(31, 97, 10046, 4, '');
level indicates:
1 - standard sql_trace functionality.
4 - as level 1 plus tracing of bind variables
8 - as level 1 plus wait events
12 - as level1 plus bind variables and wait events.

4) read_ev
exec dbms_system.read_ev(event,output);
if output = 1 the event is being traced.

Oracle: time/date operation

2006-02-14T13:47:00.000-08:00

1. date interval

select sysdate, sysdate - interval '7' MINUTE from dual

select sysdate, sysdate - interval '7' day from dual

select sysdate, sysdate - 8*interval '2' hour from dual

2. date to char conversion
select sysdate, to_char(sysdate, 'yyy-mm-dd hh24:mi:ss') from dual

3. char to date conversion
select to_date('2005-10-17 21:15:37','yyyy-mm-dd hh24:mi:ss') from dual

4. trunk/round functions
select trunc(sysdate,'YEAR') from dual

5. using mili-second date type
select to_char(current_timestamp(9),'HH24:MI:SSxFF') from dual

6. calculate process runtime
declare
type rc is ref cursor;
l_rc rc;
l_dummy all_objects.object_name%type;
begin
for i in 1 .. 1000
loop
open l_rc for 'select object_name from all_objects '
'where object_id = ' i;
fetch l_rc into l_dummy;
close l_rc;
end loop;
dbms_output.put_line
(round( ( dbms_utility.get_time-l_start)/100, 2) ' seconds...');
end;

Oracle: how to skip bad block

2006-02-13T16:10:00.000-08:00

First thing you need to do is find the bad block ids. To do this you can run dbverify. Lets assume our block id is 1234 .

Now find the segment name by running this query
select segment_name, segment_type, block_id, blocks from dba_extents where (1234 between block_id and (block_id + blocks - 1))

Once you have the bad segment name, its better to create a temporary table with good data if the segment is a table. If the segment is an index, drop the index and recreate it.
create table good_table as select * from bad_table where rowid not in ( select /*+index(bad_table, any_index)*/ rowid from bad_table where substr( rowid, 1, 8 ) = 1234)

Oracle: Oradebug - view bind value

2006-02-13T15:42:00.000-08:00

1) get sid
select username,sid,serial#
from v$session
where audsid = userenv('sessionid');

2) oradebug setorapid 15
oradebug EVENT 10046 trace name context forever,level 12

Java: GC tunning

2006-02-09T10:53:00.000-08:00

http://www.petefreitag.com/articles/gctuning/

3.2 The Young Generation
The bigger the young generation the less minor GC's, but this implies a smaller tenured generation which increases the frequency of major collections.
You need to look at your application and determine how long your objects live for to tune this.
-XX:NewRatio=3 - the young generation will occupy 1/4 the overall heap
-XX:NewSize - Size of the young generation at JVM init. Calculated automatically if you specify -XX:NewRatio
-XX:MaxNewSize - The largest size the young generation can grow to (unlimited if this value is not specified at command line)

4 Types of Collectors
Everything to this point talks about the default garbage collector, there are other GC's you can use
Throughput Collector - Uses a parallel version of the young generation collector
-XX:+UseParallelGC
Tenured collector is the same as in default
Concurrent Low Pause Collector
Collects tenured collection concurrently with the execution of the app.
The app is paused for short periods during collection
-XX:+UseConcMarkSweepGC
To enable a parallel young generation GC with the concurrent GC add -XX:+UseParNewGC to the startup. Don't add -XX:+UseParallelGC with this option.
Incremental Low Pause Collector
Sometimes called Train Collector
Collects a portion of the tenured generation at each minor collection.
Tries to minimize large pause of major collections
Slower than the default collector when considering overall throughput
Good for client apps (my observation)
-Xincgc
Don't mix these options, JVM may not behave as expected.

Oracle: Runtime Row Source Statistics

2006-02-08T15:43:00.000-08:00

9iR2 introduces dynamic performance table: v$sql_plan_statistics.
it provides for each cached cursor the execution statistics of
each operation in the execution plan.
to use this view, parameter STATISTICS_LEVEL must be set to ALL.

additional information, see

v$sql_plan
v$sql_plan_statistics_all
v$sql_workarea ( contains memory usage statistics for row sources that use sql memory, for instance, hash-join and sort)

• v$sql_plan - This view shows the same information as shown by Explain Plan except it is the actual execution plan and not the predicted one – just like tkprof and even better than Explain Plan.

• v$sql_plan_statistics - This view contains the execution statistics for each operation (step) in the v$sql_plan. Queries should access this view and look for poor SQL operations including TABLE ACCESS FULL – full-table scans.

• v$sql_plan_statistics_all - This view combines data from v$sql_plan, v$sql_plan_statistics and v$sql_workarea.

Both v$sql_plan_statistics and v$sql_plan_statistics_all are not populated by default. The option statistics_level=all must be set.
Or turn on sql_trace will also populates these views.

Oracle: 9i segment level statistics

2006-02-08T15:41:00.000-08:00

9i release 2 and higher,
segment statistics could be retrieved from following views.

v$segment_statistics
v$segstat
v$segstat_name

select owner, object_name,statistic_name,value
from v$segment_statistics
where owner='SYSTEM'
and object_name='TABLE'

Perl: references of array/hash/scalar

2006-02-07T11:04:00.000-08:00

# create variables
$a = "world";
@array = (10,20,30);
%hash = ("james"=>"pei", "lisa"=>"han");

# create references to them
$ra = \$a;
$rarray= \@array;
$rhash = \%hash;

# create references to constant scalars
$ra = \10;
$rs = \"hello world";

# references to scalars
$ra = \$a;
$$ra += 2;
print $$ra;

# references to Hashes
$rhash = \%hash;
print $rhash->{"key1"};

Oracle: Hit Radio

2006-02-04T17:47:00.000-08:00

Buffer Hit Ratio
BUFFER HIT RATIO NOTES:

Consistent Gets - The number of accesses made to the block buffer to retrieve data in a consistent mode.
DB Blk Gets - The number of blocks accessed via single block gets (i.e. not through the consistent get mechanism).
Physical Reads - The cumulative number of blocks read from disk.

Logical reads are the sum of consistent gets and db block gets.
The db block gets statistic value is incremented when a block is read for update and when segment header blocks are accessed.
Hit Ratio should be > 80%, else increase DB_BLOCK_BUFFERS in init.ora

select sum(decode(NAME, 'consistent gets',VALUE, 0)) "Consistent Gets",
sum(decode(NAME, 'db block gets',VALUE, 0)) "DB Block Gets",
sum(decode(NAME, 'physical reads',VALUE, 0)) "Physical Reads",
round((sum(decode(name, 'consistent gets',value, 0)) +
sum(decode(name, 'db block gets',value, 0)) -
sum(decode(name, 'physical reads',value, 0))) /
(sum(decode(name, 'consistent gets',value, 0)) +
sum(decode(name, 'db block gets',value, 0))) * 100,2) "Hit Ratio"
from v$sysstat

Data Dict Hit Ratio
DATA DICTIONARY HIT RATIO NOTES:

Gets - Total number of requests for information on the data object.
Cache Misses - Number of data requests resulting in cache misses

Hit Ratio should be > 90%, else increase SHARED_POOL_SIZE in init.ora

select sum(GETS),
sum(GETMISSES),
round((1 - (sum(GETMISSES) / sum(GETS))) * 100,2)
from v$rowcache

SQL Cache Hit Ratio
SQL CACHE HIT RATIO NOTES:

Pins - The number of times a pin was requested for objects of this namespace.
Reloads - Any pin of an object that is not the first pin performed since the object handle was created, and which requires loading the object from disk.

Hit Ratio should be > 85%

select sum(PINS) Pins,
sum(RELOADS) Reloads,
round((sum(PINS) - sum(RELOADS)) / sum(PINS) * 100,2) Hit_Ratio
from v$librarycache

Library Cache Miss Ratio
LIBRARY CACHE MISS RATIO NOTES:

Executions - The number of times a pin was requested for objects of this namespace.
Cache Misses - Any pin of an object that is not the first pin performed since the object handle was created, and which requires loading the object from disk.

Hit Ratio should be < 1%, else increase SHARED_POOL_SIZE in init.ora

select sum(PINS) Executions,
sum(RELOADS) cache_misses,
sum(RELOADS) / sum(PINS) miss_ratio
from v$librarycache

Oracle: DBWn or DB Slaves

2006-02-02T14:10:00.000-08:00

To determine whether to use multiple DBWn processes or database slaves, follow these guidelines:

1) For write intensive applications that also have a large data buffer cache (100,000 and up), configure DB_WRITER_PROCESSES

2) For applications that are not write intensive and run on operating systems that support asynchronous I/O, configure DBWR_IO_SLAVES

3) If the operating system does not support asnychronous I/O, use DBWR_IO_SLAVES

4) If your server only has one CPU, use DBWR_IO_SLAVES, DBWR processes are CPU intensive

UNIX: Sendmail

2006-02-01T17:00:00.000-08:00

to test relay using telnet
========================================

helo cland.com
250 gsc6.gv.synnex.net Hello cland.com (192.168.7.7)
mail from:
250 sender ok
RCPT TO:
250 Recipient ok
DATA
354 send the mail data, end with .
Subject: this is a test
this is a test
.
exit

Oracle: merge tables

2006-01-31T15:41:00.000-08:00

merge combined insert and update into one command

merge into EMP e1
using EXTERNAL_TABLE e2
on ( e2.empno = e1.empno )
when matched then
update set e1.sal = e2.sal
when not matched then
insert ( empno, ename, job, mgr, hiredate, sal, comm, deptno )
values ( e2.empno, e2.ename, e2.job, e2.mgr, e2.hiredate, e2.sal, e2.comm,
e2.deptno )

Doing a direct path load would simply be:

insert /*+ append */ into emp select * from external_table;

create external table from a flat text file

create or replace directory data_dir as 'c:\temp\'

create table external_table
(EMPNO NUMBER(4) ,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2)
)
ORGANIZATION EXTERNAL
( type oracle_loader
default directory data_dir
access parameters
( fields terminated by ',' )
location ('emp.dat')
)

Oracle: fetch across commit

2006-01-31T15:23:00.000-08:00

for x in ( select rowid rid, t.* from T ) loop
update T set x = x+1 where rowid = x.rid;
commit;
end loop;

That implicit cursor is fetched from "across a commit". It is the practice of
keeping a cursor open after committing. It is a bad practice and is a common
cause of ORA-1555 (the above looping construct in particular)

Oracle: Histogram-2

2006-01-30T18:01:00.000-08:00

Remember, analyzing for histograms is time-consuming, and histograms are used under two conditions:

*
Table join order – The CBO must know the size of the intermediate result sets (cardinality) to properly determine the correct join order the multi-table joins.

*
Table access method – The CBO needs to know about columns in SQL where clauses, where the column value is skewed such that a full-table scan might be faster than an index range scan. Oracle uses this skew information in conjunction with the clustering_factor columns of the dba_indexes view.

Hence, this is the proper order for using the dbms_stats package to locate proper columns for histograms:

1. Skewonly option - You want to use skewonly to do histograms for skewed columns, for cases where the value will make a difference between a full-table scan and an index scan.

2. Monitor - Next, turn-on monitoring. Issue an “alter table xx monitoring” and “alter index yyy monitoring” command for all segments in your schema. This will monitor workload against

3. Auto option - Once monitoring is in-place, you need to re-analyze with the "auto" option to create histograms for join columns within tables. This is critical for the CBO to determine the proper join order for finding the driving table in multi-table joins.

4. Repeat option - Finally, use the "repeat" option to re-analyze only the existing histograms.

Oracle: Histogram-1

2006-01-30T17:27:00.000-08:00

A new feature of the dbms_stats package is the ability to look for columns that should have histograms, and then automatically create the histograms. Oracle introduced some new method_opt parameter options for the dbms_stats package. These new options are auto, repeat and skewonly and are coded as follows:

method_opt=>'for all columns size auto'
method_opt=>'for all columns size repeat'
method_opt=>'for all columns size skewonly'

The auto option is used only when monitoring has been invoked via the alter table…monitoring command. Histograms are created based upon both the data distribution (see Figure A) and the workload on the column as determined by monitoring, like this.

execute dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'for all columns size auto',
degree => DBMS_STATS.DEFAULT_DEGREE);

Using the repeat option, histograms are collected only on the columns that already have histograms. Histograms are static, like any other CBO statistic, and need to be refreshed when column value distributions change. The repeat option would be used when refreshing statistics, as in this example:

execute dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'for all columns size repeat',
degree => DBMS_STATS.DEFAULT_DEGREE);

The skewonly option introduces a very time-consuming build process because it examines the data distribution of values for every column within every index. When the dbms_stats package finds an index whose column values are distributed unevenly, it creates histograms to help the CBO make a table access decision (i.e., index versus a full-table scan). From the earlier vehicle_type example, if an index has one column value (e.g., CAR) that exists in 65 percent of the rows, a full-table scan will be faster than an index scan to access those rows, as in this example:

execute dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'for all columns size skewonly',
degree => DBMS_STATS.DEFAULT_DEGREE);

Oracle: PL/SQL interval datatype

2006-01-30T12:25:00.000-08:00

Old fashion way:

declare
begin_2000 date := to_date('1-Jan-2000','dd-Mon-yyyy');
begin_2001 date := to_date('1-Jan-2001','dd-Mon-yyyy');
days_in_2000 number;
begin
days_in_2000 := begin_2001 - begin_2000;
dbms_output.put_line(days_in_2000);
end;

To advance a DATE to the next day, you add 1; to advance a DATE by 1 hour, you add 1/24; and so forth.

New interval datatype:

DECLARE
begin_2000 TIMESTAMP := TO_TIMESTAMP('1-Jan-2000','dd-Mon-yyyy');
begin_2001 TIMESTAMP := TO_TIMESTAMP('1-Jan-2001','dd-Mon-yyyy');
days_in_2000 INTERVAL DAY(3) TO SECOND;
BEGIN
days_in_2000 := begin_2001 Ð begin_2000;

END;
/

DECLARE
x TIMESTAMP WITH TIME ZONE;
y INTERVAL YEAR TO MONTH;
BEGIN

x := TIMESTAMP '2002-07-14 21:15:00 -07:00';
y := INTERVAL '0-1' YEAR TO MONTH;
x := x + y;
x := x + INTERVAL '1 00:00:00' DAY TO SECOND;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(
x, 'yyyy-mm-dd hh24:mi:ss tzh:tzm'));
END;
/

Oracle: Locking

2006-01-27T23:07:00.000-08:00

---------------- showsql.sql --------------------------
column status format a10
set feedback off
set serveroutput on

select username, sid, serial#, process, status
from v$session
where username is not null
/

column username format a20
column sql_text format a55 word_wrapped

set serveroutput on size 1000000
declare
x number;
begin
for x in
( select username||'('||sid||','||serial#||
') ospid = ' || process ||
' program = ' || program username,
to_char(LOGON_TIME,' Day HH24:MI') logon_time,
to_char(sysdate,' Day HH24:MI') current_time,
sql_address, LAST_CALL_ET
from v$session
where status = 'ACTIVE'
and rawtohex(sql_address) <> '00'
and username is not null order by last_call_et )
loop
for y in ( select max(decode(piece,0,sql_text,null)) ||
max(decode(piece,1,sql_text,null)) ||
max(decode(piece,2,sql_text,null)) ||
max(decode(piece,3,sql_text,null))
sql_text
from v$sqltext_with_newlines
where address = x.sql_address
and piece < 4)
loop
if ( y.sql_text not like '%listener.get_cmd%' and
y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%')
then
dbms_output.put_line( '--------------------' );
dbms_output.put_line( x.username );
dbms_output.put_line( x.logon_time || ' ' ||
x.current_time||
' last et = ' ||
x.LAST_CALL_ET);
dbms_output.put_line(
substr( y.sql_text, 1, 250 ) );
end if;
end loop;
end loop;
end;
/

column username format a15 word_wrapped
column module format a15 word_wrapped
column action format a15 word_wrapped
column client_info format a30 word_wrapped

select username||'('||sid||','||serial#||')' username,
module,
action,
client_info
from v$session
where module||action||client_info is not null;

Shell: Korn: rename file in subdirectory

2006-01-27T16:49:00.000-08:00

# rename directory first

for f in `find . -type d`; do
g=`echo $f | tr '[A-Z]' '[a-z]'`
mv "$f" "$g"
done

# then rename all files in subdirectory
for f in `find . ! -type d`; do
g=`echo $f | tr '[A-Z]' '[a-z]'`
mv "$f" "$g"
done

Oracle: Fine-grained audit

2006-01-26T16:14:00.000-08:00

http://www.oracle.com/technology/oramag/webcolumns/2003/techarticles/nanda_fga.html

begin
dbms_fga.add_policy (
object_schema=>'BANK',
object_name=>'ACCOUNTS',
policy_name=>'ACCOUNTS_ACCESS'
);
end;

select timestamp, db_user, os_user, object_schema, sql_text
from dba_fga_audit_trail;

begin
dbms_fga.add_policy (
object_schema=>'BANK',
object_name=>'ACCOUNTS',
policy_name=>'ACCOUNTS_ACCESS',
audit_column => 'BALANCE',
audit_condition => 'BALANCE >= 11000'
);
end;

FGA requires cost-based optimization(CBO) in order to work correctly

begin
dbms_fga.drop_policy (
object_schema => 'BANK',
object_name => 'ACCOUNTS',
policy_name => 'ACCOUNTS_ACCESS'
);
end;

begin
dbms_fga.enable_policy (
object_schema => 'BANK',
object_name => 'ACCOUNTS',
policy_name => 'ACCOUNTS_ACCESS',
enable => FALSE
);
end;

Oracle: Wait Events

2006-01-24T18:17:00.000-08:00

1. DB File Scattered Read
- This generally indicates waits related to full table scans. As full table scans
are pulled into memory, they rarely fll into contiguous buffers but instead are
scattered throughout the buffer cache.
- A large number here indicates that your table may have missing or suppressed index
check to ensure that full table scan are necessary when you see these waits.
try to cache small tables to avoid reading them in over and over again, since
full table scan is put at cold end of LRU list.

2. DB File Sequential Read
- This event generally indicates a single block read(an index read, for example).
A large number of waits here could indicate poor joining orders of tables or
unselective indexing.
- You should correlate this wait statistic with other known issues within the
statspack report, such as inefficient SQL. check to ensure that index scans are
necessary and join orders for multiple table joins.

3. Free Buffer
- This indicates your system is waiting for a buffer in memory because none is
available. Waits in this category may indicate that you need to increase the
DB_BUFFER_CACHE. It could also indicate that unselective SQL is causing data
to flood the buffer cache with index blocks.
- This normally indicate that there's a substantial amount of DML being done and
that the DBWR is not writing quickly enough.
To address this, you may want to consider accelerating incremental checkpointing
using more DBWR processes or increasing the number of physical disks.

4. Buffer Busy

5. Latch Free

6. Enqueue
- An enqueue is a lock that protects a shared resource.

7. Log Buffer space

Oracle: Rman: Duplicate database on remote site

2006-01-24T15:32:00.000-08:00

1) create a password file for duplicate instance(since you will connect as sysdba)
orapwd file=/u01/app/oracle/product/9.2.0.1.0/dbs/orapwDUP password=password entries=10

2) edit listener.ora and tnsnames.ora to make sure dup instance can be accessed from network.

3) create pfile with minimum requirement.
# Minimum Requirement.
DB_NAME=DUP
CONTROL_FILES=(/u02/oradata/DUP/control01.ctl,
/u02/oradata/DUP/control02.ctl,
/u02/oradata/DUP/control03.ctl)

# Convert file names to allow for different directory structure.
DB_FILE_NAME_CONVERT=(/u02/oradata/TSH1/,/u02/oradata/DUP/)
LOG_FILE_NAME_CONVERT=(/u01/oradata/TSH1/,/u01/oradata/DUP/)

# make sure block_size and compatible parameters
# match if you are not using the default.
DB_BLOCK_SIZE=8192
COMPATIBLE=9.2.0.0.0

4) create spfile from pfile and start instance
sqlplus /nolog
conn / as sysdba
CREATE SPFILE FROM PFILE='/u01/app/oracle/admin/DUP/pfile/init.ora';
STARTUP FORCE NOMOUNT;

5) Backup original database
rman target / catalog rman/cat@oem
rman> backup database
rman> backup archivelog all delete input

6) duplicate database
man TARGET sys/password@tsh1 CATALOG rman/rman@tshadm AUXILIARY /
DUPLICATE TARGET DATABASE TO DUP;
or
# Duplicate database to TARGET's state 4 days ago.
DUPLICATE TARGET DATABASE TO DUP UNTIL TIME 'SYSDATE-4'

Oracle: Data Guard

2006-01-21T14:49:00.000-08:00

http://www.lc.leidenuniv.nl/awcourse/oracle/server.920/a96653/standbycon.htm

Oracle: create package

2006-01-20T15:29:00.000-08:00

CREATE or REPLACE PACKAGE test_actions AS
PROCEDURE add_entry(c1 number, c2 varchar2);
PROCEDURE del_entry(c1 number);
END test_actions;

CREATE or REPLACE PACKAGE BODY test_actions AS
PROCEDURE add_entry(c1 number, c2 varchar2);
BEGIN
INSERT INTO test2(c1,c2) VALUES(c1,c2);
END add_entry;
PROCEDURE del_entry(c1 number)
BEGIN
DELETE FROM test2 WHERE c1 = c1;
END del_entry;
END test_actions;

Oracle: bind variables and ksh

2006-01-14T15:40:00.000-08:00

#!/bin/ksh

function f
{
sqlplus -s < login/passwd@db
var v1 varchar2(10);
var v2 varchar2(10);
exec :v1 := '$1';
exec :v2 := '$2';
exec pkg.prc(:v1, :v2);
EOF
}

f A B

Unix: Search text in subdirectories

2006-01-12T11:35:00.000-08:00

find . -print -name *.sh |xargs grep ECProductUpdate

Oracle: perf views

2006-01-06T14:27:00.000-08:00

1. v$session_wait : current state view. It lists either the event currently
being waited for or the event last waited for on each session

2. v$session_event: lists the cumulative history of events waited for on each
session. After a session exits, the wait event statistics for that session are
removed from this view.

3. v$system_event: lists the events and times waited for by the whole instance
(that is, all session wait events data rolled up) since instance startup.

v$session_wait is current state view. it also contains a finer-granularity of
information than v$session_event or v$system_event.
it includes additional identifying data for the current event in three parameter
columns: P1, P2 and P3.

Unix: Rsync password authentication

2005-12-29T21:33:00.000-08:00

1) create configre file /usr/local/etc/rsyncd.conf
chmod 640 rsyncd.conf

uid = rsync
gid = rsync
use chroot = no
max connections = 4
syslog facility = local5
pid file = /var/run/rsyncd.pid

[www]
path = /usr/local/websites/
comment = all of the websites

2) create user/group as rsync/rsync

3) run rsyncd as "rsync --daemon"

4) create /usr/local/etc/rsyncd.conf (use mode 640)
auth users = tridge, susan
secrets file = /usr/local/etc/rsyncd.secrets

5) create /usr/local/etc/rsyncd.secrets (chmod 640)
tridge:mypass
susan:herpass

6) rsync -avz susan@ducky::www /home/dan/test

7) add option "--password-file /home/dan/test/rsync.password"
add password in /home/dan/test/rsync.password

UNIX: context switch

2005-12-29T16:27:00.000-08:00

Each process has its own virtual memory space. References to real memory are provided through a process-specific set of address translation maps. The computer's Memory Management Unit (MMU) contains a set of registers that point to the current process's address translation maps. When the current process changes, the MMU must load the translation maps for the new process. This is called a "Context Switch"

Oracle: transformation between v$lock and v$session_wait

2005-12-27T17:18:00.000-08:00

enqueue
-- P1: lock type(or name) and mode
-- P2: resource identifier ID1 for the lock
-- P3: resource identifier ID2 for the lock

v$lock.id1 = p2
v$lock.id2 = p3

--lock type:
v$lock.type = chr(bitand(p1,-16777216)/16777215)||chr(bitand(p1,1671680)/65535)

--the mode in which the enqueue is being requested
v$lock.request = mod(p1,65536)

--find locks and lock holders
to show only holders and waiters for locks being waited on
select decode(request,0,'Holder: ','Waiter: ')||sid sess,
id1,
id2,
lmode,
request,
type
from v$lock
where (id1, id2, type) in
(select id1, id2, type from v$lock where request > 0)
order by id1, request;

Oracle: i/o wait events

2005-12-27T17:04:00.000-08:00

1) db file sequential read:
single block read into one SGA buffer
single block I/Os are usually the result of using indexes
rarely, full table scan calls could get truncated to a single block call due to
extent boundaries, or buffers already present into the buffer cache.

2) db file scattered read:
multiblock read into many discontinuous SGA buffers
scattered read wait event identified that a full table scan is occurring.

3) direct read:
single or multiblock read into the PGA, bypassing the SGA

Oracle: Wait events

2005-12-22T17:52:00.000-08:00

-- query following views for wait events
V$SESSION_WAIT V$SESSION_EVENT V$SYSTEM_EVENT

-- V$SESSION_WAIT is a current state view.
it lists either the event currently being waited for or the event last waited
for on each session
-- V$SESSION_EVENT lists the cumulative history of events waited for on each session
After a session exists, the wait event statics for that session are removed from
this view.
-- V$SYSTEM_EVENT lists the events and times waited for by the whole instance
since instance startup

-- timed statistics are automatically collected for database
if the initialization parameter statistics_level is set to typical or all
if STATISTICS is set to BASIC, then you must set TIMED_STATISTICS to TRUE to enable
collection of timed statistics

Linux: Tune disk device

2005-12-21T17:19:00.000-08:00

-query current device settings
/sbin/hdparm /dev/hda

-run benchmark before tuning
/dbin/hdparm -Tt /dev/hda

Sybase: SQL text auditing

2005-12-21T15:13:00.000-08:00

exec sp_audit cmdtext,wlsuser,"all","on"
exec sp_audit cmdtext,wlsuser,"all","off"

Solaris: How to limit display of other user's processes

2005-12-20T17:53:00.000-08:00

On Solaris10, it is possible to allow user only see their own processes by command
ps -aef

Following is how to configure it.
1)to set limit globally edit /etc/security/policy.conf file and uncomment PRIV_DEFAULT line
set its value as
PRIV_DEFAULT=basic,!proc_info

2)to set limit per user basis
edit /etc/user_attr and add following entry
gmb::::lock_after_retries=no;defaultpriv=basic

Oracle: PL/SQL table and cursor

2005-12-20T15:42:00.000-08:00

set serveroutput on

declare
-- Declare the PL/SQL table
type deptarr is table of dept%rowtype
index by binary_integer;
d_arr deptarr;

-- Declare cursor
type d_cur is ref cursor return dept%rowtype;
c1 d_cur;

i number := 1;
begin
-- Populate the PL/SQL table from the cursor
open c1 for select * from dept;
loop
exit when c1%NOTFOUND;
fetch c1 into d_arr(i);
i := i+1;
end loop;
close c1;

-- Display the entire PL/SQL table on screen
for i in 1..d_arr.last loop
dbms_output.put_line('DEPTNO : '||d_arr(i).deptno );
dbms_output.put_line('DNAME : '||d_arr(i).dname );
dbms_output.put_line('LOC : '||d_arr(i).loc );
dbms_output.put_line('---------------------------');
end loop;
end;
/

Oracle: 9i flashback query

2005-12-19T13:58:00.000-08:00

Flashback Query is enabled and disabled using the DBMS_FLASHBACK package. The point in time of the flashback can be specified using the SCN or the actual time:

EXECUTE Dbms_Flashback.Enable_At_System_Change_Number(123);
EXECUTE Dbms_Flashback.Enable_At_Time('28-AUG-01 11:00:00');

Once you've finished performing all your read-only operations you can turn off flashback query using:

EXECUTE Dbms_Flashback.Disable;

to get current system change number

select dbms_flashback.get_system_change_number from dual;

Rsync: certification authentication on unix

2005-12-13T11:10:00.000-08:00

1) use rsync over ssh to copy files between systems
rsync -av -e ssh src:/dir/* dest:/dir

2) use rsync over ssh and use public key for user authentication
(login from host A to host B without password authentication)
1) on host A generate private/public key pair
ssh-keygen -t rsa -b 2048 -f ~/.ssh_key/A-rsync-key
2) copy A-rsync-key.pub file to host B:~/.ssh/authorized_keys file
(use sshd_config to determine the location of this file)
3) create "config" file in A:~/.ssh as
Host B
User root
Compression yes
Protocol 2
RSAAuthentication yes
StrictHostKeyChecking no
ForwardAgent yes
ForwardX11 no
IdentityFile /.ssh_key/A-rsync-key

Apache: configure SSI - server side includes

2005-12-13T10:55:00.000-08:00

1) add following lines to httpd.conf
AddType text/html .shtml
AddHandler server-parsed .shtml

2) modify options line in Document root to add Includes
Options Indexes FollowSymLinks +Includes

3) optionally turn on XBitHack
XBitHack On
and chmod 744 filename

4) alternatively you can create .htaccess file under a given directory and add
AddType text/html .shtml
AddHandler server-parsed .shtml
Options Indexes FollowSymLinks Includes

Perl: Serach Matching

2005-12-09T20:16:00.000-08:00

.* : match anything not a newline till end of line

Oracle: trace event

2005-12-07T11:33:00.000-08:00

init.ora: event='1401 trace name errorstack, level 12'

user session: alter session set events '10046 trace name context forever, level 4';

* 10046 trace name context forever, level 4

Trace SQL statements and show bind variables in trace output.

* 10046 trace name context forever, level 8

This shows wait events in the SQL trace files

* 10046 trace name context forever, level 12

This shows both bind variable names and wait events in the SQL trace files

* 1401 trace name errorstack, level 12
1401 trace name errorstack, level 4
1401 trace name processstate

Dumps out trace information if an ORA-1401 "inserted value too large for column" error
occurs. The 1401 can be replaced by any other Oracle Server error code that you want
to trace.

* 60 trace name errorstack level 10

Show where in the code Oracle gets a deadlock (ORA-60), and may help to diagnose
the problem.

============================================================
SQL> oradebug setmypid
SQL> oradebug unlimit
SQL> oradebug dump systemstate 10
SQL> oradebug tracefile_name
or to trace a specific error
SQL> select username, spid from v$process;
SQL> oradebug setospid 2280
SQL> oradebug event 10046 trace name context off

Oracle: lock - enqueue

2005-12-07T11:21:00.000-08:00

A latch is an internal Oracle mechanism used to protect data structures in the SGA from simultaneous access. Atomic hardware instructions like TEST-AND-SET are used to implement latches. Latches are more restrictive than locks in that they are always exclusive. Latches are never queued, but will spin or sleep until they obtain a resource, or time out.

Enqueues and locks are different names for the same thing. Both support queuing and concurrency. They are queued and serviced in a first-in-first-out (FIFO) order.

Semaphores are an operating system facility used to control waiting. Semaphores are controlled by the following Unix parameters: semmni, semmns and semmsl. Typical settings are:

semmns = sum of the "processes" parameter for each instance
(see init.ora for each instance)
semmni = number of instances running simultaneously;
semmsl = semmns

Oracle: v$sql_plan view

2005-12-06T17:19:00.000-08:00

SQL Execution Statistics in 9i

The SQL tuning process prior to 9.2 involved executing SQL commands, then OS commands, and then SQL commands again. This is a very time-consuming and burdensome process. In 9.2, Oracle decided to retain the SQL metrics for each statement in the SGA (library cache) while the statement remains cached. The DBA could then diagnose SQL issues at a SQL prompt and leave tkprof alone. This is a vast improvement over prior versions.

Oracle9.2 contains the following views that enable the DBA to identify SQL issues directly from a SQL prompt. These views should be used to periodically check SQL statistics and full-table scans, alerting the DBA to problem areas requiring corrective action.

• v$sql_plan - This view shows the same information as shown by Explain Plan except it is the actual execution plan and not the predicted one – just like tkprof and even better than Explain Plan.

• v$sql_plan_statistics - This view contains the execution statistics for each operation (step) in the v$sql_plan. Queries should access this view and look for poor SQL operations including TABLE ACCESS FULL – full-table scans.

• v$sql_plan_statistics_all - This view combines data from v$sql_plan, v$sql_plan_statistics and v$sql_workarea.

Both v$sql_plan_statistics and v$sql_plan_statistics_all are not populated by default. The option statistics_level=all must be set.

to query v$sql_plan
1) retrieve "address" and "hash_value" from v$sql
2) query v$sql_plan with columns "operation","options","object_name" and "cost"
using "address" and "hash_value" as primary key.

Perl: Find the Nth Occurrence of a match

2005-12-04T21:39:00.000-08:00

while(/(\w+)\s+fish\b/gi){
if(++$count == $WANT){
# do what ever you want to do
}
}

\w+ match a word
\s+ 1 or n space
\b word boundary
/g gloal match/substitue as often as possible.
/i ignore case

Perl: match numbers

2005-12-04T17:05:00.000-08:00

to match one or more numbers in a string

$str ~= /(\d+)/

Perl: difference between + and *

2005-12-04T16:52:00.000-08:00

+ means 1 or more occurences

* means 0 or more occurences

for examples:

$str = "good food";
($str1 = $str) =~ s/o*/e;
($str2 = $str) =~ s/o+/e;

result is:
$str1: egood food
$str2: ged food

Perl: transaction across databases

2005-12-02T11:37:00.000-08:00

How to create transaction across multiple databases in perl script.

#!/usr/local/bin/perl -w

#
# this script will test transaction rollback
#

use DBD::Sybase;

$dbh_ora = DBI->connect("dbi:Oracle:host=192.168.69.11;sid=qadtrain",
'qad',
'qad',
{AutoCommit=>0}
)|| die "Database connection not made: $DBI::errstr";

$dbh_syb = DBI->connect("dbi:Sybase:server=DEVELOP;database=CIS",
"sa",
"october"
)|| die "Database connection not made: $DBI::errstr";

$dbh_syb->{AutoCommit}=0;
$dbh_syb->{RaiseError}=1;

$dbh_ora->{AutoCommit}=0;
$dbh_syb->{RaiseError}=1;

eval{
my $v1=4;
$sth_ora=$dbh_ora->prepare("insert into peij1 values(?,?)");
$sth_ora->execute($v1,$v1);
$sth_syb=$dbh_syb->prepare("insert into peij1 values(?,?)");
$sth_syb->execute($v1,$v1);
$dbh_ora->commit;
$dbh_syb->commit;
};
if($@){
warn "Transaction aborted due to $@";
$dbh_ora->rollback;
$dbh_syb->rollback;
}

Quick Sort

2005-11-28T15:44:00.000-08:00

Pick an element, called a pivot, from the list.
Reorder the list so that all elements which are less than the pivot come before the pivot and so that all elements greater than the pivot come after it (equal values can go either way). After this partitioning, the pivot is in its final position. This is called the partition operation
Recursively sort the sub-list of lesser elements and the sub-list of greater elements.

Pseudo Code:
function quicksort(q)
var list less, pivotList, greater
if length(q) = 1
return q
else
select a pivot value pivot from q
for each x in q except the pivot element
if x <>

if x = pivot then add x to greater

add pivot to pivotList

return concatenate(quicksort(less), pivotList, quicksort(greater))

TYPE int_arr IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;

procedure qsort(arr in out int_arr, lo in INTEGER, hi in INTEGER ) is
i INTEGER := lo;
j INTEGER := hi;
x INTEGER := arr((lo+hi)/2);
tmp INTEGER;
begin
LOOP
WHILE (arr(i) <> x LOOP
j := j - 1;
END LOOP;
IF i <= j THEN tmp := arr(i); arr(i) := arr(j); arr(j) := tmp; i := i + 1; j := j - 1; END IF; EXIT WHEN i > j;
END LOOP;
IF lo <>

Oracle: Choosing Index Keys

2005-11-21T16:14:00.000-08:00

Guidelines for choosing keys to index:

Consider indexing keys that are used frequently in WHERE clauses.
Frequently to join tables in SQL statements.
have high selectivity(percentage of rows in a table that have the same value for indexed key). Indexing low selectivity columns can be helpful if the data distribution is skewed so that one or two values occur much less often than other values.
Do not use b-tree indexes n keys with few distinct vlues.
Do not index columns that are modified frequently
Do not index keys that appear only in WHERE clauses with functions or operators except with function based indexes.
Consider indexing foreign keys of referential intergrity constraints in cases in which a large number of I/U/D access the parent and child tables. this will avoid "share locking" on child table.
when choosing to index a key, you should balance the perf gain for queries and the perf loss for I/U/Ds by using SQL trace facility.

Oracle: Flashback Query in 10g

2005-11-19T17:44:00.000-08:00

select versions_starttime, versions_endtime, versions_xid,
versions_operation, rate
from rates versions between timestamp minvalue and maxvalue
order by VERSIONS_STARTTIME

select rate, versions_starttime, versions_endtime
from rates versions
between timestamp
to_date('12/1/2003 15:57:54','mm/dd/yyyy hh24:mi:ss')
and to_date('12/1/2003 16:57:55','mm/dd/yyyy hh24:mi:ss')

select rate, versions_starttime, versions_endtime
from rates versions
between scn 1000 and 1001

select versions_starttime, versions_endtime, versions_xid,
versions_operation, rate
from rates versions between timestamp
to_date('12/11/2003 15:57:52', 'mm/dd/yyyy hh24:mi:ss')
and maxvalue
order by VERSIONS_STARTTIME

SELECT UNDO_SQL
FROM FLASHBACK_TRANSACTION_QUERY
WHERE XID = '000A000D00000029';

Oracle: Links

2005-11-14T20:28:00.000-08:00

http://www.cuddletech.com/articles/oracle/node1.html

Oracle: Flashback

2005-11-14T09:45:00.000-08:00

-- flashback doesn't work on system tablespace.( don't login as sys/system)

- startup mount

- alter database archivelog;

- alter system set DB_FLASHBACK_RETENTION_TARGET=1440

- alter system set DB_RECOVER_FILE_SIZE=512000000

- alter database flashback on;

- alter database open

- flashback table "TEST" to before drop;

-- two important things: SCN and 'Recycle Bin'
show recyclebin;

Oracle: Using Loop and Random

2005-11-10T15:22:00.000-08:00

Create table test_normal (empno number(10), ename varchar2(30), sal number(10));

Begin
For i in 1..1000000
Loop
Insert into test_normal
values(i, dbms_random.string('U',30), dbms_random.value(1000,7000));
If mod(i, 10000) = 0 then
Commit;
End if;
End loop;
End;/

Oracle: Histograms

2005-11-04T16:05:00.000-08:00

When to use histograms:
1. When the columns are used frequently in where clauses.
2. it's hightly skewed data distribution.

Creating Histograms:
- execute DBMS_STATS.GATHER_TABLE_STATS
('scott','emp', METHOD_OPT => 'FOR COLUMNS SIZE 10 sal');
( create a 10 bucket histograms on sal column of emp table)

The default number of buckets is 75

Oracle: Generation statistics

2005-11-04T14:47:00.000-08:00

Oracle use following techniques to generates statistics:
- Estimation based on random data sampling
- Exact computation
- User-defined statistics collection methods

To collection statistics on schema
- execute DBMS_STATS.GATHER_SCHEMA_STATS('OE',DBMS_STATS.AUTO_SAMPLE_SIZE);

You can use DBMS_STATS to gather, modify, view, export, import and delete statistics.

DBMS_STATS Package procedures:
- GATHER_INDEX_STATS : Index statistics
- GATHER_TABLE_STATS : Table, Columns and index statistics
- GATHER_SCHEMA_STATS : Statistics for all objects in a schema
- GATHER_DATABASE_STATS : Statistics for all objects in a database
- GATHER_SYSTEM_STATS : CPU and I/O statistics for the system

Gathering statistics on table/col/index will invalidate parsed SQL statements.
Gathering system statistics will not invalidate parsed SQL.

- Gather statistics. Gathering ends after 720 mins and is stored in mystats table
** day time **
BEGIN
DBMS_STATS.GATHER_SYSTEM_STATS(
gathering_mode => 'interval',
interval => 720,
stattab = 'mystats',
statid => 'OLTP');
end;
/

** night time **
BEGIN
DBMS_STATS.GATHER_SYSTEM_STATS(
gathering_mode => 'interval',
interval => 720,
stattab = 'mystats',
statid => 'OLAP');
end;
/

- to import OLAP statistics for nighttime

BEGIN
DBMS_JOB.SUBMIT(:jobno,
'DBMS_STATIS.IMPORT_SYSTEM_STATS(''mysstats'',''OLAP'');'
SYSDATE +0.5, 'SYSDATE + 1');
COMMIT;
END;
/

Oracle: Statistics

2005-11-04T14:35:00.000-08:00

- Statistics is the data distribution and storage characteristics of tables, columns, indexes and partitions.
- Selectctivity is the fraction of rows in a table that sql statement's predicate chooses.

- Statistics generated include the following:
- Table statistics
- Number of rows
- Number of blocks
- Average row length
- Column statistics
- Number of distinct values in column
- Number of nulls in column
- Data distribution (histogram)
- Index statistics
- Number of leaf blocks
- Levels
- Clustering factor
- System statistics
- I/O performance and utilization
- CPU performance and utilization

Oracle: setup sga_aggregate_targate

2005-11-04T10:44:00.000-08:00

- in oracle9i, it intruduced a new feature sga_aggreate_targate/workarea_size_policy

- sga_aggregate_targate will define how much memory will be allocated to sga for all sessions. if there're more user session, each session will be allocated less memory. if there're less user sessions, each session will be allocated more memory.

- this eliminate tunning on hash_area_size, sort_area_size and so on.

- how to set :
set workarea_size_policy to auto
set sga_aggregate_targate to 16% of physical memory on oltp and 40% of memory on dss system.

- how measure the performance
select * from v$pgastat
over allocation count should be 0
cache hit percent should be 100
select name,value from v$sysstat where name like 'workarea exe%'
one pass and multi pass should be zero

- advisor feature in 9iR2
v$pga_target_advice

- how to query v$pga_target_advice
to eliminate over_allocate:
select round(pga_target_for_estimate/1024/1024) as target_size_MB,
bytes_processed,estd_extra_bytes_rw as est_rw_extra_bytes,
estd_pga_cache_hit_percentage as est_hit_pct,
estd_overalloc_count as est_overalloc
from v$pga_target_advice;

to eliminate extra reads/writes
select round(pga_target_for_estimate/1024/1024) as target_size_MB,
bytes_processed,estd_extra_bytes_rw as est_rw_extra_bytes,
estd_pga_cache_hit_percentage as est_hit_pct,
estd_overalloc_count as est_overalloc
from v$pga_target_advice;

Oracle: what happened during hot backup

2005-11-03T17:48:00.000-08:00

Oracle checkpoints the tablespace, flushing all changes from shared memory to disk.
The SCN markers for each datafile in that tablespace are "frozen" at their current values. Even though further updates will be sent to the datafiles, the SCN markers will not be updated until the tablespace is taken out of backup mode.
Oracle switches to logging full images of changed database blocks to the redologs. Instead of recording how it changed a particular block (the change vector), it will log the entire image of the block after the change. This is why the redologs grow at a much faster rate while hot backups are going on.

TCPWrapper: logging

2005-11-03T17:17:00.000-08:00

in hosts.deny add below entry to enable tcpwrapper logging to send email to admin

ALL:All: spawn (echo '%h-%d' | mailx -s'%h-%d rejected on fcasun2' -r jamesp@synnex.com jamesp@synnex.com) &

Perl: automate sftp using perl or expect

2005-11-03T15:02:00.000-08:00

#!/usr/bin/perl -w

use Net::SFTP;
use strict;

my $host = "host.ssh.com";
my %args = (
user => 'your_user_name,
password => 'your_password',
debug => 'true'
);

my $sftp = Net::SFTP->new($host, %args);
$sftp->get("/home/user/something.txt", "/home/user/hey.txt");
$sftp->put("bar", "baz");

#!/usr/local/bin/expect

spawn sftp -b cmdFile user@yourserver.com
expect "password:"
send "shhh!\n";
interact

This will spawn sftp in batch mode and pass in the password shhh! to the program. SFTP will than execute all the commads in cmdFile

cmdFile
lcd /home/ftp/test
cd /home/ftp/somedir
mput *.dat

lcd /home/recieving
cd /home/someotherdir
mget *.dat

Enable tcpwrapper on solaris10

2005-11-03T10:51:00.000-08:00

- on solaris 10, ssh, sendmail and rcpbind was tcpwrapper enabled by default
if there's no hosts.allow/deny files, it will grant access by default to all

-To enable TCP Wrappers support for inetd-based services, you can simply use the following commands:

# inetadm -M tcp_wrappers=true
# svcadm refresh inetd

# inetadm -l telnet | grep tcp_wrappers

- you can also use svccfg to enable inetd based services.
# svccfg -s inetd setprop defaults/tcp_wrappers=true
# svcadm refresh inetd

Korn Shell Global funcations

2005-11-02T17:38:00.000-08:00

- create file get_time in directory ~/kfunctions
function get_time{
TIME=$(date '+%m/%d/%y')
printf "$TIME\n"
}

- create .kshrc file
export FPATH=/export/home/jamesp/kfunctions
autoload get_time

- add entry to .profile
ENV=~.kshrc

- use global functions
#!/bin/ksh
SYSTIME=$(get_time)
echo "$SYSTIME\n"

Perl: creating modules

2005-11-02T16:37:00.000-08:00

- Perl is distributed with a program called h2xs. it will be used to generate skeletion files for a new module.

- h2xs -AXc -n NewModule

- edit NewModuel.pm

- run perl Makefile.PL
make
make install

- use NewModule
NewModule->functions

The Changes file is where you might keep track keep track of changes you make to your module as you write new versions

MANIFEST contains a list of files in this directory. If you add new files to the directory, you should also add them to the MANIFEST. The MANIFEST is used to create a tarball of your module for distribution, and it's also checked when people unpack the tarball and install the module

Makefile.PL is a Perl program used to create a Unix Makefile. You'll use this Makefile to test and install your module.

NewModule.pm is your module. You'll write the code here in the next step.

Perl DBI and Loop

2005-11-01T17:06:00.000-08:00

DBI and loops
DBI statements are often used within loops that store or fetch data from a database. You might insert the contents of a text file into a database line by line, or read and parse multiple lines of output from a database query. I often see code like the following from new DBI programmers:

foreach $line () {
chomp $line;
($alpha, $beta, $charlie, $delta) = split(/,/,$line);
$sql = qq{
insert into $table (col_a, col_b, col_c, col_d)
values($alpha,$beta,$charlie,$delta)
};
$dbh = DBI->connect($dsn,'login','password');
$sth = $dbh->prepare($sql);
$sth->execute;
$dbh->disconnect;
}
This code works, but not very efficiently. With the above code it took an hour to insert 200,000 rows into a MySQL database -- and MySQL is pretty fast! It might take half a day with Oracle.
There are lots of improvements we can make. The first is just common sense: Never put anything in a loop that doesn't absolutely have to be there. For each of our 200,000 insertions, we connect to the database, prepare our SQL statement, execute it, and disconnect from the database. However, we only need to connect and disconnect once. We simply have to move our connect() and disconnect() outside the loop to drastically improve our performance: $dbh = DBI->connect($dsn,'login','password');
foreach $line () {
chomp $line;
($alpha, $beta, $charlie, $delta) = split(/,/,$line);
$sql = qq{
insert into $table (col_a, col_b, col_c, col_d)
values($alpha,$beta,$charlie,$delta)
};
$sth = $dbh->prepare($sql);
$sth->execute;
}
$dbh->disconnect;
This reduces our run time to half an hour, doubling our speed.
Placeholders
DBI allows us to further improve our code by using placeholders. We now connect and disconnect the database only once, but we still prepare and execute our SQL statement 200,000 times. The statement we prepare doesn't change much; all that changes is the insertion value. We can create a generic statement that we prepare only once, outside the loop. With this generic statement we can specify the insertion values at execution time. DBI uses a ? to define a placeholder, so our new SQL statement looks like this: $sql = qq{
insert into $table (col_a, col_b, col_c, col_d)
values(?, ?, ?, ?)
};
The statement is prepared the same as before: $sth = $dbh->prepare($sql);
Now we can fill in each ? at execution time, once we fill in the ? values. There are two ways to do this. First, we can use the bind_param() subroutine to indicate what values to use: $sql = qq{
insert into $table (col_a, col_b, col_c, col_d)
values(?, ?, ?, ?)
};
$sth = $dbh->prepare($sql);
foreach $line () {
chomp $line;
($alpha, $beta, $charlie, $delta) = split(/,/,$line);
$sth->bind_param(1, $alpha);
$sth->bind_param(2, $beta);
$sth->bind_param(3, $charlie);
$sth->bind_param(4, $delta);
$sth->execute;
}
The bind_param() subroutine allows a data type as a third argument. Normally, this is used to tell the driver whether the placeholder is a number or a string: $sth->bind_param(1, $alpha, SQL_INTEGER);
or $sth->bind_param(2, $beta, SQL_VARCHAR);
The second way to specify values at execution time is to pass them directly as arguments to the execute statement: $sth->execute($alpha, $beta, $charlie, $delta);
Using this method our code simplifies to: $dbh = DBI->connect($dsn,'login','password');
$sql = qq{
insert into $table (col_a, col_b, col_c, col_d)
values(?, ?, ?, ?)
}
$sth = $dbh->prepare($sql);
foreach $line () {
chomp $line;
($alpha, $beta, $charlie, $delta) = split(/,/,$line);
$sth->execute($alpha, $beta, $charlie, $delta);
}
$dbh->disconnect;
Our code now takes twenty minutes to run. We achieved this improvement despite the fact that the MySQL database driver only emulates placeholders. Had this been a database that directly supports placeholders, such as Oracle, the improvement would have been even more dramatic. Placeholders are any easy way to improve your DBI coding, but there are a few gotchas that beginners need to avoid. First, notice that placeholders don't have quotes. This is correct: $sql = qq{
insert into $table (col_a, col_b, col_c, col_d)
values(?, ?, ?, ?)
};
This is not correct: $sql = qq{
insert into $table (col_a, col_b, col_c, col_d)
values('?', '?', '?', '?')
};
Placeholders cannot be used for column names or table names. This won't work: $sql = qq{
select alpha, ? from table
};
Nor will this: $sql = qq{
select alpha, beta from ?
};
Or even this: $sql = qq{
select alpha, beta from table where ? > 200
};
However, you can use Perl variables for column and table names: $sql = qq{
select alpha, beta from $table where $col > 200
};
You'll have to re-prepare this statement whenever you want to change the values of $table or $col.
Fetches
We've dealt mostly with inserting data into a database, so now we'll look at the ways DBI provides to get data out. Most of those ways follow the same basic format: $dbh = DBI->connect($dsn, 'login', 'passwd');
$sql = qq{
select * from $table
};
$sth = $dbh->prepare($sql);
$sth->execute;
while (@row = $sth->fetchrow_array) {
print "@row\n";
}
$dbh->disconnect;
The most efficient of these is fetchrow_arrayref():
while ($row = $sth->fetchrow_arrayref) {
print "@$row\n";
}
There's also fetchrow_hashref(), which stores the keys and values of the database into a hash: while ($ref = $sth->fetchrow_hashref) {
foreach $key (keys %{$ref}) {
print "$ref->{$key}, ";
}
print "\n";
}
Bind Columns
Database fetches can be made even more efficient and elegant with the use of bind columns. Bind columns allow you to bind a variable to each column of a table, so that whenever a fetch is performed the bound variables are automatically updated: $sth->bind_columns(\%attr,@references_to_variables_to_bind);
We can use it like this: $sql = qq{
select alpha, beta, delta from table
};
$sth->prepare($sql);
$sth->execute;
my ($alpha, $beta, $delta);
$sth->bind_columns(undef, \$alpha, \$beta, \$delta);
while ($sth->fetch) {
print "$alpha - $beta - $delta\n";
}
Here, the fetch() subroutine is synonymous with fetchrow_arrayref(). This subroutine, combined with bind_columns(), is the fastest way to fetch data with DBI.
Error Checking
There's a big problem with all of code snippets you've seen in this article: We never check the return values of DBI methods. We simply assume that every DBI call was successful. The obvious solution is to add an or die clause after every DBI method call: $dbh = DBI->connect($dsn,'login','passwd')
die $DBI::errstr;
$sth = $dbh->prepare($sql) die $dbh->errstr;
$sth->execute die $sth->errstr;
However, there is a cleaner solution. Each DBI handle has a boolean attribute called RaiseError. If we set this to true, any errors triggered by that handle cause the script to die with an appropriate error message, saving us from having to check every DBI call we make. RaiseError can be set via the connect statement: $dbh = DBI->connect($dsn,'login','passwd',
{ RaiseError => 1 });
We can also call it after a connection is already made: $dbh->{RaiseError} = 1;
Transactions
I've found that many administrators shy away from using transactions with DBI. For those unfamiliar, transactions provide a way to group multiple SQL statements together so that database changes are only made if every statement is successful. For example, a transaction for updating a customer order database might include statements to update inventory, update accounts payable, and update shipping manifests. All updates must succeed; otherwise, we don't want to perform any of them. If only some of the updates succeeded, we might bill a customer for an order not shipped, or ship an order to a customer without billing them.
Many administrators shy away from using DBI transactions.
By default, DBI commits each statement as it's performed. To use transactions, we need to disable this behavior with the AutoCommit variable: $dbh = DBI->connect($dsn,'login','passwd', {
RaiseError => 1,
AutoCommit => 0,
});
We can also just say: $dbh->{AutoCommit} = 0;
With AutoCommit off, changes are revocable until commit() is called. Uncommitted statements can be undone with rollback().
Next, we need a way to group our statements that lets us test whether any failed. The easiest and most robust way to do this is to set RaiseError and wrap the statements in an eval block: $dbh->{RaiseError} = 1;
eval {
$sth_1->execute; # Update Inventory
$sth_2->execute; # Update Accounts Payable
$sth_3->execute; # Update Shipping
};
if ($@)
$dbh->rollback;
} else {
$dbh->commit;
}
If any statement in the eval block fails, $@ is set and we revoke all three statements.
Don't forget to commit your statements. If you do forget, what happens next depends on the database. Some databases automatically commit them; others roll them back.
References
The material for this article came from personal experience, a thousand or so readings of the DBI documentation and FAQ, and a generous review by DBI guru Tim Bunce. You can find the DBI module and its related documentation at http://www.symbolstone.org/technology/perl/ DBI.
__END__

Oracle Optimizer Operations

2005-10-31T18:16:00.000-08:00

Evaluation of expressions and conditions
Statement transformation
Choice of optimizer approaches
Choice of access paths
Choice of join orders
Choice of join methods

Oracle Hash Join and nested join

2005-10-31T18:05:00.000-08:00

- In a nested loop join, the database reads and index, builds a list of ROW ID’s and then probes into the second table for the matching rows.

-In a hash join, the database does a full-scan of the driving table, builds a RAM hash table, and then probes for matching rows in the other table. For certain types of SQL

-The propensity of the SQL optimizer to invoke a hash join is heavily controlled by the setting for the hash_area_size Oracle parameter

Oracle Plan Table

2005-10-31T16:30:00.000-08:00

use UTLXPLAN.SQL to create PLAN_TABLE
use UTLXPLS.SQL to generate report.
use command "explain plan for my-sql-statement"
or "explain plan into table_name for my-sql-statement" to generate execution plan

The fields (attributes) within the plan table
- the most important fields with in plan table are:
operation, option, object_name, id and parent_id

select substr (lpad(' ', level-1) operation ' (' options ')',1,30 ) "Operation", object_name "Object"
from plan_table
start with id = 0
connect by prior id=parent_id;

Oracle SQL Processing

2005-10-31T16:19:00.000-08:00

sql processing use the following main components to execute a SQL query

The parser checks both syntax and semantic analysis
The Optimizer uses costing methods, cost-based optimizer(CBO), or internal rules, rule-based optimizer(RBO), to determine the most efficient way of producing the result of the query.
The Row Source Generator receives the optimal plan from the optimizer and outputs the execution plan for the SQL statement.( execution plan defines how oracle finds or writes the data. For example, an important decision that Oracle has to take is if it uses indexes or not. And if there are more indexes, which of these is used.)
The SQL Execution Engine operates on the execution plan associated with a SQL statement and then produces the results of the query.

oracle how to retrieve sid information

2005-10-25T12:34:00.000-07:00

Method 1 (for Oracle 7.x and up)
SELECT sid FROM V$SESSION WHERE audsid = userenv('sessionid');

Method 2 (for Oracle 8i and up - uses new function SYS_CONTEXT)
SELECT sid FROM V$SESSION WHERE audsid = SYS_CONTEXT('userenv','sessionid');

Analyze snappack output on oraperf website

2005-10-24T16:34:00.000-07:00

http://www.oraperf.com/
login: jamesp

> set line 500
> set serveroutput on
> execute oraperf.analyze_lio(10,2);

Server log monitoring

2005-10-24T10:27:00.000-07:00

use logsurfer or swatch to monitor server log files.

Setup Sun StorEge 3510 disk array

2005-01-08T20:19:00.000-08:00

1) turn on point to point mode (default is loop mode)
2) turn on auto failover back
3) choose random mode ( default is sequencial mode)
4) create at least one spare disk
5) create two logical drive and assign one drive to primary controller and another drive to secondary controller
6) create partitions on logical drive
7) map host LUN