October 2016

Oracle Database Cloud (DBaaS) Performance Consistency - Part 6

#333333; font-family: Verdana, Arial, sans-serif;">In the next parts of this series I'll have a look at the results of similar performance consistency tests performed on a comparable Amazon RDS Oracle cloud database instance.

#333333; font-family: Verdana, Arial, sans-serif;">

My Favorite Scripts – 2016

I did a talk at the Dallas Oracle Users Group Technical Training Day which was held on Oct 20th. The title was My Favorite Scripts and basically talked through some of the scripts I use most often and logged on to a system and showed how they work. I promised I would upload the presentation and a zip file here. So here you go.

 

My Favorite Scripts 2016 – Presentation

My Favorite Scripts 2016 – Zip File


 

I had a great time doing the talk. I hope you found it useful. Please let me know if you have any questions.

 

Transactions and SCNs

It’s general knowledge that the Oracle database is ACID compliant, and that SCNs or ‘system change numbers’ are at the heart of this mechanism. This blogpost dives into the details of how the Oracle engine uses these numbers.

Oracle database version 12.1.0.2.161018
Operating system version: OL 7.2, kernel: 4.1.12-61.1.14.el7uek.x86_64 (UEK4)

Redo generation
Whenever DML is executed, redo is generated in the form of ‘change vectors’. These change vectors are copied into the redo buffer as part of the transaction, during the transaction. The function that performs this action is called ‘kcrfw_copy_cv()’. This can be derived by watching the foreground process perform memory copy into the memory area of the redo buffer.

In order to do this, you first need to find the memory area of the redo buffer. This can be done by executing ‘oradebug setmypid’ and ‘oradebug ipc’ as sysdba, and examine the resulting trace file:

Truncating a timestamp to the second

We had an interesting AskTom question recently, about why certain TRUNC commands would generate an error when applied to a TIMESTAMP value.  At first glance, TRUNC seems fine.


SQL> select trunc(localtimestamp,'MM') to_the_month from dual;

TO_THE_MO
---------
01-OCT-16

1 row selected.

SQL> select trunc(localtimestamp,'YYYY') to_the_year from dual;

TO_THE_YE
---------
01-JAN-16

1 row selected.

SQL> select trunc(localtimestamp,'DD') to_the_day from dual;

TO_THE_DA
---------
20-OCT-16

1 row selected.

But the moment you try apply a TRUNC down to the second, then things go wrong

User Group Boards, Voting, Oh Yeah and ODTUG

I think we’ve just about all heard enough about voting this election year, but I want to discuss a slightly different angle and that’s when it comes to user groups.  As most people know, I’m pretty passionate about everyone contributing time to your local and national user groups.

Pass Summit 2016 and Old School MSSQL DBA Skills

I’ll be attending my very first Pass Summit next week and I’m really psyched!

Refreshing Materialized Views with Referential Integrity Constraints

I have a number of tables on a reporting system which have referential integrity constraints, and whose contents are replicated from a primary system. I am going to create materialized views on these prebuilt tables to manage incremental refresh. However, the referential integrity means that some materialized view will have to be refreshed before others which refer to them.

Conjuctive Normal Form

I recently tweeted about a comment I’d picked up at the Trivadis performance days regarding tablescans and performance.

“If you can write your SQL in conjunctive normal form it can help the optimizer to offload more predicates”

Inevitably someone asked me if I had an example to demonstrate this – I didn’t, and still don’t really, but here’s an interesting demo based on an example from the Oracle In-Memory blog showing how the optimizer will rearrange your filter predicates before passing them to the tablescan code for evaluation against an inmemory table.

Taking a peek at SYS_CONTEXT

There was Twitter discussion about using context variables, accessed via SYS_CONTEXT within queries. It’s an easy means of passing parameters into a view. The question that got asked was – does a sys_context variable act exactly like a bind variable. Let’s take a look at a simple demo to explore that.

We’ll create a table with 1 row with a value of ‘a’, and 100,000 rows with a value of ‘b’.

Translating SQL (a migration tool)

When you are moving from another database to Oracle, in a perfect world, you’d set aside plenty of time to refactor all of your code and re-engineer your applications to get the maximum benefit out of the new database technology.  But there are not many “perfect worlds” out there  in the IT landscape Smile  So sometimes we have to “make do” with the time and budget constraints that are placed upon us.  To assist in migrations without having to change every piece of SQL code, you can use our translation tool that will intercept SQL as it is passed to the database, and replace with one of your own choosing. 

Here’s a little demo of the feature called “SQL Translation”.  First we’ll create a user DEMO that our “application” will be running under.  It has two tables – a copy of EMP, and an empty copy of EMP (called