Python
Additional cursor trace information in 11.2.0
Oracle has added a lot of additional trace output in the 10046 optimizer trace files. As I have posted previously, I find the output in these files to be useful not only for troubleshooting performance issues, but also understanding the business flow of the application in cases where I don’t have access to the... »
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. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21... »
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 sometimes idle wait events can be important, but if they are, they can usually... »
Simple usage of a user profile to limit concurrent logins
We had an issue in a development database where a group of developers were not using a connection manager, but trying to manage their own connections. This did not end well. To limit our exposure and the impact on other application users, we implemented a simple profile for the problematic user. A simple test... »
Which service is executing a given SQL statement
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 was executing a given SQL statement while we plan a migration that may impact... »
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 file. As such, I used the python gzip module to decompress the source file... »
Python multiple producer/consumer queue
Below is a simple skeleton for a python queue that is both fed by and consumed by multiple different threads. We run five threads, each of which populate a queue with random integers. Lastly, we sleep a couple of seconds between each put, to simulate an ongoing workload. Our popper thread, which reads items... »
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 wherever you like. You can call it as follows: 1 ./getDdl.py case_insensitive_user_for_which_to_get_tables_and_indexes 1 2... »
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 records it finds. The version below needs to be enhanced to have search filters... »
Quick way to compare two python lists
***Please read the entire post if you are looking for a fast way to compare two lists*** If you need to compare two lists, you can use a list comprehension such as the following: 1 2 3 4 5 >>> a=[1,3,5] >>> b=[1,2,3,4,5,6] >>> print [c for c in b if c not in... »