Tuesday, September 29, 2009

Job Saving Prompt

If you are using Oracle on Linux, then it would be highly beneficial to use the following prompt:

export ORACLE_HOSTNAME=`hostname`
PS1=’${ORACLE_HOSTNAME}:${ORACLE_SID}$’

With the above prompt you are less likely to perform operations intended for another database in another database.

Saturday, September 26, 2009

Oracle Performance Tuning Approach

This post is a general outline as how I go about my tuning endevours in a typical Oracle database environment, and is more aimed at getting feedback for improvement than anything else.

I divide my performance tuning of Oracle in two modes: Proactive and Reactive.

In proactive mode, I make sure that the hardware is strong enough with plenty of memory, CPU, network and Disk.

As I prepare my Oracle database environment (11g), I utilize ASM and ACFS with flash recovery area with block change tracking enable for RMAN.

I also use automatic feature of the database like Automatic Memory Management, Automatic Tablespace management and ASM and so on.

I run nightly batch jobs to keep the statistics up to date and configure the optimizer optimally according to requirements..

I daily analyze the findings of the ADDM and look into the alert log file.

I also analyze the prospects of indexing at the table level, partitioning the tables, materialized view usage.


In reactive mode, as some user come running complaining that "database is slow", I try to follow the following steps:

I identify the part of application running slow. At the start of that part, I start tracing via DBMS_MONITOR, and then at the end of that part I finish the tracing. I run trace file through the profiler and identify the culprit SQL.

Once SQL is identified, I get its execution plan and other information and calculate its selectivity and on the basis of its selectivity I check to see that whether it's using the appropriate access path or not (Index or FTS). Is parsing is problem or its doing lots of logical reads?

If selectivity is high and according to plan, query is also using an index, then I check the clustering factor of the index.

If selectivity is low and FTS is being done, I check the partitioning options.

I keep the measurements of the start and end of the exercise and yes I don't tune too much, I just keep the users happy.

I welcome corrections, suggestions, improvements and feedback in the comments.

Thursday, September 24, 2009

What is Bind Variable Peeking?

Bind Variable peeking is when optimizer looks into the literal behind the behind variable. When a query with bind variable is submitted to Oracle, and the parsed representation of that query is already present in the library cache, then optimzer simply uses the already present plan, regardless of the value behind the variable.

Now with literals optimizer could make much more informed decision about the best way to access the data (i.e the best execution plan). For example, if historgram is present on the column of the table, then the optimizer already knows as how frequently or rarily a value is present in that column, and with literal in query, optimizer could use the best access path, but in case of bind variable it cannot do that because it doesn't know the value. So in case of bind variable it uses bind variable peeking to know the value. But when it uses the bind variable peeking and gets an access path, it always uses the same plan for the subsequent similar queries with bind variables up to 10g, though it has grown more intelligent in the 11g.

But as a general tip, you might want to use bind variables in the queries of OLTP and literals in the queries of data warehouse. Because in OLTP, more often than not, the parsing time is as crucial as execution time, while in DW the parsing time is very little as compared to the execution time given the high volume of data.

Wednesday, September 23, 2009

Why Oracle is Not Aware of Space Below HWM?

Just thinking aloud....

High water mark (HWM)is the boundary between ever used and never used space in a segment. Oracle always reads up to the HWM during full table scan, no matter if the space is empty.

Why CBO doesn't know this fact?

It should just be a matter of adding yet another statistic in the arsenal of CBO and should be gathered up during stat gathering job?

Or am I missing something here....

Tuesday, September 22, 2009

In Oracle, There Does Exist a Parameter Called Make_Database_Fast=True

Well, not exactly, but in most cases, keeping the statistics in your Oracle database fresh, you get faster results.


Now come kill me in comments....:)

Selectivity and Cardinality

In performance tuning, understanding key concept of selectivity and cardinality are very important.

According to the Chris Antognini:

Selectivity is the value between 0 and 1, and it is the fraction of rows returned after applying a filter on the table. For example if a table has 10,000 rows and the query returns 2601 rows, the the selectivity would be 2601/10000 or .26 or 26 percent. Selectivity enables you (or optimizer for that matter) to decide which data access method is optimum in the execution plan.

Cardinality is the number of rows returnd by an operation on the table.

Sunday, September 20, 2009

Sun Oracle Exadata Runs Oracle's Linux

As Sun sets behind the Oracle's temple, there is lot of debate as whether Oracle would keep on promoting Linux or would go for the newly acquired Solaris. Well that's aside, the newly arrived Sun Oracle Exadata storage product runs Linux by Oracle.

This storage product by Oracle is the amalgamation of high speed server, storage and software for large scale dataware house.

So for now Oracle has preferred Linux over Solaris, as far as the Exadata is concerned.

De-Dup to Dupe the DBA?

We are in the process of evaluating a new SAN machine for our data center and are in contact with various vendors. Their pre-sales guys seem to be pitching the one option quite aggressively while mentioning the others just in passing and that jewel of the crown is "De-Duplication", which means that if you have identical blocks on the SAN disks, then SAN would only store one copy of it and will save you space.

In every presentation I sit through, I get hammered by these guys about this chic new feature and they spend more than half a time on this and in remaining time,they wink and refer to other features as cool but not so cool as "De-Dup".

I plan to use the half part of SAN as shared storage for my RAC, while the other part for the RMAN backups. Now I am at loss because:

1- I don't think that my database would have too many identical blocks (if any), and if they are there, then we need to seriously re-visit the database design, instead of 'De-Duping.'

2- RMAN doesn't back the empty blocks.

So, apparently, there won't be many identical blocks on my SAN.

So how De-Dup is the silver bullet for my requirements?

I am afraid to ask the pre-sales guy as they are too enthusiastic to be interrupted and besides I am in the habit of heart-breaking.

Saturday, September 19, 2009

Patching Oracle Application Server 10g on Redhat Linux AS 4

In order to apply patch on Oracle Application Server installation on single machine

Operating System: Red Hat Linux AS 4

Application Server Version: 10.1.2.0.2

We have Oracle Metadata repository and Oracle AS on the same machine.

First apply the database patch to the metadata repository and upgrade it to 10.1.5.

Then download the AS patch: Patch Set 3 (10.1.2.3.0)

you have to apply the Application Server patch in following order:

1.Apply the Software Update to the AS installation.
2.Apply the Software Update to Infrastructure installation.
3.Apply the Metadata Repository Update.

Saturday, September 12, 2009

Oracle WebLogic Suite 11g

The reason why Oracle is the leading the market for too long a time when it comes to the enterprise software is that they are flexible and adaptable and have got mettle to learn from their not-so-successful products.

Oracle Internet Application Server (iAS) is not something which is talked about highly the middleware circles. Understandbly, that's not an official line from Oracle, but iAS lacks in stability, reliability and performance and it's OC4J is also of low quality compared to Oracle standards.

Oracle learns, and learns fast. They have now turbo-charged and terrorism-proof their middleware offerings in this regard by acquiring BEA, and by presenting Oracle WebLogic Server, an industry proven, time beaten Application Server plus much more. It replaces OC4J and though primarily it targets the JAVA, it also protects the legacy investments in Oracle Forms and Reports.

With Sun in Oracle's temple, JAVA will surely be the blue-eyed daughter and WebLogic Server proves that. With Oracle Weblogic Server, Oracle Coherence, and JRockit, Oracle Weblogic Server is one of the most important pillar in Oracle Fusion Middleware.

DBAs must learn architecture, installation, deployment, configuration, and maintenance of Oracle WebLogic Suite 11g.

Thursday, September 10, 2009

Oracle Database 11g Release 2 and Cluster Time Service

Just checked the OTN habitually and was delighted to see that Oracle released release 2 of its 11g database for Linux. Release 2 of any new database version from Oracle means that now the DBAs could seriously think about upgrading their previous release to the new one in production.

So, it's time to chalk out migration plans from 10g to 11g in production.

While browsing through the new features, the one which instantly stole my heart, was the Cluster Time Service. After haggling with NTP server, hangcheck timer thingie, and in VMWARE time synchronization which is a must to install the cluster and to keep it sane, this out-of-the-box Cluster Time Service will automatically sync the times of cluster nodes. Just only this option is enough to have a RAC now in production. Single Instance will soon seem so sissy.

Welcome 11gR2 RAC.