We have a shared PLSQL code base between several pieces of application services. Some of them use all of it, some only a few pieces. We also use database services for accounting reasons. I needed to determine which database service…
Category: Database
Why MERGE should be used for even single row inserts
We recently had an issue where a particular insert was generating a large amount of CPU time requirements. When we examined the PL/SQL code, we found the developer had coded the following… begin insert into table values(pk); exception when dup_val_on_index…
Why PGA_AGGREGATE_TARGET is not a hard limit
PGA_AGGREGATE_TARGET is not a hard limit. Lather, rinse, and repeat 🙂 I like to think of it as a tuning suggestion to Oracle as to how much it can allocate for things such as sorts and hash tables. Below is…
bbed password
It never ceases to amaze me how difficult some responders on message boards make it to use certain tools. Requests for information are usually denied with something similar to “If you have to ask that, you shouldn’t be _fill_in_the_blank_”. How…
Gzip in python
We have a large text file containing 325 million integer values that we need to load into a database. The file uncompressed is about 4.5GB. Even though disk is cheap, we didn’t have enough readily available to quickly decompress the…
Handling constraints when truncating a table
Oracle doesn’t supply a TRUNCATE TABLE TABLE_NAME CASCADE CONSTRAINTS syntax structure as it does when dropping a table. The anonymous block below will truncate all tables in the current schema and handle the constraints violations (foreign keys) that may be…
Dumping Oracle DDL part two
Below is a python script that strip the double quotes from the output of dbms_metadata.get_ddl, as well as the storage clauses. The initial clause is often far too high, so this allows you to extract the DDL and run it…
Running log miner from python
Python is so flexible for quick and dirty toolsets, that I built one this morning that invokes the Oracle log miner software to read archived redo logs. It queries the logs for changes to a given table, and prints any…
“resmgr:cpu quantum” preventing logins??
Below is the content of an email I wrote this morning. We could not login to a single instance database due to the statistics gathering job running long. What is odd is that when we tried to troubleshoot, we couldn’t…
Generating chargeback costs using AWR
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…