Archive for July, 2006

A must-have Oracle book

July 26, 2006

Cost-Based Oracle Fundamentals
Simply the best.
It’s a good time for Oracle community that both Tom Kyte and Jonathan Lewis have come up with excellent books.
I can’t wait for the other two books in the series from Jonathan Lewis.

ORA-08103

July 25, 2006

This could be 2GB filesize limit problem:
Here’s a discussion: http://www.tek-tips.com/viewthread.cfm?qid=1218851&page=4

The insidious and obscure thing about the 2GB+ problem is that you don’t even know you have a problem unless you just happen to access data that is in a block that is at or beyond the block that just happens to be on the 2GB boundary. The access problem occurs only to blocks that are at or beyond the block on the 2GB boundary in the same object (e.g., same table/index/et cetera). So, frankly, you could go for years and not know that you have a problem if the object is obscure or if accesses just happen to occur to blocks prior to the “problem block”.
Therefore, you can isolate if this is an issue for you by determining if the table with which you are having trouble happens to lie on the 2GB “threshold” of one of your 2GB+ data files. Here is a script that I wrote for you that will show the owner, name, and object_type of any object that is on a 2GB boundary in your database:
col a heading “Owner.Object (Type/File)|on 2GB Boundaries” format a50
select owner ||’.’||segment_name||’ (‘||lower(segment_type)
||’ in file #’||file_id||’)’ a
,block_id, blocks
from dba_extents
where power(2,31)/8192 between block_id and block_id+blocks
order by file_id,owner,segment_name
/
For any tables that lie on the 2GB boundary(ies), run a “SELECT” (without a “WHERE” clause) of a single numeric column from the table (which forces a full table scan). If the SELECT fails, then you know you have a problem.

Metalink DocID 136697.1 has a script for “Pro-actively detecting common data dictionary problems”.

Oracle 9i CPU costing

July 25, 2006

Oracle 9i introduced CPU costing and it is turned off by default. However, there are bugs in some 9.2 revisions inadvertantly turned on CPU costing when you set a non-default optimizer_index_cost_adj. CPU costing can be annoying when you dig into details of CBO and compare two different machines. To turn it off in 9.2 is:
alter session set “_optimizer_cost_model”=io;
alter session set “_optimizer_system_stats_usgae”=0;

There is a no_cpu_costing hint, but it doesn’t seem to work, at least for me.
Usual warnings with undocumented init parameters, don’t use it on a production system without blessing by Oracle’s support.

Under the hood – MV complete refresh

July 22, 2006

If MV is refresh on demand, Oracle does truncate/insert(append).
If MV is refresh on commit, Oralce does delete/insert.
That’s why MV is usually not for OLTP environment.
In OLTP environment and your MV is not fast refreshable, if you choose refresh on demand, you have a race-condition where the MV will be empty for a while(may be just some milliseconds, but still a while in an OLTP envrionment). If you choose refresh on commit, your transaction will take longer to complete and also more undo/redo for delete and conventional insert.
In DSS or DW, typically you refresh the MV on demand in the batch job at night when no one will notice or care.

Now the question is what to do with real-time DW?

Fast refreshable MV with union-all

July 20, 2006

If you need to have a fast-refreshable join MV with union-all structure(don’t use union, add a second aggregate MV with group by on top if you need union), you need to do the following:

  • ROWIDs in the SELECT list, as for any join MVs.
  • Materialized view logs on detail tables, as for any join MVs.
  • A marker column for union all
  • Make sure each branch of union all is fast-refreshable, check with dbms_mview.explain_mview first.

If you miss the count(*) in the second MV, it will only be fast-refreshable after insert, not for all DML.

Don’t forget parallel_enable in PL/SQL

July 7, 2006

While looking into why parallelism did not kick in for certain queries, I have found that those queries call PL/SQL functions. Yeah, yeah, there’s context switch. But even worse, the PL/SQL function did not declare parallel_enable in the spec. The lesson is every object referenced in the query need to enable parallelism(including those implicitly, like indexes) for parallel execution.

Distributed query against remote partitioned table

July 7, 2006

It’s been a while, so I should fulfill my promise to discuss distributed query against remote partitioned table.
If a distributed query involves database objects(tables, indexes, PL/SQL procs, etc) from different databases, then sometimes, Oralce will do row-by-row processing.
Let’s look at a very simple example:

CREATE TABLE “DIMENSION1”

( “PK1” NUMBER(11,0) NOT NULL ENABLE,

“DIMVAL1” DATE NOT NULL ENABLE,

CONSTRAINT “DIMENSION1_PK” PRIMARY KEY (“PK1”) ENABLE

) ;

CREATE TABLE “DIMENSION2”

( “PK2” NUMBER(11,0) NOT NULL ENABLE,

“DIMVAL2” NUMBER,

CONSTRAINT “DIMENSION2_PK” PRIMARY KEY (“PK2”) ENABLE

) ;

CREATE TABLE “FACT”

( “PK1” NUMBER(11,0) NOT NULL ENABLE,

“PK2” NUMBER(11,0) NOT NULL ENABLE,

“VAL” NUMBER

)

PARTITION BY RANGE (“PK1”)

……

CREATE UNIQUE INDEX “FACT_PK” ON “NHUSER”.”FACT” (“PK1”, “PK2”) LOCAL COMPRESS 1;

CREATE BITMAP INDEX “FACT_BI1” ON “NHUSER”.”FACT” (“PK1”) LOCAL;

CREATE BITMAP INDEX “FACT_BI2” ON “NHUSER”.”FACT” (“PK2”) LOCAL;

Now create some database links, could be loopback ones, say remote1 and remote2.

 

CREATE OR REPLACE FORCE VIEW “FACT_VIEW” (“PK1”, “PK2”, “VAL”) AS

select “PK1″,”PK2″,”VAL” from fact@remote1

union all

select “PK1″,”PK2″,”VAL” from fact@remote2;

select /*+ USE_MERGE */sum(t3.val)

from dimension1 t1, dimension2 t2, fact_view t3

where t1.PK1=t3.PK1 and t2.PK2=t3.PK2

and t1.DIMVAL1 between timestamp ‘2006-06-01 00:00:00.000’

and timestamp ‘2006-06-02 00:00:00.000’

and t2.DIMVAL2 in (120001);

 

The plan is

——————————————————————————————-

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Inst |IN-OUT|

——————————————————————————————-

| 0 | SELECT STATEMENT | | 1 | 62 | 1569 (3)| | |

| 1 | SORT AGGREGATE | | 1 | 62 | | | |

| 2 | NESTED LOOPS | | 2843 | 172K| 1569 (3)| | |

| 3 | MERGE JOIN CARTESIAN| | 1432 | 32936 | 129 (27)| | |

|* 4 | TABLE ACCESS FULL | DIMENSION1 | 5 | 65 | 6 (17)| | |

| 5 | BUFFER SORT | | 283 | 2830 | 123 (27)| | |

|* 6 | TABLE ACCESS FULL | DIMENSION2 | 283 | 2830 | 25 (28)| | |

| 7 | VIEW | FACT_VIEW | 2 | 78 | | | |

| 8 | UNION-ALL PARTITION| | | | | | |

| 9 | REMOTE | | 1 | 14 | | REMOTE1 | R->S |

| 10 | REMOTE | | 1 | 14 | | REMOTE2 | R->S |

——————————————————————————————-

In the remote database, the query executed is:

SELECT /*+ INDEX(“FACT”) */ “PK1″,”PK2″,”VAL”

FROM

“FACT” “FACT” WHERE “PK2″=:1 AND “PK1″=:2

 

 

call count cpu elapsed disk query current rows

——- —— ——– ———- ———- ———- ———- ———-

Parse 1 0.00 0.00 0 0 0 0

Execute 23040 13.91 13.07 0 0 0 0

Fetch 46080 4.30 4.02 0 92160 0 23040

——- —— ——– ———- ———- ———- ———- ———-

total 69121 18.21 17.09 0 92160 0 23040

 

Misses in library cache during parse: 0

Optimizer goal: CHOOSE

Parsing user id: 23

 

Rows Row Source Operation

——- —————————————————

23040 PARTITION RANGE SINGLE PARTITION: KEY KEY

23040 TABLE ACCESS BY LOCAL INDEX ROWID OBJ#(54225) PARTITION: KEY KEY

23040 INDEX UNIQUE SCAN OBJ#(54394) PARTITION: KEY KEY (object id 54394)

Notice that this query is executed 23040 times, exactly the same number as the rows in the intermediate resultset of the Cartesian join. Oracle is doing this row-by-row processing, as Tom Kyte describes, slow-by-slow.
I guess this is the only correct and generic way of handling union-all views.