I wanted to expand on a thread in a LinkedIn group I’m part of, where one of the members wrote “It’s funny when 2 experts are arguing about who is better”, using Tom Kyte and Jonathan Lewis as examples of people they say are “Experts”.
Disclaimer: I have not spoken to Jonathan or Tom in regard to their viewpoints on this subject, so this shouldn’t be taken as them saying any of this, just my (more...)
I am learning about how well commits scale on Oracle 11.2 and so far they seem to scale surprisingly well.
I’ve looked at two waits – log file parallel write and log file sync. Based on documents I’ve read on Oracle’s support site log file parallel write represents the time it takes to do one write to the redo logs. For mirrored redo logs the log file parallel write time includes the (more...)
A couple of people have asked me recently about a classic problem that most DBAs know: how to view ASM trace files in the VIM editor when the filenames start with a + character. To my surprise, there are actually quite a few different ways of doing it. Since it’s come up, I thought I’d list a few of them here… If you have another one to add, feel free to comment. I know that (more...)
In case you haven’t noticed it – and chances are you haven’t noticed because I just created it! – I’ve added a new page called “Tips and Techniques” to the website. It’s where I keep a random collection of odd items I’ve come across that might be of interest yet don’t deserve a full blog post. It’s sort of like a FAQ list, but not necessarily with the “F”.
You can reach it either via (more...)
While 8k is the default block size, Oracle supports other block sizes, too. Smaller block sizes (more commonly, 4k) are encouraged for OLTP workloads to reduce concurrency, and even smaller block size, 2k, is recommended for databases running on “Advanced format” (or “4K”) storage. Oracle documentation warns us of possible implications when storing larger rows in 2k/4k blocks, such as potentially larger space overhead due to metadata, or even possibility of row chaining. What it (more...)
It’s funny how you can make little savings in work all over the place in Oracle if you’re prepared to look a little closely at what’s going on. Here’s a quirky little example with LOBs and function calls that might just have some greater relevance in other situations. Here’s a little data set, and two queries that I might run against it:
create table tbl(
lob (c1) store as c_lob(
disable storage (more...)
Just a quick follow up to my previous post
on how Zone ID's are calculated.
Let's take the following example:
SQL> select rid, sys_op_zone_id(rid) zone_id
4 select chartorowid('AAAS5KAAHAAABYDAAA') rid
5 from dual
Recalling that extended ROWID
has the following format (a nice picture from Oracle Documentation
In the binary format that would correspond to:
- Data Object Number -- 32 bits
- Relative File (more...)
This is an interesting point that came up recently, where someone had a DBaaS environment running on an Exadata system for a couple of months, with no issues. It has recently started to return errors on the Database Cloud Self Service portal in the “Placement Algorithm” region:
Constraint : Number Of Databases
Current Resource Usage: 18
Total Resource Available: 10
Maximum Limit Defined: 10
Requested Value: 1
Total Resource that can be used: 10
Zone Maps is a new feature that got officially introduced
in 184.108.40.206.0 so I've decided to take a closer look.
From the Oracle Documentation:
For full table access, zone maps allow I/O pruning of data based on the physical location of the data on disk, acting like an anti-index.
Let's start by creating a test table:
SQL> create table t pctfree 95 clustering by linear order (n) as
2 select level (more...)
What Is Oracle Elapsed Time And Wall Time With Parallelism Twist
In this post I'm focusing on Oracle Database SQL elapsed time, adding parallelism into the mix and then revisiting wall time. What initially seems simple can take some very interesting twists!
If you are into tuning Oracle Database systems, you care about time. And if you care about time, then you need to understand the most important time parameters: what they are, their differences, (more...)
Before 11g if you added a new column to a table then Oracle had to lock and physically update all rows in the table. Possibly a painful process :) 11g helped with that a little, giving metadata only default values for NOT NULL columns, so the change was done only in data dictionary, not in the table data.
SQL> create table default_values (
2 id number primary key
SQL> insert into (more...)
by Kim Berg Hansen
Okay, okay, Iggy, you want one buffer get? I’ll do it ;-)
Actually two different versions that might be useful in different circumstances. First one goes like this:
create materialized view log on employees with rowid, commit scn
(employee_id, job_id, first_name, last_name, department_id)
including new values;
create materialized view log on departments with rowid, commit scn
including new values;
create materialized view log on locations with rowid, commit scn
Step 1 :
[oracle@asmoracle ~]$ /etc/init.d/oracleasm listdisks
Step 2 :
[oracle@asmoracle ~]$ /etc/init.d/oracleasm querydisk -d DISK1
Disk “DISK1″ is a valid ASM disk on device /dev/sdc1[8,33]
The device noted here is (8,33), so go to
[root@asmoracle dev]# ls -l sd*
brw-r—– 1 root disk 8, 0 Aug 18 08:39 sda
brw-r—– 1 root disk 8, 1 Aug 18 08:39 sda1
brw-r—– 1 root disk 8, 2 (more...)
Recently I was declared, for want of a better word, an Oracle ACE. I was nominated by someone I respect and admire; just the nomination itself was enough, really, to make my day. When I received notification that I had been selected I was truly surprised. I immediately thanked my nominator, who told me:
"Still, it is just a nomination form, and you did all the work that
made it so impressive. Congratulations!"