DBMS_STATS Enhancements in Oracle 11g Database

DBMS_STATS Enhancements in Oracle 11g Database
Virag Sharma virag123@gmail.com

We know in 10g and 11g there is automatic job that collect stats of database based on certain preferences settings , lets have a look what are these preferences and what are there default values and how DBMS_STATS is different in 11g from 10g

Changing Preferences for Statistics

Preference name

Default Values













PUBLISH ( New in 11g)


INCREMENTAL new in 11g


STALE_PERCENT (New in 11g)


# In Oracle 10g


Hide Show Regions on an Apex Page

One of the really nice features of APEX is the ability to hide / show regions when editing item or page attributes. This is really useful if you have to update the same attribute for multiple items.

If you want to implement this type of approach on a page you (more...)

Fine tuning

Mike is doing some work on an application that started life as SQL Server. Now that it has been converted to Oracle, there are one or two bits that could still do with a little fine tuning.

One particular procedure seemed to take rather a lot of time, and several developers had tried to get better performance out of it without much success. Here it is:

    ( p_customer_id  IN VARCHAR2 DEFAULT NULL
    , pc_results_out IN OUT SYS_REFCURSOR )
    v_fetch_status     INTEGER := 0;
    v_sql_status       INTEGER;
    v_fetch_status1    INTEGER := 0;
    v_sql_status1      INTEGER;
    v_event_id         VARCHAR2(50);
    v_runid            NUMBER(10,  (more...)

OracleAS Java Object Cache

by Fábio Souza Before I start, I would like to say that many texts and observations were took from the Oracle® Containers for J2EE Services Guide 10g (, Chapter 7. What is it? The Java...

This is a summary only. Please, visit the blog for full (more...)

EclipseLink Webinar: May 1st

The EclipseLink Webinar originally scheduled for April 24th (today) has been rescheduled to May 1st due to some technical issues. Please join us to discuss what EclipseLink is and gain a better understanding of many of the advanced features.

If you have any questions you would like to see addressed please post them in a comment here and I'll either address them in this session or provide the answers here.


11g RAC Database hanged or appears to hanged

11g RAC Database hanged or appears to hanged

Around lunch time 1:00 PM database hanged. When we tried to login as sysdba
our sqlplus session also hanged. we not able to login on either of node of RAC.
Thought to kill all the remote connection , so we will able to login

ps -aef |grep LOCAL=NO |awk '{ print $2}' |xargs kill -9

When your database hanged and you not able to login, we have used following procedure
to create hang analysis dump

sqlplus /nolog

SQL> set _prelim on
SQL> connect / as sysdba
SQL> oradebug setmypid
Statement processed.
SQL> (more...)

Bashing RDBMS

| Apr 23, 2008
Please review the following interesting article by Jonathan Holland.Why Relational Databases end up being the bottleneckFor the appropriate context of this debate, you can review this forum discussion on Joel's web site as just one example.Stored Proc to avoid frequent buildsAs is evident from the title and content of my blog, I'm a database professional. Nevertheless, I try to stay as objective

How to setup Ruby and new Oracle Instant Client on Leopard

UPDATE: New version of instructions for Snow Leopard is available here.


We waited for it long, long time and finally it has arrived – Oracle Instant Client for Intel Mac. I was lucky to test beta version of the client already for the last couple of weeks and so far everything was working OK. Therefore as the final version is now available to everybody I am here rewriting my previous instructions on how to get Ruby working with Oracle on Mac.

Installing Oracle Instant Client for Intel Mac

At first you need to download Oracle Instant Client for (more...)

Diagnostics beyond OracleAS Control Console

by Eduardo Rodrigues One of the many companies recently acquired by Oracle is a small one called "Auptyma", whose founder and former CEO, Mr. Virag Saksena, was previously Director of the CRM...

This is a summary only. Please, visit the blog for full content and more.

Tab Order of Elements on an APEX Page

To set the TAB order of your form, you simply need to add the attribute: TABINDEX="1" to the "HTML Form Element Attributes" of your items replacing the number with the desired sequence.

This is a great way of enhancing the usability of your forms.

Business case – Customer on Credit Hold

| Apr 20, 2008

Business Scenario:
There is a requirement to put all open orders in Order Management on Hold by putting a Credit Hold on customer under the following two conditions:
1. Customer exceeds the Credit limit set
2. Cutomer has not paid an invoice even after 'x' days of the invoice due date on invoice. However, this customer may not have exceeded the credit limit.

The solution to the first scenario is to enable the Credit Check for the customer and put an amount in the Credit Limit field for the customer. Oracle Order Management would use this information to place (more...)

A comprehensive XML processing benchmark

by Eduardo Rodrigues h4 { margin-bottom: 0; font-style: italic; font-size: 110%; text-decoration: none; }Introduction I think I've already mentioned it here but, anyway, I'm currently leading a very...

This is a summary only. Please, visit the blog for full content and more.

Manual Switchover.

Data Guard maintains the standby databases as transactionally consistent copies of the production database. Then, if the production database becomes unavailable because of a planned or an unplanned outage, Data Guard can switch any standby database to the production role, minimizing the downtime associated with the outage.

Due to low network bandwidth or when using Standard Edition, we maintain manual dataguard, in which, we transfer the archive log files manually to the DR site and then apply them there. In such cases, it is very difficult to use oracle internal switch over mechanism. This document is prepared for the customers (more...)

Fixed blog’s appearance on IE6

Great news folks! We've just fixed our blog's template so it'll be displayed 100% correctly on Internet Explorer 6 browser. Now, we expect this blog to render identically on both Internet Explorer...

This is a summary only. Please, visit the blog for full content and more.

JavaScript API Documentation

I have just been reading the Apex 3.1 API documentation and noticed that the JavaScript API that are commonly used in Apex are now documented:


There is some very useful stuff in there!!

Rotate your Logs … Two birds with one stone

I have been struggling with getting a custom Grid Control (10g Release 3) report to a) Run on a schedule successfully and b) email it to me for several weeks. I spent my customary time troubleshooting the problem myself, scouring metalink etc and finally resorted to opening an SR with Oracle Support. Two weeks later, and many "run opatch lsinventory -detail" or telling me to check my proxy settings (which I did on my own several times) tasks later, still no resolution. I decided to shelve the problem and move on to more pressing matters...

Enter the hand-off of the (more...)

Avoid Deprayments

| Apr 18, 2008
There are deployments and there are deprayments. What's the difference?A deployment is when you deploy a change and verify that it was successfully deployed and functioning properly.A deprayment is when you "deploy and pray." It's kind of like Dr. Evil in the Austin Powers movie: you don't actually witness that the deployment succeeded, you just walk away and assume everything went according to

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 (more...)

Is there a cook in JDev’s team?

This week I found something at least very curious when I launched my JDeveloper as I do almost every morning. This was the "Tip of the Day" it showed me: Well, I don't know what this...

This is a summary only. Please, visit the blog for (more...)

Customer Refund in Receivables

| Apr 11, 2008
There are cases when customers overpay, or pay one invoice twice. In all such cases the customer is due for a refund. The easiest approach would be to ask the customer to absorb the overpayment if the amount is small. However, if the amount is large the customer would no doubt ask for a refund. There are basically three ways to handle customer refunds:
1. Issue a manual check for refund to the customer and record a Debit Memo in Receivables and match this to the receipt.
2. Ask the customer to leave the cash as "on-account" and apply this (more...)