Why shouldn’t you be able to look at a report that shows details about a session’s open cursors? This query reports elapsed time, cpu_time, #executions, average execution time, sql_text, and a couple of other columns.
You need to remember that
- these figures could include significant contributions from executions by other sessions
- the session might have spent significant time running queries whose cursors are now closed
So why am I bothering with this post?
This doesn’t have anything to do with database performance, but it’s useful. Here are a set of privileges that I’m granting to accounts that are used to manage Oracle logins. These allow the user to set up similarly privileged users as well as ordinary business users.
Where I work, Oracle E-business users don’t log in to separate database accounts, instead they’re subjected to some sort of up-front authorization checks and then their programs are run by apps or applsys or whatever generic account is used for these things. However the user login information can be found in the fnd_signon_audit_view, which can be joined with the v$ views. This script accepts a specific username and shows the user’s sessions. This query makes it simple to look at a particular user’s session and draw useful conclusions like “the slowness this user is experiencing isn’t in the database.”
Here’s a little script that looks in the shared pool(s) for queries that are using sql profiles.
Here’s a sort of obvious script that shows the objects referenced by your query’s execution plan(s) along with information about the number of rows, when statistics were last gathered, and information from dba_tab_modifications about what has happened to those objects since statistics were last gathered. If you’re going to copy the sql but not the shell script, you’ll need to remove “\” from the references to the gv$ views.
The script doesn’t show table stats for indexes’ tables, that is, if your query’s plan references an index but the index’s table isn’t in the plan, then the index’s table’s statistics aren’t displayed. I’ll change that one of these days, hasn’t seemed necessary so far since I’ve only used it to check staleness, whatever that is.
Oracle’s coe_xfr_sql_profile.sql script is a nice piece of work, as has been blogged elsewhere: but it breaks when presented with queries that are too long, something to do with the maximum length of a quoted string in pl/sql, I believe. [ Update March 23, 2012, the script is being upgraded to handle long queries, it will be released on April 2. I've tested it, it worked well on one of my production queries and on a 40k test query. ] If you look at the coe_xfr-generated sql script for a really long query, say 40,000 characters, you’ll see a long assignment statement to a variable named sql_txt. That’s the string that gets too long. A workaround for this is to have the generated code build the sql statement the way it’s done in this example which shows the first part of a large Discoverer query broken up into small chunks, yet still being all appended to the same clob:
I was curious about why the 11.2 grid infrastructure documentation for a “typical” install includes asmlib. I’m even more curious after searching the internet – it seems that asmlib is an unpopular feature. Grid/Infrastructure documentation says it’s good for persistent device names and for managing permissions. In a relatively small amount of internet searching I haven’t found anything that says asmlib is so great that you should start using it right away.. and I’ve found some strong opinions against asmlib, and some apparently uninterested parties that point out it’s not used much and that there’s not a really strong reason to use it.