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...)
I suppose it had to happen eventually but one of my presentations has ended up on YouTube. It’s a recent presentation I did for the Oracle Midlands user group in September.
The topic is (as the title of this blog post hints at!)Boosting select performance by clustering data. The video consists of the slides I presented, changing as the presentation progresses, with my audio over the top. It goes on for a bit, (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...)
Recently I’ve seen an issue with CPU usage on a server running Windows 2003 Server in a VMware. This is a small Virtual Machine with just 2 cores allocated (which are possibly mapped to “threads” on a host level but I don’t know the details). For some reason very high System CPU time was reported in a Statspack report.
Here is how it looks like in a 1 hour Statspack report:
Host CPU (CPUs: 2 (more...)
Chasing cost efficiency, business often cuts back on money spent on UAT boxes used for performance testing. More often than not, this is a bad-decision, because the only thing worse than not having a UAT environment is having a UAT environment that is nothing like production. It gives a false sense of security while exposing your application to all sorts of nasty surprises. In this post I tried to summarize a few typical configuration differences (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 220.127.116.11 – 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...)
SQL trace file provide the highest level of detail possible about SQL execution. The problem with that information is converting it to a convenient format for further analysis. One very good solution is parsetrc tool by Kyle Hailey written in Perl. It gives high-resolution histograms, I/O transfer rates as a function of time, and other very useful info. Unfortunately, I myself am not a Perl expert, so it’s a bit difficult for me to customize (more...)
(the details are investigated and specific to Oracle’s database implementation on Linux x86_64)
Exadata IO: This event is not used with Exadata storage, ‘cell single block physical read’ is used instead.
Despite p3 listing the number of blocks, I haven’t seen a db file sequential read event that read more than one block ever. Of course this could change in a newer release.
One of the important things (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 :
You can access performance monitoring dashboard using this address. Access will be available for a month or so, until my trial account will expire. You would need to use following login credentials - user: redsam, password (more...)