Search

Top 60 Oracle Blogs

Recent comments

November 2013

When do Oracle Parallel Execution Slaves issue buffered physical reads – Part 2?

In the previous post about in-memory parallel execution I described in which cases the in-mem PX can kick in for your parallel queries.
A few years ago (around Oracle 11.2.0.2 and Exadata X2 release time) I was helping a customer with their migration to Exadata X2. Many of the queries ran way slower on Exadata compared to their old HP Superdome. The Exadata system was configured according to the Oracle's “best practices”, that included setting the parallel_degree_policy = AUTO.

Replicating Tanel’s Script Library

Tanel does offer a zip file with all of his scripts. The zip seems up-to-date now; I started doing this alternative technique awhile ago when the zip file didn’t seem to get updated as quickly as the raw scripts directory.

  mkdir tpt
  cd tpt

wget -r -nH --cut-dirs=2 --no-parent --reject="index.html*" http://blog.tanelpoder.com/files/scripts/

  cd ..

[svn/git] add tpt
[svn/git] commit tpt -m "added Tanel Poder's script library to our script repository"

Please remember that as Tanel says on his own website, “always proofread the scripts and test their effect out in a test environment before running in production.”

Replicating Tanel’s Script Library

Tanel does offer a zip file with all of his scripts. The zip seems up-to-date now; I started doing this alternative technique awhile ago when the zip file didn’t seem to get updated as quickly as the raw scripts directory.

  mkdir tpt
  cd tpt

wget -r -nH --cut-dirs=2 --no-parent --reject="index.html*" http://blog.tanelpoder.com/files/scripts/

  cd ..

[svn/git] add tpt
[svn/git] commit tpt -m "added Tanel Poder's script library to our script repository"

Please remember that as Tanel says on his own website, “always proofread the scripts and test their effect out in a test environment before running in production.”

When do Oracle Parallel Execution Slaves issue buffered physical reads – Part 1?

This post applies both to non-Exadata and Exadata systems.

Before Oracle 11.2 came out, it was true to say that Oracle Parallel Execution slaves always do direct path reads (bypassing buffer cache) when doing full segment scans. This should not be taken simplistically though. Even when you were doing full table scans, then yes the scanning was done with direct path multiblock reads – but if you had to visit other, additional blocks out of the scanning sequence, then these extra IOs were done with regular buffered reads. For example, next row piece fetching of chained rows or or undo block access for CR reads was done with buffered single block reads, or even buffered multiblock reads, if some form of prefetching kicked in.

When do Oracle Parallel Execution Slaves issue buffered physical reads – Part 1?

This post applies both to non-Exadata and Exadata systems.

Before Oracle 11.2 came out, it was true to say that Oracle Parallel Execution slaves always do direct path reads (bypassing buffer cache) when doing full segment scans. This should not be taken simplistically though. Even when you were doing full table scans, then yes the scanning was done with direct path multiblock reads – but if you had to visit other, additional blocks out of the scanning sequence, then these extra IOs were done with regular buffered reads. For example, next row piece fetching of chained rows or or undo block access for CR reads was done with buffered single block reads, or even buffered multiblock reads, if some form of prefetching kicked in.

When do Oracle Parallel Execution Slaves issue buffered physical reads – Part 1?

This post applies both to non-Exadata and Exadata systems.

Inside a RAC 12c GNS cluster

Based on some reader feedback I started looking at GNS again, but this time it will be for RAC 12c. According to the documentation GNS has been enhanced so that you can use it without subdomain delegation. I decided to try the “old fashioned” way though: DHCP for VIPs, SCAN IPs, subdomain delegation and the like as it is the most complex setup. I occasionally like complex.

The network setup is exactly the same as I used before in 11.2 and thankfully didn’t require any changes. The cluster I am building is a 2 node system on Oracle Linux 6.4 and the Red Hat compatible kernel. I have to use this as the Unbreakable Kernel doesn’t know about block devices made available to it via virtio-scsi. I use virtio-scsi for shared block devices very much in the same way I did for Xen.

Result Cache Latch and PDBs

One interesting aspect of Oracle 12cR1 database when it comes to PDBs is how latching is done. For example, if all PDBs have to work under the same latch then contention in one PDB can easily affect users in other PDBs too.

Continuing my series of posts on the Result Cache latch today I'll check what happens when you try to acquire RC latch from two different PDBs.

Session 1

The first session is connected under container name TEST:





SQL> select sys_context('userenv', 'con_name') con_name from dual;

CON_NAME
--------------------
TEST

SQL> select addr from v$latch where name='Result Cache: RC Latch';

ADDR
----------------
0000000060041C78

Session 2

The second session is connected under container name TEST2:

SQL> select sys_context('userenv', 'con_name') con_name from dual;

CON_NAME
--------------------
TEST2

OakTable World UK 2013

OakTable World UK 2013 Conference - Manchester 2-3 December 2013
Event date: 
Mon, 2013-12-02 - Tue, 2013-12-03

Drill Down the I/O stack at UKOUG Tech13

It’s just under a week to go before the doors open for the UKOUG Tech13 conference and the adjoining OakTable World UK 2013 sessions, so I thought I would write a very short blog post about what I will be doing there, where I’ll be, and what I’m looking forward to. This year I will … Continue reading "Drill Down the I/O stack at UKOUG Tech13"