I've been operating with a 12c environment this year and I can see some standard patterns of mine changing.
One is the use of triggers, or lack thereof. I live in a city with a certain evangelist who does not like triggers
, so I was happy to see Sven Weller's "perfect trigger
" post. The answer is: there is no trigger.
Well, it's one thing to say 'create all new tables like this', (more...)
This is the fourth part of this installment, comparing the performance consistency of the DBaaS cloud offering with a dedicated physical host. This time the previous read-only test was modified to be a 100% update read-write test. So every row read was updated in the following way:begin loop for rec in ( select /*+ index(t_o) (more...)
This post is about performance optimizations introduced in Apache Spark 2.0, in particular whole-stage code generation. A test case is introduced and investigated with diagnostic tools.Introduction: performance troubleshooting of a slow query using parallel query execution in a Hadoop cluster
The idea for this post comes from a performance
troubleshooting case that has come up recently on our database services. It started with a user reporting slow response time from a query (more...)
Logging is a very useful feature of WebLogic.
Unfortunately the log levels, which are set after a clean install of WebLogic or some of the Fusion Middleware product creates a lot of noise and therefore it costs I/O performance.
Additional after analyzing an issue with logging often resetting the log level is forgotten.
Here you get a script to reset the log levels at regular intervals or after a trace session.
Here is another example (besides the fact that Adaptive Cursor Sharing only gets evaluated during a PARSE call
(still valid in 12c) and supports a maximum of 14 bind variables) I've recently come across at a client site where the default implementation of Adaptive Cursor Sharing fails to create a more suitable execution plan for different bind variable values.Broken down to a bare minimum the query was sometimes executed using non-existing values
for a (more...)
Having done my mini-series on Nested Loop join logical I/O optimizations a while ago I unfortunately never managed to publish anything regarding the Nested Loop join physical I/O
optimizations, which are certainly much more relevant to real-life performance.Therefore the main purpose of this blog post is to point you to Nikolay Savvinov's (whose blog I can recommend in general) great mini-series covering various aspects of these optimizations:Part 1Part 2Part 3Summary (more...)
This is the second part of this installment, comparing the performance consistency of the DBaaS cloud offering with a dedicated physical host. This time instead of burning CPU using a trivial PL/SQL loop (see part 1
) the test harness executes a SQL statement that performs logical I/O only, so no physical I/O involved.
In order to achieve that a variation of Jonathan Lewis' good old "kill_cpu" script got executed. In principle each thread performed (more...)
Prompted by a (not really that) recent discussion on the OTN
forum I've decided to publish this note.Sometimes you have the task of comparing column values and handling the NULL value cases correctly makes this rather cumbersome for columns that are allowed to be NULL.The "official" SQL way of comparing two column values and to find out whether they are equal or not - under the assumption that having NULL in both columns (more...)
A new version 4.23 of the XPLAN_ASH utility is available for download.As usual the latest version can be downloaded here.
This version comes only with minor changes, see the change log below.
Here are the notes from the change log:
- Finally corrected the very old and wrong description of "wait times" in the script comments, where it was talking about "in-flight" wait events but that is not correct. ASH performs a "fix-up" (more...)
Oracle 12c Unified Auditing is a brand new feature in the latest Oracle version which consolidates database level auditing records into a single location. DBAs can access the audit information from the view UNIFIED_AUDIT_TRAIL for all kind audit records, and they are
SQL> select distinct COMPONENT from all_unified_audit_actions;
Direct path API
8 rows selected.
UNIFIED_AUDIT_TRAIL is a view owned by SYS and a public (more...)
I wanted to go to Hotsos Symposium for quite some time, having heard so many great topics from there for years. And every time I was a bit lazy to think about what I can talk about. Apparently I thought that everything I know is well covered elsewhere, so why would I be accepted. Plus all these complexities of budget, getting a visa, travel arrangements, jet lag, and personal matters in between. Last year, when (more...)
is a tool aimed at DBAs and Oracle performance
analysts. It provides a simplified interface to extract and visualize AWR
The reason for a tool like PerfSheet.js is to make the analysis of AWR data easier
by providing a graphical interactive interface and by automating several repetitive steps of data extraction and chart preparation. Pivot charts
provide a flexible and easy to use way to navigate (more...)
There is no question that massive data is being generated in greater volumes than ever before. Along with the traditional data set, new data sources as sensors, application logs, IOT devices, and social networks are adding to data growth. Unlike traditional ETL platforms like Informatica, ODI, DataStage that are largely proprietary commercial products, the majority of Big ETL platforms are powered by open source.
With many execution engines, customers are always curious about their usage (more...)
Oracle PARTIAL INDEXES – a new feature in Oracle 12c, initial impression was really good, but the implementation is quite poor. With this feature, you can define INDEXING OFF for a partition so that any index with PARTIAL clause will skip this partition while creating the index. The advantage is you can selectively create indexes on partitions. And for a global index or a global partitioned index, the rows from the INDEXING OFF partitions will (more...)
this post is about Linux perf and uprobes for tracing and profiling Oracle workloads for advanced troubleshooting.Context
The recent progress and maturity of some of the Linux dynamic tracing tools has raised interest in applying these techniques to Oracle troubleshooting and performance investigations. See Brendan Gregg
's web pages for summary and future developments on dynamic traces for Linux. Some recent work on applying these tools and techniques to Oracle can be found (more...)
Stack trace sampling is very powerful technique for performance troubleshooting. Advantages of stack trace sampling are
- it doesn't require upfront configuration
- cost added by sampling is small and controllable
- it is easy to compare analysis result from different experiments
Unfortunately, tools offered for stack trace analysis by stock profilers are very limited.
Solving performance problem in complex applications (a lot of business logic etc) is one of my regular challenges. Let's assume I have another (more...)
This post provides a short summary and pointers to previous work on Extended Stack Profiling for troubleshooting and performance investigations.Understanding
the workload is an important part of troubleshooting activities. We seek answers to questions like: what is the system doing, where is the time spent
, which code
paths are most used, what are the wait events, etc. Sometimes the relevant diagnostic data is easy to find, other times we need to dig (more...)
It is widely known that unindexed foreign keys can be performance issue. Unindexed foreign keys on child tables can cause table locks or performance problems in general.
There are many articles on this subject so I won't go in details.
My plan is to show simple demo case where empty child table with unindexed foreign key column can cause big problems.
Imagine that you have highly active table (supplier) with lots DML operations from many (more...)
Today here’s a shorter post about my experiments with Oracle SQL Developer’s user-defined reports: A report on all long running operations (“LongOps”) with details on session wait events, explain plans and live SQL monitoring. “Wait a minute”, you might say, “there’s already the session report in SQL Developer’s standard reports that shows Session_LongOps”! – and you’re […]