If you you want to get an idea how some feature may be implemented by Oracle, then you ought to have a look for patents. Here are several examples I find quite interesting and containing valuable information:
You’ve probably heard about adaptive cursor sharing, and possibly you’ve wondered why you haven’t seen it happening very often on your production systems (assuming you’re running a modern version of Oracle). Here’s a little bug/fix that may explain the non-appearance.
MOS Doc ID 9532657.8 Adaptive cursor sharing ignores SELECTs which are not fully fetched.
This bug is confirmed in 184.108.40.206, and fixed in 220.127.116.11. The problem is that the ACS code doesn’t process the statistical information from the cursor unless the cursor reaches “end of fetch” – i.e. if you don’t select all the data in your query, Oracle doesn’t consider the statistics of that execution when deciding whether or not to re-optimise a statement.
In the previous post I wrote about strangely behaving V$SQL. For some reason there were duplicate rows leading to wrong results issue when running DBMS_XPLAN.DISPLAY_CURSOR for a particular child cursor. I tried to reproduce the issue using simple test case – and it was reproduced.
Here it is. I’m starting to execute a simple query against DUAL while constantly changing optimizer environment, forcing Oracle to build a new child cursor for each execution:
I just did an update from Oracle Linux 5.7 to 5.8 on one of my VirtualBox RAC installations and things are not looking to clever at the moment. After a reboot, the ASM instances and therefore the database instances wouldn’t restart. A quick look showed the ASM disks were not visible. On this installation I was using UDEV, rather than ASMLib. In checking the UDEV rules I noticed the scsi_id command on OL5.8 doesn’t report an ID for partitions on disks, only the disks themselves. For example, on OL5.7 I get this,
# /sbin/scsi_id -g -u -s /block/sdb/sdb1 SATA_VBOX_HARDDISK_VBd306dbe0-df3367e3_ #
On OL5.8 I get this,
# /sbin/scsi_id -g -u -s /block/sdb/sdb1 #
If I run it against the disk, rather than the partition it works fine.
The column is there for a long time – even 9i documentation have it. I’ve never thought about it until today when I caought something extraordinary on 18.104.22.168 instance.
Starting with 10g oracle introduced SQL_ID for simplicity; it is used in combination with CHILD_NUMBER to locate a particular row in the V$SQL. The documentation is clear (bold is mine):
V$SQL lists statistics on shared SQL areas without the GROUP BY clause and contains one row for each child of the original SQL text entered
Quick post congratulating Gwen Shapira on becoming Oracle ACE Director. Gwen has be an Oracle ACE for a while by now and been very active in the community. Widely recognized in the conferencing circles and a frequent blogger, Gwen has recently been focusing a lot on Big Data and many of her recent articles have [...]
A couple of weeks ago I wrote about my invite to the University of Birmingham CS Alumni Dinner, which took place last night…
It was really weird being back on the campus after all these years. I finished my PhD about 18 years ago (or something like that) I popped in to my department (Biosciences) a couple of times soon after I left and I used to train at the University gym for a little while, but it must be over a decade since I’ve been back. I don’t know about the rest of the campus, the part where I spent most of my time as changed a lot. Very freaky.
Just like my posting on an index hash, this posting is about a problem as well as being about a hash join. The article has its roots in a question posted on the OTN database forum, where a user has shown us the following execution plan: