Monday, September 29, 2008

Locking and unlocking Oracle optimizer statistics

Last Saturdy night, I supported a Change Request which involved copying the statistics from a populated partition in the partitioned table to partitions currently showing zero rows. Such change would be made for about 24 partitioned tables . Script to implement this change was provided by the requestor. However, when I run the script, just for the first partitioned table in the script, I recived the following error:

declare
*
ERROR at line 1:
ORA-00081: address range [0x60000000000D5E80, 0x60000000000D5E84) is not
readable
ORA-00600: internal error code, arguments: [4832], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_STATS", line 6518
ORA-06512: at "SYS.DBMS_STATS", line 7254
ORA-06512: at line 30


Note: dababase version: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit

I have no idea at all about this. Later, the requester suggested to unlock the statistics before running the script. After unlocking the schema statistics, the script went through. I don't know lock and unlock schema or table statistics before, so I decided to explore this feature a little bit.

First of all, in the Oracle documentation:
Statistics for a table or schema can be locked. Once statistics are locked, no modifications can be made to those statistics until the statistics have been unlocked. These locking procedures are useful in a static environment in which you want to guarantee that the statistics never change.

The DBMS_STATS package provides two procedures for locking and two procedures for unlocking statistics:

LOCK_SCHEMA_STATS

LOCK_TABLE_STATS

UNLOCK_SCHEMA_STATS

UNLOCK_TABLE_STATS

From another Oracle do:

The LOCK_* procedures either freeze the current set of the statistics or to keep the statistics empty (uncollected).When statistics on a table are locked, all the statistics depending on the table, including table statistics, column statistics, histograms and statistics on all dependent indexes, are considered to be locked.

I build up a test case trying to simulate the situaltion. However, I got more descriptive error message instead of the one presented above: (my test db is 32-bit 10g)


denis@TEST10G> @lock_stats2
denis@TEST10G> set echo on
denis@TEST10G> rem lock_stats2.sql
denis@TEST10G> rem -- simulate copy stats from one partition to other partitions that have zero rows
denis@TEST10G> rem
denis@TEST10G>
denis@TEST10G> drop table t purge;

Table dropped.

denis@TEST10G> drop table my_stat_tab purge;

Table dropped.

denis@TEST10G>
denis@TEST10G> create table t nologging
2 partition by range(id)
3 (
4 partition p1 values less than (100),
5 partition p2 values less than (200),
6 partition p3 values less than (300),
7 partition p4 values less than (400),
8 partition p5 values less than (500),
9 partition p_maxval values less than (MAXVALUE)
10 )
11 as
12 select
13 rownum id, a.*
14 from dba_objects a
15 where rownum < 1000;

Table created.

denis@TEST10G>
denis@TEST10G>
denis@TEST10G> Prompt >>> only analyze one partition
>>> only analyze one partition
denis@TEST10G> exec dbms_stats.gather_table_stats(user, 'T', 'P1');

PL/SQL procedure successfully completed.

denis@TEST10G>
denis@TEST10G> select partition_name, num_rows from user_tab_partitions where table_name='T';

PARTITION_NAME NUM_ROWS
------------------------------ ----------
P1 99
P2
P3
P4
P5
P_MAXVAL

6 rows selected.

denis@TEST10G>
denis@TEST10G>
denis@TEST10G>
denis@TEST10G> Prompt >>> in the case of table stats is locked
>>> in the case of table stats is locked
denis@TEST10G> exec dbms_stats.lock_table_stats(user, 'T');

PL/SQL procedure successfully completed.

denis@TEST10G>
denis@TEST10G> exec dbms_stats.create_stat_table(user,'my_stat_tab')

PL/SQL procedure successfully completed.

denis@TEST10G>
denis@TEST10G>
denis@TEST10G> begin
2 for part_rec in ( select partition_name from user_tab_partitions where table_name= 'T'
3 and nvl(num_rows,0) <= 75) loop
4 begin
5 dbms_stats.export_table_stats(user,'T', null, 'my_stat_tab',null, false,null);
6 execute immediate 'update my_stat_tab set c2='||''''||part_rec.partition_name||''''|| ' where c1 = ''T''';
7 dbms_stats.import_table_stats(user,'T', null, 'my_stat_tab',null, false,null);
8 end;
9 end loop;
10 end;
11 /
begin
*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 1607
ORA-06512: at "SYS.DBMS_STATS", line 2117
ORA-06512: at "SYS.DBMS_STATS", line 7249
ORA-06512: at line 7


denis@TEST10G> select partition_name, num_rows from user_tab_partitions where table_name='T';

PARTITION_NAME NUM_ROWS
------------------------------ ----------
P1 99
P2
P3
P4
P5
P_MAXVAL

6 rows selected.

denis@TEST10G>
denis@TEST10G> Prompt >>> in the case of table stats is unlocked
>>> in the case of table stats is unlocked
denis@TEST10G> exec dbms_stats.unlock_table_stats(user, 'T');

PL/SQL procedure successfully completed.

denis@TEST10G>
denis@TEST10G> begin
2 for part_rec in ( select partition_name from user_tab_partitions where table_name= 'T'
3 and nvl(num_rows,0) <= 75) loop
4 begin
5 dbms_stats.export_table_stats(user,'T', null, 'my_stat_tab',null, false,null);
6 execute immediate 'update my_stat_tab set c2='||''''||part_rec.partition_name||''''|| ' where c1 = ''T''';
7 dbms_stats.import_table_stats(user,'T', null, 'my_stat_tab',null, false,null);
8 end;
9 end loop;
10 end;
11 /

PL/SQL procedure successfully completed.

denis@TEST10G> select partition_name, num_rows from user_tab_partitions where table_name='T';

PARTITION_NAME NUM_ROWS
------------------------------ ----------
P1 99
P2 99
P3 99
P4 99
P5 99
P_MAXVAL 99

6 rows selected.

denis@TEST10G> spool off






The following test shows that we can query the stattype_locked column of user_tab_statistics to know if the table stats is locked or not:


denis@TEST10G> exec dbms_stats.lock_table_stats(user, 'T');

PL/SQL procedure successfully completed.

denis@TEST10G> select table_name,partition_name, stattype_locked from user_tab_statistics;

TABLE_NAME PARTITION_NAME STATT
------------------------------ ------------------------------ -----
T ALL
MY_STAT_TAB
T P_MAXVAL ALL
T P5 ALL
T P4 ALL
T P3 ALL
T P2 ALL
T P1 ALL

8 rows selected.

denis@TEST10G> exec dbms_stats.unlock_table_stats(user, 'T');

PL/SQL procedure successfully completed.

denis@TEST10G> select table_name,partition_name, stattype_locked from user_tab_statistics;

TABLE_NAME PARTITION_NAME STATT
------------------------------ ------------------------------ -----
T
MY_STAT_TAB
T P_MAXVAL
T P5
T P4
T P3
T P2
T P1

8 rows selected.

Tuesday, September 23, 2008

Troubleshooting ORA-16191 : Primary log shipping client not logged on standby

I recieved an error ORA-16191 from email alert tonight when I was on-call. I searched the internet immediately, found that:

---
ORA-16191: Primary log shipping client not logged on standby
Cause: An attempt to ship redo to standby without logging on to standby or with invalid user credentials.
Action: Check that primary and standby are using password files and that both primary and standby have the same SYS password. Restart primary and/or standby after ensuring that password file is accessible and REMOTE_LOGIN_PASSWORDFILE initialization parameter is set to SHARED or EXCLUSIVE
---

I checked the alert log file of the primary database, found that the alert starting from 1:18




Tue Sep 23 01:18:15 2008
Error 1017 received logging on to the standby
------------------------------------------------------------
RA-16191: Primary log shipping client not logged on standby
PING[ARC3]: Heartbeat failed to connect to standby 'PS4008A.world'. Error is 16191.
Tue Sep 23 01:23:16 2008
Error 1031 received logging on to the standby
Tue Sep 23 01:23:16 2008
Errors in file /logs/ORACLE/MYDBNAME/bdump/pphi08a_arc3_2846.trc:
ORA-01031: insufficient privileges
PING[ARC3]: Heartbeat failed to connect to standby 'PS4008A.world'. Error is 1031.

Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
returning error ORA-16191
------------------------------------------------------------



I also checked the alert log file of the standby database, find that the someone probably was doing shutdown/start the standby db



-----
Tue Sep 23 02:00:33 2008
Physical Standby Database mounted.
Completed: alter database mount standby database
Tue Sep 23 02:00:33 2008
Physical Standby Database mounted.
Completed: alter database mount standby database
Tue Sep 23 02:01:26 2008
alter database recover managed standby database parallel 16 disconnect
Tue Sep 23 02:01:26 2008
alter database recover managed standby database parallel 16 disconnect
---



The timestamp of the pmon process also indicated it was just started tonight.



$ ps -ef grep pmon
oracle 23059 1 0 01:59:26 ? 0:00 ora_pmon_PS4008A



Another team member explained to me that we change the SYS password every 2 or 3 months, however,
when we change for the primary database, we don't change for the standby database. So after standby db down and up, Oracle trying to connect the standby using the new password, but the password file in the standby db site still contains old password. This is the reason that I saw the error.

Based on what he said and the problem observed, it looks to me that somehow there is a "connection" from primary db to the standby db using the password in the password files and the two password files have to be in sync to enable such a connection. When we changed the sys password in the primary only, the password files are out of sync. So when standby is bounced, this "connection" need to be re-established but it can not because the out of sync situation.

I thus used the "orapwd" command to create a new password file on the standby site. Problem resolved. No such alerts recieved again.

Saturday, September 20, 2008

Oracle 10g - Purge Recyclebin



denis@test10g> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$4Pq19seDSl6+wC0qPEIl8g==$0 TABLE
BIN$5Axx4CZGRuSCF/IRkjHSAQ==$0 TABLE
BIN$ChfXT4unSY+zQgw7Voq1uQ==$0 TABLE
BIN$5LE/p9ZnRXaEWOMM9P02Ow==$0 TABLE
BIN$dl4rs24DSku72JGFBT3CVA==$0 TABLE
BIN$/DNAvyGPQxyRQm2mymxJ8Q==$0 TABLE
BIN$eoUDn9iZQZu8008avWIZTw==$0 TABLE
BIN$s/TtyOpCScOyQF4ryjTqoQ==$0 TABLE
BIN$Bg8JK9uhTYSuYKgj8RAHEQ==$0 TABLE
BIN$pQRYjS68TIWaxHVzVJPm3w==$0 TABLE
BIN$jiQonp+AR4qiyJrUtXuS1g==$0 TABLE
BIN$llYoG9Z/TPuHAqtp2lZl+Q==$0 TABLE
BIN$621+M0RgSOekCDcAcAUZ1w==$0 TABLE
BIN$O9Bb4JGdS2Ws/IHpXMvybw==$0 TABLE
BIN$oSZCxPp1SXWBseQhvo7XTw==$0 TABLE
BIN$LHMtbGiDSPyM7p0jFGzsxA==$0 TABLE
BIN$rhxtKwpeTFCUfajaRKGlew==$0 TABLE
T TABLE
BIN$ZC5/LPvySYW4O/iF+yz/6g==$0 TABLE
BIN$9s/ipMyTQ+yRrO3lpB0L/w==$0 TABLE
BIN$LfTIv03ES+eN1r3twcQD2g==$0 TABLE
BIN$1gapbvADSTKwC+vCkqa0xA==$0 TABLE
BIN$GYBu155XRl6HLLepitUvBQ==$0 TABLE
BIN$L87e8DnqSPWL8l4CQbPdpA==$0 TABLE
BIN$fofZqfT0QK2oKEkGy9y3Ag==$0 TABLE
BIN$mJbc7OqwQ4mvD+exbZMH3Q==$0 TABLE
BIN$M24sVrWOTWuf1W3Fn/qglA==$0 TABLE

27 rows selected.

denis@test10g>
denis@test10g>
denis@test10g> purge recyclebin;

Recyclebin purged.

denis@test10g> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
T TABLE

denis@test10g>

Thursday, September 18, 2008

Using Regular Expressions with Oracle Database - two examples

Reference: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_regexp.htm



denis@test10g> ho ls

denis@test10g> @regexp_like.sql
denis@test10g> rem regexp_like.sql
denis@test10g> rem - familar with REGEXP_LIKE
denis@test10g> rem using regular expression to enfore data integrity
denis@test10g>
denis@test10g> drop table t;

Table dropped.

denis@test10g> create table t
2 (
3 l_name varchar2(30),
4 p_number varchar2(30)
5 constraint p_number_format
6 check ( REGEXP_LIKE(p_number, '^\(\d{3}\) \d{3}-\d{4}$' ) )
7 );

Table created.

denis@test10g>
denis@test10g> -- first two statements use valid phone number format
denis@test10g> INSERT INTO t (p_number)
2 VALUES( '(650) 555-5555' );

1 row created.

denis@test10g> INSERT INTO t (p_number)
2 VALUES( '(215) 555-3427' );

1 row created.

denis@test10g> -- remaining statements generate check contraint errors
denis@test10g> INSERT INTO t (p_number)
2 VALUES( '650 555-5555' );
INSERT INTO t (p_number)
*
ERROR at line 1:
ORA-02290: check constraint (DENIS.P_NUMBER_FORMAT) violated


denis@test10g> INSERT INTO t (p_number)
2 VALUES( '650 555 5555' );
INSERT INTO t (p_number)
*
ERROR at line 1:
ORA-02290: check constraint (DENIS.P_NUMBER_FORMAT) violated


denis@test10g> INSERT INTO t (p_number)
2 VALUES( '650-555-5555' );
INSERT INTO t (p_number)
*
ERROR at line 1:
ORA-02290: check constraint (DENIS.P_NUMBER_FORMAT) violated


denis@test10g> INSERT INTO t (p_number)
2 VALUES( '(650)555-5555' );
INSERT INTO t (p_number)
*
ERROR at line 1:
ORA-02290: check constraint (DENIS.P_NUMBER_FORMAT) violated


denis@test10g> INSERT INTO t (p_number)
2 VALUES( ' (650) 555-5555' );
INSERT INTO t (p_number)
*
ERROR at line 1:
ORA-02290: check constraint (DENIS.P_NUMBER_FORMAT) violated


denis@test10g> /
INSERT INTO t (p_number)
*
ERROR at line 1:
ORA-02290: check constraint (DENIS.P_NUMBER_FORMAT) violated


denis@test10g>
denis@test10g> ho ls

denis@test10g> @back_references.sql
denis@test10g> rem back_references.sql
denis@test10g> rem
denis@test10g> rem Using Back Referencs to Reposition Characters
denis@test10g> rem -- back references store matched subexpressions in a temporary buffer,
denis@test10g> rem thereby enabling you to reposition characters
denis@test10g> rem
denis@test10g> rem Explanation of the Regular Expression Elements in the Example
denis@test10g> rem
denis@test10g> rem ^ Matches the beginning of the string.
denis@test10g> rem $ Matches the end of the string.
denis@test10g> rem (\S+) Matches one or more non-space characters. The parentheses are not escaped so
denis@test10g> rem they function as a grouping expression.
denis@test10g> rem \s Matches a whitespace character.
denis@test10g> rem \1 Substitutes the first subexpression, that is, the first group of parentheses
denis@test10g> rem in the matching pattern.
denis@test10g> rem \2 Substitutes the second subexpression.
denis@test10g> rem \3 Substitutes the third subexpression.
denis@test10g> rem , Inserts a comma character.
denis@test10g>
denis@test10g>
denis@test10g> create table famous_people
2 ( names varchar2(30)) ;

Table created.

denis@test10g>
denis@test10g> -- populate table with data
denis@test10g> INSERT INTO famous_people
2 VALUES ('John Quincy Adams');

1 row created.

denis@test10g> INSERT INTO famous_people
2 VALUES ('Harry S. Truman');

1 row created.

denis@test10g> INSERT INTO famous_people
2 VALUES ('John Adams');

1 row created.

denis@test10g> INSERT INTO famous_people
2 VALUES (' John Quincy Adams');

1 row created.

denis@test10g> INSERT INTO famous_people
2 VALUES ('John_Quincy_Adams');

1 row created.

denis@test10g> COMMIT;

Commit complete.

denis@test10g>
denis@test10g>
denis@test10g> col "names" format a40
denis@test10g> col "names after regexp" format a40
denis@test10g>
denis@test10g> SELECT names "names",
2 REGEXP_REPLACE(names,
3 '^(\S+)\s(\S+)\s(\S+)$',
4 '\3, \1 \2')
5 AS "names after regexp"
6 FROM famous_people;

names names after regexp
---------------------------------------- ----------------------------------------
John Quincy Adams Adams, John Quincy
Harry S. Truman Truman, Harry S.
John Adams John Adams
John Quincy Adams John Quincy Adams
John_Quincy_Adams John_Quincy_Adams

denis@test10g>
denis@test10g> drop table famous_people;

Table dropped.

denis@test10g>
denis@test10g> spool off

Wednesday, September 17, 2008

Representing Conditional Expressions as Data - Testcase

Representing Conditional Expressions as Data - Testcase

I am working on night shift. To keep me awake, I decided to enhance my pl/sql knowledge during the shift if I have some free time. I build this test case for the conditional expressions when reading the Oracle application developer guide document.

At high level, I have a table called trader:



NAME EMAIL INTEREST
-------------------- ------------------------------ --------------------------------------------------
name1 vishu@abc.com symbol = 'ABC' and price > 31
name2 denis@abc.com symbol = 'ABC' and price < 31
name3 Nadeem@abc.com symbol = 'ABC' and price = 31
name4 Sam@abc.com symbol = 'ABC' and price <> 5.2
name5 John@abc.com symbol = 'ABC' and price < 31 and change = 5.2
name6 John@abc.com symbol = 'ABC' and price < 31 and change < 5.2
name7 denis@abc.com symbol = 'CCC' and price < 31
name8 denis@abc.com symbol = 'AAA' and price < 31
name9 vishu@abc.com symbol = 'ABC' and price > 25
name10 denis@abc.com symbol = 'ABC' and price < 25
name11 Nadeem@abc.com symbol = 'ABC' and price = 25
name12 Sam@abc.com symbol = 'ABC' and price <> 5.2
name13 John@abc.com symbol = 'ABC' and price > 25 and change = 5.2
name14 John@abc.com symbol = 'ABC' and price > 25 and change < 5.2
name15 denis@abc.com symbol = 'CCC' and price < 25
name16 denis@abc.com symbol = 'AAA' and price > 25



Then I issue a query and the output is as follows:

denis@test10g> select name, email
2 from traders
3 where evaluate ( interest,
4 'symbol=> ''ABC'',
5 price=>31,
6 change=>5.2'
7 ) = 1;

NAME EMAIL
-------------------- ------------------------------
name3 Nadeem@abc.com
name9 vishu@abc.com
name13 John@abc.com



I don't understand fully why the output looks like that, but at this time I decided to move on, leaving it to sometime later to figure it out

The following gives the whole output of the test case:



denis@test10g> @cond_expr
denis@test10g> rem script: cond_expr.sql
denis@test10g> rem
denis@test10g> rem Representing Conditional Expressions as Data
denis@test10g> rem
denis@test10g> rem The Oracle Expression Filter feature enables you to store
denis@test10g> rem conditional expressions as data in the database. The Expression Filter
denis@test10g> rem provides a mechanism that you can use to place a constraint on a VARCHAR2
denis@test10g> rem column to ensure that the values stored are valid SQL WHERE clause
denis@test10g> rem expressions. This mechanism also identifies the set of attributes
denis@test10g> rem that are legal to reference in the conditional expressions.
denis@test10g> rem
denis@test10g> rem Reference: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_sqltypes.htm
denis@test10g> rem
denis@test10g>
denis@test10g>
denis@test10g> -- (1) create a table traders holds data for a stock trading accont holder:
denis@test10g> create table traders
2 ( name varchar2(50),
3 email varchar2(50),
4 interest varchar2(50)
5 );

Table created.

denis@test10g>
denis@test10g> -- (2) create the user-defined datatype ticker with attributes for trading
denis@test10g> -- symbol, limit price, and amount of change in the stock price
denis@test10g>
denis@test10g> create or replace type ticker
2 as object
3 ( symbol varchar2(20),
4 price number,
5 change number
6 );
7 /

Type created.

denis@test10g> -- (3) create an attribute set ticker based on the ticker datatype
denis@test10g>
denis@test10g> begin
2 dbms_expfil.create_attribute_set ( attr_set => 'ticker',
3 from_type => 'YES');
4 end;
5 /

PL/SQL procedure successfully completed.

denis@test10g>
denis@test10g> -- (4)
denis@test10g> -- Associate the attribute set with the expression set stored in the
denis@test10g> -- database column trader.interest
denis@test10g> -- The following code places a constraint on the interest column that
denis@test10g> -- ensures the column stores valid conditional expressions
denis@test10g>
denis@test10g> begin
2 dbms_expfil.assign_attribute_set (attr_set => 'ticker',
3 expr_tab => 'traders',
4 expr_col => 'interest');
5 end;
6 /

PL/SQL procedure successfully completed.

denis@test10g>
denis@test10g>
denis@test10g> -- (5) Populate the table with trader names, email addresses and conditional
denis@test10g> -- expressions that represents a stock the trader is interested in at
denis@test10g> -- a particular price:
denis@test10g>
denis@test10g> insert into traders (name, email, interest)
2 values ('name1', 'vishu@abc.com', 'symbol = ''ABC'' and price > 31');

1 row created.

denis@test10g>
denis@test10g> insert into traders (name, email, interest)
2 values ('name2', 'denis@abc.com', 'symbol = ''ABC'' and price < 31');

1 row created.

denis@test10g>
denis@test10g> insert into traders (name, email, interest)
2 values ('name3', 'Nadeem@abc.com', 'symbol = ''ABC'' and price = 31');

1 row created.

denis@test10g>
denis@test10g> insert into traders (name, email, interest)
2 values ('name4', 'Sam@abc.com', 'symbol = ''ABC'' and price <> 5.2');

1 row created.

denis@test10g>
denis@test10g> insert into traders (name, email, interest)
2 values ('name5', 'John@abc.com', 'symbol = ''ABC'' and price < 31 and change = 5.2 ');

1 row created.

denis@test10g>
denis@test10g> insert into traders (name, email, interest)
2 values ('name6', 'John@abc.com', 'symbol = ''ABC'' and price < 31 and change < 5.2 ');

1 row created.

denis@test10g>
denis@test10g> insert into traders (name, email, interest)
2 values ('name7', 'denis@abc.com', 'symbol = ''CCC'' and price < 31');

1 row created.

denis@test10g>
denis@test10g> insert into traders (name, email, interest)
2 values ('name8', 'denis@abc.com', 'symbol = ''AAA'' and price < 31');

1 row created.

denis@test10g>
denis@test10g>
denis@test10g> insert into traders (name, email, interest)
2 values ('name9', 'vishu@abc.com', 'symbol = ''ABC'' and price > 25');

1 row created.

denis@test10g>
denis@test10g> insert into traders (name, email, interest)
2 values ('name10', 'denis@abc.com', 'symbol = ''ABC'' and price < 25');

1 row created.

denis@test10g>
denis@test10g> insert into traders (name, email, interest)
2 values ('name11', 'Nadeem@abc.com', 'symbol = ''ABC'' and price = 25');

1 row created.

denis@test10g>
denis@test10g> insert into traders (name, email, interest)
2 values ('name12', 'Sam@abc.com', 'symbol = ''ABC'' and price <> 5.2');

1 row created.

denis@test10g>
denis@test10g> insert into traders (name, email, interest)
2 values ('name13', 'John@abc.com', 'symbol = ''ABC'' and price > 25 and change = 5.2 ');

1 row created.

denis@test10g>
denis@test10g> insert into traders (name, email, interest)
2 values ('name14', 'John@abc.com', 'symbol = ''ABC'' and price > 25 and change < 5.2 ');

1 row created.

denis@test10g>
denis@test10g> insert into traders (name, email, interest)
2 values ('name15', 'denis@abc.com', 'symbol = ''CCC'' and price < 25');

1 row created.

denis@test10g>
denis@test10g> insert into traders (name, email, interest)
2 values ('name16', 'denis@abc.com', 'symbol = ''AAA'' and price > 25');

1 row created.

denis@test10g>
denis@test10g> commit;

Commit complete.

denis@test10g>
denis@test10g> -- (6) Use the EVALUATE operator to identify teh conditional expressions
denis@test10g> set linesize 120
denis@test10g> col name format a20
denis@test10g> col email format a30
denis@test10g>
denis@test10g>
denis@test10g> select * from traders;

NAME EMAIL INTEREST
-------------------- ------------------------------ --------------------------------------------------
name1 vishu@abc.com symbol = 'ABC' and price > 31
name2 denis@abc.com symbol = 'ABC' and price < 31
name3 Nadeem@abc.com symbol = 'ABC' and price = 31
name4 Sam@abc.com symbol = 'ABC' and price <> 5.2
name5 John@abc.com symbol = 'ABC' and price < 31 and change = 5.2
name6 John@abc.com symbol = 'ABC' and price < 31 and change < 5.2
name7 denis@abc.com symbol = 'CCC' and price < 31
name8 denis@abc.com symbol = 'AAA' and price < 31
name9 vishu@abc.com symbol = 'ABC' and price > 25
name10 denis@abc.com symbol = 'ABC' and price < 25
name11 Nadeem@abc.com symbol = 'ABC' and price = 25
name12 Sam@abc.com symbol = 'ABC' and price <> 5.2
name13 John@abc.com symbol = 'ABC' and price > 25 and change = 5.2
name14 John@abc.com symbol = 'ABC' and price > 25 and change < 5.2
name15 denis@abc.com symbol = 'CCC' and price < 25
name16 denis@abc.com symbol = 'AAA' and price > 25

16 rows selected.

denis@test10g>
denis@test10g> select name, email
2 from traders
3 where evaluate ( interest,
4 'symbol=> ''ABC'',
5 price=>31,
6 change=>5.2'
7 ) = 1;

NAME EMAIL
-------------------- ------------------------------
name3 Nadeem@abc.com
name9 vishu@abc.com
name13 John@abc.com

denis@test10g>
denis@test10g>
denis@test10g> -- clean up the test code:
denis@test10g> drop table traders;

Table dropped.

denis@test10g>
denis@test10g> begin
2 dbms_expfil.drop_attribute_set ( attr_set => 'ticker');
3 end;
4 /

PL/SQL procedure successfully completed.

denis@test10g> spool off


Library Cache Pin for over 600 seconds

During my on-call shift today, I recieved the alerts for a database: Library Cache Pin for over 600 seconds

Checked the database, found the following:

===> check session wait: sid=852 wait for library cache pin


SID USERNAME EVENT P1 P2 WAIT_TIME STATE
------ --------------- -------------------- ---------- ---------- ---------- -------------------

852 MTZUSG12CON library cache pin 1.3835E+19 1.3835E+19 0 WAITING


===> current running sql:

SQL_TEXT SID USER_NAME
---------------------------------------- ------ ------------
update AC_PROCESSING_ACCOUNTING set FIL 852 MTZUSG12CON
E_STATUS='CN' where (((FPFC_NXT_PGM_NAME
=:b0 and FILE_STATUS='RD') and FPFC_NXT_
FILE_ALIAS=:b1) and DATA_GROUP like :b2)


From Google, found Burleson has a tip for this topic. click this.

The following script is published by Mark Bobak for locating the source of library cache pin waits.


select decode(lob.kglobtyp, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
11, 'PACKAGE BODY', 12, 'TRIGGER',
13, 'TYPE', 14, 'TYPE BODY',
19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
32, 'INDEXTYPE', 33, 'OPERATOR',
34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
42, 'MATERIALIZED VIEW',
43, 'DIMENSION',
44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
48, 'CONSUMER GROUP',
51, 'SUBSCRIPTION', 52, 'LOCATION',
55, 'XML SCHEMA', 56, 'JAVA DATA',
57, 'SECURITY PROFILE', 59, 'RULE',
62, 'EVALUATION CONTEXT',
'UNDEFINED') object_type,
lob.KGLNAOBJ object_name,
pn.KGLPNMOD lock_mode_held,
pn.KGLPNREQ lock_mode_requested,
ses.sid,
ses.serial#,
ses.username
FROM
x$kglpn pn,
v$session ses,
x$kglob lob,
v$session_wait vsw
WHERE
pn.KGLPNUSE = ses.saddr and
pn.KGLPNHDL = lob.KGLHDADR
and lob.kglhdadr = vsw.p1raw
and vsw.event = 'library cache pin'
order by lock_mode_held desc
/




I run it as sysdba and the output is:




OBJECT_TYPE
------------------
OBJECT_NAME
---------------------------------------------------------------------------------------------------------------------- --
LOCK_MODE_HELD LOCK_MODE_REQUESTED SID SERIAL# USERNAME
-------------- ------------------- ------ ---------- ---------------
NEXT OBJECT
update AC_PROCESSING_ACCOUNTING set FILE_STATUS='CN' where (((FPFC_NXT_PGM_NAME=:b0 and FILE_STATUS='RD') and FPFC_NX T_
FILE_ALIAS=:b1) and DATA_GROUP like :b2)
3 0 985 28668 MTZUSG08CON

NEXT OBJECT
update AC_PROCESSING_ACCOUNTING set FILE_STATUS='CN' where (((FPFC_NXT_PGM_NAME=:b0 and FILE_STATUS='RD') and FPFC_NX T_
FILE_ALIAS=:b1) and DATA_GROUP like :b2)
0 2 852 14208 MTZUSG12CON





After a while, the wait envet went away by itself.

Sunday, September 14, 2008

Understanding Read-Only Transaction

In the Oracle document, it stats:
"A read-only transaction does not acquire any additional data locks to provide transaction-level read consistency. The multi-version consistency model used for statement-level read consistency is used to provide transaction-level read consistency; all queries return information with respect to the system change number (SCN) determined when the read-only transaction begins. Because no data locks are acquired, other transactions can query and update data being queried concurrently by a read-only transaction."

To help understand the concept of read-only transaction, I did a small test:

In the session 1, I created a table and keep updating the column value:



test@MYDB> create table t ( a int);

Table created.

test@MYDB> insert into t values(1);

1 row created.

test@MYDB> commit;

Commit complete.

test@MYDB> select * from t;

A
----------
1

test@MYDB> update t set a=2;

1 row updated.

test@MYDB> commit;

Commit complete.

test@MYDB> update t set a=3;

1 row updated.

test@MYDB> commit;

Commit complete.

test@MYDB> update t set a=4;

1 row updated.

test@MYDB> commit;

Commit complete.

test@MYDB> update t set a=5;

1 row updated.

test@MYDB> commit;

Commit complete.

test@MYDB>





In the session 2, after update the value to 3 in session 1, I started a read-only transaction as following:


test@MYDB> set transaction read only;

Transaction set.


And after each update in session 1, I query the table t, notice I always got value 3, this demonstrated the point:
"all queries return information with respect to the system change number (SCN) determined when the read-only transaction begins"


test@MYDB> select * from t;

A
----------
3

test@MYDB> select * from t;

A
----------
3

test@MYDB> select * from t;

A
----------
3





Then in session 2, I issue the commmit, this end of the read only transaction mode, and I query table t again, got the value 5 as expected


test@MYDB> commit;

Commit complete.

test@MYDB> select * from t;

A
----------
5