I may have said it before but I consider presenting and teaching a great way to expand one’s knowledge: first of all it requires me to really understand a subject. Secondly, when presenting, you get lots of interesting questions that can turn into blog posts like this one.
Lately I have been asked about the impact of synchronous log shipping to a physical standby database. I was sure there was an effect to be observed, (more...)
Recently I’ve seen not so smart optimizer behavior: one query took long time to parse, and ended with an error hitting PGA_AGGREGATE_LIMIT in few minutes; another query was just parsed for ages while using reasonable (under 2G :)) amount of PGA and still could hit PGA_AGGREGATE_LIMIT but after way more time – up to an hour.
Both cases were similar and involved queries which were accessing views; and those views’ code is generated by an (more...)
To me, ‘cloud computing’ is renting a compute resource to perform a task. In order to use that compute resource, you need to instruct it to do something, which is typically done via the network. If the task the compute resource needs to fulfil is being an application server or being a client or both in the case of an application server that uses an Oracle database, the network latency between the client of the (more...)
One of the optimizer enhancements that appeared in 12.2 for SQL is the “band join”. that makes certain types of merge join much more efficient. Consider the following query (I’ll supply the SQL to create the demonstration at the end of the posting) which joins two tables of 10,000 rows each using a “between” predicate on a column which (just to make it easy to understand the size of the result set) happens (more...)
There’s a thread running on OTN at present about deleting huge volumes of duplicated data from a table (to reduce it from 1.1 billion to about 22 million rows). The thread isn’t what I’m going to talk about, though, other than quoting some numbers from it to explain what this post is about.
An overview of the requirement suggests that a file of about 2.2 million rows is loaded into the table every (more...)
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 (more...)
In the last part of this installment I'll have a brief look at the network performance measured in the Oracle DBaaS environment, in particular the network interface that gets used as private interconnect in case of RAC configuration. The network performance could also be relevant when evaluating how to transfer data to the cloud database.
I've used the freely available "iperf" tool to measure the network bandwidth and got the following results:[root@test12102rac2 ~]# iperf3 (more...)
On one of the databases I’m looking after (126.96.36.199, Solaris, non-RAC), several different INSERT statements (all into tablespaces with manually managed segments) suffer from occasional hiccups. The symptoms are always the same: in one of the sessions, the INSERT gets stuck doing lots of single-block I/O against one of the indexes on the inserted table, and if other sessions are running similar INSERTs, they hang on enq: TX – index contention. The (more...)
Yep, I'm still doing this. A bunch of questions to come, many worth the visit. Just a few weeks between drinks, so to speak. My annual review is a little late, too. Anyway...
Time for the performance questions in my 2015 survey
. A favourite topic of mine, and my boss, Penny Cookson, lives for tuning.
Q9. How do you proactively address performance? (tick all that apply)
- I would be surprised if this (more...)
Recently I repeated the I/O related tests on a 188.8.131.52 instance for curiosity and was surprised by the fact that I consistently got significantly better results as on 184.108.40.206 and 220.127.116.11.
Now you're probably aware that the version 12.2 so far is "cloud-only", so I can't tell / test whether the version 12.2 is generically providing that increased performance or whether Oracle has optimized (more...)
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 (more...)
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 (more...)
In this second part of this installment I'll focus on the performance figures related to I/O encountered when the corresponding tests were performed on the platform.IOPS
When running with minimum sized buffer cache, direct and asynchronous I/O enabled, the following average read-only IOPS figures were measured over a period of several days (this is the test described in part three
of the "performance consistency" series) .
First, running on a 4 OCPU single instance configuration (more...)
web scale patterns in the bol.com back office – Event Sourcing
Last week we started a series of blog posts we show you how we use “web scale” patterns to achieve scaleability and flexibility in our back office software. Last weeks pattern we discussed was CQRS. This week we will dive in to Event Sourcing. Showing you how this doesn’t just solve a technical problem, they helps us solve our business problems!
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.
When we want to generate the AWR report, we usually run the awrrpt.sql script. We don’t need to be on the database server for that (more...)
After having looked at the performance consistency provided by the Oracle Database Cloud offering in the previous series
, I'll focus here on the raw performance figures I've measured during my tests, starting with the CPU related performance findings.
One of the first surprises is related to the fact that Oracle uses a unit called "OCPU" to specify the CPU capacity provided, which is explained here:
So one "OCPU" is supposed to represent one core (more...)
For people interested, here the slide desks used internally to debrief my colleagues regarding Oracle…
The internet is full of information about indexes, and for a reason. Indexes in a database is probably the most important performance related topic. There are so many cases, properties, and different ways to use indexes that there is simply a lot to write about. In this post I’d like to talk about a specific … Continue reading Scanning an Index