This is a decent way to determine why a cursor isn’t being shared, resulting in cache locking during parsing (134 minutes total yesterday, which is very high historically)… SQL> select count(*),sql_id from gv$active_session_history where event = ‘library cache lock’ group…
Category: Oracle
Recreating diskgroup in an existing RAC
We created a diskgroup with normal redundancy, and were wasting half the space on the mirror as a result. We are completely striped and mirrored in EMC, so a mirror buys us no additional protection. We wanted to recreate the…
Finding rows that were locked, after the fact
In the Oracle database, if you have purchased the tuning pack, you have full access to the DBA_HIST_% views. AWR, or Automated Workload Repository, is normally accessed via grid control or another tool. However, the raw data in the DBA_HIST…
Setting TCPNODELAY on Oracle XA datasource in JBOSS
While troubleshooting performance problems, we wanted to test disabling Nagle’s algorithm in JBOSS with an Oracle XA datasource. We found that property was not configurable in the regular Oracle XA class. We ended up extending the Oracle class to set…
High latch free waits on Oracle XA transactions
We experienced large waits as well as the associated CPU spikes, when using distributed XA transactions in an ATG/JBOSS application server to Oracle database environment. Oracle “support” was, per usual, of absolutely no help is diagnosing the situation. I honestly…
Quick way to trace a session on a server
If you identify an Oracle database process consuming a high amount of CPU, or want to trace it for any reason, you can simply run the following script and pass the PID of the offending process when requested. begin for…
Schema compare command line tool
I use what is below to quickly compare two Oracle database schema’s tables. It would be trivial to add indexes to the mix. import java.sql.*; import java.util.*; public class schemaDiffer { public static void main(String args[]) { try { Class.forName(“oracle.jdbc.driver.OracleDriver”);…
Simple dbca command line creation
dbca -silent \ -createDatabase -templateName “General_Purpose.dbc” \ -nodelist “expressdb1,expressdb2” \ -gdbName express.home \ -sid express \ -sysPassword ExpressCMH1 \ -systemPassword ExpressCMH1 \ -emConfiguration NONE \ -datafileDestination +DATA \ -redoLogFileSize 50 \ -storageType ASM \ -asmsnmpPassword ExpressCMH1 \ -characterSet AL32UTF8 \…
opatch “patch not applicable”
I saw the exception in the subject title of this post this earlier this week while applying the most recent PSU. Of course, the problem had absolutely nothing to do with whether or not the patch was applicable to the…
Exhausting the heap on a JDBC batch with huge size
This is to be expected, but I had never seen it. I increased the batch size on a JDBC batch insert to 100,000 (yes, go ahead and laugh :)), and hit the exception below. As I noted, it makes sense,…