One of the best things about AWR is the fact that it tracks CPU at a database service level. This allows us to calculate the amount of CPU used by a service in any number of ways. Once you can…
Category: Oracle
Careful with that append hint!!
Today a DBA issued an insert statement with an append hint. This resulted in a fairly time consuming insert of many rows. The DBA was puzzled when all other sessions were blocked from inserting into the table. This is actually…
Fined grained dependency – Down with select *!!!
In 11G, I would like to suggest that you begin to move away from select * in your PL/SQL views and hardcoded procedure/function cursors. The reason I suggest this is that versions 11.1.0.6 and higher support “fine grained” dependency checking.…
Who is the big load on my server?!
What is below will show SQL statements for a given window (the last four days in the example below) that are tightly correlated with load average on a server. In general, a statistical correlation greater than 60 means there is…
A simple example of a custom component managed by CRS
You can add custom components to CRS if you have a need. You basically need a program (perhaps as simple as a shell script) that accepts three command line arguments: start stop check Below is an example of a shell…
Redo on unchanged column values
I recently spoke with an Oracle DBA. He mentioned they had a third party application that was generating gobs of redo by updating rows to their existing values, i.e., update t set c = c. This bothered me, as my…
Parsing hanganalyze output for blocking sessions
The hanganalyze command was introduced in Oracle 8i. It is very useful, especially to mere mortals like us. It is formatted in a human readable format. In contrast, a systemstate dump normally contains a lot of memory addresses and offsets,…
Converting SCN to decimal
I found a slick little script at the following URL for converting an SCN to decimal. http://www.bluegecko.net/oracle/converting-hexadecimal-oracle-scns-to-decimal/ You can take the raw SCN and substitute it below… scn=0x0623.02021908;echo $((${scn%%.*}*4294967296+0x${scn##*.}))
Yet another reason to use Oracle’s JDBC connection manager
Over the weekend we had an odd issue. We have four nodes in a cluster, with two core services. SERVICE_A runs on servers 1 and 2, while SERVICE_B runs on server 4. Server 3 is effectively idle most of the…
How to “unregister” a service from a listener
If a service is registered with your listener outside of the clusterware, you can unregister it with the following call: dbms_service.stop_service(service_name,instance_name) I verified that this will not disconnect existing sessions in the instance on which the service is being stopped.…