In preparation for upcoming Oracle Open World 2014 and BGOUG 2014 (Bulgarian User Oracle Group) conferences, I will write a series of tips and tricks for new Oracle 12c RAC and ASM features. Today I would like to review the new Oracle 12c RAC Flexible ASM feature. For our discussion as a reference point you can source details in the useful My Oracle Support (http://support.oracle.com) note FAQ: Oracle Flex ASM 12c (more...)
Not every commit results in a redo write. This is because there are multiple optimizations (some controlled by the user e.g. with COMMIT_LOGGING parameter, some automatic) that aim at reducing the number of redo writes caused by commits by grouping redo records together. Such group or “piggyback” commits are important for understanding log file sync waits and various statistics around it. In particular, “piggyback” commits play a key role when many sessions commit concurrently (more...)
VDI is the new technology that gives lots of benefits.
• Efficient use of CPU and memory resources
• Reduced desktop downtime and increased availability
• Patches and upgrades performed in data center
• New users can (more...)
I have been teaching the Enkitec Exadata Administration Class this week and made an interesting observation I thought was worth sharing with regards to IO Resource Management on Exadata.
I have created a Database Resource Manager (DBRM) Plan that specifically puts a resource consumer group to a disadvantage. Actually, quite severely so but the following shouldn’t be a realistic example in the first place: I wanted to prove a point. Hang-on I hear you say: you (more...)
I have a table with several indexes on it, and I have two versions of a query that I might run against that table. Examine them carefully, then come up with some plausible reason why it’s possible (with no intervening DDL, DML, stats collection, parameter fiddling etc., etc., etc.) for the second form of the query to be inherently more efficient than the first.
select bit_1, id, small_vc from bit_tab where bit_1 (more...)
There is a very common mistake in troubleshooting log file sync (LFS) waits: comparing its average time to average log file parallel write (LFPW) and trying to deduce from that whether the root cause of the wait is slow I/O or something else. The fact that this approach is recommended by Oracle itself (e.g. MOS 1376916.1) and many independent experts unfortunately doesn’t make it any less wrong.
It is well known that averages and ratios can distort the reality (Milsap and (more...)
It can also lead to some peculiar side effects.
Let's begin by creating a test table with the on commit refresh materialized zone map:
SQL> create (more...)
In Oracle releases 10.2.0.x and later join processing can be made more efficient by the use of Bloom filters, primarily to reduce traffic between parallel query slaves. What is a Bloom filter? Named after Burton Howard Bloom, who came up with the concept in the 1970s, it’s an efficient data structure used to quickly determine if an element has a high probability of being a member of a given set. It’s based (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...)
One question I get asked a lot is “how can I add additional agent software to OEM 12c”? The answer is pretty easy; just download and apply to the software library. Now what does that mean? In this post, I’ll explain how to download additional agents for later deployments to other platforms.
After logging into OEM 12c, go to the Setup -> Extensibility -> Self Update (Image 1).
Here’s one that started off with a tweet from Kevin Closson, heading towards a finish that shows some interesting effects when you truncate large objects that are using ASSM. To demonstrate the problem I’ve set up a tablespace using system allocation of extents and automatic segment space management (ASSM). It’s the ASSM that causes the problem, but it requires a mixture of circumstances to create a little surprise.
create tablespace test_8k_auto_assm datafile -- OMF SIZE 1030M autoextend (more...)
What grabbed my attention from the many different and very useful public and private (more...)
Watch Oracle Elapsed Time and Wall Time With Parallel Query
In my recent postings I wrote that when using the Oracle Database parallel query a SQL statement's wall time should be equal to its elapsed time divided by the number of parallel query slaves plus some overhead.
That may seem correct, but is it really true? To check I ran an experiment and posted the results here. The results are both obvious and illuminating.
I’ll be in Openworld again this year, and have a couple of speaking slots…
I’ll talk about new features for developers in 12c on Sunday, Sep 28, 2:30 Moscone South 303
and of course, at the awesome Oaktable world..
Drop in, learn some cool things, or just pop up and say Hello!
(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...)