Source: mcol_histogram.sql
When running an OLTP system you may have a transactions table with things like transaction ids, transaction types and states of transaction (open, closed, cancelled, etc). Different transactions tend to have different usage patterns – some might be automatically dealt with, others require user interaction, some typically rejected and still others where a [...]
Source: trigger.sql
If you are in a mutating table situation where your trigger needs to read from the same table that it is firing on your only real option before Oracle 11g was a multi trigger solution with a muddle of row and statement triggers communicating via a package. But now Oracle 11g comes through [...]
I had hoped to come up with a neater version of my Weekly Transaction report using the new PIVOT function in Oracle 11g but I wasn’t able to come up with anything much better primarily due to:
ORA-56901: non-constant expression is not allowed for pivot|unpivot values
So let’s take a look at the revised version of the [...]
Just a little one for today. This table will tell you how much space the top 5 objects owned by SYS (for example) are taking on disk:
SELECT * FROM (
SELECT segment_name, segment_type, SUM (BYTES) / POWER(1024,3) gb
FROM dba_segments
[...]
OraSRP takes trace files and, like tkprof, turns them into a more human-readable form but better because it provides results in HTML and shows bind variables. You will need to get a level 12 (bind variables and wait events) 10046 trace file to get the most out of OraSRP. If you are trying [...]
The performance benefits of using BIND variables are easily illustrated with simple thought experiment. In your language of choice write a complete SQL parser which also analyses statistics and comes up with an execution plan. Ready? Go! Now how long does that parser take to tokenize the queries? Parsing and [...]
The title of this post implies use of Stored Outlines where you capture the current plan of all running queries and then use those plans to force execution plans in the future. Another way to ensure plan stability is simply to not analyze your tables. For large tables which have a lot of [...]
When you want to retrieve SQL statements from the SGA you can query the v$sqlarea and extract the sql from the sql_text … but there’s a catch. This column is only 1000 characters and truncates anything longer:
Note that we had to look for “v_$sqlarea” rather than v$sqlarea because the latter is a synonym for [...]
Using WITH allows Oracle to get the result set once, store it and reuse it. The SQL is also shorter. Let’s generate a random sequence as an inline view using the WITH clause and then use it twice. If the query were being recalculated we’d see that the random numbers would be [...]
To demonstrate the MIN and MAX functions let’s create a global temporary table with an index and then add some data to the table. Note that you must create indexes on temporary tables when they are empty so you’ll have to TRUNCATE the table if there is anything in it. Let’s create the [...]