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...

I was just asked to prepare a short, one-hour workshop/presentation about optimization on Oracle. As this topis is so huge, and everyone had already read something, I decided to concept this workshop as an overview of the concepts (starting with db design) and the tools available. The .pdf version is thus a kind of checklist - have you read about all of these issues? Have you thought them out when designing your system? I hope you will find at least one new thing there:-) The PDF is available for download on my website download area .

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,...

Quite often, a procedure needs to know some information about the session it is running in - at least for logging some information. It seems to be easy to find this information - just look at SYS_CONTEXT documentation, and find the impressive list of attributes of USERENV context. And if something is missing, just grab the SESSIONID, SID or AUDSID and query v$session or other view. Well, this works nicely, as long as you don't do it in a job. Unfortunately, in a job you always get 0. You can try to battle with FG_JOB_ID and BG_JOB_ID, but I think this deserves a more elegant solution - and that's why I always query v$mystat to get the SID of my process. Just fetch the first line: select * from v$session where sid = (select sid from v$mystat where rownum=1) You need a select grant on v_$mystat by SYS, but as this view does not expose anything important, it's not a security issue. The next question that comes is: if I am run by a job (let's talk about dbms_job now), what...

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...