October 2016

Locked rows and lunch breaks ? A simple fix

Even in the new world of stateless web applications, from time to time we still come across the “lunch break locking” problem.  That is, someone or something, commences a transaction which locks some rows, and then their session goes idle, without committing or rolling back the transaction.  The metaphor commonly used was the “someone locks a row in a table and then goes out for lunch”, with them being totally oblivious to the havoc they have caused until they return.

Generating rowids

We have several posts on AskTom where it is described how to “carve up” a table into equi-sized chunks in order to (say) perform a task in parallel on that table.  Here is an example of one.

Much of this has nowadays been obsoleted by the DBMS_PARALLEL_EXECUTE package, but in either instance, one key point remains:  When you are generating rowids, there is no guarantee that the rowid you generate is either valid or will return a row from the table even if you used xxx_EXTENTS to build that rowid.

Let’s look at an example

Using ORDS To Protect the Crown Jewels (your data)

Using ORDS To Protect the Crown Jewels (your data)

Introduction

Information Technology today is fantastic. Never before have we had so much capability to collect, store, and analyze data. Never before have we had so many wonderful tools for presenting data and our analysis of data.

Today, data represents the “crown jewels” of IT. All of our wonderful systems are less useful if the data is incorrect or inaccurate.
crownjewelshttp://kingtraining.com/blog/wp-content/uploads/2016/10/crownjewels.png 600w" sizes="(max-width: 300px) 100vw, 300px" />

Associating Oracle Database 12c Databases with Compliance Standards

In my last post, I walked you through the first part of installing the Oracle Database 12c STIG compliance standards sample code. The next step to using these compliance standards is to associate your Oracle Database 12c databases with these standards. You may recall there are two separate standards in the sample code, one for multitenant databases and the other for conventional architecture databases. The process of associating the databases is the same in each case. You simply have to choose the compliance standard that matches your architecture. In this walkthrough, I will show you how to associate a multitenant database with the “Oracle 12c PDB STIG” compliance standard.

The first step is to go to the “Compliance Standards” tab on the “Compliance Framework” page. To access this, follow the menu path “Enterprise” -> “Compliance” -> “Library”:

Fixed Stats

There are quite a lot of systems around the world that aren’t using the AWR (automatic workload repository) and ASH (active session history) tools to help them with trouble shooting because of the licensing requirement – so I’m still finding plenty of sites that are using Statspack and I recently came across a little oddity at one of these sites that I hadn’t noticed before: one of the Statspack snapshot statements was appearing fairly regularly in the Statspack report under the “SQL Ordered by Elapsed Time” section – even when the application had been rather busy and had generated lots of other work that was being reported. It was the following statement – the collection of file-level statistics:

Running external programs from the scheduler

Although I normally use the job or scheduler facility to run database-centric style processes, most commonly PL/SQL programs, there is nothing to stop you from using the scheduler to gain control over tasks that might normally need to be done outside of the database.  And of course, in the world of virtualization, cloud and other such innovations, the ability to initiate and perform tasks from within the database becomes increasingly useful, since often access to the underlying OS layer is either prohibited or restricted.

For example, here’s a simple demo of performing an original-mode export straight out of the scheduler:

Oracle Database Cloud (DBaaS) Performance Consistency - Part 5

This is the fifth part of this installment, and before coming to comparisons to other cloud providers, in this part I show the results of the read-only test that I've already described in part three of this series, but repeated at a later point in time. The test setup was identical and can be checked in the mentioned previous part.

The reason for running the test again was the fact that I was informed during the first test run that the zone that my Oracle Cloud domain was assigned to was temporarily overloaded, which I also noticed since I wasn't able to create new services for some time.

Hence I decided to repeat the tests after it was confirmed that the issue got resolved.

Dallas Oracle User Group Performance & 12.2 New Features Technical Day

Just letting people in DFW area know that I’m speaking at the DOUG Performance & Tuning and 12.2 New Features Technical Day!

Time:

  • Thursday 20 October 2016 9:30am-5:30pm

Location: 

  • Courtyard & TownePlace Suites DFW Airport North/Grapevine, TX
    2200 Bass Pro Court|Grapevine, TX 76051 [map]

Speakers (Seven Oracle ACE Directors!):

  • Jim Czuprynski

  • Charles Kim

  • Cary Millsap

  • Dan Morgan

  • Kerry Osborne

Smoke and mirrors: monitoring function calls that do not exist anymore

During investigating I ran once again into statistics in the Oracle database that still provide a useful details, but the actual naming of the statistic is describing a situation that in reality does not exist anymore. The statistics I am talking about are ‘calls to kcmgcs’, ‘calls to kcmgrs’, ‘calls to kcmgas’ and ‘calls to get snapshot scn: kcmgss’.

Disclaimer: this is research. Any of these techniques potentially can crash your instance or leave your database in a corrupted state. Test the techniques used in this article severely before applying it in an actual situation. Use at your own risk.

Back to the ‘calls to’ statistics. To see what I mean here, you can look up the functions in symbol table in the Oracle executable. There are several ways to do that, one way is using gdb: