If you are using Oracle Enterprise Linux (OEL) 5, the installation media comes with a yum repository on it. The repository is in the directory /media/Enterprise Linux dvd 20090127/Server/repodata for OEL 5.3 (the location may change).
It is possible to use that repository when installing new packages or components locally without accessing a remote repository or without having the need to copy the rpms to a local directory. If you did a base installation the yum package is already installed, if not you need to install it first. After yum is in place edit /etc/yum.conf to insert lines related to the repository on the installation media.
[main]
cachedir=/var/cache/yum
keepcache=0
debuglevel=2
logfile=/var/log/yum.log
distroverpkg=redhat-release
tolerant=1
exactarch=1
obsoletes=1
gpgcheck=1
plugins=1
# Note: yum-RHN-plugin doesn't honor this.
metadata_expire=1h
# Default.
# installonly_limit = 3
# PUT YOUR REPOS HERE OR IN separate files named file.repo
# in /etc/yum.repos.d
[local]
name="media"
baseurl=file:///media/Enterprise%20Linux%20dvd%2020090127/Server
enabled=1
gpgkey=file:///media/Enterprise%20Linux%20dvd%2020090127/RPM-GPG-KEY-oracle
You need to add only the last 5 lines, the other ones are already there.
After this you can use yum to add packages or components when needed. An example to install the oracle-validated rpm from this repository will be in my next post.
Tuesday, November 09, 2010
How to use the Oracle Enterprise Linux installation media as the yum repository
Monday, November 08, 2010
Starting a vm in VirtualBox hangs
When trying to start a new vm in Oracle VM VirtualBox 3.2.10 it hot hung at progress 0%. There is no way to cancel the startup without killing the process from the host OS.
The first thing to look at is the VBox.log file which resides under C:\Documents and Settings\
I had these lines at the bottom of the log file.
00:00:04.228 AIOMgr: Endpoint for file 'E:\vm\OELTest\OELTest_boot.vdi' (flags 000c0723) created successfully
00:00:04.228 AIOMgr: I/O manager 0x3f3f898 encountered a critical error (rc=VERR_INVALID_PARAMETER) during operation. Falling back to failsafe mode. Expect reduced performance
00:00:04.228 AIOMgr: Error happened in D:\tinderbox\win-3.2\src\VBox\VMM\PDMAsyncCompletionFileNormal.cpp:(1631){pdmacFileAioMgrNormal}
00:00:04.228 AIOMgr: Please contact the product vendor
This seems like the problem is related to the virtual disk I created as the root disk. I am using the SATA interface and in the virtual machine storage settings there is an option named "Use host I/O cache" which is unchecked in my case. Checking it and starting up the vm again resolves the issue.
There are lots of Google results when you search for messages in the log file but the issue has been explained in http://www.virtualbox.org/ticket/7363.
Friday, June 19, 2009
opatch problem on Windows
There is a note in Metalink that explains that on Windows having space characters in your ORACLE_HOME variable, the patch location or JDK location causes an error when running opatch. Yesterday I saw a strange problem that is similar to the above case.
If your opatch directory contains space characters you get a strange error. Even if the above conditions were not present we got an error like this:
C:\Documents and Settings\test\Desktop\OPatch>opatch lsinventory
Exception in thread "main" java.lang.NoClassDefFoundError: and
OPatch failed with error code = 1
Metalink returns no results for this error. This error is caused by the space characters in "Documents and Settings". When you move the opatch directory to another directory which does not contain space in its name opatch runs without this problem.
Just a note to help in case someone gets the same error.
DBFS
Yesterday I attended Kevin Closson's Exadata technical deep dive webcast series part 4. It is now available to download here. In there he talks about DBFS which is a filesystem on top of the Oracle database which can store normal files like text files. DBFS is provided with Exadata and is used to store staging files for the ETL/ELT process. This looks very promising, he sites several tests he conducted and gives performance numbers too. Watch the webcast if you haven't yet.
Tuesday, June 09, 2009
Tablespace selection in interval partitioning
11G brought interval partitioning which is a new partitioning method to ease the maintenance burden of adding new partitions manually. The interval partition clause in the create table statement has an option to list tablespace names to be used for interval partitioning. The documentation states that the tablespaces in the list you provide are used in a round-robin manner for new partitions:
Interval partitions are created in the provided list of tablespaces in a round-robin manner.
This does not mean that any newly created partition will reside in the tablespace which is next on the list. The tablespaces may be skipped if partitions map to more than one interval. Here is a test case that shows how the list is used.
set lines 200
SQL> r
1 create table t(col1 date,col2 varchar2(100))
2 partition by range (col1)
3 interval(numtoyminterval(1,'MONTH')) store in (tbs1,tbs2,tbs3)
4* (PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2009', 'DD-MM-YYYY')) tablespace tbs1)
Table created.
SQL> r
1 select PARTITION_NAME,HIGH_VALUE,TABLESPACE_NAME
2* from user_Tab_partitions where table_name='T'
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
------------------------------ -------------------------------------------------------------------------------- ------------------------------
P0 TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS1
The "store in" clause lists tablespaces tbs1, tbs2 and tbs3 to be used for interval partitioning. After the above create table command I now have one partition which resides in tbs1. Let's insert a row which needs to be inserted into a new partition and see which tablespace the partition will be created in.
SQL> insert into t values(to_date('15.01.2009','dd.mm.yyyy'),'jan');
1 row created.
SQL> commit;
Commit complete.
SQL> select PARTITION_NAME,HIGH_VALUE,TABLESPACE_NAME
2 from user_Tab_partitions where table_name='T';
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
------------------------------ -------------------------------------------------------------------------------- ------------------------------
P0 TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS1
SYS_P61 TO_DATE(' 2009-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS2
The row I inserted maps to one interval, which is one month, it does not have a date value which is more than one month higher than the current maximum value. So the next tablespace, tbs2, is used for the new partition.
SQL> insert into t values(to_date('15.02.2009','dd.mm.yyyy'),'feb');
1 row created.
SQL> commit;
Commit complete.
SQL> select PARTITION_NAME,HIGH_VALUE,TABLESPACE_NAME
2 from user_Tab_partitions where table_name='T';
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
------------------------------ -------------------------------------------------------------------------------- ------------------------------
SYS_P62 TO_DATE(' 2009-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS3
P0 TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS1
SYS_P61 TO_DATE(' 2009-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS2
Again I inserted a row for the next month and the partition is created in tbs3, which is the next one on the list.
What happens if I insert a row with a date value that is more than one month after the current maximum partitioning key?
SQL> insert into t values(to_date('15.04.2009','dd.mm.yyyy'),'apr');
1 row created.
SQL> commit;
Commit complete.
SQL> select PARTITION_NAME,HIGH_VALUE,TABLESPACE_NAME
2 from user_Tab_partitions where table_name='T';
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
------------------------------ -------------------------------------------------------------------------------- ------------------------------
SYS_P62 TO_DATE(' 2009-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS3
P0 TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS1
SYS_P61 TO_DATE(' 2009-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS2
SYS_P63 TO_DATE(' 2009-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS2
I skipped March and inserted a value for April. The current maximum key becomes May 1st, we do not see a partition with a maximum value of Apr 1st. The next tablespace on the list was tbs1 but we see that the new partition is on tbs2, not tbs1. Tbs1 would be used if I did not skip an interval when inserting rows.
So, the tablespaces on the list are used in a round-robin manner but each is used for only one interval. If you skip intervals the tablespaces related to that interval are skipped too.
This is something to keep in mind if you want to strictly decide which tablespace will hold which partition.
Wednesday, March 04, 2009
Different plans for a sql with the rule hint
I was trying to find out why a query with the RULE hint produces different plans. I got stuck so I posted the problem to the OTN database forum and Randolf Geist provided a good answer and starting point for it. A second eye on the problem can remove the mind blockage and clear your way.
Monday, February 23, 2009
Remote dependencies
When changing plsql code in a production system the dependencies between objects can cause some programs to be invalidated. Making sure all programs are valid before opening the system to users is very important for application availability. Oracle automatically compiles invalid programs on their first execution, but a successfull compilation may not be possible because the error may need code correction or the compilation may cause library cache locks when applications are running.
Dependencies between plsql programs residing in the same database are easy to handle. When you compile a program the programs dependent on that one are invalidated right away and we can see the invalid programs and compile or correct them before opening the system to the users.
If you have db links and if your plsql programs are dependent on programs residing in other databases you have a problem handling the invalidations. There is an initialization parameter named remote_dependencies_mode that handles this dependency management. If it is set to TIMESTAMP the timestamp of the local program is compared to that of the remote program. If the remote program's timestamp is more recent than the local program, the local program is invalidated in the first run. If the parameter is set to SIGNATURE the remote program's signature (number and types of parameters, subprogram names, etc...) is checked, if there has been a change the local program is invalidated in the first run.
The problem here is; if you change the remote program's signature or timestamp you cannot see that the local program is invalidated because it will wait for the first execution to be invalidated. If you open the system to the users before correcting this you may face a serious application problem leading to downtime.
Here is a simple test case to see the problem.
I start by creating a plsql package in the database TEST.
SQL> create package test_pack as
2 procedure test_proc(a number);
3 end;
4 /
Package created.
SQL> create package body test_pack as
2 procedure test_proc(a number) is
3 begin
4 null;
5 end;
6 end;
7 /
Package body created.
On another database let's create a database link pointing to the remote database TEST, create a synonym for the remote package and create a local package calling the remote one.
SQL> create database link test.world connect to yas identified by yas using 'TEST';
Database link created.
SQL> select * from dual@yas.world;
D
-
X
SQL> create synonym test_pack for test_pack@yasbs.world;
Synonym created.
SQL> create package local_pack as
2 procedure local_test(a number);
3 end;
4 /
Package created.
SQL> r
1 create or replace package body local_pack as
2 procedure local_test(a number) is
3 begin
4 test_pack.test_proc(1);
5 end;
6* end;
Package body created.
If we look at the status of this local package we see that it is valid.
SQL> col object_name format a30
SQL> r
1* select status,object_name,object_type from user_objects where object_name='LOCAL_PACK'
STATUS OBJECT_NAME OBJECT_TYPE
------- ------------------------------ ------------------
VALID LOCAL_PACK PACKAGE
VALID LOCAL_PACK PACKAGE BODY
We can execute it without any problems.
SQL> exec LOCAL_PACK.local_test(1);
PL/SQL procedure successfully completed.
Now, let's change the remote package code including the package specification.
SQL> create or replace package test_pack as
2 procedure test_proc(b number,a number default 1);
3 end;
4 /
Package created.
SQL> create or replace package body test_pack as
2 procedure test_proc(b number,a number default 1) is
3 begin
4 null;
5 end;
6 end;
7 /
Package body created.
When we look at the local package we see its status as valid.
SQL> r
1* select status,object_name,object_type from user_objects where object_name='LOCAL_PACK'
STATUS OBJECT_NAME OBJECT_TYPE
------- ------------------------------ ------------------
VALID LOCAL_PACK PACKAGE
VALID LOCAL_PACK PACKAGE BODY
But when it is executed we get an error.
SQL> exec LOCAL_PACK.local_test(1);
BEGIN LOCAL_PACK.local_test(1); END;
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04062: signature of package "YASBS.TEST_PACK" has been changed
ORA-06512: at "YASBS.LOCAL_PACK", line 4
ORA-06512: at line 1
SQL> select status,object_name,object_type from user_objects where object_name='LOCAL_PACK';
STATUS OBJECT_NAME OBJECT_TYPE
------- ------------------------------ ------------------
VALID LOCAL_PACK PACKAGE
INVALID LOCAL_PACK PACKAGE BODY
SQL> exec LOCAL_PACK.local_test(1);
PL/SQL procedure successfully completed.
SQL> select status,object_name,object_type from user_objects where object_name='LOCAL_PACK';
STATUS OBJECT_NAME OBJECT_TYPE
------- ------------------------------ ------------------
VALID LOCAL_PACK PACKAGE
VALID LOCAL_PACK PACKAGE BODY
The second execution compiles the package and returns the status to valid.
How can we know beforehand that the local program will be invalidated on the first execution? The only way I can think of is to check the dependencies across all databases involved. By collecting all rows from dba_dependencies from all databases we can see that when the remote program is changed the programs on other databases that use this remote program will be invalidated when they are executed. Then we can compile these programs and see if they compile without errors.
Database links may be very annoying sometimes, this case is just one of them.
Wednesday, August 06, 2008
Index block split bug in 9i
In his famous index internals presentation Richard Foote mentions a bug in 9i about index block splits when rows are inserted in the order of the index columns. Depending on when you commit your inserts the index size changes dramatically.
While I was trying to find out why a 3-column primary key index takes more space than its table I recalled that bug and it turned out that was the reason of the space issue. The related bug is 3196414 and it is fixed in 10G.
Here is the test case Richard presents in his paper.
SQL> create table t(id number,value varchar2(10));
Table created.
SQL> create index t_ind on t(id);
Index created.
SQL> @mystat split
NAME VALUE
------------------------------ ----------
leaf node splits 0
leaf node 90-10 splits 0
branch node splits 0
SQL> ed
Wrote file afiedt.buf
1 begin
2 for i in 1..10000 loop
3 insert into t values(i,'test');
4 commit;
5 end loop;
6* end;
SQL> r
1 begin
2 for i in 1..10000 loop
3 insert into t values(i,'test');
4 commit;
5 end loop;
6* end;
PL/SQL procedure successfully completed.
SQL> @mystat2 split
NAME VALUE DIFF
------------------------------ ---------- ----------
leaf node splits 35 35
leaf node 90-10 splits 0 0
branch node splits 0 0
SQL> analyze index t_ind validate structure;
Index analyzed.
SQL> select lf_blks, pct_used from index_stats;
LF_BLKS PCT_USED
---------- ----------
36 51
SQL> drop table t;
Table dropped.
I am trying to insert the rows in the order of the primary key column, so what I expect to see is that when an index block fills there will be a 90-10 split and the index will grow in size. But as the number of leaf block splits show there are 35 block splits and none of them are 90-10 splits meaning all are 50-50 block splits. I have 36 leaf blocks but half of each one is empty.
If we try the same inserts but commit after the loop the result changes.
SQL> create table t(id number,value varchar2(10));
Table created.
SQL> create index t_ind on t(id);
Index created.
SQL> @mystat split
NAME VALUE
------------------------------ ----------
leaf node splits 35
leaf node 90-10 splits 0
branch node splits 0
SQL> ed
Wrote file afiedt.buf
1 begin
2 for i in 1..10000 loop
3 insert into t values(i,'test');
4 end loop;
5 commit;
6* end;
SQL> r
1 begin
2 for i in 1..10000 loop
3 insert into t values(i,'test');
4 end loop;
5 commit;
6* end;
PL/SQL procedure successfully completed.
SQL> @mystat2 split
NAME VALUE DIFF
------------------------------ ---------- ----------
leaf node splits 53 53
leaf node 90-10 splits 18 18
branch node splits 0 0
SQL> analyze index t_ind validate structure;
Index analyzed.
SQL> select lf_blks, pct_used from index_stats;
LF_BLKS PCT_USED
---------- ----------
19 94
In this case we see that there have been 18 block splits and all were 90-10 splits as expected. We have 19 leaf blocks and all are nearly full. Depending on where the commit is we can get an index twice the size it has to be. When I ran the same test in 10G it did not matter where the commit was. I got 19 leaf blocks in both cases.
I did not test if this problem happens when several sessions insert a single row and commit just like in an OLTP system but I think it is likely because we have indexes showing this behavior in OLTP systems.
Friday, August 01, 2008
OTN members, don't change your e-mail account!
I am regular user of OTN and its forums. Last week I was trying to login to OTN from a public computer and I got the "invalid login" error everytime I tried. I was sure I was typing my password correct but I could not get in anyway. So, I tried to get my password reset and sent to my e-mail address. Then I remembered that the e-mail address I used to register for OTN was from my previous employer meaning I did not have access to it anymore. As OTN does not allow changing the registration e-mail address I was stuck. I send a request from OTN to get my password delivered to my current e-mail address. Here is the reply I got:
Resolution: Oracle's membership management system does not currently support
the editing of the email address or username in your membership profile.
(It will support this capability in a future release.)
Please create a new account with the new email address you wish to use. However,
it is possible to change the email at which you receive
Discussion Forum "watch" emails (see "Your Control Panel" when logged in).They tell me to create a new user and forget about my history, watch list, everything. What a user centric approach this is.If you are an OTN member do not lose your password and your e-mail account at the same time, you will not find anybody from OTN who is willing to solve your problem and help you to recover your password.
I am used to bad behavior and unwillingness to solve problems in Metalink, now I get the same behavior in OTN. Whatever, just wanted to let you know about it.
Thursday, April 17, 2008
Materialized view refresh change in 10G and sql tracing
Complete refresh of a single materialized view used to do a truncate and insert on the mview table until 10G. Starting with 10G the refresh does a delete and insert on the mview table. This guarantees that the table is never empty in case of an error, the refresh process became an atomic operation.
There is another difference between 9.2 and 10G in the refresh process, which I have realized when trying to find out why a DDL trigger prevented a refresh operation. In 9.2 the refresh process runs an ALTER SUMMARY statement against the mview while in 10G it does not.
We have a DDL trigger in some of the test environments (all 9.2) for preventing some operations. The trigger checks the type of the DDL and allows or disallows it based on some conditions. After this trigger was put in place some developers started to complain about some periodic mview refresh operations failing a few weeks ago. I knew it was not because of the TRUNCATE because the trigger allowed truncate operations.
So I enabled sql tracing and found out what the refresh process was doing. Here is a simplified test case for it in 10G.
YAS@10G>create table master as select * from all_objects where rownum<=5;
Table created.
YAS@10G>alter table master add primary key (owner,object_name,object_type);
Table altered.
YAS@10G>create materialized view mview as select * from master;
Materialized view created.
YAS@10G>exec dbms_mview.refresh('MVIEW');
PL/SQL procedure successfully completed.
YAS@10G>create or replace trigger ddl_trigger before ddl on schema
2 begin
3 if ora_sysevent<>'TRUNCATE' then
4 raise_application_error(-20001,'DDL NOT ALLOWED');
5 end if;
6 end;
7 /
Trigger created.
YAS@10G>exec dbms_mview.refresh('MVIEW');
PL/SQL procedure successfully completed.
The refresh was successful in 10G even after the DDL trigger was active. In 9.2 it errors out after the DDL trigger is enabled.
SQL> exec dbms_mview.refresh('MVIEW');
BEGIN dbms_mview.refresh('MVIEW'); END;
*
ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of YAS.MVIEW
ORA-20001: DDL NOT ALLOWED
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 820
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 877
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 858
ORA-06512: at line 1
After enabling sql trace and running the refresh again, the trace file shows the problem statement.
SQL> alter session set sql_trace=true;
Session altered.
SQL> exec dbms_mview.refresh('MVIEW');
BEGIN dbms_mview.refresh('MVIEW'); END;
*
ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of YAS.MVIEW
ORA-20001: DDL NOT ALLOWED
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 820
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 877
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 858
ORA-06512: at line 1
The last statement in the trace file is this:
ALTER SUMMARY "YAS"."MVIEW" COMPILE
So, in 9.2 refresh process runs an ALTER SUMMARY command against the mview. After changing the trigger to allow DDL operations on SUMMARY objects I could refresh the mview without errors.
Tonguc Yilmaz had a recent post about using 10046 for purposes other than performance tuning, like finding out what statements a procedure runs. I find uses for sql tracing nearly everyday and the above case is one of them. When you are not able to understand why an error happens, it is sometimes very useful to turn on sql tracing and examine the trace file. Errors related to specific bind values, errors raised from a "when others" block (you know we do not like "when others", right?) are a couple of things you can dig and analyze with sql tracing.
SQL> r
1 create or replace trigger ddl_trigger before ddl on schema
2 begin
3 if ora_sysevent<>'TRUNCATE' and ora_dict_obj_type<>'SUMMARY' then
4 raise_application_error(-20001,'DDL NOT ALLOWED');
5 end if;
6* end;
Trigger created.
SQL> exec dbms_mview.refresh('MVIEW');
PL/SQL procedure successfully completed.
Friday, April 11, 2008
Disqus
Last week I read a post in Andy C's blog about the service called Disqus. It is a service to keep track of comments in blogs. Later he made another post about it.
I have been looking for a solution to keep track of blog comments, both mine and other people's. Not all blogs have the option to subscribe to the comments, when you comment on a post you need to check later if someone commented further. I want a central repository where I can see all my comments on a post, all comments made by others on the same post and all comments made on my own blog.
I tried Cocomment before but I was not satisfied with it. So I decided to give Disqus a try and enabled it on this blog. Laurent Schneider decided to try it too.
Then Andy made another post about the concerns of some people about Disqus (one being Tim Hall). Their concerns make sense. Who owns the comments one makes, the commenter or the blog owner? Is it sensible to store the comments to your blog not in your blog database, but elsewhere? What if Disqus is not there next year, what if Disqus is inaccessible for some time? Is it possible to export the comments from Disqus and import them back to the blog?
Some of these may be irrelevant if you are using a public blogging service, like Blogger, because it means you are already storing your posts and comments somewhere else. The question "What if Blogger is not there next year?" comes to mind for example.
A solution suggested by Tim Hall is to dual post the comments. The comments will be in the blog and on Disqus also, this need the blogging service to provide a way to do it.
Another solution can be a strong export-import utility in Disqus. That way you can export the comments and put them back to the blog whenever you want. Disqus currently has an export utility but as far as I have read it is not reliable for now.
While I agree with these concerns I liked what Disqus provides. The one-stop page for all comments, the threading of comments, being able to follow other users are primary features I like. So, I will stick with it, at least for now.
Collect in 10G
Collections can be a great help in speeding up the PL/SQL programs. By using bulk collect operations it is possible to get great performance improvements.
In 9.2 we needed to use the bulk collect clause to fetch rows into a collection. 10G brings a new function called COLLECT, which takes a column as a parameter and returns a nested table containing the column values. Using this we can get the data into a collection without using bulk collect.
Here is a very simple demo of this new function.
YAS@10G>create table t as select * from all_objects;
Table created.
YAS@10G>create or replace type name_type as table of varchar2(30);
2 /
Type created.
YAS@10G>set serveroutput on
YAS@10G>r
1 declare
2 v_names name_type;
3 begin
4 select cast(collect(object_name) as name_type) into v_names from t;
5 dbms_output.put_line(v_names.count);
6 dbms_output.put_line(v_names(1));
7* end;
42268
ICOL$
PL/SQL procedure successfully completed.
One difference between this and bulk collect is, since this a sql function we need a sql type for this, it cannot be used with local PL/SQL types.
YAS@10G>r
1 declare
2 type name_type is table of varchar2(30);
3 v_names name_type;
4 begin
5 select collect(object_name) into v_names from t;
6* end;
select collect(object_name) into v_names from t;
*
ERROR at line 5:
ORA-06550: line 5, column 35:
PLS-00642: local collection types not allowed in SQL statements
ORA-06550: line 5, column 9:
PL/SQL: ORA-00932: inconsistent datatypes: expected CHAR got -
ORA-06550: line 5, column 2:
PL/SQL: SQL Statement ignored
Another difference, and a more important one, is the performance difference between these two constructs. Using Tom Kyte's runstats package, I did a test to compare the two. I ran the test several times, the results were similar.
YAS@10G>r
1 declare
2 v_names name_type;
3 begin
4 runStats_pkg.rs_start;
5
6 for i in 1..1000 loop
7 select object_name bulk collect into v_names from t;
8 end loop;
9
10 runStats_pkg.rs_middle;
11
12 for i in 1..1000 loop
13 select cast(collect(object_name) as name_type) into v_names from t;
14 end loop;
15
16 runStats_pkg.rs_stop;
17
18* end;
Run1 ran in 1329 hsecs
Run2 ran in 4060 hsecs
run 1 ran in 32.73% of the time
Name Run1 Run2 Diff
LATCH.qmn state object latch 0 1 1
STAT...redo entries 9 10 1
LATCH.JS slv state obj latch 1 0 -1
LATCH.qmn task queue latch 5 6 1
LATCH.transaction branch alloc 0 1 1
LATCH.sort extent pool 0 1 1
LATCH.resmgr:actses change gro 1 0 -1
LATCH.ncodef allocation latch 0 1 1
LATCH.slave class 0 1 1
LATCH.archive control 0 1 1
LATCH.FAL subheap alocation 0 1 1
LATCH.FAL request queue 0 1 1
STAT...heap block compress 6 5 -1
LATCH.session switching 0 1 1
LATCH.ksuosstats global area 1 2 1
LATCH.event group latch 1 0 -1
LATCH.threshold alerts latch 0 1 1
LATCH.list of block allocation 2 0 -2
LATCH.transaction allocation 2 0 -2
LATCH.dummy allocation 3 1 -2
LATCH.user lock 2 0 -2
LATCH.Consistent RBA 4 2 -2
STAT...calls to kcmgcs 4 6 2
STAT...active txn count during 4 6 2
STAT...cleanout - number of kt 4 6 2
STAT...consistent gets 587,009 587,011 2
STAT...consistent gets from ca 587,009 587,011 2
STAT...consistent gets - exami 4 6 2
LATCH.resmgr:free threads list 3 0 -3
LATCH.PL/SQL warning settings 3 0 -3
LATCH.OS process 6 3 -3
LATCH.compile environment latc 3 0 -3
LATCH.slave class create 0 3 3
LATCH.resmgr:actses active lis 3 0 -3
LATCH.cache buffers lru chain 0 3 3
LATCH.OS process allocation 10 14 4
LATCH.session state list latch 4 0 -4
LATCH.redo allocation 50 46 -4
STAT...consistent changes 17 22 5
LATCH.resmgr group change latc 5 0 -5
STAT...db block gets 17 22 5
STAT...db block gets from cach 17 22 5
LATCH.library cache pin alloca 6 0 -6
STAT...db block changes 26 32 6
STAT...session logical reads 587,026 587,033 7
LATCH.In memory undo latch 23 16 -7
LATCH.session idle bit 10 3 -7
LATCH.mostly latch-free SCN 6 14 8
LATCH.KMG MMAN ready and start 5 13 8
LATCH.lgwr LWN SCN 6 14 8
LATCH.simulator hash latch 34,010 34,001 -9
LATCH.simulator lru latch 34,010 34,001 -9
LATCH.session timer 5 14 9
LATCH.dml lock allocation 10 1 -9
LATCH.undo global data 20 10 -10
LATCH.post/wait queue 10 0 -10
LATCH.active checkpoint queue 4 15 11
LATCH.session allocation 13 2 -11
LATCH.archive process latch 4 15 11
LATCH.library cache lock alloc 16 0 -16
LATCH.object queue header oper 8 32 24
LATCH.client/application info 25 0 -25
LATCH.redo writing 24 51 27
LATCH.active service list 37 81 44
STAT...undo change vector size 2,124 2,200 76
LATCH.channel operations paren 60 197 137
LATCH.cache buffers chains 1,174,359 1,174,189 -170
LATCH.JS queue state obj latch 108 288 180
LATCH.messages 108 291 183
STAT...redo size 2,772 2,964 192
LATCH.checkpoint queue latch 80 282 202
LATCH.enqueue hash chains 269 652 383
LATCH.enqueues 248 645 397
LATCH.SQL memory manager worka 276 944 668
LATCH.shared pool 42 1,029 987
LATCH.library cache lock 170 2,018 1,848
LATCH.library cache pin 2,130 4,064 1,934
STAT...Elapsed Time 1,330 4,061 2,731
STAT...CPU used by this sessio 1,334 4,083 2,749
STAT...recursive cpu usage 1,268 4,064 2,796
LATCH.library cache 2,264 5,074 2,810
LATCH.row cache objects 49 9,015 8,966
STAT...session pga memory 2,097,152 1,441,792 -655,360
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
1,248,536 1,267,073 18,537 98.54%
PL/SQL procedure successfully completed.
As you see the bulk collect method completes in 1/3rd of the time of the collect function method. Using the collect function hits the library cache harder and uses more latch operations.
The COLLECT function can be used in sql statements to compare collections of columns. In Laurent Schneider's comment on my previous post you can find an example of it.
Wednesday, April 09, 2008
Relational algebra: division in sql
There was a question in one of the Turkish Oracle mailing lists which got me interested. The question simply was:
I have a table holding the parts of a product and another table holding the suppliers of these parts. How can I find the suppliers which supply all the parts?
Someone suggested using the division operation of relational algebra but did not provide how to do it in Oracle. So I started with the Wikipedia link he provided to solve the problem in sql.
Here are the tables:
SQL> create table parts (pid number);
Table created.
SQL> create table catalog (sid number,pid number);
Table created.
SQL> insert into parts select rownum from all_objects where rownum<=5;
5 rows created.
SQL> insert into catalog values (10,1);
1 row created.
SQL> insert into catalog select 1,pid from parts;
5 rows created.
SQL> select * from catalog;
SID PID
---------- ----------
10 1
1 1
1 2
1 3
1 4
1 5
So, the supplier which has all the parts is 1. How do we find that?
The division in relational algebra is done by some steps which are explained in the link. Let's follow those:
The simulation of the division with the basic operations is as follows. We assume that a1,...,an are the attribute names unique to R and b1,...,bm are the attribute names of S. In the first step we project R on its unique attribute names and construct all combinations with tuples in S:
- T := πa1,...,an(R) × S
In our case we have the table CATALOG as R, the table PARTS as S. So if we write a sql to find out the above relation we need a cartesian join.
select sid,pid
from (select sid from catalog) ,parts
;
This give us all suppliers combined with all parts.
SID PID
---------- ----------
10 1
1 1
1 1
1 1
1 1
1 1
10 2
1 2
1 2
1 2
1 2
SID PID
---------- ----------
1 2
10 3
1 3
1 3
1 3
1 3
1 3
10 4
1 4
1 4
1 4
SID PID
---------- ----------
1 4
1 4
10 5
1 5
1 5
1 5
1 5
1 5
We now have all the possibilities for the supplier-part relation.
The second step is:
In the next step we subtract R from this relation:
U := T - R
To subtract the table CATALOG we need the MINUS operator.
select sid,pid
from (select sid from catalog) ,parts
minus
select sid,pid from catalog;
SID PID
---------- ----------
10 2
10 3
10 4
10 5
After we had all the possibilities we subtracted the ones which are already in the CATALOG table and we got the ones which are not present in the table.
On to the next step:
Note that in U we have the possible combinations that "could have" been in R, but weren't. So if we now take the projection on the attribute names unique to R then we have the restrictions of the tuples in R for which not all combinations with tuples in S were present in R:
V := πa1,...,an(U)
This step just gets the supplier id's from the previous query.
select sid from (
select sid,pid
from (select sid from catalog) ,parts
minus
select sid,pid from catalog
);
SID
----------
10
10
10
10
Now we have the supplier id which does not supply all of the parts. The next step is obvious, to find the other suppliers (the remaining ones supply all the parts).
So what remains to be done is take the projection of R on its unique attribute names and subtract those in V:
W := πa1,...,an(R) - V
To do this we need to subtract the previous query from the table CATALOG.
select sid from catalog
minus
select sid from (
select sid,pid
from (select sid from catalog) ,parts
minus
select sid,pid from catalog
);
SID
----------
1
In some pages (like this one) the same operation is done using a different sql (obtained by using "not exists" instead of "minus").
select distinct sid from catalog c1
where not exists (
select null from parts p
where not exists (select null from catalog where pid=p.pid and c1.sid=sid));
This one is harder for me to understand in the first look, I am more comfortable following the steps above.
It is great to follow the steps of relational algebra to solve a problem in sql. It helps very much in understanding the solution. Relational algebra rocks!
Tuesday, February 05, 2008
Local vs. remote connection performance
It has been asked several times in several places; is there a performance difference between running a query locally in a client on the server and running the same query in a remote client?
The obvious answer given by the respondents including myself is: "if you do not return thousands of rows through the network, there must not be any difference". This type of response is opposed to what I believe; even if the answer seems obvious test it before you make any suggestions.
Tanel Poder got the same question and did what is needed to be done, he tested it and showed that there was a difference. In this great post of his.
His tests use a database on Solaris, sqlplus clients on Windows and Linux. I have tested the same using a database on Linux and the same behavior is observed there too.
Lesson learned again and again: test your suggestion even if the answer seems obvious.
Monday, February 04, 2008
Database version control
Coding horror is one of the software development blogs I keep a close eye on.
Jeff Atwood posted a nice piece about database version control recently. Database version control is maybe one of the most important and unfortunately most overlooked things in software development. The post is a good read including the links he provides.
Friday, January 11, 2008
The blog tagging thing
During the last few days lots of Oracle bloggers have been busy tagging each other and posting eight unknown things about themselves. I was also tagged by some friends and was asked to post eight things about myself. I have never forwarded any chain e-mails or messages to anyone and in parallel to that I have not written anything about myself after this either.
What I think about this blog tagging thing is very similar to what Howard Rogers thought about it. He shut his site down for some time and you can read what he thinks when you go to his blog. My thoughts on this are here in the comments to an Eddie Awad post. Howard has also posted a comment there to explain further.
Sunday, December 09, 2007
Hiring DBAs based on certification
There was a question in the OTN database forum recently which said:
" Can any one advise me? I have joined a company as new senior dba.
i am not understanding what shall be done at beginning?
Can anybody advice me how to check all the database and what to do in the beginning ?
I have been reading all the stuff,documents fr. a week?"
The same poster also said:
" I have all th knowledge of oracle,I am ocp certified.
But fearing at the beginning.
how to proceede as this is my first job as a dba."
This is what can happen when a company hires someone for a position like "senior DBA" just based on some papers called certifications.
How he claims he has "all the knowledge of oracle" just by getting an OCP certification is another story.
Thursday, December 06, 2007
Consolidating oraInventories
I have always had trouble with dealing with multiple oraInventories and multiple oraInst.loc files in a server.
If there are lots of people installing products in a server or if you are handed an environment which is in such a mess it is a big trouble to get things tidied up (if the installer people used a central inventory instead of each one using their own inventories, you a re lucky).
You need to know which oraInst.loc points to which oraInventory and which products and versions are in that inventory. You need to put the right oraInst.loc in place in order to install a patchset or a patch. Or you need a way to make the central inventory know about all Oracle homes which exist but which are not in the inventory.
In such situations Oracle Universal Installer 10.2 comes to the rescue. There is a Metalink note I have just realized which is 438133.1. The note explains how to consolidate multiple Oracle homes in one central inventory. There is a new option of the installer in 10.2 which is -attachHome. Using a 10.2 installer and this option you can get all your homes included in the central inventory.
./runInstaller -silent -attachHome ORACLE_HOME=<home you want to add> ORACLE_HOME_NAME=<unique home name>
The steps are clearly explained in the note which basically are; using a 10.2 universal installer, setting oraInst.loc to point to the inventory which you want to use to consolidate everything in, setting $ORACLE_HOME to the home you want to include and running the installer with the -attachHome option.
This works for 9i and 10G.
Optimizer blog
As I have learnt from Greg Rahn's blog Structured Data, the people behind the optimizer started blogging. In their first technical post they talk about bind peeking in 11G. I think we will see lots of examples and explanations about the optimizer in there. Their blog is here.
Wednesday, December 05, 2007
Hard Disk Reference
There was a reference to a site about the hard disk a few days ago in the oracle-l list from Ranko Mosic. I have gone through half of it for now, I can say the referenced pages are a great guide for understanding the basics of the hard disk, its parts, inner workings, etc... Trying to tune I/O without knowing about the hard disk basics can lead a DBA to the wrong path easily, this site is a must read in my opinion.
