Amazon Deals

Tuesday, March 11, 2014

Urge to Purge Part 3

Another area to help identify tables that can be purged is for high number of similar tables. The query below will identify tables with similar names, note that you may need to adjust the numbers to better identify such tables. Some examples of cases like this from Oracle's E-Business Suite are listed out below.

The query to use:
select substr(table_name, 1, 15), count(1)
from dba_tables
group by substr(table_name, 1, 15) having count(1) > 30
order by 2 desc;

In Oracle E-Business Suite there are several cases where interim and temp tables are not being cleaned out by the application. Below are a few cases that depend on the module used and the current release of the applications.

There are a couple of groups of temporary tables that have not been cleaned out by Oracle. These tables can be manually removed, but review notes indicated below to check that. The tables may not be causing any significant issues, they are just consuming space on the system. These objects can also slow down gathering object level statistics if there are a large number of them.

A.            FA_JOURNALS_INTERIM_xxx
Review note FA_JOURNALS_INTERIM Tables are Not Dropped after Successful Create Journal Entry and Journal Import (Doc ID 757666.1)

B.            GL_CONS_INTERFACE_xxxx
Review note Many Interim Tables and Indexes are filling up the GL and GL_NDX tablespaces (Doc ID 287534.1)

C.            GL_POSTING_INTERIM_xxx
Review note GL_POSTING_INTERIM_xxxx Tables are Not Being Dropped after Successful Posting (Doc ID 364040.1)

D.            XLA_GLT_xxx tables not being cleaned up by oracle
Review note XLA_GLT_XXXX Temporary Tables For Purge Program 'Purge Temporary Journal Import Interface Tables' Not Dropped (Doc ID 1158934.1)

Tuesday, August 27, 2013

Tuning Advice - Be Sure to Check the Bind Variables

An occasional problem I encounter is with queries doing too much work due to the bind variables used. When looking at queries performing a high number of reads, part of the investigation should include a review of all of the bind variables passed into the query.

Recall from older posts that the top queries can be identified in AWR/Statspack reports or by running a query against the data dictionary such as:

select buffer_gets, elapsed_time,cpu_time,executions,sql_id,sql_text
from (select buffer_gets, elapsed_time,cpu_time,executions,sql_id,sql_text, rank() over
(order by buffer_gets desc
) as rank from v$sqlstats
where last_active_time > (sysdate - 1/12)
) where rank <=20;

A trace with binds and waits will give you information about the bind variables that were used for a query. You can also query v$sql_bind_capture. If you have the appropriate license you can query dba_hist_sqlbind to get the bind variables for older queries.

select child_number,position,name,datatype_string,value_string 
from v$sql_bind_capture 
where sql_id=&SQL_ID_from_1
order by child_number,position;

When the bind variables have been identified, check the occurrences of those values in the table columns being queried. A value that is heavily skewed could represent a programming issue if too much data is being read or retrieved. Also any date variables that are causing the query to read multiple years worth of data should be reviewed.

Sometimes the fix to a query performing high reads is a programming change to alter how those queries are used. If bad variables are used against a query, then you will be judged for the performance issues created.

The Smith family seeing a query using bad variables:

Tuesday, June 11, 2013

Urge to Purge part 2

Part 1 of this blog post discussed using standard purge functions to reduce overhead on the system. A query to identify the largest objects was provided as a starting point for purge investigations.

A couple of other items are worth reviewing. Often systems will have backup tables created to assist with troubleshooting. These tables should be removed when they are no longer needed.

One way to locate backup tables is to run a query like the following. It may not catch all tables depending upon the naming standards, but it is a good place to start. The list that gets returned may include valid tables, so be sure to check to determine if the tables are being used. You can also edit this query to use other possible strings such as TEMP, TMP, ARCH, ARCHIVE, HIST, BACK, OLD, etc.

select * from dba_segments
where owner not in ('SYS','SYSTEM')
and segment_name like '%BKP%'
and segment_type='TABLE'
order by bytes desc;

Another place to review, involves the log files for materialized views. Sometimes these logs can become quite large. There are a few options to reclaim the space, review Oracle notes for cleaning up materialized view logs.  If you are using E-Business Suite, look up the view name in My Oracle Support as there may be additional steps needed to reclaim the space. The following query will list the materialized view logs ordered by size. Focus on only the largest objects.

select *
from dba_segments
where segment_name like 'MLOG$_%'
order by bytes desc;

Friday, April 26, 2013

Urge to Purge part 1

Purge is a four letter word in many organizations. Resistance to purging data is understandable. The technical staff will be at odds with the functional groups if there is ever a time when needed data is no longer available.

The penalty against purging is performance degredation and additional storage requirements. The additional storage also has an adverse effect on backup and recovery operations. In addition to purchasing additional storage, companies often have to purchase additional CPU which can also impact licensing costs.

If you're not sure of the best place to start your purge investigation, for EBS customers you should be purging as much of the Concurrent Request histor and Workflow history as possible. Next you should investigate the purging options for the features that are most heavily used at your site.

If you are unsure of which modules result in excessive storage usage, then check for the largest objects in your database. Based upon the tables returned, you can then use My Oracle Support to check for appropriate purge routines.

A simple query to check for the 50 largest objects:
select owner,segment_name, segment_type, bytes
from (select owner,segment_name, segment_type, bytes, rank() over
(order by bytes desc
) as rank from dba_segments
) where rank <=50;

Monday, February 18, 2013

Hotsos Symposium 2013

Good news! I found out today that I will get to present at the 2013 Hotsos Symposium. I was slotted in as a fill-in speaker for another presentation that had to cancel.

The Symposium is always a great event. This conference focuses totally on practical performance tuning and database management topics. I'm proud to be part of the company that hosts this event. Last year's Tenth Annual event was great.

My presentation, "Hunting Down Performance Problems", is suitable for beginner - intermediate level DBAs. The presentation will focus on examples of performance problem identification and resolution. I hope to see some of you there.

For more information see -My Speaker profile

Wednesday, September 5, 2012

OOW 2013 Update

Oracle Open World is coming up very soon. If you are going, be sure to stop by the EBS Application Technology Stack SIG meeting on Sunday. The details for the meeting are:

Sunday, September 30th
10:30 to Noon
Moscone West 3018

As usual, there will be a lot of great content from Oracle provided during the SIG meeting. The Special Interest Group meeting typically allow for more interaction with the speakers and other attendees.

Speaking of conferences, the Hotsos Symposium is taking submissions for next years conference.

Symposium speaker submission

If you are not interested in speaking, you can still register to attend.

Also, on the Hotsos front, be sure to check out the latest newsletter. There are a lot of big updates from Hotsos.

Hotsos Newsletter

Wednesday, April 25, 2012


I had a lot of fun presenting my topic to the attendees at COLLABORATE 12 this year in Vegas.
My presentation can be found at the following location:

There are a lot of My Oracle Support notes and SQL commands included in the presentation. Check them out and let me know if you have any questions or issues.