Visualizing Statspack Performance Data in SQL Developer

If you run Oracle Standard Edition or haven’t licenced Diagnostics Pack for Enterprise Edition, then you don’t have AWR and ASH Data available. This is when Statspack, the predecessor of AWR, comes in handy to keep a history of database performance metrics. But although Oracle still deliver Statspack with their recent DB releases (yes, even in […]

Extra-cost Option?


"Just because you have a choice, it doesn't mean that any of them 'has' to be right."
 Norton Juster, The Phantom Tollbooth 

In an Oracle forum that I frequent a user posted that he found cardinality values to be severely inflated when using an analytic function. This user posted an example that will be used here to demonstrate the problem. It will also be used to demonstrate another issue that can make this problem (more...)

Tablespace growth history

Recently we had a tablespace space run out and ended up in an application failure.  I have questioned my DBA and he just plainly blamed the application team members who loaded large number records without a prior notice.  A convincing answer, but you can’t really fool Oracle.

Oracle introduced a new DBA_TABLESPACE_USAGE_METRICS view from 10g onwards to report the space usage with in a tablespace.  I created a new tablespace and immediately (more...)

Invoking REST Service from Oracle ACM Java Activity

In this post I will show you, how to call REST service from ACM Java activity class method. This could be useful in the situations, when you would like to have programmatic ACM activity integrated with REST service data. We could access ACM payload data from within the method overriden in the class implementing Case Activity Callback.

ACM activity implemented on top of Java class, contains the same properties and configuration as the regular one. (more...)

x$bh and consistency

Oracle caches the data blocks in buffer cache in various modes depends on the block usage.  As per the Oracle documentation it can CR (Consistent mode – reads), XCUR (Current mode – updates), FREE etc.   I understand and other heard saying – whenever a block READs into memory will be in CR mode while if the block is fetching for UPDATE it will be in XCUR mode so that sessions can apply the (more...)

Developer Interview (#DI15) with Niko Köbler (@dasniko) Java EE and Node.js Performance

Developer interview Friday. Today it is my pleasure to welcome Niko Köbler (@dasniko). We meet a couple of times before and I love following his ramblings about JavaScript on the server side. I talked to him about why and how he came in contact with server side JavaScript in general and made him show a little more about his recent performance test, that he ran against RESTFUL backends (WIldFly and node.js). For (more...)

Merge, Right?


"There are no wrong roads to anywhere."
 - Norton Juster, The Phantom Tollbooth 

Oracle can do some, well, strange things when fixing issues caused by the optimizer. For 10g releases up to 10.2.0.x Oracle chose to silently ignore a message and eliminate an outer join on the MERGE statement under certain conditions. Let’s examine this and see exactly what Oracle implements.

Occasionally in a 10046 trace file generated from 10g releases (more...)

ADF BC Range Paging and REST Pagination

In this post I would like to explore and integrate two thing - ADF BC Range Paging and REST service pagination. It would inefficient to retrieve entire set of data in the REST service, ideally should be available option to specify number of rows and range number to fetch. ADF BC allows to query VO in Range Paging mode - SQL query will be constructed with row numbers, to query data in certain range of (more...)

Latest updates to PerfSheet4, a tool for Oracle AWR data mining and visualization

Topic: This post is about the latest updates to PerfSheet4 v3.7 (February 2015). PerfSheet4 is a tool aimed at DBAs and Oracle performance analysts. It provides a simplified interface to extract and visualize AWR time series data using Excel pivot charts.


Why PerfSheet4: PerfSheet4 is aimed at querying and displaying time-series data from AWR repository tables. This is very a rich source of information to analyze database workloads and trends in the context of performance analysis or (more...)

Binary search – is it still most optimal?

If you have a sorted collection of elements, how would you find index of specific value?
"Binary search" is likely to be your answer.
Algorithms theory is teaching us what binary search is most optimal algorithm for this task with log(N) complexity.
Well, hash table can do better, if you need to find key by exact match. In many cases, though, you have reasons to have your collection sorted, not hashed.

On my job, I'm (more...)

MAF 2.1 Alta Mobile UI and Oracle Mobile Suite

This post is about Alta Mobile UI and MAF 2.1. I was using Oracle Work Better mobile application as a reference, along with Alta Mobile UI Design Guidelines. This is based on my previous posts about Oracle Mobile Suite and MAF 2.1 integration, read more here - Oracle Mobile Suite - Web Service Performance Optimisation with Result Caching. You could also use Alta UI 12c with regular ADF Web applications, check more here (more...)

To N or Not to N, is SYS_OP_C2C the Question; Oracle NVARCHAR Slow?

February 8, 2015 I was recently contacted about an odd performance issue with a software product named TDM, where certain SQL statements submitted to an Oracle 9.2.x database required roughly 30 seconds to execute when a nearly instantaneous response was expected.  The person who reported the issue to me provided a sample SQL statement that […]

Oracle Mobile Suite – Web Service Performance Optimisation with Result Caching

One of the main advantages of Oracle Mobile Suite - Service Bus and SOAP/REST web service transformation (more here - Oracle Mobile Suite Service Bus REST and ADF BC SOAP). In addition you will get very nice performance improvement, there is out of the box caching for Web Service resultset with Coherence. I'm going to demonstrate how it works, all out of the box - really simple.

You could define caching for external service (more...)

Bind peeking and session cached cursor

Bind peeking is a nice feature in Oracle to have many optimized plans for an SQL for various bind values.  DBAs believe that bind peeking happens during a soft parse which will identify an alternate plan. Why do I say that?

 

Hard Parse: Parsing first time, nothing exists to bind peek

 

Soft Parse :  SQL cursor is existing and executing not the first time.  Under the soft parse, bind peeking (more...)

Blog update!

Hi everyone!

This blog hasn’t seen any updates since over a year now and I do apologize for that. However, the reason for that is simple. Last year I’ve decided to take one a new project. But before I was going to tell everybody about it I wanted to make sure that I also really got the time to do so. In the meantime I got the confidence that the project will succeed and that (more...)

New Version Of XPLAN_ASH Utility – In-Memory Support

A new version 4.21 of the XPLAN_ASH utility is available for download. I publish this version because it will be used in the recent video tutorials explaining the Active Session History functionality of the script.

As usual the latest version can be downloaded here.

This is mainly a maintenance release that fixes some incompatibilities of the 4.2 version with less recent versions (10.2 and 11.2.0.1).

As an extra however, (more...)

enq: TM – contention due to parallel DML and foreign keys

This is a write-up of an issue I recently posted to the OTN discussion forum (https://community.oracle.com/message/12798407). I thought the associated test case was useful in demonstrating the issue, so is captured here for future reference. There were some useful replies to the OTN post, confirming my suspicions.

The test was performed using Oracle Enterprise Edition 11.2.0.3.0 on Linux.

Problem statement

A parallel delete blocks insert into dependent (more...)

Complément : Analyse de performances via AWR Warehouse


Setup Streams Performance Advisor (UTL_SPADV) for #GoldenGate

With Oracle “merging” Oracle GoldenGate into Oracle Streams (or vise-versa), capturing statitics on the intergrated extract (capture) or integrated replicat (happy) will be needed.  In order to do this, the Streams Performance Advisor (UTL_SPADV) can be used.  Before using the Stream Performance Advisor, it needs to be configured under the Streams Administrator, i.e. Oracle GoldenGate user.  In my test lab, I use a user named GGATE for all my Oracle GoldenGate work.

Configure user (more...)

enq: SQ – contention

Yesterday, I have seen huge waits “enq SQ – contention’”  – in every snapshot there were thousands of waits.  But the fix was so simple!  Here is the root cause of the issue –

When you select from a sequence, the NEXTVAL generated from a the seq$ table if it is not cached.  If it is cached, it will be available in a memory structure and no need to generate the value (more...)