Vit Spinka technical blog

In these days, disk capacity is often not a big issue today. If you have at least a decent load on the database, you will hit the IOPS limit much sooner than you run out of disk space. Well, almost. First, you will still have a lot of inactive data that consumes the space but does not require any IOPS. And second, in some applications (like ETL in DWH) you are bound by throughput. Let me talk about this case. Don't expect any exceptional thoughts, this post just inspired by a real production case and tries to pinpoint that there is always one more way to consider. The optimal plan for many ETL queries is a lot of fullscans with hash joins. And often, you read one table more times, to join it in different ways. Such queries benefit if you make your tables smaller - you save on I/O. (1) In ETL, your source tables are often imported from a different system, and you actually don't need all columns from the tables. So, first of all - don't load the data you don't need. Howev...

This quest started with the usual question: why is this query so slow? To put it in the picture, it was a query loading one DWH table by reading one source table from a legacy system (already loaded to Oracle, so no heterogenous services were involved at this step), joining it several times to several tables. (It's the usual badly-designed legacy system: if flag1 is I, join table T1 by C1, if flag1 is N, join table T1 by C2... 20 times.) If I simplify the query, we are talking about something like: SELECT T1.m, case when T1.h = 'I' then T2_I.n when T1.h = 'G' then T2_G.n else null end FROM T1 LEFT OUTER JOIN T2 T2_I ON (T1.h = 'I' and T1.y = T2_I.c1) LEFT OUTER JOIN T2 T2_G ON (T1.h = 'G' and T1.z = T2_G.c2) We even know, that the query always return number of rows identical to number of rows in T2. However, ommiting the T1.h = 'I'/'G' conditions in join clause would duplicate the rows, so the conditions are necessary there. O...

An interesting questions is: what is in fact the difference between unique and non-unique indexes? For a long discussion, see Richard Foote's blog . Here, we look at the on-disk differences. Let's start with environment setup and block dump creation: connect system create user itest identified by itest; grant dba to itest; create tablespace ITEST; alter user itest default tablespace itest; connect itest/itest create table TDATA (pk varchar2(20)); begin for i in 1..10000 loop insert into TDATA values ('VAL'||i); end loop; commit; end; create index TIDX1 on TDATA(pk); (I chose varchar2 type so that the actual characters are clearly seen in the dump. Also, I created a fresh new tablespace so the block numbers are small and probably consecutive.) Now, find the extents involved: select * from dba_extents where owner=user; And dump the blocks (take numbers from the query above - relative_fno, block_id): alter system dump datafile 14 block min 33 block max 62; Save the trace,...

Last week, I found a not-so-nice bug in 10gR2. Just try this short test case, creating a table with two list partitions, default and null: alter session set nls_territory=America; alter session set nls_language=American; select version from product_component_version; drop table th3; prompt Creating TH3... create table TH3 ( DF DATE, DT DATE, N NUMBER ) partition by list (DT) ( partition THTBL_CURRENT values (NULL) tablespace USERS, partition THTBL_OLD values (default) tablespace USERS ) ; prompt Loading TH3... insert into TH3 (DF, DT, N) values (to_date('01-01-2000', 'dd-mm-yyyy'), to_date('10-04-2008', 'dd-mm-yyyy'), 1); insert into TH3 (DF, DT, N) values (to_date('01-01-1000', 'dd-mm-yyyy'), to_date('01-03-3000', 'dd-mm-yyyy'), 2); insert into TH3 (DF, DT, N) values (to_date('10-04-2008', 'dd-mm-yyyy'), to_date('10-04-2008', 'dd-mm-yyyy'), 10); insert into TH3 (DF, DT, N) value...

Just a few issues you should be aware of when trying to install 10gR2 RAC on RHEL 5.1: See Metalink 465001.1 for raw device configuration for RHEL 5. It's not explicitly said, but all disks used must be partitioned! Otherwise, if you don't partition the OCR disk, the root.sh from clusterware installation will fail (fail with “Failed to upgrade Oracle Cluster Registry configuration” error, with “Failed to call clsssinit” in log.) The ASMLib will also refuse to stamp whole disk, a partition is required. For OS configuration, see Metalink 421308.1. However, the sysctl parameters listed there do not exist on RHEL 5.1, you will have to use: kernel.shmmni = 4096 kernel.sem = 250 32000 100 128 fs.file-max = 65536 net.ipv4.ip_local_port_range = 1024 65000 net.core.rmem_default = 262144 net.core.rmem_max = 262144 net.core.wmem_default = 262144 net.core.wmem_max = 262144 net.ipv4.tcp_rmem = 4194304 4194304 4194304 net.ipv4.tcp_wmem = 262144 262144 262144 You will als...

The new Oracle 11g New Features exam is available in beta: http://www.oracle.com/global/cz/education/certification/11g_nfbeta.html , open till December, 15th. The first opportunity to earn the 11g OCP credential... By the way, the full 11g OCP track is announced too, and it looks similiar to the 10g track - 1 exam for OCA, 1 exam and hans-on course for OCP. OCM is promised too, but no date is given.

I've prepared a presentation about 11g new features - it's ment for an internal workshop at my company, but perhaps you will find it interesting, too. So, the direct link is here: 11g New Features presentation .