A recent post on the OTN forum asked:
I was wondering is there any fast method for updating 8 million records out of 10 million table? For eg :
I am having a customer table of 10m records and columns are cust_id, cust_num and cust_name.
i need to update 8m records out of 10m customer table as follows.
update customer set cust_id=46 where cust_id=75;
The above statement will update 8m records. And cust_id is indexed.
If you’ve ever looked at view v$sql_bind_capture, you’ve probably seen that (at least in 10.2) it allows you to see the actual values for bind variables that have been used with an SQL statement.
For a long time I had assumed that this view was capturing the bind values used when the optimizer peeked at the variables when it wanted to produce an execution plan.
But I’ve just seen a note on the OTN forums from Dion Cho that highlighted my error. The note describes a couple of parameters that affect the way that bind capture behaves – including a parameter to change the “interval (in seconds) between two bind capture for a cursor”.
Sure enough, by using an ‘alter system’ call to change the “_cursor_bind_capture_interval” to a (performance-threatening) 10 seconds on a small test system, I found that the values in v$sql_bind_capture would change fairly regularly as I re-executed a given query with constantly changing input bind values. (With no change to the “peeked_binds” output from dbms_xplan.display_cursor() ).
[Back to part 1][Forward to part 3]
In the first part of this series, I showed you how a query by hash key against a sorted hash cluster would return the data in an order dictated by the sort key – without showing a sort operation in the execution plan, even in the absence of an ‘order by’ clause.
There is a recent thread [ed Jan 2009: the following link is temporarily broken] on the OTN forum about block sizing and the impact it may have on performance. The thread contains some interesting insights into the problems of testing and trouble-shooting. Unfortunately the thread has accumulated so much extraneous material that it has become hard to separate the wheat from the chaff.
[Ed Jan 2009:
According to this comment from Justin Kestelyn, extremely long messages were causing performance problems in the current version of the forum software and have have been temporarily archived to a separate location – and this thread contained some very long messages
Since it may be some time before it reappears on the forum I’ve posted a copy made just before it disappeared, so that (until the original reappears) you can read the interesting bits here. Warning – this is a pdf file of around 3MB.
The file will open in a separate window so that you can read the file and my blog about the file at the same time. When I view the file, it comes to 233 pages, so I’ve added page references after the links in the blog (the links, of course, do not currently work).
Towards the end of April, I published a note about manual optimisation, and mentioned in one of the comments (#19) that as part of the discussion of the (slightly suspect) mechanism I had introduced I would eventually get around to talking about sorted hash clusters. So I’ve finally managed to make a start.
This (Oracle-related) blog site caught my eye a few days ago. There isn’t much on it at present, but what’s there is worth reading, so I thought I’d post a pointer to it for a few days.
Updated 13th July. Randolf Geist has started to work his way through the test scripts that I supplied with my book Cost Based Oracle – Fundamentals, posting details of any interesting changes on his blog. The first results are on a posting dated 27th June – currently the second posting.
I’ve just been browsing through a subdirectory on my laptop that has been copied from machine to machine over the years – even though some of the material needs programs that probably don’t exist any more.
This slide, from a presentation I gave at the UKOUG annual conference in 1996, raised a wry smile. The title of the presentation was “Fact, Folklore, or Fairy-table” – posisbly the first ever “Mythbuster” presentation on the Oracle scene. The title of each slide declared a popular belief, and the rest of the slide commented on the sense (or lack thereof) in the belief.
Postings by RSS Comments by RSS