21. 10. 2019.

Performing stress test against Oracle database that is running in a Cloud

Today majority of enterprises are moving or at least considering moving some load into the Cloud. What you need to be aware in such case, system requirements for a Cloud load are quite different when comparing with on-premises, mainly due to many new moving parts that are not present within classical environment such as sharing […]

07. 10. 2019.

Installing Oracle database on Amazon AWS

Although you can use AWS RDS service which offers PaaS architectural model of running Oracle database on top of AWS Cloud, due to many limitations (installation type, options selected etc.), many enterprises still prefer using IaaS model, where such restrictions does not exist (e.g. you can install Enterprise Edition of Oracle database, RAC, Data Guard […]

05. 09. 2019.

Reducing CPU consumption and Oracle licenses by fixing context switching in Oracle Db

Context switching is one of the most overlooked topics when tuning the Oracle database, as you can’t simply see it directly (e.g. “context switching” wait event doesn’t exist). Instead you should use indirect approach (through the increased CPU consumption), as it’s recorded as a CPU wait event. Context switching what it is and what impact […]

23. 08. 2019.

Oracle single-threaded CPU performance reference test, native compilation and performance improvement after upgrading your database

In this article I’ll provide a very simple way of how to test Oracle CPU single-thread performance.   Apart from that, I’ll cover performance boost you might expect when upgrading your server (especially CPU), expected performance boost when performing simple upgrade of the Oracle database, and issues and workarounds related to native compilation of PL/SQL […]

22. 07. 2019.

Oracle database benchmarking by using CALIBRATE_IO

CALIBRATE_IO is yet another popular database simulation/stress test utility, mainly used to perform IO benchmarks.   Procedure, that is part of DBMS_RESOURCE_MANAGER package, will generate read-only workload made up of 1 MB of random of I/Os to the database to determine the maximum number of IOPS and MB per second. You can save the following […]

04. 07. 2019.

When to use Manual Memory Management in Oracle

In this post I’ll explain one case when Manual memory management is superior over letting Oracle to automatically manage PGA memory. Recently I need to create a test table with 10 million rows by using CONNECT BY clause. When I issue the following DDL command: I’m getting the following error: PGA parameters where set to […]

17. 05. 2019.

How to change the execution plan without changing the code in Oracle 10g

Recently I had an interesting case when I’ve been asked how to change the execution plan of one complex query without changing the code. I’ve already had a lecture with similar title at 2012 in Oracle User Group Conference – more details you can find at the following link: How to change the execution plan […]

19. 02. 2019.

Bulk collect and memory limits

We all know that for bulk collect PL/SQL operation it’s important to keep the memory consumption under control. For that reason all bulk collect operations should be combined with a LIMIT clause like in the following code fragment: Setting the limit to process only 10K records in bulk is does not impress me in a […]

11. 02. 2019.

Performance comparison: Python & cx_Oracle versus SQL*Plus & SQL*Net

In this article I want to check the truth about the slowness of Python language when it comes to retrieving data directly from Oracle database. Second goal is to show graphically impact of changing the array size on performance, which is not possible and convenient to do with SQL*Plus.   Python is widely used as […]

10. 02. 2019.

Impact of the parameter arraysize on performance

Main goal of this article is to explain impact of the parameter arraysize on database performance. First it’s important to define what array size is. The array size is the number of rows fetched by the server. I’m using Oracle’s SH demo schema, and it’s SALES table which has almost 1 million rows. In the […]