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...)
The intention of this blogpost is to show the Oracle wait time granularity and the Oracle database time measurement granularity. One of the reasons for doing this, is the Oracle database switched from using the function gettimeofday() up to version 11.2 to clock_gettime() to measure time.
This switch is understandable, as gettimeofday() is a best guess of the kernel of the wall clock time, while clock_gettime(CLOCK_MONOTONIC,…) is an monotonic increasing timer, which means it (more...)
This is the second blogpost on using PL/SQL inside SQL. If you landed on this page and have not read the first part, click this link and read that first. I gotten some reactions on the first article, of which one was: how does this look like with ‘pragma udf’ in the function?
Pragma udf is a way to speed up using PL/SQL functions in (user defined function), starting from version 12. If you want (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...)
Whenever you use PL/SQL in SQL statements, the Oracle engine needs to switch from doing SQL to doing PL/SQL, and switch back after it is done. Generally, this is called a “context switch”. This is an example of that:
-- A function that uses PL/SQL
create or replace function add_one( value number ) return number is
l_value number(10):= value;
-- A SQL statement that uses the PL/SQL function
select sum(add_one(id)) (more...)
It's probably not that well known that Oracle can actually rollback / re-start the execution of a DML statement should the cursor become invalidated. By rollback / re-start I mean that Oracle actually performs a statement level rollback (so any modification already performed by that statement until that point gets rolled back), performs another optimization phase
of the statement on re-start (due to the invalidation) and begins the execution of the statement from scratch. Note (more...)
There’s been a lot of work in the area of profiling. One of the things I have recently fallen in love with is Brendan Gregg’s flamegraphs. I work mainly on Linux, which means I use perf for generating stack traces. Luca Canali put a lot of effort in generating extended stack profiling methods, including kernel (only) stack traces and CPU state, reading the wait interface via direct SGA reading and kernel stack traces and getting (more...)
"The only thing you can do easily is be wrong, and that's hardly worth the effort."
Norton Juster, The Phantom Tollbooth
Oracle can lie to you. Not like a disreputable uwed-car salesman but more like the ‘little white lie’ sometimes told in order to hide less-than-desirable parts of the truth. And it’s not Oracle, really, it’s the optimizer and it does it by reporting query plans that may not accurtely report the execution path. (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...)
A new version 4.22 of the XPLAN_ASH utility is available for download.As usual the latest version can be downloaded here.
This version primarily addresses an issue with 12c - if the HIST mode got used to pull ASH information from AWR in 12c it turned out that Oracle forgot to add the new "DELTA_READ_MEM_BYTES" columns to DBA_HIST_ACTIVE_SESS_HISTORY - although it got officially added to V$ACTIVE_SESSION_HISTORY in 12c. So now I had to implement (more...)
This post is about manually calling and freeing a shared latch. Credits should go to Andrey Nikolaev, who has this covered in his presentation which was presented at UKOUG Tech 15. I am very sorry to see I did miss it.
Essentially, if you follow my Oracle 12 and shared latches part 2 blogpost, which is about shared latches, I showed how to get a shared latch:
SQL> oradebug setmypid
SQL> oradebug (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 […]
this post is about investigating Oracle wait events using stack profiles and flame graphs extended with OS-process state and Oracle wait event details.Context: The case of the DB Time > CPU Time + Wait Time
provides wait event and CPU time accounting, a powerful and readily accessible data source for performance troubleshooting. An Oracle session at a given point in time is either on CPU, for example when processing data from cache, (more...)
This article is about the Oracle 12c in-memory option, and specifically looks at how the background worker processes do IO to populate the in-memory column store.
Hardware: Apple Macbook with VMWare Fusion 7.1.3.
Operating system: Oracle Linux 6.7, kernel: 3.8.13-118.el6uek.x86_64.
Database version: Oracle 188.8.131.52
Patch: opatch lspatches
19392604;OCW PATCH SET UPDATE : 184.108.40.206.1 (19392604)
19303936;Database Patch Set Update : 12.1. (more...)
While preparing to teach a class this week I have had the time to look into the In Memory (cost) option a bit closer. I noticed a few interesting things along the way and would like to share one of these here.
One of the questions I was asking myself was:
“What happens if I scan segments that are within the IM area, and some are not?”
I was primarily thinking of joins in (more...)
In my last post I demonstrated an example how to use a PL/SQL package to monitor a workload in order to identify correlated columns. Such columns are good candidates for the creation of extended statistics since the optimiser does not assume that information stored in different columns may be related. Before starting my tests I invoked DBMS_STATS.SEED_COL_USAGE, which in turn increased Oracle’s attention level trying to find correlated columns. Eventually these have been identified (more...)
This blog post is about kernel stack profiling and visualization with flame graphs with examples.
Stack profiling and flame graphs
are very useful techniques and tools for troubleshooting and investigating workloads at the OS-level and understand which code path take most of the execution time. You can find extensive material and examples o flame graphs in Brendan Gregg
's website and blog. A few additional examples of using stack tracing and flame graphs for (more...)