This blog post is the first of a series of 4 related to time series and indexing.
According to wikipedia this is the definition of a time series:
A time series is a sequence of data points, typically consisting of successive measurements made over a time interval.
This blog post shows how we can efficiently store time series in an oracle database and how indexing strategy can have a big impact on insertion performance rate.
This is the second post in my mini-series on leveraging SQL Developer Reports for DBA tasks, today with visualizing Average Active Sessions (AAS). In this article I’ll cover What AAS is and how to interpret it How to build a basic line graph in SQL Developer How to extend the graph with detailed child reports (Time […]
Topic: PyLatencyMap v1.2
and how it can be used to produce heat map visualization of SystemTap histograms.Introduction:
When studying storage performance, the latency drill down is a very important data source. Measuring the average I/O latency is often not enough, latency histograms are proven to more suitable for investigating modern storage systems. This is because for many storage systems the response time has multiple modes: think for example of the common case of storage (more...)
Recently we had a tablespace space run out and ended up in an application failure. I have questioned my DBA and he just plainly blamed the application team members who loaded large number records without a prior notice. A convincing answer, but you can’t really fool Oracle.
Oracle introduced a new DBA_TABLESPACE_USAGE_METRICS view from 10g onwards to report the space usage with in a tablespace. I created a new tablespace and immediately (more...)
Oracle caches the data blocks in buffer cache in various modes depends on the block usage. As per the Oracle documentation it can CR (Consistent mode – reads), XCUR (Current mode – updates), FREE etc. I understand and other heard saying – whenever a block READs into memory will be in CR mode while if the block is fetching for UPDATE it will be in XCUR mode so that sessions can apply the (more...)
This post is about the latest updates to PerfSheet4
v3.7 (February 2015). PerfSheet4 is a tool aimed at DBAs and Oracle performance analysts. It provides a simplified interface to extract and visualize AWR
time series data using Excel pivot charts.
PerfSheet4 is aimed at querying and displaying time-series data from AWR repository tables. This is very a rich source of information to analyze database workloads and trends in the context of performance analysis or (more...)
If you have a sorted collection of elements, how would you find index of specific value?
"Binary search" is likely to be your answer.
Algorithms theory is teaching us what binary search is most optimal algorithm for this task with log(N) complexity.
Well, hash table can do better, if you need to find key by exact match. In many cases, though, you have reasons to have your collection sorted, not hashed.
On my job, I'm (more...)
Bind peeking is a nice feature in Oracle to have many optimized plans for an SQL for various bind values. DBAs believe that bind peeking happens during a soft parse which will identify an alternate plan. Why do I say that?
Hard Parse: Parsing first time, nothing exists to bind peek
Soft Parse : SQL cursor is existing and executing not the first time. Under the soft parse, bind peeking (more...)
This blog hasn’t seen any updates since over a year now and I do apologize for that. However, the reason for that is simple. Last year I’ve decided to take one a new project. But before I was going to tell everybody about it I wanted to make sure that I also really got the time to do so. In the meantime I got the confidence that the project will succeed and that (more...)
This is a write-up of an issue I recently posted to the OTN discussion forum (https://community.oracle.com/message/12798407). I thought the associated test case was useful in demonstrating the issue, so is captured here for future reference. There were some useful replies to the OTN post, confirming my suspicions.
The test was performed using Oracle Enterprise Edition 18.104.22.168.0 on Linux.
A parallel delete blocks insert into dependent (more...)
Yesterday, I have seen huge waits “enq SQ – contention’” – in every snapshot there were thousands of waits. But the fix was so simple! Here is the root cause of the issue –
When you select from a sequence, the NEXTVAL generated from a the seq$ table if it is not cached. If it is cached, it will be available in a memory structure and no need to generate the value (more...)
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...)
This post is about tracing logical and physical reads in Oracle using systemtap. You will find a few examples illustrating the main mechanisms used by Oracle to do physical and logical I/O and you will learn how to build systemtap scripts to further explore and troubleshoot Oracle I/O.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...)
This post is about using SystemTap
for investigating and troubleshooting Oracle RDBMS. In particular you will learn how to probe Oracle
processes and their userspace
functions. These techniques aim to be useful
as well as fun
to learn for those keen into peeking under the hood
of the technology and improve
their effectiveness in troubleshooting and performance
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 22.214.171.124 – results may vary on other versions.
A typical deadlock graph arising from an unindexed foreign key is as follows.
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 :