In Memory Undo

In 2008, Zagreb, I attended 3 days presentation "Reactive Performance Management Intensive", by Craig Shallahamer founder of OraPub. This was a great presentation on a many features I have to admit I really knew nothing about.

So, in some direct talk with Craig, I asked him where does he get this rare knowledge, because I have never seen so deep understanding of very cool Oracle subjects. And the best of all most of them are never shown around Internet in written manuals? He answered me: "I read US patents from Oracle and try to connect new features with themes that they have copyrighted!" I was amazed with an answer and this approach took me in one moment for his follower.

One of it's presentation part was "In-Memory Undo (IMU)" where Craig friendly admit that he has a doubt able slide(s) from that area. So I was promised that he'll make review and let us now the result of it's changes. Regardless Craig react very soon (22nd January 2009) I have forgotten for that promise. But recent article on DBA Village remind me on this. So I decided to share original document that help a lot to understand the whole idea of IMU.

PDF can be downloaded for free from Craig's WEB site

For those who doesn't want to read the whole document here is briefly about IMU:

• In a nutshell, “traditional” undo uses physical blocks, just like any other segment. If a query needs to read an undo block to maintain read-consistency, it still has to go through all the latch and potentially I/O overhead of any other query. Additionally (and this is probably new for many of us, but now seems obvious), undo block changes also must maintain redo information for recovery purposes.
• In-memory undo replaces undo blocks with an in-memory control structure called an IMU node. Instead of writing a change to an undo buffer, undo is written to the IMU node and persists in memory even after commit. A read-consistent query needs none of the overhead associated with physical blocks, so speed is dramatically improved.
  
• Craig’s tests showed a 21% drop in CPU time and a 6.5% drop in wall-clock time for a load test that used IMU versus one that used physical undo.
• He attributes the not as dramatic wall-clock improvement to I/O overhead that isn’t reflected in the CPU timing.

What to say at the end but respect Craig, great respect to you and your work!

7 Golden Rules That Make You a Better Programmer

Steven Feuerstein published a presentation titled Golden Rules for Developers. It is one of the most comprehensive and original rules that I have ever read. Here is a brief summary of his seven "golden rules" that will help you write better code:

1. Don’t repeat anything.
2. Hide everything.
3. Don’t take shortcuts.
4. Embrace standards.
5. Build on a foundation.
6. Never lose information.
7. Don’t write code alone.

Amazing! Bravo Steve!

Cheers!

Words of wisdom for 2011. Year

Health

1. Drink plenty of water.
2. Eat breakfast like a king, lunch like a servant, and diner like a beggar.
3. Eat more foods that grow on trees and plants, and less industrially produced.
4. Live with the 3 E: E(nergy) - E(nthusiasm) - E(mpathy)
5. Make time for meditation.
6. Play as much as you can.
7. Read more books than you in did in 2010.
8. Sit in silence for at least 10 minutes a day.
9. Sleep for 7 hours.
10. Walk 10-30 minutes every day. And smile while walking.

Charisma

11. Do not compare your life with others. You have no idea what is actually going on in their minds.
12. Do not have negative thoughts or things you can not control. Instead, invest your energy in the positive present moment.
13. Do not do too much. Do not exceed your limits.
14. Do not be too serious.
15. Do not throw your precious energy on gossip.
16. Dream more while you are awake.
17. Envy is a waste of time. You already have everything you need ...
18. Forget issues of the past. Do not remind your partner on his / her mistakes of the past. It will ruin your happiness.
19. Life is too short to waste time on hate. Do not hate the other.
20. Reconciled with its past, not to spoil the present.
21. Nobody is in charge of your happiness except yourself.
22. Realize that life is school, and you are here to learn. Problems are simply part of the curriculum, appear and disappear, but the lessons you learn will last a lifetime.
23. Smile and laugh as much as possible!
24. You do not have to win every debate. Agree to disagree ...

Company

25. Often interact with members of his family.
26. Each day give something good to others.
27. Forgive everyone for everything, and finally forgive myself!
28. Spend time with people older than 70 and younger than 6 years.
29. Every day try to smile at least three people.
30. What others think about you is not your concern.
31. Your job is not to worry about you when you're sick, but your friends will. So keep in touch.

Life

32. Do the right thing!
33. Get rid of anything that is not useful, beautiful or not brings joy.
34. Love heals all.
35. No matter how the situation was good or bad, will change ...
36. Regardless of how you feel - get up, dress up and leave.
37. The best is yet to come ...
38. When you wake up alive in the morning, thank God for that.

Last but not least

Share this with everyone to whom you care!

Automatic Memory Management (11g)

Recently I have partially read Oracle 11g: New features for Administrator. In it I found some nice parts about Automatic Memory Management, which I'd like to accent here more as a small memo for me then as a standalone blog topic for other. This is mainly because most of the things are well know from other Oracle's manuals and books but  clearness of presentation and compress of information is somehow the main reason of writing this blog topic here.

Automatic Memory Management was a new feature introduced in 10g. With 10g release Oracle has come up with a new parameter called sga_target which was used to automatically manage the memory inside SGA. The components which were managed by sga_target are db_cache_size, shared_pool_size, large_pool_size, java_pool_size and streams_pool_size. With 11g, Oracle went a step further to manage both SGA as well as PGA automatically. Oracle database 11g introduced 2 new parameters
memory_target and memory_max_target.

memory_target

The memory_target parameter is somehow somehow a combination of the sga_target parameter value and the pga_aggregate_target parameter, representing the total amount of memory that Oracle has to allocate between the various SGA and PGA structures. The memory_target parameter is dynamic and can be changed up to and including the value of memory_max_target.

memory_max_target

The memory_max_target parameter allows dynamically change the value of the parameter memory_target within the confines of memory_max_target. Thus you can adjust the total amount of memory available to the database as a whole at any time. There are known limitations:

1. The memory_target and memory_max_target parameters cannot be used when lock_sga parameter has been set.
2. memory_target and memory_max_target cannot be used in conjunction with huge pages on Linux or Windows

Ant the best of all, here is "how to" diagram that shows effects of memory init.ora parameters:

The End

While setting these new parameters (memory_target and max_memory_target), one has to be careful. A general advice would be to set sga_target and pga_aggregate_target to a minimum fixed value and set memory_target. Oracle will automatically increase sga_target and pga_aggregate_target to the desired level in specific moments.
If you are upgrading 10g database to 11g and want to keep the current value of sga_target and pga_aggregate_target, than make sure you set the value:

memory_target>=(sga_target + pga_aggregate_target)

With new hierarchy in memory management, Oracle has also introduced new memory advisory. You can view V$MEMORY_TARGET_ADVICE view to get advice on the optimal value of memory_target parameter. This view will show advisory data only after you set mentioned memory_target parameter.

Cheers!

Apex feature for non Apex users

In one of mine previous posts (SQL based export data to file) I have shown how to export data to flat file by plain sql statement. Here is also simple but different way of use of another cool Oracle "out of the box" feature from Apex.

Since Apex is now part of every Oracle database, these Apex ready made utilities are useful even if you aren't using Apex. Let me show you easy example of how to use SQL based on dynamic (custom) lists.

SQL> declare
  2    l_arr apex_application_global.vc_arr2;
  3    l_str varchar2(4000);
  4  begin
  5    -- Convert delimited string to array
  6    l_arr := apex_util.string_to_table('one,two,three,four,five', ',');
  7    for i in 1..l_arr.count loop
  8      dbms_output.put_line(l_arr(i));
  9    end loop;
 10    
 11    -- Convert array to delimited string with ";"
 12    l_str := apex_util.table_to_string(l_arr,';');
 13    dbms_output.put_line(l_str);
 14  end;
 15  /
one
two
three
four
five

one;two;three;four;five

PL/SQL procedure successfully completed.

SQL> 

With dynamic lists it is very easy to construct functions/procedures whose parameters can easy handle dynamic number of elements.

create or replace function count_dyn_items (p_str in varchar2, p_delim in varchar2) 
return pls_integer
is
  l_arr apex_application_global.vc_arr2;
begin
  l_arr := apex_util.string_to_table(p_str,p_delim);
  return l_arr.count;
end;
/

And the usage of that function is like:

SQL> select count_dyn_items ('a,b,c,d',',') from dual;

COUNT_DYN_ITEMS('A,B,C,D',',')
------------------------------
                             4

SQL> select count_dyn_items ('A1;A2;A3;B;RT;12;22',';') from dual;

COUNT_DYN_ITEMS('A1;A2;A3;B;RT;12;22',';')
------------------------------------------
                                         7

SQL> 

All other is just imagination.

Cheers!

dbhome script

Recently I was looking for some files when I found out in /usr/local/bin directory file named dbhome. This is original Oracle script (available only for non Windows editions), which greps content of oratab file (usually stored in ./etc directory) and shows parent %ORACLE_HOME% for mentioned SID.

Usage is easy:

dbhome SID

Live example:

[oracle@serverX ~]$ dbhome HAC
/u01/HAC/hacdb/10.2.0

[oracle@serverX ~]$ dbhome HACT
/u01/HACT/hactdb/10.2.0

[oracle@serverX ~]$ dbhome HLPD
/u01/HLPD/hlpddb/10.2.0

The End

Even they exists, ASM (and CRS) are not real Oracle databases (regardless they usually have own Oracle home) their values are not stored in oratab so they cannot be queried with this script.

[oracle@serverX ~]$ dbhome ASM
/home/oracle

Cheers!