All_source is slow

A customer is preparing to move an application from 11g to 12c. A key function in the application is to detect new tasks, I do not have the details how it works but the function is executed every minute. The sql listed below is part of that function. In 11g this sql is very fast, less than a second. In 12.2 it takes much longer I have seen from 17 to 46 seconds to run.

Continue reading

Advertisements

Enq TX for a long time.

I’m helping a customer that experienced a scenario where some functions in the application stopped responding. The situation was seen for 48 hours. It has also been seen pretty frequent in the past months.
When approaching the vendor of the application the response was that the session was not receiving data from the database which implied it was a database issue. A reference to a blog by Oracle is used to confirm it to be a database issue. Looking at the details and reading the blog I agree that the conclusion that the jdbc session is stuck waiting for data from the database, but the reason for not receiving data are not database related. I think it is clearly a design issue related to the application. Here are my analyze…

Continue reading

Free Buffer Waits

I have been working on an application for some time now, the plan is to move it to Oracle 12c. We started with 12.1 but as soon as 12.2 was released we focused on using 12.2. We also applied the last bundle patch. This is a test system that uses the same data over and over so no new data are added.

A little background: The application has a job running every hour calculating prices, in 12.1 when I was first involved the execution time was around 90 minutes.
In 11.2 which is the current version each calculation took around 10 minutes.
My first steps was to revert all parameter changes used in 11g and basically starting with default values.

Continue reading

enq: TX – row lock contention

I received a case where a developer had a session that hung waiting on  enq: TX – row lock contention. It was several things that puzzled me, he claims he is the only one running on this database and the row lock was related to an insert.

insert into PRODUCT (ID, VIN, CHASSISSERIES, CHASSISNO, MODIFICATIONTIME, CREATIONTIME) values (:1 , :2 , :3 , :4 , :5 , :6 )

I found this a bit odd at first. I relate enq: TX row lock contention with updates and deletes, not inserts. I want to analyze this further.

Continue reading

Group Column Statistics (Part 2)

As described in Part 1 Oracle gathers extended statistics automatically on column groups. This was also available in 11g but here you had to add it manually.

How can this become a performance issue in our 12c database ? If you read my post about   our performance issues related to this you know that we spent a lot of time hard parsing and that we  saw a specific sql on the top list for parsing.

Continue reading

Group column statistics (part 1)

So group column statistics, what is it ? it is information about relationship between columns in the same table. There are numerous blogs about this. One I read is the Oracle Optimizer Blog explaining the details. This is from 11g when you had to create it manually.
From 12.1.0.2 it is done on the fly, which can cause issue. See my previous post about my experiences. This post is a spin off from that post explaining the details a bit more.

Continue reading