Many DBAs are familiar with the SGA size limitations on 32-bit platforms.  This post shows how to allocate a 3Gb buffer cache on 32-bit RHAS 3.

• Step 1: Mount /dev/shm to as type ramfs.  Edit /etc/fstab and add an entry like this:

none                    /dev/shm                ramfs defaults,size=4G 0 0

• Step 2: do a “mount -a” to mount /dev/shm.  I use ramfs instead of tmpfs because it doesn’t use swap; tmpfs does.  With ramfs memory allocation will also grow dynamically, whereas when using tmpfs it will not.
• Step 3: As root, assuming your database is owned by the Linux account oracle, whose primary group is “dba”, do this:

# chown oracle:dba /dev/shm

• Step 4: Add the following to /etc/security/limits.conf to increase maximum memory lock parameters.  Ensure “oracle” user has his environment sourced to establish these settings.

oracle            soft    memlock         3145728
oracle            hard    memlock         3145728

• Step 5: Login as oracle and do “ulimit -a”; validate that memlock is set to the above values
• Step 6: Edit /etc/sysctl.conf and add/change the following.  When complete, do “sysctl -w” to activate changes into the Linux kernel.  The “vm.hugetbl_pool” setting below is set based on the output of a script provided here (http://download-uk.oracle.com/docs/cd/B28359_01/server.111/b32009/appi_vlm.htm) - run this AFTER Oracle is started at the completion of this document to get a realistic value for vm.hugetbl_pool and adjust accordingly

kernel.sem = 1000 32000 100 150
kernel.shmmax = 4294967295
kernel.shmall = 4194304
net.ipv4.ip_local_port_range = 1024 65000
vm.pagecache = 10 20 30
kernel.shmmni=4096
vm.hugetlb_pool=4096

• Step 7: Unset db_cache_size, db_xk_cache_size, sga_target, sga_max_size, memory_target init.ora parameters and manually set shared_pool_size to appropriate value.  You can use “show sga” to determine this
• Step 8: Set use_indirect_data_buffers=true
• Step 9: Set db_block_buffers such that the product of db_block_buffers and db_block_size = 3G
• Step 10: In oracle’s .profile/.bash_profile, set DISABLE_MAP_LOCK=1.  This is required to avoid unnecessarily long connect times for databases that are connected to frequently

export DISABLE_MAP_LOCK=1

• Step 11: Stop oracle, source environment, ensure O/S limits are correct (ulimit -a), ensure /dev/shm is owned by oracle (ls -al /dev/shm), ensure DISABLE_MAP_LOCK=1, and then start Oracle
• Step 12: Add the following to /etc/rc2.d/S99local:

mount /dev/shm

chown oracle:dba /dev/shm

Test.  Enjoy the benefits of a large cache.  Test across reboots to ensure /dev/shm is mounted correctly.

As a DBA, have you ever had an issue you suspected may be a bug and have been asked to generate “test data” for Oracle development?  We ran into this at a client recently and found a cool 11g utility (at least I *think* it’s new :)) to get Oracle Support the data they need to reproduce the issue in-house.

The situation had to do with optimizer_features_enable being set to 11.1.0.7 in a database recently upgraded form 9.2.0.5 in an Oracle eBusiness Suite environment (11.5.10.2) that utilized Oracle Reports heavily.  Our problem was specific to a custom Report that had optimizer hints embedded in the main query.  With optimizer_features_enable set to 11.1.0.7, the report retrieved no rows.  With it set to 9.2.0.x or 10.2.0.x, it returned the proper number of rows.  Based on this data discrepancy, we felt it safe to set optimizer_features_enable < 11.1.0.7 across the board, as we didn’t know the scope of the issue.

During our work on an SR with Oracle, they asked for us to send the query, all the versions and optimizer settings, as well as an export of all the tables involved in the query with exported optimizer statistics.  Our problem was several-fold; first, the query was complex and had many embedded views, so we didn’t really want to spend time deconstructing it to get a comprehensive list of tables to export.  Second, the underlying tables were very large (hundreds of millions of rows for a few of them) and we didn’t have disk space or quite frankly, time, to export all the tables in their entirety.

Enter DBMS_SQLDIAG …

Using DBMS_SQLDIAG and 11g Data Pump features, we were able to quickly generate a complete test case to export a subset of the rows from all the impacted tables, very quickly and with minimal disk space requirements.  Here’s what we did:

1. Grabbed the offending query from a TKPROF’d trace file
2. Used this syntax to generate a test case:

declare
tc_out clob;
begin
dbms_sqldiag.export_sql_testcase(directory=>’<directory>’,
sql_text=>’<SQL Text>’,
testcase => tc_out,
exportdata=>TRUE,
samplingpercent=>1);
end;
/

In the above example, note the following:

• <directory> is a valid directory - check DBA_DIRECTORIES
• <SQL Text> is the SQL statement from the TKPROF output
• exportdata=>TRUE tells DBMS_SQLDIAG to export the data from the base tables
• samplingpercent=>1 tells Data Pump to use a 1% sampling size.  This was important to limit the number of rows

After executing, a number of XML, log, and Data Pump export dumps are generated to <directory> and available to upload to the SR!

One additional step we took was to export table statistics for all the tables involved in the query.  For this, we looked in the log file for all tables export and used DBMS_STATS.EXPORT_TABLE_STATS to export segment statistics.

The obvious benefit here is that it enabled us to continue working on the SR, but some other possible applications of DBMS_SQLDIAG could be for internal testing purposes, testing functionality/performance across versions of Oracle without a complicated upgrade, regression testing, and so forth.

On a recent project we had a business and technology reason to connect Oracle EBS running 11g to a DB2 database.  After a few weeks of researching for the best practice and approach we decided on the following implementation steps.

1. Download a DB2 driver, such as from DataTek, and download to your application server in any storage directory.
2. Create a directory called “YM” under your custom application top.  example: $CUSTOM_TOP/java/YM.  (Assumes you have already created a $CUSTOM_TOP and it has a directory called “java”.
3. Copy all files from the storage directory to $CUSTOM_TOP/java/YM
4. Add 3 entries to s_adovar_classpath and s_adovar_afclasspath to point to the files in $CUSTOM_TOP/java/YM.
5. Make sure you run auto-config on your updated environment.
6. Bounce the application and database tier.
7. Perform any select, insert, update and or delete from the Oracle EBS application via java program to the DB2 database.  The java program will reference the DB2 connection from a jdbc connect string such as: “jdbc:oracle:db2://servername.com:port;databasename=xxxxxx; User=xxxx; Password=xxxx”

We have been using jdeveloper 10g to create new OA Framework forms for our R12 Oracle EBS applications 12.0.6 with database version of 10.2.0.4 without issues.

We then upgraded our database to 11.1.0.7.0 and EBS to 12.1.1.  That is when we encountered an issue with prior custom OA forms in EBS.  oracle.apps.fnd.framework.OAException: oracle.jbo.JboException: JBO-29000: Unexpected exception caught: java.lang.IllegalAccessError, msg=oracle/jdbc/driver/OracleCallableStatement.

This issue/error is specifically related to the OracleCallableStatement used in 11g.  With 11g certain features/libraries are not available.  The Oracle Database 10g Release 2 (10.2) will be the last major release to offer the following feature/libraries: classes12.jar, oracle.jdbc.drivers.* and OracleConnectionCacheImpl. In other words these features/libraries will not be available in the next major database release (for example 11g).

Since our java code was using an OracleCallableStatement and also using the oracle.jdbc.drivers on the 11g database we encountered the error above.  This means that public Oracle JDBC driver classes that were referenced by oracle.jdbc.driver package must be referenced by using the oracle.jdbc package . Therefore, Java programs must not import public classes that  have belonged to the oracle.jdbc.driver package - but import  those classes from the  oracle.jdbc package.

Since our code was using “import oracle.jdbc.driver*” we had to replace it with import oracle.jdbc.*

Once we replaced the above statement; the OracleCallableStatement executed as expected.

So you’re going to patch from 12.0.6 to 12.1.1?  It’s basically like any other major upgrade with Oracle’s e-Business Suite; tons of pieces to upgrade, patch, validate, and a high potential for running into problems.  The documentation for all the “gotchas” is all over the place, but Metalink document 752619.1 is the best place to start and has all the major steps listed.  In general, the following is required:

• Confirm database version
• Upgrade 10.1.3 home to 10.1.3.4
• Upgrade 10.1.2 home to 10.1.2.3
• Apply necessary pre-req patches
• Apply maintenance pack (7303030)
• Apply post-maintenance pack patches
• Do all the post-upgrade steps

Below are some of the lessons learned in an environment in which we patched 12.0.6 to 12.1.1 and along the way, upgraded the 10.2.0.4 database to 11.1.0.7.

Oracle XDB core-dumps (look for ORA-31114)

• Follow Metalink document 760611.1
• We’re not sure exactly why this happened, but something in the XDB upgrade from 10.2.0.4 to 11.1.0.7 failed

AZ tables/objects invalid and workers fail on 7303030 (azR12active.sql)

• Result of XDB core-dump issue above
• Follow Metalink document 832459.1

Worker fails on EGOSILDU.sql

• Per Metalink document 847687.1, this is a bug patched with 8485942.  This patch, 8485942, isn’t available as of 10/10/2009
• Per the recommendation in 847687.1, skipped worked and continued

Relink fails on RAXTRX

• Choose to continue.  This is due to bug
• Look in 841113.1 for patches.  These are the ones I found that fixed this, plus some other issues found during testing …

8239041

7721420
7660309
7673650
8393427
8414254
8414069

Failures during 7303030 compiling multiple forms and libraries

• Choose to continue.  Manually recompile using adadmin after the patch, and if things still fail look for patches

Issues applying 10.1.3.4 IAS_ORACLE_HOME patch (7272722)

• Receive failures trying to start OPMN while at 83% complete on initial screen
• Environment was cloned and adcfgclone’d successfully from a 12.0.6 instance
• adcfgclone.pl will update all the right stuff in $ORA_CONFIG_HOME correctly, but several files in $IAS_ORACLE_HOME will not be updated with the correct environment-specific values
• Change hard-coded paths in the following:

$IAS_ORACLE_HOME/inventory/Clone/clone.xml
$IAS_ORACLE_HOME/config/ias.properties
$IAS_ORACLE_HOME/opmn/bin/opmnctl
$IAS_ORACLE_HOME/Apache/Apache/bin/iasobf

• Make sure you can run $IAS_ORACLE_HOME/opmn/bin/opmnctl startall|stopall|status
• Resume 7272722

Failures with 7303030 doing FndXdfCmp

• I’m pretty sure this was a site-specific issue related to not cloning cleanly
• If the adworkxxx.log file looks like this …

java.lang.reflect.InvocationTargetException
at sun.reflect.GeneratedMethodAccessor1.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at oracle.apps.ad.worker.AdJavaWorker.invokeUtility(AdJavaWorker.java:501)
at oracle.apps.ad.worker.AdJavaWorker.main(AdJavaWorker.java:356)
Caused by: java.lang.NoSuchFieldError: mTypeData
at oracle.apps.fnd.odf2.FndInitType.doCmpType(FndInitType.java:156)
at oracle.apps.fnd.odf2.FndXdfCmp.doOdfCmp(FndXdfCmp.java:98)
at oracle.apps.fnd.odf2.FndXdfCmp.applyXDF(FndXdfCmp.java:186)

• … hopefully you’ve got an existing 12.1.1 instance to refer to
• Look in $JAVA_TOP/oracle/apps/odf2 for class files containing mTypeData.  You should see 3 class files
• Restore anything missing from a clean instance
• Generate all JAR Files
• Kill patch
• Resume

Patch takes a very long time on adobjcmp.sql (11.1.0.7)

• This will take a long time, it’s compiling all  invalid objects and there will be 50,000+
• Look for lots of time on “resmgr:cpu quantum” wait event
• Do “alter system set resouce_manager_plan=””
• Bounce the database when everything is done

Are you trying to use RapidWiz to Upgrade to 12.1.1 from 12.0.6?

• Don’t.  This is for an 11.5.x upgrade

More to come as I find them, I’m sure …

If you are anything like me you were trained from the beginning that forms development was done on a shared server.  In the cases that you came onto a client where such a mechanism was not enabled you would go through the effort of ftp’ing all of the relevant forms and libraries to your desktop and make your modifications within Oracle Forms Builder installed on your local machine.  You would compile your form to make sure it had no errors, but you would move your form back to the server and generate your fmx there.  Up until recently I never questioned that methodology as it always worked fine.

On a client site part of a consultant’s job is to mentor the legacy staff.  In a lot of cases these individuals have no exposure to Oracle technology.  Recently an employee at a client came to me because he was having several issues working on a form on his desktop.  His issues were as follows:

1.  When opening the form it gave him a message that it was unable to find other forms and another that stated it was unable to find libraries.

This was a simple enough fix.  We had him to go his windows registry (run->regedit) and modify the FORMS_PATH variable to include the location of the forms and libraries.  This was of course after we made sure he downloaded all the forms and libraries in question.

2.  After successfully compiling the form, he attempted to run it and was given an error as follows:  “FRM-10142: The HTTP listener is not running on <local computer name> port 8889.  Please start the listener or check your runtime preferences.”

This again was an easy one.  Forms Builder 10G is web based so the app server instance has to be running to serve up the form.  First we modified the DEFAULT.env file located in the ORACLE_HOME/forms/server folder to make sure that the FORMS_PATH variable matched what we did in the system registry earlier.  Then we went to Oracle Forms Builder from the windows start menu and choose “Start OC4J Instance”.

Once this was done we were able to run the form and the apps server instance served it up, but we promptly received another error:

3.  After app server served up the form, a form trigger error occurred:  “FRM-40375: ON-ERROR trigger raised unhandled exception ORA-06508.”

Now the first thing we checked was ORA-06508 which states the following:

It was only after we opened and compiled all of the attached libraries and forms that this error went away and the form served up correctly.  The error occurs because of an incompatibility between forms and libraries compiled on a Linux box vs. a windows box.  This is why the forms served up just fine on a Linux server implementation of forms but not on the local windows install.  As you can imagine some of the native forms have a large number of forms and libraries attached making it impractical to do this all of the time.

For a custom form that does not leverage many native EBS libraries or forms you would not have a difficult time doing the development on your desktop.  However, for extension to native forms, stick with doing your development on the Linux server.

In the current economy you’re probably looking under every rock to see where you can cut costs and software support and maintenance fees may be something for you to consider. These fees can be significant. Take your Oracle support fees for example, roughly 22% of original license fees paid annually.

If it’s been a while since you last reviewed your support agreements, start there. You may be paying support fees for applications you no longer use. Those should be a no-brainer, get them on the chopping block ASAP. Others may not be so straightforward. A common example are those modules you purchased licenses for, but shelved for whatever reason. They were cut from the scope of the original implementation or maybe they were purchased with thoughts of using down the road. Now you are paying support fees for something you’re not using and may or may not use in the future. What should you do now?

If you’re considering terminating the support on these modules, there are a few things to keep in mind. Oracle requires that you drop the software licenses when you stop paying for annual support, which means, when/if you decide to implement those modules down the road, you’ll need to purchase new licenses. Oracle will also hit you up with reinstatement fees including retroactive support and maintenance fees and additional penalties. So keep this in mind when kicking around the options.

So I guess the answer to my original question is….it depends. It depends on many factors that you’ll need to consider before making any changes to your support agreements. Before making any changes, review your current agreements and read over the latest cancellation policies and do what makes sense for you.

 The following SQL shows you the top resource-consuming pieces of SQL in your instance:

select ash.SQL_ID ,
     sum(decode(ash.session_state,'ON CPU',1,0))     "CPU",
     sum(decode(ash.session_state,'WAITING',1,0))    -
     sum(decode(ash.session_state,'WAITING', decode(en.wait_class, 'User I/O',1,0),0))    "WAIT" ,
     sum(decode(ash.session_state,'WAITING', decode(en.wait_class, 'User I/O',1,0),0))    "IO" ,
     sum(decode(ash.session_state,'ON CPU',1,1))     "TOTAL"
from v$active_session_history ash,
       v$event_name en
where SQL_ID is not NULL
  and en.event#=ash.event#
group by sql_id
order by sum(decode(session_state,'ON CPU',1,1))   desc 

As DBAs, we’re faced with this question all the time.  In order to quickly supply an accurate answer to this question, an experienced Oracle DBA needs to have a few tools in his belt - and I’m not talking about any special software or monitoring solutions, I’m talking simply SQL*Plus scripts and access to a database account with access to the V$ views.

Here’s what I do when someone asks me this question:

Step #1:

Take a look at V$SESSION_WAIT.  This will show you details about sessions currently and actively waiting on named Oracle wait events.  More often than not, if things are “slow”,  a session or sessions is waiting on an instrumented Oracle wait event.  The query I use to do this (works on 9i-11g) is below:
select     A.sid,
decode(A.event,'null event','CPU Exec',A.event) WaitEvent,
decode(A.event,'slave wait','N/A',
'PX Deq: Execution Msg','N/A',
'PX Deq Credit: send blk','N/A',
'latch free','N/A',
'enqueue',
chr(bitand(A.p1,-16777216)/16777215)||chr(bitand(A.p1,16711680)/65535),
'file open','-1',to_char(A.p1)) p1,
decode(A.event,'enqueue',decode(mod(A.p1,16),'6','ROW-LOCK','4','ITL','3',
'FK?','OTHER'),
'file open',
-1,
A.p2) p2,
decode(A.event,'latch free','N/A','enqueue',null,'PX qref latch','-1',
'buffer busy waits',to_char(A.p3), A.p3) p3,
decode(A.state,'WAITING','WTG',
'WAITED UNKNOWN TIME','UNK',
'WAITED SHORT TIME','WST',
'WAITED KNOWN TIME','WKT') wait_type,
decode(A.state,'WAITING',A.seconds_in_wait,
'WAITED UNKNOWN TIME',-999,
'WAITED SHORT TIME',A.wait_time,
'WAITED KNOWN TIME',A.WAIT_TIME) wt,
round((last_call_et/60),2) lc,
substr(nvl(b.module,b.program),1,15) pgm
from    v$session_wait A,
v$session B
where  A.event not in ('Queue Monitor Slave Wait','wait for unread message on broadcast channel','Queue Monitor Wait','jobq slave wait','queue messages','SQL*Net message to client','Null event','rdbms ipc message','i/o slave wait','io done')
and A.event <> 'pipe get'
and A.event not like '%akeup%'
and A.event not like 'Streams AQ%'
and A.state in ('WAITING','WAITED KNOWN TIME')
and A.sid=B.sid
and B.status='ACTIVE'
order by 1
/

Sample output is below (you’ll have to set column headings and other SQL*plus formatting options, but you get the point):

Sid Wait Event                     P1         P2         P3         Typ     Time       last call What
------ ------------------------------ ---------- ---------- ---------- --- -------- ---------------
4518 gc buffer busy                 24         38019      65537      WTG        0             .00
4519 gc buffer busy                 24         38019      65537      WTG        0             .00 XXVG_INV_PICKLI
4680 gc buffer busy                 24         38019      65537      WTG        0             .00
4830 gc buffer busy                 24         38019      65537      WTG        0             .00 FNDRSSUB
4886 smon timer                     300        0          0          WTG       29        18447.47 oracle@usplsvpe
4887 control file parallel write    2          4          2          WTG        0        18447.47 oracle@usplsvpe
4893 gcs remote message             24         0          0          WTG        0        18447.47 oracle@usplsvpe
4895 gcs remote message             24         0          0          WTG        0        18447.47 oracle@usplsvpe
4896 ges remote message             64         0          0          WTG      152        18447.47 oracle@usplsvpe
4899 DIAG idle wait                 1          1          200        WTG  1106848        18447.47 oracle@usplsvpe
4900 pmon timer                     300        0          0          WTG      680        18447.47 oracle@usplsvpe

In this output, you’ll see a handful of sessions waiting on “gc buffer busy” wait events.  At this point, it’s time for the Oracle DBA to study up on what the wait events mean; in this case, sessions are waiting on RAC-related global buffer busy waits, which means that blocks are being used and are pinned in another instance’s cache.   I won’t go into a description on what all the wait events mean here - you can look them up at any of the following URLs:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/waitevents.htm#REFRN101
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/waitevents003.htm#BGGIBDJI
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/instance_tune.htm#i22670
http://www.adp-gmbh.ch/ora/tuning/event.html
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=34405.1
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=62172.1

At this point,  you know who’s waiting on what and you can use the output to look for anomalies for the current environment.  A couple of things to note:

• There are a handful of common wait events in any “busy” Oracle environment; specifically, “db file sequential read”, “db file scattered read”, latch-related, enqueue-related (locks), etc.  You should be familiar with what types of waits are “normal” for a given system.
• You should become familiar with the relative quantity of each type of wait for each system at various times during the day.  For example, at client A, with a new implementation, low transaction volume, not many users, you may never see more than a handful of I/O-related waits at any given time.  At this client, if you see several dozen sessions waiting on the same type or class of wait event, it’s probably a cause for concern.  At a different client, it may be typical to see 20 or 30 I/O-related waits at any given time.  Bottom line is this - you need to have familiarity with the system you’re monitoring.
• Any DBA with his salt should become study Oracle’s wait interface and become familiar with what each of the major wait events means
• You can use Centroid’s “CCEO Infra Wait Interface.ppt” document as a quick reference on the wait interface

Step #2:

Grab the SQL for the sessions that show up repeatedly and frequently in the output from the above query.  Note the SID (Session Identifier) and use it as input to the following script:

select
t.sql_fulltext ,
t.buffer_gets, t.disk_reads,t.executions
from v$session s,
v$sql t
where s.sql_address =t.address and s.sql_hash_value =t.hash_value
and s.sid = &&1
/

Format the out of this if you plan on running an execution plan on it.

If you want additional detail about the session(s) from V$SESSION_WAIT, you can query V$SESSION.

Step #3:

If the SQL statements extracted from the previous step are waiting on I/O-related or contention-related waits,  you should grab an execution plan/explain plan by taking the formatted SQL and plugging into the below script:

set lines 120
explain plan for
<< insert SQL here >>
select * from table(dbms_xplan.display(null, null,'all'));

Step #4:

If the slowness is related to, for example, locks (enqueue waits), find out who the lock holder(s) is by querying V$LOCK or DBA_WAITERS and make a judgement call as to whether to kill the session(s) holding the lock, communicate with the end-user, etc.

Step #5:

Fix it.  This could be a quick-fix (resolving a lock), or more likely will take some time to assess.  If your cause of slowness is I/O-related waits, for example, you need to determine whether the SQL is optimized, whether indexes will help, whether concurrency patterns are abnormal (i.e., are there 50 simultaneous executions of a batch job that should only be running serially?), etc.  SQL optimization is a science in itself that requires knowledge of the underlying data structures and data volumes, as well as an understanding of Oracle’s optimizer.

Step #6:

What if V$SESSION_WAIT doesn’t tell you anything meaningful?  This is when you should consult ASH (Active Session History) views to give you time breakdown details:
select
decode(nvl(to_char(s.sid),-1),-1,'DISCONNECTED','CONNECTED')
"STATUS",
topsession.sid             "SID",
topsession.program                  "PROGRAM",
max(topsession.CPU)              "CPU",
max(topsession.WAIT)       "WAITING",
max(topsession.IO)                  "IO",
max(topsession.TOTAL)           "TOTAL"
from (
select * from (
select
ash.session_id sid,
ash.session_serial# serial#,
ash.user_id user_id,
ash.program,
sum(decode(ash.session_state,'ON CPU',1,0))     "CPU",
sum(decode(ash.session_state,'WAITING',1,0))    -
sum(decode(ash.session_state,'WAITING',
decode(wait_class,'User I/O',1, 0 ), 0))    "WAIT" ,
sum(decode(ash.session_state,'WAITING',
decode(wait_class,'User I/O',1, 0 ), 0))    "IO" ,
sum(decode(session_state,'ON CPU',1,1))     "TOTAL"
from v$active_session_history ash
group by session_id,user_id,session_serial#,program
order by sum(decode(session_state,'ON CPU',1,1)) desc
) where rownum < 10
)    topsession,
v$session s,
all_users u
where
u.user_id =topsession.user_id and
/* outer join to v$session because the session might be disconnected */
topsession.sid         = s.sid         (+) and
topsession.serial# = s.serial#   (+)
group by  topsession.sid, topsession.serial#,
topsession.user_id, topsession.program, s.username,
s.sid,s.paddr,u.username
order by max(topsession.TOTAL) desc
/

The output may look like this:

STATUS         Sid PROGRAM                           CPU    WAITING    IO  TOTAL
------------ ----- ------------------------- ----------- ---------- ----- ------
DISCONNECTED  4518                                 11220          9    83  11312
CONNECTED     4584                                  9620         25    50   9695
DISCONNECTED  4683 das@usplsvpba002.verigy.n        5598        258   735   6591
et (TNS V1-V3)

DISCONNECTED  4614                                   158         75   552    785
DISCONNECTED  4698 sqlservr.exe                       52         20   695    767
DISCONNECTED  4491 sqlservr.exe                      102         35   496    633
DISCONNECTED  4698 sqlservr.exe                       14          9   578    601

You can use the methods in Steps 2-3 above to get details about the sessions above.

Step #7:

If nothing stands out at this point, consult system logs and Oracle alert logs, as well as O/S performance tools (sar, top, glance, etc)

As a DBA, how many times have you been asked… “what happened at 3:00am today, everything slowed down?”  Try answering this question with V$SESSION_WAIT – you can’t.  Try answering by looking at AWR, Automatic Workload Repository, or Statpack reports – you may be able to make some guesses, but they’d only be guesses.  Try answering with data in the alert log or data in O/S logs – you’ll only be able to answer this question if you get very lucky.  Try answering by looking at Oracle Enterprise Manager Grid Control 10g.  Well actually, this gets you on the right track, because Grid Control will show you ASH, Activity Session History data!

ASH is built into the kernel in Oracle 10g and MMON and MMNL do the work!  ASH collects data every second from V$SESSION, V$SESSTAT, V$SESSION_WAIT, etc, and populates V$ACTIVE_SESSION_HISTORY.  An hour of data is kept in memory for easy access, just query V$ACTIVE_SESSION_HISTORY (think of it as V$SESSION_WAIT plus some more stuff, but longer lasting).  Data is flushed to disk every hour and externalized via views WRH$_<ASH name>.  The collection interval and volume self-adjusts based on load.  ASH will collect more aggressively when the system is busy, less so when not.

In short, ASH gives you a way to use all your “right now” tuning methods for time periods in the past.  When someone asks you what happened at X:00AM when you were fast asleep, you have an option now.  Give it a try!

Look for deeper dives into ASH in future posts.  Stay tuned!