Top 60 Oracle Blogs

Recent comments

January 2017

An introduction to PCP / Performance Co Pilot on Oracle Linux

I was investigating gathering performance data on (oracle) linux servers recently and came across Performance Co-Pilot (PCP). I have come across this product regularly in the past, but it seemed somewhat abstract to me, and I never ran into any actual usage. And we got sar for linux performance data and for the Oracle database we got oswatcher (and it’s exadata cousin exawatcher) and TFA right? How wrong I was.

First let me explain a few things.

Creating a RAC 12.1 Data Guard Physical Standby environment (2)

In the first part of this mini-series you saw me define the environment as well as creating a primary database. With that out of the way it’s time to think about the standby. Before the standby can be created, a few preparations are necessary both on the primary as well as the standby cluster.

NOTE: As always, this is just a demonstration using VMs in my lab, based on my notes. Your system is most likely different, so in real-life you might take a different approach. The techniques I am using here were suitable for me, and my own small scale testing. I tried to make sure they are valid, but you may want to allocate more resources in your environment. Test, test, test on your own environment on test kit first!

Preparing the Creation of the Standby Database

use_nl hint

In response to a recent lamentation from Richard Foote about the degree of ignorance regarding the clustering_factor of indexes I commented on the similar level of understanding of a specific hint syntax, namely use_nl(a b) pointing out that this does not mean “do a nested loop from a to b”. My comment was underscored by a fairly prompt response asking what the hint did mean.

Extending Flashback Data Archive in 12c

Flashback Data Archive (previously called Total Recall) has been around for a long time now. For those unfamiliar with it, (and by the way, if you are on Enterprise Edition, you should be familiar with it, because its a free feature), here is a very quick primer.

Create a tablespace to hold your history, and create a flashback archive using that space allocation.

Two New Oracle Security Public Class Dates

I will be teaching two of my Oracle Security classes with Oracle University soon. The first is my class "Securing and Locking Down Oracle Databases". This class will be taught on the 24th January on-line via the Oracle LVC platform....[Read More]

Posted by Pete On 12/01/17 At 02:47 PM

Identifying Redundant Indexes in PeopleSoft

This is the first of a two-part series that discusses how to identify and remove redundant indexes from a PeopleSoft system.
I recently wrote a series of articles on my Oracle blog about identifying redundant indexes on an Oracle database. The same approach can be taken with PeopleSoft, but using the metadata in the PeopleTools tables rather than the database catalogue.
(This means you could take a similar approach for PeopleSoft on databases other than Oracle, but I can't say anything about the behaviour of other optimizers) 
The following query returns a report of superset and redundant subset indexes in a PeopleSoft system. It can be downloaded as a script from my website (psredundant.sql).

The Most Important Tool for SQL Tuning

Summary: SQLT is a tool that collects comprehensive information on all aspects of a SQL performance problem. SQL tuning experts know that EXPLAIN PLAN is only the proverbial tip of the iceberg but the fact is not well recognized by the Oracle database community, so much evangelization is necessary.

I remember the time I was trying to solve a production problem a long time ago. I did not have any tools but I was good at writing queries against the Oracle data dictionary. How does one find the PID of an Oracle dedicated server process? Try something like this:

select spid from v$process where addr=(select saddr from v$session where sid = '&sid')

My boss was not amused.

After the incident, he got me a license for Toad.

Writing queries against the data dictionary is macho but it is not efficient.

Tools are in.

Auto Sync for Password Files in #Oracle 12c Data Guard

sync 600w,

Join Elimination 12.2

From time to time someone comes up with the question about whether or not the order of tables in the from clause of a SQL statement should make a difference to execution plans and performance. Broadly speaking the answer is no, although there are a couple of boundary cases were a difference can appear unexpectedly.