A question appeared on the Oracle developer forum recently (Oct 2024) that could be answered by a blog note that I started drafting about 18 months ago after answering a very similar question that had appeared in the same forum. Take a look at the following query, with results, about a status column in a ..read more

Some years ago I wrote a note describing how with a query using identical literal values in the predicate, the optimizer was able to produce exactly the same plan body with exactly the same Predicate Information section while producing different cardinality estimates depending on how you expressed the requirement for “column not in list of ..read more

What could make a “create index” statement fail with an error about a table having too many columns (ORA-01792)? And why would this suddenly happen when the code that raised the error had been dropping and recreating the index every night for the best part of 3 years with no problems? This is a question ..read more

This is a small case study from one of the MOS Community Fora (needs a MOS account) that I drafted about a year ago. It started out as a question about a two node RAC system running Oracle 19.17 where an SQL Monitor report showed time spent in a wait for resmgr:cpu quantum . Here’s ..read more

Oracle error 942 translates, for most people, into: “table of view does not exist”. This note is based on a conversation that I had with Mikhail Velikikh on the Oracle-l list server over the last couple of days while looking at the question: “How do I find out which table (or view) does not exist?”. There are several possibilities – none of them ideal – but one suggestion that came up was to take advantage of dumping the errorstack when error 942 was signal: alter session set events '942 trace name errorstack forever, level [N]'; Mikhail suggested using level 2 and then picking up various d ..read more

Here’s a silly little puzzle that baffled me for a few moments until I spotted my typing error. It starts with a small table I’d created to hold a few rows, and then deletes most of them. Here’s a statement to create and populate the table: create table t1 (id number , c1 clob) lob(c1) store as basicfile text_lob ( retention disable storage in row ); insert into t1 select rownum, rpad(rownum,200,'0') from all_objects where rownum <= 1000 ; commit; Here’s what I meant to type to delete most of the data – followed by the response from SQL*Plus: SQL> delete from t1 ..read more

What to do when you hit a problem (possibly after an incomplete recovery) that reports a “unique key violation” on sys.wrm$_snapshot_pk. (As reported recently in this thread on the Oracle database forum.) Snapshot ids are carefully sequenced, without gaps, so somehow the thing that controls the “current” sequence number has gone backwards and is trying to generate a value that is lower than the current highest value in wrm$snapshot. The thread I referenced above does point to an article dated 2017 on Alibaba discussing methods of checking for corruption and clearing up messes; but as an extra ..read more

Here’s a detail about dbms_output that is probably overlooked because (in most cases) it’s ignorable, except that it can lead to unexpected response times when you try using it to debug “busy” operations. A question on the Oracle SQL and PL/SQL forum asked: “Why is a PL/SQL ‘for loop’ so slow on Oracle Autonomous DB?” and provided the following code to demonstrate the issue. begin for i in 1..36000 loop dbms_output.put_line ('i value: '|| i); end loop; end; / The OP reported the elapsed time for this block as 1 minute 40 seconds (compared to Java taking only ..read more

In the second part of this series I described some of the technicalities of Index Usage Tracking and showed an example of what I was doing to test the feature. In this episode I’ll describe some of the index access methods I’ve tested and report the results. I’ve listed the tests I’ve planned so far and will show results as I find time to run the tests – if you can think of more cases add them in the comments and I’ll extend the list. (If you think a test is a very good idea, “upvote” it in the comments and I’ll try to run it sooner. Before I’ve done all the tests I’ll add a section on Conclus ..read more

In the first part of this series I introduced Index Usage Tracking and the view dba_index_usage – a feature that appeared in 12.2 as a replacement for index monitoring and the view dba_object_usage. In this note I’ll give a quick sketch of the technicalities of the implementation and comments on how to test and use the feature. Actual tests, results and observations will have to wait until part 3. A not very deep dive There are three parameters relating to Index Usage Tracking (iut), shown below with their default values: _iut_enable [TRUE] _iut_max_entries [30000] _iut_stat_collection_type ..read more