better now than never – buffer busy wait challenges and “data block class”

Object contention – Buffer Busy waits ?? ? – Tablespaces have no ASSM set?? On RAC ?? – single freelist group?

I have a RAC instance with a 30million row+ OLTP table that is not Partitioned (this is our most used table and since we have a Standard edition license partitioning right now is not an option until we get budget for Enterprise edition ).

The underlying DATA tablespace for this table is not on ASSM and has a single freelist group. I got called about 3 weeks back for slow performance at certain times during the day. Obviously the first thing I do is jump on Spotlight do my general checks and see my wait events. There was no doubt that my biggest wait event was “Buffer Busy waits”. While I used my performance monitoring tool to get down to this – the below sql will also list your top wait events.

select event,time_waited,
round(time_waited*100/SUM (time_waited) OVER(),2) wait_pct
from(
select event,time_waited
from v$system_event
where event NOT IN
('Null event',
'client message',
'rdbms ipc reply',
'smon timer',
'rdbms ipc message',
'PX Idle Wait',
'PL/SQL lock timer',
'file open',
'pmon timer',
'WMON goes to sleep',
'virtual circuit status',
'dispatcher timer',
'SQL*Net message from client',
'parallel query dequeue wait',
'pipe get'
) UNION
(select name,value from v$sysstat
where name like 'CPU uses when call started'))
order by wait_pct desc

We had this issue 2 days consecutively in the week(and have had similar behavior over the last few months) . We also collect statspack data frequently and the below query was run to identify the buffer busy segments in question for the time interval we had performance problems.

acknowledging source of sql : Author:Tim Gorman (SageLogix, Inc)
spool sp_buffer_busy_waits
clear breaks computes
break on day skip 1 on object_type on report
select yyyymmdd sort0,
daily_ranking sort1,
day,
object_type,
owner,
object_name,
buffer_busy_waits
from (select to_char(ss.snap_time, 'YYYYMMDD') yyyymmdd,
to_char(ss.snap_time, 'DD-MON') day,
o.object_type,
o.owner,
o.object_name,
sum(s.buffer_busy_waits) buffer_busy_waits,
rank () over (partition by to_char(ss.snap_time, 'YYYYMMDD') order by sum(s.buffer_busy_waits) desc) daily_ranking
from (select dbid,
instance_number,
dataobj#,
obj#,
snap_id,
nvl(decode(greatest(buffer_busy_waits,
nvl(lag(buffer_busy_waits) over (partition by dbid, instance_number, dataobj#, obj# order by snap_id),0)),
buffer_busy_waits,
buffer_busy_waits - lag(buffer_busy_waits) over (partition by dbid, instance_number, dataobj#, obj# order by snap_id),
buffer_busy_waits), 0) buffer_busy_waits
from stats$seg_stat) s,
stats$seg_stat_obj o,
stats$snapshot ss
where o.dataobj# = s.dataobj#
and o.obj# = s.obj#
and o.dbid = s.dbid
and ss.snap_id = s.snap_id
and ss.dbid = s.dbid
and ss.instance_number = s.instance_number
and ss.snap_time between (sysdate - &&V_NBR_DAYS) and sysdate
group by to_char(ss.snap_time, 'YYYYMMDD'),
to_char(ss.snap_time, 'DD-MON'),
o.object_type,
o.owner,
o.object_name
order by yyyymmdd, buffer_busy_waits)
where daily_ranking <= 10 order by sort0, sort1; There results showed that my biggest table had the highest buffer busy waits (note it was only on the table object and not on the index objects; the index tablespace had ASSM on them) I knew my options were the below:

(i)alter this object for multiple free list groups (this calls for an object reorg, we all know that reorgs require time, appropriate approvals and planning for it to be implemented in Production).

(ii)recreate the DATA/INDEX tablespace for this object with ASSM and let Oracle manage your freelist contention.
(we are on version 9i, as a DBA you wish your underlying structure are set correctly for a huge application. But then that’s not a realistic world ; most likely you have to pickup the work that was done in the past and get them all together)

(iii)alter storage for the object to add more freelists (this can be done with a simple alter table statement).

Obviously the patch I could submit rightaway to change management was for (iii). Since we have added more freelists; I have not had calls on performance issues – but given that I was on RAC and a single freelist group ....the issue had to be addressed

I mustered all the courage to let Management know that we may have to Reorg our biggest table and given that I will need to reorg the table I rather go with ASSM

While the reorg will need some planning and preparation it can be done. “Better now than never.” My experience with Quest Central for Live Reorg has been fairly good

Below are some quick / important steps to get your Live Reorg rolling
Before you begin install QSA (quest server agent) on your database server. I prefer the manuall install – Install from the GUI keeps failing at the login prompt even if you have rexec enabled.

1.Step1 connect to Space manager(part of Quest Central – Version 6.0.1.289) as an account that has dba privileges.
2) On your Tolls Bar Reorganize > Reorg Manager
3)Find the object you want to reorganize and say Next
4)Choose the Option (i) Optimize for speed (ii) Relocate Tablespace (create a new tablespace with SEGMENT SPACE MANAGEMENT AUTO)
5)Choose default scripting options;default tablespace storage options; Object Options
make sure to check for NoLogging
6)Store the Reorg as job schedule
(Details on the Reorg will be a separate on its own)
While I am in the process of testing my reorg in preprod and had no performance issues after I added multiple freelists to the heavily used table in PRODUCTION, I am still left with one question, will a freelist contention not manifect itself as a “freelist class” in v$waitstat when you are on RAC and have a single freelist group? I have read some posts saying that in all probablility it may manifest itself as a “datablock class” until you add multiple freelist groups.(I will need to run more test to verify this)

October 30th, 2006 - Shakir Sadikali's blog pointed out buffer busy waits as a result of disk write caching disabled..."I did not have to check our batteries" we are on NFS and noac "nocaching is a separate issue that we are dealing with for I/O performance issues"

my everyday struggles with noac……….

"noac" is a mount attribute that disables attribute caching on the client side. If you are on RAC this option is mandatory for mounting shared files with NetApp. I have proved several times now through several tests that our performance is 3 to 4 times slower with the noac option as opposed to mounting the files without noac. Oracle does support RAC on NetApp – and NetApp requires the noac option for RAC. The bottom line the database is suffering from performance problems due to this option ; the current version is SLES 8 and Oracle 9i. NetApp claims that with SLES 9 we should not have any problems and Oracle says Oracle10g On Solaris should not pose any problems. ….well, I will have to see to believe now …we are in the process of a couple of migration tests to get past this problem

more on NetApp and RAC can be found on the below linkhttp://www.ntap.com/library/tr/3359.pdf

Is VPD a Realistic Solution for existing Production Applications with n tables??

With security becoming a pressing concern these days, Customers like to enforce multiple levels of security. When the question was put on the table to ; my obvious answer to the Customer and Application team was “the database allows for fine grained access control and we can enforce security using VPD policies at the database level”.

It was not until I started looking at the n(power n) tables in my Database that I started wondering ……….is VPD really going to be a realistic solution? VPD lets you create policy functions that returns the appropriate predicates to be applied to the tables. I have seen it work well for some small Applications where there is a definite set of predicates that are returned. In an Application with many many tables; I think it would be an exhaustive effort just to come up with a list of predicates that need to be applied to the tables……………..”Column Relevance”, is certainly a good feature with 10g but I still keep wondering how one would come up with the list of predicates for many tables linked by many keys and no single data coulumn available to filter data across all tables.

Saving Public connections to Oracle Business Intelligence 10g (10.1.2)…..

In the middle of all your OOW2K6 blog information, a little peek at the few roadblocks I hit today with BI 10G

The last few hrs I have been cranking out Discoverer Reports for a presentation with Business Users. Business is in the process of evaluating WebFocus and BI Discoverer (the $$$ numbers look better with Discoverer but will it serve the purpose is what we are trying to figure out).
I have Oracle Business Intelligence 10g (10.1.2) up in running with all my workbooks/worksheets ready to go for the demo….hmmm…until I hit one problem.

i)Oracle Business Intelligence 10g (10.1.2) does not have an option to save Viewer Public connections. I know this can be done when integrated with the portal but BI standalone doesn’t seem to give me this ability on the EM console. I have spent sometime now looking for this information on the web and have found no solutions (any solutions?? will be great to know)

(ii) are Analytic Functions available in Discoverer? With the need to develop more and more complex Reports these days , most of our Reports use Analytic Functions ROW_NUMBER(),RANK(),DENSE_RANK() I have had to create views with Analytic Functions and then build Discoverer Reports on the views

Configuring Oracle 10g Reports In-Process Server .......

I spend a lot of time in the Database World ; but requirements have needed me to work with Oracle Application Server often as well(years back I had to maintain OAS 4.0 - remember the cartridge world!!!; maintaing OAS 10g has made life so much more easier )With 10g OAS most of my work has been with Deploying and maintaining the BI Discoverer container and everything with Discoverer, having said this I was familiar with opmn and all its underying fundamentals to manage the Discoverer Container.Recently I got moved to an effort where my Clients wanted me to set up a 10g Reports server(dev/test and prod instances and start getting reports out the door).

Below are a few key points that got me upto speed fairly fast with setting up my Oracle Reports Server

1)In-Process Server is the default configuration for the Reports Server. In-Process Server Default name = "rep_hostname"

2)Starting your Reports Server cd $ORACLE_HOME/bin/rwserver.sh server="rep_hostaname"

3)Configuring connection information for your reports servercd $ORACLE_HOME/reports/confOpen cgicmd.dat add the following entry: userid=/@ destype= desformat=

4)To maanage your In-Process Report Server from opmn$ORACLE_HOME/bin/addNewServerTarget.sh "rep_hostname". To start and stop your container./opmnctl stopproc ias-component=repsrv904 process-type=ReportsServer

5)Configuring Directory Location for your Reports cd $ORACLE_HOME/reports/conf/rep_hostname .confedit rep_hostname.conf and change sourceDir property to where the Reports are. Remember to remove the comments from the XML Propert

6)Invoking Web based Report from your Forms Forms WEB.SHOW_DOCUMENT Built In to call ReportsSyntax: Web.Show_Document (URL,Target) .Target can be _blank or _self depending on whether the reports needs to open in a new window or not.

While my dev env is almost there, I am sure we will have a little more things to deal with before we go Live. It has been a busy week overall ; keeping up with work and trying to squeeze in time to catchup with all what's happening at OOW2006; my car breakdown has added to the mix as well

Count Down for Oracle Open World (2006)!!

As a few of my friends leave today for Open World, I have been busy at work and stayed back. I can't wait for the podcast sessions from Open World. Some of the sessions I have been waiting hear are below ..........................and as fast as Oracle can get them out; I will be ready to consume the information

1) Concurrent Manager Queue Overlap Analysis
2) Oracle On Demand Powered By Oracle Database Technology
3) Leveraging Oracle Workflow for Efficient Transaction Processing
4) Oracle Daily Business Intelligence: A Business Intelligence Solution for Oracle E-Business Suite, Providing Relevant, Timely Information to End Users
5) Using Oracle Warehouse Builder 10g Release 2 to Implement Production ETL in a Geospatial Data Warehouse
6) A Hidden Treasure: Oracle E-Business Suite Support Tools
7) Coming Improvements in Oracle Text Performance
8) Oracle Database Secure Configuration: What You Need to Know Today
9) Dell IT: Get Beyond Oracle Real Application Clusters and Start Deploying an Enterprise Grid Architecture
10) Bristol-Myers Squibb: Transparent Application Tuning Using SQL Profiles--Practical Lessons, Tips, and Techniques
11) Continuous Data Protection (CDP) for the Oracle Database
12) To Upgrade or Not to Upgrade? Archiving Strategies for Oracle E-Business Suite Sites
13) Tuning SQL When You Cannot Change the Code
14) Oracle Application Server 10g Release 2: New Features
15) Using Business Intelligence to Maximize Customer Value
16) Oracle Discoverer Future: Protect, Extend, Integrate
17) Oracle Forms Personalization Without Coding
18) MAA Best Practices: Reducing Downtime for Planned Maintenance
19) Operations Using Oracle Database 10g High-Availability Features
20) Composite Fusion Middleware Solutions for Oil and Gas
21) Why Digital Signatures and Oracle Go Hand-in-Hand
22) Oracle Data Mining Case Study: Xerox
23) Demystifying SQL Tuning with Oracle Tuning Pack
24) Welcome to My Nightmare: The Common Performance Errors in Oracle Databases
25) Maximum Availability Architecture Best Practices: Building a Highly
26) Available and Disaster-Proof Architecture, Using Data Guard, Oracle RAC, Automatic Storage Management (ASM), and Flashback27) Storage Area Networks for Your Oracle Applications

Cost Based Optimizer - Let your optimizer do it for you!!!(Easier said than done)

Easier said than done ! I have a 9i RAC environment (SLES; Suse Linux) - its been up for a while with no statistics updated except when the first roll out happened. I come onboard and start crying we need statistics updated (its a COST Based Optimizer OLTP system); some queries are suffering because of bad execution plans which in turn is because stats are not updated.

All right, so convinced management that we should go ahead and get our stats updated and keep it updated for a COST Based Optimizer system. And guess what while 80% of the queries do better - 20% suffered and started taking a very long time after statistics were updated (In a PreProduction environment).

So the process has not been simple (considering we have n number of Applications on the system) - we have had to go through many test cycles to identify bad sql and tune them. I have not found an easier way to get past this problem ; have not heard of a better solution so far from my friends in the Oracle community as well. I do know that AWR on 10g will make this effort a little easy - are there any solutions for 9i????

Do away with tnsnames.ora - setup your OID proof of concept in less than half hour

If you are looking to move towards a centralized LDAP server and do away with tnsnames.ora, you can set up a proof of concept with OID fairly fast.

9i Release 2 Database Install comes with OID, do a custom install of OID and all its Managed Server Products.

Once the Installation is successfully completed :
Oracle Net Manager:
The Oracle Net Manager can be used to perform entry management within OID:

Start up the Oracle Net Manager. Expand the Directory node.
Click on Service Naming node and press the + button.
On the Directory Server Authentication dialog enter the correct user and password (cn=orcladmin/password) and click the OK button.
Add the service and test it in the same way you would add a local naming service using the Net Manager.
You can add all your services at this step.
Exit Oracle Net Manager.

Oracle Directory Manager
The Oracle Directory Manager is the main directory administration tool.
In this case we will use it to check that the Oracle Net Manager has entered our connection information into the directory:
Start the Oracle Directory Manager.
Log into the OID Server Instance using the correct user (cn=orcladmin), password, server and port (389).
Expand the Entry Management node. Expand the cn=OracleContext node. Listed under this node will be an entry (cn=Service) for each service configured by the Oracle Net Manager.
Exit the Oracle Directory Manager.

Once the above steps are completed, remove your tnsnames.ora and publish your ldap.ora to your users. ldap.ora should be saved in the $ORACLE_HOME/network/admin directory.

below is an example of an ldap.ora file:

# LDAP.ORA Network Configuration File: C:\oracle\oid\network\admin\ldap.ora# Generated by Oracle configuration tools.
DEFAULT_ADMIN_CONTEXT = ""
DIRECTORY_SERVERS= (xxxxxxxxx:389:636)
DIRECTORY_SERVER_TYPE = OID


your connections now to Oracle is Centralized and you can stop distributing tnsnames.ora and connection information. The flip side to it , you have a single point of failure if your OID server is down - think of having a backup ldap server, can ldap.ora support multiple ldap servers - the last I tried we could not get ldap.ora to support multiple ldap servers......................

Oracle Apps 11i Installation

Anybody installed Oracle Apps 11i on Windows - I wanted to jumpstart my experience with Apps 11i with Vision Demo.
I had a windows server sitting on my Desk - thought it will be quick to have that setup with 11i Apps.
Its no joke figuring out all the little bits and pieces that need to be in place prior to Installing Oracle Apps 11i on windows.
---cygwin( make sure you get make and gcc),VC++,perl (if you used edelivery to download your software you will not need to go through the staging steps).
might have just been faster to rebuild my box to RedHat and completed the 11i install......more to come...Installation steps Oracle11i Apps on windows finally......