Oracle

fritshoogland's picture

A look into oracle redo, part 9a: commit – concurrency considerations

During the investigations of my previous blogpost about what happens during a commit and when the data becomes available, I used breaks in gdb (GNU debugger) at various places of the execution of an insert and a commit to see what is visible for other sessions during the various stages of execution of the commit.

However, I did find something else, which is very logical, but is easily overlooked: at certain moments access to the table is blocked/serialised in order to let a session make changes to blocks belonging to the table, or peripheral blocks like undo, for the sake of consistency. These are changes made at the physical layer of an Oracle segment, the logical model of Oracle says that writers don’t block readers.

Jonathan Lewis's picture

SQL Monitor

I’ve mentioned the SQL Monitor report from time to time as a very useful way of reviewing execution plans – the feature is automatically enabled by parallel execution and by queries that are expected to take more than a few seconds to complete, and the inherent overheads of monitoring are less than the impact of enabling the rowsource execution statistics that allow you to use the ‘allstats’ format of dbms_xplan.display_cursor() to get detailed execution information for a query. The drawback to the SQL Monitor feature is that it doesn’t report predicate information.

dbakevlar's picture

Lady Coders Conference, Denver 2017

This weekend I’m not going to have to say, “No, this is not my husband’s code” for a change.

kevinclosson's picture

Whitepaper Announcement: Migrating Oracle Database Workloads to Oracle Linux on AWS

This is just a quick blog entry to share a good paper on migrating Oracle Database workloads to Amazon Web Services EC2 instances running Oracle Linux.

Please click the following link for a copy of the paper:  Click Here.

 

fritshoogland's picture

A look into oracle redo, part 9: commit

The previous blogpost talked about a simple insert, this blogpost investigates what happens when the DML is committed. Of course this is done with regular commit settings, which means means they are not touched, which means commit_logging is set to immediate and commit_wait is set to wait as far as I know. The documentation says there is no default value, and the settings are empty in all parameter views. In my humble opinion, if you must change the commit settings in order to make your application perform usable with the database, something is severely wrong somewhere.

This blogpost works best if you thoroughly gone through the previous post. I admit it’s a bit dry and theoretical, but you will appreciate the knowledge which you gained there, because it directly applies to a commit.

First let’s look at the flow of functions for the commit:

Franck Pachot's picture

After IoT, IoP makes its way to the database

At each new Oracle version, I like to check what’s new, not only from the documentation, but also from exposed internals. I look (and sometimes diff) on catalog views definitions, undocumented parameters, and even the new C functions in the libraries. At last Oak Table World, I was intrigued by this V$SQLFN_METADATA view explained by Vit Spinka when digging into the internals of how execution plans are stored. This view has entries with all SQL functions, and a VERSION column going from ‘V6 Oracle’ to ‘V11R1 Oracle’. The lastest functions has an ‘INVALID’ entry and we also can see some functions with ‘SQL/DS’. Well, now that we have Oracle 18c on the Oracle Cloud, I came back to this view to see if anything is new, listing the highest FUNC_ID at the top and the first row attired my attention:

kevinclosson's picture

Whitepaper Announcement: Benchmarking Amazon Aurora.

This is just a quick blog post to inform readers of a good paper that shows some how-to information for benchmarking Amazon Aurora PostgreSQL. This is mostly about sysbench which is used to test transactional capabilities.

As an aside, many readers my have heard that I’m porting SLOB to PostgreSQL and will make that available in May 2018. It’ll be called “pgio” and is an implemention of the SLOB Method as described in the SLOB documentation. Adding pgio, to tools like sysbench, rounds-out the toolkit for testing platform readiness for your PostgreSQL applications.

To get a copy of the benchmarking paper, click here.

Franck Pachot's picture

Docker: efficiently building images for large software

I see increasing demand to build a Docker image for the Oracle Database. But the installation process for Oracle does not really fit the Docker way to install by layers: you need to unzip the distribution, install from it to the Oracle Home, remove the things that are not needed, strop the binaries,… Before addressing those specific issues, here are the little tests I’ve done to show how the build layers increase the size of the image.

I’m starting with an empty docker repository on XFS filesystem:

[root@VM121 docker]# df -hT /var/lib/docker
Filesystem Type Size Used Avail Use% Mounted on
/dev/sdc xfs 80G 33M 80G 1% /var/lib/docker

Franck Pachot's picture

Docker-CE on Oracle Enterprise Linux 7

Here is how I install the latest Docker version on Oracle Linux 7. You find several blog posts about it which all install ‘docker-engine’. But things move fast in this agile world and docker package name has changed. The Community Edition is now ‘docker-ce’ and you want this one to run the latest version.

I’m on OEL 7.4 but should also wotj on RHEL 7:
[root@VM188 yum]# cat /etc/oracle-release
Oracle Linux Server release 7.4

Jonathan Lewis's picture

Lock Types

Every now and again I have to check what a particular lock (or enqueue) type is for and what the associated parameter values represent. This often means I have to think about the names of a couple of views and a collection of columns – then create a few column formats to make the output readable (though sometimes I can take advantage of the “print_table()” procedure that Tom Kyte published a long time ago.  It only takes a little time to get the code right but it’s a nuisance when I’m in a hurry so I’ve just scribbled out a few lines of a script that takes a lock type as an input parameter and reports all the information I want.

To prevent automated spam submissions leave this field empty.
Syndicate content