Saturday, January 31, 2009

shell script tip: wait

Shell scripting tip: wait

I have a task to clone two databases. I need to scp data files with 5 parallel sessions. This can be accomplished by using 'wait'.

The script looks like:


#!/bin/ksh

echo " tbs 1 starts `date` "
scp /v03/oradata/mydb/tbs34.dbf myserver01:/v03/oradata/mydb/ &
scp /v04/oradata/mydb/tbs05.dbf myserver01:/v04/oradata/mydb/ &
scp /v04/oradata/mydb/tbs06.dbf myserver01:/v04/oradata/mydb/ &
scp /v04/oradata/mydb/tbs07.dbf myserver01:/v04/oradata/mydb/ &
scp /v04/oradata/mydb/tbs08.dbf myserver01:/v04/oradata/mydb/ &
wait

echo " tbs 2 starts `date` "
scp /v04/oradata/mydb/tbs09.dbf myserver01:/v04/oradata/mydb/ &
scp /v04/oradata/mydb/tbs10.dbf myserver01:/v04/oradata/mydb/ &
scp /v04/oradata/mydb/tbs11.dbf myserver01:/v04/oradata/mydb/ &
scp /v04/oradata/mydb/tbs12.dbf myserver01:/v04/oradata/mydb/ &
scp /v04/oradata/mydb/tbs04.dbf myserver01:/v04/oradata/mydb/ &
wait

echo " tbs 3 starts `date` "
scp /v04/oradata/mydb/tbs17.dbf myserver01:/v04/oradata/mydb/ &
scp /v04/oradata/mydb/tbs26.dbf myserver01:/v04/oradata/mydb/ &
scp /v04/oradata/mydb/tbs27.dbf myserver01:/v04/oradata/mydb/ &
scp /v04/oradata/mydb/tbs28.dbf myserver01:/v04/oradata/mydb/ &
scp /v04/oradata/mydb/tbs16.dbf myserver01:/v04/oradata/mydb/ &
wait


--- truncated -------



In this script, five processes will be executed at background parallelly. The next five processes will start until all the previous background processes finish.

Friday, January 30, 2009

ORA-27101: shared memory realm does not exist

I created a 10g database manually on the CentOS4.7 virtual machine. I have no problem to connect as SYS or SYSTEM on the server,
however, when I configured the listner and tried to connect remotely or on the server with the connection string, I got ORA-27101 error.
i.e.


vmlinux1:/app/oracle/product/10.2.0/db_1/network/admin [dbt10g] $ sqlplus system@dbt10g

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jan 27 22:04:34 2009

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

Enter password:
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory



It took me a while to figure out the reason. My ORACLE_SID is in upper case, however I used lower case sid name in the listener.ora, i.e.




$ cat listener.ora
LISTENER =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.233.128)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))

SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=dbt10g)
(SID_NAME=dbt10g)
)

)




Atfer changing them to upper case, I fixed the problem.

Tuesday, January 27, 2009

Collection: boobooke video lecture

Bewlow are the list of my video lectures on http://www.boobooke.com/, which is an educational site for various IT technologies hosted in China.

What I am trying to do is to present key concepts of Oracle Database through concrete examples and hands-on labs. All materials are written in English, however, some lectures are spoken in Chinese.

Oracle Database Core Concepts for DBA - Instance and Database Part I
http://www.boobooke.com/v/bbk2187

Oracle Database Core Concepts for DBA - Instance and Database Part II
http://www.boobooke.com/v/bbk2213

Oracle Database Core Concepts for DBA - Instance and Database Part III
http://www.boobooke.com/v/bbk2214

Oracle Database Core Concepts for DBA - Data Dictionary
http://www.boobooke.com/v/bbk2064 (in Chinese)

Oracle Database Core Concepts for DBA - Data Block Part I
http://www.boobooke.com/v/bbk2067 (in Chinese)

Oracle Database Core Concepts for DBA - Data Block Part II
http://www.boobooke.com/v/bbk2068 (in Chinese)

Oracle Database Core Concepts for DBA - DML Data Locks
http://www.boobooke.com/v/bbk2053 (in Chinese)

Discovering Performance Issues Using OEM, AWR and ADDM - Part I
http://www.boobooke.com/v/bbk2405

Discovering Performance Issues Using OEM, AWR and ADDM - Part II
http://www.boobooke.com/v/bbk2406

Below are video lectures presented by other people that are interested to me:

Installation of Cygwin Software
http://www.boobooke.com/v/bbk1473


(last updated: Mar 23, 2009)

Saturday, January 24, 2009

Be careful when using SYSDATE in the predicate on 9i databases

I had a task to check a SQL and modify it to be used to generate hourly report. The original SQL given looks like (table and column names modified as usual):


---- literal string for the date
SELECT To_Char(mas.o_save_date, 'MM/DD/YYYY HH24') o_time,
DECODE(tr.nt_id, '201','D','209','F','217','V','222','FV') svc_type,
tr.tr_type_id,
Count(DISTINCT tr.bo_id) o_count
FROM o_tr tr,
o_mas mas
WHERE mas.is_pq = 'N'
AND mas.int_o_id = tr.int_o_id
AND tr.nt_id IN (201, 209, 217, 222)
AND mas.o_save_date >= To_Date('01/22/2009 00:00:00','MM/DD/YYYY HH24:MI:SS')
AND mas.o_save_date < To_Date('01/23/2009 00:00:00','MM/DD/YYYY HH24:MI:SS')
GROUP BY To_Char(mas.o_save_date, 'MM/DD/YYYY HH24'), tr.nt_id, tr.tr_type_id
;



I run it and the excution plan looks like:





-------------------------------------------------------------------------
| Id | Operation | Name |Rows | Bytes | Cost |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 41 |
| 1 | SORT GROUP BY | | 4 | 148 | 41 |
| 2 | TABLE ACCESS BY INDEX ROWID | O_TR | 2 | 40 | 9 |
| 3 | NESTED LOOPS | | 4 | 148 | 25 |
| 4 | TABLE ACCESS BY INDEX ROWID| O_MAS | 2 | 34 | 7 |
| 5 | INDEX RANGE SCAN | O_MAS_IX2| 3 | | 4 |
| 6 | INDEX RANGE SCAN | O_TR_FK3 | 14 | | 3 |
-------------------------------------------------------------------------




The SQL returns in less than a minute. Then I execuate the following SQL which uses
SYSDATE instead of literal date.





SELECT To_Char(mas.o_save_date, 'MM/DD/YYYY HH24') o_time,
DECODE(tr.nt_id, '201','D','209','F','217','V','222','FV') svc_type,
tr.tr_type_id,
Count(DISTINCT tr.bo_id) o_count
FROM o_tr tr,
o_mas mas
WHERE mas.is_pq = 'N'
AND mas.int_o_id = tr.int_o_id
AND tr.nt_id IN (201, 209, 217, 222)
AND mas.o_save_date >= trunc(sysdate)-1
AND mas.o_save_date < trunc(sysdate)
GROUP BY To_Char(mas.o_save_date, 'MM/DD/YYYY HH24'), tr.nt_id, tr.tr_type_id
;



The execution plan changed to hash join and checked v$longops, indicating table scan will takes more than 1 hour, ie:




-------------------------------------------------------------------------------
| Id | Operation | Name |Rows |Bytes |TempSpc| Cost |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 699K|
| 1 | SORT GROUP BY | | 2445K| 86M| 243M| 699K|
| 2 | FILTER | | | | | |
| 3 | HASH JOIN | | 2445K| 86M| 30M| 683K|
| 4 | TABLE ACCESS BY INDEX ROWID| O_MAS | 1113K| 18M| | 38909 |
| 5 | INDEX RANGE SCAN | O_MAS_IX2 | 401K| | | 2976 |
| 6 | TABLE ACCESS FULL | O_TR | 55M| 1053M| | 617K|
---------------------------------------------------------------------------------



I tried to use hint to force the Oracle to use the first execution plan, the cost is 10M, no surprise that Oracle won't choose this plan.




SELECT
/*+ use_nl( mas, tr) index(tr O_TR_FK3) */
SELECT To_Char(mas.o_save_date, 'MM/DD/YYYY HH24') o_time,
DECODE(tr.nt_id, '201','D','209','F','217','V','222','FV') svc_type,
tr.tr_type_id,
Count(DISTINCT tr.bo_id) o_count
FROM o_tr tr,
o_mas mas
WHERE mas.is_pq = 'N'
AND mas.int_o_id = tr.int_o_id
AND tr.nt_id IN (201, 209, 217, 222)
AND mas.o_save_date >= trunc(sysdate)-1
AND mas.o_save_date < trunc(sysdate)
GROUP BY To_Char(mas.o_save_date, 'MM/DD/YYYY HH24'), tr.nt_id, tr.tr_type_id
;


-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 10M|
| 1 | SORT GROUP BY | | 2445K| 86M| 243M| 10M|
| 2 | FILTER | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID | O_TR | 2 | 40 | | 9 |
| 4 | NESTED LOOPS | | 2445K| 86M| | 10M|
| 5 | TABLE ACCESS BY INDEX ROWID| O_MAS | 1113K| 18M| | 38909 |
| 6 | INDEX RANGE SCAN | O_MAS_IX2 | 401K| | | 2976 |
| 7 | INDEX RANGE SCAN | O_TR_FK3 | 14 | | | 3 |
-------------------------------------------------------------------------------------


For the hourly report, I finally obtained the following SQL:




SELECT /*+ use_nl( mas, tr) index(mas O_MAS_IX2) index(tr O_TR_FK3) */
To_Char(mas.o_save_date, 'MM/DD/YYYY HH24') o_time,
DECODE(tr.nt_id, '201','D','209','F','217','V','222','FV') svc_type,
tr.tr_type_id,
Count(DISTINCT tr.bo_id) o_count
FROM
o_mas mas,
o_tr tr
WHERE mas.is_pq = 'N'
AND mas.int_o_id = tr.int_o_id
AND tr.nt_id IN (201, 209, 217, 222)
AND mas.o_save_date >= trunc(sysdate,'HH24') - 1/24
AND mas.o_save_date < trunc(sysdate, 'HH24')
GROUP BY To_Char(mas.o_save_date, 'MM/DD/YYYY HH24'), tr.nt_id, tr.tr_type_id
/


This SQl returns in seconds. If not using the hint, Oracle will choose hash join. What happend here? I seek answers from Jonathan Lewis's book "Cost-Based Oracle Fundamentals" as I vaguelly remember there is something about SYSDATE. I am right. His book has a section titled "Surprising Sysdate" (p130) that shed light on this issue. Basically, Oracle treat "Sysdate+N" as unknown, and treat it same as bind variable. As a result, optimizer can not calculate the cardinality and selectivity in the same way as the case of literal string. Oracle 10g has identified and addressed this problem.

Wednesday, January 21, 2009

Installation of Oracle 10gR2 on CentOS 4.7 for personal study

I bought a new laptop with Windows XP professional SP3 - I requested to downgrade to Windows XP from Vista when I placed the order. However I found that Windows XP can only recognize 3GB memory though BIOS shows 4GB installed. I have to live with that for now since I don't want to use Vista. I assigned myself a project to install Oracle 10gR2 software on CentOS 4.7 and use virtual machine technology.

The steps I took to accomplish this project will be described as follows:

1. Download VMware Server 2.0 from http://www.vmware.com/download/server/

2. Download CentOS 4.7 from http://www.centos.org

3. Create a virtual machine through VMware Infrastructure Web Access.
The vitural machine was created successfully with inital RAM 768M and host-only for the network configuration. After creating the virtual machine, I login with VMware Console. I obtained the IP address of the vitual machine by ifconfig, which is 192.168.233.128. I did not specify anything during installation, so this IP is kind of automatically configured. I verified that I can ping and ssh using this IP from host machine.

4. Configure Cygwin/X
Since I knew I need X-windows to lanch Oracle Universal Installer and I decided not to run it within the server (virtual machine), instead from the host machine, I configured the Cygwin/X. The User guide about how to install and config Cygwin/X is here. I verified I can ssh to the virtual machine and launch xclient such as XCLOK. The command involved:

In the Cygwin terminal:
$ sh /usr/bin/startxwin.sh

Cygwin X terminal shown up, then:
$ ssh -Y -l oracle 192.168.233.128

I found that I did not need set DISPLAY environment variable at all.

5. Download Oracle 10gR2 and sftp to the virtual machine

6. Install Oracle 10gR2

I followed this article ( http://www.oracle.com/technology/pub/articles/smiley_10gdb_install.html) to install the software. When I checked the required packages, I found that two package are missing:
- sysstat
- libaio is not installed

I decide to ignore it, but the first installation failed, Oracle complains that libaio is not found and also indicated the minimum memory requirement is not met. So I downloaded these two packages from http://mirror.astate.edu/pub/centos/4.7/os/i386/CentOS/RPMS After installing them and increased the memory size of the virtual machine to 1G, the Oracle software got installed successfully.


Note: 'usermod' can be used to modify user group, ex.

[root@localhost /]# groupadd oinstall
[root@localhost /]# usermod -g oinstall -G dba oracle
[root@localhost /]# id oracle
uid=500(oracle) gid=502(oinstall) groups=502(oinstall),501(dba)

Tuesday, January 20, 2009

Self anti-join - failed to obtain optimal exectuion plan on a 9i database

I continued to study the query I described in the previous post on Jan 18. I realized that the first part of the query is essentially a self anti-join.

The column statistics of the table BM in the production is as follows:



COLUMN_NAME LOW_VAL HIGH_VAL NUM_DISTINCT NUM_NULLS
------------- ------------- -------------- ----------- ----------
BID 3 598219339 314914980 0
BSCI B S 2 0
BSC 1 7 7 0
VP_BID 0 598218162 25905093 0


 

I thus constructed a baseline test case as following:



create table bm
as
select
rownum bid,
trunc(dbms_random.value(1,500)) vp_bid,
mod(rownum, 7) + 1 bsc,
decode(mod(rownum,2),
0, 'A',
1, 'S') bsci
from all_objects
where rownum <= 6500;

create index bm_fk5 on bm(vp_bid);

begin
dbms_stats.gather_table_stats( user, 'bm', cascade => true);
end;
/

set autotrace traceonly
SELECT * FROM bm
WHERE bm.bsc = 3
AND bm.bsci = 'S'
AND NOT EXISTS (
SELECT 1
FROM bm bm1
WHERE bm.vp_bid = bm1.vp_bid
AND bm1.bsci = 'S'
AND bm1.bsc NOT IN (3, 4));
set autotrace off


  

Here are the results of Autotrace

1. When executed the baseline test case in a 9.2.0.8 database:


Execution Plan
-------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=14 Card=1 Bytes=12)
1 0 FILTER
2 1 TABLE ACCESS (FULL) OF 'BM' (Cost=4 Card=1 Bytes=12)
3 1 TABLE ACCESS (BY INDEX ROWID) OF 'BM' (Cost=10 Card=1 Bytes=12)
4 3 INDEX (RANGE SCAN) OF 'BM_FK5' (NON-UNIQUE) (Cost=1 Card=13)


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


 

2. When executed the baseline test case in a 10.2.0.1 database:


---------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
---------------------------------------------------------------------------
0 SELECT STATEMENT 1 21 14 (15) 00:00:01
* 1 HASH JOIN ANTI 1 21 14 (15) 00:00:01
* 2 TABLE ACCESS FULL BM 464 5568 7 (15) 00:00:01
* 3 TABLE ACCESS FULL BM 2388 21492 7 (15) 00:00:01
---------------------------------------------------------------------------

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

1 - access("BM"."VP_BID"="BM1"."VP_BID")
2 - filter("BM"."BSC"=3 AND "BM"."BSCI"='S')
3 - filter("BM1"."BSCI"='S' AND "BM1"."BSC"<>3 AND "BM1"."BSC"<>4)


Statistics
----------------------------------------------------------
166 recursive calls
0 db block gets
55 consistent gets
0 physical reads
0 redo size
689 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
8 rows processed


Note: 10g picked up hash join anti acccess path and the consistent gets is only 55 vs 1508 in the case of 9i.

3. Drop the index BM_FK5, and run the baseline test case in the 9.2.0.8 database


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=9 Card=1 Bytes=21)
1 0 HASH JOIN (ANTI) (Cost=9 Card=1 Bytes=21)
2 1 TABLE ACCESS (FULL) OF 'BM' (Cost=4 Card=464 Bytes=5568)
3 1 TABLE ACCESS (FULL) OF 'BM' (Cost=4 Card=2388 Bytes=21492)


Statistics
----------------------------------------------------------
153 recursive calls
0 db block gets
54 consistent gets
0 physical reads
0 redo size
389 bytes sent via SQL*Net to client
234 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
8 rows processed



Note: without that index, 9i can choose right executin plan.

I then tested different ways in order to get 9.2.0.8 database to pick up the good execution plan when the index is present.

I tried:

1. NO_INDEX hint
2. collect histogram on the indexed column
3. rewrite the SQL to use NOT IN
4. NOT IN with HASH_AJ hint
5. HASH_AJ hint
6. Not analyzing the table, using dynamic sampling feaure

None of above succeeded. After playing with it the whole afternoon. I decided to move on and document it here for reference.

Monday, January 19, 2009

Anti-Join - A test case showing difference between NOT IN and NOT EXISTS on a 9i database

This test case is built based on Tom Kyte's book "Effective Oracle by Design" p477.


Summary of logical reads of anti-join queris when using deffierent structure

---------------------------------------------------------
Structure NOT IN NOT EXISTS Outer join
---------------------------------------------------------
9i 160 20133 160
10g 155 155 155
----------------------------------------------------------



 The test case is as follows: 


 
rem script: anti_join_cbo.sql
rem
rem anti-join: used to return rows from a table that are not
rem present in some other row source
rem

set echo on
drop table t1;
drop table t2;

create table t1 as select *
from all_objects where rownum <=10000;


create table t2 as select *
from all_objects where rownum <=9950;

create index t2_idx on t2(object_id);



begin
dbms_stats.gather_table_stats(
user,
't1',
cascade => true
);
end;
/

begin
dbms_stats.gather_table_stats(
user,
't2',
cascade => true
);
end;
/

alter session set tracefile_identifier = anti_join;
alter session set timed_statistics=true;
alter session set events '10046 trace name context forever, level 12';

-- 1. NOT IN
select count(*) from t1 cbo
where object_id not in (select object_id from t2);


-- 2. NOT EXISTS

select count(*) from t1 cbo
where not exists (select null from t2 where t2.object_id = cbo.object_id );


-- 3. OUTER JOIN

select count(*) from t1, t2 cbo
where t1.object_id = cbo.object_id(+)
and cbo.object_id is NULL;


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

set doc off
doc

------- 9.2.0.8 optimizer_mode=choose ------

1. NOT IN

select count(*) from t1 cbo
where object_id not in (select object_id from t2)

call count cpu elapsed disk query current rows
------- ------ ---- -------- ----- ------ -------- -----
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.04 0.03 0 160 0 1
------- ------ ---- -------- ----- ------ -------- -----
total 4 0.05 0.03 0 160 0 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 178

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
50 HASH JOIN ANTI
10000 TABLE ACCESS FULL T1
9950 INDEX FAST FULL SCAN T2_IDX (object id 443635)





2. NOT EXISTS

select count(*) from t1 cbo
where not exists (select null from t2 where t2.object_id = cbo.object_id )

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 2 0.17 0.16 0 20133 0 1
------- ------ ----- -------- ------ ------ ------- -----
total 4 0.17 0.16 0 20133 0 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 178

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
50 FILTER
10000 TABLE ACCESS FULL T1
9950 INDEX RANGE SCAN T2_IDX (object id 443635)


3. OUTER JOIN

select count(*) from t1, t2 cbo
where t1.object_id = cbo.object_id(+)
and cbo.object_id is NULL

call count cpu elapsed disk query current rows
------- ------ ----- --------- ----- ------ -------- -----
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.04 0.04 0 160 0 1
------- ------ ----- --------- ----- ------ -------- -----
total 4 0.05 0.04 0 160 0 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 178

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
50 FILTER
10000 HASH JOIN OUTER
10000 TABLE ACCESS FULL T1
9950 INDEX FAST FULL SCAN T2_IDX (object id 443635)

------- 10.2.0.1 optimizer_mode=all_rows ------


1. NOT IN
select count(*) from t1 cbo
where object_id not in (select object_id from t2)

call count cpu elapsed disk query current rows
------- ------ ----- -------- ---- ------- -------- ----
Parse 1 0.01 0.11 0 0 0 0
Execute 1 0.00 0.03 0 0 0 0
Fetch 2 0.01 0.02 0 155 0 1
------- ------ ----- -------- ---- ------- -------- ----
total 4 0.03 0.17 0 155 0 1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 62

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=155 pr=0 pw=0 time=25632 us)
50 HASH JOIN ANTI (cr=155 pr=0 pw=0 time=25386 us)
10000 TABLE ACCESS FULL T1 (cr=129 pr=0 pw=0 time=30170 us)
9950 INDEX FAST FULL SCAN T2_IDX (cr=26 pr=0 pw=0 time=29952 us)(object id 58211)

2. NOT EXISTS

Same as "NOT IN"


3. Outer join

Same as "NOT IN"

#

Sunday, January 18, 2009

An experience of tunning a SQL with EXISTS and NOT EXISTS subqueries

Our team was requested to run a SQL on Saturday aternoon. The time window was from 4PM to midnight. Since from midnight a maintenance job was scheduled and the database won't be available, we promised to deliver the results by midnight. However, the DBA responsible for carring out the task had no idea how long this SQl would take. So he would do a dry run on Friday afternoon. I was called to help him.

The SQL has the following structure (actual table names, column name were modified of course):


SELECT *
FROM a,
bi,
bm
WHERE bm.bsc = 3
AND bm.bsci = 'S'
AND a.bid = bi.bid
AND a.bid = bm.vp_bid
AND
NOT EXISTS (
SELECT 1
FROM bm bm1
WHERE bm.vp_bid = bm1.vp_bid
AND bm1.bsci = 'S'
AND bm1.bsc NOT IN (3, 4));
union
SELECT *
FROM a,
bi,
bm
WHERE bm.bsc = 3
AND bm.bsci = 'S'
AND a.bid = bi.bid
AND a.bid = bm.vp_bid
AND
EXISTS (
SELECT 1
FROM bm bm1
WHERE bm.vp_bid = bm1.vp_bid
AND bm1.bsci = 'S'
AND bm1.bsc NOT IN (3, 4));



The DBA initially tried to do a dry run using hint parallel 5 for all the three tables. He said using parallel hint he got smaller cost value from the explain plan. I told him if he used parallel 10, he would get even smaller cost value, but this did not mean the SQL would run faster. I told him just start the SQL without any hint and see how long it would take.

The number of rows of the table involved are as follows:



TABLE_NAME NUM_ROWS LAST_ANALYZED
----------------- ---------- -------------------
BM 309688610 2009-01-03 07:22:04
A 15837920 2009-01-03 10:54:31
BI 15840300 2009-01-03 11:08:33


Our databae is Oracle 9.2.0.8.



It turned out the SQL did not finish after 5.5 hours running. The execution plan looked like:


 
------------------------------------------------------------------------
|Id | Operation | Name | Rows| Bytes |Temp |
Spc |Cost |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 11M|
| 1 | SORT UNIQUE | | 110M| 9017M| 19G| 11M|
| 2 | UNION-ALL | | | | | |
| 3 | FILTER | | | | | |
| 4 | HASH JOIN | |1124K| 79M| 61M| 334K|
| 5 | HASH JOIN | |1124K| 48M| 38M| 305K|
| 6 | TABLE ACCESS FULL | BM |1124K| 25M| | 271K|
| 7 | TABLE ACCESS FULL | A | 15M| 319M| |24889|
| 8 | TABLE ACCESS FULL | BI | 15M| 440M| |17719|
| 9 | TABLE ACCESS BY INDEX ROWID| BM | 5 | 60 | | 8 |
|10 | INDEX RANGE SCAN | BM_FK5| 13 | | | 4 |
|11 | HASH JOIN | | 108M| 8937M|1844M| 703K|
|12 | HASH JOIN | | 22M| 1587M| 772M| 359K|
|13 | TABLE ACCESS FULL | BM | 22M| 514M| | 71K|
|14 | HASH JOIN | | 15M| 759M| 501M|60525|
|15 | TABLE ACCESS FULL | A | 15M| 319M| |24889|
|16 | TABLE ACCESS FULL | BI | 15M| 440M| |17719|
|17 | TABLE ACCESS FULL | BM | 115M| 1323M| | 271K|
------------------------------------------------------------------------



We were worried about this SQL, our manager set up a call with developers at about 7PM. I learned from them that we can use inion all instead of union since there are no possible dulpicate rows from the two parts. Finally we agreed with runing the two parts of the SQL separatly and load the results to two separate temp table. And DBA team only need diliver these two temp talbe to the devlopers.

I took over the task. I started a new test and using the nested loop hint for the first part:

 

SELECT /*+ use_nl(bm, a) use_nl(a, bi) */
*
FROM a,
bi,
bm
WHERE bm.bsc = 3
AND bm.bsci = 'S'
AND a.bid = bi.bid
AND a.bid = bm.vp_bid
AND
NOT EXISTS (
SELECT 1
FROM bm bm1
WHERE bm.vp_bid = bm1.vp_bid
AND bm1.bsci = 'S'
AND bm1.bsc NOT IN (3, 4));


The execution plan for this part is as follows:




------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 14M|
| 1 | FILTER | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | BI | 1 | 29 | 3 |
| 3 | NESTED LOOPS | | 1106K| 78M| 5797K|
| 4 | NESTED LOOPS | | 1106K| 47M| 2479K|
| 5 | TABLE ACCESS FULL | BM | 1106K| 25M| 267K|
| 6 | TABLE ACCESS BY INDEX ROWID| A | 1 | 21 | 2 |
| 7 | INDEX UNIQUE SCAN | A_PK | 1 | | 1 |
| 8 | INDEX RANGE SCAN | BI_PK | 1 | | 2 |
| 9 | TABLE ACCESS BY INDEX ROWID | BM | 5 | 60 | 8 |
| 10 | INDEX RANGE SCAN | BM_FK5| 13 | | 4 |
------------------------------------------------------------------------


I used parallel hint for the second part:




 
SELECT /*+ parallel (bm, 4) */
*
FROM a,
bi,
bm
WHERE bm.bsc = 3
AND bm.bsci = 'S'
AND a.bid = bi.bid
AND a.bid = bm.vp_bid
AND
EXISTS (
SELECT 1
FROM bm bm1
WHERE bm.vp_bid = bm1.vp_bid
AND bm1.bsci = 'S'
AND bm1.bsc NOT IN (3, 4));



And the execution plan looks like:




----------------------------------------------------------------------------
Id | Operation | Name|Rows|Bytes|Temp|Cost | TQ |IN-OUT|PQ
|Spc | | | |Distrib
----------------------------------------------------------------------------
0 | SELECT STATEMENT | | | | | 404K| | |
1 | HASH JOIN | | 22M|1814M|154M| 404K|98,00| P->S |QC(RAND)
2 | TABLE ACCESS FULL | BI | 15M| 438M| |17565|98,00| S->P | HASH
3 | HASH JOIN | | 22M|1202M|124M| 378K|98,00| PCWP |
4 | TABLE ACCESS FULL | A | 15M| 317M| |24624|98,00| S->P | HASH
5 | HASH JOIN SEMI | | 22M| 759M|189M| 348K|98,00| PCWP |
6 | TABLE ACCESS FULL| BM | 22M| 506M| |66931|98,00| P->P | HASH
7 | TABLE ACCESS FULL| BM |113M|1301M| | 267K|98,00| S->P | HASH
----------------------------------------------------------------------------


Good news was the first part finished in 3 hours, and the second part took 2 h 40 min. On Saturday afternoon, I did the real run and it took a little longer but finished in 4 hours.

At this point, I am not sure I chose the best way to accomplish this task. I just ensured that we deliverd as we promised. I don't know if it could be better if we just adopt the approach of using parallel hint 5 for all the three tables. It was too late for DBA to having sufficient time to find the best way to execute this SQL.

I will re-visit this SQL if I have time. Acutally I already invest some time to understand anti-join this evening - the first part of this SQL is acutally anti-join, but I have not seen Oracle choose anti-join access path in the current execution plan.

Wednesday, January 14, 2009

Obtain bind variable values in a SQL

I searched the internet and learned that sql trace can be used to obtain actual bind variable values in a SQL. The value will appear in the raw trace file, not available in the formatted reprot by TKPROF. I don't always remember the meaning of trace levels, usually just do 10046 with level 12 - highest level.

In 10 g, there is a view called v$sql_bind_capture that can be used for this purpose. See a post here.

Below is a test about using sql trace to find the bind varialbe values.

Once execute the followoing code, I check the trace file:




drop table t1;
create table t1 (v1 varchar2(20), n1 number);

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

variable b1 varchar2(5);
variable b2 number;

exec :b1 := 'abc5'; :b2 := 123;
insert into t1 values (:b1, :b2);
commit;

exec :b1 := 'efg'; :b2 := 456;
insert into t1 values (:b1, :b2);
commit;

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

exit;


Here are the results




******** Observiation in the trace 10.2.0.1:

Only can find the following related to that sql statment. Note the bind variable values for the
first time execution are not shown in the trace file:


=====================
PARSING IN CURSOR #7 len=32 dep=0 uid=62 oct=2 lid=62 tim=98152339039 hv=1961723033 ad='69629204'
insert into t1 values (:b1, :b2)
END OF STMT
PARSE #7:c=0,e=434,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=98152339027

=====================
PARSING IN CURSOR #3 len=32 dep=0 uid=62 oct=2 lid=62 tim=98152761378 hv=1961723033 ad='69629204'
insert into t1 values (:b1, :b2)
END OF STMT
PARSE #3:c=0,e=164,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=98152761367
BINDS #3:
kkscoacd
Bind#0
oacdty=01 mxl=32(05) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=01 csi=178 siz=56 off=0
kxsbbbfp=07c125b4 bln=32 avl=03 flg=05
value="efg"
Bind#1
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=00 csi=00 siz=0 off=32
kxsbbbfp=07c125d4 bln=22 avl=03 flg=01
value=456
EXEC #3:c=0,e=841,p=0,cr=1,cu=5,mis=0,r=1,dep=0,og=1,tim=98152762535
WAIT #3: nam='SQL*Net message to client' ela= 7 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=98152762774
WAIT #3: nam='SQL*Net message from client' ela= 375 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=98152763291


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

TKPROF

insert into t1
values
(:b1, :b2)


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.12 0.11 0 2 26 2
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.12 0.11 0 2 26 2

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 62

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 0.00 0.00








******** Observiation in the trace 9.2.0.8: we can see values for the two executions

=====================
PARSING IN CURSOR #1 len=32 dep=0 uid=178 oct=2 lid=178 tim=10450665631029 hv=340431440 ad='b8241108'
insert into t1 values (:b1, :b2)
END OF STMT
PARSE #1:c=0,e=2540,p=0,cr=5,cu=0,mis=1,r=0,dep=0,og=0,tim=10450665631025
BINDS #1:
bind 0: dty=1 mxl=32(05) mal=00 scl=00 pre=00 oacflg=03 oacfl2=0 size=56 offset=0
bfp=ffffffff7c95fb70 bln=32 avl=04 flg=05
value="abc5"
bind 1: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=03 oacfl2=0 size=0 offset=32
bfp=ffffffff7c95fb90 bln=22 avl=03 flg=01
value=123
EXEC #1:c=0,e=1043,p=0,cr=1,cu=20,mis=0,r=1,dep=0,og=4,tim=10450665632203
WAIT #1: nam='SQL*Net message to client' ela= 9 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 145321 p1=1413697536 p2=1 p3=0

=====================
PARSING IN CURSOR #1 len=32 dep=0 uid=178 oct=2 lid=178 tim=10450667390749 hv=340431440 ad='b8241108'
insert into t1 values (:b1, :b2)
END OF STMT
PARSE #1:c=0,e=156,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=10450667390743
BINDS #1:
bind 0: dty=1 mxl=32(05) mal=00 scl=00 pre=00 oacflg=03 oacfl2=0 size=56 offset=0
bfp=ffffffff7c95fb60 bln=32 avl=03 flg=05
value="efg"
bind 1: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=03 oacfl2=0 size=0 offset=32
bfp=ffffffff7c95fb80 bln=22 avl=03 flg=01
value=456
EXEC #1:c=0,e=554,p=0,cr=1,cu=4,mis=0,r=1,dep=0,og=4,tim=10450667391444
WAIT #1: nam='SQL*Net message to client' ela= 3 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 144598 p1=1413697536 p2=1 p3=0

Reference: Dealing with hang situation

When I opened a SR with Oracle about a slow performance problem due to blocking sessions, the support guy gave me an action plan about how to deal with hanging sql. It makes no sense to implement this action plan since problem has already gone. So I blog these commands here for furture reference. Hopefully when needed I can quickly find these commands.


ACTION PLAN
============
When the SQL appears to be hanging, Please generate system state and hanganalyze (should do hanganlyze first?):

$ sqlplus /nolog
connect / as sysdba
oradebug setmypid
oradebug unlimit
oradebug dump systemstate 266
wait 90 seconds
oradebug dump systemstate 266
wait 90 seconds
oradebug dump systemstate 266

In another session, generate hanganalyze:

1-Using SQL*Plus connect as "/ AS SYSDBA"
2- Execute: oradebug hanganalyze 3
3- Wait 1 minute....
4- Execute: oradebug hanganalyze 3


Feb 5, 2010 Updated:

Kyle Hailey mentioned: 

If you have such an instance hang you can use a preliminary connection (which starts the process and attaches to SGA, but doesn't initialize SGA structs nor allocate any state objects):

sqlplus -prelim "/as sysdba"
oradebug dump latches 1
oradebug dump hanganalyze 4

Anup Nanda described a real case hung scenario here.

Related MOS notes:

1. Interpreting HANGANALYZE trace files to diagnose hanging and performance problems [ID 215858.1]
2. Steps to generate HANGANALYZE trace files [ID 175006.1]
3. Systemstate dump when connection to the instance is not possible [ID 359536.1]

Saturday, January 10, 2009

Recursive SQL statement: update seq$

Last Friday, one of our production database experienced slowness in about 1 hours time period. During that period of time, there were 60+ blocking sessions. One of the blocking session was issuing the following statment:


update seq$ set
increment$=:2,minvalue=:3,maxvalue=:4,cycle#=
:5,order$=:6,cache=:7,highwater=:8,audit$=:9,
flags=:10 where obj#=:1

 

This looks like having somthing to do with the sequences. Our team leader suggested that we should increase the cache size for two relevant sequences to proactively avoid the problem. I acutally opened a SR with Oracle, asking what this statment is doing. But they did not give direct answer to this specific question so far. On the other hand, the guy suggested we should increase the log file size to reduce "log file sync" wait event, which appears at the top of the wait event in our statspack report. His suggestions is obviously nonsense. He must have no idea about what "log file sync" wait event is. I am disappointed with the support.

To understand this update statment, I did a test today. I created a table T and a sequence T_SEQ:



labadmin@DB10G> desc t;
Name Null? Type
------------------------- -------- ------------------------------------
ID NUMBER
VALUE VARCHAR2(20)

labadmin@DB10G> select * from user_sequences;

SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
--------------------- ---------- ---------- ------------ - - ---------- -----------
T_SEQ 1 1.0000E+27 1 N N 20 904821



 

Then I have 20 sessions repeatedly executing the following insert statment simutaneouly:

isnert into t values(t_seq.nextval, 'xxxx');

I can observe there is one blocking sessions from time to time, for example:


Lock Time Held
SID Lock Type Requested Lock Held (minutes) Block
------ --------- --------- --------- --------- -----
126 SQ None Exclusive 0 Yes
99 SQ Exclusive None 0 No
124 SQ Exclusive None 0 No
123 SQ Exclusive None 0 No
128 SQ Exclusive None 0 No
158 SQ Exclusive None 0 No
109 SQ Exclusive None 0 No
159 SQ Exclusive None 0 No
130 SQ Exclusive None 0 No
114 SQ Exclusive None 0 No
131 SQ Exclusive None 0 No
....k



I traced one of the 20 sessions, found that:



update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,
cache=:7,highwater=:8,audit$=:9,flags=:10
where
obj#=:1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 561 0.03 0.05 0 0 0 0
Execute 561 2.04 8.81 0 561 1132 561
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1122 2.07 8.87 0 561 1132 561

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

Rows Row Source Operation
------- ---------------------------------------------------
1 UPDATE SEQ$ (cr=1 pr=0 pw=0 time=339 us)
1 INDEX UNIQUE SCAN I_SEQ1 (cr=1 pr=0 pw=0 time=40 us)(object id 102)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
latch: undo global data 2 0.00 0.00
latch: library cache 1 0.01 0.01
log buffer space 8 1.00 6.39
latch: library cache pin 2 0.10 0.10
********************************************************************************

INSERT INTO T
VALUES
(T_SEQ.NEXTVAL, :B1 )


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 9026 8.51 351.24 0 211 21192 9026
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 9026 8.51 351.24 0 211 21192 9026

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

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
enq: SQ - contention 775 3.07 258.38
latch: enqueue hash chains 6 0.03 0.06
log buffer space 38 1.36 21.72
latch: library cache pin 3 0.01 0.01
latch free 12 0.40 1.52
free buffer waits 1717 0.08 25.67
latch: library cache 10 0.15 0.62
latch: In memory undo latch 8 1.27 2.56
latch: redo copy 6 0.33 0.41
buffer busy waits 19 0.98 4.37
latch: undo global data 4 0.07 0.09
enq: FB - contention 2 0.82 0.98
enq: TX - contention 2 0.22 0.22
enq: HW - contention 2 0.45 0.45
latch: session allocation 1 0.00 0.00
********************************************************************************



 

So it can be seen that the "update seq$ ..." statement is a recursive sql issued by Oracle sys user to update dictionary table about sequence.

I did a further test:

I set the cache of t_seq to be 20, and I trace the session when executing following statement:

insert into t
select t_seq.nextval, 'qqq' from all_objects where rownum <=100;

I found the 'update seq$' statment executed 5 times.

Then I set the cache of t_seq to be 50, I found the 'update seq$' executed 2 time.

Thus, it looks like that every time that the cached sequence number is used up, Oracle should do the caching again and issue this statment to update dictionary table.

Thursday, January 08, 2009

Hierachical SQL used to combine multiple rows

A user asked a question about how to combine rows that have same key value into one rows on the Boobooke Oracle forum.
For example, for the table:

SQL> select * from t;

KEY VALUE
---------- --------------------
1 a
1 b
1 c

How to write a sql to give the output looks like:

KEY VALUE
--- -----
1 a/b/c

He actually gave the answer. Based on his input, I did a test. First I created the following table:



SQL> create table uavsub (num_prod number,
2 summary number,
3 cod_dep varchar2(20)
4 );

Table created.

SQ>
SQL> insert into uavsub values (3, 3, 'a');

1 row created.

SQL> insert into uavsub values (2, 9, 'b');

1 row created.

SQL> insert into uavsub values (1, 1, 'a');

1 row created.

SQL> insert into uavsub values (1, 3, 'a');

1 row created.

SQL> insert into uavsub values (3, 4, 'a');

1 row created.

SQL> insert into uavsub values (2, 1, 'a');

1 row created.

SQL> insert into uavsub values (3, 1, 'a');

1 row created.

SQL> insert into uavsub values (3, 2, 'a');

1 row created.

SQL> insert into uavsub values (1, 2, 'b');

1 row created.

SQL> insert into uavsub values (3, 3, 'a');

1 row created.

SQL> commit;

Commit complete.

SQ>
SQL> col summary format 999
SQL> select * from uavsub;

NUM_PROD SUMMARY COD_DEP
-------- ------- ---------
3 3 a
2 9 b
1 1 a
1 3 a
3 4 a
2 1 a
3 1 a
3 2 a
1 2 b
3 3 a

10 rows selected.



Then I executed the following sql, which only gave ouput for the case of num_prod=3:



SQL>
SQL> select t.num_prod num_prod,
2 max(substr(sys_connect_by_path(t.summary, '/'), 2)) summary
3 from (
4 select num_prod,
5 summary,
6 row_number() over (partition by num_prod order by summary ) rn
7 from uavsub
8 where num_prod=3
9 and cod_dep is not null
10 ) t
11 start with rn = 1
12 connect by rn = prior rn + 1
13 and num_prod = prior num_prod
14 group by t.num_prod;

NUM_PROD SUMMARY
-------- ------
3 1/2/3/3/4



The below sql lift the constraint: num_prod=3



SQL>
SQL>
SQL> col summary format a20
SQL>
SQL> select t.num_prod num_prod,
2 max(substr(sys_connect_by_path(t.summary, '/'), 2)) summary
3 from (
4 select num_prod,
5 summary,
6 row_number() over (partition by num_prod order by summary ) rn
7 from uavsub
8 where cod_dep is not null
9 ) t
10 start with rn = 1
11 connect by rn = prior rn + 1
12 and num_prod = prior num_prod
13 group by t.num_prod;


NUM_PROD SUMMARY
-------- --------------------
1 1/2/3
2 1/9
3 1/2/3/3/4



  

However, I did not fully understand the "connect by" sql at that time. I don't know if this is the best way to solve the problem, but this did urge me to understand hierarchical sql better. So I did a few more tests as follows:

For brevity, I create a view first

SQL> create view t_vw as
2 select key, value, row_number() over (partition by key order by value) rn from t;

View created.

SQL> select * from t_vw;

KEY VALUE RN
---------- --------- ----------
1 a 1
1 b 2
1 c 3

Then, I tested the meaning of sys_connect_by_path,

SQL>;
1 select key, value, sys_connect_by_path(value, '/') path from t_vw
2 start with rn = 1
3* connect by rn = prior rn + 1

SQL>

KEY VALUE PATH
---------- -------------------- ----------
1 a /a
1 b /a/b
1 c /a/b/c

Then:

SQL> ;
1 select key, max(sys_connect_by_path(value, '/')) path from t_vw
2 start with rn = 1
3 connect by rn = prior rn + 1
4* group by key
SQL> /

KEY PATH
---------- ------------------------------
1 /a/b/c


Finally,

1 select key, substr(max(sys_connect_by_path(value, '/')),2) path from t_vw
2 start with rn = 1
3 connect by rn = prior rn + 1
4* group by key
SQL> /

KEY PATH
---------- ------------------------------
1 a/b/c