60000 bind variables?! Maybe it’s time to use a temporary table instead…

February 25, 2009 by 18 Comments

I just noticed a bug 8277300 filed in Metalink with following description:

ORA-7445[XTYQBCB] OCCURS DURING EXECUTING SQL THAT USES 60000 BIND VARIABLES.

Wow! That’s about 100 times more bind variables in a single query than what I’ve seen in past. And I thought that query was bad!!! :)

I suspect this is a massive IN list passed to a query. Maybe it’s time to use a temporary table or a collection for passing in the IN values instead?

Filed Under: Oracle Tagged With: ,

SQL_ID is just a fancy representation of hash value

February 22, 2009 by 70 Comments

…Or in other words, how to translate SQL_ID to a hash value :)

I once wrote a script to demo this in my Advanced Oracle Troubleshooting class.

Check this, I’ll run a query and then check what is its SQL_ID and HASH_VALUE from V$SQL:

SQL> select * from dual;

D
-
X

SQL> select sql_id, hash_value from v$sql
  2  where sql_text = 'select * from dual';

SQL_ID        HASH_VALUE
------------- ----------
a5ks9fhw2v9s1  942515969

So, V$SQL reports the real SQL_ID and HASH_VALUE above.

Now lets use my i2h.sql script ( i2h stands for: sql Id to Hash value ):

SQL> @i2h a5ks9fhw2v9s1

SQL_ID        HASH_VALUE
------------- ----------
a5ks9fhw2v9s1  942515969

It works! :)

The code itself is simple (and you can download the script from here)

[Read more...]

Filed Under: Cool stuff, Oracle Tagged With:

Performance Visualization, Capacity planning and Hotsos Symposium

February 14, 2009 by 3 Comments

I have slowly moved towards performance visualization and system capacity planning world. Or at least this is my main focus (in addition to deep Oracle internals of course ;)

I’ve published an easy Oracle performance visualization tool, which is based on Excel and can visualize the resultset of any SQL query. It’s called PerfSheet (and I’ve blogged about it here).

Last December I spoke at CMG Conference in Las Vegas about a new method for visualizing database-wide performance while still being able to see session level performance profile. Having session level performance overview is important when diagnosing performance problems which only some users of the whole system are experiencing.

The paper I was talking about is accessible from here: http://arxiv.org/pdf/0809.2532

We wrote it together with Dr. Neil Gunther, the theoretical part was his idea and I helped to apply it in Oracle.

Neil received the prestigious A.A. Michelson’s award during that CMG event, so I was pretty honoured to even be at the same stage with him :)

 

I will be presenting some further peformance visualization and capacity planning related work during my “No slides” session at Hotsos Symposium this year. I’m excited as I will be showing some really cool (and useful!) things there ;)

Filed Under: Cool stuff, Oracle Tagged With: ,

When was a table last changed?

February 7, 2009 by 22 Comments

I frequently get a question about how to find out when was a table last modified. I’m talking about table data, not table structure, the latter would be detectable from dba_objects.last_ddl_time.

Unless you have some table level DML auditing already turned on, then as one option you could use LogMiner and “just” work through the redo/archivelogs in the range of interest with it. This could be very time consuming (especially if the last change was done a while back), so here’s another option for getting last table modification info very easily, however it comes with some restrictions.

http://www.tanelpoder.com/files/scripts/lastchanged.sql

(read instructions and limitations from the script header).

The idea is following:

  1. Oracle has an ORA_ROWSCN pseudocolumn which reports the last known change time for a row in a table. The “time” shows a commit SCN number of last transaction modifying the row, not a real timestamp though. It is important to note that unless the ROWDEPENDECIES are enabled, then the last SCN is known only at data block level, not row level, rowscn’s for all rows in a block would report whatever SCN is in the last change SCN in block header.
  2. SCN is ever-increasing internal “time” used by Oracle recovery and transaction layers and it is possible to map this to real time with reasonable accuracy using few Oracle’s tables which store SCN to wallclock time mappings. My script reports the time range in which the last change to datablock/row occurred so you’ll know how accurate it is.
    I use sys.smon_scn_time and v$log_history views for SCN to real time mapping. So my script reports two times, first one may be more accurate, second one has longer history on the other hand.You can use whatever other datasource for doing this mapping, as long as it has SCNs and corresponding timestamps in it. For example, if you have log_checkpoints_to_alert parameter set to true, you can grep the SCN/timestamp pairs out with command like this:

    cat alert_win10g.log | egrep -e "^Beginning.*checkpoint|[[:alpha:]]{3} [[:alpha:]]{3} [[:digit:]]{2} "

Here are few usage examples.

First I’ll check some data which was probably last changed a long time ago:

SQL> @lastchanged sys.obj$ name='DBMS_STANDARD'

-- LastChanged.sql v1.0 by Tanel Poder ( http://www.tanelpoder.com )

Running this query:

.   select MAX(ora_rowscn)
.   from sys.obj$
.   where name='DBMS_STANDARD';

DATA_SOURCE       LAST_CHANGED
----------------- -------------------------------------------------------
sys.smon_scn_time Before  2008-12-31 16:05:25 (earlier than 21 days ago)
v$log_history     Before  2008-10-27 03:58:16 (earlier than 86 days ago)

2 rows analyzed.

As both sys.smon_scn_time and v$log_history don’t have records dating back to that old time when the database was created (when DBMS_STANDARD and other object records in that datablock were created), they just show that the change happened before the oldest SCN to time mapping they have.

Here’s a check on a regular table, but we are only interested in rows/datablocks where the “sal” column is bigger than 1000:

[Read more...]

Filed Under: Oracle Tagged With: , ,