Learning is not a spectator sport
Connor McDonald on SQL and the Oracle Database
HIGH_VALUE (and other LONG columns) revisited
Just a quick post to ensure people don’t get caught out by a recent “trick” I saw on an AskTOM question for extracting the HIGH_VALUE out of it’s LONG storage in the dictionary to a more appropriate datatype. A reviewer (I’m sure in good faith) posted the following mechanism to extract the HIGH_VALUE
SQL> create table t1 ( x timestamp )
2 partition by range ( x )
3 ( partition p1 values less than ( timestamp '2019-01-01 00:00:00' ),
4 partition p2 values less than ( timestamp '2019-02-01 00:00:00' )
5 );
Table created.
SQL>
SQL> set serveroutput on
SQL> declare
2 v long;
3 d date;
4 begin
5 for i in ( select high_value from user_tab_partitions where table_name = 'T1' )
6 loop
7 execute immediate 'select cast(:hv as date) from dual' into d using i.high_value;
8 dbms_output.put_line(d);
9 end loop;
10 end;
11 /
01-JAN-19
01-FEB-19
PL/SQL procedure successfully completed.
You can see that we are casting the HIGH_VALUE as a date, and voila, out pops the values in a nice DATE datatype. That all seems well and good, but it only works for TIMESTAMP partitions and not the general case. Let’s replace our table with a DATE based partitioning scheme
SQL> drop table t1 purge;
Table dropped.
SQL> create table t1 ( x date )
2 partition by range ( x )
3 ( partition p1 values less than ( date '2019-01-01' ),
4 partition p2 values less than ( date '2019-02-01' )
5 );
Table created.
SQL>
SQL> set serveroutput on
SQL> declare
2 d date;
3 begin
4 for i in ( select high_value from user_tab_partitions where table_name = 'T1' )
5 loop
6 execute immediate 'select cast(:hv as date) from dual' into d using i.high_value;
7 dbms_output.put_line(d);
8 end loop;
9 end;
10 /
declare
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at line 6
ORA-06512: at line 6
There are plenty of methods to overcome the limitations of LONG. Here’s the one I typically use – just being a small variant on the code above
SQL> set serveroutput on
SQL> declare
2 d date;
3 begin
4 for i in ( select high_value from user_tab_partitions where table_name = 'T1' )
5 loop
6 execute immediate 'select '||i.high_value||' from dual' into d;
7 dbms_output.put_line(d);
8 end loop;
9 end;
10 /
01-JAN-19
01-FEB-19
PL/SQL procedure successfully completed.
which I’ve also covered in video form here.
But just be careful using the CAST trick. It might cause you grief depending on the partitioning key you are using.
And if you’re wondering why the image associated with this post is that of a horse. It’s the old joke: “A horse walks into a bar, and the barman says: Why the LONG face?” 🙂
Same dog, different leash – functions in SQL
Let’s start with this somewhat odd looking result. I have an inline function that returns a random number between 0 and 20, and I call that for each row in ALL_OBJECTS and then I extract just those rows for which the generated random number is 10. Seems simple enough….but why do I get results for which the value of the second column is most certainly not 10?
SQL> with
2 function
3 getrand(pval in number) return number is
4 begin
5 return round(dbms_random.value(0,20));
6 end;
7 my_rnd as
8 ( select object_id, getrand(object_id) getrnd from all_objects )
9 select *
10 from my_rnd
11 where getrnd = 10;
12 /
OBJECT_ID GETRND
---------- ----------
29 1
53 10
42 8
30 3
78 16
87 18
89 1
145 12
155 13
175 15
183 12
198 15
...
...
This is just a new variation of the same old theme – determinism.
There is no guarantee when and how many times the database will execute a function within a single invocation of a SQL statement, or even how many times it will be executed for a single candidate row the SQL statement is processing. The optimizer is totally entitled to shift it to anywhere in the plan. What does this mean ? It means only deterministic functions make sense in SQL. Let’s model the above with a simpler example:
SQL> create table t as select * from dba_objects where rownum <= 20;
Table created.
SQL> create or replace
2 function xgetrand(pval in number) return number is
3 begin
4 return round(dbms_random.value(0,20));
5 end;
6 /
Function created.
SQL> select *
2 from (
3 select object_id, xgetrand(object_id) gg from t
4 )
5 where gg = 10;
OBJECT_ID GG
---------- ----------
38 2
6 8
Immediately that looks odd, because the SQL reads like “I only want GG=10” yet I got back “2” and “8”. But if we drill into the execution plan, we’ll see this:
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 3 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("XGETRAND"("OBJECT_ID")=10)
Notice the FILTER information. The query has been transformed to be a simple:
SQL> select object_id, xgetrand(object_id) gg from t
2 where xgetrand(object_id) = 10;
If you’re doubtful of that claim, we can validate it by dumping a 10053 trace. In that you’ll find the following:
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T"."OBJECT_ID" "OBJECT_ID","XGETRAND"("T"."OBJECT_ID") "GG" FROM "T" "T" WHERE "XGETRAND"("T"."OBJECT_ID")=10
Already you can see the scope for the function being called twice per row – once for the WHERE clause, and once for the SELECT part. In fact, for all we know it could be called three times, or four times. You (the coder) does not have control over that decision. For the sake of discussion, let’s assume it is called only twice. The first execution of the function (on line 2 above) returned 10 twice across the 20 rows (because we got 2 rows back in the result set), but then we ran the function again (on line1) as we gave back each row, hence the counter-intuitive output.
In the original example, ALL_OBJECTS is being used as a source for rows which is a complex view. The function call could be pushed anywhere deep into this view, which means it might be executed tens of thousands of times, and many of those executions might return a result of “10”.
The bottom line remains the same: non-deterministic means trouble for functions in SQL.
MERGE and ORA-8006
I’m sure there will be a slew of post-Kscope wrap up posts coming out into the blogosphere, so in lieu of that, and the the fact that I’m just stuck in an airport waiting for a flight, I’ll offer something slightly more technical. I did a post a while back about a curious error “unable to get a stable set of rows” when using MERGE. Here is another variant which can occur when you allow rows to physically move during a MERGE.
“How is that possible?” I hear you ask. Easy. All we need is partitioned table with ENABLE ROW MOVEMENT.
SQL> create table t (pk number primary key, x number)
2 partition by list (pk)
3 (partition p1 values(1),
4 partition p2 values(2)
5 )
6 enable row movement;
Table created.
SQL>
SQL> insert into t values (1, 1);
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> merge into t
2 using (select 1 idx, 2 new_val from dual
3 connect by level <= 2
4 ) u
5 on (t.x = u.idx)
6 when matched then
7 update set pk=new_val;
merge into t
*
ERROR at line 1:
ORA-08006: specified row no longer exists
You can see that the merge is going to first change the PK column values from 1 to 2, which will move the row from one partition to another. The second row from the source (CONNECT BY LEVEL <= 2) will go hunting for that original value of 1 in its original partition and not find it there. Hence it “no longer exists”.
This is just another example of why you might want to consider cleansing input data for sensibility before aimlessly throwing it at a MERGE command.
SMON_SCN_TIME and ORA-8161? Digging deeper
In the recent versions of the Oracle database, we’ve had the ability to convert between a System Change Number (SCN) and the approximate time to which that SCN pertains. These functions are unsurprisingly called SCN_TO_TIMESTAMP and TIMESTAMP_TO_SCN. The only potential misnomer here is that even though the functions are called “timestamp” and return a datatype of timestamp, on most platforms you are going to notice that the granularity doesn’t run down into fractions of seconds
SQL> select scn_to_timestamp(14816563713652) from dual;
SCN_TO_TIMESTAMP(14816563713652)
---------------------------------------------------------
08-JUN-19 02.30.59.000000000 AM
This all looks great until you start poking around too far into the past, and you end up in territory like this:
SQL> select scn_to_timestamp(14816563693489) from dual;
select scn_to_timestamp(14816563693489) from dual
*
ERROR at line 1:
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1
If I poke around in V$LOG_HISTORY, then I can pretty easily confirm that SCN 14816563693489 was indeed a valid SCN for this database at some stage in the past, so the fact that we can encounter ORA-08181 suggests that there is a finite structure that holds the mapping between SCNs and the time at which those SCNs pertain to. And indeed there is. The table is called SYS.SMON_SCN_TIME
SQL> desc sys.smon_scn_time
Name Null? Type
----------------------------------------- -------- --------------------
THREAD NUMBER
TIME_MP NUMBER
TIME_DP DATE
SCN_WRP NUMBER
SCN_BAS NUMBER
NUM_MAPPINGS NUMBER
TIM_SCN_MAP RAW(1200)
SCN NUMBER
ORIG_THREAD NUMBER
and in most instances, it will hold around a week’s worth of SCN to timestamp mapping information.
SQL> select min(time_dp), max(time_dp) from sys.smon_scn_time;
MIN(TIME_ MAX(TIME_
--------- ---------
07-JUN-19 13-JUN-19
SQL> select count(*) from sys.smon_scn_time;
COUNT(*)
----------
1603
When SMON_SCN_TIME first came into existence, the granularity of data in this table was an entry approximately every 5 minutes. Hence you could map an SCN to, at best, a 5 minute window. In more recent releases, this has been improved to a granularity of 3 seconds. But the table does not appear to reflect that evolution if you look at some sample data.
SQL> select scn, time_dp
2 from sys.smon_scn_time s
3 where rownum <= 10;
SCN TIME_DP
------------------ -------------------
14816565366476 11/06/2019 08:25:11
14816565366679 11/06/2019 08:30:38
14816565366808 11/06/2019 08:35:11
14816565367164 11/06/2019 08:40:44
14816565367291 11/06/2019 08:45:12
14816565367475 11/06/2019 08:50:32
14816565029366 10/06/2019 01:28:13
14816565029605 10/06/2019 01:33:40
14816565032515 10/06/2019 01:38:13
14816565032779 10/06/2019 01:43:40
It still has a row every 5 minutes to handle backward compatibility, and to drill down to the 3 second level, you are expected to use the functions SCN_TO_TIMESTAMP and TIMESTAMP_TO_SCN already mentioned.
But what if you wanted to store this SCN to timestamp mapping for longer? Activating flashback data archive is one way of achieving this, but what if you just wanted to store the mapping forever without doing this? At first, it seems to be a simple task. I can simply get the lowest SCN from SMON_SCN_TIME:
SQL> select min(scn) scn from sys.SMON_SCN_TIME;
SCN
------------------
14816563714099
and then use a familiar CONNECT BY technique to roll through the SCNs from that point onward
SQL> select 14816563714099+rownum scn,
2 scn_to_timestamp(14816563714099+rownum) ts
3 from ( select 1 from dual connect by level <= 50 );
SCN TS
------------------ -----------------------------------
14816563714100 08-JUN-19 02.41.53.000000000 AM
14816563714101 08-JUN-19 02.41.56.000000000 AM
14816563714102 08-JUN-19 02.41.59.000000000 AM
14816563714103 08-JUN-19 02.41.59.000000000 AM
14816563714104 08-JUN-19 02.41.59.000000000 AM
14816563714105 08-JUN-19 02.41.59.000000000 AM
14816563714106 08-JUN-19 02.41.59.000000000 AM
14816563714107 08-JUN-19 02.41.59.000000000 AM
14816563714108 08-JUN-19 02.41.59.000000000 AM
14816563714109 08-JUN-19 02.41.59.000000000 AM
14816563714110 08-JUN-19 02.41.59.000000000 AM
14816563714111 08-JUN-19 02.42.05.000000000 AM
14816563714112 08-JUN-19 02.42.08.000000000 AM
14816563714113 08-JUN-19 02.42.11.000000000 AM
14816563714114 08-JUN-19 02.42.14.000000000 AM
14816563714115 08-JUN-19 02.42.17.000000000 AM
14816563714116 08-JUN-19 02.42.20.000000000 AM
14816563714117 08-JUN-19 02.42.23.000000000 AM
14816563714118 08-JUN-19 02.42.26.000000000 AM
14816563714119 08-JUN-19 02.42.29.000000000 AM
14816563714120 08-JUN-19 02.42.32.000000000 AM
14816563714121 08-JUN-19 02.42.35.000000000 AM
14816563714122 08-JUN-19 02.42.38.000000000 AM
14816563714123 08-JUN-19 02.42.41.000000000 AM
14816563714124 08-JUN-19 02.42.44.000000000 AM
14816563714125 08-JUN-19 02.42.47.000000000 AM
14816563714126 08-JUN-19 02.42.50.000000000 AM
14816563714127 08-JUN-19 02.42.53.000000000 AM
14816563714128 08-JUN-19 02.42.56.000000000 AM
14816563714129 08-JUN-19 02.42.59.000000000 AM
14816563714130 08-JUN-19 02.42.59.000000000 AM
14816563714131 08-JUN-19 02.42.59.000000000 AM
14816563714132 08-JUN-19 02.42.59.000000000 AM
14816563714133 08-JUN-19 02.42.59.000000000 AM
...
...
But if we are going to store this data, we can start to see some things that can be improved.
Firstly, a lot of rows have the same timestamp. As mentioned, there is only a granularity of 3 seconds, and we can certainly burn through a lot of SCNs in a 3 second period. I can use some simple analytics to see if a timestamp matches the trailing one and filter it out if that is the case.
SQL> with
2 t as
3 ( select min(scn) lo from sys.smon_scn_time ),
4 all_scns as
5 ( select lo+rownum scn, scn_to_timestamp(lo+rownum) ts
6 from t
7 connect by level <= 100 ),
8 delta_scns as
9 ( select a.*, row_number() over ( partition by ts order by scn ) as rn
10 from all_scns a )
11 select * from delta_scns
12 where rn = 1;
SCN TS RN
------------------ ---------------------------------------- ----------
14816563714100 08-JUN-19 02.41.53.000000000 AM 1
14816563714101 08-JUN-19 02.41.56.000000000 AM 1
14816563714102 08-JUN-19 02.41.59.000000000 AM 1
14816563714111 08-JUN-19 02.42.05.000000000 AM 1
14816563714112 08-JUN-19 02.42.08.000000000 AM 1
14816563714113 08-JUN-19 02.42.11.000000000 AM 1
14816563714114 08-JUN-19 02.42.14.000000000 AM 1
14816563714115 08-JUN-19 02.42.17.000000000 AM 1
14816563714116 08-JUN-19 02.42.20.000000000 AM 1
14816563714117 08-JUN-19 02.42.23.000000000 AM 1
14816563714118 08-JUN-19 02.42.26.000000000 AM 1
14816563714119 08-JUN-19 02.42.29.000000000 AM 1
14816563714120 08-JUN-19 02.42.32.000000000 AM 1
14816563714121 08-JUN-19 02.42.35.000000000 AM 1
14816563714122 08-JUN-19 02.42.38.000000000 AM 1
14816563714123 08-JUN-19 02.42.41.000000000 AM 1
14816563714124 08-JUN-19 02.42.44.000000000 AM 1
14816563714125 08-JUN-19 02.42.47.000000000 AM 1
14816563714126 08-JUN-19 02.42.50.000000000 AM 1
14816563714127 08-JUN-19 02.42.53.000000000 AM 1
14816563714128 08-JUN-19 02.42.56.000000000 AM 1
14816563714129 08-JUN-19 02.42.59.000000000 AM 1
...
...
Secondly, we need to stop at a particular point. I can easily get the highest SCN from the table
SQL> select max(scn) scn from sys.smon_scn_time;
SCN
------------------
14816565838367
but that is when things start to get problematic. Even for just this small set of data (just a few days), on my almost entirely idle database (its my laptop):
SQL> select max(scn) - 14816563714100 from sys.smon_scn_time;
MAX(SCN)-14816563714100
-----------------------
2124267
you can see that I’ve burned through over 2million SCNs. When I put that back into my query
SQL> with
2 t as
3 ( select min(scn) lo, max(scn) hi from sys.smon_scn_time ),
4 all_scns as
5 ( select lo+rownum scn, scn_to_timestamp(lo+rownum) ts
6 from t
7 connect by level <= hi-lo )
8 delta_scns as
9 ( select a.*, row_number() over ( partition by ts order by scn ) as rn
10 from all_scns a )
11 select * from delta_scns
12 where rn = 1
it took a looonnnggg time to come back. This is understandable – it is millions of calls to extract a timestamp, followed by some heavy duty analytics to remove duplicates. It has become a very expensive operation to perform.
So that got me thinking – how does the database do it? I started with – how does the database get down to 3 second granularity when there is still only a row every 5 minutes in SMON_SCN_TIME? This is where the TIM_SCN_MAP column comes into play.
SQL> select scn, num_mappings, tim_scn_map
2 from sys.smon_scn_time
3 where scn = 14816565797824
SCN NUM_MAPPINGS TIM_SCN_MAP
------------------ ------------ -----------------------------------------------------------------------------
14816565797824 100 2F90015DC12324C0790D00003290015DC22324C0790D00003590015DC32324C0790D0000...
Searching along raw data and looking for patterns, I could see that the “D0000” was repeated every 24 characters (or 12 bytes). A simple check confirmed that this appears to equate to the NUM_MAPPINGS column, so it is reasonable to assume that the raw data is a set of time mappings. Given that the maximum value for NUM_MAPPINGS is 100, this also fits the hypothesis because 100 x 3 seconds granularity yields 5 minutes, which is how often we get a row in SMON_SCN_TIME
SQL> select scn, num_mappings, length(tim_scn_map)/24
2 from sys.smon_scn_time
3 /
SCN NUM_MAPPINGS LENGTH(TIM_SCN_MAP)/24
------------------ ------------ ----------------------
14816565366476 100 100
14816565366679 83 83
14816565366808 100 100
14816565367164 82 82
14816565367291 100 100
14816565367475 86 86
14816565029366 100 100
...
So 24 characters (12 bytes) is probably an occurrence of a SCN to timestamp mapping. The task now is dig out that information from that raw encoding. I’ll grab that first 12 bytes from the row above 2F90015DC12324C0790D0000 and see if we can align it to other values in the table. The first thing I noticed is that ‘790D0000’ rarely changes across the whole table, so I converted that to decimal to see if I could match it to anything. We have to swap the bytes first, and then I got:
SQL> select to_number('0D79','xxxx') from dual;
TO_NUMBER('0D79','XXXX')
------------------------
3449
That matched the SCN_WRP column in SMON_SCN_TIME. Grabbing the next 4 bytes gives
SQL> select to_number('C02423C1','xxxxxxxx') from dual;
TO_NUMBER('C02423C1','XXXXXXXX')
--------------------------------
3223593921
That matched the SCN_BAS column in SMON_SCN_TIME. So SCN_WRP and SCN_BAS are enough to form a complete SCN number. Which would mean that the remaining 4 bytes should be the time information.
SQL> select to_number('5D01902F','xxxxxxxx') from dual;
TO_NUMBER('5D01902F','XXXXXXXX')
--------------------------------
1560383535
Obviously that does not look like a date or a time, but this is an internal format that is seen in various places around Oracle database, for example, in redo logs etc. To explain where it comes from, it is better to approach it from the other direction (starting with time).
Lets say I wanted a unique number for clock time of just hours and minutes (eg: 09:37). I could do something like:
unique val = hours * 100 + minutes
That would work because I know that minutes is capped at 59, so there is no chance of an overlap by using a multiplier of 100. So here is how that concept can be taken to its full extent to get a unique number for a full date and time. I’ll pick a date/time of 2019-6-12 23:52:15 as an example
- Take the year, 2019.
- We don’t need anything before Oracle was “born”, so subtract 1976 = 43
- For the months, multiply by 13 (anything more than 12 months) = 559
- Because we’re IT nerds, we’ll start months at zero (0=January), so we add 5 for June = 564
- For the day in a month, multiply by 32 (anything more than 31) = 18048
- Because we’re IT nerds, we’ll start day of month at zero (=1st), so we add 11 for the 12th = 18059
- For the hours, multiply by 24 (because hours never exceed 23) = 433416
- No need for nerdy stuff here, because hours are already normalised at 0-23, so we add 23 = 433439
- For the minutes, multiply by 60 (minutes are 0 to 59) = 26006340
- Then add the minutes = 26006392
- Same for the seconds, multiply by 60 = 1560383520
- and finally we add the seconds = 1560383535
And voila! You can that it matches the decimal expansion of the 4 bytes of 5D01902F above. Now that we know how the time to number conversion is done, a little PL/SQL lets the reverse be done (number to time):
SQL> declare
2 t int := 1560383535;
3 secs int;
4 mins int;
5 hrs int;
6 dy int;
7 mon int;
8 yr int;
9 begin
10 secs := mod(t,60);
11 t := t - secs;
12 t := floor(t / 60);
13 mins := mod(t,60);
14 t := t - mins;
15 t := floor(t / 60);
16 hrs := mod(t,24);
17 t := t - hrs;
18 t := floor(t / 24);
19 dy := mod(t,32);
20 t := t - dy;
21 t := floor(t / 32);
22 dy := dy + 1;
23 mon := mod(t,13);
24 t := t - mon;
25 mon := mon + 1;
26 t := floor(t / 13);
27 yr := t + 1976;
28 dbms_output.put_line(yr||'-'||mon||'-'||dy||' '||hrs||':'||mins||':'||secs);
29 end;
30 /
2019-6-12 23:52:15
To make all of this simpler and modular, I’ll build a couple of functions to bring all the bits and pieces we’ve learnt together. First a function to take a non-byte swapped hex string to a SCN
SQL> create or replace
2 function raw_to_scn(p_scn varchar2) return number is
3 begin
4 return to_number(
5 substr(p_scn,7,2)||
6 substr(p_scn,5,2)||
7 substr(p_scn,3,2)||
8 substr(p_scn,1,2),'xxxxxxxx');
9 end;
10 /
Function created.
and then a function that will extend the anonymous block above to take a non-byte swapped hex string and return a timestamp (well, a date in reality).
SQL> create or replace
2 function raw_to_date(p_time varchar2) return date is
3 t int := to_number(
4 substr(p_time,7,2)||
5 substr(p_time,5,2)||
6 substr(p_time,3,2)||
7 substr(p_time,1,2),'xxxxxxxx');
8 secs int;
9 mins int;
10 hrs int;
11 dy int;
12 mon int;
13 yr int;
14 begin
15 secs := mod(t,60);
16 t := t - secs;
17 t := floor(t / 60);
18 mins := mod(t,60);
19 t := t - mins;
20 t := floor(t / 60);
21 hrs := mod(t,24);
22 t := t - hrs;
23 t := floor(t / 24);
24 dy := mod(t,32);
25 t := t - dy;
26 t := floor(t / 32);
27 dy := dy + 1;
28 mon := mod(t,13);
29 t := t - mon;
30 mon := mon + 1;
31 t := floor(t / 13);
32 yr := t + 1976;
33 return to_date(yr||'-'||mon||'-'||dy||' '||hrs||':'||mins||':'||secs,'yyyy-mm-dd hh24:mi:ss');
34 end;
35 /
Function created.
With these in place, I can create a couple of types to serve as return datatypes for a pipelined function
SQL> create or replace
2 type scn_dte as object
3 ( scn int, dte date );
4 /
Type created.
SQL>
SQL> create or replace
2 type scn_dte_nt as table of scn_dte
3 /
Type created.
and here is a function that will take important components from SMON_SCN_TIME namely SCN_BAS, NUM_MAPPINGS, SCAN_WRAP and the raw data in TIM_SCN_MAP to spit out the 3-second interval data rather than just 1 row per 5 minutes.
SQL> create or replace
2 function full_smon_scn_timestamp(p_start_scn int default 0) return scn_dte_nt pipelined as
3 l_map varchar2(2400);
4 begin
5 for i in ( select * from sys.smon_scn_time
6 where scn > p_start_scn
7 order by scn
8 )
9 loop
10 pipe row ( scn_dte(i.scn_wrp * 4294967296 + i.scn_bas,i.time_dp));
11 l_map := i.tim_scn_map;
12 for j in 1 .. i.num_mappings
13 loop
14 pipe row (
15 scn_dte(
16 i.scn_wrp * 4294967296 + raw_to_scn(substr(l_map,9,8)),
17 raw_to_date(substr(l_map,1,8))
18 )
19 );
20 l_map := substr(l_map,25);
21 end loop;
22 end loop;
23 end;
24 /
Function created.
Let’s give that function a run to see what comes out:
SQL> select * from full_smon_scn_timestamp()
2 where rownum <= 20;
SCN DTE
------------------ -------------------
14816563726597 07/06/2019 21:50:43
14816563726598 07/06/2019 21:50:46
14816563726599 07/06/2019 21:50:49
14816563726600 07/06/2019 21:50:52
14816563726601 07/06/2019 21:50:55
14816563726602 07/06/2019 21:50:58
14816563726603 07/06/2019 21:51:01
14816563726604 07/06/2019 21:51:04
14816563726605 07/06/2019 21:51:07
14816563726606 07/06/2019 21:51:10
14816563726607 07/06/2019 21:51:13
14816563726608 07/06/2019 21:51:16
14816563726609 07/06/2019 21:51:19
14816563726610 07/06/2019 21:51:22
14816563726611 07/06/2019 21:51:25
14816563726612 07/06/2019 21:51:28
14816563726633 07/06/2019 21:51:34
14816563726634 07/06/2019 21:51:37
14816563726635 07/06/2019 21:51:40
14816563726636 07/06/2019 21:51:43
Each row in SMON_SCN_TIME contains a starting value for the SCN plus all of the mappings, so the total number of 3-second intervals is:
SQL> select sum(num_mappings+1) from sys.smon_scn_time;
SUM(NUM_MAPPINGS+1)
-------------------
145262
and I can compare that to the total number of rows that will be returned from my function
SQL> select count(*) from full_smon_scn_timestamp();
COUNT(*)
----------
145262
So there you have a way to grab all of those 3-second granularity entries in SMON_SCN_TIME without having to call SCN_TO_TIMESTAMP millions of time. You could run this function (say) daily by passing in a starting SCN value equal to the highest value you have so far retrieved to build up your own custom SMON_SCN_TIME variant that you can keep for as long as you want.
Of course, poking around in raw fields is totally unsupported, so don’t come crying to me if you decide to use this for anything more than exploration and one day it all blows up in your face 🙂
Kscope Sunday will be awesome
Yeah yeah I know. What kind of dufus cut-pastes a giant image into their blog post. That would be ….. me 
But the reality is, there is just soooooo much going on this year on the Sunday before the “official” start of the Kscope conference, it was too hard to condense it into a few sentences. So I just dumped the image from the website, but you can read about it here.
It’s my first Kscope and it will be blast to be running a day of high quality but dirt cheap learning with Maria, Jeff, Steven and Blaine.
And we’ll even have some fun and games. How good is your knowledge of the Oracle Database and the Oracle community? Our Sunday quiz will let you shine! (Well, to be honest, we’ve designed the quiz so that anyone in the community has a chance to win, so come along to have some fun and maybe pick up some prizes as well!)
See you in Seattle!
With and without WITH_PLSQL within a WITH SQL statement
OK, let’s be honest right up front. The motivation for this post is solely to be able to roll out a tongue twisting blog post title 
. But hopefully there’s some value as well in here for you if you’re hitting the error:
ORA-32034: unsupported use of WITH clause
First some background. A cool little enhancement to the WITH clause came along in 12c that allowed PLSQL functions to be defined within the scope of the executing SQL statement. To see the benefit of this, consider the following example that I have a personal affinity with (given my surname).
Let’s say I’ve allowed mixed-case data in a table that holds names.
SQL> select surname
2 from names;
SURNAME
------------------------------
jones
brown
SMITH
There’s nothing inherently wrong here, but in terms of rendering that data in a report or on screen, it would be nice to have some consistency.
“No problem” you think, “I’ll just slap an INITCAP in there”
SQL> select initcap(surname)
2 from names;
INITCAP(SURNAME)
------------------------------
Jones
Brown
Smith
That works fine of course until …. yours truly gets added into the mix . After a couple of new rows are added, we can start to see the shortcomings of INITCAP.
SQL> select initcap(surname)
2 from names;
INITCAP(SURNAME)
------------------------------
Jones
Brown
Smith
Mcdonald
Johnson'S
I’d like a capital D, and letters that follow apostrophes have some nuances that might need handling. This can be solved without too much fuss – with a little bit of PLSQL I can produce a custom version of INITCAP that will handle these exceptional cases.
SQL> create or replace
2 function MY_INITCAP(p_string varchar2) return varchar2 is
3 l_string varchar2(1000) := p_string;
4 begin
5 if regexp_like(l_string,'(Mac[A-Z]|Mc[A-Z])') then
6 null;
7 elsif l_string like '''%' then
8 null;
9 else
10 l_string := initcap(l_string);
11 if l_string like '_''S%' then
12 null;
13 else
14 l_string := replace(l_string,'''S','''s');
15 end if;
16 end if;
17
18 return l_string;
19 end;
20 /
SQL> select my_initcap(surname)
2 from names;
MY_INITCAP(SURNAME)
--------------------------
Jones
Brown
Smith
McDonald
Johnson's
But perhaps I’d like that functionality inline with the SQL so that a future maintainer can directly see what I’ve done. Yes, I could refactor the code to be 100% SQL with no reliance on PLSQL using something like this:
SQL> select
2 case
3 when regexp_like(surname,'(Mac[A-Z]|Mc[A-Z])') then surname
4 when surname like '''%' then surname
5 when initcap(surname) like '_''S%' then surname
6 else replace(initcap(surname),'''S','''s')
7 end ugh
8 from names;
UGH
-------------------------------
Jones
Brown
Smith
McDonald
Johnson's
But if I’m doing this to help a future maintainer….well… that convoluted CASE statement probably isn’t such a nice remnant for them . So since 12c, we’ve been able to add that PLSQL code directly within the SQL statement itself.
SQL> WITH
2 function my_initcap(p_string varchar2) return varchar2 is
3 l_string varchar2(1000) := p_string;
4 begin
5 if regexp_like(l_string,'(Mac[A-Z]|Mc[A-Z])') then
6 null;
7 elsif l_string like '''%' then
...
17
18 return l_string;
19 end;
20 select my_initcap(surname)
21 from names;
MY_INITCAP(SURNAME)
-----------------------------------------
Jones
Brown
Smith
McDonald
Hopefully you can now see the benefit of the feature. Now back to the topic at hand, the ORA-32034 error. If you attempt to use the feature within an INSERT, UPDATE or DELETE statement, you’ll get a surprise:
SQL> insert into TARGET_TABLE
2 WITH
3 function my_initcap(p_string varchar2)
4 return varchar2 is
5 l_string varchar2(1000) := p_string;
6 begin
...
20 end;
21 select my_initcap(surname)
22 from names;
23 /
WITH
*
ERROR at line 2:
ORA-32034: unsupported use of WITH clause
To overcome this, you need to specify the WITH_PLSQL hint
SQL> insert /*+ WITH_PLSQL */ into TARGET_TABLE
2 WITH
3 function my_initcap(p_string varchar2)
4 return varchar2 is
5 l_string varchar2(1000) := p_string;
6 begin
...
20 end;
21 select my_initcap(surname)
22 from names;
23 /
5 rows inserted.
I’ve never ascertained the reason precisely why the hint is needed (I’m asking around internally within the database group), but I have a hypothesis: Given that you could potentially write anything within an PLSQL function (including independent transactions), my guess is that the hint is a flag to the database to say “Have an extra careful double-check of the function code to make sure it’s not doing anything dangerous, in particular, to the table we doing the DML on”.
I might be 100% wrong here – I’ll update the post if I get more information in future.
Advice on fragmentation and shrinkage
If you have performed some sort of data cleanup or similar on a table, then the deleted space will be reused by future insertions. But if
- that cleanup was the last task you were performing on that table, ie, you were not expecting a lot of new data to ever come in again, or
- you are performing a lot of full scan queries on that table and you want to make sure they are as efficient as possible
then there may be benefits to performing a shrink on that table to reclaim that space. One of the cool things about the segment advisor is that it will detect if there are some benefits to be gained by shrinking a segment. Here’s an example of that. I create a large table and then delete every 2nd row.
SQL> create table scott.demo_table as
2 select d.* from dba_objects d,
3 ( select 1 from dual connect by level <= 100 ) ;
Table created.
SQL>
SQL> delete from scott.demo_table
2 where mod(object_id,2) = 0;
4125300 rows deleted.
SQL> commit;
Commit complete.
Now I run the segment advisor and I get a nice report on what can be done to reclaim that space.
SQL>
SQL>
SQL> set serveroutput on size 1000000
SQL> set linesize 200
SQL> set verify off
SQL>
SQL> declare
2 l_object_id number;
3 l_task_name varchar2(50) := 'TEST_TASK';
4 l_object_type varchar2(50) := upper('TABLE');
5 l_attr1 varchar2(50) := upper('SCOTT');
6 l_attr2 varchar2(50) := upper('DEMO_TABLE');
7 begin
8 begin DBMS_ADVISOR.delete_task(task_name => l_task_name); exception when others then null; end;
9
10 dbms_advisor.create_task (
11 advisor_name => 'Segment Advisor',
12 task_name => l_task_name);
13
14 dbms_advisor.create_object (
15 task_name => l_task_name,
16 object_type => l_object_type,
17 attr1 => l_attr1,
18 attr2 => l_attr2,
19 attr3 => NULL,
20 attr4 => 'null',
21 attr5 => NULL,
22 object_id => l_object_id);
23
24 dbms_advisor.set_task_parameter (
25 task_name => l_task_name,
26 parameter => 'RECOMMEND_ALL',
27 value => 'TRUE');
28
29 dbms_advisor.execute_task(task_name => l_task_name);
30
31
32 for cur_rec in (select f.impact,
33 o.type,
34 o.attr1,
35 o.attr2,
36 f.message,
37 f.more_info
38 from dba_advisor_findings f
39 join dba_advisor_objects o on f.object_id = o.object_id and f.task_name = o.task_name
40 where f.task_name = l_task_name
41 order by f.impact desc)
42 loop
43 dbms_output.put_line('..');
44 dbms_output.put_line('Type : ' || cur_rec.type);
45 dbms_output.put_line('Attr1 : ' || cur_rec.attr1);
46 dbms_output.put_line('Attr2 : ' || cur_rec.attr2);
47 dbms_output.put_line('Message : ' || cur_rec.message);
48 dbms_output.put_line('More info : ' || cur_rec.more_info);
49 end loop;
50
51 dbms_advisor.delete_task(task_name => l_task_name);
52 end;
53 /
..
Type : TABLE
Attr1 : SCOTT
Attr2 : DEMO_TABLE
Message : Enable row movement of the table SCOTT.DEMO_TABLE and perform shrink, estimated savings is 596868412 bytes.
More info : Allocated Space:1342177280: Used Space:745308868: Reclaimable Space :596868412:
PL/SQL procedure successfully completed.
But what if that segment sits in a manual segment space managed tablespace? We can see from the above that a shrink-style operation will yield some benefits, but there’s a problem. You cannot perform an ALTER TABLE SHRINK command unless a segment sits in an ASSM tablespace. So is the segment advisor of any use in these cases? Let’s re-run the demo to find out
SQL> select * from dba_tablespaces where tablespace_name = 'NO_ASSM'
2 @pr
==============================
TABLESPACE_NAME : NO_ASSM
BLOCK_SIZE : 8192
INITIAL_EXTENT : 65536
NEXT_EXTENT :
MIN_EXTENTS : 1
MAX_EXTENTS : 2147483645
MAX_SIZE : 2147483645
PCT_INCREASE :
MIN_EXTLEN : 65536
STATUS : ONLINE
CONTENTS : PERMANENT
LOGGING : LOGGING
FORCE_LOGGING : NO
EXTENT_MANAGEMENT : LOCAL
ALLOCATION_TYPE : SYSTEM
PLUGGED_IN : NO
SEGMENT_SPACE_MANAGEMENT : MANUAL
DEF_TAB_COMPRESSION : DISABLED
RETENTION : NOT APPLY
BIGFILE : NO
PREDICATE_EVALUATION : HOST
ENCRYPTED : NO
COMPRESS_FOR :
DEF_INMEMORY : DISABLED
DEF_INMEMORY_PRIORITY :
DEF_INMEMORY_DISTRIBUTE :
DEF_INMEMORY_COMPRESSION :
DEF_INMEMORY_DUPLICATE :
SHARED : SHARED
DEF_INDEX_COMPRESSION : DISABLED
INDEX_COMPRESS_FOR :
DEF_CELLMEMORY :
DEF_INMEMORY_SERVICE :
DEF_INMEMORY_SERVICE_NAME :
LOST_WRITE_PROTECT : OFF
CHUNK_TABLESPACE : N
PL/SQL procedure successfully completed.
SQL>
SQL> create table scott.demo_table tablespace no_assm as
2 select d.* from dba_objects d,
3 ( select 1 from dual connect by level <= 100 ) ;
Table created.
SQL>
SQL> delete from scott.demo_table
2 where mod(object_id,2) = 0;
4125300 rows deleted.
SQL> commit;
Commit complete.
SQL>
SQL>
SQL> set serveroutput on size 1000000
SQL> set linesize 200
SQL> set verify off
SQL>
SQL> declare
2 l_object_id number;
3 l_task_name varchar2(50) := 'TEST_TASK';
4 l_object_type varchar2(50) := upper('TABLE');
5 l_attr1 varchar2(50) := upper('SCOTT');
6 l_attr2 varchar2(50) := upper('DEMO_TABLE');
7 begin
8 begin DBMS_ADVISOR.delete_task(task_name => l_task_name); exception when others then null; end;
9
10 dbms_advisor.create_task (
11 advisor_name => 'Segment Advisor',
12 task_name => l_task_name);
13
14 dbms_advisor.create_object (
15 task_name => l_task_name,
16 object_type => l_object_type,
17 attr1 => l_attr1,
18 attr2 => l_attr2,
19 attr3 => NULL,
20 attr4 => 'null',
21 attr5 => NULL,
22 object_id => l_object_id);
23
24 dbms_advisor.set_task_parameter (
25 task_name => l_task_name,
26 parameter => 'RECOMMEND_ALL',
27 value => 'TRUE');
28
29 dbms_advisor.execute_task(task_name => l_task_name);
30
31
32 for cur_rec in (select f.impact,
33 o.type,
34 o.attr1,
35 o.attr2,
36 f.message,
37 f.more_info
38 from dba_advisor_findings f
39 join dba_advisor_objects o on f.object_id = o.object_id and f.task_name = o.task_name
40 where f.task_name = l_task_name
41 order by f.impact desc)
42 loop
43 dbms_output.put_line('..');
44 dbms_output.put_line('Type : ' || cur_rec.type);
45 dbms_output.put_line('Attr1 : ' || cur_rec.attr1);
46 dbms_output.put_line('Attr2 : ' || cur_rec.attr2);
47 dbms_output.put_line('Message : ' || cur_rec.message);
48 dbms_output.put_line('More info : ' || cur_rec.more_info);
49 end loop;
50
51 dbms_advisor.delete_task(task_name => l_task_name);
52 end;
53 /
..
Type : TABLE
Attr1 : SCOTT
Attr2 : DEMO_TABLE
Message : Perform re-org on the object DEMO_TABLE, estimated savings is 600175966 bytes.
More info : Allocated Space:1342177280: Used Space:742001314: Reclaimable Space :600175966:
PL/SQL procedure successfully completed.
SQL>
SQL>
As you can see, the segment advisor will take that into account and adjust its recommendations accordingly. And one of the cool things with 12.2 and above, is that tables can be reorganised without an outage!
SQL> alter table scott.demo_table move online;
Table altered.
Nice!
