I have, in the past, used the dbms_rowid package to create rowids from block addresses (typically faking the first and last rowids that could appear in an extent); but I’ve just been sent a piece of information by Valentin Nikotin that’s going to make me go back and check whether (more...)
A recent question on the OTN database forum included an execution plan that prompted one reader to ask: “but where has the existence subquery gone?” Here’s the original question showing the query, and here’s the later response showing the plan that prompted the question.
There were three possible reasons (more...)
Oracle 12c has increased the maximum length of character-based columns to 32K bytes – don’t get too excited, they’re stored out of lines (so similar in cost to LOBs) and need some modification to the parameter file and data dictionary (starting the database in upgrade mode) before you can use (more...)
Here’s one of those little details which I would have said just couldn’t be true – except it’s in the manuals, and the manuals happen to be right.
create table t1 pctfree 75 pctused 25 as select * from all_objects where rownum <= 10000; create table t2 pctfree 75 pctused (more...)
Here’s a question that appeared recently on OTN, and it’s one I’ve wondered about a few times – but never spent any time investigating. Are there any overheads to enabling row movement on a table ? If not, why is it not enabled by default and eliminated as an option ?
There an interesting example of a deadlock on the OTN database forum:
DEADLOCK DETECTED ( ORA-00060 ) [Transaction Deadlock] Deadlock graph: ---------Blocker(s)-------- ---------Waiter(s)--------- Resource Name process session holds waits process session holds waits PS-00000001-00000011 92 423 S 33 128 S X BF-2ed08c01-00000000 33 128 S 92 423 S X
Here’s the output I got from a 10.2.0.5 system after generating a stored outline on a query – then dropping the index that was referenced by the stored outline and creating an alternative index. Spot the problem:
Index dropped. Index created. -- run query -- call to (more...)
Here’s an odd little optimizer glitch – probably irrelevant to most people, but an indication of the apparent randomness that appears as you combine features. I’ve created an example which is so tiny that the only explanation I can come up with the for optimizer not “behaving properly” is that (more...)