Posts

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 →

/0 Comments/by /Estimated Reading Time: 2 Minutes

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 →

/0 Comments/by /Estimated Reading Time: 2 Minutes

Using Read Only Tables

,

While visiting a customer, we had a conversation about the correct way to stop users from writing to code tables. The customer described his ancient logic: when he wanted to move table to a read only state, he removed the write (insert/update/delete) permissions from all of his users. That was good enough for him for years – since he started using this method way back in Oracle 8. All was well, util this week he found out that the table owner’s privileges could not be revoked – and due to a bug, someone accidentally deleted the table content…

So I asked him why didn’t they change it to read only tables and he had no idea what I am talking about.
Well, I wrote about read only table like 6 years ago (in Hebrew), but I never thought I should translate it – since it’s a fairly “old” (11gR1) feature and everybody knows about it, right?
Well, this week proved me wrong. Here is what I wrote about it years ago.

Read more →

/0 Comments/by /Estimated Reading Time: 2 Minutes

Oracle Week 2016: Advanced PL/SQL Optimizing for Better Performance (slides)

,

This is the presentation for Oracle Advanced PL/SQL session I did in Oracle Week 2016, a few weeks ago.

I wrote about the Oracle Week conference in the previous post so I won’t go over that again… 🙂

This presentation was all about PL/SQL. Most of it are things that are quite some old, but for some reason people seem to miss out on them. The presentation is divided into 3 parts.

The first one is development and it takes up most of the day. We talk about complex data types, cursors and things that are into PL/SQL should probably know. In this part I also try to add some new features and stuff that people will find interesting, or else it gets really boring. The second part is about the compilation processes and the last part is about tuning.

This year presentation was quite big – around 40 people in the class and got excellent feedback: 4.97/5 for overall satisfaction.

Here is the agenda:

  • Developing PL/SQL:
    • Composite datatypes, advanced cursors, dynamic SQL, tracing, and more…
  • Compiling PL/SQL:
    • Dependencies, optimization levels, and DBMS_WARNING
  • Tuning PL/SQL:
    • GTT, Result cache and Memory handling
  • Oracle 11g, 12cR1 and 12cR2 new useful features
  • SQL Developer Command Line tool

Read more →

/0 Comments/by /Estimated Reading Time: 1 Minute

Oracle Week 2016: Oracle Database Advanced Querying 2016 (slides)

,

This is the presentation for Oracle Advanced Querying session I did in Oracle Week few weeks ago.

Since most of my readers aren’t from Israel, let explain a bit about this event. Oracle Week is by far the biggest Oracle oriented conference/event in Israel. It’s a 5 days conference that is being organized by John Bryce Education (a technology schooling company) and has around 1500 participants each year. It’s an educational event – meaning, as less promotion/sell speeches as possible.

One of the main differences between this event and other conferences is the platform: instead of having five or six 45-60 minutes sessions, back to back – the event has 10-12 different day long sessions every day, for 5 days. Each session is from 9:00 until 16:30 (with breaks, of course) so there are enough time to go over many different aspects of the same topic.

This session is one of the crown jewels. For years it was considered to be the largest session in the conference (with 60-80 attendees) and couple of years ago it landed in my laps.

This year session was very good. The participant reviews was around 4.8/5 and course material were around 4.9/5 which was awesome.  We had around 45 people in the class what smaller than last year but still the biggest session of the day.

This year agenda included some of 12.2 new features (the ones I got to actually test, not all of them) which was a big improvment on last years session.

In the agenda:

  • Aggregative and advanced grouping options
  • Analytic functions, ranking and pagination
  • Hierarchical and recursive queries
  • Regular Expressions
  • Oracle 12c new rows pattern matching
  • XML and JSON handling with SQL
  • Oracle 12c (12.1 + 12.2) new features
  • SQL Developer Command Line tool

Read more →

/0 Comments/by /Estimated Reading Time: 2 Minutes

PL/SQL Teaser: A Valid Package With No Callable Subprograms

,

Last week I saw a PL/SQL teaser tweet by Steven Feurestein (@sfonplsql):

https://twitter.com/sfonplsql/status/583365900373377024

The question sounded simple: can you build a valid package with header and body with more than one procedure – that can compile but can never run. That was a really interesting question and I found myself thinking about it while working.

Since it is really had some good things we could learn from it, let’s go over my thought process and get to know this unique PL/SQL behavior.

Read more →

/0 Comments/by /Estimated Reading Time: 5 Minutes