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 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 [...]
The lead and lag functions offer the power to peek into adjacent rows and pull values from next or previous rows into the current row for processing. They are a convenient way of accessing multiple rows and cut processing time compared to the alternative of self-joins. Here’s a simple dataset to work [...]
No, on 9i it evaluates and fails on a subquery it had no business looking at since the first argument isn’t NULL:
SQL> SELECT COALESCE(’Not NULL’,
(SELECT ‘True short circuit’ FROM dual
UNION
SELECT ’should ignore this’ FROM dual))
FROM dual; 2 [...]
Generate a sequence of numbers with a NULL to test:
WITH seq AS
(
SELECT num
FROM (
SELECT LEVEL num
FROM [...]
When running an OLTP system you are dealing with timestamped transactions or messages which you may like to report on. Let’s say you want to check whether the message rates broken down by hour have been consistent over the last week. So let’s start with some sample message times generated randomly:
EXEC dbms_random.initialize(123);
CREATE GLOBAL [...]