MiDBA

See rainbow while walking home

2012-01-05T23:15:00.001+08:00

Test post with picture from iphone.

Rename datafile on-the-flying

2012-01-05T21:55:00.001+08:00

Imaging you accidentally append a space to the datafile...
Luckily, you have the database in archivelog mode.

Examples
1. Resize datafile if possible (for backup before rename: save space and faster copy)

SQL> ALTER DATABASE DATAFILE 'F:\oradata\live\Mydb02.ora ' RESIZE 50m;

2. Take datafile offline:

SQL> ALTER DATABASE DATAFILE 'F:\oradata\live\Mydb02.ora ' OFFLINE;

Database altered.

3. at OS level, copy (preferable) or rename the file

cd  F:\oradata\live\

copy 'Mydb02.ora ' 'Mydb02.ora'
#cp -p 'Mydb02.ora ' 'Mydb02.ora'  (for unix like platform)

or move 'Mydb02.ora ' 'Mydb02.ora'

4. Rename the datafile in database to update control file

ALTER DATABASE RENAME FILE 'F:\oradata\live\Mydb02.ora ' TO 'F:\oradata\live\Mydb02.ora
';

5. Take datafile online, when doing this, file recovery is needed to update the timestamp in the offline datafile header.:

SQL> ALTER DATABASE DATAFILE 'F:\oradata\live\Mydb02.ora' ONLINE;

ALTER DATABASE DATAFILE 'F:\oradata\live\Mydb02.ora' ONLINE
* ERROR at line 1: ORA-01113: file 16 needs media recovery
ORA-01110: data file 16: 'F:\ORADATA\LIVE\Mydb02.ORA'

SQL> RECOVER DATAFILE 'F:\oradata\live\Mydb02.ora';
 Media recovery complete.

SQL> ALTER DATABASE DATAFILE 'F:\oradata\live\Mydb02.ora' ONLINE;

Database altered

Ref:

1. ALTER DATABASE

2. Renaming or Moving Oracle Files

3. Move/rename datafiles in Oracle

alter system register to speed up dynamic registration

2011-10-26T13:32:00.001+08:00

【LISTENER】使用“alter system register;”解决动态监听注册缓慢问题 - secooler 快乐的DBA - ITPUB个人空间 - powered by X-Space

7 Linux Grep OR, Grep AND, Grep NOT Operator Examples

2011-10-24T00:06:00.001+08:00

7 Linux Grep OR, Grep AND, Grep NOT Operator Examples

db_block_checking overhead.

2011-10-24T00:03:00.001+08:00

11g中的db_block_checking参数 | Oracle Clinic – 提供专业Oracle技术支持,性能调整及数据恢复服务

Furthermore, if there are few indexes, I believe more overhead needed.

I do have data loading performance issues in data warehouse, after enable DB_ULTRA_SAFE = DATA_AND_INDEX

more redo size generated when undo tablespace is insufficient and rollback as result

2011-10-02T22:28:00.001+08:00

oracle新人关于redo和undo的疑惑 - Oracle专题深入讨论 - ITPUB论坛 - it168旗下专业技术社区

Cernatis Inc - Fix high "latch: cache buffers chains" waits

2011-09-29T22:21:00.001+08:00

Cernatis Inc - Fix high "latch: cache buffers chains" waits

relevant links
http://blog.tanelpoder.com/2011/06/05/cache-buffers-chains-latch-contention-using-latchprofx-sql-example/

http://tech.e2sn.com/oracle/troubleshooting/latch-contention-troubleshooting

tnsping response time

2011-09-29T10:42:00.001+08:00

Should I use TNSPING to test my oracle net performance ?

Typically when TNSPING times go up in Dedicated Server configuration, it is because the system is hitting a high level of load and the listener is having to wait for a process to fork and execute the oracle dedicated server.The TNSPING program sends a packet to the listener,which goes into it’s listening queue.If there were also connect requests in the queue, then the listener will handle each request (including the tns “ping”) in the order they were received per second. If those connect requests take time, then it will take time to process the ping.TNSPING should never be used to test network performance. TNSPING’s only function is to send a connect Packet (NSPTCN) to the listener, Listener replies with a refuse Packet (NSPTRF) and a round trip time is computed. A slow TNSPING time could be anything from poor DNS resolution to a slow network to a busy listener to a busy server.

If connections are going fine ,We should not be worried about the tnsping response time.

log shipping

2011-09-13T23:34:00.001+08:00

SQL Server 2008 R2 : LOG SHIPPING – A VERY LARGE DATABASE « SQL DBA Support . COM – Knowledge Centre for SQL DBAs

10g and 11g Statistics Tables Created by the DBMS_STATS Package

2011-09-09T17:28:00.000+08:00

REM in 10g database

SYS> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE    10.2.0.2.0      Production
TNS for HPUX: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production

EXEC DBMS_STATS.CREATE_STAT_TABLE(ownname=>'PERFSTAT',stattab => 'T1_STATTAB');

SQL> DESC DBO.T1_STATTAB
Name                                          Null?    Type
----------------------------------------- -------- ----------------------------
STATID                                             VARCHAR2(30)
TYPE                                               CHAR(1)
VERSION                                            NUMBER
FLAGS                                              NUMBER
C1                                                 VARCHAR2(30)
C2                                                 VARCHAR2(30)
C3                                                 VARCHAR2(30)
C4                                                 VARCHAR2(30)
C5                                                 VARCHAR2(30)
N1                                                 NUMBER
N2                                                 NUMBER
N3                                                 NUMBER
N4                                                 NUMBER
N5                                                 NUMBER
N6                                                 NUMBER
N7                                                 NUMBER
N8                                                 NUMBER
N9                                                 NUMBER
N10                                                NUMBER
N11                                                NUMBER
N12                                                NUMBER
D1                                                 DATE
R1                                                 RAW(32)
R2                                                 RAW(32)
CH1                                                VARCHAR2(1000)

REM in 11g

SYS> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for HPUX: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> DESC PERFSTAT.T1_STATTAB
Name                                    Null?    Type
----------------------------------------- -------- ----------------------------
STATID                                             VARCHAR2(30)
TYPE                                               CHAR(1)
VERSION                                            NUMBER
FLAGS                                              NUMBER
C1                                                 VARCHAR2(30)
C2                                                 VARCHAR2(30)
C3                                                 VARCHAR2(30)
C4                                                 VARCHAR2(30)
C5                                                 VARCHAR2(30)
N1                                                 NUMBER
N2                                                 NUMBER
N3                                                 NUMBER
N4                                                 NUMBER
N5                                                 NUMBER
N6                                                 NUMBER
N7                                                 NUMBER
N8                                                 NUMBER
N9                                                 NUMBER
N10                                                NUMBER
N11                                                NUMBER
N12                                                NUMBER
D1                                                 DATE
R1                                                 RAW(32)
R2                                                 RAW(32)
CH1                                                VARCHAR2(1000)
CL1                                                CLOB

IN Step 33 of manual upgrade checklist to 11gR2.

Upgrade Statistics Tables Created by the DBMS_STATS Package
If you created statistics tables using the DBMS_STATS.CREATE_STAT_TABLE procedure, then upgrade these tables by executing the
following procedure:

EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE('PERFSTAT','T1_STATTAB');

about "db file parallel read/write"

2011-09-09T17:21:00.003+08:00

'db file parallel read' occurs during recovery. The datablocks that need to be changed are read from various datafiles and are placed in non-contiguous buffer blocks. The server process waits till all the blocks are read in to the buffer.
interesting chart here.
http://oracledba-vinod.blogspot.com/2009/08/db-file-parallel-readwrite.html

shrinking datafile

2011-09-02T14:45:00.000+08:00

TIP #21: Shrink datafiles

Oracle Datafiles can be shrinked if chunk of free space exists at the end of datafiles.
This URL has some useful query for shrinking :
http://www.oracle.com/technology/oramag/oracle/04-sep/o54asktom.html

--- to shrink datafiles:

select 'alter database datafile ''' file_name ''' resize ' ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) 'm;' cmd from dba_data_files a, ( select file_id, max(block_id+blocks-1) hwm from dba_extents group by file_id ) b where a.file_id = b.file_id(+) and ceil(blocks*&&blksize/1024/1024)- ceil((nvl(hwm,1)* &&blksize)/1024/1024 ) > 0;

$ cat resize_advisor.sql
set pages 1000

SELECT a.file_id,
a.file_name,
a.filesize,
b.freesize,
(a.filesize - b.freesize) usedsize,
c.hwmsize,
c.hwmsize - (a.filesize - b.freesize) unsedsize_belowhwm,
a.filesize - c.hwmsize-1 canshrinksize
FROM (SELECT file_id, file_name, round(bytes / 1024 / 1024) filesize FROM dba_data_files) a,
(SELECT file_id, round(SUM(dfs.bytes) / 1024 / 1024) freesize FROM dba_free_space dfs GROUP BY file_id) b,
--(SELECT file_id, round(MAX(block_id) * 8 / 1024) HWMsize FROM dba_extents GROUP BY file_id) c
(SELECT file_id, round(MAX(block_id + blocks) * (select value/1024 from v$parameter where name ='db_block_size') / 1024) HWMsize FROM dba_extents GROUP BY file_id) c
WHERE a.file_id = b.file_id
AND a.file_id = c.file_id
ORDER BY unsedsize_belowhwm DESC;

-- resize advisor for one tablespace
cat resize_advisor_tbs.sql
set pages 1000

SELECT a.file_id,
a.file_name,
a.filesize,
b.freesize,
(a.filesize - b.freesize) usedsize,
c.hwmsize,
c.hwmsize - (a.filesize - b.freesize) unsedsize_belowhwm,
a.filesize - c.hwmsize - 1 canshrinksize
FROM (SELECT file_id, file_name, round(bytes / 1024 / 1024) filesize FROM dba_data_files where tablespace_name='&tbs_name') a,
(SELECT file_id, round(SUM(dfs.bytes) / 1024 / 1024) freesize FROM dba_free_space dfs GROUP BY file_id) b,
(SELECT file_id, round(MAX(block_id + blocks) * (select value/1024 from v$parameter where name ='db_block_size') / 1024) HWMsize FROM dba_extents GROUP BY file_id) c
WHERE a.file_id = b.file_id
AND a.file_id = c.file_id
ORDER BY unsedsize_belowhwm DESC;

--- To find which objects have extents at the end of datafile.
Relocating these objects makes shrinking of relevant datafile possible.

select *
from (
select owner, segment_name,
segment_type, block_id
from dba_extents
where file_id =
( select file_id
from dba_data_files
where file_name = &FILE )
order by block_id desc
)
where rownum <= 5;

Partition Pruning Links

2011-09-01T23:46:00.001+08:00

Partition Pruning

Partition Pruning and Joins [ID 179518.1]

Ask Tom "partition pruning"

How to disable 10g recycle bin (to avoid ORA-01658)

2011-08-19T00:30:00.000+08:00

Developer feedback that she is not able to create a table, regarless of 13GB free space in the tablespace.

The error message is :

ORA-01658: unable to create INITIAL extent for segment in tablespace.

Problem solved immediately after I purged recyclebin from my 2nd feeling.

Finally , I found :
in 10g , if you have database running with recyclebin=on for some period and you have objects create & drop in those tablespaces.
Due to some reason, e.g restore to another location with recyclebin = off, or you decided to turn it off. Howerver, the dropped objects when recyclebin was ON , will remain in the recyclebin even if we set the recyclebin parameter to OFF. Hence, the space shown in dba_free_space is not swapped out, cuased the error.

Below is my test.

SYS@ODSPRX2> show parameter recyclebin;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      on

SYS@ODSPRX2> create tablespace tbs1 datafile '/software/oraods/labs/recyclebin/tbs1.dbf' size 5m
2 extent management local uniform size 512k
3 segment space management auto;

Tablespace created.

@> conn / as sysdba
Connected.

SYS@ODSPRX2> create user liqy identified by liqyliqy default tablespace tbs1;

User created.

SYS@ODSPRX2> grant dba to liqy;

Grant succeeded.

SYS@ODSPRX2> conn liqy/liqyliqy
Connected.
LIQY@ODSPRX2> create table t1 (f1 number) ;

Table created.

LIQY@ODSPRX2> create table t2 (f1 number) storage(initial 512k);

Table created.

LIQY@ODSPRX2> select segment_name, bytes from dba_segments where tablespace_name='TBS1';

SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES
----------
T1
    524288

T2
    524288


LIQY@ODSPRX2> select bytes from user_free_space where tablespace_name='TBS1';

     BYTES
----------
   3670016

LIQY@ODSPRX2> select bytes/512/1024 from user_free_space where tablespace_name='TBS1';

BYTES/512/1024
--------------
             7

LIQY@ODSPRX2> alter table t2 allocate extent (size 3670016);

Table altered.

LIQY@ODSPRX2> select bytes from user_free_space where tablespace_name='TBS1';

no rows selected

LIQY@ODSPRX2> create table t3 (f1 number);
create table t3 (f1 number)
*
ERROR at line 1:
ORA-01658: unable to create INITIAL extent for segment in tablespace TBS1

LIQY@ODSPRX2> select segment_name, bytes from dba_segments where tablespace_name='TBS1';

SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES
----------
T1
    524288

T2
   4194304


LIQY@ODSPRX2> drop table t1;

Table dropped.

LIQY@ODSPRX2> select bytes/512/1024 from user_free_space where tablespace_name='TBS1';

BYTES/512/1024
--------------
             1

LIQY@ODSPRX2> create table t3 (f1 number);

Table created.

LIQY@ODSPRX2> drop table t3;

Table dropped.

LIQY@ODSPRX2> select bytes/512/1024 from user_free_space where tablespace_name='TBS1';

BYTES/512/1024
--------------
             1

LIQY@ODSPRX2> conn / as sysdba
Connected.
SYS@ODSPRX2> alter system set recyclebin=off;

System altered.

SYS@ODSPRX2> conn liqy/liqyliqy
Connected.
LIQY@ODSPRX2> select bytes/512/1024 from user_free_space where tablespace_name='TBS1';

BYTES/512/1024
--------------
             1

LIQY@ODSPRX2> create table t3 (f1 number);
create table t3 (f1 number)
*
ERROR at line 1:
ORA-01658: unable to create INITIAL extent for segment in tablespace TBS1

LIQY@ODSPRX2> purge recyclebin;

Recyclebin purged.

LIQY@ODSPRX2> create table t3 (f1 number);

Table created.

LIQY@ODSPRX2> spool off
SYS@ODSPRX2> drop tablespace tbs1 including contents and datafiles;

Tablespace dropped.

SYS@ODSPRX2> spool off

But in 11.2.0.2, the dropped objects will be swapped out even with recyclebin=off.

BTW, in 11gr2 we can't use 'alter system' to change value of recyclebin, while in 10g we can change it on the fly. The oracle document is not correct.

UTF8 and AL32UTF8

2011-08-11T16:41:00.001+08:00

In short, I think we should use AL32UTF8 to keep characterset support latest Unicode standard.

Oracle 8i (or lower) RDBMS and client releases connect to an 9i (or up) AL32UTF8 system or when connecting using database links from an 9i (or up) AL32UTF8 database to an 8i (or lower) database.

UTF8 is Unicode revision 3.0 in 8.1.7 and up. AL32UTF8 is updated with newer Unicode versions in each major release.

If you need to understand "code point" , check code point and description in UTF-8 wiki

As far as these two character sets go in Oracle, the only difference between AL32UTF8 and UTF8 character sets is that AL32UTF8 stores characters beyond U+FFFF as four bytes (exactly as Unicode defines UTF-8). Oracle’s “UTF8” stores these characters as a sequence of two UTF-16 surrogate characters encoded using UTF-8 (or six bytes per character). Besides this storage difference, another difference is better support for supplementary characters in AL32UTF8 character set.

in

Difference between UTF8 and AL32UTF8

Caution:

AL32UTF8 is the Oracle Database character set that is appropriate for XMLType data. It is equivalent to the IANA registered standard UTF-8 encoding, which supports all valid XML characters.

Using database character set UTF8 for XML data could cause a fatal error or affect security negatively.

References:

UTF-8 wiki

Problems connecting to AL32UTF8 databases from older versions (8i and lower) [ID 237593.1]

Unicode Character Sets In The Oracle Database [ID 260893.1]

Difference between UTF8 and AL32UTF8 http://oracleappstechnology.blogspot.com/2007/10/difference-between-utf8-and-al32utf8.html

Difference between UTF8 and AL32UTF8 character sets in Oracle

UTF8 to AL32UTF8

When PHP and Oracle assume the worst about each other

AL32UTF8/UTF8 (Unicode) Database Character Set Implications

AL32UTF8 / UTF8 (Unicode) Database Character Set Implications [ID 788156.1]

Hash Group by exceed temporary tablespace

2011-08-05T18:51:00.009+08:00

Tuned a "HASH GROUP BY" after increase the pga_aggregate_target from 190MB to 2GB , since workload increased over the time.

Feeling the data volume in below plan :

- massive SUMs

- 52 milliows row,

- 5 month's data.

PERFSTAT@ABC>@disp_cursor_plan
Enter value for sqlid: 885gs8h6jjxzc
old   1: select * from table(dbms_xplan.display_cursor('&sqlid'))
new   1: select * from table(dbms_xplan.display_cursor('885gs8h6jjxzc'))

SQL_ID 885gs8h6jjxzc, child number 0
-------------------------------------
       ,sum(CASE period WHENesets
to_date('201107','YYYYMM') THEN (case hourofday when 0 then mou else 0 end) ELSE 0
       ,sum(case hourofday when 0 then mou else 0 end) as
       ,sum(CASE period WHEN to_date('201107','YYYYMM') THEN (case
hourofday when 0 then no_call else 0 end) ELSE 0 END) AS nocall1m_0001
,sum(case hourofday when 0 then no_call else 0 end) as nocall6m_0001
,sum(CASE period WHEN to_date('201107','YYYYMM') THEN (case hourofday when 1 then
       ,sum(case hourofday when 1 then102
       ,sum(CASE period WHEN2
to_date('201107','YYYYMM') THEN (case hourofday when 1 then no_call else 0 end)
       ,sum(case hourofday when 1 then no_call else 0
       ,sum(CASE period WHEN to_date('201107','YYYYMM') THEN
(case hourofday when 2 then mou else 0 end) ELSE 0 END) AS mou1m_0203

Plan hash value: 2842556147

--------------------------------------------------------------------------------------------
| Id | Operation          | Name                  | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                       |       |       |   263K(100)|          |
|   1 | HASH GROUP BY     |                       |    52M| 3929M|   263K (8)| 00:52:48 |
|* 2 |   TABLE ACCESS FULL| DM_TRAN_LOCALOUTGOING |    52M| 3929M|   253K (4)| 00:50:43 |
--------------------------------------------------------------------------------------------

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

   2 - filter(("CALL_TYP"='VOICE' AND "HOUROFDAY">=0 AND "HOUROFDAY"<=23 AND
              "PERIOD"<=TO_DATE(' 2011-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "PERIOD">=TO_DATE(' 2011-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

Note
-----
   - dynamic sampling used for this statement

37 rows selected.

Hash group (and hash joins, as well as other operations such as sorts etc.) can use either optimal (i.e. in-memory), one-pass or multi-pass methods. The last two methods use TEMP storage and is thus much slower.
By increasing the number of possible items you might have exceeded the number of items that will fit in memory reserved for this type of operations.
Try looking at v$sql_workarea_active whilst the query is running, to see if this is the case. Or look at v$sql_workarea for historical information. It will also give you an indication of how much memory and/or temp space is needed for the operation.
If turns out to be the actual problem - try increasing the pga_aggregate_target initialization parameter, if possible. The amount of memory available for optimal hash/sort operations is usually around a 5% fraction of the pga_aggregate_target.

Useful info from http://www.orafaq.com/node/1446

Hash Join

The hash join is used for high-volume equi-joins (joins with equals predicates). Oracle performs a single read of the smaller row source (call this T1) and builds a hash table in memory. The join key is used as the hash-key of the hash table. Then a single pass of the larger row source (call this T2) is performed, hashing the join key of each row to obtain an address in the hash table where it will find matching T1 rows.
Provided T1 remains small enough to build the hash table in memory, T2 can be scaled up to any arbitrarily large volume without affecting throughput or exceeding temp space. If T1 cannot be hashed in memory, then a portion of the hash-table spills to disk. When the hash table is probed by T2, the rows with join keys that match those parts of the in-memory hash table are joined immediately; the rest are written to TEMP and joined in a second pass. The bigger T1 is, the smaller the proportion of the hash table that can fit in memory, and the larger the proportion of T2 that must be scanned twice. This slows the Hash Join down considerably and also makes the join non-scalable.

For large hash processes, where temp tablespace usage is unavoidable, temp tablespace groups offer significant improvement in I/O performance and more CPU utilization.

Indexed Nested Loops

Indexed Nested Loops is used primarily in low volume joins; it is efficient over small volumes and versatile enough to be used in a variety of situations. Although it is fully scalable, Indexed Nested Loops is inefficient over large data volumes.

2.8 millions rows in driving table looks up a larger table still should be regarded as inefficient. I had once case which is unable to complete running for 12 hours. Solved by using hash join plus partition pruning.

Sort-Merge

A sort-merge join works by reading each row-source in the join separately; sorting both sets of results on the join column(s); then concurrently working through the two lists, joining the rows with matching keys. Sort-Merge is generally faster than Indexed Nested Loops but slower than Hash Join for equi-joins. It is used almost exclusively for non-equi joins (>, <, BETWEEN) and will occasionally be used when one of the row sources is pre-sorted (eg. a GROUP BY inline view)
If both row sources are small then they may both be sorted in memory, however large sorts will spill to disk making then non-scalable.
There is no way to make a Sort-Merge join scalable. The only other way to resolve a non-equijoin is to use Nested Loops, which is slower. As volumes increase, Sort-Merge will continue to out-perform Nested Loops, but will eventually run out of Temp space. The only solution is to extend TEMP, or convert the join to Nested Loops (and then wait).

also read

http://www.dbspecialists.com/files/presentations/temp_space.html

http://stackoverflow.com/questions/154722/can-anyone-explain-how-the-oracle-hash-group-works

http://www.dba-oracle.com/tips_oracle_hash_joins.htm

relatioship of physical reads and logical I/O

2011-08-05T17:04:00.001+08:00

"A physical read of table or index data places the block into the buffer cache. Then we performan a logical I/O to retrieve the block. Hence most physical reads are immediately followed by a logical I/O!"

Generally, all physical I/Os result in logical I/Os

--from 《effective Oracle by Design》

database capacity planning for running database

2011-06-03T00:05:00.000+08:00

storage administrator may not know the annual growth in database, while the database may keep growing. As DBA we need to regularly , say yearly, review the growth of critical database, before it is too late to realize that we have no space to grow in SAN.

Below are few areas to drill down for careful review.

    1. Identify main contributers (tablespaces) to the growth. To achieve this, ideally you have job to record the tablespace used, total size daily , or simply can based the data file creation timestamp in dba_data_files. Create a spreadshee to calculate space needed for coming two years, using dimension tablespace and mount point name.

2. Return space to disk by shrink down data files of over-allocated tablespace. Or even you can drop unused tablespace.

3. Check if any housekeeping job is not paused accidentally. If there is big table in the tablespace and keeps growing, check with application if housekeep can be taken.

4. Check tablespace with uniform extent size , especially for uniform size >= 10MB, make sure no space wasted due to forget to consider 8x8k or 4x32k header overhead for each data file.

fix the database date

2011-06-02T23:46:00.002+08:00

SQL> select sysdate from dual;

SYSDATE
---------
03-JAN-11

SQL> !date
Tue May 31 09:33:05 SST 2011

SQL> alter system set fixed_date=none;

System altered.

SQL> select sysdate from dual;

SYSDATE
---------
31-MAY-11

datafile overhead of tablespace using uniform extent size

2011-05-27T22:00:00.000+08:00

    Note that there is datafile overhead for tablespaces with uniform size extent allocation .

    i.e, eight 8k blocks used for 8k block_size tablespace and four 32k blocks for 32k block size tablespace.

    eg. Add a 1000MB datafile to existing 8k block_size tablespace with 100MB uniform extent size tablespace in CUSTPA, the right size should be 1MB*1000+8k*8 =1000Mb+64Kb=1048641536 bytes.

     -- If we specify right "size 1000m", there will be only 9 x 100Mb extents available, 1 extent with 100Mb is used for overhead (wasted).

     -- If specify "size 1G" , it is 1024Mb , we waste (24Mb- 64kb). The datafile size should be (round to integer times of uniform extent size + datafile overhead). To make it easy, just plus 1MB for each data file in the tablespace.

    Please take note when extend tablespaces.

   SYS > show parameter block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     32768

SYS > create tablespace test datafile '/ods001/oradata/ODSDMS/test.dbf' size 100m
2 extent management local uniform size 1m;

Tablespace created.

SYS > select file#, bytes ,creation_time from v$datafile where file#=27;

     FILE#      BYTES CREATION_
---------- ---------- ---------
        27 104857600 27-MAY-11

SYS > create user test identified by test;

User created.

SYS > grant dba to test;

Grant succeeded.

SYS > alter user test default tablespace test;

User altered.

SYS > connect test/test;
Connected.
TEST > desc dba_free_space
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
TABLESPACE_NAME                                    VARCHAR2(30)
FILE_ID                                            NUMBER
BLOCK_ID                                           NUMBER
BYTES                                              NUMBER
BLOCKS                                             NUMBER
RELATIVE_FNO                                       NUMBER

TEST > set pages 1000
TEST > select * from dba_free_space where file_id=27;

TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
RELATIVE_FNO
------------
TEST                                   27          5 103809024       3168
          27


TEST > select 103809024/1048576 from dual;

103809024/1048576
-----------------
               99

TEST > prompt 1mb is "missing"
1mb is "missing"
TEST > prompt 1mb is the uniform size
1mb is the uniform size
TEST > desc dba_extents;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
OWNER                                              VARCHAR2(30)
SEGMENT_NAME                                       VARCHAR2(81)
PARTITION_NAME                                     VARCHAR2(30)
SEGMENT_TYPE                                       VARCHAR2(18)
TABLESPACE_NAME                                    VARCHAR2(30)
EXTENT_ID                                          NUMBER
FILE_ID                                            NUMBER
BLOCK_ID                                           NUMBER
BYTES                                              NUMBER
BLOCKS                                             NUMBER
RELATIVE_FNO                                       NUMBER

TEST > select * from dba_extents where file_id=27;

no rows selected

TEST > prompt not allocated yet
not allocated yet
TEST > select 32*1024*8 overhead_bytes from dual;

OVERHEAD_BYTES
--------------
        262144

TEST > select 104857600+262144 from dual;

104857600+262144
----------------
       105119744

TEST > alter database datafile '/ods001/oradata/ODSDMS/test.dbf' resize 105119744 ;

Database altered.

TEST > select * from dba_free_space where file_id=27;

TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
RELATIVE_FNO
------------
TEST                                   27          5 104857600       3200
          27


TEST > prompt 100MB available for allocation
100MB available for allocation

TEST > alter tablespace test add datafile '/ods001/oradata/ODSDMS/test2.dbf' size 10485760;

Tablespace altered.

TEST > select * from v$datafile where file#=28;

     FILE# CREATION_CHANGE# CREATION_        TS#     RFILE# STATUS ENABLED
---------- ---------------- --------- ---------- ---------- ------- ----------
CHECKPOINT_CHANGE# CHECKPOIN UNRECOVERABLE_CHANGE# UNRECOVER LAST_CHANGE#
------------------ --------- --------------------- --------- ------------
LAST_TIME OFFLINE_CHANGE# ONLINE_CHANGE# ONLINE_TI      BYTES     BLOCKS
--------- --------------- -------------- --------- ---------- ----------
CREATE_BYTES BLOCK_SIZE
------------ ----------
NAME
--------------------------------------------------------------------------------
PLUGGED_IN BLOCK1_OFFSET
---------- -------------
AUX_NAME
--------------------------------------------------------------------------------
FIRST_NONLOGGED_SCN FIRST_NON
------------------- ---------
        28         93782698 27-MAY-11          8         28 ONLINE READ WRITE
          93782699 27-MAY-11                     0
                        0              0             10485760        320
    10485760      32768
/ods001/oradata/ODSDMS/test2.dbf
         0         32768
NONE
                  0


TEST > select * from dba_free_space where file_id=28;

TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
RELATIVE_FNO
------------
TEST                                   28          5    9437184        288
          28


TEST > select 9437184/1048576 from dual;

9437184/1048576
---------------
              9



TEST > select 32*1024*2+10485760 from dual;

32*1024*2+10485760
------------------
          10551296

TEST > alter database datafile '/ods001/oradata/ODSDMS/test2.dbf' resize 10551296;

Database altered.

TEST > select * from dba_free_space where file_id=28;

TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
RELATIVE_FNO
------------
TEST                                   28          5    9437184        288
          28

-- -- 9 x 1MB extents

TEST > select 32*1024*3+10485760 from dual;

32*1024*3+10485760
------------------
          10584064

TEST > alter database datafile '/ods001/oradata/ODSDMS/test2.dbf' resize 10584064 ;

Database altered.

TEST > select * from dba_free_space where file_id=28;

TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
RELATIVE_FNO
------------
TEST                                   28          5    9437184        288
          28

-- still 9 x 1MB extents

TEST > select 32*1024*4+10485760 "extra4blocks" from dual;

extra4blocks
------------
    10616832

TEST > alter database datafile '/ods001/oradata/ODSDMS/test2.dbf' resize 10616832 ;

Database altered.

TEST > select * from dba_free_space where file_id=28;

TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
RELATIVE_FNO
------------
TEST                                   28          5   10485760        320

-- 10 x 1MB extents now

11gR2 AWR runs slowly due to unrealistic system statistics

2011-05-26T23:34:00.000+08:00

After database upgraded from 10.2.0.2 to 11.2.0.2, found AWR runs slower than 10g, the more snapshots covered the longer it takes.

Struggled for days, finally notice below system statistics in read is nor realistic to seem.
SYSP> select * from aux_stats$;

SNAME                          PNAME                               PVAL1
------------------------------ ------------------------------ ----------
PVAL2
------------------------------------------------------------------------------------------------------------------------------------
SYSSTATS_INFO                  STATUS
COMPLETED

SYSSTATS_INFO                  DSTART
02-23-2011 09:05

SYSSTATS_INFO                  DSTOP
02-23-2011 09:31

SYSSTATS_INFO                  FLAGS                                   1

SYSSTATS_MAIN                  CPUSPEEDNW                     1400.84388

SYSSTATS_MAIN                  IOSEEKTIM                              10

SYSSTATS_MAIN                  IOTFRSPEED                           4096

SYSSTATS_MAIN                  SREADTIM                         28231.75

SYSSTATS_MAIN                  MREADTIM                        24989.114

SYSSTATS_MAIN                  CPUSPEED                             1170

SYSSTATS_MAIN                  MBRC                                    6

SYSSTATS_MAIN                  MAXTHR                           96043008

SYSSTATS_MAIN                  SLAVETHR                          3421184

13 rows selected.

actions:
1.) backup system statistics and delete it. Run AWR report promptly .

SYS@ODSP> select * from aux_stats$;

SNAME                          PNAME                               PVAL1
------------------------------ ------------------------------ ----------
PVAL2
------------------------------------------------------------------------------
SYSSTATS_INFO                  STATUS
COMPLETED
SYSSTATS_INFO                  DSTART
05-26-2011 14:39
SYSSTATS_INFO                  DSTOP
05-26-2011 14:39
SYSSTATS_INFO                  FLAGS                                   0

SYSSTATS_MAIN                  CPUSPEEDNW                           1151

SYSSTATS_MAIN                  IOSEEKTIM                              10

SYSSTATS_MAIN                  IOTFRSPEED                           4096

SYSSTATS_MAIN                  SREADTIM

SYSSTATS_MAIN                  MREADTIM

SYSSTATS_MAIN                  CPUSPEED

SYSSTATS_MAIN                  MBRC

SYSSTATS_MAIN                  MAXTHR

SYSSTATS_MAIN                  SLAVETHR

13 rows selected.
2.) Gather NOWORKLOAD system statistics. Run report okay.
select * from aux_stats$;

SNAME                          PNAME                               PVAL1
------------------------------ ------------------------------ ----------
PVAL2
------------------------------------------------------------------------------
SYSSTATS_INFO                  STATUS
COMPLETED
SYSSTATS_INFO                  DSTART
05-26-2011 14:46
SYSSTATS_INFO                  DSTOP
05-26-2011 14:46
SYSSTATS_INFO                  FLAGS                                   1

SYSSTATS_MAIN                  CPUSPEEDNW                           1152

SYSSTATS_MAIN                  IOSEEKTIM                               6

SYSSTATS_MAIN                  IOTFRSPEED                          34397

SYSSTATS_MAIN                  SREADTIM

SYSSTATS_MAIN                  MREADTIM

SYSSTATS_MAIN                  CPUSPEED

SYSSTATS_MAIN                  MBRC

SYSSTATS_MAIN                  MAXTHR

SYSSTATS_MAIN                  SLAVETHR

13 rows selected.

Finally from http://structureddata.org/2008/01/02/what-are-your-system-statistics/ , I realized it is a bug.   Bug 9842771 Wrong SREADTIM and MREADTIM statistics in AUX_STATS$

The workaround is to manually set SYSTEM statistics or NOWORKLOAD system statistics.

Lesson learned again : understand what exactly you are going to do in depth. BTW, since this is a known bug, I think the complete checklist of upgrade guide should be updated.

Here are more informations about system statistics.

1. When Oracle gathers system statistics, it analyzes system activity in a specified time period (workload statistics) or simulates a workload (noworkload statistics). The statistics are collected using the DBMS_STATS.GATHER_SYSTEM_STATS procedure. Oracle Corporation highly recommends that you gather system statistics.

gathering_mode


Mode values are:

NOWORKLOAD: Will capture characteristics of the I/O system. Gathering may take a few minutes and depends on the size of the database. During this period Oracle will estimate the average read seek time and transfer speed for the I/O system. This mode is suitable for the all workloads. Oracle recommends to run GATHER_SYSTEM_STATS ('noworkload') after creation of the database and tablespaces. To fine tune system statistics for the workload use 'START' and 'STOP' or 'INTERVAL' options. If you gather both 'NOWORKLOAD' and workload specific (statistics collected using 'INTERVAL' or 'START' and 'STOP' ), the workload statistics will be used by optimizer. Collected components: cpuspeednw, ioseektim, iotfrspeed.

INTERVAL: Captures system activity during a specified interval. This works in combination with the interval parameter. You should provide an interval value in minutes, after which system statistics are created or updated in the dictionary or stattab. You can use GATHER_SYSTEM_STATS (gathering_mode=>'STOP') to stop gathering earlier than scheduled. Collected components: maxthr, slavethr, cpuspeed, sreadtim, mreadtim, mbrc.

START | STOP: Captures system activity during specified start and stop times and refreshes the dictionary or stattab with statistics for the elapsed period. Interval value is ignored. Collected components: maxthr, slavethr, cpuspeed, sreadtim, mreadtim, mbrc.

-- In this case, I use START|STOP to sample my database for 30 minutes during normal workload .

3. specs and units
CPUSPEED     Workload CPU speed in millions of cycles/second
CPUSPEEDNW     Noworkload CPU speed in millions of cycles/second
IOSEEKTIM     Seek time + latency time + operating system overhead time in milliseconds
IOTFRSPEED     Rate of a single read request in bytes/millisecond
MAXTHR     Maximum throughput that the I/O subsystem can deliver in bytes/second
MBRC     Average multiblock read count sequentially in blocks
MREADTIM     Average time for a multi-block read request in milliseconds
SLAVETHR     Average parallel slave I/O throughput in bytes/second
SREADTIM     Average time for a single-block read request in milliseconds

How much more memory is needed by 11gR2 DBConsole

2011-04-28T00:14:00.000+08:00

When assessing database upgrade, we'll need to know actual overhead need for newer version, which may not be found from Oracle document, or accurate enough for our own environment. e.g, in order to run 11gR2 database, my finding for development is 500Mb and production is 800Mb. The 300Mb (if I remember correctly) in oracle document may easily lead to ORA-4031.

Today did the experiment on four DBConsoles , comparing 10.2.0.2 and 11.2.0.2.
The following shows the figure of Total Memory (VM) and Residential Memory(User memory) using HP-UX 11i IA v3 glance utility, when I shutdown 4 DBConsoles one by one.

shutdown 10g dbconsole	init	1 Console	2 Consoles	3 Consoles	4 Consoles	Average
Total VM(Gb)	22.5	21.8	21.3	20.7	20	0.625
user memory (Gb)	10.6	10.4	10.2	10	9.7	0.225

shutdown 11g dbconsole	init	1 Console	2 Consoles	3 Consoles	4 Consoles	Average
Total VM(Gb)	27.8	26	24.2	22.5	20.6	1.8
user memory (Gb)	16.4	15.9	15.3	14.8	14.3	0.525

From above tables, it is clear that 11.2.0.2 DBConsole is almost 3 times than that in 10.2.0.2 , both total memory and physical memory.

For those doing capacity planning, please don't take for granted and overlook harware requirement by different version.

Install Cygwin Xserver

2011-04-27T23:46:00.000+08:00

Xserver Hummybird Exceed is funny, after launch it, I can't open multiple x-windows -- they overlap (the first window is right under 2nd one, thus can't interactive it) , e.g running dbua to upgrade multiple databases in parallel to save downtime. Hence, I think of using Cygwin/X to avoid install x11, which is easy without any license issue.

1. Open Cygwin setup application, install X11 package with default choice.
2. Type "starxwin" to launch X server
3. In the terminal , type "xhost + " to allow all connections from any
computers. (This step is important)
4. In putty session, export DISPALY=< x-server ip:0 >
5. For testing purpose, test launch "xclock"

DBA management

2011-04-26T22:35:00.001+08:00

Is pursuing MBA a good idea to achieve vertical growth for a DBA professional? | LinkedIn

11g Real Time SQL monitor

2011-04-22T16:22:00.000+08:00

V$SQL_MONITOR

V$SQL_MONITOR displays SQL statements whose execution have been (or are being) monitored by Oracle. An entry is created in V$SQL_MONITOR every time the execution of a SQL statement is being monitored. SQL monitoring is automatically started when a SQL statement runs parallel or when it has consumed at least 5 seconds of CPU or I/O time.
When the SQL statement being monitored is executing, statistics in V$SQL_MONITOR are generally refreshed in near real time, once every second. Once the execution ends, monitoring information is not deleted immediately. Instead, it is kept in V$SQL_MONITOR for at least one minute. The entry will eventually be deleted to reclaim its space as new statements are monitored.

V$SQL_PLAN_MONITOR

V$SQL_PLAN_MONITOR displays plan level monitoring statistics for each SQL statement found in V$SQL_MONITOR. Each row in V$SQL_PLAN_MONITOR corresponds to an operation of the execution plan being monitored. As with V$SQL_MONITOR, statistics exposed in V$SQL_PLAN_MONITOR are generally updated every second when the statement executes. These statistics are recycled on the same basis as V$SQL_MONITOR.
To eliminate the overhead of SQL plan monitoring, statistics collected for each operation of the plan don't record timing information such as elapsed time, CPU time, or I/O time. Instead, this timing information can be estimated quite accurately by joining V$SQL_PLAN_MONITOR with V$ACTIVE_SESSION_HISTORY on SQL_ID, SQL_EXEC_START, SQL_EXEC_ID, and SQL_PLAN_LINE_ID (simply named PLAN_LINE_ID in V$SQL_PLAN_MONITOR). The result of that join is a sample of the activity performed by each operation in the plan, from which an estimate of CPU time and wait time can be derived.

What is the retention of ASH

2011-04-18T23:12:00.000+08:00

Oracle collects Active Session History (ASH) statistics (mostly wait statistics for different events) for all active sessions every second from v$session and stores them in a circular FIFO buffer in the SGA.
ASH records are very recent session history within the last 5-10 mins. (This may not true, it depends on how busy the database it is. I show this later).
The MMNL (Manageability Monitor light - shows up as "Manageability Monitor light 2" process) process, if the buffer fills up before the AWR flushes (by default every hour) the MMNL process will flush the data (in v$active_session_history) to disk (data stored in dba_hist_active_session_history). The is column called IS_AWR_SAMPLE to indicates whether this sample has been flushed or will be flushed to the AWR DBA_HIST_ACTIVE_SESS_HISTORY) (Y) or not (N)

ASH resides in the SGA and it’s size is calculated by the lesser of:

* total # of cpu x 2MB memory
* 5% of shared pool

SYS@TT> select min(sample_time), max(sample_time) from v$ACTIVE_SESSION_HISTORY ;

MIN(SAMPLE_TIME)
---------------------------------------------------------------------------
MAX(SAMPLE_TIME)
---------------------------------------------------------------------------
17-APR-11 11.43.44.860 PM
18-APR-11 02.49.08.124 PM

LIQY: Near 15 hours' data in memory.

SYS@TT> select min(sample_time), max(sample_time) from sys.WRH$_ACTIVE_SESSION_HISTORY ;

MIN(SAMPLE_TIME)
---------------------------------------------------------------------------
MAX(SAMPLE_TIME)
---------------------------------------------------------------------------
16-AUG-10 10.37.41.345 AM
18-APR-11 02.00.07.434 PM
SYS@TT> select min(end_interval_time ), max(end_interval_time ) from dba_hist_snapshot ;

MIN(END_INTERVAL_TIME)
---------------------------------------------------------------------------
MAX(END_INTERVAL_TIME)
---------------------------------------------------------------------------
16-AUG-10 12.00.49.868 PM
18-APR-11 03.00.46.004 PM

LIQY:
1.) DBA_HIST_ACTIVE_SESSIONS_HISTORY is a view built on sys.WRH$_ACTIVE_SESSION_HISTORY.
2.) Observer that Historical ASH (on disk) is almost the same as ASH snapshots.

Testes that suceeds to generate ASH report within AWR retention, while fails to do so when the slot beyonds AWR retention. Hence, I think ASH retention is same as AWR's.

References:
Active Session History (ASH)

More active with Twitter laterly

2011-04-13T21:56:00.003+08:00

Busy with 11g R2 upgrade...

Upgrade 1st Production database to 11.2.0.2

2010-12-10T21:43:00.000+08:00

Finally, I made it , from 10.2.0.4 to 11.2.0.2 for data warehouse database.

Only one minor performance issue encountered, which is within control.

-- Provide workaround to application , before log TAR to metalink.

Cheers! One more to upgrade Jan.

How to control Ubuntu’s Services easily?

2010-11-30T23:07:00.001+08:00

How to control Ubuntu’s Services easily? | Ubuntu Tweak Blog

How To Start, Stop Services In Ubuntu 10

2010-11-30T23:01:00.001+08:00

How To Start, Stop Services In Ubuntu 10.04 Lucid Lynx Automatically | Liberian Geek

vmware player failed to compile module vmmon after upgrade to Ubuntu 10.10

2010-11-17T22:14:00.001+08:00

Installing VMware Workstation 7.1.1 64 bit on Ubuntu 10.10 » Pario TechnoBlob

wget http://www.sputnick-area.net/scripts/vmware7.1.1-patch-kernel-2.6.35.bash

VirtualBox can’t operate in VMX root mode. Please disable the KVM kernel extension, recompile your kernel and reboot (VERR_VMX_IN_VMX_ROOT_MODE).

2010-11-17T16:18:00.000+08:00

After I changed CPU from AMD to Intel, encounter the following error :
VirtualBox can’t operate in VMX root mode. Please disable the KVM kernel extension, recompile your kernel and reboot (VERR_VMX_IN_VMX_ROOT_MODE).
The root cause is either the kvm-intel or kvm-amd module has been loaded at boot time. To remove it   :
root@localhost:~# lsmod |grep kvm
kvm_intel              32832 0
kvm                   182683 1 kvm_intel
root@localhost:~# modprobe -r kvm_intel
No more message like the one above will be displayed.

CentOS: Unable to resume swap device SWAP-sda2

2010-11-17T10:10:00.000+08:00

Last night, upgrade my AMD CPU to Intel CPU, everything works fine Ubuntu, but not able to boot Cent OS , with above error message .

Googled and got idea from http://fedoraforum.org/forum/printthread.php?t=120868

It seems vladak's message match to my case.
"I hit this problem while swapping motherboard+CPU (Athlon X2 instead of Sempron) for Fedora 10 box. After the hardware change (could be caused because the old motherboard had 2 IDE channels and the new one has only 1 ? the boot disk is connected via IDE.) the system reported the dreaded "Unable to access resume device" message. Booting from Fedora 10 install DVD into rescue mode, chroot to /mnt/sysimage and mkinitrd fixed the problem."

Fixed the problem with below steps.
1. Find the CentOS 5.5 install DVD and boot into rescue mode.
2. chroot /mnt/sysimage
3. cd /boot and move existing initrd*.img ./initrd_bak
[root@localhost boot]# ll initrd_bak
total 15648
-rw------- 1 root root 2663536 Aug 21 12:46 initrd-2.6.18-194.11.1.el5.img
-rw------- 1 root root 2663856 Aug 21 12:45 initrd-2.6.18-194.11.1.el5xen.img
-rw------- 1 root root 2663556 Sep 12 22:19 initrd-2.6.18-194.11.3.el5.img
-rw------- 1 root root 2663874 Sep 12 22:19 initrd-2.6.18-194.11.3.el5xen.img
-rw------- 1 root root 2663599 Sep 29 22:56 initrd-2.6.18-194.11.4.el5.img
-rw------- 1 root root 2663935 Sep 29 22:55 initrd-2.6.18-194.11.4.el5xen.img

4. invoke below command to initialize RAM disk

mkinitrd initrd-2.6.18-194.11.4.el5.img initrd-2.6.18-194.11.4.el5

5. then I have below file, note tha the size is different with backup copy.
rw------- 1 root root 2672372 Nov 17 2010 initrd-2.6.18-194.11.4.el5.img

6. exit twice to boot up. cheers!

Using advantage of partition elimination

2010-11-12T23:07:00.001+08:00

[The problem]

Application team asked for help:

Daily financial report delayed near one month because job running very slow while DB to shutdown everyday for cold backup.
My estimation is about 30 hours for the job to completed.
Vendor not able to provide solution even tried changing code a few times.

[Diagnostic]
    This range partitioning table is about 140Gb big, with 500+ partitions, even partition stores 5 values of job_id.
    From execution plan, partition is performed but no parallelism regardless PARALLEL server enabled, and no performing full table scan, how ever generating lots of I/O , consist gets requires , physical reads etc.
    Total consist gets is about 5 times of partitions needed for scan.
The statement is like this:
     select ... from p_table where part_key_col in (select distinct job_id from job_table...);

    The execution plan shows NEST LOOP for each value return from sub-query, caused redundantly access to partitions.
    ie.
     for each job_id returned from sub-query (110 distinct job_id)
       do
         full partition scan ( one of total 22 partitions )
   done.
Hence, each partition is scanned 5 times in worst situation. Total times: 110
While parallelized scan can't happen in single partition, which only occurs for simultaneoustly access to multiple partitions.

[Solution]
Rewrite the code , to make partition elimination happen.
    select ... from p_table where part_key_col between (select min(distinct job_id) from job_table ...) and (select max(distinct job_id) from job_table ...);
Not that the logic slightly changed, but applicable in this case.

     After make this change according to my suggestion, job finished within 30 minutes, while observing 12 parallel processes running happily to scan 22 partitions once only.
    Cheers!

[Update on 17-Nov]

One more think, SQL logic should not be changed. Studied more about partition pruning from data warehousing guide, found USE_HASH hint achieved same effects without rewrite SQL.

changes/bugs may fail your 11g upgrade

2010-10-25T21:50:00.003+08:00

For OLAP type databases or running complicated batch jobs.
1. here are two bugs 8477973 and 4926618

8477973 Multiple open DB links / ORA-2020 / distributed deadlock possible

Impact: hang your sql session

4926618 Excessive CPU on HASH UNIQUE when repeated

Good news, both of them are fixed in 11.2.0.2

Impact: x times slower your UPDATE statement. Especially updating more than 1 million rows. The more rows more greater times. In my case, 21 minutes becomes 8 hours .

2. new parameter db_ultra_safe: DATA_AND_INDEX comes with significant overhead. For my INSERT case, it is 7x slower.

Spent weeks of effort to come to above findings. Hope it help preventing from happening to you . Cheers!

editing multpile files with vi

2010-10-01T22:20:00.000+08:00

Googled and learned useful commands can still recall today
:n #go to next file
:rew # rewind to first file
:e! # abandon all changes

I think these basic commands are more than enough

NTLM proxy

2010-07-24T10:18:00.001+08:00

Configuring ISA Proxy in Ubuntu « Ridvan Baluyos

cool

oracle password file

2010-07-22T22:52:00.001+08:00

(转)oracle口令文件 orapwd - 笔记 - ITPUB个人空间 - powered by X-Space

openssh missing cygcrypto-0.9.8.dll

2010-07-22T22:35:00.001+08:00

cygcrypto-0.9.8.dll ?

my solution is to go to http://www.cygwin.com/packages/ , find relative packages are libopenssl and libopenssl.

reinstall them, working now.

backup related views

2010-07-22T22:28:00.001+08:00

lsof

2010-07-18T20:20:00.001+08:00

【lsof】使用lsof获得打来 1521端口的程序清单 - secooler 快乐的DBA - ITPUB个人空间 - powered by X-Space

Convert DAA to ISO disk images in Linux

2010-07-18T20:12:00.001+08:00

Convert DAA to ISO Disk Images in Linux | 64 Bit Jungle

change db name and instance name

2010-07-18T15:19:00.001+08:00

(转)修改oracle实例名(sid)和数据库名(db_name) - 笔记 - ITPUB个人空间 - powered by X-Space

RAC 10g upgrade

2010-07-18T15:02:00.001+08:00

Oracle Rac 10.2.0.1升级至10.2.0.4操作笔记 - 菜小小的学习笔记 - ITPUB个人空间 - powered by X-Space

sql

2010-07-18T13:51:00.001+08:00

Oracle SQL写法 - 尛样儿 - ITPUB个人空间 - powered by X-Space

Oracle Character set

2010-07-18T13:39:00.001+08:00

转：Oracle数据库字符集问题解析 - chensrao的个人空间 - ITPUB个人空间 - powered by X-Space

dd

2010-07-18T10:37:00.001+08:00

dd命令使用 - ITPUB个人空间- Powered By SupeSite

fuser

2010-07-18T10:28:00.001+08:00

fuser 用法_幸福摩天轮_百度空间

Linux logging

2010-07-15T23:18:00.001+08:00

Linux日志分析的实战专题 - 笔记 - ITPUB个人空间 - powered by X-Space

ctime, atime, mtime

2010-07-11T21:40:00.001+08:00

修改文件时间与创建新文件：touch - 笔记

Shrink datafile

2010-07-11T21:26:00.001+08:00

数据库缩小表空间一例 - 在路上...... - ITPUB个人空间

buffer and cache

2010-07-11T16:24:00.001+08:00

Linux内存管理机制中buffer和cache的区别 - 尛样儿 - ITPUB个人空间

Oracle 11g防止暴力破解数据库用户密码的手段——延长失败尝试响应Oracle 11g防止暴力破解数据库用户密码的手段——延长失败尝试响应

2010-07-11T16:01:00.002+08:00

virtualbox vs vmware

2010-07-11T14:59:00.000+08:00

VirtualBox 3 vs Vmware Workstation 6.5.2

5 Reasons Why You Should Use VirtualBox Over VMware Server

VirtualBox vs. VMware vs. Parallels

[My key opinion]

VMWare: Better support USB devices, while virtualbox uses share folder.

VirtualBox: Suppose both sounds of host and guest.

linux proc/cpuinfo

2010-07-11T12:45:00.000+08:00

processor : 0

vendor_id : GenuineIntel

cpu family : 15

model : 6

model name : Intel(R) Pentium(R) 4 CPU 3.00GHz

stepping : 5

cpu MHz : 3143.295

cache size : 0 KB

physical id 0

siblings : 1

fdiv_bug : no

hlt_bug : no

f00f_bug : no

coma_bug : no

fpu : yes

fpu_exception : yes

cpuid level : 6

wp : yes

flags : fpu vme pse tsc msr pae cx8 sep pge cmov acpi mmx fxsr sse sse2

bogomips : 1674.44

rpm i386 i586 i686 之间的区别

有的rpm有分i386 i586 i686等不同版本，如：
　　abc-1.2.3-4.i386.rpm
　　abc-1.2.3-4.i586.rpm
　　abc-1.2.3-4.i686.rpm
　　它们有什么不同呢？

　　这里的i386 i586 i686指的是适用于intel i386、 i586、i686 兼容指令集的微处理器。一般来说，等级愈高的机器可接受较低等级的rpm文件。
i386—几乎所有的X86平台，不论是旧的pentum或者是新的pentum-IV与K7系统CPU，都可以正常工作，i指得是Intel兼容的CPU，至于386就是CPU的等级。
i586—就是586等级的计算机，包括pentum第一代MMX CPU，AMD的K5，K6系统CPU（socket7插脚）等CPU都是这个等级。
i686—pentum 2 以后的Intel系统CPU及K7以后等级的CPU都属于这个686等级。
你可以透过/proc/cpuinfo这个档案查询你的CPU等级。

/proc/cpuinfo

This virtual file identifies the type of processor used by your system. The following is an example of the output typical of /proc/cpuinfo:

         processor : 0 vendor_id : GenuineIntel cpu family : 15 model : 2 model name : Intel(R) Xeon(TM) CPU 2.40GHz stepping : 7 cpu MHz : 2392.371 cache size : 512 KB physical id : 0 siblings : 2 runqueue : 0 fdiv_bug : no hlt_bug : no f00f_bug : no coma_bug : no fpu : yes fpu_exception : yes cpuid level : 2 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm bogomips : 4771.02

· processor — Provides each processor with an identifying number. On systems that have one processor, only a 0 is present.

· cpu family — Austhoritatively identifies the type of processor in the system. For an Intel-based system, place the number in front of "86" to determine the value. This is particularly helpful for those attempting to identify the architecture of an older system such as a 586, 486, or 386. Because some RPM packages are compiled for each of these particular architectures, this value also helps users determine which packages to install.

· model name — Displays the common name of the processor, including its project name.

· cpu MHz — Shows the precise speed in megahertz for the processor to the thousandths decimal place.

· cache size — Displays the amount of level 2 memory cache available to the processor.

· siblings — Displays the number of sibling CPUs on the same physical CPU for architectures which use hyper-threading.

· flags — Defines a number of different qualities about the processor, such as the presence of a floating point unit (FPU) and the ability to process MMX instructions.

Understanding /proc/cpuinfo

Example:

$ uname -r
2.6.18-8.el5

How many physical processors are there?

$ grep 'physical id' /proc/cpuinfo | sort | uniq | wc -l
2

How many virtual processors are there?

$ grep ^processor /proc/cpuinfo | wc -l
4

Are the processors dual-core (or multi-core)?

$ grep 'cpu cores' /proc/cpuinfo
cpu cores       : 2
cpu cores       : 2
cpu cores       : 2
cpu cores       : 2

"2" indicates the two physical processors are dual-core, resulting in 4 virtual processors.

If "1" was returned, the two physical processors are single-core. If the processors are single-core, and the number of virtual processors is greater than the number of physical processors, the CPUs are using hyper-threading. Hyper-threading is supported if ht is present in the CPU flags and you are using an SMP kernel.

Are the processors 64-bit?

A 64-bit processor will have lm ("long mode") in the flags section of cpuinfo. A 32-bit processor will not.

e.g.,

flags : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx mmxext fxsr_opt lm 3dnowext 3dnow pni cx16 lahf_lm cmp_legacy svm cr8legacy ts fid vid ttp tm stc

What do the CPU flags mean?

The CPU flags are briefly described in the kernel header file cpufeature.h.

proc/cpuinfo 确定系统的CPU情况

一些操作系统的最新版本已经更新了 /proc/cpuinfo 文件，以支持多路平台。如果您的系统中的 /proc/cpuinfo 文件能够正确地反映出处理器信息，那么就不需要执行上述步骤。反之，可采用本文中的信息进行解释。
/proc/cpuinfo 文件包含系统上每个处理器的数据段落。/proc/cpuinfo 描述中有 6 个条目适用于多内核和超线程（HT）技术检查：processor, vendor id, physical id, siblings, core id 和 cpu cores。
processor 条目包括这一逻辑处理器的唯一标识符。
physical id 条目包括每个物理封装的唯一标识符。
core id 条目保存每个内核的唯一标识符。
siblings 条目列出了位于相同物理封装中的逻辑处理器的数量。
cpu cores 条目包含位于相同物理封装中的内核数量。
如果处理器为英特尔处理器，则 vendor id 条目中的字符串是 GenuineIntel。
拥有相同 physical id 的所有逻辑处理器共享同一个物理插座。每个 physical id 代表一个唯一的物理封装。Siblings 表示位于这一物理封装上的逻辑处理器的数量。它们可能支持也可能不支持超线程（HT）技术。每个 core id 均代表一个唯一的处理器内核。所有带有相同 core id 的逻辑处理器均位于同一个处理器内核上。如果有一个以上逻辑处理器拥有相同的 core id 和 physical id，则说明系统支持超线程（HT）技术。如果有两个或两个以上的逻辑处理器拥有相同的 physical id，但是 core id 不同，则说明这是一个多内核处理器。cpu cores 条目也可以表示是否支持多内核。
例如，如果系统包含两个物理封装，每个封装中又包含两个支持超线程（HT）技术的处理器内核，则 /proc/cpuinfo 文件将包含此数据。（注：数据并不在表格中。）

processor	0	1	2	3	4	5	6	7
physical id	0	1	0	1	0	1	0	1
core id	0	2	1	3	0	2	1	3
siblings	4	4	4	4	4	4	4	4
cpu cores	2	2	2	2	2	2	2	2

此例说明逻辑处理器 0 和 4 驻留在物理封装 0 的内核 0 上。这就表示逻辑处理器 0 和 4 支持超线程（HT）技术。相同的工作可用于封装 0 内核 1 上的逻辑处理器 2 和 6，封装 1 内核 2 上的逻辑处理器 1 和 5，以及封装 1 内核 3 上的逻辑处理器 3 和 7。此系统支持超线程（HT）技术，因为两个逻辑处理器共享同一个内核。有两种方式可以确定是否支持多内核。由于内核 0 和 1 存在于封装 0 上，而内核 2 和 3 存在于封装 1 上，所以这是一个多内核系统。此外，cpu cores 条目为 2，也说明有两个内核驻留在物理封装中。这是一个多路系统，因为有两个封装。
值得注意的是 physical id 和 core id 的编号可能是也可能不是连续的。系统上有两个物理封装并不罕见，而且 physical id 等于 0 和 3
CPU ID
CPU ID是CPU生产厂家为识别不同类型的CPU，而为CPU制订的不同的单一的代码；不同厂家的CPU，其CPU ID定义也是不同的；如 “0F24”（Inter处理器）、“681H”（AMD处理器），根据这些数字代码即可判断CPU属于哪种类型，这就是一般意义上的CPU ID。
由于计算机使用的是十六进制，因此CPU ID也是以十六进制表示的。Inter处理器的CPU ID一共包含四个数字，如“0F24”，从左至右分别表示 Type（类型）、Family（系列）、Mode（型号）和Stepping（步进编号）。从CPUID为“068X”的处理器开始，Inter另外增加了Brand ID（品种标识）用来辅助应用程序识别CPU的类型，因此根据“068X”CPUID还不能正确判别Pentium和Celerom处理器。必须配合Brand ID来进行细分。AMD处理器一般分为三位，如“681”，从左至右分别表示为Family（系列）、Mode（型号）和 Stepping（步进编号）。
Type（类型）
类型标识用来区别INTEL微处理器是用于由最终用户安装，还是由专业个人计算机系统集成商、服务公司或制作商安装；数字“1”标识所测试的微处理器是用于由用户安装的；数字“0”标识所测试的微处理器是用于由专业个人计算机系统集成商、服务公司或制作商安装的。我们通常使用的INTEL处理器类型标识都是“0”，“0F24”CPUID就属于这种类型。
Family（系列）
系列标识可用来确定处理器属于那一代产品。如6系列的INTEL处理器包括Pentium Pro、Pentium II、 Pentium II Xeon、Pentium III和Pentium III Xeon处理器。5系列（第五代）包括Pentium处理器和采用 MMX技术的Pentium处理器。AMD的6系列实际指有K7系列CPU，有DURON和ATHION两大类。最新一代的 INTEL Pentium 4系列处理器（包括相同核心的Celerom处理器）的系列值为“F”
Mode（型号）
型号标识可用来确定处理器的制作技术以及属于该系列的第几代设计（或核心），型号与系列通常是相互配合使用的，用于确定计算机所安装的处理器是属于某系列处理器的哪种特定类型。如可确定Celerom处理器是Coppermine还是Tualutin核心；Athlon XP处理器是Paiomino还是 Thorouhgbred核心。
Stepping（步进编号）
步进编号用来标识处理器的设计或制作版本，有助于控制和跟踪处理器的更改，步进还可以让最终用户更具体地识别其系统安装的处理器版本，确定微处理器的内部设计或制作特性。步进编号就好比处理器的小版本号，如CPUID为 “686”和“686A”就好比WINZIP8.0和8.1的关系。步进编号和核心步进是密切联系的。如CPUID为“686”的Pentium III 处理器是cCO核心，而“686A”表示的是更新版本cD0核心。
Brand ID（品种标识）
INTEL从Coppermine核心的处理器开始引入Brand ID作为CPU的辅助识别手段。如我们通过Brand ID可以识别出处理器究竟是Celerom还是Pentium 4。

和Oracle安装有关的Linux参数

2010-07-11T12:25:00.000+08:00

共享内存
共享内存通过将通用的结构和数据放在共享内存段中，使得进程可以对它们进行访问。这是现有最快的进程间通信（IPC）方式主要是因为数据在进程之间传递时没有涉及到内核的操作。在进程之间不需要复制数据。
Oracle 将共享内存用于它的系统全局区 (SGA)，这是一个由所有的 Oracle 备份进程及前台进程所共享的内存区域。为 SGA 分配足够的容量对于 Oracle 的性能非常重要，因为它负责保存数据库缓冲区高速缓存、共享 SQL、访问路径以及更多。
要确定所有共享内存的限制，可使用以下命令：
# ipcs -lm
------ Shared Memory Limits --------
max number of segments = 4096
max seg size (kbytes) = 32768
max total shared memory (kbytes) = 8388608
min seg size (bytes) = 1
SHMMAX
SHMMAX 参数定义共享内存段的最大大小（以字节为单位）。
Oracle SGA 由共享内存组成，且错误设置 SHMMAX 可能会限制 SGA 的大小。在设置 SHMMAX 时，切记 SGA 的大小应该适合于一个共享内存段。SHMMAX 设置不足可能会导致以下问题：
ORA-27123:unable to attach to shared memory segment
您可以通过执行以下命令来确定 SHMMAX 的值：
# cat /proc/sys/kernel/shmmax
33554432
SHMMAX 的默认值为 32MB。通常，这个值对于配置 Oracle SGA 而言太小了。我通常使用以下任一方法将 SHMMAX 参数设置为 2GB：
* 通过直接更改 /proc 文件系统，你无需重新启动计算机便可以改变 SHMMAX 的缺省设置。可以使用以下方法动态设置 SHMMAX 的值。通过将此命令置于 /etc/rc.local 启动文件中可以使它永久有效：
# echo "2147483648" > /proc/sys/kernel/shmmax
* 您还可以使用 sysctl 命令来更改 SHMMAX 的值：
# sysctl -w kernel.shmmax=2147483648
* 最后，通过将该内核参数插入到 /etc/sysctl.conf 启动文件中，您可以使这种更改永久有效：
# echo "kernel.shmmax=2147483648" >> /etc/sysctl.conf
SHMMNI
我们现在看一下 SHMMNI 参数。这个内核参数用于设置系统范围内共享内存段的最大数量。该参数的缺省值是 4096。该值足以满足需要，因此通常无需更改。
可以通过执行以下命令来确定 SHMMNI 的值：
# cat /proc/sys/kernel/shmmni
4096
SHMALL
该参数控制系统一次可以使用的共享内存总量（以页为单位）
简言之，该参数的值始终应至少为：
ceil(SHMMAX/PAGE_SIZE)
SHMALL 的默认大小为 2097152，并可以使用以下命令进行查询：
# cat /proc/sys/kernel/shmall
2097152
SHMALL 的默认设置足以满足 Oracle RAC 10g 安装的需要。
（注意：i386 平台上的 Red Hat Linux 中的页面大小为 4,096 字节。但您可以使用 bigpages，它支持配置更大的内存页面大小。)
设置信号
对信号的最佳描述是，它是用于在共享资源（如共享内存）的进程（或进程中的线程）之间提供同步的计数器。Unix System V 支持信号集，其中的每个信号都是一个信号计数。当应用程序请求信号时，它使用“集合”来完成此工作。
要确定所有信号限制，可使用以下命令：
# ipcs -ls
------ Semaphore Limits --------
max number of arrays = 128
max semaphores per array = 250
max semaphores system wide = 32000
max ops per semop call = 32
semaphore max value = 32767
您还可以使用以下命令：
# cat /proc/sys/kernel/sem
250 32000 32 128
SEMMSL
SEMMSL 内核参数用于控制每个信号集合的最大信号数。
Oracle 建议将 SEMMSL 设置为 init.ora 文件（适用于 Linux 系统上所有数据库）中的最大 PROCESS 实例参数设置再加上 10。此外，Oracle 建议将 SEMMSL 设置为不小于 100。
SEMMNI
SEMMNI 内核参数用于控制整个 Linux 系统中信号集的最大数量。
Oracle 建议将 SEMMNI 设置为不小于 100。
SEMMNS
SEMMNS 内核参数用于控制整个 Linux 系统中的信号（而非信号集）的最大数量
Oracle 建议将 SEMMNS 设置为系统上每个数据库的 PROCESSES 实例参数设置之和，加上最大的 PROCESSES 的两倍，最后为系统上的每个 Oracle 数据库加上 10。
使用以下计算式确定可以在 Linux 系统上分配的信号的最大数量。它将是以下两者中较小的一个值：
SEMMNS — 或 — (SEMMSL * SEMMNI) 。
SEMOPM
SEMOPM 内核参数用于控制每个 semop 系统调用可以执行的信号操作数。
semop 系统调用（函数）能够使用一个 semop 系统调用完成多个信号的操作。一个信号集可以拥有每个信号集中最大数量的 SEMMSL，因此建议将 SEMOPM 设置为等于 SEMMSL。
Oracle 建议将 SEMOPM 设置为不小于 100。
设置信号内核参数
我想更改（增加）的唯一参数是 SEMOPM。所有其他的缺省设置可以完全满足我们的示例安装。
* 您可以通过直接更改 /proc 文件系统，不必重新启动机器而更改所有信号设置的缺省设置。该方法将以下内容置于 /etc/rc.local 启动文件中：
# echo "250 32000 100 128" > /proc/sys/kernel/sem
* 您还可以使用 sysctl 命令来更改所有信号设置的值：
# sysctl -w kernel.sem="250 32000 100 128"
* 最后，可以通过将内核参数插入到 /etc/sysctl.conf 启动文件中以使此更改永久有效：
# echo "kernel.sem=250 32000 100 128" >> /etc/sysctl.conf
设置文件句柄
配置 Red Hat Linux 服务器时，必须确保最大文件句柄数足够大。文件句柄的设置表示您在 Linux 系统上可以打开的文件数。
使用以下命令来确定整个系统中文件句柄的最大数量：
# cat /proc/sys/fs/file-max
32768
Oracle 建议将整个系统的文件句柄值至少设置为 65536。
* 通过直接更改 /proc 文件系统，您可以不必重新启动机器而改变文件句柄最大数量的默认设置。该方法将以下内容置于 /etc/rc.local 启动文件中：
# echo "65536" > /proc/sys/fs/file-max
* 您还可以使用 sysctl 命令来更改 SHMMAX 的值：
# sysctl -w fs.file-max=65536
* 最后，可以通过将内核参数插入到 /etc/sysctl.conf 启动文件中以使此更改永久有效：
# echo "fs.file-max=65536" >> /etc/sysctl.conf
可以通过使用以下命令查询文件句柄的当前使用情况：
# cat /proc/sys/fs/file-nr
613 95 32768
file-nr 文件显示了三个参数：分配的文件句柄总数、当前使用的文件句柄数以及可以分配的最大文件句柄数。
（注意：如果需要增大 /proc/sys/fs/file-max 中的值，请确保正确设置 ulimit。对于 2.4.20，通常将其设置为 unlimited。使用 ulimit 命令来验证 ulimit 设置：
# ulimit
unlimited

如何设置

1．通过在/etc/sysctl.conf中配置
kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.shmall = 2097152
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
修改后运行sysctl -p 命令使得内核改变立即生效。以后重启机器也会按照这些配置修改机器环境
2．在/etc/rc.d/rc.local中直接写入/proc中去
在/etc/rc.d/rc.local文件的最后，加入
echo 8192 > /proc/sys/fs/file-max
echo 32768 > /proc/sys/fs/inode-max
echo 536870912 > /proc/sys/kernel/shmmax
echo 4096 > /proc/sys/kernel/shmmin
还需要在/etc/security/limits.conf文件增加如下内容：
oracle soft nofile 65536
oracle hard nofile 65536
oracle soft nproc 16384
oracle hard nproc 16384

Performance of compress tools : 7z and gzip

2010-07-11T00:13:00.001+08:00

hmc@hmc-desktop:/mnt/vm$ ls -l xp
total 5358756
-rw------- 1 hmc hmc 5481996800 2010-07-10 22:52 xp.vdi

compress this 5Gb file of virtual image, using 7z takes near 1 hours to finish, while gzip is about 8 minutes. However, 7z's compress ratio is higher than gzip about 16%.

hmc@hmc-desktop:/mnt/vm$ time tar cvfz xp.gz ./xp
./xp/
./xp/xp.vdi

real    7m49.668s
user    6m3.430s
sys    0m28.660s

[compressed file size]

-rw-r--r-- 1 hmc hmc 2587547513 2010-07-10 23:41 xp.7z
-rw-r--r-- 1 hmc hmc 3332232784 2010-07-10 23:52 xp.gz

Here is a good article about compress tools' performance

Compression Tools Compared

Add testing result with bz2. Looks gz format will be my first choice.

hmc@hmc-desktop:/mnt/vm$ time tar cvfj xp.bz2 ./xp
./xp/
./xp/xp.vdi

real    36m11.689s
user    29m39.640s
sys    0m36.570s
hmc@hmc-desktop:/mnt/vm$ ls -l xp
total 5371056
-rw------- 1 hmc hmc 5494579712 2010-07-11 13:53 xp.vdi
hmc@hmc-desktop:/mnt/vm$ ls -l xp.bz2
-rw-r--r-- 1 hmc hmc 3318611695 2010-07-11 15:15 xp.bz2

SQL Coding Conventions, Best Practices, and Programming Guidelines

2010-07-10T23:30:00.000+08:00

SQL Server TSQL Coding Conventions, Best Practices, and Programming Guidelines

Database Coding Standard and Guideline

SQL编程规范

详解/etc/fstab文件内容

2010-07-08T21:41:00.000+08:00

/etc/fstab内容主要包括六项：

例如：打印出中间的两行内容，如下
LABEL=/    /    ext3   defaults     1     1
/dev/sda2   /mnt/D/     vfat    defaults    0   0

第一列：设备名或者设备卷标名，（/dev/sda10 或者 LABEL=/）

第二列：设备挂载目录        （例如上面的“/”或者“/mnt/D/”）

第三列：设备文件系统          （例如上面的“ext3”或者“vfat”）

第四列：挂载参数     （看帮助man mount）
对于已经挂载好的设备，例如上面的/dev/sda2，现在要改变挂载参数，这时可以不用卸载该设备，而可以使用下面的命令（没有挂载的设备，remount 这个参数无效）
#mount /mnt/D/ -o remount,ro （改defaults为ro）
为了安全起见，可以指明其他挂载参数，例如：
noexec（不允许可执行文件可执行，但千万不要把根分区挂为noexec，那就无法使用系统了，连mount 命令都无法使用了，这时只有重新做系统了！
nodev（不允许挂载设备文件）
nosuid,nosgid（不允许有suid和sgid属性）
nouser（不允许普通用户挂载）

第五列：指明是否要备份，（0为不备份，1为要备份，一般根分区要备份）

第六列：指明自检顺序。（0为不自检，1或者2为要自检，如果是根分区要设为1，其他分区只能是2）

Linux tips

2010-07-07T23:46:00.001+08:00

noclobber

noclobber变量可以在重定向输出时保护已存在的文件，防止被意外地覆盖。在下例中，用户设置 noclobber为有效，在重定向时，用户试图去覆盖已经存在的文件myfile，此时系统将返回一个错误信息。

［例］

$ set –o noclobber

$ cat preface>myfile

bash: myfile: cannot overwrite existing file

http://space.itpub.net/batch.viewlink.php?itemid=662916

http://linux.vbird.org/linux_basic/0160startlinux.php
man一條指令后，在第一行，COMMAND(1)，或者是其他数字。
比如：
man date DATE(1)
man fstab FSTAB(5)

代號	代表內容
1	使用者在shell環境中可以操作的指令或可執行檔
2	系統核心可呼叫的函數與工具等
3	一些常用的函數 (function)與函式庫(library)，大部分為C的函式庫(libc)
4	裝置檔案的說明，通常在/dev下的檔案
5	設定檔或者是某些檔案的格式
6	遊戲(games)
7	慣例與協定等，例如Linux檔案系統、網路協定、ASCII code等等的說明
8	系統管理員可用的管理指令
9	跟kernel有關的文件

add Linux service for auto boot Oralce instances

2010-07-07T22:03:00.000+08:00

Key steps:

—操作详细过程：

[root@localhost ~]#cd /etc/rc.d/init.d

[root@localhost init.d]#touch dbauto              

用命令新建好文件(或是 在root用户etc/rc.d/init.d目录下直接新建文件),然后在文件中加入--下面--的脚本内容,保存

[root@localhost ~]# chmod 755 /etc/rc.d/init.d/dbauto    // 设置文件权限

[root@localhost ~]# ls -l /etc/rc.d/init.d/dbauto

-rwxr-xr-x  1 oracle oinstall 785 Oct 23 08:27 /etc/rc.d/init.d/dbauto

[root@localhost ~]# chkconfig --add dbauto   服务添加服务列表

[root@localhost ~]# chkconfig --level 345 dbauto on    //设置dbauto服务在指定的运行级别内被启动

centos 5.5 kernel failed to reboot during start udev

2010-07-05T22:18:00.000+08:00

[Symptom] after upgrade to CentOS 5.5 with new kernel 2.6.18-194.el5 keeps reboot during running udev, while using kernel 2.6.18-164 succeeds reboot.

someone says disable /sbin/start_udev inside /etc/rc.sysinit , tried "works" but failed to start x server and network.

someone says any incompatible hardware, this remind me I have one TV tuner plugged , but doesn't work.

after remove it, boots perfectly.

config cygwin cron

2010-06-28T23:46:00.000+08:00

$ which cron-config
/usr/bin/cron-config
$ cron-config
Do you want to install the cron daemon as a service? (yes/no) yes
Enter the value of CYGWIN for the daemon: [ ] Cygwin Cron
ERROR: Only "[no]ntsec" "[no]smbntsec" "[no]traverse" allowed.
Enter the value of CYGWIN for the daemon: [ ] ntsec

You must decide under what account the cron daemon will run.
If you are the only user on this machine, the daemon can run as yourself.
   This gives access to all network drives but only allows you as user.
Otherwise cron should run under the local system account.
It will be capable of changing to other users without requiring a
password, using one of the three methods detailed in
http://cygwin.com/cygwin-ug-net/ntsec.html#ntsec-nopasswd1
Do you want the cron daemon to run as yourself? (yes/no) no

Running cron_diagnose ...
... no problem found.

INFO: A cron daemon is already running.

In case of problem, examine the log file for cron,
/var/log/cron.log, and the Windows event log (using /usr/bin/cronevents)
for information about the problem cron is having.

Examine also any cron.log file in the HOME directory
(or the file specified in MAILTO) and cron related files in /tmp.

If you cannot fix the problem, then report it to cygwin@cygwin.com.
Please run the script /usr/bin/cronbug and ATTACH its output
(the file cronbug.txt) to your e-mail.

WARNING: PATH may be set differently under cron than in interactive shells.
         Names such as "find" and "date" may refer to Windows programs.

$ cygrunsrv -R "Cron daemon"
cygrunsrv: Error removing a service: OpenService: Win32 error 1060:
The specified service does not exist as an installed service.

$ cygrunsrv -R cron
$ cron-config
Do you want to install the cron daemon as a service? (yes/no) yes
Enter the value of CYGWIN for the daemon: [ ] ntsec

You must decide under what account the cron daemon will run.
If you are the only user on this machine, the daemon can run as yourself.
   This gives access to all network drives but only allows you as user.
Otherwise cron should run under the local system account.
It will be capable of changing to other users without requiring a
password, using one of the three methods detailed in
http://cygwin.com/cygwin-ug-net/ntsec.html#ntsec-nopasswd1
Do you want the cron daemon to run as yourself? (yes/no) yes

Please enter the password for user 'liqy':
Reenter:
Running cron_diagnose ...
... no problem found.

INFO: A cron daemon is already running.

In case of problem, examine the log file for cron,
/var/log/cron.log, and the Windows event log (using /usr/bin/cronevents)
for information about the problem cron is having.

Examine also any cron.log file in the HOME directory
(or the file specified in MAILTO) and cron related files in /tmp.

If you cannot fix the problem, then report it to cygwin@cygwin.com.
Please run the script /usr/bin/cronbug and ATTACH its output
(the file cronbug.txt) to your e-mail.

WARNING: PATH may be set differently under cron than in interactive shells.
         Names such as "find" and "date" may refer to Windows programs.

$ cat /var/log/cron.log
/usr/sbin/cron: can't lock /var/run/cron.pid, otherpid may be 4428: Resource temporarily unavailable
/usr/sbin/cron: can't lock /var/run/cron.pid, otherpid may be 4428: Resource temporarily unavailable
$ ps -ef |grep 4428
SYSTEM    4428       1   ? 13:46:40 /usr/sbin/cron
$ > /var/log/cron.log
$ cat /var/log/cron.log
$ crontab -l
# DO NOT EDIT THIS FILE - edit the master and reinstall.
# (t.cron installed on Mon Jun 21 13:41:56 2010)
# (Cron version V5.0 -- $Id: crontab.c,v 1.12 2004/01/23 18:56:42 vixie Exp $)
* * * * * date>>t.log
$ pwd
/home/liqy
$ cat t.log
Mon Jun 21 13:47:02 MPST 2010
Mon Jun 21 13:48:02 MPST 2010
Mon Jun 21 13:49:02 MPST 2010
Mon Jun 21 13:50:03 MPST 2010
Mon Jun 21 13:51:03 MPST 2010
Mon Jun 21 13:52:02 MPST 2010
Mon Jun 21 13:53:02 MPST 2010
Mon Jun 21 13:54:02 MPST 2010
Mon Jun 21 13:55:02 MPST 2010

sample code for load top AWR sql into sql plan baseline

2010-06-28T23:39:00.000+08:00

--in this example we load top 30 sql inside AWR snapshot 710 to 714 , into sql tuning set , followed by loaded and create as sql plan baseline.

EXEC DBMS_SQLTUNE.DROP_SQLSET('tset1');
EXEC DBMS_SQLTUNE.CREATE_SQLSET('tset1');

DECLARE
baseline_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
my_plans PLS_INTEGER;
BEGIN
OPEN baseline_cursor FOR
    SELECT VALUE(p)
    FROM TABLE (DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
                  710,714,
                   NULL, NULL,
                   'elapsed_time',
                   NULL, NULL, NULL,
                   30)) p;

    DBMS_SQLTUNE.LOAD_SQLSET(
             sqlset_name     => 'tset1',
             populate_cursor => baseline_cursor);
    my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( sqlset_name => 'tset1');
END;
/

Quick NFS HOWTO for Centos

2010-06-28T23:34:00.000+08:00

On the server

vi /etc/exports
add lines like:
/data1/sessions 192.168.0.0/255.255.0.0(rw) 10.0.0.0/255.0.0.0(rw)
vi /etc/hosts.allow
add lines like:
portmap: 192.168.0.0/255.255.0.0, 10.0.0.0/255.0.0.0
/etc/init.d/nfsd start

On the Client

vi /etc/fstab, adding the following line:
nfshostname:/data1/sessions /mnt nfs rw,hard,intr 0 0
make sure to mkdir /mnt/sessions, or it won’t work. To do it manually, just:
mount nfshostname:/data1/sessions /mnt/sessions

What is sytem privilege "export/import full database"

2010-06-28T23:17:00.000+08:00

SYS@ODST> select * from dba_sys_privs where grantee='LIQY';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
LIQY                           CREATE VIEW                              NO
LIQY                           CREATE TABLE                             NO
LIQY                           ALTER SESSION                            NO
LIQY                           CREATE SESSION                           NO

SYS@ODST> grant export full database to liqy;

Grant succeeded.

odsdev01:ODST:/software/oraods/temp> exp liqy/liqyliqy@ODST file=icc.dmp log=icc.log tables=DBAM1.M1_icc_call

Export: Release 10.2.0.2.0 - Production on Fri Jun 25 10:45:50 2010

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
EXP-00009: no privilege to export DBAM's table M_ICC_CALL
Export terminated successfully with warnings.
odsdev01:ODST:/software/oraods/temp> oerr exp 9
00009, 00000, "no privilege to export %s's table %s"
// *Cause: An attempt was made to export another user's table. Only a
//          database administrator can export another user's tables.
// *Action: Ask your database administrator to do the export.

SYS@ODST> select * from dba_sys_privs where grantee='LIQY';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
LIQY                           CREATE VIEW                              NO
LIQY                           CREATE TABLE                             NO
LIQY                           ALTER SESSION                            NO
LIQY                           CREATE SESSION                           NO
LIQY                           EXPORT FULL DATABASE                     NO

SYS@ODST> select * from dba_role_privs where grantee='LIQY';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
LIQY                           T_ROLE                         NO YES
LIQY                           EXP_FULL_DATABASE              NO YES

odsdev01:ODST:/software/oraods/temp> exp liqy/liqyliqy@ODST file=icc.dmp log=icc.log tables=DBAM1.M1_icc_call

Export: Release 10.2.0.2.0 - Production on Fri Jun 25 10:51:41 2010

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
Current user changed to DBAM. . exporting table                    M_ICC_CALL

SYS@ODST> select * from dba_sys_privs where grantee='EXP_FULL_DATABASE';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
EXP_FULL_DATABASE              RESUMABLE                                NO
EXP_FULL_DATABASE              BACKUP ANY TABLE                         NO
EXP_FULL_DATABASE              EXECUTE ANY TYPE                         NO
EXP_FULL_DATABASE              SELECT ANY TABLE                         NO
EXP_FULL_DATABASE              READ ANY FILE GROUP                      NO
EXP_FULL_DATABASE              SELECT ANY SEQUENCE                      NO
EXP_FULL_DATABASE              EXECUTE ANY PROCEDURE                    NO
EXP_FULL_DATABASE              ADMINISTER RESOURCE MANAGER              NO

8 rows selected.

Finally, get the answer from metalink:

The system privileges EXPORT/IMPORT FULL DATABASE, introduced with 10gR1, are currently not used. These will be implemented in future releases with new functionality but in 10/11g these are not operational.

So confusing ...

Simple log miner

2010-06-28T22:40:00.002+08:00

本身这个步骤很多高手都已经贴过了，只是我在使用中发现大体上大家写的都有些复杂，于是，我总结了个超级简化版的，方便大家使用：

1.安装LOGMNR包，需要本步骤没什么可多说的，只是需要注意在连接数据库的时候默认最好使用本地验证方式
C:\>sqlplus /nolog
SQL> conn / as sysdba
SQL> @D:\oracle\product\10.2.0\db_2\RDBMS\ADMIN\dbmslm.sql
SQL> @D:\oracle\product\10.2.0\db_2\RDBMS\ADMIN\dbmslmd.sql
SQL> @D:\oracle\product\10.2.0\db_2\RDBMS\ADMIN\dbmslms.sql"
SQL> show parameter utl;

2.创建数据字典
SQL> alter system set utl_file_dir='d:\oracle\logmnr' scope=both;
SQL> EXECUTE dbms_logmnr_d.build('dictionary.ora','d:\oracle\logmnr');

3.添加日志文件
SQL> EXECUTE dbms_logmnr.add_logfile(LogFileName=>'D:\1_15969.dbf',Options=>dbms_logmnr.new);
SQL> EXECUTE dbms_logmnr.add_logfile(LogFileName=>'D:\1_15969.dbf',Options=>dbms_logmnr.addfile);
或
SQL> begin
sys.dbms_logmnr.add_logfile(LogFileName=>'D:\1_15969.dbf',options =>dbms_logmnr.addfile);
end;

4.使用字典分析日志文件
SQL> execute dbms_logmnr.start_logmnr(dictfilename=>'d:\oracle\logmnr\dictionary.ora');

5.查询结果
SQL> select scn,sql_redo from v$logmnr_contents;

6.退出logmnr
SQL> execute dbms_logmnr.end_logmnr;

PS:这里最重要的是第5步，如果结果集很大的话建议使用PL/SQL等工具进行操作，这样便于后期修改，相对于SQLPLUS的格式化输出命令来说使用 PL/SQL DEVELOPER确实能方便很多。

Safety shutdown abort

2010-06-28T22:36:00.002+08:00

以下方式是本人认为比较安全的的shutdown abort步骤，非官方，仅作参考
1. kill all dedicated server process
$kill -9 `ps -ef|grep LOCAL=NO|grep -v grep|awk '{print $9}'`
$ps -ef|grep LOCAL=NO|grep -v grep|awk '{print $9}'|xargs kill -9
可以等等一下事物回滚，呵呵
2.switch log file
sqlplus '/as sysdba'
SQL> alter system switch logfile;
3.checkpoint and suspend IO
SQL> alter system checkpoint;
SQL> alter system suspend;
执行完这个别把这个session终止，也不要执行其他语句，否则可能sqlplus "/as sysdba"上不去了。
4.shutdown db
SQL> shutdown abort
5.restart db
sqlplus '/as sysdba'
SQL> start up
6.review transaction rollback
SQL> select * from V$FAST_START_TRANSACTIONS;
通常shutdown abort不会损坏数据文件，即使损坏，也可能只是一些Block corruption，recove就OK了，更坏的可能是数据文件损坏，可能就需要做恢复了，比较麻烦。
不过我觉得这种方式还比较安全一些吧。
到是对于shutdown immediate不成功的情况，没有按照这个方法试过，呵呵。

redo log size of shrink table

2010-05-22T21:40:00.000+08:00

Table size 3.x Gb during my alter table shrink space compact, the redo log size is about 13Gb.
Be careful if the archived log space is too small.

500强金融行业需要什么样的IT人才

2010-05-22T21:37:00.000+08:00

500强金融行业需要什么样的IT人才
                                    ----访太保集团OCM大师孙俊
  孙俊，作为Oracle技术在全球的顶级认证大师，在中国的人数不超过200人。现在中国太平洋保险集团公司从事数据库的管理工作，保障太平洋保险在中国地区核心业务系统正常运作。曾经管理与参与的重点项目有太平洋保险集团电子商务系统、集团P05 ods数据仓库系统、寿险P10核心业务系统、寿险营销员系统、太平洋保险集团恒生的估值系统等。依托其优秀的Oracle技术能力，为太平洋保险集团中国区业务的正常运行保障护航，为公司创造了巨大的价值。通过多年的职业历练，目前孙俊已成为 Oracle技术领域的大师级别认证高手，也是太平洋保险集团Oracle技术领域不可或缺的顶尖技术高手。

1、孙老师在太保公司已经待了比较长的时间，一方面可以就之前的经历和我们做一些回顾，另一方面，您也给公司的Oracle技术管理这个层面带来了很多有价值的东西，在开始之前孙老师可以聊一下太保这边Oracle大概的情况。
太保目前正在逐步走向国际化，公司目前主要使用的数据库版本有Oracle 、DB2、Informix 、SQL Server、MYSQL，由于Informix 在海量数据方面能力不是很强，所以目前太保集中式的系统都由原来的infomix转换成Oracle。其次，由于Oracle数据库始终坚持走开放路线且对Unix、Linux系统支持较好，而且 Oracle在版本更新这块能够与时俱进，所以太保这边集中式的系统都采用Oracle的体系架构。

2、       现在中国很多大型的集团公司包括垄断性行业用的都是Oracle的产品，Oracle可以说是这个行业的主流了，您能不能大致跟我们谈一下 Oracle技术在未来的一个行业发展趋势？
Oracle分几块，第一块是传统的Oracle技术RAC，第二Oracle在中间件这块的网格计算将来也会得到加强，从Oracle收购BEA之后，可以看到Oracle 在中间件融合方面进步很神速，最近收购SUN之后，基本上完成软件（操作系统、中间件、数据库）、硬件产品等方面的融合。下一步的话，Oracle应该对这些收购的产品做一个产品整合，然后推出一个更加完善的产品或者是解决方案。目前，Oracle主推的Exadata存储服务器就可以看到，它是将Oracle的存储软件和Sun硬件相结合的产物，提升了数据库的存储性能。

3、       像您这样的技术高管工作内容想必很复杂，日程通常也排得很满，您能否描述一下您工作的内容，同时在这么繁重的工作中，您是怎样来规避风险以及处理各种突发性和问题？
对于太平洋保险这样企业级系统和平台，工作重点在于带领团队进行整体技术架构的设计与规划，不仅仅立足于简单的技术层面，需要从保险业务视角去审视合理的、可扩展的技术框架，包括硬件、数据库以及应用层该如何高效地去协同工作，去支撑太保业务的高速发展。我带领整个团队，以Oracle数据库为核心和出发点，进行日常运行流程的制定，巡查数据库的日志、查看监控报告；同时在各个项目启动初期，安排资深团队提前介入，制定一个有针对性的容量规划和物理设计，帮助项目组建立数据库开发规范、项目验收要求，确保系统设计先天品质优良。在日常工作中，带领团队成员共同通过对以往经验的归纳总结，在风险管控和处理时效上找寻平衡点，制定出切实可行的制度，以制度和流程来保证数据库运维工作平稳，其中包括项目验收制度、风险评估流程、变更审批制度等一系列内容，通过这些工作实现运维问题管理，而不是运维危机的管理。
上述工作都需要建立在两个重要基础之上：强有力的技术团队+严谨的工作规范，带领团队对已有技术的精益求精、对新技术的不断尝试，同时通过对以往经验和问题总结归纳，制定切实可行的工作规范，这才有可能使我们的工作进入良性循环。我们的工作不是危机管理，不是制造“惊喜”，应用系统的安全、高效、平稳地运行才是我们的目标。

4、在您这么多年和Oracle 技术打交道的职业生涯中，比如说数据库这块，您认为是开发方向的技术更加核心还是管理方向更加核心？
首先呢，我个人觉得管理方向相对开发来说是更加核心的，但这两者之间是一个密不可分的关系，这就好比是枪和靶子的关系。管理是靶子，开发是枪，先划靶子再打抢，当枪和靶子都有了，就是一个执行的问题，管理的关键不在于知道怎么去做，而在于执行。海尔集团CEO张瑞敏曾经说过，管理的关键不在于知，而在于行。实质上也是强调的一个执行力的问题。

5、在您这么多年和Oracle 技术打交道的职业生涯中，能否分享一下，在技术的学习及提高方面，有什么样的心得和技巧？
唯一有的技巧就是勤奋。每天坚持去学习新的技术和学习资料，当别人在休闲之余，我们要耐得住性子，去钻研技术，勤奋和坚持是很重要的亮点。我始终相信，机会是留给有准备的人。

6、关于Oracle为什么要收购Sun已经谈论了很久，作为客户，您觉得这起收购能够给你们带来哪些方面的影响？
SUN和ORACLE经过了一段比较长的时间，Oracle收购SUN 其实是一个“软硬结合、软硬互补，两条腿走路”的策略，一个数据库的厂商和一个搞服务器的厂商能走在一起，这在以前是很难想象的，但是在去年年底却奇迹般的发生。SUN除了有强大的硬件平台，还有丰富的开源软件产品线。因为Oracle是一个强大的应用数据库平台。两者的结合将会给我们带来更加完整的解决方案，这些方案就包括软件，硬件，甚至操作系统等，这是我感受最深的。

7、您作为公司的资深Oracle技术专家，希望您能跟我们分享一下公司在招募新的员工时，什么样的技术水平比较符合公司对Oracle人才的需求？
公司在招人的时候，特别是应届生，对于这类人的职位，一般定位在助理管理员，技术门槛也较低，关键是要求他们有肯学、肯干的品质。在面试的时候怎么去考量他们有没有这两个品质呢，可以看他们在业余时间去学了什么，而有没有相关证书，对于我们来说可以是一个比较重要的参考依据，能看出他们对于技术这块有没有去付出努力。

8、您本人也参加过很多次Oracle的培训，您觉得培训后对于您个人在IT职业规划上面能起到多大作用？
从刚接触Oracle开始我都是参加比较正规的培训，中国有句老话：“师傅领进门，修行看个人”。参加一个系统的培训，能让我们对数据库有一个系统的认识。培训机构能够给我们的是时间和空间，如果是自己学习的话，很难做到在这段时间内全身心的去关注在学习这块。而培训机构能够让我们几天内全身心投入在这块的学习，并且能够提供一个非常不错的实验环境，这一点对于我们来说是非常有帮助的。

9、对于Oracle认证价值您是怎么看的？包括您本人在招聘新员工的时候对于这些证书是不是一个衡量的指标？
是一个衡量指标，不是一个决定性指标，但有总比没有好，至少代表了这个人学习技术的态度。拥有证书可以让我们看到这个人确实为提高自己的技术付出了努力，那么即使现在他的能力和我们招的职位不是很匹配，我们也会考虑录用这样的年轻人，因为我们相信这个人的学习能力肯定是比较强的。

10、目前Oracle在中国的用户及合作伙伴已经有一万多家，那么未来人才市场对于Oracle人才需求会是一个什么样的局面，能不能请您做个预测。
Oracle在收购了BEA、 Sun 、Peoplesoft等厂商后, Oracle人才的需求肯定会增加，而且还将是一个比较大的需求增长，这和Oracle公司本身发展速度有关，而且会需要更多高、精、尖的人才。包括 Oracle的中间件，ERP也会需要很多的人才，需求量一旦放大之后，那么对于人才的要求就是偏向更高端了。

Learning PL/SQL

2010-04-24T10:15:00.002+08:00

Driven by application PL/SQL troubleshooting, as the Pro*C doesn't work . while succeeds to OPEN CURSOR for a very complex SQL in less than 1 second (manually run it takes few minutes). The processing part is taken care by PRO*C.

From http://docstore.mik.ua/orelly/oracle/langpkt/ch01_09.htm, it says,
"You must open an explicit cursor before you can fetch rows from that cursor. When the cursor is opened, the processing includes the PARSE, BIND, OPEN, and EXECUTE statements. This OPEN processing includes: determining an execution plan, associating host variables and cursor parameters with the placeholders in the SQL statement, determining the result set, and, finally, setting the current row pointer to the first row in the result set. "

However, I doubt "the processing" includes " EXECUTE . Reason being, from my 10046 tracing comparison of without FETCH or with FETCH, there is no EXECUTION PLAN showed for without FETCH.

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

     OPEN emp_cv FOR SELECT first_name, salary FROM employees where employee_id < emp_id;

--WITHOUT fetch

SELECT FIRST_NAME, SALARY
FROM
EMPLOYEES WHERE EMPLOYEE_ID < :B1

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total        2      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 33     (recursive depth: 1)
********************************************************************************

alter session set events '10046 trace name context off'

--corresponding 10046 trace file of WITHOUT FETCH processing

=====================
PARSING IN CURSOR #1 len=65 dep=1 uid=33 oct=3 lid=33 tim=1242222958636520 hv=3716011877 ad='4cd98690'
SELECT FIRST_NAME, SALARY FROM EMPLOYEES WHERE EMPLOYEE_ID < :B1
END OF STMT
PARSE #1:c=0,e=71,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1242222958636514
BINDS #1:
kkscoacd
Bind#0
oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1206001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=f6fe60ac bln=22 avl=03 flg=05
value=106
EXEC #1:c=0,e=144,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1242222958636745
WAIT #3: nam='SQL*Net message to client' ela= 5 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1242222958636793
EXEC #3:c=10000,e=3030,p=0,cr=0,cu=0,mis=1,r=1,dep=0,og=1,tim=1242222958636870
WAIT #3: nam='SQL*Net message from client' ela= 236 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1242222958637166

-- with FETCH processing

    OPEN emp_cv FOR SELECT first_name, salary FROM employees where employee_id < emp_id;
     loop
      fetch emp_cv into emp_dataPkg.er;
     exit when emp_cv%notfound;
      dbms_output.put_line(emp_dataPkg.er.name || ' - ' || emp_dataPkg.er.sal);
    end loop;
   CLOSE emp_cv;

********************************************************************************

SELECT FIRST_NAME, SALARY
FROM
EMPLOYEES WHERE EMPLOYEE_ID < :B1

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        7      0.00       0.00          0         13          0           6
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total        9      0.00       0.00          0         13          0           6

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 33     (recursive depth: 1)

Rows     Row Source Operation
------- ---------------------------------------------------
      6 TABLE ACCESS BY INDEX ROWID EMPLOYEES (cr=13 pr=0 pw=0 time=104 us)
      6   INDEX RANGE SCAN EMP_EMP_ID_PK (cr=7 pr=0 pw=0 time=120 us)(object id 12082)

********************************************************************************

select condition
from
cdef$ where rowid=:1

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          2          0           1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total        3      0.00       0.00          0          2          0           1

Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 2)

Rows     Row Source Operation
------- ---------------------------------------------------
      1 TABLE ACCESS BY USER ROWID CDEF$ (cr=1 pr=0 pw=0 time=67 us)

********************************************************************************

alter session set events '10046 trace name context off';

--corresponding 10046 trace file of WITH FETCH processing

=====================
PARSING IN CURSOR #1 len=65 dep=1 uid=33 oct=3 lid=33 tim=1242222802758295 hv=3716011877 ad='4cd98690'
SELECT FIRST_NAME, SALARY FROM EMPLOYEES WHERE EMPLOYEE_ID < :B1
END OF STMT
PARSE #1:c=0,e=548,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=1242222802758286
=====================
PARSING IN CURSOR #4 len=42 dep=2 uid=0 oct=3 lid=0 tim=1242222802759562 hv=844002283 ad='4cf6f080'
select condition from cdef$ where rowid=:1
END OF STMT
PARSE #4:c=0,e=123,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1242222802759554
BINDS #4:
kkscoacd
Bind#0
oacdty=11 mxl=16(16) mxlc=00 mal=00 scl=00 pre=00
oacflg=18 fl2=0001 frm=00 csi=00 siz=16 off=0
kxsbbbfp=f6f62148 bln=16 avl=16 flg=05
value=00006F44.001D.0001
EXEC #4:c=0,e=362,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1242222802760102
FETCH #4:c=0,e=99,p=0,cr=2,cu=0,mis=0,r=1,dep=2,og=4,tim=1242222802760255
STAT #4 id=1 cnt=1 pid=0 pos=1 obj=31 op='TABLE ACCESS BY USER ROWID CDEF$ (cr=1 pr=0 pw=0 time=67 us)'
BINDS #1:
kkscoacd
Bind#0
oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1206001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=f6f6254c bln=22 avl=03 flg=05
value=106
EXEC #1:c=0,e=3997,p=0,cr=2,cu=0,mis=1,r=0,dep=1,og=1,tim=1242222802762431
FETCH #1:c=0,e=108,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=1,tim=1242222802762631
FETCH #1:c=0,e=24,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=1,tim=1242222802762862
FETCH #1:c=0,e=18,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=1,tim=1242222802762950
FETCH #1:c=0,e=15,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=1,tim=1242222802763030
FETCH #1:c=0,e=14,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=1,tim=1242222802763108
FETCH #1:c=0,e=15,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=1,tim=1242222802763185
FETCH #1:c=0,e=9,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=1,tim=1242222802763257
STAT #1 id=1 cnt=6 pid=0 pos=1 obj=12080 op='TABLE ACCESS BY INDEX ROWID EMPLOYEES (cr=13 pr=0 pw=0 time=104 us)'
STAT #1 id=2 cnt=6 pid=1 pos=1 obj=12082 op='INDEX RANGE SCAN EMP_EMP_ID_PK (cr=7 pr=0 pw=0 time=120 us)'
WAIT #3: nam='SQL*Net message to client' ela= 10 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1242222802763502
EXEC #3:c=10000,e=6638,p=0,cr=15,cu=0,mis=0,r=1,dep=0,og=1,tim=1242222802763589
WAIT #3: nam='SQL*Net message from client' ela= 359 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1242222802764056
=====================

SSH public key authentication

2010-04-20T23:09:00.000+08:00

Finally I configured SSH public key authentication on my Cygwin. So it is so convenient to log on to various Unix servers. :-)

-- on my local PC
$ pwd
/home/liqy/.ssh
$ ls -l
total 8
-rw------- 1 liqy Domain Users 898 2010-04-19 11:09 id_rsa
-rw-r--r-- 1 liqy Domain Users 1196 2010-04-19 10:56 known_hosts
$ ssh rpt02
Last   successful login for liqy: Mon Apr 19 11:10:48 SST-8 2010
Last unsuccessful login for liqy: Tue Mar 16 01:59:06 SST-8 2010
Last login: Mon Apr 19 11:10:48 2010 from 146-105-22.int.m1.com.sg
rpt02@/home/liqy> cd .ssh
-- on remote server
rpt02@/home/liqy/.ssh> ls -lrt
total 64
-rw-------   1 liqy       users         1024 Nov 27 2007 prng_seed
-rw-r--r--   1 liqy       users         9061 Dec 14 09:57 known_hosts
-rw-r--r--   1 liqy       users          220 Apr 1 13:57 authorized_keys

prerequsite of using sql loader direct path loading

2010-04-20T22:34:00.001+08:00

Be careful with using sql*loader, if the target table :

is big and having indexes during data loading with logging = YES.

Reason being during data loading, index becomes UNUSABLE in dba_indexes (remains VALID in dba_objects). After data loading, then start rebuild indexes. When logging=YES, image FULL TABLE SCAN on a huge table, how many archived log we shall we ? multiply by number of indexes ... It is a big shock. and how much more time it will take , assuming the job loads multiple files , each invoke of sql*loader loads one file only .

The common understanding of sql*load

Use Direct Path Loads - The conventional path loader essentially loads the data by using standard insert statements. The direct path loader (direct=true) loads directly into the Oracle data files and creates blocks in Oracle database block format. The fact that SQL is not being issued makes the entire process much less taxing on the database. There are certain cases, however, in which direct path loads cannot be used (clustered tables). To prepare the database for direct path loads, the script $ORACLE_HOME/rdbms/admin/catldr.sql.sql must be executed.

In my case, apps team is not happy with 5 minutes performance, hence added "direct=ture", in the end caused archive log disk space full, job can't finish after running for 3 hours, generated 40+Gb archived log until archiver log hang.

During indexes rebuild after "direct=ture", see tremendous I/O incurred.

Report of conventional loading
Top 5 Timed Events

Event    Waits    Time(s)    Avg Wait(ms)    % Total Call Time    Wait Class
db file parallel write    6,358    240    38    97.5    System I/O
db file sequential read    44,238    128    3    52.0    User I/O
CPU time         115         46.7
log file parallel write    11,538    79    7    32.1    System I/O
log file sync    11,094    76    7    30.9    Commit

Tablespace    Reads    Av Reads/s    Av Rd(ms)    Av Blks/Rd    Writes    Av Writes/s    Buffer Waits    Av Buf Wt(ms)
IDX     40,216     11     2.84     1.00     99,853     28     0     0.00

report of direct path loading
Top 5 Timed Events

Event   Waits   Time(s)   Avg Wait(ms)   % Total Call Time   Wait Class
db file sequential read   2,901,803   1,610   1   42.4   User I/O
log file parallel write   22,845   1,410   62   37.1   System I/O
CPU time      729      19.2
Log archive I/O   46,028   455   10   12.0   System I/O
log file sequential read   22,795   87   4   2.3   System I/O

Tablespace IO Stats

    * ordered by IOs (Reads + Writes) desc

Tablespace   Reads   Av Reads/s   Av Rd(ms)   Av Blks/Rd   Writes   Av Writes/s   Buffer Waits   Av Buf Wt(ms)
IDX    2,866,984    794    0.52    1.00    427,225    118    0    0.00


rebuild indexes takes more time than gained from data loading part. Below is the figure I just used to rebuild unusable indexes for surepay04 tables, took near 10 minutes for nonvoice04 for each invoke of sql*loader, event with rebuild parallel.

    For the remedy, after I remove those backed up archived log, set relevant tables and indexes to NOLOGGING mode. Ask application team to remove "deirct=true".

> select index_name, status from dba_indexes where index_name like 'M1_SUREPAY_%04%_IDX%' ;

INDEX_NAME                     STATUS
------------------------------ --------
NONVOICE04_IDX1     UNUSABLE
NONVOICE04_IDX2     UNUSABLE
NONVOICE04_IDX4     UNUSABLE
VOICE04_IDX1        VALID
VOICE04_IDX2        VALID
VOICE04_IDX3        VALID
VOICE04_IDX4        VALID
OTHER04_IDX2        VALID
OTHER04_IDX3        VALID
OTHER04_IDX1        VALID
NONVOICE04_IDX3     UNUSABLE

11 rows selected.

> alter index NONVOICE04_IDX1 rebuild parallel 3 ;

Index altered.

Elapsed: 00:02:43.39
> alter index NONVOICE04_IDX2 rebuild parallel 3 ;

Index altered.

Elapsed: 00:02:40.96
> alter index NONVOICE04_IDX3 rebuild parallel 3 ;

Index altered.

Elapsed: 00:02:23.70
> alter index NONVOICE04_IDX4 rebuild parallel 3 ;

Index altered.

alter index NONVOICE04_IDX1 noparallel ;
alter index NONVOICE04_IDX2 noparallel ;
alter index NONVOICE04_IDX3 noparallel ;
alter index NONVOICE04_IDX4 noparallel ;

some info about datapump import

2010-04-20T12:08:00.001+08:00

data pump is much efficient than imp/exp , which help me import 19Gb within one hour without generating redo logs.

LIQY@ADMP> @chk_session

SUBSTR(USERNAME,1,15)                                SID    SERIAL# SQL_HASH_VALUE SERVER    STATUS   OSUSER
--------------------------------------------- ---------- ---------- -------------- --------- -------- ------------------------------
SUBSTR(MACHINE,1,20)
------------------------------------------------------------
SUBSTR(PROGRAM,1,25)                                                        TO_CHAR(LOGON_TIME,'DD-MON CPU_MINUTES
--------------------------------------------------------------------------- -------------------------- -----------
SYSMAN                                              1079          9     4281219134 DEDICATED ACTIVE
bill07
OMS                                                                         08-apr-2010 02:09:47        2.56233333

DBSNMP                                              1101          3              0 DEDICATED ACTIVE   oracle1
bill07
emagent@bill07 (TNS V1-V3                                                   08-apr-2010 02:11:20        2.78166667

SYS                                                  187      64288     3732391352 DEDICATED ACTIVE   oracle1
bill07
udi@bill07 (TNS V1-V3)                                                      19-apr-2010 15:54:17              .011

SYS                                                  269      16294              0 DEDICATED ACTIVE   oracle1
bill07
oracle@bill07 (DM00)                                                        19-apr-2010 15:54:17        .011833333

SYS                                                 1069       3981              0 DEDICATED ACTIVE   oracle1
bill07
oracle@bill07 (DW01)                                                        19-apr-2010 15:54:18        1.69966667

LIQY                                                 359         32     2579827540 DEDICATED ACTIVE   liqy
bill07
sqlplus@bill07 (TNS V1-V3                                                   19-apr-2010 15:55:18              .002

6 rows selected.

--SID 187, 269 and 1069 belong to datapump import, directly write to data file by DB writer.

LIQY@ADMP> @chk_session_wait

       SID SUBSTR(EVENT,1,25)                                                                  P1 SUBSTR(P1TEXT,1,10)
---------- --------------------------------------------------------------------------- ---------- ------------------------------
        P2 SUBSTR(P2TEXT,1,10)                    P3 SUBSTR(P3TEXT,1,10)
---------- ------------------------------ ---------- ------------------------------
       187 wait for unread message o                                                   1.3835E+19 channel co
1.3835E+19 channel ha                              0

       269 wait for unread message o                                                   1.3835E+19 channel co
1.3835E+19 channel ha                              0

       283 Streams AQ: qmn slave idl                                                            0
         0                                         0

       534 Streams AQ: waiting for t                                                            0
         0                                         0

       536 Streams AQ: qmn coordinat                                                            0
         0                                         0

      1017 Streams AQ: qmn slave idl                                                            0
         0                                         0

      1069 Datapump dump file I/O                                                               1 count
       256 intr                           4294967295 timeout

      1079 wait for unread message o                                                   1.3835E+19 channel co
1.3835E+19 channel ha                              0

      1101 Streams AQ: waiting for m                                                       602323 queue id
1.3835E+19 process#                                5 wait time

      1102 Streams AQ: waiting for t                                                            0
         0                                         0

10 rows selected.

--SID 187, 269 and 1069 belong to datapump import

2. insert data first , then rebuild indexes. Howerver, during data is written to data file, indexes remain valid and usable (not like directly path loading by sql*loader)

LIQY@ADMP> select segment_name, bytes from dba_segments where segment_name like '%NONVOICE03%';

SEGMENT_NAME                                                                           BYTES
--------------------------------------------------------------------------------- ----------
IMPDP_M1_SUREPAY_NONVOICE03                                                           524288
M1_SUREPAY_NONVOICE03                                                             1.5781E+10
M1_SUREPAY_NONVOICE03_IDX1                                                        1090519040
M1_SUREPAY_NONVOICE03_IDX2                                                        1237319680
M1_SUREPAY_NONVOICE03_IDX3                                                          20971520
M1_SUREPAY_NONVOICE03_IDX4                                                          20971520

6 rows selected.

--notice the indexes's size keep changing one by one.
LIQY@ADMP> /

SEGMENT_NAME                                                                           BYTES
--------------------------------------------------------------------------------- ----------
M1_SUREPAY_NONVOICE03                                                             1.5781E+10
M1_SUREPAY_NONVOICE03_IDX1                                                        1090519040
M1_SUREPAY_NONVOICE03_IDX2                                                        1384120320
M1_SUREPAY_NONVOICE03_IDX3                                                        1048576000
M1_SUREPAY_NONVOICE03_IDX4                                                         901775360

-rw-rw----   1 oracle1    dba1       26440704 Apr 19 13:31 ADMP_0001_525208771_80943.arc
-rw-rw----   1 oracle1    dba1         55296 Apr 19 13:31 ADMP_0001_525208771_80944.arc
-rw-rw----   1 oracle1    dba1       24170496 Apr 19 13:59 ADMP_0001_525208771_80945.arc
drwxrwxr-x   2 oracle1    dba1         16384 Apr 19 14:22 oldarch
-rw-rw----   1 oracle1    dba1       195064832 Apr 19 16:25 ADMP_0001_525208771_80946.arc

--no log file generated

-rw-rw-r--   1 oracle1    dba1       13533204480 Apr 19 15:32 expdp_M1_SUREPAY_NONVOICE03.dmp
-rw-rw-r--   1 oracle1    dba1       698085376 Apr 19 15:32 expdp_M1_SUREPAY_OTHER03.dmp
-rw-rw-r--   1 oracle1    dba1       19535446016 Apr 19 15:38 expdp_M1_SUREPAY_VOICE03.dmp

bill07:ADMP:/csa074/oradata/surepayimp> cat impdp_M1_SUREPAY_VOICE03.par
userid='/ as sysdba'
directory=surepay_dump_dir
dumpfile=expdp_M1_SUREPAY_VOICE03.dmp
logfile=impdp_M1_SUREPAY_VOICE03.log
tables=dbam1.M1_SUREPAY_VOICE03
CONTENT=data_only    # whit table & indexes exist (truncate before import)
#CONTENT=metadata_only
#REMAP_SCHEMA=dbam1:liqy
REMAP_TABLESPACE=surepay_tbl:m1_surepay_tbl
REMAP_TABLESPACE=surepay_idx:m1_surepay_idx
job_name=impdp_M1_SUREPAY_VOICE03

bill07:ADMP:/csa074/oradata/surepayimp> ll expdp_M1_SUREPAY_VOICE03.dmp
-rw-rw-r--   1 oracle1    dba1       19535446016 Apr 19 15:38 expdp_M1_SUREPAY_VOICE03.dmp
bill07:ADMP:/csa074/oradata/surepayimp> impdp parfile=impdp_M1_SUREPAY_VOICE03.par^C
bill07:ADMP:/csa074/oradata/surepayimp> cat impdp_M1_SUREPAY_NONVOICE03.log
;;;
Import: Release 10.2.0.2.0 - 64bit Production on Monday, 19 April, 2010 15:54:17

Copyright (c) 2003, 2005, Oracle. All rights reserved.
;;;
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning and Data Mining options
Master table "SYS"."IMPDP_M1_SUREPAY_NONVOICE03" successfully loaded/unloaded
Starting "SYS"."IMPDP_M1_SUREPAY_NONVOICE03": parfile=impdp_M1_SUREPAY_NONVOICE03.par
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "DBAM1"."M1_SUREPAY_NONVOICE03"             12.60 GB 41019272 rows
Job "SYS"."IMPDP_M1_SUREPAY_NONVOICE03" successfully completed at 16:25:32

bill07:ADMP:/csa074/oradata/surepayimp> impdp parfile=impdp_M1_SUREPAY_VOICE03.par

Import: Release 10.2.0.2.0 - 64bit Production on Monday, 19 April, 2010 16:53:35

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning and Data Mining options
Master table "SYS"."IMPDP_M1_SUREPAY_VOICE03" successfully loaded/unloaded
Starting "SYS"."IMPDP_M1_SUREPAY_VOICE03": parfile=impdp_M1_SUREPAY_VOICE03.par
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "DBAM1"."M1_SUREPAY_VOICE03"                18.19 GB 57052084 rows
Job "SYS"."IMPDP_M1_SUREPAY_VOICE03" successfully completed at 17:41:12

11g sqlplus doesn't hide password

2010-04-18T15:08:00.000+08:00

Prior to 10g Oracle database, anyone can see the database account password using 'ps -ef |grep sqlplus ' , if it is hard coded on same command line with sqlplus, e.g sqlplus username/password@DBNAME

In 10g , Oracle "solved" this problem ,.

However, it comes back again in 11g. Searched metalink, no more document says password will be removed. :(

Too bad. Lucky, we have alternative way.

> ps -ef |grep sqlplus
oravas11 4997 4748 0 13:41:31 pts/0 0:00 ied sqlplus perfstat/xxxxxxxx@VAS11GP
oravas11 4998 4997 0 13:41:31 ttyp1 0:00 sqlplus perfstat/xxxxxxxx@VAS11GP

two cases of missing indexes

2010-04-18T15:01:00.000+08:00

Case 1:

SQL> select status, count(*) from dbaantispam.tb_sms_out group by status;

    STATUS   COUNT(*)
---------- ----------
         1     112459

SQL> alter session set current_schema=dbaantispam;

Session altered.

SQL> set autotrace on
SP2-0613: Unable to verify PLAN_TABLE format or existence
SP2-0611: Error enabling EXPLAIN report

cd $ORACLE_HOME/rdbms/admin/

SQL> @utlxplan

Table created.

SQL> set autotrace on

SQL> conn /
Connected.
SQL> set autotrace on

SQL> SELECT sms_out_id, text, msisdn, messagetype FROM dbaantispam.tb_sms_out WHERE status = 0 ORDER BY sms_out_id ASC;

no rows selected

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TB_SMS_OUT'
   2    1     INDEX (FULL SCAN) OF 'SMS_OUT_ID_PK' (UNIQUE)

Statistics
----------------------------------------------------------
        248 recursive calls
          0 db block gets
      31466 consistent gets
       3072 physical reads
          0 redo size
        467 bytes sent via SQL*Net to client
        461 bytes received via SQL*Net from client
          1 SQL*Net roundtrips to/from client
          6 sorts (memory)
          0 sorts (disk)
          0 rows processed

As you can see there are lot of consistent reads and physical reads .

SQL> @chk_indcol
Enter value for tbl: TB_SMS_OUT
old   1: select index_name,column_name,column_position from dba_ind_columns where table_name=upper('&tbl')
new   1: select index_name,column_name,column_position from dba_ind_columns where table_name=upper('TB_SMS_OUT')

INDEX_NAME                     COLUMN_NAME          COLUMN_POSITION
------------------------------ -------------------- ---------------
SMS_OUT_ID_PK                  SMS_OUT_ID                         1

-- No index found on STATUS column
SQL> create index dbaantispam.tb_sms_out_statusidx on tb_sms_out(status) tablespace antispam_idx;

Index created.

SQL> SELECT sms_out_id, text, msisdn, messagetype FROM dbaantispam.tb_sms_out WHERE status = 0 ORDER BY sms_out_id ASC;

no rows selected

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (ORDER BY)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'TB_SMS_OUT'
   3    2       INDEX (RANGE SCAN) OF 'TB_SMS_OUT_STATUSIDX' (NON-UNIQ
          UE)

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
          2 consistent gets
          1 physical reads
          0 redo size
        467 bytes sent via SQL*Net to client
        461 bytes received via SQL*Net from client
          1 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)
          0 rows processed

2 consistent reads and 1 physical reads only.

Case 2 :

alter session set current_schema=dbamsgctr;

SQL> set timing on
SQL> set pages 1000
SQL> SELECT '11:00-11:59', COUNT(destination_type) FROM dbamsgctr.tb_msg_transaction
2   WHERE destination_type = 0 AND msg_type LIKE '%SMS%'
3   AND transaction_timestamp >= TO_DATE('2010-03-16 11:00:00', 'yyyy-mm-dd hh24:mi:ss')
4   AND transaction_timestamp <= TO_DATE('2010-03-16 11:59:59', 'yyyy-mm-dd hh24:mi:ss');

'11:00-11:5 COUNT(DESTINATION_TYPE)
----------- -----------------------
11:00-11:59                       0

Elapsed: 00:07:21.94

Execution Plan
----------------------------------------------------------

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
     380503 consistent gets
     380195 physical reads
          0 redo size
        586 bytes sent via SQL*Net to client
        656 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

Huge amounts of logical reads, because of this statement, during 3 hours , the "Buffer Cache Reads" is 278.4G

SQL> set timing on pages 1000
SQL> create index msg_idx2 on tb_msg_transaction(transaction_timestamp) tablespace msgctr_idx nologging parallel (degree 3 );

Index created.

Elapsed: 00:14:55.86

alter index msg_idx2 logging noparallel;

SQL> set autotrace on
SQL> SELECT '11:00-11:59', COUNT(destination_type) FROM dbamsgctr.tb_msg_transaction
2   WHERE destination_type = 0 AND msg_type LIKE '%SMS%'
3   AND transaction_timestamp >= TO_DATE('2010-03-16 11:00:00', 'yyyy-mm-dd hh24:mi:ss')
4   AND transaction_timestamp <= TO_DATE('2010-03-16 11:59:59', 'yyyy-mm-dd hh24:mi:ss');

'11:00-11:5 COUNT(DESTINATION_TYPE)
----------- -----------------------
11:00-11:59                       0

Elapsed: 00:00:02.81

Execution Plan
----------------------------------------------------------

Statistics
----------------------------------------------------------
        164 recursive calls
          0 db block gets
         28 consistent gets
          4 physical reads
          0 redo size
        586 bytes sent via SQL*Net to client
        656 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          6 sorts (memory)
          0 sorts (disk)
          1 rows processed

Tremendous improvement !      minutes of run becomes 3 seconds !!!
Again , housekeeping and Oracle developer understands how Oracle database works is important.

blog templated widened

2010-04-18T14:26:00.002+08:00

Finally I widened this blog's layout , which is using template rounders 4, leave those .gif picture unchanged. (Lazy to download it from blogblog.com and customize them and upload to 3rd party web ).

Main time is spent on removing the post's right sidebar -- looks like a vertical line. Hence, searching on keyword "width" throughout the xml file. Finally identify it a GIF picture from http://www.blogblog.com/rounders4/rails_main.gif , as the "width" is fixed inside a picture.

My solution is to comment it .

/* background:url("http://www.blogblog.com/rounders4/rails_main.gif") repeat-y $startSide; */

References:

http://www.bloggerguide.lk/2008/04/rounders-with-wider-post-area.html

How To Change The Width Of The Blogger Templates

http://blogger-and-money.blogspot.com/2007/06/how-to-change-width-of-blogger.html

Rounders 4 with a wider post area

http://www.bloggerguide.lk/2008/11/rounders-4-with-wider-post-area.html

Key Words: widen blogger.com template rounder4

Private db link

2010-04-16T23:56:00.000+08:00

private link can be created/dropped by owner only, it can't be achieved by using below command.

alter session set current_schema= ;

cluttered gviw backup files

2010-04-16T23:38:00.000+08:00

Below info is from http://my.opera.com/peterchenadded/blog/2008/12/27/gvim-7-1-swap-and-backup-files, which puzzled me few years.

A swap file is a file that contains changes made to the original file. it matches *.swp.
A backup file is created right after saving changes to a newly opened file. it is a copy of the file before changing the file. it matches *~ where the * is the original filename.
So logically, by definition you can put them together to make the original or original plus some or all changes made.
source: http://www.ph.unimelb.edu.au/~ssk/vim/recover.html

Normally, both files appear in the directory of the file being edited. This can make the directory very cluttered, so would be better to remove it some where else. To do this use

set directory=c:/vim_swap_files
set backupdir=c:/vim_swap_files

You will need to create the vim_swap_files folder before it will work.
Add the lines to the c:\program files\vim\_vimrc file or in vim edit -> startup settings and add the lines and you will never have to worry about it again.

Human Error Preventives

2010-04-16T23:15:00.000+08:00

Being a DBA or system administrator, simple ENTER may bring us into a nightmare.

It is unavoidable, however what we can do is to prevent it from happening at low probability as possible.

Good habit should help us.
*** This thread would be updated on-going , any comments are welcome.

1. shell environment settings.
Today I notice below are mentioned in cygwin's .bashrc

# Some example alias instructions
# If these are enabled they will be used instead of any instructions
# they may mask. For example, alias rm='rm -i' will mask the rm
# application. To override the alias instruction use a \ before, ie
# \rm will call the real rm not the alias.

# Interactive operation...
alias rm='rm -i'
alias cp='cp -i'
alias mv='mv -i'

Good ! In real world practices, I'd like these options to be there.

To be continued ....

Interesting to see a from from Amazon, menting Human Error in Medicine, however looks not any for IT industry yet.

Using rxvt in Cygwin

2010-04-16T22:47:00.005+08:00

What is rxvt ?

Here it is http://infrablue.tripod.com/cygwin.html , notice the installation part, it is under Shells: rxvt .

And I get this command

C:\cygwin\bin\rxvt.exe -sl 1500 -fn "Lucida Console-12" -bg black -fg grey -sr -e bash --login -i

However, when I follow it , the terminal crash upon click (window open and immediately disappear . It is also reported here : http://cygwin.com/ml/cygwin-xfree/2006-07/msg00146.html , saying crash with "-e" option (not true to my case , find answer below ) .

Finally , I get below command from http://www.khngai.com/emacs/bash.php

D:\Programs\Cygwin\bin\rxvt.exe -sl 500 -fn courier -bg wheat
-sr -title "Bash Sailor" -e D:\Programs\Cygwin\bin\bash.exe

--rcfile %HOME%\.bashrc

But , it is still not working for me, however slightly better . Window remains open with error message complaining .bashrc.

Comparing above two commands carefully, finally I get my working version, which is need to specify full path for bash.exe

c:\cygwin\bin\rxvt.exe -sl 25000 -fn 'Lucida Console-16' -bg wheat -sr -title "Bash Sailor" -e c:\cygwin\bin\bash.exe --login -i

And my final cygwin.bat is like this.

-- content of cygwin.bat

@echo off
C:
chdir C:\cygwin\bin

set EDITOR=vi
set VISUAL=vi
set CYGWIN=codepage:oem tty binmode title

rem change font size below
rem c:\cygwin\bin\rxvt.exe -sl 5000 -fn courier -bg wheat -sr -title "Bash Sailor" -e c:\cygwin\bin\bash.exe --login -i

rem c:\cygwin\bin\rxvt.exe -sl 25000 -fn 'Courier New-18' -bg wheat -sr -title "Bash Sailor" -e c:\cygwin\bin\bash.exe --login -i
c:\cygwin\bin\rxvt.exe -sl 25000 -fn 'Lucida Console-16' -bg wheat -sr -title "Bash Sailor" -e c:\cygwin\bin\bash.exe --login -i

-- end of content of cygwin.bat

*** -sl 25000 : stands for number of scrollback lines, 25000 lines here
*** -fn 'Lucida Console-16' : font Lucida Console with size 16, change it to suit you best .

References:

http://www.khngai.com/emacs/bash.php
http://www.petersblog.org/node/1583
http://cygwin.com/ml/cygwin/2005-09/msg00741.html
http://c2.com/cgi/wiki?BetterCygwinTerminal
Configuring the Command Prompt Window http://commandwindows.com/configure.htm

sqlplus command wrapper for cygwin rxvt (terminal)

2010-04-16T22:01:00.002+08:00

From limited scrollback lines of ugly MS-DOS COMMAND prompt, I link to cywin bash shell, later on I found rxvt (ouR eXended Virtual Terminal), then I started thinking using local sqlplus without SSH logon to remote database server, finally I realize that I need command wrapper rlwrap, which the exactly same as what I use in Linux.

Without rlwrap , you can see the funny problem with UP, DOWN key as described in http://www.cygwin.com/ml/cygwin/2007-10/msg00465.html

To install rlwrap in Cygwin, choose rlwrap under Utils packages of cygwin setup program. Now try :

$ sqlplus perfstat/XXXX@ICTD
/usr/bin/rlwrap.exe: error while loading shared libraries: cygreadline6.dll: cannot open shared object file: No such file or directory

oops !

search "cygreadline*.*" under c:\cygwin, found cygreadline7.dll under c:\cygwin\bin

Search "readline" again in cgywin setup program, found there are version5, 6,7 dll of cyglibreadline under Lib packages, so I install version 6.

This time, it works !!!

Search c:\cygwin folder again , see cygreadline6.dll, cygreadline7.dll under c:\cygwin\bin

Finally, I define below in .bashrc
alias sqlplus='rlwrap sqlplus'

It is exact what I have in Linux environment. :)

Next step, I will configure using Public Key for SSH authentication on local PC , so as not to bother with password for tens of logons everyday.

Reference:

http://www.cygwin.com/ml/cygwin/2007-10/msg00465.html
http://infrablue.tripod.com/cygwin.html

How high sysem load can it be from uptime command

2010-03-20T19:07:00.002+08:00

Here it is , after a command to stop diagmond (HP-UX) and many stm process come up, DISASTER comes ...

> uptime
10:59am up 7 days, 21:45, 4 users, load average: 162.51, 64.13, 24.67
> uptime
^C
> uptime
11:00am up 7 days, 21:46, 5 users, load average: 170.60, 77.04, 30.64

--------------------------------------------------------------------------------
Cpu Util S SU U |100% 41% 100%
Disk Util | 0% 1% 9%
Mem Util S SU UF F | 32% 32% 32%
Swap Util U UR R | 17% 17% 17%
--------------------------------------------------------------------------------
PROCESS LIST Users= 4
User CPU % Thrd Disk Memory Block
Process Name PID Name ( 400% max) Cnt IOrate RSS/VSS On
--------------------------------------------------------------------------------
stm 1552 root 9.2 1 0.0 1.6mb 1.8mb PRI
stm 27516 root 9.0 1 0.0 1.6mb 1.8mb SLEEP
stm 28757 root 6.5 1 0.0 1.6mb 1.8mb PRI
stm 10511 root 5.9 1 0.0 1.6mb 1.8mb PRI
stm 28247 root 3.6 1 0.0 1.6mb 1.9mb PRI
stm 4899 root 3.4 1 0.0 1.6mb 1.8mb SLEEP
stm 12180 root 3.4 1 0.0 1.6mb 1.8mb SLEEP
stm 12471 root 3.1 1 0.0 1.6mb 1.8mb PRI
stm 26386 root 3.1 1 0.0 1.6mb 1.8mb SLEEP
stm 1811 root 2.5 1 0.0 1.6mb 1.9mb PRI
stm 26596 root 2.5 1 0.0 1.6mb 1.8mb PRI
\