The Arup Nanda Blog

Revived Boston Area DBA SIG Meeting

2011-11-20T18:15:00.001-05:00

The DBA SIG of the Northeast Oracle User Group has been revived (thank you, Lyson and Jeane) and I was honored to be the speaker of the first session of what I hope will be a long list of very successful like the old days.

I started at 7 PM and finished at midnight - a solid 5 hours later! Thank you for your patience. It just made my day to have you in the audience that late. I hope you found it useful.

Here is the slide deck and the scripts I used during my session. As in the past, I cherish the moments and will highly appreciate to have your feedback.

Revived NOUG DBA SIG Events

2011-11-10T15:41:00.001-05:00

Congratulations to North East Oracle User Group in the Boston area who has restarted the DBA SIG. This was a highly successful program that used to be held after work hours on a weekday in the Oracle building in Burlington. I was privileged to have presented there from 2004 until its sad demise in 2009. Now, I am honored to be invited to be the first speaker in the revived program. I am presenting the session "Addressing Performance Issues during Change with Real Application Testing, Intelligent Stats and SQL Plan Baselines with Live Demos". More information here.

When: Nov 16th 6:30 PM to 9:00 PM
Where: Doubletree by Hilton at 5400 Computer Drive, Westborough, MA 01581
Food and drinks will be served.
The event is free to all NOUG members.

I will give away several Oracle books at the event for the best questions, etc.

If you plan on attending this, the organizers respectfully request that you RSVP to treasurer@noug.com immediately. They need some reasonably accurate headcount to order food and drinks, which is yet another reason to attend.

Abstract: Change is inevitable - be it applying a patchset or creating an index. In this session you will learn how to harness the power of three major features of Oracle database to improve the performance during any types of change, or at any other time. You will learn, with plenty of demos, how to configure and use Database Replay and SQL Performance Analyzer to predict performance, use extended statistics to make the optimizer more intelligent and use SQL Plan Baselines to make the performance consistent but open to further improvements.

As it has been the norm, I plan to explain these concepts and techniques with lots of live demos. If you are in the Boston area, I sincerely hope to see you all there. I have nothing but pleasant memories every one of the 5 times I have presented in that venue and expect nothing less this time.

AIOUG Webcast: Methodical Performance Tuning

2011-10-18T01:00:00.000-04:00

A big thank you to all those you attended my session today. I sincerely hope you got something out of it. Here are the scripts I used in the demo. And, here is the slide deck, if you are interested.

Remember, this was just the beginner's session. We will have intermediate and advanced ones in near future. Stay tuned through the AIOUG site.

Migration to Exadata Session at #OOW11

2011-10-06T20:40:00.001-04:00

Considering it was the last session of #OOW11 I was surprised to see a sizable number of folks showing up for my 3rd and final session slated for 3 to 4 PM on Thursday. Thank you for attending and for your questions.

Here is the slide deck. Note: please do not click on the link. Instead, right click on it, save the file and open it. It's a Powerpoint show; not a PPT. You can download free Powerpoint player to watch it, if you don't have Powerpoint installed.

Rise of the Machines

2011-10-05T23:03:00.001-04:00

This is the penultimate day of #OOW11 and I am here at the hotel lobby trying to put some order around the myriads of nuggets of information I have had over the last several days.

The announcements this year have been centered around introduction of various new products from Oracle - Oracle Database Cloud, Cloud Control, Database Appliance, Big Data Appliance, Exalytics, T4 Super cluster and so on. One interesting pattern that emerges from the announcements that is different from all the previous years is the introduction of several engineered and assembled systems that perform some type of task - specialized or generic. In the past Oracle announced machines too; but not so many at the same time, leading to an observation by April Sims (Executive Editor, Select Journal) that this year can be summed up in one phrase - Rise of the Machines.

But many of the folks I met in person or online were struggling to put their head around the whole lineup. It's quite clear that they were very unclear (no pun intended) how these are different and what situation each one would fit in. It's perfectly normal to be little confused about the sweet spots of each product considering the glut of information on them and seemingly overlapping functionalities. In the Select Journal Editorial Board meeting we had earlier this morning, I committed to writing about the differences between the different systems announced at #OOW11 and their usages in Select Journal 2012 Q1 edition. I didn't realize at that time what a tall order that is. I need to reach out to several product managers and executives inside Oracle to understand the functionality differences in these machines. Well, now that I have firmly put my feet in mouth, I will have to do just that.

In the demogrounds I learned about Oracle Data Loader for Hadoop and Enterprise-R, two exciting technologies that will change the way we collect and analyze large data sets, especially unstructured ones. Another new technology, centered around Cloud Control, was the Data Subsetting. It allows you to pull a subset of data from the source system to create test data, mask it if necessary and even find sensitive data based on some format. The tool was due for quite some time.

Again, I really need to collect my thoughts and sort through all that information overload I was subjected to at OOW. This was the best OOW ever.

Unicode Migration Assistant for Oracle

2011-10-04T03:03:00.001-04:00

When you want to convert a database created in the default characterset to a multibyte characterset, there were two basic approaches - the safe export/import and the not-for-the-faint-of-the-heart alter database convert internal. In either case you had to follow a string of activities - checking the presence of incompatible values by running csscan, etc.

There is a new tool from Oracle to make the process infinitesimally simpler - Migration Assistant for Unicode. It's a GUI tool that you can install on the client. A server side API (installed via a patch) does all the heavy lifting with the client GUI providing a great intuitive interface. You have the steps pretty much laid out for you. But the main strength of the tool is not that. There are two primary differentiators for the tool.

When you do have a bad character, what can you really do? You can truncate the part of the data. But how do you know how much to truncate? If you truncate aggressively, you may shave off a chunk and lose valuable data; but be miserly and you risk having the bad data in place. This tool will show the data in a separate window allowing you to correct only the affected data; nothing less, nothing more.
When users copy and paste data from some unicode compliant system to Oracle, e.g. from MS Word to a VARCHAR2 field in the database, the characters may look garbled; but given proper characterset they become meaningful. This tool allows you to see the data in many charactersets to identify which one was used to create it in the first place. After that it's a simple matter to reproduce that characters in the proper characterset.

With these two differentiators in place, the tool has great future. Check out everything on this tool at http://www.oracle.com/technetwork/database/globalization/dmu/overview/index-330958.html or just visit the booth at #OOW Demogrounds in Moscone South.

Oh, did I mention that the tool is free?

OOW11 Session #2 Exadata Management

2011-10-04T02:23:00.001-04:00

Thank you all for attending my second session in #OOW11 - Exadata Management. You can download the slide deck here. Important: DO NOT CLICK on this link; instead, right click on this link, save the file and then open it.

Here is the slide deck.

Here are the resources I referred to in the presentation. Please note: URLs could change without my knowledge.

•My Papers

–5-partLinux Commands article series http://bit.ly/k4mKQS

–4-partExadata Command Reference article series http://bit.ly/lljFl0

•OTN Page on Exadata

–http://www.oracle.com/technetwork/database/exadata/index.html

•Tutorials

–http://www.oracle.com/technetwork/tutorials/index.html

•OTN Exadata Forum

–https://forums.oracle.com/forums/forum.jspa?forumID=829

Thanks for attending. As always, your feedback will be highly appreciated.

OOW11 Presentation: Exadata for Oracle DBAs

2011-10-02T22:06:00.000-04:00

Thank you very much for attending my session "Exadata for Oracle DBAs" at #OOW11 (Oracle Open World), 2011. Here are the links to I mentioned in the presentation:

5-part Linux Commands article series http://bit.ly/k4mKQS

4-part Exadata Command Reference article series http://bit.ly/lljFl0

You can download the Powerpoint show here http://bit.ly/nH0rpK (please don't click. Right click and download it to watch)

Update: If you have downloaded the slides earlier, please redownload. I corrected a small inaccuracy - the compression is handled by compute nodes; decompression can be offloaded to the cells. Thanks to Greg Rahn for pointing it out.

Of course, please remember that your feedback - good or bad - is always valued.

Setting Up Oracle Connection Manager

2011-08-29T19:24:00.000-04:00

The Problem

It seems really simple. We have an Oracle database (on all nodes of a full rack Exadata, to be exact), which a lot of end-users connect to through apps designed in a rather adhoc and haphazard manner - on Excel spreadsheets, Access forms, TOAD reports and other assorted tools. We want to control the access from these machines and streamline them.

The database machine sits behind a firewall. To allow the adhoc tools accessing the database from the client machines mean we have to change the firewall rules. Had it been one or two clients, it would have been reasonable; but with 1000+ client machines, it becomes impractical. So I was asked to provide an alternative solution.

The Solution

This is not a unique problem; it's the same problem when the machines need to access resources that exist across firewalls. The easy solution is to punch a hole through the firewall to allow that access; but is not desirable for obvious security reasons. A better solution, often implemented, is to have a proxy server. The proxy sits between the two layers of access and can access the servers behind the firewall. Clients make the request to the proxy which it passes on to the server.

Such a proxy solves the problem; but we are looking for a simpler solution. Does one exist?

Yes, it does. The answer is Connection Manager from Oracle. Among its many functions, one stands out - it acts as a proxy between the different layers of access and passes through the request. It's not a separate product; but is an option in the Oracle Client software (not the database or grid infrastructure software). This option is not automatically installed. When installing the client software, choose "Custom" and explicitly select "Connection Manager" from the list.

The Architecture

Let's quickly go through the architecture of tool. Assume there are three hosts:

client1 - the machine where the client runs and wants to connect to the database
dbhost1 - the machine where the database instance runs
cmhost1 - the machine where the Connection Manager process runs

Here is a rough network diagram of the three machines

From client1 you can reach cmhost1 but not dbhost1:

C:\>ping dbhost1
Ping request could not find host dbhost1. Please check the name and try again.

Or, if the host is known but not reachable, you will notice a message like this:

C:\>ping dbhost1

Pinging dbhost1 [192.168.104.31] with 32 bytes of data:

Request timed out.

However, you can ping the dbhost1 from cmhost1 and cmhost1 from client1.

Normally the TNS entry at the client machine would have looked like this:

TNS_REG =

(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)(HOST = dbhost1)(PORT = 1521)
)
(CONNECT_DATA =
(SERVICE_NAME=srv1)
)
)

However, this will not work since the client does not even know the routing for dbhost1. Instead the client connects to the CM host. The connection manager has two processes

The Connection Manager Admin Process (cmadmin)
One or more Connection Manager Gateway Processes (cmgw)

The CMGW processes allows the client connections to come in through them. The admin process manages the gateways. We will cover more on that later.

After setting up the CM processes, you will need to rewrite the TNSNAMES.ORA in the following way:

TNS_CM =
(DESCRIPTION =
(SOURCE_ROUTE = YES)
(ADDRESS =
(PROTOCOL = TCP)(HOST = cmhost1)(PORT = 1950)
)
(ADDRESS =
(PROTOCOL = TCP)(HOST = dbhost1)(PORT = 1521)
)
(CONNECT_DATA =
(SERVICE_NAME=srv1)
)
)

How it Works

Note the special parameter:

SOURCE_ROUTE = YES

This tells the client connection request to attempt the first address listed first and only then attempt the next one. This is different from load balance setups where you would expect the client tool to pick one of the addresses at random. So the client attempts this address first:

(PROTOCOL = TCP)(HOST = cmhost1)(PORT = 1950)

This is the listener for Connection Manager. The clients are allowed to connect to the port 1950 (the port where CM listener listens on) on the host cmhost1.

After that the connection attempts the second address

(PROTOCOL = TCP)(HOST = dbhost1)(PORT = 1521)

However, it will fail since the client does not have access to the port 1521 of the host dbhost1. This is where CM comes in. The client does not make the request; CM does on behalf of the client connection that just came in. The connection manager (running on cmhost1) makes the request with that address. Since the host cmhost1 can access dbhost1 on port 1521, that connection request goes through successfully. When the response comes back from the database, CM passes it back to the original client.

A single CM connection can handle many client connection requests.

Setting Up

Now that you know how CM works, let's see how to enable it, step by step.

(1) Install CM, if you don't have already. Check for a file cmctl under $ORACLE_HOME/bin. If you have it, CM may have been installed already. If not, install CM by running the installer from Oracle Client (not Database or Grid Infra) software. Choose Custom Install and explicitly choose Connection Manager.

(2) Go to $OH/network/admin (remember the $OH of the client software home; not the database home)

(3) You need to create a configuration file called cman.ora. Instead of creating it from scratch, go to the samples subdirectory and copy the cman.ora sample file back into the admin directory.

(4) In the file cman.ora, make the changes to the following lines. Of course, I assumed cmhost1 as the server running Connection Manager process. Substitute by whatever name you choose for the CM server. I also assumed you would use port 1950 for the CM listener. It does not have to be. Whatever you choose will need to be opened in the firewall.

The lines you are changing will be at the beginning and the end of the cman.ora file.

cman_cmhost1 =

(configuration=

(address=(protocol=tcp)(host=cmhost1)(port=1950))

(parameter_list =

...

# conn_stats = connect_statistics

(rule_list=

(rule=

(src=*)(dst=*)(srv=*)(act=accept)

(action_list=(aut=off)(moct=0)(mct=0)(mit=0)(conn_stats=on))

)

Keep the remaining lines as is, for now. I will explain the meaning of these parameters later.

(5) Start the CM command line interface by executing "cmctl"

# cmctl

CMCTL for Linux: Version 11.2.0.1.0 - Production on 29-AUG-2011 15:16:01

Welcome to CMCTL, type "help" for information.

CMCTL>

This will show a prompt "CMCTL>". Here you will enter different admin commands for the CM processes, much like LSNRCTL command line interpreter.

(6) Start the administration process by typing "administer"

CMCTL> administer

Current instance CMAN_cmhost1 is not yet started

Connections refer to (address=(protocol=tcp)(host=cmhost1)(port=1950)).

The command completed successfully.

CMCTL:CMAN_cmhost1>

Note how the prompt changed showing the name of the CM.

(7) Start the connection manager processes by issuing "startup"

CMCTL> startup

Starting Oracle Connection Manager instance CMAN_cmhost1.proligence.com. Please wait...

TNS-04077: WARNING: No password set for the Oracle Connection Manager instance.

CMAN for Linux: Version 11.2.0.1.0 - Production

Status of the Instance

----------------------

Instance name cman_cmhost1.proligence.com

Version CMAN for Linux: Version 11.2.0.1.0 - Production

Start date 29-AUG-2011 17:25:48

Uptime 0 days 0 hr. 0 min. 9 sec

Num of gateways started 2

Average Load level 0

Log Level OFF

Trace Level OFF

Instance Config file /opt/oracle/product/11gR2/client1/network/admin/cman.ora

Instance Log directory /opt/oracle/product/11gR2/client1/network/log

Instance Trace directory /opt/oracle/product/11gR2/client1/network/trace

The command completed successfully.

(8) If everything goes well, you should see the CM status

CMCTL:CMAN_cmhost1> show status

Status of the Instance

----------------------

Instance name cman_cmhost1

Version CMAN for Linux: Version 11.2.0.1.0 - Production

Start date 30-JUN-2011 13:26:16

Uptime 60 days 2 hr. 15 min. 10 sec

Num of gateways started 2

Average Load level 1

Log Level ADMIN

Trace Level OFF

Instance Config file /opt/oracle/product/11gR2/client1/network/admin/cman.ora

Instance Log directory /opt/oracle/product/11gR2/client1/network/log

Instance Trace directory /opt/oracle/product/11gR2/client1/network/trace

If it does not start, refer to the troubleshooting section later in this blog.

(9) Make the TNSNAMES.ORA file change at the client as shown earlier.

(10) Make the connection using this new TNS connection alias:

C:\> sqlplus arup/arup@TNS_CM

You should be able to connect to the database server now. Note, you still can't access the database host directly. If you use the regular TNS connect string - TNS_REG - you will fail. This new connection was established through the connection manager.

(11) Check the number of connections coming through the CM, using CMCTL tool:

CMCTL:CMAN_cmhost1> show connections

Number of connections: 1.

The command completed successfully.

The output shows there is one connection through the CM listener. As you connect more, you will see the number next to "Number of connections:" increasing.

That's it. You have successfully configured Connection Manager interface.

Fine Tuning

In the previous setup I asked you to enter some values without really explaining the significance of them. Let's go through them.

One of the powerful features of the CM interface is to act as sort of a firewall, i.e. allow connections from/to certain hosts and for specific services. You can define these inside the RULES_LIST section as shown below:

(rule_list=

(rule=

(src=x)(dst=x)(srv=*)(act=accept)

(action_list=(aut=off)(moct=0)(mct=0)(mit=0)(conn_stats=on))

)

Here are the parameters and what they mean:

src = the source server where the connection request would come from. If you want to leave it unrestricted, use "*", as a wildcard.
dst = the destination server, which is probably the database server the request would go to. Again, unrestricted access would be given as "*".
srv = the service. enter "*" for all types of services.
act = the action, e.g. accept, reject or drop the request

On src and dst parameters you can give hostnames, IP addresses as well as wildcards. You would use this section to allow or deny the access between different servers, making it a really powerful firewall-like tool.

The action_list parameter allows you to fine tune the actions on the connection.

aut = whether the Oracle Advanced Security Option authentication filter should be applied. The value shown here is OFF, means this is not to be applied.
moct = after how long the outbound connection established should timeout. The value set here is 0, means the outbound connection is never to be timed out.
mct = after how long the session should disconnect. The value is 0, i.e. never.
mit = the timeout duration for idle connections
conn_stats = whether the connection statistics be maintained.

Note the use of parentheses. You can use different rules and actions for each combination of sources and destinations. It allows you to finetune the access. For instance, database D1 is highly secure and you would want ASO filter; but not database D2. For the request coming from the same client, you can have a different set of actions for each destination. For D1, the more secure database host, you can establish various timeouts.

CMCTL Primer

Now that you know about the CMAN.ORA file, let's see the activities you can perform in CMCTL. The first command you should explore should be "help".

CMCTL> help

The following operations are available

An asterisk (*) denotes a modifier or extended command:

administer close* exit quit

reload resume* save_passwd set*

show* shutdown sleep startup

suspend*

You can get help on a specific command as well:

CMCTL> help administer

administer [] [using ] - Sets up a context for administering the given Oracle Connection Manager instance

Two commands you will be using a lot are - SHOW and SET.

CMCTL> help SHOW

The following operations are available after show

An asterisk (*) denotes a modifier or extended command:

all connections defaults events

gateways parameters rules services

status version

CMCTL> help set

The following operations are available after set

An asterisk (*) denotes a modifier or extended command:

aso_authentication_filter connection_statistics

event idle_timeout

inbound_connect_timeout log_directory

log_level outbound_connect_timeout

password session_timeout

trace_directory trace_level

Most of these modifiers are self explanatory, e.g. show status will show you the status of CM; show connections will show connections established through CM, etc.

Troubleshooting

Of course things may not go well the first time. Don't despair. You can perform extensive diagnostics and enable logging and tracing.

The most common error may come during the startup:

TNS-04012: Unable to start Oracle Connection Manager instance.

Unfortunately it's a generic, catch-all error. The most common reason is an incorrectly constructed CMAN.ORA file, e.g. with non-identifiable hostnames and port numbers, log/trace directories that do not yet exist, invalid parameters and values or even mismatched parantheses. It's difficult to guess what caused the issue. The best option for you is to copy the sample CMAN.ORA file and replace the values with your own, paying special attention to the directory names.

Another cause of this error is not using Fully Qualified Names. For instance, you must use cmhost1.proligence.com instead of "cmhost1".

Sometimes the issues still linger. You can perform extensive diagnostics by using extended tracing and logging. To enable logging and tracing, you have to set the parameters in CMAN.ORA:

log_level : set to SUPPORT

trace_level : set to SUPPORT

The default for both is OFF, means no logging and tracing.

When enabled, CM emits tracings and logging in the appropriate directories that may lead to the source of the problem. Here is an excerpt from the log:

(LOG_RECORD=(TIMESTAMP=29-AUG-2011 16:22:26)(EVENT=CMAN.ORA contains no rule for local CMCTL connection)(Add (rule=(src=cmhost1)(dst=127.0.0.1)(srv=cmon)(act=accept)) in rule_list)

This shows clearly the issue. We have to make the appropriate entry to make CM work. You can also enable these dynamically

CMCTL:CMAN_cmhost1.proligence.com> set trace_level support

CMAN_cmhost1.proligence.com parameter trace_level set to support.

The command completed successfully.

Takeaways

Connection Manager is a great product form Oracle Network family of products that can, among many things, perform as a connection concentrator from multiple client requests, act as a rule based mini-firewall for the database requests and act as a proxy between different access domains. Here you learned how to set it up, fine tune the parameters and manage it effectively. Hope you liked it. As always, please provide your feedback.

Difference between Select Any Dictionary and Select_Catalog_Role

2011-07-24T23:27:00.000-04:00

When you want to give a user the privilege to select from data dictionary and dynamic performance views such as V$DATAFILE, you have two options:

grant select any dictionary to ;
grant select_catalog_role to ;

Did you ever wonder why there are two options for accomplishing the same objective? Is one of them redundant? Won't it make sense for Oracle to have just one privilege? And, most important, do these two privileges produce the same result?

The short answer to the last question is -- no; these two do not produce the same result. Since they are fundamentally different, there is a place of each of these. One is not a replacement for the other. In this blog I will explain the subtle but important differences between the two seemingly similar privileges and how to use them properly.

Create the Test Case

First let me demonstrate the effects by a small example. Create two users called SCR and SAD:

SQL> create user scr identified by scr;
SQL> create user sad identified by sad;

Grant the necessary privileges to these users, taking care to grant a different one to each user.

SQL> grant create session, select any dictionary to sad;

Grant succeeded.

SQL> grant create session, select_catalog_role to scr;

Grant succeeded.

Let's test to make sure these privileges work as expected:

SQL> connect sad/sad

Connected.

SQL> select * from v$session;

... a bunch of rows come here ...

SQL> connect scr/scr

Connected.

SQL> select * from v$datafile;

... a bunch of rows come here ...

Both users have the privilege to select from the dictionary views as we expected. So, what is the difference between these two privileges? To understand that, let's create a procedure on the dictionary tables/views on each schema. Since we will create the same procedure twice, let's first create a script which we will call p.sql. Here is the script:

create or replace procedure p as
l_num number;
begin
select count(1)
into l_num
from v$session;
end;
/

The procedure is very simple; it merely counts the number of connected sessions by querying V$SESSION. When you connect as SAD and create the procedure by executing p.sql:

SQL> @p.sql

Procedure created.

The procedure was created properly; but when you connect as SCR and execute the script:

SQL> @p.sql

Warning: Procedure created with compilation errors.

SQL> show error
Errors for PROCEDURE P:

LINE/COL ERROR
-------- ------------------------------------------------
4/2 PL/SQL: SQL Statement ignored
6/7 PL/SQL: ORA-00942: table or view does not exist

That must be perplexing. We just saw that the user has the privilege to select from the V$SESSION view. You can double check that by selecting from the view one more time. So, why did it report ORA-942: table does not exist?

Not All Privileges have been Created Equal

The answer lies in the way Oracle performs compilations. To compile a code with a named object, the user must have been granted privileges by direct grants; not through the roles. Selecting or performing DML statements do not care how the privileges were received. The SQL will work as long as the privileges are there. The privilege SELECT ANY DICTIONARY is a system privilege, similar to create session or unlimited tablespace. This is why the user SAD, which had the system privilege, could successfully compile the procedure P.

The user SCR had the role SELECT_CATALOG_ROLE, which allowed it to SELECT from V$SESSION but not to create the procedure. Remember, to create another object on the base object, the user must have the direct grant on the base object; not through a role. Since SCR had the role not the direct grant on V$DATAFILE, it can't compile the procedure.

So while both the privileges allow the users to select from v$datafile, the role does not allow the users to create objects; the system privilege does.

Why the Role?

Now that you know how the privileges are different, you maybe wondering why the role is even there. It seems that the system grant can do everything and there is no need for a role. Not quite.The role has a very different purpose. Roles provide privileges; but only when they are enabled. To see what roles are enabled in a session, use this query:

SQL> connect scr/oracle
Connected.
SQL> select * from session_roles
2 /

ROLE
------------------------------
SELECT_CATALOG_ROLE
HS_ADMIN_SELECT_ROLE

2 rows selected.

We see that two roles - SELECT_CATALOG_ROLE and HS_ADMIN_SELECT_ROLE - have been enabled in the session. The first one was granted to the user. The other one is granted to the first one; so that was also enabled.

Just because a role was granted to the user does not necessarily mean that the role would be enabled. The roles which are marked DEFAULT by the user will be enabled; the others will not be. Let's see that with an example. As SYS user, execute the following:

SQL> alter user scr default role none;

User altered.

Now connect as SCR user and see which roles have been enabled:

SQL> connect scr/oracle

SQL> select * from session_roles;

no rows selected

None of the roles have been enabled. Why? That's because none of the roles are default for the user (effected by the alter user statement by SYS). At this point when you select from a dynamic performance view:

SQL> select * from v$datafile;

select * from v$datafile

ERROR at line 1:

ORA-00942: table or view does not exist

You will get this error because the role is not enabled, or active. Without the role the user does not have any privilege to select from the data dictionary or dynamic performance view. To enable the role, the user has to execute the SET ROLE command:

SQL> set role SELECT_CATALOG_ROLE;

Role set.

Checking the enabled roles:

SQL> select * from session_roles;

ROLE

------------------------------

SELECT_CATALOG_ROLE

HS_ADMIN_SELECT_ROLE

2 rows selected.

Now the roles have been enabled. Since the roles are not default, the user must explicitly enable them using the SET ROLE command. This is a very important characteristic of the roles. We can control how the user will get the privilege. Merely granting a role to a user will not enable the role; the user's action is required and that can be done programmatically. In security conscious environments, you may want to take advantage of that property. A user does not always have the to have to privilege; but when needed it will be able to do so.

The SET ROLE command is an SQL*Plus command. To call it from SQL, use this:

begin

dbms_session.set_role ('SELECT_CATALOG_ROLE');

end;

You can also set a password for the role. So it will be set only when the correct password is given;

SQL> alter role SELECT_CATALOG_ROLE identified by l
2 /

Role altered.

To set the role, you have to give the correct password:

SQL> set role SELECT_CATALOG_ROLE identified by l;

Role set.

If you give the wrong password:

SQL> set role SELECT_CATALOG_ROLE identified by fl

2 /

set role SELECT_CATALOG_ROLE identified by fl

ERROR at line 1:

ORA-01979: missing or invalid password for role 'SELECT_CATALOG_ROLE'

You can also revoke the execute privilege on dbms_session from public. After that the user will not be able to use it to set the role. You can construct another wrapper procedure to call it. Inside the wrapper, you can have all sort of checks and balances to make sure the call is acceptable.

We will close this discussion with a tip. How do you know which roles are default? Simply use the following query:

SQL> select GRANTED_ROLE, DEFAULT_ROLE
2 from dba_role_privs
3 where GRANTEE = 'SCR';

GRANTED_ROLE DEF
------------------------------ ---
SELECT_CATALOG_ROLE NO

Conclusion

In this blog entry I started with a simple question - what is the difference between two seemingly similar privileges - SELECT ANY DICTIONARY and SELECT_CATALOG_ROLE. The former is a system privilege, which remains active throughout the sessions and allows the user to create stored objects on objects on which it has privileges as a result of the grant. The latter is not a system grant; it's a role which does not allow the grantee to build stored objects on the granted objects. The role can also be non-default which means the grantee must execute a set role or equivalent command to enable it. The role can also be password protected, if desired.

The core message you should get from this is that roles are different from privileges. Privileges allow you to build stored objects such as procedures on the objects on which the privilege is based. Roles do not.

Who Manages the Exadata Machine?

2011-07-24T18:05:00.000-04:00

For organizations that just procured an Exadata machine, one of the big questions is bound to be about the group supporting it. Who should it be - the DBAs, Sys Admins, Network Admins, or some blend of multiple teams?

The conventional Oracle database system is a combination of multiple distinct components - servers, managed by system admins; storage units, managed by SAN admins; network components such as switches and routers, managed by network admins; and, of course, the database itself, managed by the DBAs. Exadata has all those components - servers, storage (as cell servers), infiniband network, ethernet network, flash disks, the whole nine yards; but packaged inside a single physical frame representing a single logical unit - a typical engineered system. (For a description of the components inside the Exadata system, please see my 4-part article series on Oracle Technology Network) None of these conventional technology groups posses the skillsets to the manage all these components. That leads to a difficult but important decision - how the organization should assign the operational responsibilities.

Choices

There are two choices for organizations to assign administrative responsibilities.

Distributed - Have these individual groups manage the respective components, e.g. Sys Admins managing the Linux servers, the storage admins managing the storage cells, network admins managing the network components and finally DBAs managing the database and the cluster.
Consolidated - Create a specialized group - Database Machine Administrator (DMA) and have one of these groups expand the skillset to include the other non-familiar areas.

Each option has its own pros and cons. Let's examine them and see if we can get the right fit for our specific case.

Distributed Management

Under this model each component of Exadata is managed as an independent entity by a group traditionally used to manage that type of infrastructure. For instance, the system admins would manage the Linux OS, overseeing all aspects of it such as creation of users to applying the patches and RPMs. The storage and database would be managed likewise by the specialist teams.

The benefit of this solution is its seeming simplicity - components are managed by their respective specialists without a need for advanced training. The only need for training is for storage, where the Exadata Storage Server commands are new and specific to Exadata.

While this approach seems a nobrainer on surface, it may not be so in reality. Exadata is not just something patched up from these components; it is an engineered system. There is a huge meaning behind that qualifier. These components are not designed to act alone; they are put together to make the entire structure a better database machine. And, note the stress here - not an application server, not a fileserver, not a mail server; not a general purpose server - but a database machine alone. This means the individual components - the compute nodes, the storage servers, the disks, the flashdisk cards and more - are tuned to achieve that overriding objective. Any incremental tuning in any specific component has to be within the framework of the entire frame; otherwise it may fail to produce the desired result, or worse, produce undesirable result.

For instance the disks where the database resides are attached to the storage cell servers; not the database compute nodes. The cell servers, or Cells run Oracle Enterprise Linux, which is very similar to Red Hat Linux. Under this model of administration, the system admins are responsible for managing the operating system. A system admin looks at the host and determines that it is under tuned since the filesystem cache is very low. In a normal Linux system, that would have been a correct observation; but in Exadata, the database is in ASM and a filesystem cache is less important. On the other hand, the Cells need the memory to place the Storage Indexes on the disk contents. Placing a large filesystem cache not only produce nothing to help the filesystem; but actually hurt the performance for the paging of Storage Indexes.

This is just one example of how the engineered systems are closely interrelated. Assuming they are separate and assigning multiple groups with different skillsets may not work effectively.

Database Machine Administrator

This is leads to the other approach - making a single group responsible for the entire frame from storage to the database. The single group would be able to understand the impact of the changes in one component to the overall effectiveness of the rack and will be in a better position to plan and manage. The single role that performs the management of Exadata is known as Database Machine Administrator (DMA).

I can almost hear the questions firing off inside your brain. The most likely question probably is whether it is even possible to have a single skillset that encompasses storage, system, database and network.

Yes, it definitely is. Remember, the advantages of an engineered system do not stop at being a carefully coordinated individual components. Another advantage is the lack of controls in those components. There are less knobs to turn on each component in an Exadata system. Take for instance the Operating System. There are two types of servers - the compute nodes and the cells. In the cells, the activity performed by a system admin is severely limited - almost to the point of being none. On the compute nodes, the activities are limited as well. The only allowable activities are - setting up users, setting up email relays, possibly setting up an NFS mount and handful of more. This can easily be done by a non-expert. One does not have to a System Admin to manage the servers.

Consider storage, the other important component. Traditionally storage administrators perform critical functions such as adding disks, carving out LUNs, managing replication for DR and so on. These functions are irrelevant in Exadata. For instance, the disks are preallocated in Exadata, the LUNs are created at installation time, there is no replication since the DR is by Data Guard which at the Oracle database level. One need not be a storage expert to the perform the tasks in Exadata. Additionally the Storage Admins are experts in the specific brand of storage, e.g. EMC VMax or IBM XiV. In Exadata, the storage is different from all the other brands your storage admins may be managing. They have to learn about the Exadata storage anyway; so why not have someone else, specifically the DMA learn?

Consider Network. In Exadata the network components are very limited since it is only for the components inside the rack. This reduces the flexibility of the configuration compared to a regular general purpose network configuration. the special kind of hardware used in Exadata - Infiniband - requires some special skills which the network ops folks may have to learn anyway. So, why not the DMAs instead of them? Besides, Oracle already provides a lot of tools to manage this layer.

That leaves the most visible component - the database which is, after all, the heart and soul of Exadata. This layer is amenable to a considerable degree of tuning and the depth of skills in this layer is vital to managing Exadata effectively. Transferring the skills needed here to a non-DBA group or individual is difficult, if not impossible. This makes the DBA group the most natural choice for evolving into the DMA role after absorbing the relevant other skills. The other skills are not necessarily at par with the administrator of the respective components. For instance the DMA does not need to be a full scale Linux system admin; but just needs to know a few relevant concepts, commands and tools to perform the job well. Network management is Exadata is a fraction of the skills expected from a network admin. The storage management in cell servers are new to any group; so the DMA will find that as easy as any other group, if not easier.

By understanding the available knobs on all the constituent components of Exadata, the DMA can be better prepared to be an effective administrator of the Exadata system; not by divvying up the activities to individual groups which are generally autonomous. The advantages are particularly seen when troubleshooting or patching Exadata. Hence, I submit here for your consideration - a new role called DMA (Database Machine Administrator) for the management of Exadata. The role should have the following skillsets:

60% Database Administration
20% Cell Administration
15% Linux Administration
5% Miscellaneous (Infiniband, network, etc.)

I have written an article series on Oracle Technology Network - Linux for Oracle DBAs. This 5-part article series has all the commands an concepts the Oracle DBA should understand about Linux. I have also written a 4 part article series - Commanding Exadata - for DBAs to learn the 20% cell administration. With these two , you will have everything you need to be a DMA. Scroll down to the bottom of this page and click on "Collection of Some of My Very Popular Web Articles" to locate all these articles and more.

Summary

In this blog entry, I argued for creating a single role to manage the Exadata system instead of multiple groups managing individual parts. Here are the reasons in a nutshell:

Exadata is an engineered system where all the components play collaboratively instead of as islands. Managing them separately may be ineffective and detrimental.
The support organizations of components such as Systems, storage, DBA, etc. in an organizations are designed with a generic purpose in mind. Exadata is not generic. Its management needs unprecedented close coordination among various groups which may be new to the organization and perhaps difficult to implement.
The needed skillsets are mostly database centric; other components have very little to manage.
These other skills are easy to add to the DBA skills making the natural transition to the DMA role.

Best of luck in becoming a DMA and implementing Exadata.

Feedback on Tanel Poder's AOTS #2 Class

2011-05-15T14:29:00.000-04:00

In my earlier post I me wrote about Tanel Poder's Virtual Class #2. Tanel is a world famous expert in Oracle internals. I was fortunate to be able to attend the class on all days except Friday. It was solid 4 hours of learning every day. Let me summarize some of the key points I appreciated as an attendee. I hope it will be useful for you making a decision to attend one later.

(1) The beginning of the class explained the concepts like latching and locking pretty well

(2) Next in the line was understanding "why" they occur; not "how much"

(3) Reinforced the fact that latches smell like, look like, behave like locks; and are kind of locks

(4) Differentiated between soft and hard parses and explained the role of cursor_sharing parameter

(5) Differentiated between library cache pins, locks and latches

But if there is only one benefit I can say is priceless, that would be the holistic tuning, using the key concepts to understand the behavior all the way from the user response time to tracing the unix process to understand where the bottlenecks are. Tanel explained all that with a live example to cement that knowledge. All in all, an excellent class by an excellent instructor.

Tanel Poder's Virtual Class #2

2011-05-06T13:14:00.000-04:00

On a fine day in some month in some year, a little scene is playing out in Acme Bank's IT department.

Developer (with the best I-told-you-these-DBAs-are morons face) harrumphs, "The database is slow again!!! Does anybody even care?"

Junior DBA enthusiastically responds with a slightly belligerent tone, "Of course we do. I opened a ticket. The ticket# is ...."

Mid-level DBA announces with an officious tone, "The session is waiting for latches" as if explains everything.

Mid-level DBA aspiring to be the top dog chimes in "the session has waited 123.904567 seconds in cache buffer chain latch, 24.096534 seconds in share pool latch, 456.742340 seconds in db file scattered read ...." and rants the list of wait events measured precisely by taking a trace and/or looking at an esoteric mix of v$ and x$ views to drive even Graham Wood mad.

Everyone looks at the Senior DBA, who pronounces with the gravest voice, "my intuition says that this is caused by the application change that went in yesterday"

The developers gasped but before their string of protests flowed in, the user asks innocently, "OK. How do we solve it?"

Silence!

Exaggeration and over-dramatization aside, I think you will appreciate the simple truth in this scenario, which is not really that far from reality in many cases. At the end of the day you have to"solve" the problem. Measuring the wait events to the precision of micro seconds helps; but does not identify the root cause. You must know what causes the latch. No; you must understand what causes the cache buffer chain latch and then go on a hunt to stamp out the root cause. The tenets of solving a problem are understanding why it happens in the first place, identifying all possible causes, eliminating the irrelevant causes by evidence and finally zeroing on a solution based on findings. Intuition helps; but is not a replacement, not by a long shot, of the understanding the inner workings.

So, Acme hires an "expert", who did all that; but in the end, solved the problem. the junior DBA closed the ticket. Everyone was happy.

Do you want to be that expert? Do you want to poke your head under the hood of the Oracle engine? If so, look no further. Tanel Poder has been hacking the Oracle database since the, oh, I don't know, a long time. He put together all his knowledge into a set of instructional modules and he is offering all that as a virtual class. It's runs May 9th to 12th, 11 AM to 3 PM (US Eastern Time) every day. Interested? Register here http://bit.ly/ioabPz

Oh, did I mention that this is a web-based class? That means you don't have to take even a step more for this - you can be on your bed in pajamas or in your cube at work. You get tons of material for learning, hacking and playing with innards of Oracle, along with Tanel's world famous TPT scripts - an alchemist's box for Oracle-geeks.

I am excited to attend; and I highly recommend anyone seriously wishing to be that expert.

Nulls in Ordering

2011-04-24T14:52:00.000-04:00

You want to find out the tables with the highest number of rows in a database. Pretty simple, right? You whip up the following query:

select owner, table_name, num_rows
from dba_tables
order by num_rows;

And, here is the output:

OWNER         TABLE_NAME                       NUM_ROWS
------------- ------------------------------ ----------
CRM_ETL       GTT_RES_DLY
CRM_ETL       GTT_RES_PRDCT_CT
CRM_ETL       GTT_RES_PRDCT_RATE_CT
CRM_ETL       GTT_RRSD_DRVR
CRM_ETL       GTT_SUS_RES
SYS           L$1
SYS           L$2
SYS           WRI$_ADV_OBJSPACE_TREND_DATA
SYS           WRI$_ADV_OBJSPACE_CHROW_DATA
SQLTXPLAIN    SQLG$_TAB_SUBPART_COLUMNS
SQLTXPLAIN    SQLG$_DBA_SUBPART_HISTOGRAMS
SQLTXPLAIN    SQLG$_WARNING

... output truncated ...

Whoa! The NUM_ROWS columns comes up with blanks. Actually they are nulls. Why are they coming up first? This is due to the fact that these tables have not been analyzed. CRM_ETL seems like an ETL user. The tables with GTT_ in their names seem to be global temporary table, hence there are no statistics. The others belong to SYS and SQLTXPLAIN, which are Oracle default users and probably never analyzed. Nulls are not comparable to actual literals; so they are neither less or greater than the others. By default the nulls come up first when asking for a ordered list.

You need to find the tables with the highest number of rows fast. If you scroll down, you will see these rows; but it will take some time and it makes you impatient.You can add a new predicate something like: where num_rows is not null; but it's not really elegant. It will do the null processing. And what if you want the table names with null num_rows as well? This construct will eliminate that possibility. So, you need a different approach.

Nulls Last

If you want to fetch the nulls but push them tot he end of the list rather than first, you should add a new clause to the order by - NULLS LAST, as shown below.

select owner, table_name, num_rows
from dba_tables
order by 3 desc nulls last

Here is the output:

OWNER            TABLE_NAME        NUM_ROWS
---------------- --------------- ----------
CRM_ETL          F_SALES_SUMM_01 1664092226
CRM_ETL          F_SALES_SUMM_02  948708587
CRM_ETL          F_SALES_SUMM_03  167616243

... output truncated ...

This solves the problem. The nulls will be shown; but after the last of the rows with non-null num_rows value.

Can I Fit a 80MB Database Completely in a 80MB Buffer Cache?

2011-04-16T22:28:00.001-04:00

This is in the Series "100 Things You Probably Didn't Know About Oracle". If you haven't already, I urge you to read the other parts -

Part1 (Commit does not force writing of buffers into the disk),
Part2 (How Oracle Locking Works),
Part3 (More on Interested Transaction Lists).

During the recently concluded Collaborate 11 (http://collaborate11.ioug.org) I was asked a question which led me to the idea for this entry - the fourth in the series. If the database size is 100GB and you have a 100GB buffer cache, all the blocks will be in the memory and there will never be an I/O to the datafiles other than the regular DBWR lazy writes, right?

This is a very important question and you must consider the implications carefully. Many folks assume that by getting a large buffer cache eliminates or reduces the buffer related I/O - a very wrong assumption.A large buffer cache helps; but the relationship between buffer and block is not one to one. A block may have more than one buffer in the buffer cache. How so? Let's see how that happens. We will examine this in a non-RAC database to keep it simple.

Setup

First, let's create a table and insert some rows into it.

SQL> create table bhtest (col1 number, col2 char(2000));

Table created.

SQL> insert into bhtest values (&n,'&c');
Enter value for n: 1
Enter value for c: x
old   1: insert into bhtest values (&n,'&c')
new   1: insert into bhtest values (1,'x')

1 row created.

SQL> /
Enter value for n: 2
Enter value for c: x
old   1: insert into bhtest values (&n,'&c')
new   1: insert into bhtest values (2,'x')

1 row created.

... insert 6 rows ...
commit;

Note how I used char(2000) instead of varchar2(2000). The reason is simple. The char datatype takes up entire string of values, all 2000 of them regardless of the actual value placed there. So, even though I put 'x' there, the entire row will be quite a long one.

After the insertion, check which blocks these rows went into. We can do that by calling dbms_rowid.rowid_block_number() function.

select col1, dbms_rowid.rowid_block_number(rowid)
from bhtest;

      COL1 DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
---------- ------------------------------------
         4                                 4419
         5                                 4419
         1                                 4423
         2                                 4423
         3                                 4423

5 rows selected.

From the output we can see that rows 1 through 3 went to block 4423 and 4 and 5 went to block 4419. We also need the object ID of the object

SQL> select object_id from dba_objects
  2  where object_name = 'BHTEST'

  3  /
 OBJECT_ID
----------
     99360

This completes the setup. In summary, we know that these rows are in blocks 4419 and 4423 and the object ID is 99360.

Experiment

If possible, clear out the cache by shutting down and restarting the database. This brings up an empty cache. It's not absolutely necessary though. Now select from the table:

SQL> select * from bhtest;

This will bring up all the blocks of the table into the buffer cache, To check for the same, you can check the view V$BH (the buffer headers). The column OBJD is the object_id. (Actually it's the DATA_OBJECT_ID. In this case both are the same; but may not be in all cases). Here are the columns of interest to us:

FILE# - the file_id
BLOCK# - the block number
CLASS# - the type of the block, e.g. data block, segment header, etc. Shown as a code
STATUS - the status of the buffer, Exclusive Current, Current, etc.
LOCK_ELEMENT_ADDR - if there is a buffer lock on the buffer, then the address of the lock

To make it simpler to understand, we will use a decode() on the class# field to show the type of the block. With that, here is our query:

select file#, block#,
    decode(class#,1,'data block',2,'sort block',3,'save undo block', 4,
'segment header',5,'save undo header',6,'free list',7,'extent map',
8,'1st level bmb',9,'2nd level bmb',10,'3rd level bmb', 11,'bitmap block',
12,'bitmap index block',13,'file header block',14,'unused',
15,'system undo header',16,'system undo block', 17,'undo header',
18,'undo block') class_type, status, lock_element_addr
from v$bh
where objd = 99360
order by 1,2,3
/

Save this query because we will be using it a lot in this experiment. Here is the output.

     FILE#     BLOCK# CLASS_TYPE         STATUS     LOCK_ELE
---------- ---------- ------------------ ---------- --------
         7       4418 segment header     cr         00
         7       4418 segment header     xcur       00
         7       4419 data block         xcur       00
         7       4420 data block         xcur       00
         7       4421 data block         xcur       00
         7       4422 data block         xcur       00
         7       4423 data block         xcur       00

7 rows selected.

There are 7 buffers. In this example we have not restarted the cache. So there are two buffers for the segment header. There is one buffer for each data block - from 4419 to 4423. The status is "xcur", which stands for Exclusive Current. I will explain that in detail in a later blog. But in summary it means that the buffer was acquired (or filled by a block) with the intention of being modified. If the intention is merely to select, then the status would have shown CR (Consistent Read). In this case since the rows were inserted modifying the buffer, the blocks were gotten in xcur mode. From a different session update a single row. For easier identification I have used Sess2> as the prompt:

Sess> update bhtest set col2 = 'Y' where col1 = 1;

1 row updated.

From the original session, check the buffers:

     FILE#     BLOCK# CLASS_TYPE         STATUS     LOCK_ELE
---------- ---------- ------------------ ---------- --------
         7       4418 segment header     cr         00
         7       4418 segment header     xcur       00
         7       4419 data block         xcur       00
         7       4420 data block         xcur       00
         7       4421 data block         xcur       00
         7       4422 data block         xcur       00
         7       4423 data block         cr         00
         7       4423 data block         xcur       00

8 rows selected.

There are 8 buffers now, up one from the previous seven. Note there are two buffers for block ID 4423. One CR and one xcur. Why two?

It's because when the update statement was issued, it would have modified the block. Instead of modifying the existing buffer, Oracle creates a "copy" of the buffer and modifies that. This copy is now [Note there was a typo earlier "not", it should have been "now". Corrected. Thanks to Martin Bex] XCUR status because it was acquired for the purpose of being modified. The previous buffer of this block, which used to be xcur, is converted to "CR". There can't be more than one XCUR buffer for a specific block, that's why it is exclusive. If someone wants to find out the most recently updated buffer, it will just have to look for the copy with the XCUR status. All others are marked CR.

Let's continue with this experiment. From a third session, update a different row in the same block.

Sess3> update bhtest set col2 = 'Y' where col1 = 2;

1 row updated.

From the original session, find out the buffers.

     FILE#     BLOCK# CLASS_TYPE         STATUS     LOCK_ELE
---------- ---------- ------------------ ---------- --------
         7       4418 segment header     xcur       00
         7       4418 segment header     cr         00
         7       4419 data block         xcur       00
         7       4420 data block         xcur       00
         7       4421 data block         xcur       00
         7       4422 data block         xcur       00
         7       4423 data block         cr         00
         7       4423 data block         xcur       00
         7       4423 data block         cr         00
         7       4423 data block         cr         00

There are 4 buffers for block 4423 now - up from 2. What happened? Since the buffer was required to be modified once more, Oracle created yet another "copy", marked it "xcur" and relegated the older one to "cr". What about the extra CR copy? That was done because Oracle had to perform something called CR processing to create a CR copy from another CR copy or an XCUR copy.

You can notice how the number of buffers proliferate. Let's change the experiment a little bit. From a 4th session, select from the table, instead of updating a row:

Sess4>  select * from bhtest ;

From the original session, check for the buffers.

     FILE#     BLOCK# CLASS_TYPE         STATUS     LOCK_ELE
---------- ---------- ------------------ ---------- --------
         7       4418 segment header     xcur       00
         7       4418 segment header     cr         00
         7       4419 data block         xcur       00
         7       4420 data block         xcur       00
         7       4421 data block         xcur       00
         7       4422 data block         xcur       00
         7       4423 data block         cr         00
         7       4423 data block         cr         00
         7       4423 data block         cr         00
         7       4423 data block         cr         00
         7       4423 data block         cr         00
         7       4423 data block         xcur       00

12 rows selected.

Whoa! there are 12 buffers now. Block 4423 now has 6 buffers - up from 4 earlier. This was merely a select statement, which, by definition does not change data. Why did Oracle create a buffer for that?

Again, the answer is CR processing. The CR processing creates copies of the buffer and rolls them back or forward to create the CR copy as of the correct SCN number. This created 2 additional CR copies. From one block, now you have 6 buffers and some buffers were created as a result of select statement. This should answer the question whether the buffer cache of size of the database will be able to hold all the buffers.

Free Buffers

While being on the subject, let's ponder over another question - what happens when you flush the buffer cache? Let's see.

SQL> alter system flush buffer_cache;

System altered.

Checking the buffers using the script shown earlier:

     FILE#     BLOCK# CLASS_TYPE         STATUS     LOCK_ELE
---------- ---------- ------------------ ---------- --------
         7       4418 segment header     free       00
         7       4418 segment header     free       00
         7       4419 data block         free       00
         7       4420 data block         free       00
         7       4421 data block         free       00
         7       4422 data block         free       00
         7       4423 data block         free       00
         7       4423 data block         free       00
         7       4423 data block         free       00
         7       4423 data block         free       00
         7       4423 data block         free       00
         7       4423 data block         free       00

The buffers are still there and marked as belonging to the object. However the status is "free", i.e. the buffers can be reused if some session wants them for some other block. If a session wants a free buffer and can't find one, it waits with the wait event "free buffer wait". At that point, Oracle makes room in the buffer cache for the blocks requested by the session by forcing the buffers out of the buffer cache. The CR copies are discarded (since they were not updated) and the XCUR copies were written to the disk (if not written already).

Who does the writing? It's the process known as DBWR - Database Buffer Writer, which is generally named DBW0, DBW1, etc. The DBWR is a very lazy process. It sleeps most of the time, unless it is woken up by someone. In this case the session (actually the server process) kicks DBWn to write the dirty buffers to the disk and change the status to non-dirty. This is why sometimes SELECT statement may generate writing to data files. Until the buffers are freed up, the session waits patiently and displays to all that it's waiting on free buffer waits. You can check it by selecting the EVENT column from V$SESSION.

Impact on Segment Header

So far we talked about the data block. When does the segment header see some action? Segment header does not contain any data; so it does not need to be updated every time an update is made. But when the segment itself is modified, segment header is updated. Let's see an example with adding a column:

SQL> alter table bhtest add (col3 number);

Table altered.

Check the buffers:

     FILE#     BLOCK# CLASS_TYPE         STATUS     LOCK_ELE
---------- ---------- ------------------ ---------- --------
         7       4418 segment header     cr         00
         7       4418 segment header     cr         00
         7       4418 segment header     cr         00
         7       4418 segment header     cr         00
         7       4418 segment header     free       00
         7       4418 segment header     free       00
         7       4419 data block         free       00
         7       4420 data block         free       00
         7       4421 data block         free       00
         7       4422 data block         free       00
         7       4423 data block         free       00
         7       4423 data block         free       00
         7       4423 data block         free       00
         7       4423 data block         free       00
         7       4423 data block         free       00
         7       4423 data block         free       00

16 rows selected.

There are 6 copies of the segment header. Table alteration changed the segment header block - block# 4418. Just like any other block, the buffer was copied over and modified, creating multiple copies of the block. Whe you issue another DDL operation - truncate - the result is the same:

SQL> truncate table bhtest; 

     FILE#     BLOCK# CLASS_TYPE         STATUS     LOCK_ELE
---------- ---------- ------------------ ---------- --------
         7       4416 1st level bmb      free       00
         7       4417 2nd level bmb      free       00
         7       4418 segment header     cr         00
         7       4418 segment header     cr         00
         7       4418 segment header     free       00
         7       4418 segment header     free       00
         7       4418 segment header     cr         00
         7       4418 segment header     cr         00
         7       4418 segment header     cr         00
         7       4418 segment header     free       00
         7       4419 data block         free       00
         7       4420 data block         free       00
         7       4421 data block         free       00
         7       4422 data block         free       00
         7       4423 data block         free       00
         7       4423 data block         free       00
         7       4423 data block         free       00
         7       4423 data block         free       00
         7       4423 data block         free       00
         7       4423 data block         free       00

20 rows selected.

There are now additional segment header buffers, since truncate is a DDL command and segment header is modified. Even though there was no block of the table in the buffer cache (remember, we flushed it), the segment header still needs to be updated. Truncate also marks all blocks as free, and resets the high water mark and updates the bitmap block. The bitmap block (BMB) is used in tablespaces with automatic segment space management (ASSM) to display whether a block is free or not, which is similar to the functionality of freelists. The truncate caused the BMBs (there are two - 1st level and 2nd level) to be modified as well and they also come into the buffer cache.

Takeaways

From the above discussion you saw how a table with just two blocks populated fills up the buffer cache with 20 buffers. Imagine a normal database with, say 10000 filled blocks (8KX10,000 = 80M). It might easily fill 200,000 buffers. With a 8K block size that amounts to 8 K X 200 K = 1600M, or about 1.6 GB of buffer cache. The amount of buffers taken up depends on several factors:

(1) less buffers consumed in selects
(2) less buffers consumed if the commits occur more frequently, since the demand for CR processing is less
(3) more buffers are consumed if the activity updates buffers
(4) more the logical I/O, the more the need for buffers
(5) DDL activities increase the demand for the buffers, even if the blocks are empty

Food for Thought

Now that you know how the buffers are consumed, what do you think the situation is in a RAC environment where buffers are transferred from one instance to the other - better, worse, or about the same? The answer will come in a future blog. Stay tuned.

Fourth Day at Collaborate 11

2011-04-16T15:34:00.000-04:00

Thank you to those who attended my fourth and last session Designing Applications for RAC at Collaborate 11. Considering it was at 8:30 AM on Thursday, right after the big party night, you must all be really committed to your work. Much appreciated.

You can get the slides here.

I look forward to seeing you all at Collab next year.

Second Day at Collaborate 11

2011-04-16T15:29:00.000-04:00

Many thanks to those attended my session RAC for Beginners despite the 8 AM timeslot. You must be really motivated. I hope you found the session useful and leaned something you can start using.

You can download the slides here.

Thanks to those who came to my second session of the day - Secure Your Database in a Single Day. Hopefully that met your expectations. The slides are found here.

Day One of Collaborate 11 - My Session

2011-04-11T22:26:00.000-04:00

Thank you everyone who came to my session which got off with a rocky start due to a recording issue in the facilities. I was also the manager of the Manageability Bootcamp which ran the whole day. My sincere thanks to the speakers who spoke on the track. We had some of the best speakers and best topics.

If you want to get a copy of my session - Wait Events in RAC - you can download it here. Three more sessions and two expert panels to go.

My next sessions

(1) RAC Customer Panel - 4/12 9:15 - 10:15
(2) HA Bootcamp Panel - 4/12 11:45 - 12:15
(3) RAC for Beginners - 4/13 8:00 - 9:00
(4) Secure your Database in a Single Day - 4/13 2:15-3:15
(5) Designing Applications for RAC - 4/14 8:30 - 9:30

I hope to see all there.

Tanel Poder's Online Seminars

2011-03-27T19:59:00.000-04:00

What are good ways to learn about the inner workings of Oracle to troubleshoot performance and availability issues?

Fortunately there is only one good answer - just one good source - Tanel Poder's virtual seminars at http://tech.e2sn.com/oracle-training-seminars

Why? Let's examine the typical objectives and the various means to accomplish them. I'm sure you have heard this complaint before - "the database is slow". If I had a dime every time I heard it, well ... you know the rest! Most DBAs by now know the next best thing they should do - check the wait interface - V$SESSION. That's a very good first step.

From the view they figured out the session is waiting on cache buffer chain latch, and has been waiting 1234.56 seconds, measured to the precise microseconds and counting. They also got all the other data such as session Id, the statement and so and so forth. But then what? It's like a radio host reporting the stick market - x number of stocks are up, y are down, Dow is up by n, Nasdaq is down by m, and so on. All are factually correct; but none helps you in answering your question - why your specific portfolio of stocks is down and what other stocks you should consider.

Of course, you have to pay a professional to get that information. In your database issue at the moment, that's what you have to do as well - you have to be a professional to decipher the further information. And that's the vital second step. After you identified what is the ailment, you have to understand how to eliminate that. Most seasoned professionals stop at the first step when the second step is the most important. And that's where you need Tanel's highly acclaimed Advanced Oracle Performance Troubleshooting seminar. It's not based on slides; but showing the demos right in front of you, using code you can understand and reuse.

In this specific example, in the first part of his series, under the section "Latches" he shows you how to get the specifics of the latch, for instance the latch children. There are some traditional fixes, of course; but the big question is what if (and, that is a big IF) these well understood fixes do not work? What do you do next. Pray?

No; turn to Tanel's class. He explains how to get information from various sources inside the database. In this case the trick is to find out who has the latch and who is waiting for it, and what specific latch is so popular. This picture helps (from Tanel's class). Reproduced with permission:

As they say, a picture is worth thousand words. Now that you know how to find that popular latch, you are well on your way to troubleshooting. Hopefully this is all you have to do.

But what if it's not. The problem still eludes a solution? Have no fear. In his hang analysis section, Tanel talks about taking system dumps and explains how to analyze them - again with a few slides; and showing the actual trace file and interpreting the file right in front of you.

Talk about tough luck; suppose the problem is still not solved. Tanel takes a process stack dump. Yes, it is not for the faint of the heart; but with the right training anyone can do it. He shows you how to take the stack trace and analyzes one right in front of you. Here is an example of how to interpret the stack trace (reproduced with permission)

And he explains each section with how to interpret the different data to come to the solution.

At this point no problem is big enough to stand this type of scrutiny. If the problem persists, well, it's most likely a bug then. Oh, yes, Tanel will state the bugs that could affect performance issues and point to the MetaLink notes as well.

Interested? I guess more like excited. I certainly am. Tanels' first class is running April 11-15 http://tech.e2sn.com/oracle-training-seminars. It's a virtual class; so you don't have to step away from wherever you are, in whatever state you are in. As long you have the internet connection and an agile mind to absorb the superb information presented, you are in for a massive dose of superior learning.

What about the scripts Tanel uses in the class? Are they proprietary? Do you have to buy them to use them? Do you need to write them down so fast that you capture all the details? Not at all. As a part of the course, Tanel will expose his entire script library to you.

Intersted? Oops; I lied! Actually the scripts and tools are all free, even if you don't attend his class. They are at http://tech.e2sn.com/oracle-scripts-and-tools. Download to your heart's content.

So, why is Tanel giving away his hard work for free? Well, buying the best tool from Home Depot will not make me a good carpenter; I must learn how to use them to build a deck. A script is only 20% of the solution; the rest 80% is knowledge. And the 80% is what Tanel's virtual seminars are all about. Hope you make the right choice. I know I have.

Speaking at a Virtual Conference #VirtaThon

2011-03-27T17:43:00.000-04:00

I was putting together abstracts for Oracle Open World #oow11 this year and remembered something someone had asked me earlier - to present at a conference like this, how does one overcome the fear of delivering a session? In fact, it is a question asked of me several times.

This is not a trivial issue; it's a real problem. There are many folks who are otherwise excellent sources of knowledge, in fact fountains of practical ideas; but when asked to speak in front of a live audience, they would rather kiss a frog than step in from of the podium. The mortal fear of public speaking is one of the many challenges to get good speakers for conferences.

The second challenge is cost. Conferences are conducted at some physical place. Unless you live in that city, or within commutable distance, you have to travel there. Add to the plane fare, cost of hotel, food, rental car, and all that extra expenses the emotional turmoil of being away from the family, especially those folks with small children who would miss one of their parents a is not something you can just brush aside. Even if you are not a parent, you may be a caregiver to a loved one and your absence will be hard on the cared one.

Finally, the change is not something most people like. You may be comfortable with your present surroundings, among familiar people you work with every day. Traveling to a new city and spending time with strangers may be exciting or daunting based on how you look at it. Perhaps you work from home everyday in your PJs. Getting the wrinkles off the pants to go to the conferences may be a lot. At least to some people.

The answer to all this may be a trend I see developing now - web based conferences. You can attend them in your PJs and speak at them in your PJs as well; no iron needed. One such conference is #VirtaThon (http://bit.ly/hc2Vjh) where several speakers, most of them widely known in the Oracle user community are speaking. Being a virtual conference, you don't have to travel anywhere to attend the sessions; you attend from the privacy and comfort of your own home or office. Remember, they are *not* webcasts; they are virtual conference sessions. So you actually participate in the sessions as you would do in a normal conference - ask questions, interact via chat and have follow ups after talks. If you are a speaker, there is nothing better - you don't have to travel to the venue. You need a computer with an internet connection. If you are uncomfortable speaking to a very live audience, it should be much easier speaking to virtual audience.No traveling, no TSA checks and no red eye flights.

Just to pique your interest, here is just a sampling of the speakers (in no particular order)

Dan Hotka
Bert Scalzo
Riyaj Shamsudeen
Syed Jaffer Hussain
Steven Fuerurestein
Jeremy Schneider
Guy Harrison
Brian Huff
Lewis Cunningham
Mike Ault

And many more. Oh, I am speaking as well.

Interested? Submit an abstract at http://bit.ly/hc2Vjh as soon as possible. The deadline is approaching fast.

Oh, yeas, a little detail. You will not be speaking for free; you will earn money for your efforts. So, what's stopping you?

Review: Oracle Application Express 4.0 Cookbook

2011-03-19T22:40:00.000-04:00

I decided to add a twist to my usual blogs - a book review, which I have not done before. I have been reading a book - Oracle Apex 4.0 Cookbook by Marcel van der Plas and Michel van Zoest and published by Packt, a UK based publisher. Michel is one of the first Apex Certified Experts in the world - a no small feat. It has been technically reviewed by a well known cast - Oracle ACE Director Dimitri Gielis, who also won Oracle Magazine's Apex Developer of the Year in '09; Maarten van Luijtelaar and Oracle ACE and frequent blogger Surachart Opun.

Why Apex

Back in the early 90's there was a product called SQL*Forms - for those who are old enough to remember that. Up to verion 3.0, the source code, known as .INP file, was a text file. It was a breeze to generate a form to show data and even do some rudimentary editing. Since the source file was in text format, we used to open the file in vi editor instead of the required Forms Designer and add other fields and records. It was very convenient not just for non-SQL savvy users; but for us as well, to examine and easily fix incorrect data. It certainly beat writing long SQL statements. It was, in a way, one of the original Rapid Application Development environments for Oracle database.

Forms 4.0 changed the convenience factor a little - the source file was no longer text and hence not editable by vi. We had to open the form in the Designer. However, the value proposition of the Forms product as a rapid application development tool remained. It was still possible to create the perfect quick edit tool or a quick application for the non-SQL savvy user in a jiffy. In fact the added "true" graphical interface was a competition for the more popular GUI tools of those times.

Over the course of time, the product ceased to exist by itself. With the advent of the web environment, inclusion of Java and the three tier execution model, Forms assumed a more sophisticated avatar. Rather than being a standalone product, it had to have a crutch in the form of Application Server. While that undoubtedly added reliability, scalability and brought it to the realm of real web development, it took away the convenience of the rapid application development by the non-developers such as DBAs.

That was so until about fifteen years ago, when Oracle introduced WebDB, a database development tool. Later it morphed into a product called HTMLDB, which blossomed into Oracle Application Express. It not only brought web development to Oracle database, it afforded quick application development, and even development of applications by users - end-users; not DBAs or developers. This shifted the whole paradigm in favor of the users, enabling them to roll out apps without mush dependence on their more "skilled" colleagues.

The Book

Now back to this topic. Application Express (fondly called Apex) is pretty well documented and has been around for some time. It has matured as a product and seen its share of coverage in the IT circuits. There is no dearth of books, articles, blogs, code samples, etc. about Apex. So, with all that in place, how long will it take you to create your very first application (assuming you know reasonably about Oracle)?

You might be surprised to find that it will take a while. The manuals and normal books are usually geared towards imparting knowledge about a specific topic - Apex in this case. Quick start is not usually the objective. It's somewhat like the difference between all_rows and first_rows hints.

In addition, most books are designed to build up knowledge through a series of logical steps - starting with concepts. It's very important to understand the concepts to build a strong foundation. However, there will be days when you want to know just how to do a certain stuff. It's like checking a dictionary for the meaning of a word, rather than reading a book about improving vocabulary, or learning about its etymology. It could happen to anyone - beginner or a seasoned professional. I know; I thumb through my own book - RMAN Recipes whenever I am stuck with a task. A cook book format work well. This book is exactly that - a cookbook of recipes which you can quickly refer to instead of spending the time in looking where exactly that is covered.

Detailed Review

Apex is a GUI environment; so a book has to have a lot of screenshots to explain things properly. This book does not crimp on that aspect.
It has a very useful four section approach for each recipe.

It starts with how you can start with a simple form from the EMP table. It cleverly points out that
It has recipes to manage several off the shelf utilities, e.g. Google Maps.In most applications today, maps are a choice du rigueur, not just that have a geo-spatial element in them.
It handles creating apps in different languages, which is very useful in many cases.
If you are already an expert in Apex, you understand how important it is to develop and distribute plug-ins. This book provides the easiest explanation of plug-ins and how to develop them.
Using APIs and Webservices is explained lucidly.
Finally, one of the thorniest topics - managing Apex environments - has been adequately covered in two chapters.

All in all, this is a solid book to keep for beginners and advanced users alike. I am sure you will like reading this book. I am definitely keeping it as one of my carry along books (I have the e-Book)

http://link.packtpub.com/yEotp8

RAC One Changes in 11.2.0.2 Patchset

2011-03-16T18:30:00.000-04:00

Oracle patchsets used to be simple - they were bugfixes only; no additional functionalities were added. Oracle added stuff quietly in 10.2.0.4 patchset (the workload capture); but that was part of a new functionality anyway.

The 11.2.0.2 patchset changed all the rules. Several new functionalities were added to the patchsets. Several functionalities have been added to RACOne. Unfortunately they didn't make it to the manuals. Some of them I discovered only recently. If you have been using RACOne, or considering it, you will be delighted to know these.

(1) Bye Bye Patch - In 11.2.0.1 RACOne was supported; but you had to apply a patch - 9004119 . Well, it was confusing at the best. A favorite interview question on RACOne fo rmany way this patch. It's gone; 11.2.0.2 already has the patch.

(2) OMotion - remember the OMotion tool used for relocating the database instance? It was probably modeled after the functionality of VMotion in VMWare; hence the namesake. However, it was completely redundant. The clusterware has the functionality anyway; and RACOne has the clusterware; so I never understood the need for OMotion.

In 11.2.0.2, it's gone. To relocate database instance from one server to another, you simply issue srvctl relocate command.

$ srvctl relocate database -d DBNAME -n

(3) Creation - no need to create the RACOne using raconeinit script. DBCA can create it. Oracle Installer can install RACOne as well.

(4) Status - Earlier you had to use raconestatus command to know the status of the RACOne database. This is no longer needed. You can use srvctl status or crsctl status commands.

(5) Upgrades - upgrading RACOne? DB Ugrade Assistant is aware of RACOne as well.

(6) Conversion - Converting a database to RACOne? SRVCTL comes to rescue:

$ srvctl convert database -d ....

(7) Data Guard will work on RACOne as well.

New York Oracle User Group DBA SIG Webcast

2011-03-11T16:19:00.000-05:00

Thank you all those who attended my webcast "Deferred Statistics and SQL Plan Management for Performance in Oracle 11g" for the DBA SIG of New York Oracle User Group. The recording will be available on that website sometime later. In the meantime, here is the zipfile with the slides and the scripts I used for the demo, if you are interested. The script names are referenced in the slides.

This was the first time NYOUG used webcasts for the SIG meetings. I was the guinea pig for the experiment. I paid a little in the way, though. There were technical glitches for the first 10 minutes; and there some annoying background noises for the first 45 minutes. But considering this being the first webcast, I think NYOUG handled it pretty well. Thank you, Simay.

As always, please let me know how you liked it, or didn't.

Good Engineering

2011-03-11T11:09:00.000-05:00

It's heartbreaking to watch the folks killed, wounded and displaced by the earthquake in the Pacific Ocean. The fury of nature mostly focused on Japan. Surprisingly the damage was not as much as expected in Tokyo. The big city was rattled, nevertheless, but relatively unscathed compared to the lesser known towns and villages. the casualties would have been unthinkably large had the buildings buckled in Tokyo - one of the cities in the world with highest population densities.

How did Tokyo manage to evade the inevitable? Someone twitted - Millions saved in Japan by good engineering and government building code. How profound. Not to belittle the unfathomable suffering of the victims, I couldn't help but think about the parallel to my own profession.

Engineering is not about being superficially creative; it's about reliability and trustworthiness. What good had been to build the highest tower in the world if that toppled over killing thousands and destroying far more in property? The true appreciation for engineering comes if something does not happen when things don't go as expected. When building a database infrastructure, or managing one, the true effort of a DBA is manifested in things that do not occur. Corruptions do not happen, rather than recovery being a necessity, or, security breaches never occur as opposed to scrambling to contain the damage of a breach. When things don't happen, the DBA is likely doing his or her job most effectively. It's not flashy webpages, or nice reports; it's plain simple non-events that differentiates [to borrow from the oft-repeated and near-cliche] men from boys.

I have a simple mantra (well, actually one of several) - success is not an accident; it's planned. Carefully planned engineering artifacts saved the day. Carefully planned processes save the organization from the perils of life - be it tsunamis or attempted credit card thefts. The success of the projects I execute, I believe, depends on how well it was planned - how prepared I was for all contingencies. There are three very important things in any project - details, details and details. Sometimes people around me get a little impatient that I pay too much attention to planning and details before starting the action. Well, without detailed analysis, I don't see how you can succeed in a project. Dumb luck, may be; but definitely not because of effort.

One of the other overlooked factors in success is standardization. It goes for building a good layout or an architectural plan that influences future projects. For years, I have been developing and enforcing strict guidelines in my own organization, to address this eventuality - just in case. Time and again, it has proved invaluable by preventing small and large mishaps, just like the building codes did in Tokyo.

Because of what the engineers did, Tokyo was spared; not because of its good luck. Millions of people should thank the unsung heroes that made it their mission to pay attention to the detail and plan very carefully. And scores of CEOs, CIOs and shareholders should thank the unsung heroes in their own organizations who saved them from corporate perils by making sure nothing happens.

Planning ... details ... boring; but important. Not something you will see in headlines, sadly, though.

2011-02-03T16:47:00.000-05:00

A question came up on my blog entry http://arup.blogspot.com/2011/01/more-on-interested-transaction-lists.html. I think the question warrants a more comprehensive explanation instead of an answer of a few lines. So I decided to create another blog.

Here was the question:

Could you please explain on the scenario when multiple transactions try to update the same row as well. Will there be any ITL allocated? Yes, I am talking about the real locking scenario.

Paraphrased differently, the reader wants to know what would happen when this series of event happens:

update row 1 (locked by transaction 1, and occupying one ITL slot)
update row 2 (locked by transaction 2, occupying a different ITL slot)
Transaction 3 now wants to update either row 1 or row 2. It will hang of course. But will it trigger the creation of a new ITL slot?

I also decided to expand the questions to cover one more scenario. Transaction 4 wants to update row 1 and row 4 in the same statement. Row 4 is not locked; but row 1 is. So will transaction 4 be allowed to lock row 4, even though the statement itself will hang? Will it trigger the creation of another ITL?

Examination

Let's examine these question via a case study. To demonstrate, let me create a table with three rows:

SQL> create table itltest2 (col1 number, col2 number)
  2  /

Table created.

SQL> insert into itltest2 values (1,1);

1 row created.

SQL> c/1,1/1,2
  1* insert into itltest2 values (1,2)
SQL> /

1 row created.

SQL> c/1,2/2,2
  1* insert into itltest2 values (2,2)
SQL> /

1 row created.

SQL> commit;

Checking the rows:

SQL> select * from itltest2
  2  /

      COL1       COL2
---------- ----------
         1          1
         1          2
         2          2

javascript:void(0)

Now open three sessions and issue different statements

Session1> update itltest2 set col2 = col2 + 1 where col1 = 1;

2 rows updated.

It updated (and locked) 2 rows - row 1 and row 2.

If you check the transaction ID, you will see the transaction details:

SQL> select dbms_transaction.local_transaction_id from dual;

LOCAL_TRANSACTION_ID
--------------------------------------------------------------------------------
7.10.33260

From session2, try to lock row 2 and 3:

Session2> update itltest2 set col1 = col1 + 1 where col2 = 2;

This will hang. The reason is obvious. The transaction is trying to get a lock on rows 2 and 3. Since row 2 is already locked by transaction 1, it can't be locked. However, what about row 3? It should have been able to be locked. Was it locked? Let's make a simple check by updating only row 3 from another session, which was attempted to be locked by transaction 2.

Session3> update itltest2 set col2 = col2 + 1 where col1 = 2 and col2 = 2;

1 row updated.

Checking the transaction ID:

SQL> select dbms_transaction.local_transaction_id from dual;

LOCAL_TRANSACTION_ID
--------------------------------------------------------------------------------
10.4.33214

We know that there are three transactions and three lock requests. Or, are there? Let's check in V$TRANSACTION:

SQL> select XIDUSN, XIDSLOT, XIDSQN
  2  from v$transaction;

    XIDUSN    XIDSLOT     XIDSQN
---------- ---------- ----------
         7         10      33260
        10          4      33214

There are only two transactions that have placed locks. If you combine the XIDUSN, XIDSLOT and XIDSQN, separated by periods, you will get the transaction ID shown earlier. The transaction that is hanging has not placed a lock on the row it could have put a lock on. That is consistent with the concept of statements inside transactions - either all rows will be updated or none - not in piecemeal. If one of the rows can't be locked, none of the rows will be.

What about ITL slots. Let's see them by doing block dumps. First , we need to know the block number these rows are in:

SQL> select dbms_rowid.rowid_block_number(rowid), col1, col2
  2  from itltest2;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)       COL1       COL2
------------------------------------ ---------- ----------
                                4052          1          1
                                4052          1          2
                                4052          2          2

After performing a checkpoint, we will dump the block.

SQL> alter system dump datafile 7 block min 4052 block max 4052;

Looking in the tracefile and searching for "Itl", we see the following two lines:

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000a.004.000081be  0x00c004fe.1873.23  ----    1  fsc 0x0000.00000000
0x02   0x0007.00a.000081ec  0x00c00350.194c.18  ----    2  fsc 0x0000.00000000

There are just two ITL slots; not three. Remember the XID column is in hexadecimal. If you convert the XID columns in the v$transaction view:

SQL> select
  2     to_char(XIDUSN,'XXXXXX'),
  3     to_char(XIDSLOT,'XXXXXX'),
  4     to_char(XIDSQN,'XXXXXX')
  5  from v$transaction;

TO_CHAR TO_CHAR TO_CHAR
------- ------- -------
      7       A    81EC
      A       4    81BE

Note how the output matches the entry under the column marked "Xid" in the Itl output. you saw the same transaction IDs in the same Itl. There are just two ITL slots and each slot points to a transaction that has placed the lock. The transaction that has not placed the lock is not given an ITL slot; there is no no need for it.

Lock Change

Now suppose Transactios 1 and 3 ended by either commit or rollback. Transaction 2, which was hanging until now, will be free to put the locks. Let's see the ITL slots:

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0008.00f.0000a423  0x00c013ce.1e11.05  C---    0  scn 0x0000.0244bcb2
0x02   0x0006.005.0000a43f  0x00c008fe.1d22.12  ----    2  fsc 0x0000.00000000

If you examine the hexadecimal values of the XID values from V$TRANSACTION,

SQL> select
  2       to_char(XIDUSN,'XXXXXX'),
  3       to_char(XIDSLOT,'XXXXXX'),
  4       to_char(XIDSQN,'XXXXXX')
  5  from v$transaction;

TO_CHAR TO_CHAR TO_CHAR
------- ------- -------
      6       5    A43F

This matches the transaction Id we see in the "Xid" column of the ITL slot. The other ITL slot is now free from any other lock.

RAC Performance Tuning Seminar on Jan 25 and 26, 2011

2011-01-26T15:39:00.000-05:00

Thank you all those who attended my Live Virtual Class on RAC Performance Tuning on January 25th and 26th. You chose to invest your time with me, for which I am immensely grateful. Some of you have sent me email afterwards how well you liked it. Please keep sending them - they make any speaker's day. Even if you didn't like something, please let me know. Your feedback is very important for me. It is more so this time since this is the first time I delivered a live virtual class.

One of the major complaints seem to be most were not aware that it was 4 hours each day. I can understand your frustration; but I hope the communication will be better from the organizers next time. I was not the organizer; so I will not be able to tell what went wrong.

The scripts can be downloaded: from http://www.proligence.com/racperf.zip

Some questions came for TVD$XSTAT. It can be downloaded from http://antognini.ch/downloads/tvdxtat_40beta9.zip

Please return the evaluation forms back to OU; they need that. And, as I mentioned, please send me your fedback - good or bad - they are valuable.

What Makes a Great Presentation

2011-01-25T13:56:00.000-05:00

What makes a great presentation? Everyone successful presenter from Steve Jobs to Tony Robbins have been studied for patterns. Some of the pre-requisites seem to be:

(1) Prepare. Prepare for 100+ hours for an 1 hour presentation

(2) Have a backup. If something goes wrong, have an assistant fix things while you entertain the audience

(3) Have jokes ready, and crack them when you encounter a technical challenge

Well, I was thinking about it while delivering the Oracle Celebrity Seminar series today and saw a reference to Steve Jobs' presentation during iPhone 4 launch where he had a wifi challenge. How convenient for the speakers to say that. Who has the luxury of practicing a speech of 1 hour for 100 hours, or having an "assistant" work things out behind the scenes, or the prowess of a comedian delivering seemingly good jokes turned horrid?

Definitely not me.

I delivered my very first public seminar in October 2002 in Richmond, VA. In the last eight and half years I have presented about 150 such sessions, and 24 day long seminars. Over all that time and sessions I have been been largely applauded; but heckled, humiliated and horrified as well. Laptops not starting, internet not connecting, powerpoint crashing - I have seen it all; not to mention travel plans disrupted. But above all, I have learned, learned from every step of the way to be better as a speaker and more effective to the attendees. There is no secret; but some simple pointers. Here I am going share with you some of those lessons.

(1) Empathize with the Audience

What type of presenter are you - do you know your subject well? Or, you research something and present what you find. I have met some folks who are great in delivering a topic in a flawless manner - any topic, without any mastery. They are eloquent and polished "speakers"; not subject matter experts. If you are lucky enough to be one, good for you; but most folks, including yours truly, are not. Most of us are *not* great speakers. So, would you throw up your arms in frustration and retreat to the cozy corner?

Not at all. Remember, the audience in a technical setting is not there to listen to your eloquence; but to the substance. Of course, being entertaining helps; but it's not the core. The attendees like the talk if they can relate with you. You must know the audience members, their needs, their challenges and the thoughts in their mind. If you are one of them, they immediately connect with you and deem your talk it valuable. If you are detached, no matter how valuable your talk is, they are less likely to appreciate.

I start most of my talk with a simple sentence, that I am just like most folks in the audience. I understand their issues, because I face the same every day. I have been fortunate to be able to address an audience like that; perhaps it's not possible in every case. But you must understand the audience's needs and wants and step into their shoes. When they talk about issues, be immediately appreciative, never dismiss it as trivial, try to understand from their perspective and you will be able to see a whole new world. The moment the audience feels that you are in sync with them, they immediately feel comfortable and try to get in sync with you - a win win situation that is the key to a successful presentation.

(2) Be Passionate

Show some passion, a lots of it. You have worked hard on the session and prepared the material meticulously; but all is moot unless you show it at he stage. Believe in the material, each word of it, believe in the usefulness and need to the audience. But just believing in itself does not work; show it. Show the enthusiasm and vigor while describing. The passion you demonstrate is contagious; the audience gets fired up as well. An engaged audience is the best audience.

(3) Show Respect and Appreciation

You have been invited to speak (perhaps you have been reimbursed your expenses and even paid a hefty speaker fee) before this audience. The audience has paid good money to have you speak there. So, they are lucky to have you, right?

WRONG!

A speaker is nothing without an audience. The audience is not lucky, you are. You are fortunate to be taken into the role where the audience has invested in you. Never, forget that. Even if the audience paid nothing in monetary terms, they paid - their time, their valuable time. Never assume that they have come in their own free time. There is no such thing called free time; it's an oxymoron. One uses the time from something more important, which could be a nap, a walk in the beach, spending time with family. The fact that an attendee has forgone all that to listen to you speak is an honor for you. Always remember that and appreciate the audience for attending. Always remember that you are here to earn their respect; and you must respect their biggest investment - their time - in you. You must not disappoint them. Without them you don't exist.

(4) Be Spontaneous

What about practice? If you can afford it, you should definitely practice. But most of us probably don't have that luxury. Relax, that's not important. If you have considered and incorporated the first three advices, especially being passionate, you will feel that you now have an sufficient thrust to launch you into orbit. The rest becomes surprising easy. Practicing may make it perfect; but it also brings in staleness into the delivery. If you are showing demos, you should definitely practice that; but the actual content of your presentation should be left to the stage. I never practice the presentation; but of course, choose what works for you.

(5) Correct Posture and Delivery

Always, always, always maintain eye contact with the audience. Don't look over them, in front of them, and most definitely not at the screen. You are the speaker, not the screen.

Stand straight; but relaxed. If you slouch, place your thumbs in your pocket, the appearance seems callous and you will kill the passion angle.

Spread your ams wide - a universal sign of acceptance and inclusion. The audience feels more comfortable y feeling included and will be more receptive. Never fold your arms, especially while listening to a question from the audience.

While speaking "project" your voice. That is very different from a conversational tone. You should practice that in front of a mirror. Basically you "throw" your voice away. There should be distinct gaps between words. Imagine you are in an echo chamber and each word is followed by an echo forcing you to pause before speaking the next word.

When asked a question from the audience, always repeat the question. It's likely that the parts of the audience might not have heard it so the answer will seem completely out of context unless you repeat the question. It will also help explain to the questioner how you interpreted the question.

(6) Spare Details in Slides

Remember, slides are to bring the audience's attention to a point, not to describe the point itself. Never crowd the slides. A few bullet points work best, unless the slides are also the course material. A better alternative is putting the descriptions in the "Notes" section of the slides, which will print; but not shown on screen. When you use a crowded slide, attendees focus more on reading that rather than listening to you.

(7) Have a Backup

If you have a slide deck, back it up on a USB stick. I carry two USB sticks in addition to the deck in the laptop. If you can, send a copy to someone at the meeting site as a backup, just in case. I also burn it into a CD, just in case the USB ports are disabled. Some computers may have that.

I also back up the files into some free online storage - Dropbox, Google Docs and email them to myself. I may not know which sites may be blocked; so options are always better.

What tool did you use? Powerpoint, Open Office, or something else? It's better to ask than assume. If Powerpoint, which version - 2003, 2007 or something else? What happens if you have the slides in an imcompatible mode?

That's why I also carry a PDF version of the presentation (create free PDF by Primo PDF http://www.primopdf.com/index.aspx) , along with the Acrobat Reader in the same USB stick and the CD. In the worst case, I will be able to show it right from the removable media. I will not be able to use all the fancy features such as animation; but it's better than nothing at all. Make sure you export the fonts when you create the PDF. Not all the fonts may be available in the target computer.

What if you want to show some demo? Will everything work? Don't count on it. Murphy's Law kicks in at the worst possible time. Always run the demo prior to the session, capture the screens using the free CamStudio tool (http://camstudio.org/). It needs a flash player, which may not be present; so you should also take some screenshots using Windows Vista's Sniping Tool or even Alt-PrintScreen. Again, they may not wow the audience, but will save the day.

(8) Have the Right Tools

Are you presenting abroad? Learn about the power requirements of the country - is is 115 volts or 220? Is the power plug the right type? Do you need and have an adapter? Learn about power requirements here: http://en.wikipedia.org/wiki/Mains_power_around_the_world

What about the laser pointer? Do you have one? If you do, do you have an extra battery? Even if you use, always carry a stick pointer. Recently while delivering a session in London, I saw a quite high tech environment - a huge LCD panel instead of the screen and projector. The laser pointer was ineffective. Fortunately I had my collapsible stick pointer.

Finally, don't think of this as ordeal. Have fun. If you fail in making the same effect on the audience, relax; the world will not end. They will not think any less of you. People have other things to worry about; your supposedly fall from pedestal is least of them.

I hope you liked it and found it useful. Let me know other tips you may have for a great presentation.

Advanced Linux Commands Mastery Series on OTN

2011-01-21T12:08:00.000-05:00

Luis Daziano from Argentina asked me about the location of the series I wrote for Oracle Technology Network on Advanced Linux commands. Fortunately, OTN still has all of them, although it has become a little difficult to find. Here are the five articles, if you are interested.

http://www.oracle.com/technetwork/articles/linux/part1-091089.html

http://www.oracle.com/technetwork/articles/linux/part3-090589.html

http://www.oracle.com/technetwork/articles/linux/part2-085179.html

http://www.oracle.com/technetwork/articles/linux/part4-097181.html

http://www.oracle.com/technetwork/articles/linux/part5-096399.html

Enjoy.

Thank You for the Overwhelming Response to the 100 Series

2011-01-21T00:58:00.000-05:00

A few weeks ago, while delivering a session at New York Oracle Users Group, I had the idea of starting a blog series with a title 100 Things You Probably Didn’t Know About Oracle Database. From the myriads of emails I have received so far, I know it has been quite successful in helping many readers. I was pleasantly surprised to hear from even some seasoned DBAs how it helped them. I would like to take this moment to talk about a few things.

In the initial installment, I put a self declared goal of producing an installment a week. I quickly, after the first installment itself, realized it was lofty, way lofty goal. I have a day job as a Database Architect where I research options, prepare project plans, estimate budgets, get approvals for the projects, jump in DBA operations, develop strategies of all terms, get them approved, chase everyone from the Sys Admin to the mailman to make sure the train chugs along. I also write articles, give training seminars, present technical sessions, review manuscripts of books (and occasionally write one), critique published books sent to me by publishers, do some mentoring, and yada yada yada. And, you see, I am just an average person with a family that needs me as well. This little “hobby”, however satisfying, faces a lot of competition.

What is the alternative? I could write shorter pieces or simply drop the code examples, figures, etc. which take up most of the time. On second thought, most of you actually like the articles because of these very elements - code examples and figures; and I don’t want to drop them.

Therefore, as much as it hurts me, I have to renege on the commitment of one installment a week. I would rather produce some quality content than just a bunch.

Second, I don’t have a plan, a set schedule or a “roadmap” for this series. I choose the next content based on what I get in the feedback. If you would like to see a specific topic covered, please drop me an email, tweet or put in the comment section here. Please be as specific as possible.

Third, some of the comments has been about additional questions which were probably not covered very well in the blog entry. I consider them to be success, rather that a failure of the writing. You see, a “complete” writing is an oxymoron; it does not exist. If article, blog or book does not generate ten other questions, then it fails. The objective of my series is never to be 100% comprehensive; instead it aims to build a foundation and generate additional interest. Some of the questions are planned to be answered in the subsequent installments; rest are left in the wishlist for the future articles.

Finally, an author is nothing without readers. From the bottom of my heart, I thank you, dear reader, for reading this. Life makes demands, which is perhaps more true for technologists like yourself; your decision to devote a slice of your life to this series is a conscious investment you made. I am honored and grateful for that.

Arup Nanda
Danbury CT, USA
January 20th, 2011
Twitter: arupnanda

More on Interested Transaction Lists

2011-01-19T17:42:00.001-05:00

How Oracle Locking Works
When a Lock is NOT a Lock!

In the last installment of this series "100 Thing you Probably Didn't Know About Oracle" you learned how Oracle locks the rows of a table. Here is what you learned in a nutshell:

(1) When a transaction modifies a record, the pre-change image is stored in the undo segments, which is required for various things; the most important of which is to provide a read consistent version of the row when another session wants it.

(2) The transaction is assigned a transaction identifier that shows the undo segment number, slot# and record of the undo information.

(3) The transaction locks the rows (since it did not commit) by placing a special type of data in the block header known as Interested Transaction List (ITL) entry. The ITL entry shows the transaction ID and other information.

(4) When a new transaction wants to update the same rows (locked by the previous transaction) it checks the ITL entries in the block first, to check if there is a lock.

(5) Since the lock information of rows is stored in the block itself, and the ITL entries in the block refer to the locks on the rows in that block alone, there is no need to have a central lock manager to dispense and handle the release of the locks. This makes the locking process not only immensely scalable but feasible as well since there is no theoretical limit to the number of locks.

[Updated Jan 22, 2011] [Thank you, Randolph Geist (info@www.sqltools-plusplus.org) for pointing it out. I follow his blog http://oracle-randolf.blogspot.com/, which is a treasure trove of information.
(6) The information that a row is locked is stored along with the row in the form of a lock byte.
[End of Update Jan 22, 2011]

While the article might have answered some of the vexing questions you may have had or needed some clarity on the concepts you were somewhat familiar with, I sincerely hope it has piqued you curiosity to learn even more about these concepts. If I was successful in explanation, now you should not be satisfied, you should have more questions. If you don’t have any, then I completely failed in my explanation.

So, what are the questions? For starters, how do you know what objects being locked in the transaction? It’s actually quite trivial. The view V$LOCK has provided that information for years, albeit in a convoluted form. A new view V$LOCKED_OBJECT is a bit more user-friendly. Let’s examine that with an example. First, update a row:

SQL> update itltest set col2 = 'CHANGED BY SESSION AGAIN' where col1 = 221
  2  /

1 row updated.

We can check the transaction ID:

SQL> select dbms_transaction.local_transaction_id from dual'

LOCAL_TRANSACTION_ID
--------------------------------------------------------------------------------
2.16.41316

1 row selected.

As you learned from the previous installment in this series, the transaction ID is a series of numbers denoting undo segment number, slot# and record# (also known as sequence#) respectively, separated by periods.

Now, check the view V$LOCKED_OBJECT:

SQL> select * from v$locked_object
  2  /

    XIDUSN    XIDSLOT     XIDSQN  OBJECT_ID SESSION_ID
---------- ---------- ---------- ---------- ----------
ORACLE_USERNAME                OS_USER_NAME
------------------------------ ------------------------------
PROCESS                  LOCKED_MODE
------------------------ -----------
         2         16      41316      95263         56
ARUP                           oracle
13181                              3

The view shows Undo Segment# (XIDUSN), Undo Slot# (XIDSLOT) and Undo Rec# (XIDSQN), which can be used to construct the transaction ID to be joined with the V$TRANSACTION to get the details. The view contains the column OBJECT_ID. Another important column is LOCKED_MODE, which shows the mode the rows are locked. In this case, it’s “3”, which means Row Exclusive. Here is a script that decodes the modes as well as reports the object name.

select
    owner               object_owner,
    object_name         object_name,
    session_id          oracle_sid,
    oracle_username     db_user,
    decode(LOCKED_MODE,
        0, 'None',
        1, 'Null',
        2, 'Row Share',
        3, 'Row Exclusive',
        4, 'Share',
        5, 'Sub Share Exclusive',
        6, 'Exclusive',
        locked_mode
    )                   locked_mode
    from v$locked_object lo,
        dba_objects do
    where
        (xidusn||'.'||xidslot||'.'||xidsqn)
            = ('&transid')
    and
        do.object_id = lo.object_id
/

Save this script and execute it when you need further details on the transaction. The script will ask for the transaction ID which you can pass in the format reported by dbms_transaction.local_transaction_id.

Next, you may draw my attention to the point #3 above. If there are 10 records in the block and a transaction updated (and therefore locked) all ten of them, how many ITL entries will be used – one or ten?

Good question (I have to say that, since I asked that :) I suppose you can answer that yourself. Ten ITL slots may be feasible; but what if the block has 10,000 records? Is it possible to have that many ITL slots in the block header? Let’s ponder on that for a second. There will be two big issues with that many ITL slots.

First, each ITL slot, by the way, is 24 bytes long. So, 10000 slots will take up 240,000 bytes or almost 22 KB. A typical Oracle block is 8KB (I know, it could be 2K, 4K or 16K; but suppose it is the default 8K). Of course it can’t accommodate 22KB.

Second, even if the total size of the ITL slots is less than the size of the block, where will be the room to hold data? In addition, there should be some space for the data block overhead; where will that space come from?

Obviously, these are genuine problems that make one ITL slot per row impractical. Therefore Oracle does not create an ITL entry for each locked row. Instead, it creates the ITL entry for each transaction, which may have updated a number of rows. Let me repeat that – each ITL slot in the block header actually refers to a transaction; not the individual rows. That is the reason why you will not find the rowid of the rows locked in the ITL slot. Here is the ITL entry from the block header, again:

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000a.019.00007c05  0x00c00288.1607.0e  ----    1  fsc 0x0000.00000000
0x02   0x0003.017.00009e24  0x00c00862.190a.0f  C---    0  scn 0x0000.02234e2b

There is a reference to a transaction ID; but not rowid. When a transaction wants to update a row in the block, it checks the ITL entries. If there is none, it means rows in that block are unlocked. However, if there are some ITL entries, does it mean that some rows in the block are locked? Not necessarily. It simply means that the rows the block were locked earlier; but that lock may or may not be active now. To check if a row is locked, the transaction checks for the lock byte stored along with the row.

That brings up an interesting question. If presence of an ITL slot does not mean a record in the block is locked, when does the ITL slot get cleared so that it can be reused, or when does that ITL slot disappear? Shouldn’t that ITL slot disappear when the transaction ends by commit or rollback? That should be the next burning question throbbing in your head right now.

Clearing of ITL Slots

To answer that question, consider this scenario: a transaction updates 10000 records, on 10000 different blocks. Naturally there will be 10000 ITL slots, one on each block, all pointing to the same transaction ID. The transaction commits; and the locks are released. Should Oracle revisit each block and remove the ITL entry corresponding to the transaction as a part of the commit operation?

If that were the processing logic, the commit would have taken a very long time. Acquiring the buffers of the 10000 blocks and updating the ITL entry will not be quick; it will take a very long time, prolonging the commit processing. From part 1 of the series, you learned that the commit processing is actually very quick, with a flush of the log buffer to redo logs and the writing of the commit marker in the redo stream. Even a checkpoint to the datafiles is not done as a part of commit processing – all the effort going towards making the process fast, very fast. Had Oracle added the logic of altering ITL slots, the commit processing would have been potentially long, very long. Therefore Oracle does not remove the ITL entries after that transaction ends (by committing, or rolling back); the slots are just left behind as artifacts.

The proof, as they say, is in the pudding. Let’s see with an example:

SQL> create table itltest (col1 number, col2 varchar2(200));

Table created.

SQL> begin
  2     for i in 1..1000 loop
  3             insert into itltest values (
  4                     i,'INITIAL VALUE OF COLUMN');
  5     end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

This inserts 1000 records. Let’s find out the file and block these records go to:

  1  select
  2     dbms_rowid.rowid_relative_fno(rowid) File#,
  3     dbms_rowid.rowid_block_number(rowid) Block#,
  4     count(1)
  5  from itltest
  6  group by
  7     dbms_rowid.rowid_relative_fno(rowid),
  8     dbms_rowid.rowid_block_number(rowid)
  9  order by
 10*    1,2
SQL> /

     FILE#     BLOCK#   COUNT(1)
---------- ---------- ----------
         7       4027        117
         7       4028        223
         7       4029        220
         7       4030        220
         7       4031        220

5 rows selected.

Let’s identify the rows in a specific block, block# 4028, for instance.

SQL> select min(col1), max(col1)
  2  from itltest
  3  where dbms_rowid.rowid_block_number(rowid) = 4028
SQL> /

 MIN(COL1)  MAX(COL1)
---------- ----------
1 223

1 row selected.

Block 4028 has the rows 1 through 223. That’s all we need to know for now. We will limit our activity to this block alone. We will need to update a single row in this block from a session:

SQL> update itltest set col2 = ‘Changed’ where col1 = 1;

Do NOT commit; just keep the session at this point. Open a different session, and update a different row, e.g. one with col1 = 2. Since this is a different row, there will be no lock contention. Similarly update 20 other rows on this block. There will be 20 different transactions on the rows of this table.

Let’s examine the innards of the block by dumping it. Before that, we should flush the block to the disk.

SQL> alter system checkpoint;

System altered.

SQL> alter system dump datafile 7 block min 4028 block max 4028;

System altered.

The information will be written to a tracefile. We have to know the SPID of the process to identify the tracefile:

SQL> select p.spid
  2  from v$session s, v$process p
  3  where s.sid = (select sid from v$mystat where rownum < 2)
  4* and p.addr = s.paddr
SQL> /

SPID
------------------------
9537

We will locate a file called D112D2_ora_9537.trc in the trace directory. Please note, this tracefile is named OracleSID_ora_ProcessID.trc; so the exact name will be different your system. Open the file and search for “Itl”. Here is an excerpt from the file:

Block header dump:  0x01c00fbc
 Object id on Block? Y
 seg/obj: 0x1741f  csc: 0x00.235a849  itc: 36  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1c00fb8 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0008.00d.0000a1eb  0x00c015d1.1d3c.28  ----    1  fsc 0x0005.00000000
0x02   0x0007.018.00007fab  0x00c01246.180b.21  ----    1  fsc 0x0005.00000000
0x03   0x0003.004.0000a1b0  0x00c005ef.1a18.07  ----    1  fsc 0x0005.00000000
0x04   0x0010.010.00000004  0x00c011ee.0001.10  ----    1  fsc 0x0005.00000000
0x05   0x000e.00e.00000003  0x00c011cb.0001.0f  ----    1  fsc 0x0005.00000000
0x06   0x000c.00e.00000003  0x00c011ab.0001.1b  ----    1  fsc 0x0005.00000000
0x07   0x0013.011.00000004  0x00c00f9c.0001.0f  ----    1  fsc 0x0005.00000000
0x08   0x0002.00a.0000a166  0x00c014d8.1c06.12  ----    1  fsc 0x0005.00000000
0x09   0x0001.010.00007f65  0x00c00cd3.16ae.14  ----    1  fsc 0x0005.00000000
0x0a   0x0014.01b.00000008  0x00c00faa.0003.67  ----    1  fsc 0x0005.00000000
0x0b   0x000f.00f.00000003  0x00c011db.0001.20  ----    1  fsc 0x0005.00000000
0x0c   0x000d.00f.00000004  0x00c011bb.0001.1d  ----    1  fsc 0x0005.00000000
0x0d   0x0012.010.00000003  0x00c00f8b.0001.1c  ----    1  fsc 0x0005.00000000
0x0e   0x000a.00c.00007f76  0x00c003d7.16ea.31  ----    1  fsc 0x0005.00000000
0x0f   0x0011.010.00000004  0x00c011fb.0001.10  ----    1  fsc 0x0005.00000000
0x10   0x0009.000.0000a236  0x00c00e91.1bbe.17  ----    1  fsc 0x0005.00000000
0x11   0x0006.00e.0000a1fc  0x00c0035c.1c24.2d  ----    1  fsc 0x0005.00000000
0x12   0x000b.012.00000003  0x00c01193.0001.1d  ----    1  fsc 0x0005.00000000
0x13   0x0004.00e.00007ff7  0x00c00d01.1771.0a  ----    1  fsc 0x0005.00000000
0x14   0x0005.002.0000a19f  0x00c00f1a.1bd6.1d  ----    1  fsc 0x0005.00000000
0x15   0x0015.000.00000002  0x00c00fba.0000.02  ----    1  fsc 0x0005.00000000
0x16   0x0016.000.00000002  0x00c00fca.0000.02  ----    1  fsc 0x0005.00000000
0x17   0x0017.000.00000002  0x00c00fda.0000.02  ----    1  fsc 0x0005.00000000
0x18   0x0018.000.00000002  0x00c00fea.0000.02  ----    1  fsc 0x0005.00000000
0x19   0x0019.000.00000002  0x00c00ffa.0000.02  ----    1  fsc 0x0005.00000000
0x1a   0x001a.000.00000002  0x00c0100a.0000.02  ----    1  fsc 0x0005.00000000
0x1b   0x001b.000.00000002  0x00c0101a.0000.02  ----    1  fsc 0x0005.00000000
0x1c   0x001c.000.00000002  0x00c0102a.0000.02  ----    1  fsc 0x0005.00000000
0x1d   0x001d.000.00000002  0x00c0103a.0000.02  ----    1  fsc 0x0005.00000000
0x1e   0x001e.002.00000002  0x00c0104a.0000.03  ----    1  fsc 0x0005.00000000
0x1f   0x001f.002.00000002  0x00c0105a.0000.03  ----    1  fsc 0x0005.00000000
0x20   0x0020.000.00000002  0x00c0106a.0000.02  ----    1  fsc 0x0005.00000000
0x21   0x0021.005.00000002  0x00c0107a.0000.08  ----    1  fsc 0x0000.00000000
0x22   0x0022.000.00000002  0x00c0108a.0000.02  ----    1  fsc 0x0005.00000000
0x23   0x0023.000.00000002  0x00c0109a.0000.02  ----    1  fsc 0x0005.00000000
0x24   0x0024.000.00000002  0x00c010aa.0000.02  ----    1  fsc 0x0005.00000000
bdba: 0x01c00fbc
data_block_dump,data header at 0xeb6994

Note the Itl entries – there is an entry for each transaction, marked by its transaction ID, as expected. When the block was created, there were two ITL slots. As the demand for locks increased, additional slots were created and used for these new transactions.

Now go to all these sessions and either commit or rollback to end the transactions. Dump the block and search for “Itl”. The ITL slots are still there, even though the transactions have ended and the locks released. Oracle does not update the ITL entries.

So, when does the ITL entry gets cleared? When block’s buffer is written to the disk, the unneeded ITL entries are checked and cleared out. Let’s force a block flushing:

SQL> alter system checkpoint;

Now dump the data block once again and examine the ITLs. Here is an excerpt from the tracefiles.

Object id on Block? Y
 seg/obj: 0x1741f  csc: 0x00.235a849  itc: 36  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1c00fb8 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0014.016.00000008  0x00c00fb3.0002.11  C---    0  scn 0x0000.0235a524
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x03   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x04   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x05   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x06   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x07   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x08   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x09   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x0a   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x0b   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x0c   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x0d   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x0e   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x0f   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x10   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x11   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x12   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x13   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x14   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x15   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x16   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x17   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x18   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x19   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x1a   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x1b   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x1c   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x1d   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x1e   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x1f   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x20   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x21   0x0021.002.00000002  0x00c0107a.0000.05  C---    0  scn 0x0000.0235a807
0x22   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x23   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x24   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
bdba: 0x01c00fbc
data_block_dump,data header at 0x484994

Note the Xid columns – the transaction Id, which shows 0’s, meaning there is no transaction using the ITL slots. These ITL slots are eligible for reuse. Update two rows from two different sessions, checkpoint and dump the block once again. Here is the ITL information again:

Itl           Xid                  Uba          Flag  Lck        Scn/Fsc
0x01   0x0014.016.00000008  0x00c00fb3.0002.11  C---    0  scn 0x0000.0235a524
0x02   0x0005.009.0000a1a5  0x00c00f21.1bd6.04  ----    1  fsc 0x0016.00000000
0x03   0x000a.002.00007f7a  0x00c003d8.16ea.13  ----    1  fsc 0x0016.00000000
0x04   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x05   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x06   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
... and so on ...

The first two Itl slots are now used. Note, only the ITL slots in this specific block will be created. All other blocks will continue to have the same number of ITL slots.

ITL Waits

From the part 2 of this series you learned that the ITL slots are not preallocated, at least not all of them. When a transaction needs to lock rows in the block, and it does not find an unused ITL slot, Oracle creates a new ITL slot for the transaction. Consider the figure below. There is no more room in the block for a new ITL entry. A new transaction comes in to update Record3. What will happen?

The transaction will have to wait. This is not the same wait as a row lock; because there is no lock on the row marked Record3.Instead, session will wait on a special wait event. You can check the wait event from the V$SESSION view.

SQL> select event
  2  from v$session
  3  where sid = 78
  4  /

EVENT
----------------------------------------------------------------
enq: TX - allocate ITL entry

The moment one of the transactions – from either Session1 or Session2 end by commit or rollback, the new transaction can grab that ITL slot and complete the locking operation. You will see that wait event disappear.

Since the ITL waits come and go, how do you capture them; or more specifically how will you know which objects are being subjected to this wait? It’s fairly trivial. Since Oracle 9.2 a new view – V$SEGMENT_STATSTICS – shows various segment related statistics on segments. Here is an example:

SQL> select statistic_name, value from v$segment_statistics
  2* where object_name = 'ITLTEST';

STATISTIC_NAME                                                        VALUE
---------------------------------------------------------------- ----------
logical reads                                                          7216
buffer busy waits                                                         3
gc buffer busy                                                            0
db block changes                                                       5600
physical reads                                                            0
physical writes                                                          39
physical read requests                                                    0
physical write requests                                                   9
physical reads direct                                                     0
physical writes direct                                                    0
optimized physical reads                                                  0
gc cr blocks received                                                     0
gc current blocks received                                                0
ITL waits                                                                 2
row lock waits                                                            1

Various stats on the segment named ITLTEST are listed here. Of the lot, the one interesting to our discussion here is “ITL waits”, which shows “2”. It means the table ITLTEST has waited 2 times for ITL waits (not for a legitimate row locking, which shown in the stats immediately afterwards).

Conversely, you may want to find out what have been subjected to ITL waits. The following query shows you that:

SQL> select owner, object_name
  2  from v$segment_statistics
  3  where statistic_name = 'ITL waits'
  4* and value > 0;

OWNER                          OBJECT_NAME
------------------------------ ------------------------------
ARUP                           ITLTEST

1 row selected.

The view has many more columns for making filtering easier:

SQL> desc v$segment_statistics
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(30)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 TABLESPACE_NAME                                    VARCHAR2(30)
 TS#                                                NUMBER
 OBJ#                                               NUMBER
 DATAOBJ#                                           NUMBER
 OBJECT_TYPE                                        VARCHAR2(18)
 STATISTIC_NAME                                     VARCHAR2(64)
 STATISTIC#                                         NUMBER
 VALUE                                              NUMBER

Actually selecting from the above view is a bit expensive on the database. The base view is V$SEGSTAT, shown below:

SQL> desc v$segstat
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TS#                                                NUMBER
 OBJ#                                               NUMBER
 DATAOBJ#                                           NUMBER
 STATISTIC_NAME                                     VARCHAR2(64)
 STATISTIC#                                         NUMBER
 VALUE                                              NUMBER

While V$SEGMENT_STATISTICS show much more information, it’s a little slow due to all those joins. If you don’t need all that information, you may want to select instead from V$SEGSTAT, which is usually faster. The columns are self explanatory; but here they are in any case:

TS# - the tablespace number. You can use this to get the tablespace name from TS$ table joined by TS# column
OBJ# - the object_id, from dba_objects. You can get the rest of the details from that view
DATAOBJ# - the data_object_id, from dba_objects. This is usually the same as object_id; except in case of sub-objects such as partitions in which case they differ.

One important point about this view: like all V$ views, it shows information from the start of the instance. When the instance recycles, the values are reset to 0. To get a historical information, you should periodically select from this view and store in a regular table. If you have AWR enabled, you can check the historical records from there. Here is an example:

SQL> select snap_id, ITL_WAITS_TOTAL, ITL_WAITS_DELTA
  2  from DBA_HIST_SEG_STAT
  3  where obj# = 95263
  4* order by snap_id;

   SNAP_ID ITL_WAITS_TOTAL ITL_WAITS_DELTA
---------- --------------- ---------------
      5014               2               2

1 row selected.

Solution

Well, so far I talked about a problem. Is there a solution? Of course there is.

Remember, the cause of ITL waits is simply space inside a block. If there is no space inside the block to grow the ITL list to add more slots, the sessions will wait with the ITL wait event. So the solution is to reserve same space for that growth. There are two basic alternatives to solve the ITL wait problem:

(1) INITRANS.

Remember the little clause during table or index creation? Have you ever explicitly set it to its non-default value? Most likely you haven’t. It specifies the number of ITL slots that must be initially created on a block. If you specify 10, then 10 ITL slots are created on the block, guaranteeing the slot for 10 transactions. The 11th transaction will need to extend the ITL list; or wait if that is not possible.

To check for the INITRANS value of tables, use:

SQL> select ini_trans
  2  from dba_tables
  3  where table_name = 'T';

 INI_TRANS
----------
        10

(2) Less Space for Data

The other option is to make sure that you have less data inside a data block to allow the ITL sufficient free space. You can do it by several ways – by setting a high value of PCTFREE and by setting MINIMIZE_RECORDS_PER_BLOCK clause.

Obviously, both these options waste space inside the block; so you should use these only on those segments that experience high ITL waits, as you can see from AWR reports or your homegrown data collectors. To increase the INITRANS of an existing table, you should issue:

ALTER TABLE ITLTEST INITRANS 10;

Remember, the setting affects the new blocks only; not the existing ones. You can issue ALTER TABLE … MOVE command to relocate the blocks to new blocks, and thereby effecting the new settings.

What is the upper limit of the ITL slots? They are set by a parameter of the object called MAXTRANS. The default is 256. If you set it to 20, the ITL slots will go up to that much only. However, the parameter has no effect in Oracle 10gR2. It’s ignored and the ITL slots can go up to 256.

Takeaways

In this installment you learned:

(1) ITL itself does not say whether a row is locked or not. The lock byte stored in the row tells that.
(2) When a transaction ends, the corresponding ITL entry is not removed or altered. It gets cleared during flush to the disk.
(3) When the ITL can’t grow due to the lack of space in the block, the session waits will the event “enq: TX - allocate ITL entry”
(4) You can identify the segments that have suffered from this wait by checking the view V$SEGSTAT.
(5) To reduce the possibility of these waits, you should have sufficient space inside the data block for ITL expansion, either by defining higher number of initial ITL slots, or forcing less data inside the blocks.

I hope you enjoyed this installment. As always, I will appreciate if you drop in a line on how you liked it.

How Oracle Locking Works

2011-01-03T01:09:00.004-05:00

Myth: Oracle has a Central Locking System

A week or so ago I started a new series – 100 Things You Probably didn’t Know about Oracle Database . Part 1 of the series proved quite popular, as I surmise from the emails I received. Here is the Part 2 of the series.

When a transaction updates a row, it puts a lock so that no one can update the same row until it commits. When another transaction issues an update to the same row, it waits until the first one either commits or rolls back. After the first transaction performs a commit or rollback, the update by the second transaction is executed immediately, since the lock placed by the first transaction is now gone. How exactly does this locking mechanism work? Several questions come to mind in this context:

Is there some kind of logical or physical structure called lock?
How does the second transaction know when the first transaction has lifted the lock?
Is there some kind of “pool” of such locks where transactions line up to get one?
If so, do they line up to return it when they are done with the locking?
Is there a maximum number of possible locks?
Is there something called a block level lock? Since Oracle stores the rows in blocks, when all or the majority of rows in the blocks are locked by a single transaction, doesn’t it make sense for to lock the entire block to conserve the number of locks?
The previous question brings up another question – does the number of active locks in the database at any point really matter?

If you are interested to learn about all this, please read on.

Lock Manager

Since locks convey information on who has what rows modified but not committed, anyone interested in making the update much check with some sort of system that is available across the entire database. So, it makes perfect sense to have a central locking system in the database, doesn’t it? But, when you think about it, a central lock manager can quickly become a single point of contention in a busy system where a lot of updates occur. Also, when a large number of rows are updated in a single transaction, an equally large number of locks will be required as well. The question is: how many? One can guess; but it will be at best a wild one. What if you guessed on the low side and the supply of available locks is depleted? In that case some transactions can’t get locks and therefore will have to wait (or, worse, abort). Not a pleasant thought in a system that needs to be scalable. To counter such a travesty you may want to make the available supply of locks really high. What is the downside of that action? Since each lock would potentially consume some memory, and memory is finite, it would not be advisable to create an infinite supply of locks.

Some databases actually have a lock manager with a finite supply of such locks. Each transaction must ask to get a lock from it before beginning and relinquish locks to it at the completion. In those technologies, the scalability of application suffers immensely as a result of the lock manager being the point of contention. In addition, since the supply of locks is limited, the developers need to commit frequently to release the locks for other transactions. When a large number of rows have locks on them, the database replaces the row locks with a block level lock to cover all the rows in the block – a concept known as lock escalation. Oracle does not follow that approach. In Oracle, there no central lock manager, no finite limit on locks and there is no such concept called lock escalation. The developers commit only when there is a logical need to do so; not otherwise.

Lock Management in Oracle

So, how is that approach different in case of Oracle? For starters, there is no central lock manager. But the information on locking has to be recorded somewhere. Where then? Well, consider this: when a row is locked, it must be available to the session, which means the session’s server process must have already accessed and placed the block in the buffer cache prior to the transaction occurring. Therefore, what is a better place for putting this information than right there in the block (actually the buffer in the buffer cache) itself?

Oracle does precisely that – it records the information in the block. When a row is locked by a transaction, that nugget of information is placed in the header of the block where the row is located. When another transaction wishes to acquire the lock on the same row, it has to access the block containing the row anyway (as you learned in Part 1 of this series) and upon reaching the block, it can easily confirm that the row is locked from the block header. A transaction looking to update a row in a different block puts that information on the header of that block. There is no need to queue behind some single central resource like a lock manager. Since lock information is spread over multiple blocks instead of a single place, this mechanism makes transactions immensely scalable.

Being the smart reader you are, you are now hopefully excited to learn more or perhaps you are skeptical. You want to know the nuts and bolts of this whole mechanism and, more, you want proof. We will see all that in a moment.

Transaction Address

Before understanding the locks, you should understand clearly what a transaction is and how it is addressed. A transaction starts when an update to data such as insert, update or delete occurs (or the intention to do so, e.g. SELECT FOR UPDATE) and ends when the session issues a commit or rollback. Like everything else, a specific transaction should have a name or an identifier to differentiate it from another one of the same type. Each transaction is given a transaction ID. When a transaction updates a row (it could also insert a new row or delete an existing one; but we will cover that little later in this article), it records two things:

The new value
The old value

The old value is recorded in the undo segments while the new value is immediately updated in the buffer where the row is stored. The data buffer containing the row is updated regardless of whether the transaction is committed or not. Yes, let me repeat – the data buffer is updated as soon as the transaction modifies the row (before commit). If you didn’t know that, please see the Part 1 of this series.

Undo information is recorded in a circular fashion. When new undo is created, it is stored in the next available undo “slot”. Each transaction occupies a record in the slot. After all the slots are exhausted and a new transaction arrives, the next processing depends on the state of the transactions. If the oldest transaction occupying any of the other slots is no longer active (that is either committed or rolled back), Oracle will reuse that slot. If none of the transactions is inactive, Oracle will have to expand the undo tablespace to make room. In the former case (where a transaction is no longer active and its information in undo has been erased by a new transaction), if a long running query that started before the transaction occurred selects the value, it will get an ORA-1555 error. But that will be covered in a different article in the future. If the tablespace containing the undo segment can’t extend due to some reason (such as in case of the filesystem being completely full), the transaction will fail.

Speaking of transaction identifiers, it is in the form of three numbers separated by periods. These three numbers are:

Undo Segment Number where the transaction records its undo entry
Slot# in the undo segment
Sequence# (or wrap) in the undo slot

This is sort of like the social security number of the transaction. This information is recorded in the block header. Let’s see the proof now through a demo.

Demo

First, create a table:

SQL> create table itltest (col1 number, col2 char(8));

Insert some rows into the table.

SQL> begin
  2     for i in 1..10000 loop 
  3             insert into itltest values (i,'x');
  4     end loop;
  5     commit;
  6  end;
  7  /

Remember, this is a single transaction. It started at the “BEGIN” line and ended at “COMMIT”. The 10,000 rows were all inserted as a part of the same transaction. To know the transaction ID of this transaction, Oracle provides a special package - dbms_transaction. Here is how you use it. Remember, you must use it in the same transaction. Let’s see:

SQL> select dbms_transaction.local_transaction_id from dual;

LOCAL_TRANSACTION_ID
------------------------------------------------------------------------


1 row selected.

Wait? There is nothing. The transaction ID returned is null. How come?

If you followed the previous section closely, you will realize that the transaction ends when a commit or rollback is issued. The commit was issued inside the PL/SQL block. So, the transaction had ended before you called the dbms_transaction is package. Since there was no transaction, the package returned null.

Let’s see another demo. Update one row:

SQL> update itltest set col2 = 'y' where col1 = 1;

1 row updated.

In the same session, check the transaction ID:

SQL> select dbms_transaction.local_transaction_id from dual;

LOCAL_TRANSACTION_ID
-------------------------------------------------------------------------
3.23.40484

1 row selected.

There you see – the transaction ID. The three numbers separated by period signify undo segment number, slot# and record# respectively. Now perform a commit:

SQL> commit;

Commit complete.

Check the transaction ID again:

SQL> select dbms_transaction.local_transaction_id from dual;

LOCAL_TRANSACTION_ID
-------------------------------------------------------------------------


1 row selected.

The transaction is gone so the ID is null, as expected.

Since the call to the package must be in the same transaction (and therefore in the same session), how can you check the transaction in a different session? In real life you will be asked to check transaction in other sessions, typically application sessions. Let’s do a slightly different test. Update the row one more time and check the transaction:

SQL> update itltest set col2 = 'y' where col1 = 1;

1 row updated.

SQL> select dbms_transaction.local_transaction_id from dual;

LOCAL_TRANSACTION_ID
-----------------------------------------------------------------------
10.25.31749

1 row selected.

From a different session, check for active transactions. This information is available in the view V$TRANSACTION. There are several columns; but we will look at four of the most important ones:

ADDR – the address of the transaction, which is a raw value
XIDUSN – the undo segment number
XIDSLOT – the slot#
XIDSQN – the sequence# or record# inside the slot

SQL> select addr, xidusn, xidslot, xidsqn
  2  from v$transaction;

ADDR         XIDUSN    XIDSLOT     XIDSQN
-------- ---------- ---------- ----------
3F063C48         10         25      31749

Voila! You see the transaction id of the active transaction from a different session. Compare the above output to the one you got from the call to dbms_transaction package. You can see that the transaction identifier shows the same set of numbers.

Interested Transaction List

You must be eager to know about the section of the block header that contains information on locking and how it records it. It is a simple data structure called "Interested Transaction List" (ITL), a list that maintains information on transaction. The ITL contains several placeholders (or slots) for transactions. When a row in the block is locked for the first time, the transaction places a lock in one of the slots. In other words, the transaction makes it known that it is interested in some rows (hence the term "Interested Transaction List"). When a different transaction locks another set of rows in the same block, that information is stored in another slot and so on. When a transaction ends after a commit or a rollback, the locks are released and the slot which was used to mark the row locks in the block is now considered free (although it is not updated immediately - fact about which you will learn later in a different installment).

[Updated Jan 22, 2011] [Thank you, Randolph Geist (info@www.sqltools-plusplus.org) for pointing it out. I follow his blog http://oracle-randolf.blogspot.com/, which is a treasure trove of information.
The row also stores a bit that represents the whether it is locked or not.
[end of Update Jan 22, 2011]

ITLs in Action

Let's see how ITLs really work. Here is an empty block. The block header is the only occupant of the block.

This is how the block looks like after a single row has been inserted:

Note, the row was inserted from the bottom of the block. Now, a second row has been inserted:

A session comes in and updates the row Record1, i.e. it places a lock on the row, shown by the star symbol. The lock information is recorded in the ITL slot in the block header:

The session does not commit yet; so the lock is active. Now a second session - Session 2 - comes in and updates row Record2. It puts a lock on the record - as stored in the ITL slot.

I have used two different colors to show the locks (as shown by the star symbol) and the color of the ITL entry.

As you can clearly see, when a transaction wants to update a specific row, it doesn’t have to go anywhere but the block header itself to know if the row is locked or not. All it has to do is to check the ITL slots. However ITL alone does not show with 100% accuracy that row is locked (again, something I will explain in a different installment). The transaction must go to the undo segment to check if the transaction has been committed. How does it know which specifci part of the undo segment to go to? Well, it has the information in the ITL entry. If the row is indeed locked, the transaction must wait and retry. As soon as the previous transaction ends, the undo information is updated and the waiting transaction completes its operation.

So, there is in fact a queue for the locks, but it's at the block level, not at the level of the entire database or even the segment.

Demo

The proof is in the pudding. Let’s see all this through a demo. Now that you know the transaction entry, let’s see how it is stored in the block header. To do that, first, we need to know which blocks to look for. So, we should get the blocks numbers where the table is stored:

SQL> select file_id, relative_fno, extent_id, block_id, blocks
  2  from dba_extents
  3  where segment_name = 'ITLTEST';

   FILE_ID RELATIVE_FNO  EXTENT_ID   BLOCK_ID     BLOCKS
---------- ------------ ---------- ---------- ----------
         7            7          0       3576          8
         7            7          1       3968          8
         7            7          2       3976          8
         7            7          3       3984          8

To check inside the block, we need to “dump” the contents of the block to a tracefile so that we can read it. From a different session issue a checkpoint so that the buffer data is now written to the dis:

SQL> alter system checkpoint;

Now dump the data blocks 3576 through 3583.

SQL> alter system dump datafile 7 block min 3576 block max 3583;

System altered.

This will create a tracefile in the user dump destination directory. In case of Oracle 11g, the tracefile will be in the diag structure under /diag/rdbms///trace directory. It will be most likely the last tracefile generated. You can also get the precise name by getting the OS process ID of the session:

SQL> select p.spid
  2  from v$session s, v$process p
  3  where s.sid = (select sid from v$mystat where rownum < 2)
  4  and p.addr = s.paddr
  5  /

SPID
------------------------
9202

1 row selected.

Now look for a file named _ora_9202.trc. Open the file in vi and search for the phrase “Itl”. Here is an excerpt from the file:

Itl           Xid                  Uba          Flag  Lck        Scn/Fsc
0x01   0x000a.019.00007c05  0x00c00288.1607.0e  ----    1  fsc 0x0000.00000000
0x02   0x0003.017.00009e24  0x00c00862.190a.0f  C---    0  scn 0x0000.02234e2b

This is where the information on row locking is stored. Remember, the row locking information is known as Interested Transaction List (ITL) and each ITL is stored in a “slot”. Here it shows two slots, which is the default number. Look for the one where the “Lck” column shows a value. It shows “1”, meaning one of the rows in the blocks is locked by a transaction. But, which transaction? To get that answer, note the value under the “Xid” column. It shows the transaction ID - 0x000a.019.00007c05. These numbers are in hexadecimal (as indicated by the 0x at the beginning of the number). Using the scientific calculator in Windows, I converted the values to decimal as 10, 25 and 31749 respectively. Do they sound familiar? Of course they do; they are exactly as reported by both the record in v$transaction and the dbms_transaction.local_transaction_id function call.

This is how Oracle determines that there is a transaction has locked the row and correlates it to the various components in the other areas – mostly the undo segments to determne if it is active. Now that you know undo segments holds the transaction details, you may want to know more about the segment. Remember, the undo segment is just a segment, like any other table, indexes, etc. It resides in a tablespace, which is on some datafile. To find out the specifics of the segment, we will look into some more columns of the view V$TRANSACTION:

SQL> select addr, xidusn, xidslot, xidsqn, ubafil, ubablk, ubasqn, ubarec,
  2  status, start_time, start_scnb, start_scnw, ses_addr
  3  from v$transaction;

ADDR         XIDUSN    XIDSLOT     XIDSQN     UBAFIL     UBABLK     UBASQN
-------- ---------- ---------- ---------- ---------- ---------- ----------
    UBAREC STATUS           START_TIME           START_SCNB START_SCNW SES_ADDR
---------- ---------------- -------------------- ---------- ---------- --------
3F063C48         10         25      31749          3        648       5639
        14 ACTIVE           12/30/10 20:00:25      35868240          0 40A73784


1 row selected.

The columns with names starting with UBA show the undo block address information. Look at the above output. The UBAFIL shows the file#, which is “3” in this case. Checking for the file_id:

SQL> select * from dba_data_files
  2> where file_id = 3;

FILE_NAME
-------------------------------------------------------------------------
   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
+DATA/d112d2/datafile/undotbs1.260.722742813
         3 UNDOTBS1                       4037017600     492800 AVAILABLE
           3 YES 3.4360E+10    4194302          640 4035969024      492672
ONLINE


1 row selected.

Note the UBASQN (which is the undo block sequence#) value in the earlier output, which was 5639. Let’s revisit the ITL entries in the dump of block:

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000a.019.00007c05  0x00c00288.1607.0e  ----    1  fsc 0x0000.00000000
0x02   0x0003.017.00009e24  0x00c00862.190a.0f  C---    0  scn 0x0000.02234e2b

Look at the entry under the Uba column: 0x00c00288.1607.0e. As indicated by the “0x” at the beginning, these are in hexadecimal. Using a scientific calculator, let’s convert them. 1607 in hex means 5639 in decimal – the UBA Sequence# (UBASQN). The value “e” is 14 in decimal, which corresponds to the UBAREC. Finally the value 288 is 648 in decimal, which is the UBABLK. Now you see how the information is recorded in the block header and is also available to the DBA through the view V$TRANSACTION.

Let’s see some more important columns of the view. A typical database will have many sessions; not just one. Each session may have an active transaction, which means you have to link sessions to transactions to generate meaningful information. The transaction information also contains the session link. Note the column SES_ADDR, which is the address of the session that issued the transaction. From that, you can get the session information

SQL> select sid, username
  2  from v$session
  3  where saddr = '40A73784';

SID USERNAME
--- --------
123 ARUP

There you go – you now have the SID of the session. And now that you know the SID, you can look up any other relevant data on the session from the view V$SESSION.

Takeaways

Here is a summary of what you learned so far:

Transaction in Oracle starts with a data update (or intention to update) statement. Actually there are some exceptions which we will cover in a later article.
It ends when a commit or rollback is issued
A transaction is identified by a transaction ID (XID) which is a set of three numbers – undo segment#, undo slot# and undo record# - separated by periods.
You can view the transaction ID in the session itself by calling dbms_transaction.local_transaction_id function.
You can also check all the active transactions in the view v$transaction, where the columns XIDUSN, XIDSLOT and XIDSQN denote the undo segment#, undo slot# and undo rec# - the values that make up the transaction ID.
The transaction information is also stored in the block header. You can check it by dumping the block and looking for the term “Itl”.
The v$transaction view also contains the session address under SES_ADDR column, which can be used to join with the SADDR column of v$session view to get the session details.
From the session details, you can find out other actions by the session such as the username, the SQL issues, the machine issued from, etc.

I hope you found it useful. As always, I will be grateful to know how you liked it. In the next installment we will examine how these ITLs could cause issues and how to identify or resolve them.

New York Oracle User Group 2010 Session

2010-12-12T22:50:00.000-05:00

Thank you for all those who came to my session in NYOUG Metro Area Meeting. I know you had choices; and I am honored by for gracing my session. I apologize for running out of time. In the hindsight, I should have entertained questions at the end.

You can download the presentation here.
You can download the paper that describes the presentation here.

Based on the feedback I received, I started a new blog series "100 Things You Probably Didn't Know About Oracle Database". You may want to check out the first of the series. It talks about the commits and the frequency of block flushes to the disk - questions that came up during my presentation.

100 Things You Probably Didn't Know About Oracle Database

2010-12-12T21:59:00.006-05:00

Recently, while delivering a presentation on Cache Fusion at New York Oracle Users Group (www.nyoug.org), the regional user group where I have been a long time member, I was surprised to hear from many participants some beliefs they had held for a long time that were completely wrong. I always thought these were as obvious as they come; but of course I was dead wrong. What was even more surprising that most of these believers were veterans in Oracle Database technologies; not newbies. Part of the problem – I think – lies with the system that focuses on the execution rather than learning and part of it due to the lack of clear documentation. During that discussion some encouraged me to write about these. I immediately agreed it was a great idea and merited serious attention. Here is the product: my attempt at explaining some of the “mysteries” of how Oracle Database operates. I will cover 100 such nuggets of information, roughly once a week.

Before you start, however, I would like to bring your attention to this important point. You may already be aware of these facts. I did; so it is reasonable to believe that a vast majority would as well. Under no circumstances I am claiming these to be ground breaking or awe-inspiring. If you are already familiar with this specific knowledge, I am not at all surprised. Please feel free to skip. For those who read on, I hope you found these helpful and will take a moment to write to me how you felt.

Part 1: Myth of Commit Causing Buffer to be Flushed to the Disk

Consider a scenario: In the EMP table I updated my salary from 1000 to 2000; and committed immediately. The very instance after I issued commit, if I check the datafile on the disk of the tablespace where this table is located, which value should I see – 1000 or 2000? (Remember, the value was committed)

Did you answer 2000 (perhaps because it was committed)? If so, then consider a normal application where commits are issued up to tens of thousands of times every minute. In a traditional database the weakest link in the chain is always I/O. If Oracle issued an update of the datafile every time someone commits, it would grind to a halt.

Did you answer 1000? Well, in that case, consider a case when the instance crashes. The datafile would have had 1000; not 2000 – the value that was committed. In such a case the instance must bring back the value committed (2000, in this case) to the datafile. How?

Let’s examine a different scenario. Suppose I did not issue a commit after the update (perhaps I was not sure of the implication of giving myself a pay hike or perhaps I had pang of conscience). I left the session as is and left for the day. The next day I was sick and didn’t come to work. 24 hours passed since I updated the record. At that point, if someone reads the datafile, what value would they see – 1000 or 2000?

Did you answer 1000 – a logical choice since the read consistency model of Oracle Database guarantees that the other sessions will see the pre-change data for the un-committed transactions?

Question #3 in this scenario: if you check the redo log file (not the datafile), what value will you find there – 1000 or 2000? Remember, it has not been committed. Did you answer 1000? It sort of makes sense; the changes are not committed so there is no reason for them to be in the redo log file, which is a very important part of the recovery process. If you answered 2000, then how would you explain the recovery process? In case of instance failure, the recovery must read the redo log file and since the transaction was not committed, it must roll the vale back to the previous – 1000. How would it do that if the redo log file contains 2000, not 1000?

The answers, if you can’t wait any longer: 1000 for the first question, 2000 for the second and 2000 for the third. How so? Please read on.

Explanation

To understand the mechanics of the process, let’s go over the buffer management process of the Oracle database. It’s a rudimentary detail but is quite vital in the path to understand the myth here. Consider a very small table in an equally small tablespace we created:

SQL> create tablespace testts datafile '/tmp/testts_01.dbf' size 1M;

SQL> create table mythbuster1 (col1 varchar2(200)) tablespace testts;

Insert a row:

SQL> insert into mythbuster1 values (‘ORIGINAL_VALUE’);
SQL> Commit;

Shutdown and restart the database so that the buffer cache is completely devoid of this table. You can also issue ALTER SYSTEM FLUSH BUFFER_CACHE; but I want to make sure all traces of this table (and value of the column inside) vanish from all memory areas – buffer cache, shared pool, PGA, whatever. You can now check the presence of the value in the datafile:

$ strings /tmp/testts_01.dbf
}|{z
-N?pD112D2
TESTTS
 1j)
 w>!
ORIGINAL_VALUE

The value is right there. Now suppose a user issues a statement like this from SQL*Plus:

SQL> select * from mythbuster1;

Oracle creates a process – called “server process” – on behalf of this user session to service the request from the session. This process is named, in unix and like OS’es, oracle. Here is how you can find it out:

$ ps -aef|grep sqlplus
oracle   14257 14214  0 13:42 pts/2    00:00:00 sqlplus   as sysdba
$ ps -aef | grep 14257
oracle   14257 14214  0 13:42 pts/2    00:00:00 sqlplus   as sysdba
oracle   14258 14257  0 13:42 ?        00:00:00 oracleD112D2 DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

The process 14258 is the server process. The SQL*Plus process is known as the user process which can be any process a user executes such as a Java program, a Pro*C code, a TOAD session and so on. It’s the server process that handles all the interaction with the Oracle database; not the user process. This is why Oracle database interaction is said to be based on a Two Task Architecture; there are always two tasks – the user task that a regular user has written and the server task that performs the database operations. This is an important concept established during the early foundations of the Oracle database to protect the database from errant code in the user task introduced either maliciously or inadvertently.

The server process then identifies the block the row exists in. Since the database instance just came up the buffer cache is empty and the block will not be found. Therefore the server process issues a read call from the datafile for that specific block. The block is read from the disk to the buffer cache. Until the loading of the block from the disk to the buffer cache is complete, the session waits with the event – db file scattered read. In this very case the session issues a full table scan. Had it performed an index scan, the session would have waited with the event db file sequential read. [I know, I know – it seems to defy conventional logic a little bit. I would have assumed index scan to be named scattered reads and full table scans to be sequential].

Once this process is complete, the buffer cache holds the copy of the block of the table mythbuster1. Subsequent session, if they select from the table, will simply get the data from this buffer; not from the disk.

Now, suppose the session issues the statement:

SQL> update mythbuster1 set col1 = ‘CHANGED_VALUE’;

And commits:

SQL> commit;

Immediately afterwards, check for the presence of the values in the datafile:

$ strings /tmp/testts_01.dbf
}|{z
-N?pD112D2
TESTTS
 1j)
 w>!
ORIGINAL_VALUE

The old value, not the new value, is found. The datafile on the disk still has the old value; not the new one, even though the transaction has been committed. The update statement actually updated only the buffer in the cache; not the disk. So, when is the data on the disk updated?

The datafile gets updated by a process known as Database Writer (a.k.a. Database Buffer Writer). It’s named DBW0. Actually, there may be more than one such process and they are named DBW0, DBW1, etc. – more conveniently addressed as DBWn. For the purpose of the discussion here, let’s assume only one process – DBW0. It has only one responsibility – to update the datafile with the most up to date buffers from the buffer caches. [Note: I used buffer caches – plural. This is not a typo. There may be more than one buffer cache in the database – keep, recycle, default and other block sizes – but that’s for another day]. The buffer that has been updated is known as a dirty buffer since its contents are different from the block on the disk. DBW0 process writes the contents of the buffer to the disk – making it clean again.

But the big question is when DBW0 writes the dirty buffer to the disk? Ah, that’s the very question we are pondering over here. There are several “triggering” events that cause DBW0 to copy the buffers to the disk – also called flushing of the buffers. By the way, DBW0 is a lazy process; it does not flush buffers by itself or on a regular basis. It sleeps most of the time and must be woken up by another process to perform its duties. One such watchdog process is called the Checkpoint (you can check its existence by ps -aef | grep ckpt in Unix systems). Checkpoint actually does not perform the flushing (also called checkpointing activity); but calls the DBW0 process to do it. How often does Checkpoint process perform a checkpoint? It depends on various conditions – the biggest of all is the MTTR setting, which we will cover later in a different installment.

Next. let's examine a different scenario. Drop the table, create the table again, recycle the database to remove all buffers of the table and then perform the update; but do not commit. Then flush the buffers from the cache to the disk. You can also trigger a checkpointing activity manually instead of waiting for the checkpoint process. Here is how to do it:

SQL> alter system checkpoint;

After that statement completes, check the presence of the values in the datafile again:

$ strings /tmp/testts_01.dbf
}|{z
-N?pD112D2
TESTTS
 1j)
 w>!
CHANGED_VALUE,
ORIGINAL_VALUE

The old value is still there; but that is an artifact; it will eventually be gone. The new value is updated in the datafile. But do you remember a very important fact – the transaction is still not committed? In a different session, if you check the data in COL1 column, you will see the value ORIGINAL_VALUE. Where does Oracle get that value from? It gets that value from the Undo Segments in the Undo Tablespace. The undo tablespace contains the pre-change value.

Well, now you may wonder how on earth the server process knows that the data is uncommitted and therefore the undo segment is to be checked. Good question. Let me add yet another wrinkle to it – the datafile contains the new value; not the old one. How does Oracle even know to return which rows pre-change? It gets that information from the header of the block where the transactions are recorded – called Transaction Table – or, a little bit differently: Interested Transaction List (ITL). I will cover that in detail in a future installment of this series. For the time being, please bear in mind that the block header holds that information. When the server process accesses the buffer (or the block on the disk) to get the column value, it accesses the transaction table, sees that there is an uncommitted transaction against it and gets the undo information from there. Finally it creates a different copy of the buffer as it would have looked like had the update statement not been issued. This process is called Consistent Read (CR) Processing.

Now back to our original discussion. Since DBW0 does not immediately flush the buffers to the datafile, it makes datafile inconsistent with the committed data. Won’t that compromise the recovery process? What would happen when the instance crashes before the flushing has occurred? Since the transaction was previously committed, the recovery should update the datafile. Where does that information come from? Undo tablespace? No; undo tablespace is also another datafile; it gets flushed in the same manner; so it may not have those values. Besides it may not even contain the new value.

Redo Stream

This is where the other leg of the database’s guarantee of the committed transaction comes in. When the changes occur in the table, Oracle also records the information in another pool in the memory called Log Buffer. Compared to buffer caches, which could be several terabytes; this buffer is tiny – often just a few MBs. The update statement records the pre and post change values to the log buffer (not to the log file, mind you). But the log buffer is just an area of memory; it also goes away when the instance crashes. So how does Oracle use the information to protect the committed data?

This is where the redo log files (a.k.a. online redo logs) come into picture. When the session commits, the contents of the log buffer are flushed to the redo log files. Until the flushing is completed, the session waits with various wait events depending on conditions, the majority of which are “log file sync” and “log file parallel write”. But does the log buffer flushing occur only when a commit occurs? No. There are other triggering events as well:
(1) When one third of the log buffer is full
(2) When 1 MB of log buffer is written
(3) Every three seconds

There are other events as well; but these are the major ones. Since commit statement flushes the log buffer to the redo log file, even if the instance crashes the information is stored in the redo log file and can be easily read by the instance recovery processes. In case of a RAC database, a single instance may have crashed. The instance recovery is done by one of the surviving instances. But it must read the redo entries of the crashed instance to reconstruct the blocks on the disk. This is why the redo log files, although for only one instance, must be visible to all nodes.

Even if the commit is not issued, the other triggering events flush the contents of the log buffer to the redo log files as well. The presence of the redo entries on the redo log files is independent of the commit. From the previous discussion you learned that the checkpoint flushes the buffers from the cache to the disk, regardless of the issuance of the commit statement. Therefore, these are the interesting possibilities after a session updates the data (which is updated in the buffer cache):

Scenario	Session committed?	Log Buffer Flushed	Checkpoint Occurred	Datafile Updated	Redo Log Updated	Comment
1	No	No	No	No	No
2	No	Yes	No	No	Yes
3	No	No	Yes	Yes	No
4	No	Yes	Yes	Yes	Yes
5	Yes	Yes	No	No	Yes	Commit will force a redo log flush
6	Yes	Yes	Yes	Yes	Yes

Looking at the table above you may see some interesting conundrums – redo log has the changed data but datafile does not and vice versa. How does Oracle know when and what exactly to recover since the presence of record in the redo log file is not a guarantee that the data was committed?

To address that issue, Oracle places a special “marker”, called a Commit Marker in the redo stream which goes into the redo log buffer. When instance recovery is required, Oracle doesn’t just recover anything that is present in the redo log buffer; it looks for a commit marker. If one is not found, then the changes are deemed to be uncommitted; and therefore Oracle rolls them back. If the changes are not found in the redo log, then the changes are uncommitted – guaranteed (remember, a commit will definitely flush the log buffer to redo). In that case Oracle rolls them back from the datafiles – a process known as rolling back. When the changes are found in redo log (along with the commit marker) but no corresponding changes in the datafile (scenario #5), Oracle will apply the changes to the datafile from the redo entries – a process known as roll forward. Recovery consists of both rolling back and forward.

To put it all together, here is a rough algorithm for the actions of the recovery process:

Read the redo log entries starting with the oldest one
Check the SCN number of the change
Look for the commit marker. If the commit marker is found, then data has been committed.
If found, then look for the changes in the datafile (via the SCN number)
   Change has been reflected in the datafile?
   If yes, then move on
   If no, then apply the changes to the datafile (roll forward)
If not found, then the data is uncommitted. Look for the changes in the datafile.
   Change found in datafile?
   If no, then move on
   If yes, then update the datafile with the pre-change data (rollback)

Takeaways

Let me reiterate some of the lessons from this installment.

(1) Data buffers are flushed to the disk from the buffer cache independently of the commit statement. Commit does not flush the buffers to the disk.
(2) If the buffer is modified in the buffer cache but not yet flushed to the disk, it is known as a dirty buffer.
(3) If a buffer is clean (i.e. not dirty), it does not mean that the data changes have been committed.
(4) When a commit occurs, the log buffer (not the buffer cache) is flushed to the disk
(5) Log buffer may already have been flushed to the disk due to other triggering events. So if a change in found in the redo log file, the change is not necessarily commited.
(6) A commit statement puts a special “commit marker” on the redo log, which is the guarantee of a commit.
(7) The frequency of the flushing of the buffer cache to the datafiles is controlled by the MTTR setting and whether free buffers are needed in the cache due to incoming blocks from the datafiles.

How do you use this information? There are several things for you to consider:

(1) The more you commit, the more log buffer will be flushed, not very good for I/O.
(2) The more aggressive the MTTR target is, the less time it will take if the instance crashes, but the more the frequency of flushing to the datafile will be as well – causing I/O
(3) The MTTR target has nothing to do with commit frequency; they are two independent activities. So, reducing commit frequency will not cause a reduction in flushing frequency.
(4) If your buffer size is small, there will be more the need to flush
(5) Exactly how small is “small”? There is no fixed formula; it depends on how much of the data in the buffer cache is updated.

I hope you enjoyed this installment of “100 Things …”. In the future installments I will explain some of the other nuances of the Oracle database that you may not have been aware of. As always, I will highly appreciate if you could drop me a line telling me your feedback – good, bad and anything inbetwen.

Exadata on Cloud

2010-12-03T11:53:00.000-05:00

I just saw this from Oracle Newsflash. Oracle Launches Exadata On Demand for High-Performance Computing. http://www.oracle.com/newsletters/information-indepth/on-demand/dec-10/exadataod.html.

This is very exciting. Of course the details are little at this time. But when the model matures, it allows us to play with the technology without investing a whole lot in in. A pet peeve of mine about Exadata was that it was a hardware; not a software. Unlike other Oracle products, I couldn't just download it and test the features. The limitation is huge. My role in my organization is to develop roadmaps and champion technological innovations. But without getting hands dirty, it comes to a pure paper excercise, which is not something I ever do.

With this, I am sure a lot of folks will now be able to validate their current systems against the Exadata platforms, making it easier and more prudent for them to champion it within their sphere of influence.

Seminar in Bucharest

2010-11-19T15:40:00.000-05:00

Thank you all those who attended my seminars in Bucharest. Thank you for taking the time of two days with me. I hope it has been worthwhile. I sincerely apologize for the delay in start of the first day. It was entirely my fault - I messed up the time difference calculations.

Please access the scripts and other materials from http://www.proligence.com/advrman_bucharest. You should have the userid and password from the class. If you have forgotten, please let me know.

Here are is the addition resource I was referring to. To present the consolidated view the status of all RMAN jobs in a single catalog: http://arup.blogspot.com/2010/11/tool-to-present-consolidated-view-of.html

My Oracle New Features Series on OTN

2010-11-12T08:36:00.000-05:00

Some people have emailed me that they can't find my Oracle 11g New Features for DBAs and Developers on Oracle Technology Network anymore.

OTN has apparently done some cleanup, defragmentation and coalescing and as a result a lot of URLs have moved around, including those for articles of mine. The new URL for my Oracle 11g NF series is http://www.oracle.com/technetwork/articles/sql/index-099021.html

Just in case you are looking for my Oracle 10g New Features for DBAs and Developers series (including the 10gR2 addendum), here it is http://www.oracle.com/technetwork/articles/sql/index-082320.html.

While on the subject of changed URLs, in case you are wondering about my other series on OTN - Project Lockdown - that is a four part series on Oracle Security, here it is http://www.oracle.com/technetwork/articles/index-087388.html

Hope you find them useful.

Tool to Add Range Partitions Automatically to a Table

2010-11-11T00:12:00.001-05:00

Continuing on my earlier posts on partitioning, here are is another tool on this topic. If you have been using partitioned tables, you might have noticed that one of the thorny issues in partitioning is the need to create partitions. If you don't have the partition ready, the inserts will fail with the following message

ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

This error is pretty disastrous to applications as they essentially stop functioning, causing outage, until you add the necessary partition. What can you do to prevent it? Maintain a unblinking vigil, of course, which may quickly become impractical. Oracle 11g has a nice answer - the INTERVAL partitioning, which automatically creates partitions when the new records come in. What if you are on 10g or do not want to use INTERVAL partitioning for some reason? Is there a way to simulate the interval partitioning?

After getting frustrated with the lack of proper partitions more than once, I decided to develop this tool. I perfected the tool over several months to make it more generic. Here it is. Enjoy.

Important: This is provided for educational purpose only. Use it at your own risk. There is no implied or expressed warranty and the author assumes no responsibility for any adverse effect.

What the Tool does

The tool is implemented as a stored procedure. When executed, it reads a driver table called partition_create_schedule, which records the tables to which the partitions are to be added. The table is created as shown below:

/* ---------------------------------------------------------------------
Name        : partition_create_schedule
Purpose     : To store the retention period of partitions of
            : indivisual tables. This data is used by partition_drop
            : procedure to automatically drop partitions.
----------------------------------------------------------------------*/

create table partition_create_schedule
(
 OWNER                  VARCHAR2(30),
 TABLE_NAME             VARCHAR2(30),
 PARTITION_UNIT         VARCHAR2(15),
 NUM_PARTS_TO_CREATE    NUMBER(4)
)
/
comment on table partition_create_schedule is 'To record the tables for which partitions are to be added automatically'
/
comment on  column partition_create_schedule.owner is 'the owner of the table'
/
comment on  column partition_create_schedule.table_name is 'the name of the table'
/
comment on  column partition_create_schedule.PARTITION_UNIT is 'the partitioning scheme - MONTHLY/DAILY/WEEKELY/QUARTERLY/YEARLY'
/
comment on column partition_create_schedule.num_parts_to_create is 'the number of units to skip in creating the partition. For instance, if you want to create the next partition 3 days from now for a DAILY unit, place 3 here'
/

The partitioning unit is important. Remember, in date-range partitioned tables, you merely provide the range; not whether the range is monthly, weekly or something like that. Even if you have a somewhat similar range, there is no guarantee that it will remain so. You may decide to split some partition or coalesce two. Therefore it is important that you tell the tool what type of partitioning duration the table is under.

Let's insert some records:

insert into partition_create_schedule values ('ARUP','PTEST1','MONTHLY',1);
insert into partition_create_schedule values ('ARUP','PTEST2','MONTHLY',1);
insert into partition_create_schedule values ('ARUP','PTEST3','QUARTERLY',1);
insert into partition_create_schedule values ('ARUP','PTEST4','DAILY',1);
commit;

Now comes the tool - the procedure. To build it, I considered these objectives:

(1) The intervals could be daily, weekly, monthly, quarterly and yearly
(2) When this procedure executes, it should automatically compute the boundary of the partition to add from the existing partitions. This should not be expected to be input from the user.
(3) This procedure will be executed every day automatically. In case of a DAILY partition, a new partition will be created; but not for other ranges. For weekly ranges, the new partition should be created only on the first day of the week; for monthly, on the first day of the month, etc.
(4) The global indexes must be updated automatically
(5) It must handle the two possibilities of the existing highest partition, e.g. a partition with a distinct boundary value and one with (MAXVALUE) clause. In the former case, a partition is added. In the latter case, the maxvalue partition has to be "split"; not added. The procedure must take care of both cases automatically.
(6) When the partition is split, the local indexes get invalidated and must be rebuilt.
(7) It should send an email to the DBA in both success and failure.

The tool handles all the above objectives pretty well.

CREATE OR REPLACE procedure SYS.partition_create
as
 cursor sched_cur is
  select * 
  from partition_create_schedule
  ;
 l_sched_rec    sched_cur%rowtype;
 l_cutoff_date   date;
 l_hvc     varchar2(2000);
 l_new_hvc    varchar2(2000);
 l_old_hvc    varchar2(2000);
 l_part_name    varchar2(2000);
 l_new_part_name   varchar2(2000);
 l_table_name   varchar2(2000);
 l_hv     date;
 l_new_hv    date;
 l_sql     varchar2(2000);
 l_full     varchar2(32000);
 l_ts     varchar2(2000);
 l_errm     varchar2(2000);
 l_mail_body    varchar2(2000);
 l_out     varchar2(2000);
 l_recipients   varchar2(2000) :=
  'dba.ops@mycompany.com';
 errc     number(10);
 l_place     varchar2(2);
 l_exec_flag    varchar2(4);
 invalid_partition_unit exception;
begin
 execute immediate 'alter session set smtp_out_server = ''smtp.mycompany.com''';
 l_out :=
  rpad('Owner',10)||
  rpad('Table',30)||
  rpad('Part Unit',10)||
  rpad('Old HV',11)||
  rpad('New HV',11)||
  rpad('Exec',4)
  ;
 dbms_output.put_line(l_out);
 l_out :=
  rpad('-',9,'-')||' '||
  rpad('-',29,'-')||' '||
  rpad('-',9,'-')||' '||
  rpad('-',10,'-')||' '||
  rpad('-',10,'-')||' '||
  rpad('-',3,'-')
  ;
 dbms_output.put_line(l_out);
 open sched_cur;
 loop --{
  fetch sched_cur into l_sched_rec;
  exit when sched_cur%notfound;
  select high_value, tablespace_name
  into l_old_hvc, l_ts
  from dba_tab_partitions tp
  where partition_position = (
    select max(partition_position) from dba_tab_partitions
    where table_name = tp.table_name
    and table_owner = tp.table_owner
  )
  and table_name = l_sched_rec.table_name
  and table_owner = l_sched_rec.owner;
  if (l_old_hvc = 'MAXVALUE') then --{
   -- PMAX code. Split the partition
   -- first get the hign value of the partition just prior to PMAX
   select high_value, tablespace_name
   into l_hvc, l_ts
   from dba_tab_partitions tp
   where partition_position = (
     select max(partition_position) - 1 from dba_tab_partitions
     where table_name = tp.table_name
     and table_owner = tp.table_owner
   )
   and table_name = l_sched_rec.table_name
   and table_owner = l_sched_rec.owner;
   execute immediate 'select '||l_hvc||' from dual' into l_hv;
   l_exec_flag := 'NO';
   case l_sched_rec.partition_unit  --{
    when 'DAILY' then
     l_new_hv := l_hv + l_sched_rec.num_parts_to_create;
     l_new_part_name := 'Y'||to_char(l_new_hv,'YY')||'M'||to_char(l_new_hv,'MM')||'D'||to_char(l_new_hv,'DD');
     l_exec_flag := 'YES';
    when 'WEEKLY' then
     l_new_hv := l_hv + (7*l_sched_rec.num_parts_to_create);
     l_new_part_name := 'Y'||to_char(l_new_hv,'YY')||'W'||to_char(l_new_hv,'WW');
     if (to_char(sysdate,'d') = '1') then
      l_exec_flag := 'YES';
     end if;
    when 'MONTHLY' then
     l_new_hv := add_months(l_hv,l_sched_rec.num_parts_to_create);
     if (to_char(sysdate,'dd') = '1') then
      l_exec_flag := 'YES';
     end if;
     l_new_part_name := 'Y'||to_char(l_new_hv,'YY')||'M'||to_char(l_new_hv,'MM');
    when 'QUARTERLY' then
     l_new_hv := add_months(l_hv,3*l_sched_rec.num_parts_to_create);
     l_new_part_name := 'Y'||to_char(l_new_hv,'YY')||'Q'||to_char(l_new_hv,'Q');
     if (to_char(sysdate,'mm/dd') in ('01/01','04/01','07/01','10/01')) then
      l_exec_flag := 'YES';
     end if;
    when 'ANNUALLY' then
     l_new_hv := add_months(l_hv,12*l_sched_rec.num_parts_to_create);
     l_new_part_name := 'Y'||to_char(l_new_hv,'YY');
     if to_char(sysdate,'mm/dd') = '01/01' then
      l_exec_flag := 'YES';
     end if;
    else
     raise invalid_partition_unit; 
   end case; --}
   l_new_hvc := to_char(l_new_hv,'mm/dd/yyyy');
   l_sql := 'alter table '||
    l_sched_rec.owner||'.'||
    l_sched_rec.table_name||
    ' split partition pmax '||
    ' at (to_date('''||l_new_hvc||
    ''',''mm/dd/yyyy''))'||
    ' into ( partition '||l_new_part_name||
    ' tablespace '||l_ts||
    ' initrans 11 storage (freelists 11 freelist groups 7)'||
    ' , partition pmax) update global indexes';
  else --}{
   -- non PMAX code. Add the partition
   l_hvc := l_old_hvc;
   execute immediate 'select '||l_hvc||' from dual' into l_hv;
   l_exec_flag := 'NO';
   case l_sched_rec.partition_unit  --{
    when 'DAILY' then
     l_new_hv := l_hv + l_sched_rec.num_parts_to_create;
     l_new_part_name := 'Y'||to_char(l_new_hv,'YY')||'M'||to_char(l_new_hv,'MM')||'D'||to_char(l_new_hv,'DD');
     l_exec_flag := 'YES';
    when 'WEEKLY' then
     l_new_hv := l_hv + (7*l_sched_rec.num_parts_to_create);
     l_new_part_name := 'Y'||to_char(l_new_hv,'YY')||'W'||to_char(l_new_hv,'WW');
     if (to_char(sysdate,'d') = '1') then
      l_exec_flag := 'YES';
     end if;
    when 'MONTHLY' then
     l_new_hv := add_months(l_hv,l_sched_rec.num_parts_to_create);
     if (to_char(sysdate,'dd') = '1') then
      l_exec_flag := 'YES';
     end if;
     l_new_part_name := 'Y'||to_char(l_new_hv,'YY')||'M'||to_char(l_new_hv,'MM');
    when 'QUARTERLY' then
     l_new_hv := add_months(l_hv,3*l_sched_rec.num_parts_to_create);
     l_new_part_name := 'Y'||to_char(l_new_hv,'YY')||'Q'||to_char(l_new_hv,'Q');
     if (to_char(sysdate,'mm/dd') in ('01/01','04/01','07/01','10/01')) then
      l_exec_flag := 'YES';
     end if;
    when 'ANNUALLY' then
     l_new_hv := add_months(l_hv,12*l_sched_rec.num_parts_to_create);
     l_new_part_name := 'Y'||to_char(l_new_hv,'YY');
     if to_char(sysdate,'mm/dd') = '01/01' then
      l_exec_flag := 'YES';
     end if;
    else
     raise invalid_partition_unit; 
   end case; --}
   l_new_hvc := to_char(l_new_hv,'mm/dd/yyyy');
   l_sql := 'alter table '||
    l_sched_rec.owner||'.'||
    l_sched_rec.table_name||
    ' add partition '||
    l_new_part_name||
    ' values less than '||
    ' (to_date('''||l_new_hvc||
    ''',''mm/dd/yyyy''))'||
    ' tablespace '||l_ts||
    ' initrans 11 storage (freelists 11 freelist groups 7)';
  end if; --}
  l_out :=
   rpad(l_sched_rec.owner,10)||
   rpad(l_sched_rec.table_name,30)||
   rpad(l_sched_rec.partition_unit,10)||
   rpad(to_char(l_hv,'mm/dd/yyyy'),11)||
   rpad(l_new_hvc,11)||
   rpad(l_exec_flag,4)
  ;
  dbms_output.put_line(l_out);
  if (l_exec_flag = 'YES') then --{
   dbms_output.put_line('l_new_part_name='||l_new_part_name);
   dbms_output.put_line('l_new_hvc='||l_new_hvc);
   l_mail_body := 'Statement Executed:'||l_sql;
         dbms_output.put_line('l_mail_body='||l_mail_body);
   begin
    execute immediate l_sql;
             l_mail_body := l_mail_body ||'...OK';
   exception
    when OTHERS then
     l_errm := SQLERRM;
     l_mail_body := l_mail_body||' ERROR='||l_errm;
     raise;
   end;
   -- table partition split. Now index.
   for ind_cur in (
    select index_owner, index_name, partition_name
    from dba_ind_partitions
    where (index_owner, index_name) in (
     select owner, index_name
     from dba_indexes
     where table_name = l_sched_rec.table_name
     and table_owner = l_sched_rec.owner
    )
   and status = 'UNUSABLE'
   ) loop --{
    dbms_output.put_line('Index Part='||ind_cur.index_owner||'.'||ind_cur.index_name||'.'||ind_cur.partition_name);
    l_sql := 'alter index '||
     ind_cur.index_owner||'.'||
     ind_cur.index_name||' rebuild partition '||
     ind_cur.partition_name||' nologging online';
    l_mail_body := l_mail_body||chr(12)||
     'Statement Executed:'||l_sql;
             dbms_output.put_line('l_mail_body='||l_mail_body);
    begin
     execute immediate l_sql;
        l_mail_body := l_mail_body||'...OK';
    exception
     when OTHERS then
         l_errm := SQLERRM;
         l_mail_body := l_mail_body||' ERROR='||l_errm;
      raise;
    end;
   end loop; --}
   -- index partitions made usable
  end if; --}
 end loop; --}
 close sched_cur;
 dbms_output.put_line(l_full);
 utl_mail.send(
  'oracle@prodb1',l_recipients,
  null, null,
  'Succeeded: PROPRD1 Partition Create',
  l_mail_body
 );
exception
 when OTHERS then
  l_errm := SQLERRM;
  utl_mail.send(
   'oracle@prodb1',l_recipients,
   null, null,
   'Failed: PROPRD1 Partition Create',
   l_mail_body
  );
  raise_application_error(-20001,l_errm);
end;
/
show error

The code is self explanatory. Let's see some example outputs:

SQL> @ex
Owner     Table                         Part Unit Old HV     New HV     Exec
--------- ----------------------------- --------- ---------- ---------- ---
ARUP      PTEST1                        MONTHLY   02/01/2010 04/01/2010 NO
ARUP      PTEST2                        MONTHLY   02/01/2010 04/01/2010 NO
ARUP      PTEST3                        QUARTERLY 04/01/2010 10/01/2010 NO
ARUP      PTEST4                        DAILY     01/12/2010 01/14/2010 YES
l_new_part_name=Y10M01D14
l_new_hvc=01/14/2010
l_mail_body=Statement Executed:alter table ARUP.PTEST4 split partition pmax  at
(to_date('01/14/2010','mm/dd/yyyy')) into ( partition Y10M01D14 tablespace
MGMT_TABLESPACE initrans 11 storage (freelists 11 freelist groups 7) , partition
pmax) update global indexes
ARUP      PTEST5                        DAILY     01/04/2010 01/06/2010 YES
l_new_part_name=Y10M01D06
l_new_hvc=01/06/2010
l_mail_body=Statement Executed:alter table ARUP.PTEST5 add partition Y10M01D06
values less than  (to_date('01/06/2010','mm/dd/yyyy')) tablespace
MGMT_TABLESPACE initrans 11 storage (freelists 11 freelist groups 7)
ARUP      PTEST6                        DAILY     01/02/2010 01/04/2010 YES
l_new_part_name=Y10M01D04
l_new_hvc=01/04/2010
l_mail_body=Statement Executed:alter table ARUP.PTEST6 add partition Y10M01D04
values less than  (to_date('01/04/2010','mm/dd/yyyy')) tablespace
MGMT_TABLESPACE initrans 11 storage (freelists 11 freelist groups 7)
ARUP      PTEST7                        DAILY     01/02/2010 01/04/2010 YES
l_new_part_name=Y10M01D04
l_new_hvc=01/04/2010
l_mail_body=Statement Executed:alter table ARUP.PTEST7 add partition Y10M01D04
values less than  (to_date('01/04/2010','mm/dd/yyyy')) tablespace
MGMT_TABLESPACE initrans 11 storage (freelists 11 freelist groups 7)

The output is sent to the mail address recorded in the stored procedure - in case of both success and failure. Now all you have to do is to put it in a Scheduler Job and let it run every day. Populate the table PARTITION_CREATE_SCHEDULE as needed and those tables will be automatically added partitions.

Hope you enjoy and find it useful. As always, I will appreciate your feedback.

Tool to Present a Consolidated View of the Status of RMAN Backups

2010-11-09T22:27:00.000-05:00

A very important area of database administration is backups which are essential to the availability of the databases. If you use RMAN to backup databases (as you should be doing), how will you ensure that all the database backups have successfully completed? One way of making sure that occurs is by going into each server and checking the backup log – a task not just tedious but often impractical considering the large number of databases we have. Is there an easy way – via a consolidated output of all databases?

The Tool

Yes, there is an easy way – by checking the catalog, assuming of course that you are using a catalog, as you should be. I developed this tool to extract the information from the catalog for each database. Since the repository may be different for each database being backed up, I used a view to union all the RC_* views from all the repositories on a common schema which I named RMAN_COMMON. Then I wrote a PL/SQL program to pull the report presenting the information on all databases together. Since my objective was to have a consolidated view of all the backups, whether they succeeded or not, I selected from multiple views to provide an indented output in case of failures to identify specifically which component failed. I will illustrate with an output shown below. The tool can be customized for number of days it reports. Here I have chosen for the last 2 days.

SQL> conn rman_common/rman_common@catalogdb
Connected.
SQL> @status

********  RMAN Database Backup Report *********

DB Name  Start Time         End Time           Status   Time Tak Output S Type
-------- ------------------ ------------------ -------- -------- -------- -------
DEMO1    2010-11-08 00:25:1 2010-11-08 00:50:4 COMPLETE 00:25:27    17.99 DISK
DEMO1    2010-11-09 00:25:1 2010-11-09 00:50:3 COMPLETE 00:25:16    17.99 DISK
ECR      2010-11-07 23:12:0 2010-11-07 23:14:2 COMPLETE 00:02:13     5.55 DISK
ECR      2010-11-08 23:12:0 2010-11-08 23:14:3 COMPLETE 00:02:21     5.58 DISK
EMREP    2010-11-08 01:00:2 2010-11-08 01:02:5 COMPLETE 00:02:35   326.07 DISK
EMREP    2010-11-09 01:00:2 2010-11-09 01:02:5 COMPLETE 00:02:28   353.71 DISK
PROPRD   2010-11-07 23:00:1 2010-11-08 00:04:0 COMPLETE 01:03:49    50.48 DISK
PROPRD   2010-11-08 00:04:2 2010-11-08 11:47:1 COMPLETE 11:42:42    27.59
PROPRD   2010-11-08 10:35:3 2010-11-08 11:20:4 COMPLETE 00:45:12    30.00 DISK
PROPRD   2010-11-08 11:28:5 2010-11-08 12:21:2 COMPLETE 00:52:33    30.00 DISK
PROPRD   2010-11-08 12:23:5 2010-11-08 12:38:5 COMPLETE 00:15:00    10.00 DISK
PROPRD   2010-11-08 12:43:3 2010-11-08 12:43:4 COMPLETE 00:00:07   192.00 DISK
PROPRD   2010-11-08 12:46:1 2010-11-08 12:46:2 COMPLETE 00:00:07   224.00 DISK
PROPRD   2010-11-08 12:48:1 2010-11-08 13:14:0 COMPLETE 00:25:50    20.00 DISK
PROPRD   2010-11-08 13:37:3 2010-11-08 13:58:4 COMPLETE 00:21:11    15.00 DISK
PROPRD   2010-11-08 14:00:2 2010-11-08 14:13:5 COMPLETE 00:13:30    10.00 DISK
PROPRD   2010-11-08 14:29:0 2010-11-08 14:29:0 FAILED   00:00:01     0.00
. Operation            Input                Status
. -------------------- -------------------- --------------------
. .                    .                    FAILED
.  Level  Status   Operation            Object Type
.  ------ -------- -------------------- --------------------
.  >      COMPLETE RMAN                 .
.  ->     FAILED   BACKUP               DATAFILE INCR
PROPRD   2010-11-08 23:00:2 2010-11-09 00:21:0 COMPLETE 01:20:43    66.75 DISK
PROPRD   2010-11-09 00:21:2 2010-11-09 14:07:3 COMPLETE 13:46:09    40.25
LIGPRD1  2010-11-08 03:00:0 2010-11-08 03:00:4 COMPLETE 00:00:37     1.74 DISK
LIGPRD1  2010-11-08 03:00:5 2010-11-08 03:04:1 COMPLETE 00:03:12     1.49
LIGPRD1  2010-11-09 03:00:0 2010-11-09 03:00:5 COMPLETE 00:00:45     2.59 DISK
LIGPRD1  2010-11-09 03:01:0 2010-11-09 03:05:1 COMPLETE 00:04:05     1.37
LIGPRD2  2010-11-08 02:00:0 2010-11-08 02:00:3 COMPLETE 00:00:29     1.04 DISK
LIGPRD2  2010-11-08 02:00:4 2010-11-08 02:02:3 COMPLETE 00:01:52     1.34
LIGPRD2  2010-11-09 02:00:0 2010-11-09 02:00:4 COMPLETE 00:00:35     1.67 DISK
LIGPRD2  2010-11-09 02:00:5 2010-11-09 02:03:2 COMPLETE 00:02:26     1.38
SW1      2010-11-08 00:05:0 2010-11-08 00:06:1 COMPLETE 00:01:06   519.17 DISK
SW1      2010-11-09 00:05:0 2010-11-09 00:08:0 COMPLETE 00:03:04     2.01 DISK

PL/SQL procedure successfully completed.

Here you can see the databases in the catalog – PROPRD, LIGPRD1, LIGPRD2 and SW1. The columns – “Start Time”, “End Time” and “Time Taken” – are self-explanatory. The “Output Size” shows the size of the backupset produced. The “Status” column shows the status of the job – the key to this report. If it shows “COMPLETE”, then all was well in the job. If it shows “FAILED” then lines below show what actually failed. For instance you can see on 8^th Nov, incremental backup of one datafile of PROPRD failed. That one definitely needs investigating. You got all that important information in just one report. As you add all the databases into the same catalog, your reports will be more complete and expansive.

Construction

Now that you saw the result of the tool, let’s see the code behind it. First I created a user – RMAN_COMMON:

SQL> grant create session, unlimited tablespace, create view to rman_common identified by rman_common;

We need just three views from the repositories; so this user needs to be granted select privileges on those only. As SYS user, grant these:

grant select on rman_PROPRD.rc_rman_backup_job_details to rman_common;
grant select on rman_LIGPRD11.rc_rman_backup_job_details to rman_common;
grant select on rman_LIGPRD21.rc_rman_backup_job_details to rman_common;
grant select on rman_11g.rc_rman_backup_job_details to rman_common;
--
grant select on rman_PROPRD.rc_rman_backup_subjob_details to rman_common;
grant select on rman_LIGPRD11.rc_rman_backup_subjob_details to rman_common;
grant select on rman_LIGPRD21.rc_rman_backup_subjob_details to rman_common;
grant select on rman_11g.rc_rman_backup_subjob_details to rman_common;
--
grant select on rman_PROPRD.rc_rman_status to rman_common;
grant select on rman_LIGPRD11.rc_rman_status to rman_common;
grant select on rman_LIGPRD21.rc_rman_status to rman_common;
grant select on rman_11g.rc_rman_status to rman_common;

Of course, you will need to customize the above script to add more repositories as you add them.

Next, you will create the views by selecting from the corresponding views from individual repositories. Instead of creating a view with all the columns, I chose only a few columns. This will help us in combining the views from 10g and 11g where the columns could be different.

conn rman_common/rman_common
--
create view rc_rman_backup_job_details
as
select
        db_name,
        start_time,
        end_time,
        status,
        time_taken_display,
        output_bytes_display,
        output_device_type,
        session_key,
        session_recid,
        session_stamp
from rman_PROPRD.rc_rman_backup_job_details
union all
select
        db_name,
        start_time,
        end_time,
        status,
        time_taken_display,
        output_bytes_display,
        output_device_type,
        session_key,
        session_recid,
        session_stamp
from rman_LIGPRD11.rc_rman_backup_job_details
union all
select
        db_name,
        start_time,
        end_time,
        status,
        time_taken_display,
        output_bytes_display,
        output_device_type,
        session_key,
        session_recid,
        session_stamp
from rman_LIGPRD21.rc_rman_backup_job_details
union all
select
        db_name,
        start_time,
        end_time,
        status,
        time_taken_display,
        output_bytes_display,
        output_device_type,
        session_key,
        session_recid,
        session_stamp
from rman_11g.rc_rman_backup_job_details
/
create view rc_rman_backup_subjob_details
as
select
        operation,
        input_type,
        status,
        session_stamp
from rman_PROPRD.rc_rman_backup_subjob_details
union all
select
        operation,
        input_type,
        status,
        session_stamp
from rman_LIGPRD11.rc_rman_backup_subjob_details
union all
select
        operation,
        input_type,
        status,
        input_type,
        status,
        session_stamp
from rman_LIGPRD21.rc_rman_backup_subjob_details
union all
select
        operation,
        input_type,
        status,
        session_stamp
from rman_11g.rc_rman_backup_subjob_details
/
create view rc_rman_status
as
select
        row_level,
        operation,
        object_type,
        status,
        session_key,
        session_recid
from rman_PROPRD.rc_rman_status
union all
select
        row_level,
        operation,
        object_type,
        status,
        session_key,
        session_recid
from rman_LIGPRD11.rc_rman_status
union all
select
        row_level,
        operation,
        object_type,
        status,
        session_key,
        session_recid
        session_key,
        session_recid
from rman_LIGPRD21.rc_rman_status
union all
select
        row_level,
        operation,
        object_type,
        status,
        session_key,
        session_recid
from rman_11g.rc_rman_status
/

Now that the views are in place, you can create the actual tool. Here is the PL/SQL code:

/*---------------------------------------------------------------------------
Name            : RMAN Job Summary Report
Version         : 2.0
Released        : Nov 9th, 2010
Author          : Arup Nanda.
Description     : Creates a report of all backup jobs in a specified number
                  of days. If anything failed, it also shows the sub-job details.
                  Tested on 11g; but will work on 10gR2 as well.
                  Connect to RMAN repository and execute.
                  For number of days, change the l_days constant.
---------------------------------------------------------------------------*/
prompt
prompt ********  RMAN Database Backup Report *********
prompt
set serveroutput on size unlimited
declare
        l_days                  constant number := 2;
        l_place                 varchar2(400);
        l_db_name               rc_rman_backup_job_details.db_name%type;
        l_start_time            varchar2(40);
        l_end_time              varchar2(40);
        l_status                rc_rman_backup_job_details.status%type;
        l_time_taken_display    rc_rman_backup_job_details.time_taken_display%type;
        l_output_bytes_display  rc_rman_backup_job_details.output_bytes_display%type;
        l_output_device_type    rc_rman_backup_job_details.output_device_type%type;
        l_session_key           rc_rman_backup_job_details.session_key%type;
        l_session_recid         rc_rman_backup_job_details.session_recid%type;
        l_session_stamp         rc_rman_backup_job_details.session_stamp%type;

        l_operation             rc_rman_backup_subjob_details.operation%type;
        l_input_type            rc_rman_backup_subjob_details.input_type%type;

        l_command_level         varchar2(9);
        l_object_type           rc_rman_status.object_type%type;
        cursor bjd_cur
        (
                p_days in number
        )
        is
        select
                bjd.db_name,
                to_char(bjd.start_time, 'yyyy-mm-dd hh24:mi:ss'),
                to_char(bjd.end_time, 'yyyy-mm-dd hh24:mi:ss'),
                bjd.status,
                bjd.time_taken_display,
                bjd.output_bytes_display,
                bjd.output_device_type,
                bjd.session_key,
                bjd.session_recid,
                bjd.session_stamp
        from rc_rman_backup_job_details bjd
        where end_time > sysdate - p_days
        order by  bjd.db_name, bjd.start_time;

begin
        l_place := 'Place 100';
        dbms_output.put_line(
                rpad('DB Name',8)
                ||' '||
                rpad('Start Time',18)
                ||' '||
                rpad('End Time',18)
                ||' '||
                rpad('Status',8)
                ||' '||
                rpad('Time Taken',8)
                ||' '||
                rpad('Output Size',8)
                ||' '||
                rpad('Type',8)
        );
        dbms_output.put_line(
                rpad('-',8,'-')
                ||' '||
                rpad('-',18,'-')
                ||' '||
                rpad('-',18,'-')
                ||' '||
                rpad('-',8,'-')
                ||' '||
                rpad('-',8,'-')
                ||' '||
                rpad('-',8,'-')
                ||' '||
                rpad('-',8,'-')
        );
        open bjd_cur (l_days);
        loop
                fetch bjd_cur
                into
                        l_db_name,
                        l_start_time,
                        l_end_time,
                        l_status,
                        l_time_taken_display,
                        l_output_bytes_display,
                        l_output_device_type,
                        l_session_key,
                        l_session_recid,
                        l_session_stamp
                ;
                exit when bjd_cur%notfound;
                dbms_output.put_line(
                        rpad(l_db_name ,8)
                        ||' '||
                        rpad(l_start_time ,18)
                        ||' '||
                        rpad(l_end_time ,18)
                        ||' '||
                        rpad(l_status ,8)
                        ||' '||
                        rpad(l_time_taken_display ,8)
                        ||' '||
                        rpad(l_output_bytes_display ,8)
                        ||' '||
                        rpad(l_output_device_type,8)
                );
                --
                --
                l_place := 'Place 300';
                if (l_status != 'COMPLETED') then
                        for bsjd_rec in (
                        select
                                operation,
                                input_type,
                                status
                        from rc_rman_backup_subjob_details
                        where session_stamp = l_session_stamp
                        ) loop
                        l_place := 'Place 400';
                        dbms_output.put_line(
                                '.'
                                ||' '||
                                rpad('Operation',20)
                                ||' '||
                                rpad('Input',20)
                                ||' '||
                                rpad('Status',20)
                        );
                        dbms_output.put_line(
                                '.'
                                ||' '||
                                rpad('-',20,'-')
                                ||' '||
                                rpad('-',20,'-')
                                ||' '||
                                rpad('-',20,'-')
                        );
                        dbms_output.put_line(
                                '.'
                                ||' '||
                                rpad(nvl(l_operation,'.') ,20)
                                ||' '||
                                rpad(nvl(l_input_type,'.') ,20)
                                ||' '||
                                rpad(nvl(l_status,'.') ,20)
                        );
                        end loop;
                        --
                        l_place := 'Place 500';
                        dbms_output.put_line(
                                '.  '||
                                rpad('Level' ,6)
                                ||' '||
                                rpad('Status' ,8)
                                ||' '||
                                rpad('Operation' ,20)
                                ||' '||
                                rpad('Object Type' ,20)
                        );
                        dbms_output.put_line(
                                '.  '||
                                rpad('-' ,6,'-')
                                ||' '||
                                rpad('-' ,8,'-')
                                ||' '||
                                rpad('-' ,20,'-')
                                ||' '||
                                rpad('-' ,20,'-')
                        );
                        for status_rec in (
                                select
                                        rpad('-', row_level, '-')||'>' command_level,
                                        operation,
                                        object_type,
                                        status
                                from rc_rman_status
                                where session_key = l_session_key
                                order by row_level, session_recid
                        ) loop
                                l_place := 'Place 600';
                                dbms_output.put_line(
                                        '.  '||
                                        rpad(nvl(status_rec.command_level,'.') ,6)
                                        ||' '||
                                        rpad(nvl(status_rec.status,'.') ,8)
                                        ||' '||
                                        rpad(nvl(status_rec.operation,'.') ,20)
                                        ||' '||
                                        rpad(nvl(status_rec.object_type,'.') ,20)
                                );
                        end loop;
                end if;
                end loop;
exception
        when OTHERS then
                dbms_output.put_line(l_place);
                raise;
end;
/

This is the PL/SQL code in the file status.sql. You can adjust the value of the constant l_days to generate the report for as many days as you want. When you run the script it shows the status of the RMAN jobs in last 2 days.

Hope you find this useful. As always, any feedback will be highly appreciated.

Automatic Range Partition Dropping Tool

2010-11-07T11:30:00.000-05:00

You have probably used paritions on your tables, especially period based range partitions on date columns. In some applications older partitions become useless after a specified period of time and you have to drop them. But you have to manually drop them; there is no automatic drop utility. Oracle offers interval partitioning for automatic creation of range partitions; but there is no counterpart in dropping operation. It could be chore, especially when the partitions are defined based on different time intervals on different tables.

I have developed a tool to make this process automatic. The concept of the tool is simple - I use a driver table that stores the preferences such as the partitioning time intervals of individual tables I am interested in and how often then should be purged. The actual tool is a stored procedure that reads this driver table, determines if the oldest partition satisfies the condition of purge and drops it if is it the case. You can put this procedure in a Scheduler job and run it every day. It will compute the partitions automatically is decide on dropping the relevant partitions everyday. It will also update global indexes.

Disclaimer: this is being provided for educational purpose only; use at your own risk. The author assumes absolutely no responsibility for any issues caused by the use of this tool.

First, I will need the driver table:

/* ---------------------------------------------------------------------
Name : partition_drop_schedule
Purpose : To store the retention period of partitions of 
        : indivisual tables. This data is used by partition_drop
        : procedure to automatically drop partitions.
----------------------------------------------------------------------*/
create table partition_drop_schedule
(

OWNER VARCHAR2(30),
   TABLE_NAME VARCHAR2(30),
   PART_SCHEME VARCHAR2(10),
   RETENTION_DURATION NUMBER(5),
   RETENTION_UNIT VARCHAR2(10)
) 
/
comment on table partition_drop_schedule is 'To store the retention period of partitions of individual tables. This data is used by partition_drop procedure to automatically drop partitions.'

comment on column partition_drop_schedule.owner is 'the owner of the table'
/
comment on column partition_drop_schedule.table_name is 'the name of the table'
/
comment on column partition_drop_schedule.part_scheme is 'the partitioning time scheme (DAILY/WEEKLY/MONTHLY/QUARTERLY/HALFYEARLY/YEARLY) of the table'
/
comment on column partition_drop_schedule.RETENTION_DURATION is 'the duration of retention of the partition in number'
/
comment on column partition_drop_schedule.RETENTION_UNIT is 'the unit of duration of retention (DAY/WEEK/MONTH/QUARTER/HALFYEAR/YEAR of the table'
/

Here are some sample inserts into the table:

insert into partition_drop_schedule values
(
    'ARUP','TEST','MONTHLY',12,'MONTH'
)
/

commit

Next comes the stored procedure:

CREATE OR REPLACE procedure SYS.partition_drop
as
 cursor sched_cur is
  select * 
  from partition_drop_schedule
  ;
 l_sched_rec    sched_cur%rowtype;
 l_cutoff_date   date;
 l_hv     date;
 l_sql     varchar2(2000);
 l_dis_cons_sql   varchar2(2000);
 l_ena_cons_sql   varchar2(2000);
 l_drop_sql    varchar2(2000);
 errm     varchar2(2000);
 l_out     varchar2(2000);
 l_full     varchar2(32000);
 l_recipients   varchar2(2000) :=
  'dba.ops@myowncompany.com';
 errc     number(10);
 l_place     varchar2(2);
 l_cons_disabled   boolean := FALSE;
 type l_varchar2_30_ty is table of varchar2(30);
 l_child_owner    l_varchar2_30_ty;
 l_child_table_name  l_varchar2_30_ty;
 l_constraint_name  l_varchar2_30_ty;
 CURSOR cons_cur (
  p_owner in varchar2,
  p_table_name in varchar2
 ) is
  select owner, table_name, constraint_name
  from dba_constraints
  where constraint_type = 'R'
  and status = 'ENABLED'
  and r_constraint_name = (
   select constraint_name
   from dba_constraints
   where owner = p_owner
   and table_name = p_table_name
   and constraint_type = 'P'
  );
begin
 -- if the email server is not set already, set it here
      execute immediate 'alter session set smtp_out_server = ''smtp.myowncompany.com''';
 l_out :=
  rpad('Owner',10)||
  rpad('Table',30)||
  rpad('Retention',10)||
  rpad('Cutoff Dt',10)
  ;
 dbms_output.put_line(l_out);
 l_full := l_out;
 l_out :=
  rpad('-',9,'-')||' '||
  rpad('-',29,'-')||' '||
  rpad('-',9,'-')||' '||
  rpad('-',9,'-')
  ;
 l_full := l_full||chr(12)||l_out;
 dbms_output.put_line(l_out);
 open sched_cur;
 loop
  fetch sched_cur into l_sched_rec;
  exit when sched_cur%notfound;
  l_out := rpad(l_sched_rec.owner,10);
  l_out := l_out||rpad(l_sched_rec.table_name,30);
  l_cutoff_date :=
   case l_sched_rec.PART_SCHEME
    when 'MONTHLY' then
     case l_sched_rec.retention_unit
      when 'MONTH' then
       add_months(sysdate,-1*l_sched_rec.retention_duration)
      else
       null
      end
    when 'DAILY' then
     case l_sched_rec.retention_unit
      when 'DAY' then
       sysdate-l_sched_rec.retention_duration
      else
       null
      end
    when 'HALFYEARLY' then
     case l_sched_rec.retention_unit
      when 'HALFYEAR' then
       add_months(sysdate,-6*l_sched_rec.retention_duration)
      when 'MONTH' then
       add_months(sysdate,-1*l_sched_rec.retention_duration)
      else
       null
      end
    when 'YEARLY' then
     null
    when 'WEEKLY' then
     null
    when 'QUARTERLY' then
     case l_sched_rec.retention_unit
      when 'QUARTER' then
       add_months(sysdate,-3*l_sched_rec.retention_duration)
      when 'MONTH' then
       add_months(sysdate,-1*l_sched_rec.retention_duration)
      else
       null
      end
   end;
  l_out := l_out||rpad(l_sched_rec.retention_duration||' '||l_sched_rec.retention_unit,10);
  l_out := l_out||rpad(l_cutoff_date,10);
  dbms_output.put_line(l_out);
  l_full := l_full||chr(12)||l_out;
  for part_cur in
  (
   select partition_name, high_value
   from dba_tab_partitions
   where table_owner = l_sched_rec.owner
   and table_name = l_sched_rec.table_name
   order by partition_position
  ) loop
   -- dbms_output.put_line('l_sched_rec.owner='||l_sched_rec.owner);
   -- dbms_output.put_line('l_sched_rec.table_name='||l_sched_rec.table_name);
   if part_cur.high_value != 'MAXVALUE' then
    execute immediate 'select '||part_cur.high_value||
     ' from dual' into l_hv;
    if l_cutoff_date > l_hv then
     l_out := 'Partition '||
      part_cur.partition_name||
      ' with high value '||
      l_hv||
      ' to be dropped ...';
     dbms_output.put_line(l_out);
     l_full := l_full||chr(12)||l_out;
     l_drop_sql := 'alter table '||l_sched_rec.owner||'.'||
     l_sched_rec.table_name||
     ' drop partition '||part_cur.partition_name||
     ' update global indexes';
     dbms_output.put_line('l_drop_sql='||l_drop_sql);
     begin
      --
      -- Disable the FKs 
      --
      l_cons_disabled := FALSE;
      open cons_cur (l_sched_rec.owner, l_sched_rec.table_name);
      fetch cons_cur
      bulk collect
      into l_child_owner, l_child_table_name, l_constraint_name;
      close cons_cur;
      if nvl(l_child_owner.COUNT,0) > 0 then
       l_cons_disabled := TRUE;
       for ctr in l_child_owner.FIRST .. l_child_owner.LAST loop
        dbms_output.put_line('l_child_owner='||l_child_owner(ctr));
        dbms_output.put_line('l_child_table_name='||l_child_table_name(ctr));
        dbms_output.put_line('l_constraint_name='||l_constraint_name(ctr));
        l_dis_cons_sql := 'alter table '||l_child_owner(ctr)||'.'||
         l_child_table_name(ctr)||' disable constraint '||
         l_constraint_name(ctr);
        dbms_output.put_line('l_dis_cons_sql='||l_dis_cons_sql);
        l_sql := l_dis_cons_sql;
        execute immediate l_sql;
       end loop;
      end if;
      l_sql := l_drop_sql;
      execute immediate l_sql;
      --
      -- Enable the FKs
      --
      if (l_cons_disabled) then
       for ctr in l_child_owner.FIRST .. l_child_owner.LAST loop
        l_dis_cons_sql := 'alter table '||l_child_owner(ctr)||'.'||
         l_child_table_name(ctr)||' enable novalidate constraint '||
         l_constraint_name(ctr);
        dbms_output.put_line('l_dis_cons_sql='||l_dis_cons_sql);
        l_sql := l_dis_cons_sql;
        execute immediate l_sql;
       end loop;
      end if;
      l_out := l_out||'DONE';
      dbms_output.put_line(l_sql);
      dbms_output.put_line(l_out);
      l_full := l_full||'DONE';
      l_full := l_full||chr(12)||l_sql;
     exception
      when others then
       errm := SQLERRM;
       errc := SQLCODE;
       l_out := l_out||'FAILED';
       dbms_output.put_line(l_sql);
       dbms_output.put_line('Place:'||l_place);
       l_full := l_full||'FAILED';
       l_full := l_full||chr(12)||chr(12)||l_sql;
     end;
    end if;
   end if;
  end loop;
 end loop;
 close sched_cur;
 dbms_output.put_line(l_full);
 utl_mail.send(
  'oracle@prodb1',l_recipients,
  null, null,
  'Succeeded: PROPRD Partition Drop',
  l_full
 );
exception
 when OTHERS then
  errm := SQLERRM;
  utl_mail.send(
   'oracle@prodb1',l_recipients,
   null, null,
   'Failed: PROPRD Partition Drop',
   l_full
  );
  raise_application_error(-20001,errm);
end;
/
show error

Now that the procedure is complete, you should do a quick test to make sure the procedure is working as expected. Update the record in the table PARTITION_DROP_SCHEDULE to reflect 1 month retention and execute this procedure. It should drop the partition and maintain the global indexes. You can confirm the actions by:

(1) checking the DBA_TAB_PARTITIONS to see that the partition is indeed gone
(2) checking the DBA_INDEXES to see that the global index is indeed in USABLE state

If all looks well, you should just put it in a DBMS_SCHEDULER job and set it to run every day at a time that will be considered off peak, e.g. 3 AM.

Important Considerations

This is not intended for mass partition drops. For instance if you are planning to drop 100 partitions of a table, do not use it. This tool will update global indexes for each partition - a process highly inefficient for large numbers of partitions. You are much better off making the global indexes unusable, dropping all the partitions and then rebuilding global indexes. That will be much faster.
At the time of the drop, the session will get an exclusive lock on the table for a fraction of a second. So you will see a slight performance degradation for that instant only.
Never use this tool without adequately testing. Partition drop is a DDL operation - an irreversible process.

As always, I would appreciate comments and suggestions from you on this and any other blog entries. Thanks for reading and happy tooling.

Keynote Presentation at NOUG Oct 2010

2010-10-22T11:33:00.000-04:00

Thank you all who came to watch my keynote presentation - "Real World DBA Best Practices" - for Northeast Oracle User Group in Boston, 21st October, 2010.

You can download the presentation here. Before downloading, please let me reiterate what I mentioned during the meeting - a best practice is not one if you do not understand the reason behind it and do not understand the applicability to your specific situation. I was not selling a product or service nor was I asking you to blindly follow it. All I wanted from you was to consider the points.

The Business of Religion

2010-10-04T00:25:00.000-04:00

Most of you who read my blog will probably be shocked to see a topic like this. I work with Oracle technologies and my blogs have been predominantly about that (well, actually always about that). But I am also a human being living in societies and there are other thoughts that cross my mind. So here is something very, very different. This is not about Oracle, or IT, or even working in general. Please stop reading if you are not interested.

One of the activities I perform outside of my work is mentoring. These are not just rookies trying to go up in their careers; but accomplished professional (some more accomplished than me) trying to get some sense from a different perspective and I am willing to help. One such mentee (I will not name him to protect his privacy) mentioned something about religion that got me thinking. It was about a debate on religion and the threats on Americans travelling internationally and some other assorted topics. As a rule, I do not engage in debates about religion. Don't get me wrong, I love to debate passionately on topics I find interesting or important; but with some exceptions - religion being one of them.

It's rather sad to see how some reasonably intelligent or outwardly sound people justify the killing of innocent people as a manifestation of their religious obligations. One could argue that that act by itself is not religious or even spiritual; it's fanatical bordering on lunacy. But from the perspective of the perpetrators, that's what it is - sanctioned religious beliefs. Religion is a very dangerous double edged sword. On one hand it brings discipline, moral values and, for the lack of a better term - "humanity" to humans. Consider this - why would you not steal from thy neighbor? It makes your act profitable and your actions immensely efficient (a lot of gain from a small effort); but you won't; because it's against established morality. Belief system - whether in the form of religion or otherwise, pours the very foundation of morality - a fact most of us probably do not realize. It differentiates us from other animals.

But the root of problem is that there is no boundary. Where should the belief system stop? Religion is after all a set of rituals and behavioral standards formed by human beings (although in some cases presumed or claimed vetted by supreme beings). Its specific actions are defined by the leaders, like any other group, but with an important difference. Belief systems adapt to situations - making the process forming the set of acceptable activities of religion highly fluid and devoid of specific direction. Some adapt it to their interpretation, in so much so, that they actually believe in their interpretation. Contrary to popular belief, these people may not be illiterate goat herding nomads; they may be at the top echelons of established societies, with a firm belief in their own direction of the religious activities.

And that makes the business of religion highly dangerous. While on one hand we cherish the extremely positive role religion plays in forming societies, we also witness the catastrophic consequence of the abuse of religion. That's why I avoid debating about religion in any form - there is a fine line between being spiritual and fanatical - and with each side deciding where the line should be. The debate has at least one attribute - logic and reason take a backseat to emotion and sentiment.

Presentation at New York Oracle User Group

2010-09-28T23:22:00.000-04:00

Thank you for all those who attended the session - Analyzing Application Performance in RAC - at New York Oracle User Group's Fall Meeting in New York City.

You can download the slides from here. And, please watch out for the article on the same topic in NYOUG Tech Journal.

OOW 2010 Session Application Profiling in RAC

2010-09-24T08:01:00.000-04:00

Thank you all who attended my session at OOW. It was the last day of the conference and I appreciate your taking the time. Here are the slides.

Thank you for attending and hope you will find it useful. Please look my other blog entry where I described the tool I built as well.

OOW 2010 Session Stats with Confidence

2010-09-24T07:50:00.000-04:00

Thank you very much to all those who attended my session "Stats with Confidence". Unfortunately I was delayed by the keynote running late. With the big party coming up, I appreciate the spirit of those brave souls who stayed back. The late start didn't allow me to show the demo completely. But here are the scripts; hope you will be able to follow it along and run it on your own infrastructure.

It contains the presentation as well. Thanks for attending and hope you will find it useful.

A Tool to Enable Stats Collection for Future Sessions for Application Profiling

2010-09-12T21:48:00.001-04:00

The other day I was putting together my presentation for Oracle Open World on Application Profiling in RAC. I was going to describe a methodology for putting a face to an app by measuring how it behaves in a database – a sort of a signature of that application. I was going to use the now-ubiquitous 10046 trace for wait events and other activities inside the database. For resource consumption such as redo generated, logical I/Os, etc., I used the v$sesstat; but then I was stuck. How would I collect the stats of a session when the session has not even started and I don’t know the SID. That problem led to the development of this tool where the stats of a future session can be recorded based on some identifying factors such as username, module, etc. Hope this helps in your performance management efforts.

The Problem

Suppose you want to find out the resource consumed by a session. The resources could be redo generation, CPU used, logical I/O, undo records generated – the list is endless. This is required for a lot of things. Consider a case where you want to find out which apps are generating the most redo; you would issue a query like this:

select sid, value
from v$sesstat s, v$statname n
where n.statistic# = s.statistic#
and n.name = 'redo size'
/

The value column will show the redo generated. From the SID you can identify the session. Your next stop is v$session to get the other relevant information such as username, module, authentication scheme, etc. Problem solved, right?

Not so fast. Look at the above query; it selects from v$sesstat. When the session is disconnected, the stats disappear, making the entries for that session go from v$sesstat. If you run the query, you will not find these sessions. You have to constantly select from the v$sesstat view to capture the stats of the sessions hoping that you would capture the stats before the session disconnects. But it will be not be guaranteed. Some short sessions will be missed in between collection samples. Even if you are lucky to capture some stats of a short session, the other relevant information from v$session will be gone.

Oracle provides a package dbms_monitor, where a procedure named client_id_stat_enable allows you to enable stats collection on a future session where the client_id matches a specific value, e.g. CLIENT1. Here is an example:

execute dbms_monitor.client_id_stat_enable('CLIENT1');

However there are three issues:

(1) It collects only about 27 stats, out of 400+

(2) It offers only three choices for selecting sessions – client_id, module_name and service_name.

(3) It aggregate them, sums up all stats for a specific client_id. That is pretty much useless without a detailed session level.

So, in short, I didn’t have a readily available solution.

Solution

Well, necessity is the mother of invention. When you can’t find a decent tool; you build it; and so did I. I built this tool to capture the stats. This is version 1 of the tool. It has some limitations, as shown at the end. These limitations do not apply to all situations; so the tool may be useful in a majority of the cases. Later I will expand the tool to overcome these limitations.

Concept

The fundamental problem, as you recall, is not the dearth of data (v$sesstat has plenty); it’s the sessions in the future. To capture those sessions, the tool relies on a post-logon database trigger to capture the values.

The second problem was persistence. V$SESSTAT is a dynamic performance view, which means the records of the session will be gone when the session disappears. So, the tool relies on a table to store the data.

The third problem is the getting the values at the very end of the session. The difference between the values captured at the end and beginning of the session are the stats. To capture the values at the very end; not anytime before, the tool relies on a pre-logoff database trigger.

The fourth challenge is identification of sessions. SID of a session is not unique; it can be reused for a new session; it will definitely be reused when the database is recycled. So, the tool uses a column named CAPTURE_ID, a sequentially incremented number for each capture. Since we capture once at the beginning and then at the end, I must use the same capture_id. I use a package variable to store that capture_Id.

Finally, the tool allows you to enable stats collections based on some session attributes such as username, client_id, module, service_name, etc. For instance you may want to enable stats for any session where the username = ‘SCOTT’ or where the os_user is ‘ananda’, etc. These preferences are stored in a table reserved for that purpose.

Construction

Now that you understand how the tool is structured, let me show the actual code and scripts to create the tool.

(1) First, we should create the table that holds the preferences. Let’s call this table RECSTATS_ENABLED. This table holds all the different sessions attributes (ip address, username, module, etc.) that can enable stats collection in a session.

CREATE TABLE SYS.RECSTATS_ENABLED
(
  SESSION_ATTRIBUTE  VARCHAR2(200 BYTE),
  ATTRIBUTE_VALUE    VARCHAR2(2000 BYTE)
)
/

If you want to enable stats collection of a session based on a session attribute, insert a record into this table with the session attribute and the value. Here are some examples. I want to collect stats on all sessions where client_info matches ‘MY_CLIENT_INFO1’. You would insert a record like this:

insert into recstats_enabled values ('CLIENT_INFO','MY_CLIENT_INFO1');

Here are some more examples. All sessions where ACTION is ‘MY_ACTION1’:

insert into recstats_enabled values ('ACTION','MY_ACTION1');

Those of user SCOTT:

insert into recstats_enabled values ('SESSION_USER','SCOTT')

Those with service name APP:

insert into recstats_enabled values ('SERVICE_NAME','APP')

You can insert as many preferences as you want. You can even insert multiple values of a specific attribute. For instance, to enable stats on sessions with service names APP1 and APP2, insert two records.

Important: the session attribute names follow the naming convention of the USERENV context used in SYS_CONTEXT function.

(2) Next, we will create a table to hold the statistics

CREATE TABLE SYS.RECSTATS
(
  CAPTURE_ID            NUMBER,
  CAPTURE_POINT         VARCHAR2(10 BYTE),
  SID                   NUMBER,
  SERIAL#               NUMBER,
  ACTION                VARCHAR2(2000 BYTE),
  CLIENT_DENTIFIER      VARCHAR2(2000 BYTE),
  CLIENT_INFO           VARCHAR2(2000 BYTE),
  CURRENT_EDITION_NAME  VARCHAR2(2000 BYTE),
  CURRENT_SCHEMA        VARCHAR2(2000 BYTE),
  CURRENT_USER          VARCHAR2(2000 BYTE),
  DATABASE_ROLE         VARCHAR2(2000 BYTE),
  HOST                  VARCHAR2(2000 BYTE),
  IDENTIFICATION_TYPE   VARCHAR2(2000 BYTE),
  IP_ADDRESS            VARCHAR2(2000 BYTE),
  ISDBA                 VARCHAR2(2000 BYTE),
  MODULE                VARCHAR2(2000 BYTE),
  OS_USER               VARCHAR2(2000 BYTE),
  SERVICE_NAME          VARCHAR2(2000 BYTE),
  SESSION_USER          VARCHAR2(2000 BYTE),
  TERMINAL              VARCHAR2(2000 BYTE),
  STATISTIC_NAME        VARCHAR2(2000 BYTE),
  STATISTIC_VALUE       NUMBER;
)
TABLESPACE USERS

Note, I used the tablespace USERS; because I don’t want this table, which can potentially grow to huge size, to overwhelm the system tablespace. The STATISTIC_NAME and STATISTIC_VALUE columns record the stats collected. The other columns record the other relevant data from the sessions. All the attributes here have been shown with VARCHAR2(2000) for simplicity; of course they don’t need that much of space. In the future versions, I will put a more meaningful limit; but 2000 does not hurt as it is varchar2.

The capture point will show when the values were captured – START or END of the session.

(3) We will also need a sequence to identify the sessions. Each session will have 400+ stats; we will have a set at the end and once at the beginning. We could choose SID as an identifier; but SIDs could be reused. So, we need something that is truly unique – a sequence number. This will be recorded in the CAPTURE_ID column in the stats table.

SQL> create sequence seq_recstats;

(4) To store the capture ID when the post-logon trigger is fired, to be used inside the pre-logoff trigger, we must use a variable that would be visible to entire session. A package variable is the best for that.

create or replace package pkg_recstats
is
   g_recstats_id number;
end;

(5) Finally, we will go on to the meat of the tool – the triggers. First, the post-logon trigger to capture the stats in the beginning of the session:

CREATE OR REPLACE TRIGGER SYS.tr_post_logon_recstats
after logon on database
declare
 l_stmt varchar2(32000);
 l_attr_val recstats_enabled.attribute_value%TYPE;
 l_capture_point recstats.capture_point%type := 'START';
 l_matched boolean := FALSE;
begin
 pkg_recstats.g_recstats_id := null;
 for r in (
  select session_attribute, attribute_value
  from recstats_enabled
  order by session_attribute
 )
 loop
  exit when l_matched;
            -- we select the userenv; but the null values may cause
            -- problems in matching; so let’s use a value for NVL
            -- that will never be used - !_!_!
  l_stmt := 'select nvl(sys_context(''USERENV'','''||
   r.session_attribute||'''),''!_!_!_!'') from dual';
  execute immediate l_stmt into l_attr_val;
  if l_attr_val = r.attribute_value then
   -- match; we should record the stats
   -- and exit the loop; since stats should
   -- be recorded only for one match.
   l_matched := TRUE;
   select seq_recstats.nextval
   into pkg_recstats.g_recstats_id
   from dual;
   insert into recstats
   select
    pkg_recstats.g_recstats_id,
    l_capture_point,
    sys_context('USERENV','SID'),
    null,
    sys_context('USERENV','ACTION'),
    sys_context('USERENV','CLIENT_IDENTIFIER'),
    sys_context('USERENV','CLIENT_INFO'),
    sys_context('USERENV','CURRENT_EDITION_NAME'),
    sys_context('USERENV','CURRENT_SCHEMA'),
    sys_context('USERENV','CURRENT_USER'),
    sys_context('USERENV','DATABASE_ROLE'),
    sys_context('USERENV','HOST'),
    sys_context('USERENV','IDENTIFICATION_TYPE'),
    sys_context('USERENV','IP_ADDRESS'),
    sys_context('USERENV','ISDBA'),
    sys_context('USERENV','MODULE'),
    sys_context('USERENV','OS_USER'),
    sys_context('USERENV','SERVICE_NAME'),
    sys_context('USERENV','SESSION_USER'),
    sys_context('USERENV','TERMINAL'),
    n.name,
    s.value
   from v$mystat s, v$statname n
   where s.statistic# = n.statistic#;
  end if;
 end loop;
end;

The code is self explanatory. I have provided more explanation as comments where needed.

(6) Next, the pre-logoff trigger to capture the stats at the end of the session:

CREATE OR REPLACE TRIGGER SYS.tr_pre_logoff_recstats
before logoff on database
declare
 l_capture_point recstats.capture_point%type := 'END';
begin
 if (pkg_recstats.g_recstats_id is not null) then
  insert into recstats
  select
   pkg_recstats.g_recstats_id,
   l_capture_point,
   sys_context('USERENV','SID'),
   null,
   sys_context('USERENV','ACTION'),
   sys_context('USERENV','CLIENT_IDENTIFIER'),
   sys_context('USERENV','CLIENT_INFO'),
   sys_context('USERENV','CURRENT_EDITION_NAME'),
   sys_context('USERENV','CURRENT_SCHEMA'),
   sys_context('USERENV','CURRENT_USER'),
   sys_context('USERENV','DATABASE_ROLE'),
   sys_context('USERENV','HOST'),
   sys_context('USERENV','IDENTIFICATION_TYPE'),
   sys_context('USERENV','IP_ADDRESS'),
   sys_context('USERENV','ISDBA'),
   sys_context('USERENV','MODULE'),
   sys_context('USERENV','OS_USER'),
   sys_context('USERENV','SERVICE_NAME'),
   sys_context('USERENV','SESSION_USER'),
   sys_context('USERENV','TERMINAL'),
   n.name,
   s.value
  from v$mystat s, v$statname n
  where s.statistic# = n.statistic#;
  commit;
 end if;
end;
/

Again the code is self explanatory. We capture the stats only of the global capture ID has been set by the post-logoff trigger. If we didn’t do that all the sessions would have started recording stats at their completion.

Execution

Now that the setup is complete, let’s perform a test by connecting as a user with the service name APP:

SQL> connect arup/arup@app

In this session, perform some actions that will generate a lot of activity. The following SQL will do nicely:

SQL> create table t as select * from all_objects;

SQL> exit

Now check the RECSTATS table to see the stats on this catured_id, which happens to be 1330.

col name format a60
col value format 999,999,999
select a.statistic_name name, b.statistic_value - a.statistic_value value
from recstats a, recstats b
where a.capture_id = 1330
and a.capture_id = b.capture_id
and a.statistic_name = b.statistic_name
and a.capture_point = 'START'
and b.capture_point = 'END'
and (b.statistic_value - a.statistic_value) != 0
order by 2
/

Here is the output:

NAME                                                                VALUE
------------------------------------------------------------ ------------
workarea memory allocated                                              -2
change write time                                                       1
parse time cpu                                                          1
table scans (long tables)                                               1
cursor authentications                                                  1
sorts (memory)                                                          1
user commits                                                            2
opened cursors current                                                  2
IMU Flushes                                                             2
index scans kdiixs1                                                     2
parse count (hard)                                                      2
workarea executions - optimal                                           2
redo synch writes                                                       2
redo synch time                                                         3
rows fetched via callback                                               5
table fetch by rowid                                                    5
parse time elapsed                                                      5
recursive cpu usage                                                     8
switch current to new buffer                                           10
cluster key scan block gets                                            10
cluster key scans                                                      10
deferred (CURRENT) block cleanout applications                         10
Heap Segment Array Updates                                             10
table scans (short tables)                                             12
messages sent                                                          13
index fetch by key                                                     15
physical read total multi block requests                               15
SQL*Net roundtrips to/from client                                      18
session cursor cache hits                                              19
session cursor cache count                                             19
user calls                                                             25
CPU used by this session                                               28
CPU used when call started                                             29
buffer is not pinned count                                             33
execute count                                                          34
parse count (total)                                                    35
opened cursors cumulative                                              36
physical read total IO requests                                        39
physical read IO requests                                              39
calls to get snapshot scn: kcmgss                                      45
non-idle wait count                                                    67
user I/O wait time                                                    116
non-idle wait time                                                    120
redo ordering marks                                                   120
calls to kcmgas                                                       143
enqueue releases                                                      144
enqueue requests                                                      144
DB time                                                               149
hot buffers moved to head of LRU                                      270
recursive calls                                                       349
active txn count during cleanout                                      842
cleanout - number of ktugct calls                                     842
consistent gets - examination                                         879
IMU undo allocation size                                              968
physical reads cache prefetch                                         997
physical reads                                                      1,036
physical reads cache                                                1,036
table scan blocks gotten                                            1,048
commit cleanouts                                                    1,048
commit cleanouts successfully completed                             1,048
no work - consistent read gets                                      1,060
redo subscn max counts                                              1,124
Heap Segment Array Inserts                                          1,905
calls to kcmgcs                                                     2,149
consistent gets from cache (fastpath)                               2,153
free buffer requested                                               2,182
free buffer inspected                                               2,244
HSC Heap Segment Block Changes                                      2,519
db block gets from cache (fastpath)                                 2,522
consistent gets                                                     3,067
consistent gets from cache                                          3,067
bytes received via SQL*Net from client                              3,284
bytes sent via SQL*Net to client                                    5,589
redo entries                                                        6,448
db block changes                                                    9,150
db block gets                                                      10,194
db block gets from cache                                           10,194
session logical reads                                              13,261
IMU Redo allocation size                                           16,076
table scan rows gotten                                             72,291
session uga memory                                                 88,264
session pga memory                                                131,072
session uga memory max                                            168,956
undo change vector size                                           318,640
session pga memory max                                            589,824
physical read total bytes                                       8,486,912
cell physical IO interconnect bytes                             8,486,912
physical read bytes                                             8,486,912
redo size                                                       8,677,104

This clearly shows you all the stats of that session. Of course the table recorded all other details of the session as well – such as username, client_id, etc., which are useful later for more detailed analysis. You can perform aggregations as well now. Here is an example of the stats collected for redo size:

select session_user, sum(STATISTIC_VALUE) STVAL
from recstats
where STATISTIC_NAME = 'redo size'
group by session_user
/

Output:

SESSION_USER STVAL
------------ ---------
ARUP            278616
APEX           4589343
… and so on …

You can disassemble the aggregates to several attributes as well. Here is an example where you want to find out the redo generated from different users coming from different client machines

select session_user, host, sum(STATISTIC_VALUE) stval
from recstats
where STATISTIC_NAME = 'redo size'
group by session_user, host
/

Output:

SESSION_USER HOST          STVAL
------------ ----------- -------
ARUP         oradba2       12356
ARUP         oradba1      264567
APEX         oradba2       34567
… and so on …

Granularity like this shows you how the application from different client servers helped; not just usernames.

Limitations

As I mentioned, there are some limitations you should be aware of. I will address them in the next iterations of the tool. These are not serious and applicable in only certain cases. As long as you don’t encounter that case, you should be fine.

(1) The logoff trigger does not fire when the user exits from the session ungracefully, such as closing down the SQL*Plus window, or closing the program before exiting. In such cases the stats at the end of the session will not be recorded. In most application infrastructure it does not happen; but it could happen for adhoc user sessions such as people connecting through TOAD.

(2) The session attributes such as module, client_id and action can be altered within the session. If that is the case, this tool does not record that fact since there is no triggering event. The logoff trigger records the module, action and client_id set at that time. These attributes are not usually changed in application code; so it may not apply to your case.

(3) Parallel Query sessions will have a special issue since there will be no logoff trigger. So in case of parallel queries, you will not see any differential stats. If you don’t use PQ, as most OLTP applications do, you will not be affected.

(4) If the session just sits there without disconnecting, the logoff trigger will never fire and the stats will never be captured. Of course, it will be eventually captured when the session exits.

Once again, these limitations apply only to certain occasions. As long as you are aware of these caveats, you will be able to use this tool to profile many of your applications.

Happy Profiling!