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...)

“log file sync” and the MTTR Advisor

I recently investigated a performance problem on an Oracle 11.2 OLTP trading system and although we still don’t fully understand the issue (and which versions of Oracle it effects), I thought I would share what we found (and how we found it). We had a hardware failure on the database server, within 30 seconds the database had automatically been restarted on an idle identical member of the cluster and the application continued on the (more...)

Autotrace

Happy New Year!

 

Oracle supplied various tools to trace the SQL and identify execution plans for the SQLs.  10046 Event, AUTOTRACE, DBMS.XPLAN etc are some of the most used tracing methods in DBAs daily life.  Sometimes, we need to be very careful while using these tools specially using bind variables.  Following are some test cases where wrong plans reported by the above tracing tools.

 

Most easiest method to get (more...)

New Version Of XPLAN_ASH Utility

A new version 4.2 of the XPLAN_ASH utility is available for download.

As usual the latest version can be downloaded here.

There were no too significant changes in this release, mainly some new sections related to I/O figures were added.

One thing to note is that some of the sections in recent releases may require a linesize larger than 700, so the script's settings have been changed to 800. If you use corresponding settings (more...)

Oracle database operating system memory allocation management for PGA – part 2: Oracle 11.2

This is the second part of a series of blogpost on Oracle database PGA usage. See the first part here. The first part described SGA and PGA usage, their distinction (SGA being static, PGA being variable), the problem (no limitation for PGA allocations outside of sort, hash and bitmap memory), a resolution for Oracle 12 (PGA_AGGREGATE_LIMIT), and some specifics about that (it doesn’t look like a very hard limit).

But this leaves out Oracle version (more...)

ADF Mythbusters UKOUG’14

I would like to post the slides from our recent session on UKOUG'14 conference - ADF Mythbusters. This session was presented by my colleague from Red Samurai Consulting - Florin Marcus. The goal was to break popular ADF myths. We have logged Oracle Support SR's after the session, each myth in the slide is assigned with SR number.

Slides are available on SlideShare:


The following topics are covered in the (more...)

Data Pump and AQ_TM_PROCESSES

First things first: Thou shalt not explicitly set AQ_TM_PROCESSES=0 ! Unless, of course, you want to disable the Queue Manager Process (QMNC in Oracle 11.x). Which you may want to do during database upgrades to prevent Streams or Advanced Queueing from interfering with the upgrade process. However, if you don’t reset this parameter afterwards, you might run […]

Boosting APEX menu SQL performance

If you're using dynamic SQL to source your menus and you're utilising the apex_application_pages dictionary view to build you list of menu options - I suggest you consider using Materialized Views.

We have a reporting application that does just this; and the query also links to an authorisation table that decides what types of users see which reports.

You can create a snapshot (materialized view) using something as simple as
create materialized view apx_application_pages
as (more...)

APPEND and PARALLEL

APPEND hint in oracle is very useful, there are many benefits

Fragmented data loading – data will clustered above the HWM so that similar data will be together. Otherwise Oracle will place the records in blocks using PCT_FREE rule

  • No undo will be generated – in large direct path insert will free up large amount of data blocks
  • Less CBC latches (because of no UNDO blocks), less CPU usage
  • Readers need not undo the records, (more...)

Life of an Oracle I/O: tracing logical and physical I/O with systemtap

Topic: This post is about tracing logical and physical reads in Oracle using systemtap. You will find a few examples illustrating the main mechanisms used by Oracle to do physical and logical I/O and you will learn how to build systemtap scripts to further explore and troubleshoot Oracle I/O.

Introduction: Performing input/output to and from the storage media, i.e. physical I/O, is one of the critical tasks underlying all database engines. When accessing data (more...)

HPC versus HDFS: Scientific versus Social

There have been rumblings from the HPC community indicating a general suspicion of and disdain for Big Data technology which would lead one to believe that whatever Google, Facebook and Twitter do with their supercomputers is not important enough to warrant seriousness—that social supercomputing is simply not worthy.  A little of this emotion seems to […]

Oracle Real World Performance

Great videos on Oracle Real World Performance.


RAC, 12c and Direct Path Reads

Yes, direct path reads again :) No worries I’m already a bit bored from digging in this algorithm, so I think this is going to be my last post about it. Till they change it again of course. And yes, in 12.1.0.2 they did. For introduction to DPR, you can check my previous post, because […]

SystemTap into Oracle for Fun and Profit

Topic: This post is about using SystemTap for investigating and troubleshooting Oracle RDBMS. In particular you will learn how to probe Oracle processes and their userspace functions. These techniques aim to be useful as well as fun to learn for those keen into peeking under the hood of the technology and improve their effectiveness in troubleshooting and performance investigations.


Introduction


Userspace probing is a very powerful technique that can be used to complement the available (more...)

JDeveloper 12c ADF View Token Performance Improvement

There is known limitation in ADF 11g, related to accessing application in the same session from multiple browser tabs. While working with multiple browser tabs, eventually user is going to consume all view tokens, he will get timeout error once he returns back to the previous browser tab. Unused browser tab is producing timeout, because ADF 11g is sharing the same cache of view tokens for all browser tabs in the same session. This means (more...)

12c HCC Row-Level Locking

In Oracle Database 12c we can find many new and shiny things… So many that we can miss the little good things really easy. I think that this one, is one of them. Previously I made a post “All About HCC“, describing how HCC is working and some of the issues that we can hit […]

The cost of using an index

When trying to understand why the optimiser might choose not to use an index to read rows from a table, I find the following diagram helpful.

index cost

The axis along the bottom represents the selectivity of the query (0% being no rows selected, 100% being all rows selected), and the axis up the side represents the cost of the chosen operation (more costly = more work to do).

When reading from a table without using an (more...)

Deadlocks due to unindexed foreign keys

Here is a step-by-step analysis of the deadlock which occurs due to unindexed foreign keys. This analysis was performed on Oracle XE 11.2.0.2 – results may vary on other versions.

A typical deadlock graph arising from an unindexed foreign key is as follows.

                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TM-0000508a-00000000        27     101    SX             28      12         SSX
TX-00090013-0000019b        28      12     X             27     101           S

This type of (more...)