Skip to content

Expert Oracle Posts

Performance Tuning Basics 13 : Automatic Workload Repository (AWR) Basics

Published January 23, 2018 By Brijesh Gogia

The Automatic Workload Repository (AWR) automatically persists the cumulative and delta values for most of the statistics at all levels except the session level.

This process is repeated on a regular time period and the result is called an AWR snapshot. The delta values captured by the snapshot represent the changes for each statistic over the time period.

The Automatic Workload Repository (AWR) collects, processes, and maintains performance statistics for problem detection and self-tuning purposes. This data is both in memory and stored in the database.

Continue ReadingPerformance Tuning Basics 13 : Automatic Workload Repository (AWR) Basics
Leave a Comment

Performance Tuning Basics 12 : Dynamic Performance Views

Published January 8, 2018 By Brijesh Gogia

You can utilize Oracle Database dynamic performance views to query important tuning related parameters like memory utilization, Disk I/O, Disk structure etc. These dynamic performance views are special views that are continuously updated while a database is open and in use.

Note that the actual dynamic performance views are identified by the prefix V_$. Public synonyms for these views have the prefix V$. You should access only the V$ objects, not the V_$ objects.

Continue ReadingPerformance Tuning Basics 12 : Dynamic Performance Views
Leave a Comment

SYSADMIN login performance issue in Oracle EBS R12

Published January 4, 2018 By Brijesh Gogia

In our EBS system, we found that SYSADMIN login was slow. It was taking around 4-5 minutes to login and when inside the EBS application it was taking 2-3 minutes every time we were navigating in the self-service pages. This issue was not occurring for normal EBS users. We thought of applying the trace to SYSADMIN user to find the root cause of it.

Continue ReadingSYSADMIN login performance issue in Oracle EBS R12
2 Comments

Oracle EBS R12 and TDE Tablespace Encryption

Published December 21, 2017 By Brijesh Gogia

Keeping data secure is one of the primary job of DBA. Oracle’s Transparent Data Encryption Tablespace Encryption option of Oracle Database 11g Enterprise Edition provides an excellent method to encrypt data at rest. This option works perfectly with Oracle EBS R12.

The TDE Tablespace Encryption option can be used to encrypt the tablespaces that store the content of your E-Business Suite application so you can encrypt the database files and any backups of these files. Good thing about TDE is you do not have to change any of your existing code as TDE is transparent to application.

Continue ReadingOracle EBS R12 and TDE Tablespace Encryption
3 Comments

Performance Tuning Basics 11 : Steps to analyze a performance problem

Published December 7, 2017 By Brijesh Gogia

A well-planned methodology is the key to success in performance tuning. Without a proper diagnosis of the issue any troubleshooting step taken will further delay the resolution. Example, a DBA can not just blindly start tracing/tuning a SQL/Sessions when all symptoms on careful analysis points towards issues with DIsk/Memory/Network etc.

Continue ReadingPerformance Tuning Basics 11 : Steps to analyze a performance problem
2 Comments

Performance Tuning Basics 10 : Histograms

Published November 28, 2017 By Brijesh Gogia

Histogram helps Oracle Optimizer in generating a better execution plan specially when the data is highly skewed in the table. You can say a histogram is an optional special type of column statistic data that provides more detailed information about the actual data distribution in a table column.

Before going into Oracle Histograms details, let us understand what it Histogram in simple language.

Continue ReadingPerformance Tuning Basics 10 : Histograms
2 Comments

Performance Tuning Basics 9 : Optimizer Mode

Published November 25, 2017 By Brijesh Gogia

Oracle’s Cost based optimizer OPTIMIZER_MODE establishes the default behavior for choosing an optimization approach. Choosing the right mode setting can improve many times the average performance of your queries. Optimizer is the mind of the Oracle engine which takes into account many factors like indexes, statistics etc and decide the optimum path to fetch the data and return back to the user session. Oracle provides some parameters which if tweaked can make Optimizer mode to go alternate ways which can be beneficial to your SQL queries. optimizer_mode database parameter is one such useful parameters.

Continue ReadingPerformance Tuning Basics 9 : Optimizer Mode
2 Comments

Performance Tuning Basics 8 : Trace File Analyzer (TRCA)

Published November 24, 2017 By Brijesh Gogia

Just like TKPROF, Trace Analyzer (TRCA) tool can help you analyze one or several SQL trace(s) generated by Event 10046 to produce an output diagnostics report which can be used to diagnose a poorly performing SQ statement. TRCA can produce output  in two formats (html and text).

TRCA identifies expensive SQL, generates their explain plan and collects some other useful information like Cost-based Optimizer CBO statistics, metadata, configuration parameters, and similar elements that influence the query performance.

This post is part 4 of the below posts on trace and tkprof. Please refer other posts to read related information.

Part 1: Trace and the methods to generate trace
Part 2: Generating TKPROF from trace file
Part 3: Analyzing TKPROF files
Part 4: Using Trace Analyzer (TRCANLZR) Tool

Continue ReadingPerformance Tuning Basics 8 : Trace File Analyzer (TRCA)
Leave a Comment