Search

Top 60 Oracle Blogs

Recent comments

Home » Monthly archive

January 2009

Stability is your friend

Submitted by akolk on Fri, 2009-01-30 19:40

Oracle and other Microsoft are putting more and more automatic and self – everything features into their database. There are of course many reasons why that makes sense (for Oracle and Microsoft), but does it make sense for all Oracle Systems and their DBAs? I don’t think so. Consider this:

All these automatic and self-tuning features will manage resources and make decisions that can and will change the behavior of your system. Now consider that you are the DBA of a mission critical Oracle system. Do you want  a system that runs good enough and stable or do you want a system that sometimes runs perfect and sometimes runs badly? Let me know.

Xplan utility

Submitted by Adrian Billington on Wed, 2009-01-28 18:26

A utility to add parent ID and execution order information to plans reported by DBMS_XPLAN. XPlan includes DISPLAY, DISPLAY_CURSOR and DISPLAY_AWR functionality for use in exactly the same way as the DBMS_XPLAN equivalents. Supports versions from 10g onwards. ***Update*** Now available in two formats: 1) as a PL/SQL package and 2) as a collection of three free-standing SQL*Plus scripts (i.e. no installation/database objects needed). January 2009 (updated October 2011)

30 days in the hole ...

Submitted by robyn on Tue, 2009-01-27 03:38

So ... not really 30 days (21 to be precise) but it feels like forever.Late November, early December (while I was in the U.K.) several sites were installed with an updated version of one of the tools. Everything seemed to be going well, until all the databases ran out of space roughly around the same time. (We lost a team member a few months ago and he used to monitor sites until they achieved

Ultra-Fast MV Alteration using Prebuilt Table Option

Submitted by arupnanda on Sat, 2009-01-24 18:53

Here is an interesting question posed to me one time and I had found a solution. After 9 years, I encountered the same question and was shocked to find that many people still don't know about a little trick that could avoid a potential problem later.

Someone asked me how to modify a column of a Materialized View, e.g. from varchar2(20) to varchar2(25), or something similar. Drop and recreate? Not an option. We are talking about a several hundred GB MV with a very complex query that will take days to complete.

Problem

When you alter a materialized view to add a column or modify a column definition, unfortunately there is no command functionally equivalent to ALTER MATERIALIZED VIEW … ADD COLUMN. The only way to alter an MV is to completely drop and recreate it with the alteration. That approach may be acceptable for small MVs; but for larger MVs the cost of rebuilding can make the process quite infeasible. In addition to the time it will take to rebuild the entire MV (which could be days, depending on the size), the redo/undo generation and the surge in logical I/O due to the MV query may seriously affect the performance of the source database. In some cases, large MVs may even fail to be rebuilt as sometimes the undo segments may not have the undo information for long running queries – causing ORA-1555 errors.

So is there a better approach? Yes, there is. In this document I am going to explain a better approach for creating an MV that makes the alterations possible without rebuilding the MV – a task accomplished in mere seconds as opposed to potentially days.

Concept of Segments

Segments are stored units in Oracle. So, a table has a segment; not a view – since the contents of the view are not stored; only the view definition is. A Materialized View, however, stores the contents; so it is a segment.

Multipart cursor subexecution and PRECOMPUTE_SUBQUERY hint

Submitted by tanelpoder on Fri, 2009-01-23 21:38

There was a question about PRECOMPUTE_SUBQUERY hint in an Oracle Forums thread.

Here I will post the answer I gave there and also elaborate it more as it explains a little known interesting fact about Oracle cursor management. Also it allows me to introduce few advanced Oracle troubleshooting scripts by example. This is a fairly long post, but if you are interested in some Oracle cursor management and SQL execution internals, keep on reading ;)

Consider the following test case with two tables, T1 and T2:

SQL> create table t1 as select rownum a from dual connect by level < 10;

Table created.


SQL> create table t2 as select rownum+10 b from dual connect by level < 10;


Table created.


SQL>
SQL> select * from t1;

Multipart cursor subexecution and PRECOMPUTE_SUBQUERY hint

Submitted by tanelpoder on Fri, 2009-01-23 21:38

There was a question about PRECOMPUTE_SUBQUERY hint in an Oracle Forums thread.

Here I will post the answer I gave there and also elaborate it more as it explains a little known interesting fact about Oracle cursor management. Also it allows me to introduce few advanced Oracle troubleshooting scripts by example. This is a fairly long post, but if you are interested in some Oracle cursor management and SQL execution internals, keep on reading ;)

Consider the following test case with two tables, T1 and T2:

SQL> create table t1 as select rownum a from dual connect by level < 10;

Table created.


SQL> create table t2 as select rownum+10 b from dual connect by level < 10;


Table created.


SQL>
SQL> select * from t1;

Multipart cursor subexecution and PRECOMPUTE_SUBQUERY hint

Submitted by tanelpoder on Fri, 2009-01-23 21:38

There was a question about PRECOMPUTE_SUBQUERY hint in an Oracle Forums thread.

Here I will post the answer I gave there and also elaborate it more as it explains a little known interesting fact about Oracle cursor management. Also it allows me to introduce few advanced Oracle troubleshooting scripts by example. This is a fairly long post, but if you are interested in some Oracle cursor management and SQL execution internals, keep on reading ;)

Consider the following test case with two tables, T1 and T2:

SQL> create table t1 as select rownum a from dual connect by level < 10;

Table created.


SQL> create table t2 as select rownum+10 b from dual connect by level < 10;


Table created.


SQL>
SQL> select * from t1;

Multipart cursor subexecution and PRECOMPUTE_SUBQUERY hint

Submitted by tanelpoder on Fri, 2009-01-23 21:38

There was a question about PRECOMPUTE_SUBQUERY hint in an Oracle Forums thread.

Here I will post the answer I gave there and also elaborate it more as it explains a little known interesting fact about Oracle cursor management. Also it allows me to introduce few advanced Oracle troubleshooting scripts by example. This is a fairly long post, but if you are interested in some Oracle cursor management and SQL execution internals, keep on reading ;)

Consider the following test case with two tables, T1 and T2:

SQL> create table t1 as select rownum a from dual connect by level < 10;

Table created.


SQL> create table t2 as select rownum+10 b from dual connect by level < 10;


Table created.


SQL>
SQL> select * from t1;

Identifying shared memory segment users using lsof

Submitted by tanelpoder on Thu, 2009-01-22 12:42

Lsof (list open files) is a really useful tool for troubleshooting open file decriptors which prevent a deleted file from being released or a shared memory segment from being removed.
Here’s a little situation on Linux where an Oracle shared memory segment was not released as someone was still using it.
$ ipcs -ma ------ Shared Memory Segments -------- key shmid owner perms bytes nattch status 0x00000000 393216 oracle 640 289406976 1 dest 0xbfb94e30 425985 oracle 640 289406976 18 0x3cf13430 557058 oracle 660 423624704 22 ------ Semaphore Arrays -------- key semid owner perms nsems 0xe2260ff0 1409024 oracle 640 154 0x9df96b74 1671169 oracle 660 154 ------ Message Queues -------- key msqid owner perms used-bytes messages The bold line should have disappeared after instance shutdown, but it didn’t.

Identifying shared memory segment users using lsof

Submitted by tanelpoder on Thu, 2009-01-22 12:42

Lsof (list open files) is a really useful tool for troubleshooting open file decriptors which prevent a deleted file from being released or a shared memory segment from being removed.
Here’s a little situation on Linux where an Oracle shared memory segment was not released as someone was still using it.
$ ipcs -ma ------ Shared Memory Segments -------- key shmid owner perms bytes nattch status 0x00000000 393216 oracle 640 289406976 1 dest 0xbfb94e30 425985 oracle 640 289406976 18 0x3cf13430 557058 oracle 660 423624704 22 ------ Semaphore Arrays -------- key semid owner perms nsems 0xe2260ff0 1409024 oracle 640 154 0x9df96b74 1671169 oracle 660 154 ------ Message Queues -------- key msqid owner perms used-bytes messages The bold line should have disappeared after instance shutdown, but it didn’t.