ORA-4031 Troubleshooting – Issue with durations in 11gR2

I recently had to troubleshoot an ORA-4031 issue at a client site. The issue reappeared 3 times within 2 months and only after escalating the SR to SEV1 and being quite persistent for an explanation, the second engineer attempting to solve the issue finally got it right.  Being curious, I digged into the trace files again to confirm and understand the issue here. This blog post describes the method to analyze the issue and (more...)

Why you should never put objects into the SYSTEM or SYSAUX tablespace

Just because you can doesn't mean you should

Creating an Oracle Database Docker image

Run your Oracle database inside a Docker container

Auto enabling APPROX_* function in the Oracle Database

With the releases of 12.1 and 12.2 of Oracle Database we have seen some new functions that perform approximate calculations. These include:

  • APPROX_COUNT_DISTINCT
  • APPROX_COUNT_DISTINCT_DETAIL
  • APPROX_COUNT_DISTINCT_AGG
  • APPROX_MEDIAN
  • APPROX_PERCENTILE
  • APPROX_PERCENTILE_DETAIL
  • APPROX_PERCENTILE_AGG

These functions can be used when approximate answers can be used instead of the exact answer. Yes can have many scenarios for these and particularly as we move into the big data world, the ability to process our data quickly is slightly more important (more...)

Birth of a user group conference…

Or, how post-conference blues hit.

Seeding

You don’t actually get to witness these kinds of things to often. Yes, there was the birth of POUG conference, the passionate work of Kamil Stawiarski and the people he gathered around him. He did an awesome job and pulled it off.
Why then is this so special? Well, first of all because it is my native conference. People that have contributed for many years, working closely together (more...)

ODA X4 – Migrating a database from ASM to ACFS

As promised ... Here's the post about migrating a database from ASM to ACFS on an ODA 4 - probably, this will work for newer ODA Servers as well

Given:

- Oracle Database Appliance machine, X4-<something>
- Software Release 12.1.2.7.0
- Prepared DB Software: 11.2.0.3, 11.2.0.4, 12.1.0.2

Story behind:

Task was, to create a new database of release 11.2.0.3 for (more...)

Reducing fetch time

Database query tuning is mostly about getting better plans. Mostly, but not always. Sometimes, the problem has nothing to do with the plan, and you might need to get a bit creative to find a solution. In this recent case a query was showing a decent performance when running from SQL Developer, but it took about 5 times longer to complete when running from R. The plan was the same, so I knew that it (more...)

Oracle Database Result Cache Troubleshooting

I was troubleshooting an issue where the Oracle Database Result Cache did not get used when the RESULT_CACHE hint was specified inside a subquery. As the main query has a sysdate inside, the RESULT_CACHE Hint can not be specified for the main query, but only for the subquery.

1
2
SELECT SQ.*, sysdate FROM
(SELECT * FROM tableX, ....) SQ;

When using “/*+ RESULT_CACHE */ inside of Subquery, result cache was not (more...)

Long-running INSERT

On one of the databases I’m looking after (11.2.0.4, 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...)

Problems with big SGAs (>200G) on Linux

I recently had an issue where a database with 240GB SGA (1 huge shared memory segment) configured with hugepages on a system with 512G RAM was suddenly becoming instable and new logons were denied with these error message:


ORA-01034: ORACLE not available
ORA-27123: unable to attach to shared memory segment
Linux-x86_64 Error: 22: Invalid argument
Additional information: 2667
Additional information: 1736718
Additional information: 215016800256

This was strange because ipcs -a showed all shared memory segments (more...)

#DB12c feature – Secure External Procedures with DBMS_CREDENTIAL

Oracle Database 12c enables enhanced security for extproc by authenticating it against a user-supplied credential. This new feature allows the creation of a user credential and links it with a PL/SQL library object. Whenever an application calls an external procedure, the extproc process authenticates the connection before loading the shared library. The DBMS_CREDENTIAL package is … Continue reading

Adding Log Groups on Oracle DataGuard Database

When adding Redo logs to a database which has a DataGuard associated with it, the log files have to manually be added to the destination. Below are the steps on how to add them to both the source and the DataGuard databases. Adding Log Groups on Oracle DataGuard Database On the source first determine the […]

The post Adding Log Groups on Oracle DataGuard Database appeared first on VitalSoftTech.

Want to SPEED Up Your Database Tasks? DBMS_PARALLEL_EXECUTE to the rescue!

Use DBMS_PARALLEL_EXECUTE to divide one huge task into multiple small tasks that can be executed at the same time. See how many ways are there to divide.

The post Want to SPEED Up Your Database Tasks? DBMS_PARALLEL_EXECUTE to the rescue! appeared first on VitalSoftTech.

What Is Oracle Elapsed Time And Wall Time With A Parallelism Twist

This page has been permanently moved. Please CLICK HERE to be redirected.

Thanks, Craig.


What Is Oracle Elapsed Time And Wall Time With A Parallelism Twist


In this post I'm focusing on Oracle Database SQL elapsed time, adding parallelism into the mix and then revisiting wall time. What initially seems simple can take some very interesting twists!

If you are into tuning Oracle Database systems, you care about time. And if you care about time, (more...)

Watch Oracle DB Session Activity With The Real-Time Session Sampler

This page has been permanently moved. Please CLICK HERE to be redirected.

Thanks, Craig.

Watch Oracle DB Session Activity With My Real-Time Session Sampler


Watching session activity is a great way to diagnose and learn about Oracle Database tuning. There are many approaches to this. I wanted something simple, useful, modifiable, no Oracle licensing
issues and that I could give away. The result is what I call the Oracle Real-Time Session Sampler (OSM: rss.sql).

(more...)

The LGWR Three Second Rule. Really?

Does the Oracle Database 12c Log Writer Really Sleep For Three Seconds?

I have learned the part of Oracle Database performance tuning is checking if what you have been taught is actually true. What I'm writing about today has been done before in Oracle Database 10g and 11g, but I wanted to document this using 12c.

When I was first learning about the Oracle Database process architecture, the instructor said there are a number of (more...)

Rotate 11G XE database and listener logs

Rotate 11G XE database and listener logs on Linux using logrotate

Oracle Restart – a 11g New Feature

Oracle Restart is a new feature introduced to enhance the availability of Oracle database. I wasn't aware of this till recently. It basically allows various components of Oracle to restart automatically in a stand-alone (non-clustered) installation. This functionality is similar to what Clusterware does in a RAC setup. On Windows and Linux, we were able to achieve all this via creating a service or writing a script. But now it’s not required. So in a way, it also makes the age old practice of writing scripts to restart redundant.

Oracle instances and its dependent components restart automatically after any hardware (more...)

Oracle Database on Amazon RDS

Recently, Amazon and Oracle announced that they are going to make "Oracle 11g Database" available on Amazon AWS as a service. It's being brought into AWS's RDS feature which currently offers "MySQL database as a service".

Amazon RDS is a web service that allows you to set up, operate, and scale a relational database in the cloud. You can provision a relational database (currently only MySQL) on RDS in just a few minutes. Amazon RDS will also manage database administration tasks including continuous backups, software patching etc.
When launched (sometime in Q2 of 2011), you will have the option to (more...)