I recently investigated a performance problem on an Oracle 11.2 OLTP trading system and although we still don’t fully understand the issue (and which versions of Oracle it effects), I thought I would share what we found (and how we found it). We had a hardware failure on the database server, within 30 seconds the database had automatically been restarted on an idle identical member of the cluster and the application continued on the (more...)
Happy New Year!
Oracle supplied various tools to trace the SQL and identify execution plans for the SQLs. 10046 Event, AUTOTRACE, DBMS.XPLAN etc are some of the most used tracing methods in DBAs daily life. Sometimes, we need to be very careful while using these tools specially using bind variables. Following are some test cases where wrong plans reported by the above tracing tools.
Most easiest method to get (more...)
APPEND hint in oracle is very useful, there are many benefits
Fragmented data loading – data will clustered above the HWM so that similar data will be together. Otherwise Oracle will place the records in blocks using PCT_FREE rule
- No undo will be generated – in large direct path insert will free up large amount of data blocks
- Less CBC latches (because of no UNDO blocks), less CPU usage
- Readers need not undo the records, (more...)
Introduction: Performing input/output to and from the storage media, i.e. physical I/O, is one of the critical tasks underlying all database engines. When accessing data (more...)
Userspace probing is a very powerful technique that can be used to complement the available (more...)
When trying to understand why the optimiser might choose not to use an index to read rows from a table, I find the following diagram helpful.
The axis along the bottom represents the selectivity of the query (0% being no rows selected, 100% being all rows selected), and the axis up the side represents the cost of the chosen operation (more costly = more work to do).
When reading from a table without using an (more...)
Here is a step-by-step analysis of the deadlock which occurs due to unindexed foreign keys. This analysis was performed on Oracle XE 18.104.22.168 – results may vary on other versions.
A typical deadlock graph arising from an unindexed foreign key is as follows.
---------Blocker(s)-------- ---------Waiter(s)--------- Resource Name process session holds waits process session holds waits TM-0000508a-00000000 27 101 SX 28 12 SSX TX-00090013-0000019b 28 12 X 27 101 S
This type of (more...)
I have been investigating “enq: TM – contention” wait events, which are almost certainly due to un-indexed foreign keys. One such extreme example which showed up in a Statspack report is as follows :
One of the great things about working in IT is that you can often win an argument simply by being right. Not because of who you are or because you are more eloquent than others, but because the facts support your position. Almost every IT person I have ever met respects facts.
In order to win arguments this way, you of course need some facts to work with. And that’s where too many people fail. (more...)
Motivations: The techniques and tools described here can be used for performance investigations to complement wait-event based information gathered from the Oracle engine, such as information available with ASH and sql monitoring. They (more...)
Spoiler: If you have reached this article in search of a tool for quantitative analysis of storage performance and in particular for measuring random read I/O in Oracle, I'd rather advise you to use tools that allow generating test workloads in a controlled manner, in a way that can be understood and measured and in particular with latency details together with IOPS measurements. For example (more...)
Random thoughts on a Friday afternoon…
We’ve all got problems. More to the point, every IT department or team has problems of some kind. It’s why we hire consultants, buy products, start long and arduous journeys into the great unknown depths of root cause analysis, and so on.
What fascinates me is the level at which we come to identify with our problems. When I’ve gone into an environment to deliver recommendations, the conversation usually (more...)
Recently while observing AWR reports, I’ve seen a very good example of how average value hides important pattern.
Here is a Workload Comparison section from an AWR diff report (generated with $ORACLE_HOME/rdbms/admin/awrddrpt.sql):
Workload Comparison ~~~~~~~~~~~~~~~~~~~ 1st Per Sec 2nd Per Sec %Diff 1st Per Txn 2nd Per Txn %Diff --------------- --------------- ------ --------------- --------------- ------ DB time: 0.6 0.6 1.6 0.0 0.0 -50.0 CPU time: 0.3 0. (more...)
My test table will have 1000000 rows with 1000 NULL values for “OBJECT_NAME” column.
Tests are performed on 11gR1 version.
select c.table_name, c.column_name, c.data_type, c.num_nulls,
When you execute an SQL – why there is a difference in Consistent gets on the same set of data for same SQL. For any SELECT query, oracle need to prepare the consistent data in the buffer cache using undo records then forward the data the requesting session as of a specific SCN. Touching any block in buffer cache to prepare the blocks for consistent data is known as Consistent Reads.
In an (more...)