Zohar Elkayam at ilOUG meetup

ilOUG meetup: Oracle 12c New Features For Better Performance (slides)

It’s been a while since the Israeli user group (iloug) had a technology meetup (SIG meeting). The last time that happened was over two years ago – and since then, we only had the bigger conferences with guests from all over the world. Yesterday we renewed that long time tradition and held such a meetup.

Although I am not part of the OUG board (and not for the lack of trying, just no elections for a very long time), I volunteered to help host the meeting together with Oracle Ace Associate Oren Nakdimon (@dboriented, http://db-oriented.com). I also presented a new session: “Oracle 12c New Features For Better Performance” (see below for the agenda and slide deck).

Read more

Script: Inactive Parallel QC Holding Parallel Processes

Working on a data warehouse system can be quite challenging, as I mentioned in the post from yesterday. One of the things we need to take care of is the amount of parallel processes that are in used at all times. Yesterday I wrote about how to locate downgraded sessions. Today we will look at another aspect – who “steals” parallel processes and what can we do to solve it.

One of the biggest thieves of parallel processes in a data warehouse environment are actually developers and DBAs. Sometime, while developing or just handling the system, DBAs run queries in GUI tools (TOAD, PL/SQL Developer, SQL Developer). That for itself isn’t a big deal, but those tools have a common feature. Instead of returning the entire data set, it sometimes return only a few records (the number depends on the tool). In that case, the cursor is being kept open and is waiting for then next fetch or until different query is ran. While waiting, the parallel processes are being reserved for that query, but the query coordinator is marked as Idle.

If there are couple of DBAs running those queries from multiple windows and they “forget” their queries (because sometime they run for very long) – that becomes a problem: “real” application queries are being downgraded causing the mess I described yesterday.

To solve that, we created the following query. We actually took that query and wrapped it with shell script to automatically kill the session, but let’s keep it to the basic query we used.

Read more

Script: Finding Session With Downgraded Parallel Execution

I was working with the data warehouse team at a customer site and at some point we realized that some parallel executions are not getting enough resources (downgraded).

Not getting enough parallel processes in such a complex environment is really bad. That means that since everybody is hogging the CPU, some sessions will not be able to complete inside the night ETL time frame. If that happens – some ETLs will go on into the day providing wrong data to the customer in even worse performance for the morning shift. Another aspect is the memory usage for large sorts or hash joins. Using less processes will mean some of the data will not reside in the memory and will need to be allocated in the temporary tablespace.

The customer asked how can he find those downgraded (meaning, not getting enough parallel processes) at real time. This is the script we created for that.

Read more

Manually Generating AWR Report

One of my customers asked me to check performance on his production database server but could not allow any access to the server itself. He asked if I could generate the AWR reports from his client machine and since it’s not really trivial (or hard) I created this script.

Read more

Script: Finding the Top N Queries for a User (AWR)

In some conditions, I need to find the top N queries for a specific user in the database.
Assuming my customer is running Enterprise Edition and have tuning pack licenses, it is easy enough to pull the data off the Automatic Workload Repository (AWR).

For some reason, a lot of DBAs are not aware that the AWR report is just a report – and you can query the base table yourself to extract more information if you need it.

This is a short script I sometime find very useful – finding the top N queries for a specific user.

Read more

elephant don't forget

Oracle 12c Caching and In Memory Databases

A few weeks ago, I was asked to give a private session about In-Memory database vs. traditional persistent databases. I created an hour-long session explaining the basics of database systems, how in-memory systems work, and when to use each of the systems.

One of the questions I got (and answered) was about persistent (regular) database cache mechanism – and I felt this is a good opportunity to write about Oracle 12c new feature – the Force FULL database cache.

In my session, I gave a long explanation about several hybrid solutions (such as the MySQL memory storage engine, and Oracle 12c database In-Memory option) but this post will focus on the Force Full database cache, which will be explained in the second part of the post.


Read more