While troubleshooting a high CPU utilization problem recently, I found that half the sessions using a given database service were using a high amount of CPU, while the other were using a normal amount. Since I knew they were each…
Category: Oracle
Help! Select queries are…filling up my redo logs?!!
It is theoretically possible (although unlikely in a well managed system) to cause a large amount of transaction logging to occur if you do something as simple as update a row in one session, don’t commit, and repeatedly query that…
Proving Oracle’s read consistent model
I was asked a good question (by a real doubter) asking me to prove Oracle’s ability to provide consistent reads. However, in the past I have been surprised at the number of fairly senior Oracle professionals that can’t come up…
Runtime connection load balancing issues in 11.2.0.2
We upgraded our largest and most important databases last month to 11.2.0.2 from 10.2.0.4. We had a few issues, most of which have been resolved. However, one that continued to plague us until yesterday is the apparent inability of any…
11g compiler warnings
The PL/SQL compiler warnings have been enhanced in 11.2.0.1 and up to include a warning for the dreaded “when others then null” exception handler. Actually, the warning is raised any time an “others” is handled (even if it is logged…
Querying load balancing information from the Load Balancing Advisory
I use what is below to periodically gather what the load balancing advisory posts. The data reflects the percentage of connections to distribute to each instance for a given service in a RAC. #!/home/oracle/local/bin/python import cx_Oracle, string conn = cx_Oracle.connect(mode…
Simple example of a type stored in a table
Nothing fancy today, this is just a placeholder for using object types as a column in a table. SQL> create type mytype as object (p_name varchar2(20),p_age number); 2 / SQL> create table type_table(c mytype); Table created. SQL> insert into type_table…
Disabling LOGGING on a LOB
In Oracle 10.2.0.4, we were doing a large data migration of archive records. Initially, we could recreate the data from the source, so our plan was to: copy rows nologging to destination database backup destination database truncate tables in the…
Which SQL statement is responsible for a wait event?
We generate a daily health check report for each database that contains high level summaries of the previous days activities. One element of this report is a list of the top ten non idle wait events (disclaimer: I know that…
Useful script to print session gv$sesstat changes over time
Oracle provides a plethora of tools for monitoring user activity, including tracing, monitoring wait events, as well as ASH. However, sometimes I want a quick (literally) overview of a session. To do this, I query the gv$sesstat view. However, the…