Here’s a hidden threat in the optimizer strategy that may cause performance problems if you’re trying to operate a series of batch updates (or batch deletes).
In the past I’ve pointed out that a predicate like “rownum <= N" generally makes the optimizer use “first_rows(N)” optimisation methods – known in the code as first_k_rows optimisation.
This isn’t true for updates and deletes, as the following simple example indicates:
(more…)
Here’s a little trick I’ve only just discovered (by accident). What’s a tidy way to extract the fractional part from a number ?
Running 11.1.0.6 (I haven’t checked earlier versions yet – or looked at the manuals for the definition of the mod() function):
SQL> select mod(1.34,1) from dual;
MOD(1.34,1)
-----------
.34
1 row selected.
SQL> select mod(-1.34,1) from dual;
MOD(-1.34,1)
------------
-.34
I really didn’t think it would make sense to use an expression like mod(p,1) – but it’s pleasant surprise that is does what it does.
Here’s the output I get from querying dba_tab_histograms for a column that has been given a frequency histogram by a call to dbms_stats.gather_table_stats().
(more…)
One of the simple scripts I use from time to time monitors the amount of undo generated by a long running session by taking a session id (SID) as an input and joining v$session to v$transaction:
column start_scn format 999,999,999,999
select
tr.start_scn, tr.log_io, tr.phy_io, tr.used_ublk, tr.used_urec, recursive
from
v$session se,
V$transaction tr
where
se.sid = &1
and tr.ses_addr = se.saddr
;
START_SCN LOG_IO PHY_IO USED_UBLK USED_UREC REC
---------------- ---------- ---------- ---------- ---------- ---
136,509,466,121 10730956 358074 1 1 NO
136,515,115,543 12 0 1 3 YES
You’ll note that this is a session that has been doing quite a lot of work (log_io = db block gets + db block changes, and phy_io = physical reads). I was watching it because I wanted to check that it was only generating undo in a series of very short recursive transactions. (It’s running a coalesce on a very large, very smashed, index).
Interestingly the work done by the recursive transactions for the coalesce was accounted for against the parent transaction, even though (fortunately) the undo block and record usage was not. The used_urec and used_ublk columns can best be described as showing you “currently held” space, rather than “cumulative space used”.
Addendum: with reference to Gary’s comment below – here’s a lovely little script – originally written for 9i, but still working) from Steve Adams for estimating how long it will take for rolling back to complete. Note that it references sys.x_$ktuxe; Steve has a convention of creating “x_” views on the x$ objects when there is no v$ view supplied by Oracle, so x_$ktuxe is just a sys-owned view defined as ‘select * from x$ktuxe’.
WordPress allows me to see statistics about “referrers” – the places that people came from before getting to my blog. This can be interesting, and potentially useful, information.
From time to time the referrer is the “WordPress Dashboard” – which I think means that my blog has appeared on the WordPress front page as one of the more popular blogs on wordpress.com. That’s a rather nice feeling. In fact some recent stats on WordPress say that it gets a little over 2 billion page views per month on 11.4 million blogs. Since my blog currently gets about 50,000 page views per month I’m responsible for about 1/40000 of the total page views … which is well above average.
There is a downside, though, to appearing on the WordPress dashboard – there seems to be an interesting correlation between the days I hit the WordPress dashboard and the days when I see spam comments climb to high values; fortunately Akismet manages to filter out almost all the spam, but I wonder if there are packages on the market that keep checking for popular blogs then automatically start generating spam comments on them.
(more…)
I find it convenient occasionally to “translate” a frequency histogram into a report of the underlying data (sometimes to see how closely the histogram matches the real data). To demonstrate the type of query I use I’ve created a data set with a small number of distinct values and generated a frequency histogram on the data set. This is what the data and histogram look like:
(more…)
There’s a thread on OTN that talks about a particular deletion job taking increasing amounts of time each time it is run.
It looks like an example where some thought needs to go into index maintenance and I’ve contributed a few comments to the thread – so this is a lazy link so that I don’t have to repeat myself on the blog.
SimpleTalk have just published another of my SQL Server articles – which looks at the way that SQL Server stores data in “clustered indexes”, and the variation that appears depending on the pattern of data.
Inevitably there’s still excitement and uncertainty in the air about Exadata – and/or the Database Machine. For ease of reference and a jolly good read I’ve started collecting a few blog postings about it:
I’ve written before about the effects of subquery factoring (common table expressions – or CTEs) on the optimizer, and the way that the optimizer can “lose” some strategies when you start factoring out subquery expressions. Here’s another example I came across quite recently. It involved a join of about 15 tables so I’ve only extracted a few lines from the SQL and resulting execution plans.
We start with the original query, which had factored out an aggregate subquery then used it in place of an inline view:
(more…)
Here’s an unexpected error that appeared recently while I was doing a test on a database running 11.1.0.6. (The fact that I got an error didn’t surprise me, it was Oracle’s choice of error for the mistake I’d made.)
Cut-n-Paste from a SQL*Plus session:
SQL> alter system set db_cache_size = 256m scope = memory; alter system set db_cache_size = 256m scope = memory * ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid ORA-00001: unique constraint (.) violated
If you’re wondering, I was trying to reduce the db_cache_size from 384MB and the value had been set by the startup parameter file – so the ORA-02097 is probably trying to tell me that I can’t reduce the dynamic value below the initial minimum. (I didn’t check this, I just bounced the database with a change of parameter file, but it’s probably a reasonable guess.)
That’s “Block Change Tracking” if you’re not familiar with the acronym – a feature that appeared in the 10g with its own background process (CTWR – change tracking writer) to help rman do faster incremental backups on very large datafiles.
It’s very useful if you’re using large tablespaces (i.e. the “one file per tablespace”) option – but, as with many things Oracle produces, when you push the limits or move into combinations of features odd errors start to appear.
Saibabu Devabhaktuni has written a blog note about using BCT with physical standby databases – a relatively new enhancement to BCT, but possibly one you need to be cautious about for reasons he explains.
Well, it surprised me!
I’ve said for a very long time that in principle, ignoring bugs and restrictions, the optimizer will always choose the lowest cost option during its search for an execution path. It turns out that this isn’t true. In a comment attached to a note I had written about a possible bug relating to function-based indexes I was told that there are cases where the optimizer follows a rule that allows it to ignore the lowest cost path if it is derived from a range-based predicate involving unpeekable bind variables.
(more…)
Here’s a little detail that I’ve known for years – but keep forgetting until I embarrass myself by rediscovering it (usually in front of a client). I’ll start with a demonstration of a useful little feature of mandatory columns:
(more…)
Have you ever wondered if there’s a way to see when the data in a table was last modified ? Depending on what you’re really trying to achieve, how accurate you want the answer to be, what resources you’re prepared to use, and how you’ve configured your database, you may be able to get an answer that’s good enough very easily.
(more…)
Postings by RSS Comments by RSSTheme: Rubric. Blog at WordPress.com.
