Keep SYS clean, please...

25 Jul 2014

One of the customers handed over to us a delivery script to be executed under SYS. We took a look and, guess what? They wanted to create some objects in the SYS schema. What? Focus on our WTF face… No, no! Why? We rejected the change and asked for details. In short, their explanation was that the application needs to dynamically change a hidden parameter using the ALTER SESSION command, but they also want to know the value of that parameter before the change so that to be able to restore it to its previous value. It was something related to a particular report which was running slow without this hidden parameter. So, their R&D department came up with this idea of creating a view in SYS based on some X$ views and granting SELECT rights on this view to the application user. They said that granting rights on X$ is not an option, which is correct. However, is it an option to mess up the dictionary?

Why It Is a Bad Idea To Mess Up The Dictionary?

Good question, let’s see:

because it is magic, some things may not work as you might expect. Just a glimpse of this here.
because you may loose the Oracle support in case you screw up the dictionary. It’s like loosing the warranty in case you start disassembling the engine of your car and then you complain it doesn’t work as it should. I haven’t found though any clear Oracle disclaimer on this, just the advice: “do not create object in SYS schema”. They don’t tell what happens if you break the rule.
because you may have nasty surprises during catalog upgrades
because you may invalidate critical catalog objects. Have a look here.
because you may have problems deploying such an application to a customer who cares about its Oracle database

A Better Way

Have you heard about APEX? It has an interesting behavior: it may impersonate and execute code on behalf of another user. But this feature, not documented, is around for a long time. The key is the DBMS_SYS_SQL package. So, the plan is this:

create an APP_ADMIN user:

 grant create session,
       create procedure
    to app_admin identified by <pwd>.

grant EXECUTE privilege on DBMS_SYS_SQL package to APP_ADMIN:
```
 grant execute on dbms_sys_sql to app_admin;
```

in APP_ADMIN create the following function:

 create or replace function get_hidden_param(param_name varchar2)
   return varchar2
 as
   l_uid number;
   l_sqltext varchar2(300) := 'select b.ksppstvl value
                               from sys.x$ksppi a, sys.x$ksppcv b
                               where a.indx = b.indx
                                 and a.ksppinm = :param_name';
   l_myint integer;
   l_value varchar2(100);
   l_result number;
 begin
   select user_id into l_uid from all_users where username like 'SYS';

   l_myint:=sys.dbms_sys_sql.open_cursor();
   sys.dbms_sys_sql.parse_as_user(l_myint, l_sqltext,
                                  dbms_sql.native, l_uid);
   sys.dbms_sys_sql.bind_variable(l_myint, 'param_name', param_name);
   sys.dbms_sys_sql.define_column(l_myint, 1, l_value, 100);
   l_result := sys.dbms_sys_sql.execute(l_myint);
   if sys.dbms_sql.fetch_rows(l_myint) > 0 then
     dbms_sql.column_value(l_myint, 1, l_value);
   end if;
   sys.dbms_sys_sql.close_cursor(l_myint);
   return l_value;
 end;
 /

grant EXECUTE rights on the above function to the application user:
```
 grant execute on get_hidden_param to <app_user>;
```

now, from the application user we can play around:

 18:56:20 SQL> select app_admin.get_hidden_param('_optimizer_mjc_enabled') special_param from dual;

 SPECIAL_PARAM
 -------------
 FALSE

 18:56:22 SQL> alter session set "_optimizer_mjc_enabled"=true;

 Session altered.

 18:56:24 SQL> select app_admin.get_hidden_param('_optimizer_mjc_enabled') special_param from dual;

 SPECIAL_PARAM
 -------------
 TRUE

Conclusion

If you can, you should rely as less as possible to any internal/hidden/undocumented features
If you really have to do it, then be gentle at least with the system. Don’t mess up the dictionary!
The APP_ADMIN user is a very powerful user, now that it has the DBMS_SYS_SQL granted. But so is SYS and if you were to choose between creating objects directly in SYS and isolating sensible/powerful code in another highly protected user, I’d rather vote for the second approach.
Because DBMS_SYS_SQL is used a lot in APEX and in other Oracle products I expect to stay around for a while. This is a legitimate concern: as soon as you create a dependency between your code and an undocumented Oracle feature you’re at the mercy of Oracle. They may remove or change that feature without any warnings and then everything will break deep down in your application guts.

MEMORY_TARGET Not Supported on This System

21 Jul 2014

I wasn’t aware, until this very moment, that there’s a strong correlation between the MAX_MEMORY_TARGET parameter and the ORA-00845: MEMORY_TARGET not supported on this system error. I thought that the MEMORY_TARGET is all that counts, considering that the actual memory allocation is based on this setting. However, apparently that’s not the case: Oracle will check the MAX_MEMORY_TARGET too and will complain if the value of this parameter cannot be honored by the available memory from the system.

The Proof

Let’s have a look on the following system:

[oracle@ion ~]$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda1              49G   20G   27G  43% /
tmpfs                 2.0G     0  2.0G   0% /dev/shm

As we can see, there are 2G of free shared memory in our tmpfs. Let’s configure our database with a MEMORY_TARGET of 1G and a MAX_MEMORY_TARGET of 4G.

SQL> alter system set memory_target=1G scope=spfile;

System altered.

SQL> alter system set memory_max_target=4G scope=spfile;

System altered.

Let’s restart the instance:

SQL> startup force
ORA-00845: MEMORY_TARGET not supported on this system

Ups, it doesn’t start up because the MEMORY_MAX_TARGET is set too high.

More Than One Database

If there are many databases running on the same host then things start to get interesting (kind of). I already have a database running:

[oracle@ion ~]$ ps aux | grep pmon
oracle    7161  0.0  0.4 1284760 19700 ?       Ss   12:21   0:00 ora_pmon_iondb

And the allocation in tmpfs is:

[oracle@ion ~]$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda1              49G   20G   27G  43% /
tmpfs                 2.0G  250M  1.7G  13% /dev/shm

Good! Let’s start the second database on the same host, using a MEMORY_MAX_TARGET of 1800M and a MEMORY_TARGET of 1500MB. I hand crafted these parameters into a customized init.ora file.

[oracle@ion ~]$ ORACLE_SID=iondb2 sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jul 21 12:55:45 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup pfile='/home/oracle/init.ora'
ORA-00845: MEMORY_TARGET not supported on this system

Pretty obvious, but please note that the value of the MEMORY_MAX_TARGET is checked against the free available space from the tempfs, not against the total size of the tempfs. This is important to keep in mind, especially because Oracle doesn’t allocate the whole memory from the very beginning, which means that you might be able to startup both databases but, as they begin to allocate stuff in their own SGA, you might end up with this ORA-00845 error later.

Shrink Segments

18 Jul 2014

I don’t know why I haven’t paid more attention to this feature. It is around from Oracle 10g, yet I still defrag tables using the old school approach: ALTER TABLE … MOVE. Well, that’s something I’m going to change! We need to shrink some big tables on a production system and we come up with a rough estimation of four hours of downtime, just to do this segment reorganization. Of course, the users are not happy with this large downtime window (and when I say users I mean the managers), therefore we need to do it with the least level of disruptions. By the way, it’s an Oracle standard edition. Forget online reorganization!

Things to be tested:

the space gain after shrinking
is it really an online operation? how does it work when there are concurrent sessions updating the target table?
are the triggers a problem if any are defined on the target table?
does it maintain the associated indexes?
does also shrink the LOB segments behind the columns?

Setup playground

Target DB is: Oracle Database 11g Release 11.1.0.6.0 - 64bit Production

Create an Oracle user:

create user talek identified by <pwd>;
grant resource, connect to talek;
grant execute on dbms_lock to talek;

Connect to the TALEK user and create the following objects:

create table big_table (id integer primary key, text varchar2(300), document clob);
create sequence big_table_id;
alter table big_table enable row movement;

Populate the big table:

begin
  for i in 1..5 loop
    insert into big_table select big_table_id.nextval, 
                                object_name, 
                                dbms_random.string('a', dbms_random.value(1000,32000)) 
                            from all_objects;
    commit;
  end loop;
end;
/

Now, that we have the table, let’s fragment it:

delete from big_table where id between 1000 and 100000;
commit;

Good! Let’s also add a dummy trigger:

create or replace trigger trg_big_table_dummy 
  before insert or update or delete
  on big_table
begin
  raise_application_error(-20000, 'Read only!');
end;
/

Let’s have a look on the fragmentation:

17:03:02 SQL> exec dbms_stats.gather_table_stats(user, 'big_table', cascade => true);

PL/SQL procedure successfully completed.

17:04:05 SQL> select table_name,round((blocks*8),2) "size (kb)" ,
17:04:05   2         round((num_rows*avg_row_len/1024),2) "actual_data (kb)",
17:04:05   3         (round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) "wasted_space (kb)"
17:04:05   4  from user_tables
17:04:05   5  where table_name='BIG_TABLE';

TABLE_NAME size (kb) actual_data (kb) wasted_space (kb)
---------- --------- ---------------- -----------------
BIG_TABLE     113416         19472.43          93943.57

How about the LOB segment?

17:08:51 SQL> select bytes/1024/1024 lob_size
17:08:51   2    from user_segments
17:08:51   3   where segment_name = (select segment_name from user_lobs where table_name='BIG_TABLE');

LOB_SIZE
--------
    2112

17:42:27 SQL> select sum(length(document))/1024/1024 real_lob_size from big_table;

REAL_LOB_SIZE
-------------
  197.500557

Perfect! Let’s simulate a hot storm of short locks on the target table:

17:29:51 SQL> declare
17:29:51   2    l_min integer;
17:29:51   3    l_max integer;
17:29:51   4  begin
17:29:51   5    select min(id), max(id)
17:29:51   6      into l_min, l_max
17:29:51   7      from big_table;
17:29:51   8    loop
17:29:51   9      -- forever
17:29:51  10      for l_rec in (select 1 from big_table 
                                 where id = dbms_random.value(l_min, l_max) 
                                 for update) loop
17:29:51  11        commit;
17:29:51  12      end loop;
17:29:51  13      dbms_lock.sleep(0.1);
17:29:51  14    end loop;
17:29:51  15  end;
17:29:51  16  /

Shrinking Process

From another session start the shrinking process:

17:44:55 SQL> alter table big_table shrink space compact cascade;

Table altered.

Elapsed: 00:02:56.33

Let’s try to also move the HWM:

17:38:40 SQL> alter table big_table shrink space cascade;

... is just waiting forever ...

It’s obvious! We’re not going to be able to move the HWM as long as we have active transactions on that table. So, we’ll stop the storm session and try again. This time the HWM move will succeed:

17:51:30 SQL> alter table big_table shrink space cascade;

Table altered.

Elapsed: 00:00:28.46

Interesting enough, the HWM move wasn’t a lightning fast operation. It took 28s on a pretty small table. Anyway, let’s see the fragmentation after shrinking:

TABLE_NAME size (kb) actual_data (kb) wasted_space (kb)
---------- --------- ---------------- -----------------
BIG_TABLE      22544         19472.43           3071.57

LOB_SIZE
--------
386.5625

Great! It worked! How about the indexes status after shrink?

18:05:04 SQL> select distinct status from user_indexes where table_name='BIG_TABLE';

STATUS
--------
VALID

Comparison with the MOVE TABLESPACE approach

Let’s compare the shrink process with the other approach of using the ALTER <table> MOVE. We’re interested in finding the compression rate:

18:09:40 SQL> alter table big_table move tablespace users;

Table altered.

The compression rate is:

TABLE_NAME size (kb) actual_data (kb) wasted_space (kb)
---------- --------- ---------------- -----------------
BIG_TABLE      22264         19472.43           2791.57

LOB_SIZE
--------
386.5625

Not a big difference at all.

Conclusions

The shrink segment process can take place, indeed, online. The only exception is during the HWM movement.
During the shrink process the top wait event was: db sequential read.
The indexes are maintained, which is great. But don’t forget to use the CASCADE clause.
Only segments in automatic segment space management tablespaces can be shrunk using this method.
You can’t shrink tables which have functional indexes defined on them.
Compressed tables are not supported.
There are other not supported features as well, not the same from one version to another (e.g. in 10g LOBs can’t be shrunk).
It is supported on standard edition.
I’m not sure, but I assume the rows movement inside the segment is done in a “deferrable transaction” way, otherwise would not be possible to shrink a parent table. Remember, the shrink is implemented as a bunch of DELETE/INSERT operations.
Tracing the session doing the shrink operation I wasn’t able to see the DELETE/INSERT sequence of commands, which means that the shrink takes place deep underneath, in the Oracle kernel guts.
The triggers are not a problem. Only if they depend somehow on the ROWID.
You cannot shrink in parallel.

The v$session_longops view is not updated throughout this shrink process. However, you can use the following query as a rough estimation of the amounts of read/writes as part of the shrink process:

 select a.event, a.WAIT_TIME, c.SQL_TEXT, 
       c.PHYSICAL_READ_BYTES / 1024 / 1024 / 1024 "GB_READ", 
       c.PHYSICAL_WRITE_BYTES / 1024 / 1024 / 1024 "GB_WRITE"
 from v$session_wait a , v$session b , v$sql c
 where a.SID = <sid_of_the_shrink_session> 
 and a.sid = b.sid
 and b.SQL_ID = c.SQL_ID;

When you move the HWM, this counts as a DDL on the table and all the cached cursors from the library cache will be invalidated.

The HWM movement is not as fast as one might think. It happened to me once to have something like this:

 16:30:15 SQL> alter table "PCP"."ORCHESTRATION_REPORT" shrink space COMPACT cascade;
 Table altered.

 Elapsed: 00:11:16.47

 16:42:34 SQL> alter table "PCP"."ORCHESTRATION_REPORT" shrink space cascade;
 Table altered.

 Elapsed: 00:47:54.28 Yes, the HWM movement took more than the online defrag!

You may choose to shrink just a LOB using: ALTER TABLE <table> MODIFY LOB(<lob_column>) (SHRINK SPACE)
There’s also a so-called segment advisor but, apparently, it needs to be licensed under the diagnostics pack since it heavily relies on AWR.
Using the shrink approach is more appealing then online reorganization because it’s far simpler and there’s no need for additional storage. Anyway, as I said, online reorganization is not available on standard edition.

References

Some interesting Oracle support notes:

Older Newer

Alec's Blog