Merge, Right?

"There are no wrong roads to anywhere."
 - Norton Juster, The Phantom Tollbooth 

Oracle can do some, well, strange things when fixing issues caused by the optimizer. For 10g releases up to 10.2.0.x Oracle chose to silently ignore a message and eliminate an outer join on the MERGE statement under certain conditions. Let’s examine this and see exactly what Oracle implements.

Occasionally in a 10046 trace file generated from 10g releases prior to 10.2.0.x and procesed by the tkprof utility you may see something similar to this:

SELECT 1 FROM DUAL WHERE <some condition here>
 
Error encountered: ORA-00904

where <some condition here> is the ‘ON’ condition from a MERGE statement executed by that session. That seems odd, I know, and Oracle silently ignores that ORA-00904 condition as it is never displayed when executing the MERGE. Why would Oracle do this? Looking at the 10053 trace file generated from the following example:

SQL> merge into t1_merge t1
  2          using (select 1 merged_key, lpad('y', 21, 'y') xtra  from dual) t2
  3          on (t1.merge_key = t2.merged_key)
  4  when matched then update set t1.xtra_stuff = t2.xtra
  5  when not matched then insert (merge_key, more_txt, xtra_stuff) values (t2.merged_key, t2.merged_key, t2.xtra);

1 row merged.

SQL>

we see the following:

PARSE ERROR #5:len=36 dep=1 uid=60 oct=3 lid=60 tim=1266258229533447 err=904
SELECT 1 FROM DUAL WHERE t1.merge_key = t2.merged_key
CLOSE #5:c=0,e=4,dep=1,type=0,tim=1266258229533578
Registered qb: MRG$1 0x84379c (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
...
====================== END SQL Statement Dump ======================
=====================
PARSING IN CURSOR #1 len=302 dep=0 uid=1158 oct=189 lid=1158 tim=33189168479100 hv=1190985157 ad='1ec71da8'
merge into t1_merge t1
        using (select 1 merged_key, lpad('y', 21, 'y') xtra  from dual) t2
        on (t1.merge_key = t2.merged_key)
when matched then update set t1.xtra_stuff = t2.xtra
when not matched then insert (merge_key, more_txt, xtra_stuff) values (t2.merged_key, t2.merged_key, t2.xtra)
END OF STMT
PARSE #1:c=40000,e=51569,p=4,cr=29,cu=0,mis=1,r=0,dep=0,og=2,tim=33189168479094
BINDS #1:
EXEC #1:c=10000,e=558,p=0,cr=7,cu=3,mis=0,r=1,dep=0,og=2,tim=33189168479778
WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=2135111 tim=33189168479862
WAIT #1: nam='SQL*Net message from client' ela= 419 driver id=1650815232 #bytes=1 p3=0 obj#=2135111 tim=33189168480324
STAT #1 id=1 cnt=2 pid=0 pos=1 obj=0 op='MERGE  T1_MERGE (cr=7 pr=0 pw=0 time=425 us)'
STAT #1 id=2 cnt=1 pid=1 pos=1 obj=0 op='VIEW  (cr=7 pr=0 pw=0 time=178 us)'
STAT #1 id=3 cnt=1 pid=2 pos=1 obj=0 op='NESTED LOOPS OUTER (cr=7 pr=0 pw=0 time=175 us)'
STAT #1 id=4 cnt=1 pid=3 pos=1 obj=0 op='FAST DUAL  (cr=0 pr=0 pw=0 time=1 us)'
STAT #1 id=5 cnt=1 pid=3 pos=2 obj=2135111 op='TABLE ACCESS FULL T1_MERGE (cr=7 pr=0 pw=0 time=170 us)'

Notice there is no outer join in the plan steps. Additionally if we look at the outline data we see:

  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
      FIRST_ROWS(10)
      OUTLINE_LEAF(@"SEL$5428C7F1")
      MERGE(@"SEL$2")
      MERGE(@"SEL$3")
      OUTLINE_LEAF(@"SEL$4")
      OUTLINE_LEAF(@"MRG$1")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      NO_ACCESS(@"MRG$1" "from$_subquery$_008"@"MRG$1")
      NO_ACCESS(@"MRG$1" "T2"@"MRG$1")
      FULL(@"MRG$1" "T1"@"MRG$1")
      LEADING(@"MRG$1" "from$_subquery$_008"@"MRG$1" "T2"@"MRG$1" "T1"@"MRG$1")
      USE_MERGE_CARTESIAN(@"MRG$1" "T2"@"MRG$1")
      USE_MERGE_CARTESIAN(@"MRG$1" "T1"@"MRG$1")
    END_OUTLINE_DATA
  */

The outline data in these cases shows the outer join isn’t used as Oracle re-writes the query into this:

merge into t1
using (select 1 merged_key, lpad('y', 21, 'y') xtra from dual) t2
on (1 = 0)
when matched then update set t1.xtra = t2.xtra
when not matched then insert (merge_key, more_txt, xtra_stuff) values (t2.merged_key, t2.merged_key, t2.xtra);

Inerestingly the reason for this behavior is available in the online documentationL

To insert all of the source rows into the table, you can use a constant filter predicate in the ON clause condition. An example of a 
constant filter predicate is ON (0=1). Oracle Database recognizes such a predicate and makes an unconditional insert of all source 
rows into the table. This approach is different from omitting the merge_update_clause. In that case, the database still must perform 
a join. With constant filter predicate, no join is performed.

This appears to be fixed in 10.2.0.x and later releases as the ORA-00904 error does not occur in either of the trace files. MOS document id 244055.1 lists this modification as an ‘enhancement’ in 10g. The document also lists the bug that requires this work-around is fixed in 10.1.0.1.0. Even though 10g in all its forms is an old release there still may be sites using it.

In 11.2.0.4 this same construct generates no ORA-00904 error, nor does the ‘dummy’ query appear. Notice that the join is restored as evidenced by the execution plan:

----- Current SQL Statement for this session (sql_id=5zv0cdsdp0bf4) -----
merge into t1_merge t1
	using (select 1 merged_key, lpad('y', 21, 'y') xtra  from dual) t2
	on (t1.merge_key = t2.merged_key)
when matched then update set t1.xtra_stuff = t2.xtra
when not matched then insert (merge_key, more_txt, xtra_stuff) values (t2.merged_key, t2.merged_key, t2.xtra)
sql_text_length=289
sql=merge into t1_merge t1
	using (select 1 merged_key, lpad('y', 21, 'y') xtra  from dual) t2
	on (t1.merge_key = t2.merged_key)
when matched then update set t1.xtra_stuff = t2.xtra
when not matched then insert (merge_key, more_txt, xtra_stuff) values (t2.mer
sql=ged_key, t2.merged_key, t2.xtra)
----- Explain Plan Dump -----
----- Plan Table -----
 
============
Plan Table
============
----------------------------------------+-----------------------------------+
| Id  | Operation             | Name    | Rows  | Bytes | Cost  | Time      |
----------------------------------------+-----------------------------------+
| 0   | MERGE STATEMENT       |         |       |       |     6 |           |
| 1   |  MERGE                | T1_MERGE|       |       |       |           |
| 2   |   VIEW                |         |       |       |       |           |
| 3   |    NESTED LOOPS OUTER |         |     1 |    31 |     6 |  00:00:01 |
| 4   |     TABLE ACCESS FULL | DUAL    |     1 |     2 |     2 |  00:00:01 |
| 5   |     TABLE ACCESS FULL | T1_MERGE|     1 |    29 |     4 |  00:00:01 |
----------------------------------------+-----------------------------------+
Predicate Information:
----------------------
5 - filter("T1"."MERGE_KEY"=CASE  WHEN (ROWID IS NOT NULL) THEN 1 ELSE 1 END )

The outline data where this is fixed shows the join is used:

  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
      DB_VERSION('11.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$5428C7F1")
      MERGE(@"SEL$2")
      MERGE(@"SEL$3")
      OUTLINE_LEAF(@"SEL$4")
      OUTLINE_LEAF(@"MRG$1")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$3")
      NO_ACCESS(@"MRG$1" "from$_subquery$_008"@"MRG$1")
      NO_ACCESS(@"MRG$1" "T2"@"MRG$1")
      FULL(@"MRG$1" "T1"@"MRG$1")
      LEADING(@"MRG$1" "from$_subquery$_008"@"MRG$1" "T2"@"MRG$1" "T1"@"MRG$1")
      USE_MERGE_CARTESIAN(@"MRG$1" "T2"@"MRG$1")
      USE_MERGE_CARTESIAN(@"MRG$1" "T1"@"MRG$1")
      PQ_DISTRIBUTE(@"MRG$1" "T2"@"MRG$1" NONE NONE)
      PQ_DISTRIBUTE(@"MRG$1" "T1"@"MRG$1" NONE NONE)
      FULL(@"SEL$5428C7F1" "DUAL"@"SEL$3")
      FULL(@"SEL$5428C7F1" "T1"@"SEL$2")
      LEADING(@"SEL$5428C7F1" "DUAL"@"SEL$3" "T1"@"SEL$2")
      USE_NL(@"SEL$5428C7F1" "T1"@"SEL$2")
    END_OUTLINE_DATA
  */

Oracle has, apparently, seen the ‘error of its ways’ with this ‘enhancement’ and re-thought how MERGE should be executed. Of course you may be stuck running an older version of 10g and could be affected by this ‘enhancement’. Even migrating to 10.2.0.4 can eliminate this strange query from your MERGE implementations. Know that this isn’t really a performance problem, but it can be disconcerting to see such essentially useless query text in trace files you may generate. Knowing where it comes from hopefully will make it easier to assess performance issues requiring 10046 and 10053 trace files.

It really helps to pay close attention when merging.

What’s The Password?

A very interesting question was posted in one of the forums I participate in:

is there any way how I can revoke the right from a normal user to change it's own password in the database?

This, obviously, sparked a fairly lively thread (which I will not re-post here) regarding security and the underlying reason such a request was made. The user who posted the original question continued in the thread to explain:

Of course the user should access his own data but I'm trying to prevent that the user can change his own password as he likes. It is part of the security awarness of my company 
and I'm searching for a way to realize that.

This raised more questions and commentary, including the fact that not allowing a user to change his or her own password really implements less security because someone else (the DBA) needs to set it. Any time a password needs to be reset by an administrative user security is compromised, in my opinion. Of course it’s going to be necessary at some point for the DBA to intervene and reset a user’s password — usually when said user has forgotten the password. At those times the DBA will reset the password and force the user to change it to something new at the next login, thus preserving security since no one but the affected user will know his or her password. But, some people are hard to convince.

I think it is more safe, when the "stupid" user can't change his password. He has to raise an incident and the DBA then will change the password

This continued on for a while with various forum members adding comments. So CAN a user be prevented from changing his or her own password and still preserve the ability for the DBA to do so? It appears not, as Pete Finnigan reported back in 2008. He tried using a trigger to generate an error whenever an ALTER USER command was submitted. Why ALTER USER? Let’s find out.

When a user is created in Oracle a curious thing happens; that user automatically, and silently, receives ALTER USER privileges for his or her own account, without an explicit grant. There is nothing to revoke, so that won’t disable the functionality:

SQL> revoke alter user from blorbo;
revoke alter user from blorbo
*
ERROR at line 1:
ORA-01952: system privileges not granted to 'BLORBO'


SQL>

The password function uses ALTER USER behind the scenes to effect the password change. Pete thought that using a trigger would solve the dilemma of a user changing his or her own password, and on one level it does. The trigger is simple, really:

SQL> create or replace trigger alter_trigger
  2  before alter
  3  on database
  4  declare
  5  begin
  6  if (ora_dict_obj_type = 'USER') then
  7  raise_application_error(-20010,'you cannot change your own password');
  8  end if;
  9  end;
 10  /

Trigger created.

SQL>

So far, so good, so let’s test this on a sample user account:

SQL> connect blorbo/#########
Connected.
SQL> password
Changing password for BLORBO
Old password:
New password:
Retype new password:
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20010: you cannot change your own password
ORA-06512: at line 4


Password unchanged
SQL>

Eureka!!! It works!!! Unfortunately it works TOO well, as now SYS and SYSTEM can’t change a user password:

SQL> show user
USER is "SYS"
SQL> alter user blorbo identified by yoopa;
alter user blorbo identified by yoopa
                                *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20010: you cannot change your own password
ORA-06512: at line 4


SQL>

That’s no good; passwords now cannot be changed and when they expire, well, the user loses access to the database which is not the intended result. Dropping the trigger restores the ability of SYS and SYSTEM to effect password changes but also opens the ‘self-service password door’ we tried to close for “security” reasons.

Another possibility was presented by John Watson in that thread, a basic function to be used as the password verify function. He posted this:

create or replace function no_self_change
(username varchar2,
password varchar2,
old_password varchar2)
RETURN boolean IS
begin
if sys_context('userenv','session_user')=username then
return false;
else
return true;
end if;
end;
/

and it does, indeed, work:

SQL> password
Changing password for BLORBO
Old password:
New password:
Retype new password:
ERROR:
ORA-28003: password verification for the specified password failed


Password unchanged
SQL>
SQL> connect / as sysdba
Connected.
SQL> alter user blorbo identified by yimpski;

User altered.

SQL>

So, we see it is possible to affect the ability for a user to change his or her own database password without affecting the ability of the DBA to do so. But, to be honest, preventing a user from changing their own password is not enforcing security, it’s reducing security by allowing another user (the DBA) to know a user’s password. The DBA should be trustworthy but would YOU want your DBA to know your password? I think not.

Passwords are personal, they should never be shared, and choosing strong passwords is a difficult enough task without adding another person to get the job done. Sometimes what seems like a good security idea at the time turns out to be more of a security risk; not allowing a user to change their own password, in my opinion, falls into that category.

So, there.

How Can I Compress Thee

“You can swim all day in the Sea of Knowledge and not get wet.” 
― Norton Juster, The Phantom Tollbooth 

In previous posts compression options have been discussed, and now it’s time to see how Oracle performs basic compression. It isn’t really compression, it’e de-duplication, but it does result in space savings for data that won’t be modified after it’s ‘compressed’. Let’s look at how Oracle saves space with your data.

Oracle de-duplicates the data by finding common strings, tokenizing them and using the token identifier in the string to reduce the row length. So, what does that mean? Looking at an example might help; a table is built and populated as follows:

--
-- Create and populate the table
--
create table comptst(
	tstcol1	varchar2(4),
	tstcol2 varchar2(6),
	tstcol3	number(8,2),
	tstcol4	varchar2(10));

insert into comptst
values ('ZZXZ', 'bbddff', 199.44, 'PENDING');

insert into comptst
values ('ZZXZ', 'ghijkl', 43.08, 'PENDING');

insert into comptst
values ('ZZXZ', 'bbddff', 881.02, 'PENDING');

insert into comptst
values ('ZZXZ', 'bbddff', 54.97, 'PENDING');

commit;

insert into comptst select * From comptst;
insert into comptst select * From comptst;
insert into comptst select * From comptst;
insert into comptst select * From comptst;
insert into comptst select * From comptst;
insert into comptst select * From comptst;
insert into comptst select * From comptst;
insert into comptst select * From comptst;
insert into comptst select * From comptst;
insert into comptst select * From comptst;
insert into comptst select * From comptst;
insert into comptst select * From comptst;
insert into comptst select * From comptst;
insert into comptst select * From comptst;
insert into comptst select * From comptst;
insert into comptst select * From comptst;
insert into comptst select * From comptst;
insert into comptst select * From comptst;
insert into comptst select * From comptst;
insert into comptst select * From comptst;
insert into comptst select * From comptst;
insert into comptst select * From comptst;
insert into comptst select * From comptst;
insert into comptst select * From comptst;
insert into comptst select * From comptst;

commit;
				--
-- Compress the table with BASIC compression
--
alter table comptst compress basic;
alter table comptst move;

[The table was compressed after the data was inserted which required two steps, the first to set the compression level and the second, a table move in place, to actually compress the data. Had the table been built as compressed and direct path inserts used the data would have been compressed without further action.] Since the initial 4-row insert was re-inserted multiple times there is a lot of duplication in the data, and since Oracle de-duplicates rows to produce the effect of compression there should be a lot of data in a block dump indicating this. There is, and the first piece of that data is the following line:

  perm_9ir2[4]={ 0 2 3 1 }

Oracle builds a token table for each data block; this provides a reference for each data string that occurs more than once in the block. Additionally Oacle can re-arrange the column values in that token table so that multiple column values can be turned into a single token and, thus, a single reference. The line shown above indicates what column values map to the table positions in the token table for this block; in this case column 0 maps to the data in table column 0, column 1 maps to the data in table column 2, column 2 maps to data column 3 and column 3 maps to data column 1. Let’s look at the unique data that was inserted:

('ZZXZ', 'bbddff', 199.44, 'PENDING');
('ZZXZ', 'ghijkl', 43.08, 'PENDING');
('ZZXZ', 'bbddff', 881.02, 'PENDING');
('ZZXZ', 'bbddff', 54.97, 'PENDING');

Since these data rows are duplicated in each block every column is a potential compression token. Two values occur in every row, ‘ZZXZ’ and ‘PENDING’, so it should be expected that tokens for those values will be found in each of the compressed data rows. As mentioned previously Oracle builds a token table in each block so there are two tables in this block, the first, starting at offset 0, is the token table that has 7 rows and the second, starting at offset 7, is the actual table data and there are 721 rows:

0x24:pti[0]	nrow=7		offs=0
0x28:pti[1]	nrow=721	offs=7

Oracle has a clue with this implementation of compression and can create a token that includes a data value and a token, from the same token table, to reduce that row length even further. The examples provided here won’t be demonstrating that but know that it is possible. Now let’s look at the first row in this block for the data table:

tab 1, row 0, @0x1f31
tl: 5 fb: --H-FL-- lb: 0x0  cc: 4
col  0: [ 4]  5a 5a 58 5a
col  1: [ 7]  50 45 4e 44 49 4e 47
col  2: [ 6]  62 62 64 64 66 66
col  3: [ 3]  c1 37 62
bindmp: 2c 00 01 04 02

The actual column lengths are supplied between the square brackets for each column; the total length should be the sum of those values plus 7 bytes, 4 of those for the column lengths, one for the lock byte, one for the flag byte and one for the column count. Using that information the total length should be 24 bytes; the block dump provides a different total length of 5, as reported by the tl entry. There is a line at the end of the row dump labeled bindmp (a binary dump of the row contents) revealing the actual contents of those 5 bytes. As expected there is the lock byte (0x2c), the number of columns at this location (0x01) and two bytes representing the token, reporting that 4 columns are in this token and that the reference row in the token table is row 2. So, let’s look at table 0, row 2:

tab 0, row 2, @0x1f5c
tl: 10 fb: --H-FL-- lb: 0x0  cc: 4
col  0: [ 4]  5a 5a 58 5a
col  1: [ 7]  50 45 4e 44 49 4e 47
col  2: [ 6]  62 62 64 64 66 66
col  3: [ 3]  c1 37 62
bindmp: 00 b3 04 04 05 06 cb c1 37 62

It looks almost like the data row, but the total token length is 10 bytes. Looking at the bindmp the first two bytes indicate this token is used 179 times in this block, the third byte indicates that 4 columns are in this token, the two bytes after that report that the first two columns are also tokens, 0x04 and 0x05. Going back to the token table we see that those tokens are:

tab 0, row 4, @0x1f66
tl: 7 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  5a 5a 58 5a
bindmp: 00 04 cc 5a 5a 58 5a
tab 0, row 5, @0x1f76
tl: 10 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 7]  50 45 4e 44 49 4e 47
bindmp: 00 04 cf 50 45 4e 44 49 4e 47

These are single-column tokens, and each is used 4 times in this block. This is how Oracle reduced the row length from 24 bytes to 5 to save block space. Working through the block dump it’s now possible to re-construct the 24 bytes of data the row originally contained even though it now is only 5 bytes in length.

"The way you see things depends a great deal on where you look at them from."
 - Norton Juster, The Phantom Tollbooth 

We see that Oracle doesn’t actually compress data, it replaces duplicate values with tokens and, through those tokens, reconstructs the data at query time by using the row directory and the actual row pieces in each block. Depending on the select list some tokens won’t be accessed if that data isn’t required. Of course all of this re-constructing can be expensive at the CPU level, and for full table scans of large tables performance can be an issue, especially with the “cache buffers chains” latch because Oracle is performing fewer “consistent gets – examination”. This is because Oracle has to pin blocks for a longer period due to the reconstruction. On the plus side the number of physical reads can decrease since the data is in a smaller number of blocks and can stay in the cache longer. Using basic compression is a trade-off between size and performance, and for extremely large tables or in cases where the compression savings are quite large (meaning the data is compressed more) queries may become CPU-intensive rather than I/O intensive. The good and the bad need to be weighed carefully when making the decision to use compression; choose wisely. Space is relatively inexpensive when compared to end-user satisfaction. The DBA’s idea of performance and the end-users ideas of performance use different criteria, and it’s really the end-users idea that should take precendence.

Anyone up for a swim?