APEX tabs

We have been documenting our systems flow using Oracle APEX. It is a really user friendly, declarative programming environment. As an old 3 GL guy, however, I find this difficult at times.

I can't tell you how many times I want to go into TextPad, or vi, and just start typing a jsp page for needed user interface functionality. On this project, however, I decided to move into the 21st century and use something more...ahem...productive.

In all fairness, it is a good tool, and in general very productive. However, the tab stuff is difficult to a mind boggling degree. If you have problems, I direct you to the single best resource I found on the web for a dummy proof guide to tabs on your APEX pages.

http://www.instantaccess.nl/wornmouse/Tabs-with-Oracle-Apex.pdf

This guy did a great job with it.

Who is generating all the redo?

Recently I was tasked with answering why we generated over 500GB of redo in a little over one day, when we normally generate an average of 100GB. We are in the midst of building out a remote data center which will house a RAC protected by Dataguard. Our initial bandwidth to send redo from the production database is severely limited based on project mismanagement, so for a few months we are stuck with a link that will allow a maximum of 400GB of throughput on this link daily.

Since 500GB are more than 400GB, we needed to understand who had generated all of this redo, and whether it may reoccur. Of course, we found that this was done over two months ago! I immediately mentioned that we would have to utilize the Oracle AWR to tell us who and/or what had done this. Thankfully, we retain 90 days of AWR with half hour snapshots.

Confidently, I strode back to my desk and begin to crack through the DBA_HIST views. To my surprise, I found no statistic that would tell me who was plowing through our redo logs like a creature in the movie "Tremors".

I did find a way to roughly identify this information, however, that I hope will be beneficial to you. I first checked DBA_HIST_SQL_STAT for any statement that had a far greater number of executions for the period in question, than they did on average.

I found none.

I then looked into the dba_hist_seg_stat view, and found the PHYSICAL_WRITES_DELTA column. Ah hah! I had my answer. To my dismay, I found that this didn’t have the information, either.

After mulling around for another hour or so, I went back to the dba_hist_seg_stat view. It was then I noticed the db_block_changes_delta column. I then identified the segment that experienced the most changes during the period, using what is below…

select to_char(begin_interval_time,'YYYY_MM_DD HH24:MI') snap_time,
dhsso.object_name,
sum(db_block_changes_delta)
from dba_hist_seg_stat dhss,
dba_hist_seg_stat_obj dhsso,
dba_hist_snapshot dhs
where dhs.snap_id = dhss.snap_id
and dhs.instance_number = dhss.instance_number
and dhss.obj# = dhsso.obj#
and dhss.dataobj# = dhsso.dataobj#
and begin_interval_time
between to_date('2008_05_02 12','YYYY_MM_DD HH24')
and to_date('2008_05_04 12','YYYY_MM_DD HH24')
group by to_char(begin_interval_time,'YYYY_MM_DD HH24:MI')

The answer immediately jumped out at me. It was a table that I recognized, but I didn’t know why it would bubble to the top of the list.

To confirm what I thought, I then ran the following query to examine the changes during the period to what they were historically.

select to_char(begin_interval_time,'YYYY_MM_DD HH24:MI') snap_time,
sum(db_block_changes_delta)
from dba_hist_seg_stat dhss,
dba_hist_seg_stat_obj dhsso,
dba_hist_snapshot dhs
where dhs.snap_id = dhss.snap_id
and dhs.instance_number = dhss.instance_number
and dhss.obj# = dhsso.obj#
and dhss.dataobj# = dhsso.dataobj#
and dhsso.object_name = 'OT_PENDING'
group by to_char(begin_interval_time,'YYYY_MM_DD HH24:MI')

I found that 90% of the block changes over a three month period *all* occurred in the day and half window I was examining. Now we were really on to something!

I gathered all SQL statements that were run during the period that referenced the table, using what is below…

select to_char(begin_interval_time,'YYYY_MM_DD HH24:MI'),
dbms_lob.substr(sql_text,4000,1),
dhss.instance_number,
dhss.sql_id,executions_delta,rows_processed_delta
from dba_hist_sqlstat dhss,
dba_hist_snapshot dhs,
dba_hist_sqltext dhst
where upper(dhst.sql_text) like '%OT_PENDING%'
and dhss.snap_id=dhs.snap_id
and dhss.instance_Number=dhs.instance_number
and dhss.sql_id = dhst.sql_id

I found a statement that was being executed an average number of times, relatively speaking. However, it was updating millions of rows, whereas it normally would update hundreds, if that.

I took the SQL_ID for the statement and searched through DBA_HIST_ACTIVE_SESS_HISTORY, using what is below...

select instance_number, to_char(sample_time,'yyyy_mm_dd hh24:mi:ss'),
user_id,
program
from dba_hist_active_sess_history
where sql_id = 'bxjz5d4p6x02u'
and snap_id between 43212 and 43220

I found that a SQL*PLUS session from a development server was executing the statement, and the execution times exactly mirrored our jump in redo generation. Now I won’t even go into the lack of processes in this shop and how often developers directly access this database, as that is a topic for a different post.

However, the information for which we were searching had been found!

Below is the output of a test case for Fast Connection Failover. Maybe it is just me, but I can never seem to find "soup to nuts" examples of this type of thing.

The runOracleConnMgrFailover java class does the following (let me know if you want the source code posted):

1. Create a pool of 30 cached connections (OracleDataSource), with FCF enabled. These are connected to a two node RAC, linux1 and linux4.
   
2. Create a threaded onsSubscriber objects that will post to standard output all events received from ONS.
3. Sleep for five minutes, during which time we bounce one of the instances.
1. At this time, we should have the connections equally distributed (I verified this but didn't show it) between linux1 and linux4.
4. After the five minutes, 20 connections are created from the pool, and the instance on which they live is shown.

I chose five minutes because I wrote a simple socket server that got messages from ONS on the RAC. It appears as though the ONS software will post clients (the onsSubscriber class Oracle provides for testing ignores these messages) every two minutes (actually slightly less, about 1:58).

This gave me time to shutdown one of the instances in under two minutes and see if the cached connections reconnected to the surviving instance (as Oracle claims will happen).

While testing this, it occurred to me that ONS posts the clients when an instance dies, while not immediately, close to it!

As such, I changed five minutes to 30 seconds, and I found the connections were all on the surviving node :)

Note that the session were "stateless", in other words, they weren't doing anything when failed over. As a matter of fact, they were not "owned" by any "application" connection request until I began asking for 20 connections five minutes later.


linux3:oracle:dummy:/home/oracle>java runOracleConnMgrFailover 20
created connection manager, you can shutdown...
ONC subscriber starting
** HA event received -- Printing header:
Notification Type: database/event/service
Affected Components: null
Affected Nodes: null
Delivery Time: 1203528172772
Generating Component: database/rac/service
Generating Node: linux1
Generating Process: linux1:16358
Notification ID: linux1:1635803
Notification Creation Time:1203524376
Cluster ID: databaseClusterId
Cluster Name: databaseClusterName
Instance ID: databaseInstanceId
Instance Name: databaseInstanceName
Local Only Flag: FALSE
Cluster Only Flag: FALSE
Body: [B@10da5eb
** Body length = 97
** Event type = database/event/service
Sleep and retry.
ONC subscriber starting
ONC subscriber starting
** HA event received -- Printing header:
Notification Type: database/event/service
Affected Components: null
Affected Nodes: null
Delivery Time: 1203528174069
Generating Component: database/rac/service
Generating Node: linux1
Generating Process: linux1:16358
Notification ID: linux1:1635804
Notification Creation Time:1203524378
Cluster ID: databaseClusterId
Cluster Name: databaseClusterName
Instance ID: databaseInstanceId
Instance Name: databaseInstanceName
Local Only Flag: FALSE
Cluster Only Flag: FALSE
Body: [B@e45076
** Body length = 103
** Event type = database/event/service
Sleep and retry.
ONC subscriber starting
ONC subscriber starting
** HA event received -- Printing header:
Notification Type: database/event/service
Affected Components: null
Affected Nodes: null
Delivery Time: 1203528175107
Generating Component: database/rac/service
Generating Node: linux1
Generating Process: linux1:30339
Notification ID: linux1:3033901
Notification Creation Time:1203524379
Cluster ID: databaseClusterId
Cluster Name: databaseClusterName
Instance ID: databaseInstanceId
Instance Name: databaseInstanceName
Local Only Flag: FALSE
Cluster Only Flag: FALSE
Body: [B@15e9756
** Body length = 113
** Event type = database/event/service
Sleep and retry.
ONC subscriber starting
SID 147 started on linux4
SID 135 started on linux4
SID 152 started on linux4
SID 126 started on linux4
SID 120 started on linux4
SID 115 started on linux4
SID 133 started on linux4
SID 149 started on linux4
SID 128 started on linux4
SID 118 started on linux4
SID 122 started on linux4
SID 148 started on linux4
SID 124 started on linux4
SID 119 started on linux4
SID 132 started on linux4
SID 125 started on linux4
SID 131 started on linux4
SID 117 started on linux4
SID 121 started on linux4
SID 113 started on linux4

RAC connection load balancing finally explained!

I have admittedly struggled to completely understand how RAC load balances connections. As I think I finally understand it, I thought I would post my findings.

Normally, when you find a resource that describes server side load balancing, it simply gives you a URL to use, or suggests you use the OracleDataSource with connection pooling "implicitly" enabled. I never understood how it works, though.

You *do* use the OracleDataSource with ConnectionCachingEnabled set to true. Using this configuration does not, at *physical* connection time, use what is the least loaded node (which is the assumption under which I labored for months). You simply provide the java class (or C#, etc.) with a URL that is configured with LOAD_BALANCE = TRUE, and it will round robin to each node an approximately equal number of connections. The load balancing comes *after* that.

Let's say you create a pool with 100 connections. The URL that asks for LOAD BALANCING will then create about 50 physical connections to each node. Your application then begins requesting a connection as needed from this pool of physical connections, frequently connecting and disconnecting.

Let's then say that a process/processes that consume(s) large amounts of CPU begins running on the first node. As new application connections requests come in, the connection manager will choose from its pool of connections based on information it has received from the ONS (Oracle Notification Services) processes on each node in the cluster. Since ONS has posted the connection manager in the java application that node 2 is less loaded, you should see less newly created "logical" connections using the first more heavily utilized node.

If you look in GV$SESSION at that point, you will still see an approximately equal number of connections to each node. However, the "logical" connections "lent" to application requests for such are taken from the second node, as it can presumably provide a better response time (if that is the service goal).

Does correlation help at all?

While studying how we can use statistics to better understand the performance of our applications, I came upon the concept of kurtosis. What this essentially means is that any given distribution is not normal if its kurtosis is very high...OK, that sounds like a fatal disease, what does it mean?!!!

A normal distribution just means, basically, that the vast majority of the values taken are "pretty close" to the average. This can obviously vary greatly. For example, if the average of 50 samples is 25, and the range 20-30, it is probably normally distributed. However, if the average is 15, and 95 % of the sample are less than 15, it is probably not normally distributed. How can this happen? Well in the case of Oracle's AWR, if we have most of our samples with a "db file sequential read" performance window of 500 seconds, and two with 15,000 seconds, this would mean our distribution is not "normal". As a result, our standard deviation and correlation calculations we have been using to predict performance may fly out the window of validity.

Kurtosis (and skewness) is what provides with insight as to how we test the normality of our data prior to drawing conclusions.

More to come...

Multicollinearity

The predictors should not correlate. In the stepwise selection one variable might take the prediction of the another variable into the model and the second variable will not be taken in to the model. The variables might get also inconsistent correlation coefficients (negative if the effect is positive).

If we use wait events, there will in a lot of cases be multicollinearity. In other words, if we see a lot of waits on "db file sequential read", it may be expected that we will also see significant waits on "db file scattered read".

Using v$sysstat makes this even more of a problem. For example, "user I/O wait time" will almost certainly be correlated with "physical reads".

As such, we need to programmatically identify those predictors that are not correlated with each other.

C callouts from mySQL

While I was trying to find a way to use the statistical functions built into mySQL for performance measurement, I discovered the ability to create user defined functions that use C callouts from within the database. I knew you could create your own functions using SQL inside the database, but this allows us to expose OS functionality/information if we need it.



This is pretty cool, because we can extend the functionality of mySQL (subject to limitations) without changing the source code.



Below is a very simple example of getting the hostname on which the server runs. This is already this functionality in mySQL (“show variables like ‘%host%’), but as noted this is just a simple example. We may have other ways that we could use this technology, such as sending mail from within the database…

[root@linux3 lib]# cat foo.c
#include 
#include 
#include 
#include 
#include 
#include 
#include 
#include 
 
typedef unsigned long long ulonglong;
typedef long long longlong;

#ifdef  __cplusplus
extern "C" {
#endif

//init function is required, and a deinit function is optional.
//These can be used to setup for the function call, and then clean
//up afterwards.

my_bool my_machine_name_init(UDF_INIT *initid,
                                  UDF_ARGS *args,
                                  char *message){
  return 0;
}

char* my_machine_name(UDF_INIT *initid,
                      UDF_ARGS *args,
                      char* result,
                      unsigned long* length,
                      char *is_null,
                      char *error){
  gethostname(result,1000);
  *length = strlen(result);
  return result;
}

We then compile the C code above into a shared library, visible in /usr/lib so mySQL is already aware of it (in the LD_LIBRARY_PATH, but could be anywhere mySQL “knows” about)…

[root@linux3 lib]# gcc -shared -o foo.so foo.c

…and then create a function in the database that references this library.

mysql> drop function my_machine_name;
Query OK, 0 rows affected (0.00 sec)
mysql> create function my_machine_name returns string soname 'foo.so';
Query OK, 0 rows affected (0.00 sec)
mysql> select my_machine_name();
+--------------------------+
| my_machine_name() |
+--------------------------+
| linux3                   |
+--------------------------+
1 row in set (0.01 sec)

mysql> exit
Bye
-bash-3.00$

Like I said, what is above is pretty simple, but I am excited about the possibilities. The code has to be thread safe, and well tested, but it does open some doors for us if the need arises.

A recent question on CDOS was regarding how to have only one space at the end of a CSV output row. Below was my response...

SQL> create table t1110(c varchar2(10))
  2  /
 
Table created.
 
SQL> insert into t1110 values('1')
  2  /
 
1 row created.
 
SQL> insert into t1110 values('2    ')
  2  /
 
1 row created.
 
SQL> insert into t1110 values('3 new  ')
  2  /
 
1 row created.
 
SQL> column len format 9
SQL> column orig format a10
SQL> column newone format a10
SQL> column new_len format 9
SQL> set trims off
SQL> select length(c) len,
  2         c orig,
  3         regexp_replace(c, '\W+$', ' ') newone,
  4         length(regexp_replace(c, '\W+$', ' ')) new_len
  5     from t1110
  6  /
 
LEN ORIG       NEWONE     NEW_LEN
--- ---------- ---------- -------
  1 1          1                1
  5 2          2                2
  7 3 new      3 new            6
 
SQL>

Control file writes when updating???

I had never seen this before, but while testing a migration strategy for a large table with XMLTYPE's, almost half our wait time was spent on control file sequential read and control file parallel write waits. Each of these occurred immediately after a "direct path write" wait.

When I dug a little further, I found that we had the table set to NOLOGGING. We had not set the LOBS to NOCACHE or NOLOGGING. However, evidently that was trumped, and the unrecoverable changes caused each update to write the corresponding SCN to the control file.

We set an event (10359) that eliminated control file writes for our session, and improved our performance by almost 50% (27 minutes for our test case down to 16)!!!

Connection pool example

I was really surprised there were no working examples (albeit simple) in the Oracle documentation. Below is a simple connection pool (with cached statements that eliminate soft parsing)...

import java.sql.*;
import oracle.jdbc.pool.*;

class oracleConnMgr {
OracleDataSource ods;
oracleConnMgr() {
 try {
   ods = new OracleDataSource();
   java.util.Properties prop = new java.util.Properties();
   prop.setProperty("InitialLimit", "3");
   prop.setProperty("MinLimit", "3");
   prop.setProperty("MaxLimit", "5");

   prop.setProperty("MaxStatementsLimit", "20");
   String url = "jdbc:oracle:thin:@wcasbuild01.prod.oclc.org:2484/wcasbld.prod.oclc.org";
   ods.setImplicitCachingEnabled(true);
   ods.setURL(url);
   ods.setUser("howards");
   ods.setPassword("oclc*5439");
   ods.setConnectionCachingEnabled(true);
   ods.setConnectionCacheProperties (prop);
 }
 catch (Exception e) {
   e.printStackTrace();
 }
}

public static void main(String args[]) {
 try {
   oracleConnMgr cm = new oracleConnMgr();
   for (int i = 1; i <= 30; i++) {

     Connection conn = cm.ods.getConnection();
     ResultSet rst1= conn.createStatement().executeQuery("select sys_context('userenv','sid') from dual");
  String sid = "";
  while (rst1.next()) {
    sid = rst1.getString(1);
  }
     PreparedStatement pst = conn.prepareStatement("select * from t");
     ResultSet rst = pst.executeQuery();
     rst.close();
     pst.close();
     ResultSet rset= conn.createStatement().executeQuery("select parse_calls,executions from v$sql where sql_id = '89km4qj1thh13'");
     while (rset.next()) {
    System.out.println("SID " + sid + " running, statement has been parsed " + rset.getString(1) + " times and executed " + rset.getString(2) + " times.");
  }
  conn.close();
   }
 }
 catch (Exception e) {
   e.printStackTrace();
 }
}
}

Below is a simple set of classes for performing your own statistical analysis. As noted, they are simple (no multiple regressions analysis, etc.)

class myStats {
double standardDeviation (double[] array) {
 double[] arr = new double[array.length];
 double av = average(array);
  for (int i = 0; i < array.length; i++) {
    arr[i] = (array[i] - av) * (array[i] - av);
 }
 double d = 0;
  for (int i = 0; i < array.length; i++) {
    d = d + arr[i];
 }
 return Math.sqrt(d / (arr.length - 1));
}

double correlation (double[] array1, double[] array2) {
 double[] arr = new double[array1.length];
 double av1 = average(array1);
 double av2 = average(array2);
  for (int i = 0; i < array1.length; i++) {
    arr[i] = (array1[i] - av1) * (array2[i] - av2);
 }
 double d = 0;
  for (int i = 0; i < array1.length; i++) {
    d = d + arr[i];
 }
 double sd1 = standardDeviation(array1);
 double sd2 = standardDeviation(array2);
 return (d / (sd1 * sd2)) / (array1.length - 1);
}

double slope (double[] array1, double[] array2) {
 double[] arr = new double[array1.length];
 double av1 = average(array1);
 double av2 = average(array2);
  for (int i = 0; i < array1.length; i++) {
    arr[i] = (array1[i] - av1) * (array2[i] - av2);
 }
 double d = 0;
  for (int i = 0; i < array1.length; i++) {
    d = d + arr[i];
 }
 double sd1 = standardDeviation(array1);
 double sd2 = standardDeviation(array2);
 return (sd1 / sd2) * correlation(array1,array2);
}

double yIntercept(double array1[], double array2[]) {
 double d = 0;
 double sl = slope(array1, array2);
 double yAvg = average(array1);
 double xAvg = average(array2);
 d = yAvg - (sl * xAvg);
 return d;
}

double average(double array[]) {
 double d = 0;
 double total = 0;
  for (int i = 0; i < array.length; i++) {
    total = total + array[i];
 }
 d = total / array.length;
 return d;
}

double lineFit(double array1[], double array2[]) {
 double yAvg = average(array1);
 double sl = slope(array1, array2);
 double yi  = yIntercept(array1, array2);
 double d1 = 0;
 double squaredResidual = 0;
 double squaredYVariance = 0;
 for (int i = 0; i < array1.length; i++) {
    squaredYVariance = squaredYVariance + ((array1[i] - yAvg) * (array1[i] - yAvg));
    d1 = ((array2[i] * sl) + yi);
    squaredResidual = squaredResidual + ((array1[i] - d1) * (array1[i] - d1));
 }
 return 1 - (squaredResidual / squaredYVariance);
}
}

class runMyStats {
public static void main(String args[]) {
 double[] i2 = {5,10,20,8,4,6,12,15};
 double[] i = {27,46,73,40,30,28,46,59};
 //double[] i2 = {3,6,9,12};
 //double[] i = {15,30,45,60};
  myStats m = new myStats();
  System.out.println("AVG = " + m.average(i));
  System.out.println("STDEV = " + m.standardDeviation(i));
  System.out.println("Correlation = " + m.correlation(i,i2));
  System.out.println("Slope = " + m.slope(i,i2));
  System.out.println("YIntercept = " + m.yIntercept(i,i2));
  System.out.println("lineFit = " + m.lineFit(i,i2));
}
}

Limitations of wait event analysis?

OK, so we have come full circle to know that hit ratio tuning doesn't work (for everything), and now wait event analysis has been deemed to have limitations...but what are they? I sense a *great* interview question coming on for a senior DBA position.

Quite frankly, there is no silver bullet in tuning. All that matters is how is your application performing. Can you risk walking down the hall to the restroom without a user "accidentally" tripping you on the way? Having said that, I do think wait event analysis is an excellent place to start. Humans measure most things by time..."the system is really slow today...", so analyzing the wait event interface is a great place to at least see what is, well...waiting :)

An example will be provided for where it is of good use. User A comes into DBA's cube, and says, "We installed a new version of the application software this weekend, and now the system is crawling."

The DBA immediately goes to his handy grid control interface, and runs ADDM for the last 30 minutes. He immediately sees that I/O is consuming nearly 60% of user time (db file scattered read). He looks at the top SQL, and sees there is an index that needs to be created. Voila, problem solved.

Now let's look at a different example. While being proactive one Thursday afternoon, the DBA reviewed the top waits of the past 60 days in his database. "What's this?! We have quadrupled our waits on "direct path read" in the last 30 days. What happened?!"

Since his restroom trips have proven largely uneventful in terms of being tripped, he checks other statistics. He finds a corresponding increase in "physical reads direct (lob)" over the same time period.

He finally drills down into the historical SQL detail, and finds a new statement that begin running 30 days ago (right after a software install). He compares the time spent performing I/O by the statement and it is almost perfectly correlated to his increase in direct path read waits. He finds it does an EXISTSNODE call on a cached XMLTYPE column. Instant answer! But where is the problem? There may or may not be one.

Stay tuned for more later...

Cool little function to provide statistical correlation between two events. This helped us to identify performance drivers in our database. Statistically, anything above 60 is considered "statistically significant", or correlated. If one goes up, so does the other.

create or replace function corr_events (p_event1 in varchar2,
                                        p_event2 in varchar2)
  return number is
  l_num number;
begin
  select round((corr(val1,val2) * 100),0)
    into l_num
    from (select snap_id,
                 last_value(snap_id)
                   over (order by snap_id
                         rows between 1 preceding and current row) snap_id1,
                 time_waited_micro - min(time_waited_micro)
                   over (order by snap_id
                         rows between 1 preceding and current row) val1
            from dba_hist_system_event
            where event_name = p_event1
            order by snap_id) a,
         (select snap_id,
                 last_value(snap_id)
                   over (order by snap_id
                         rows between 1 preceding and current row) snap_id1,
                 time_waited_micro - min(time_waited_micro)
                   over (order by snap_id
                         rows between 1 preceding and current row) val2
            from dba_hist_system_event
            where event_name = p_event2
            order by snap_id) b
    where a.snap_id = b.snap_id;
  return l_num;
end;
/

OK, so I was rebuilding a 15 billion row table (looong story, mostly being rebuilt due to poor initial design). At any rate, some of the data was "bad" (dupes we didn't know we had). I found out after the fact that 10g has a "log errors into" clause for all kinds of DML statements. Cool! I just wish I had known *before* I started the rebuild ...

See below for a test case...

SQL> create table t0808(c number primary key);

Table created.

SQL> begin
2 for i in 1..5 loop
3 insert into t0808 values(i);
4 end loop;
5 end;
6 /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> exec dbms_errlog.create_error_log('T0808');

PL/SQL procedure successfully completed.

SQL> insert into t0808 select rownum from dba_users
2 log errors into err$_t0808 reject limit unlimited;

17 rows created.

SQL> commit;

Commit complete.

SQL> select * from t0808;

C
----------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

22 rows selected.

SQL> COLUMN ora_err_mesg$ FORMAT A50
SQL> SELECT ora_err_mesg$ from err$_t0808;

ORA_ERR_MESG$
----------------------------------------------------------------------
ORA-00001: unique constraint (REP.SYS_C005175) violated
ORA-00001: unique constraint (REP.SYS_C005175) violated
ORA-00001: unique constraint (REP.SYS_C005175) violated
ORA-00001: unique constraint (REP.SYS_C005175) violated
ORA-00001: unique constraint (REP.SYS_C005175) violated

5 rows selected.

SQL>


Pretty cool, eh?

Read only tablespace requires no outstanding transactions...anywhere...

Nice little gotcha I received yesterday morning. Our intention is to rebuild two large tables from range partitioning to hash partitioning on another server. One of the datafiles for the tablespace that houses one of the tables in question is 1.4TB in size. When we tried to put it in read only mode
(we are using transport tablespace to segment the rebuild on another box), it hung on the enq - TX contention wait event. I found (and never knew before) that putting *any* tablespace in read only requires that no outstanding (uncommitted or rolled back) transactions exist, even in a tablespace that you are not putting into read only mode. Unfortunately, a long running transaction was about six hour from completing, so we had to wait it out.

It wasn't enormously inconvenient, but caused our rebuild to be pushed back a bit. I wonder why *no* transactions can exist? Does the kernel not "know" that there are no transactions that would affect the tablespace in question? Maybe it's just to complex to track, and its such a rare occurrence, the kernel designers just said "to be safe, let's ensure there is nothing happening when we go into read only mode"?

Do we need AWR?

A recent post on CDOS got me to thinking...do we need AWR? After all, AWR stands for "Automated Workload Repository". There are three words in that functionality title.

From dictionary.com:

Automated (automatic, actually): having the capability of starting, operating, moving, etc., independently
Workload: The amount of work that a machine produces or can produce in a specified time period.
Repository: A place where things may be put for safekeeping.

The normal reasons given for using AWR (or not) are as follows:

1. You can't duplicate the functionality
2. It is against your license terms to use AWR if you haven't paid for it.

If either is true, and depending on which one is, then you may or may not want to use it.

The first one indicates you can't do what it does. To answer this accurately, we have to document what it does. To be honest, I can't list every single piece of functionality, but I can say what I use it for (which is all that matters, anyway). I never use the Grid Control to view AWR information as a baseline. I have written my own queries to query the AWR (we *do* have it licensed). This is a piece of functionality I have always found lacking in AWR. If I want to see the historical change in db file sequential read, there is no Grid tool (of which I aam aware) that will do this for me. As a result, I wrote my own query to which I pass an event, and it will print out the historical time waited spent on this event. You can of course (and I do) apply this to OSSTAT, SYSSTAT, etc. In order to do this, of course, I must query the AWR views...or do I?

I could write my own SQL that would create tables as a copy of each v$view, and then periodically select everything from the current contents of these views into these repository tables. This is a simple solution. In fact, I wrote it this morning in 45 minutes.

But wait, is this legal? In other words, are the historical AWR views the ones that cannot be used, or is it the live v$view information that cannot be queried?

According to http://download.oracle.com/docs/cd/B19306_01/license.102/b14199.pdf, the only off limits view is v$active_session_history.

Command-Line APIs
Diagnostics Pack features can also be accessed by way of database server APIs and
command-line interfaces:

• The DBMS_WORKLOAD_REPOSITORY package is part of this pack.
• The DBMS_ADVISOR package is part of this pack if you specify ADDM as the value of the advisor_name parameter, or if you specify for the value of the task_name parameter any value starting with the ADDM prefix.
• The V$ACTIVE_SESSION_HISTORY dynamic performance view is part of this pack.
• All data dictionary views beginning with the prefix DBA_HIST_ are part of this pack, along with their underlying tables.
• All data dictionary views with the prefix DBA_ADVISOR_ are part of this pack if queries to these views return rows with the value ADDM in the ADVISOR_NAME column or a value of ADDM* in the TASK_NAME column or the corresponding TASK_ID.
• The following reports found in the /rdbms/admin/ directory of the Oracle home directory are part of this pack: awrrpt.sql, awrrpti.sql, addmrtp.sql, addmrpti.sql, awrrpt.sql, awrrpti.sql, addmrpt.sql, addmrpti.sql, ashrpt.sql, ashrpti.sql, awrddrpt.sql, awrddrpi.sql, awrsqrpi.sql, awrsqrpt.sql.

Can you shutdown an instance from plsql?

SQL> begin
2 execute immediate 'alter database close';
3 execute immediate 'alter database dismount';
4 end;
5 /

PL/SQL procedure successfully completed.

SQL>

Shutting down archive processes
Archiving is disabled
Tue May 29 16:41:10 2007
ARCH shutting down
ARC1: Archival stopped
Tue May 29 16:41:15 2007
ARCH shutting down
ARC0: Archival stopped
Tue May 29 16:41:16 2007
Thread 1 closed at log sequence 24
Successful close of redo thread 1
Tue May 29 16:42:07 2007
Completed: alter database close
Tue May 29 16:42:07 2007
alter database dismount
Tue May 29 16:42:08 2007
SUCCESS: diskgroup ASM1 was dismounted
Tue May 29 16:42:09 2007
SUCCESS: diskgroup ASM2 was dismounted
SUCCESS: diskgroup ASM3 was dismounted
Tue May 29 16:42:09 2007
Completed: alter database dismount
(END)

It doesn't look like you can actually shutdown the processes, though, without external code.

Also, you can't re-mount a database shutdown in PLSQL, as you will get an

ORA-00750: database has been previously mounted and dismounted.

Do we need CDOS?

The usenet newsgroup comp.databases.oracle.server ("CDOS") can be a contentious place. Posters there tend to be very opinionated, and sometimes (actually, frequently in my estimation) very rude. It occurred to me that most countries in the world desire to be free. As a result, "big brother" type message board moderators don't tend to go over well with computer types, who are normally free thinkers. However, I have seen good contributor after good contributer driven away from CDOS by what I frequently call the "self appointed Oracle police". There are a few very prominent posters on the newsgroup who consistently become what I would call angry with "newbies" for asking questions they (the "police") don't feel should be asked.

This creates (at least) two problems in my mind:

1. Newbies are reluctant to ask questions for fear they may not be posting "correctly" (don't we all have enough to worry about in our lives without complaining about if someone "top posts"?), or are asking question for which they should already know the answer, but "they are too lazy to read the documentation", etc.
2. Good posters (good, technical contributors to questions posted for discussion) are driven away by the rudeness of these posters (I can't tell you how many times I have seen this over the years...)

It occurred to me that if we don't want to be policed by a "big brother" moderator, and if we don't want to spend time in the jungle of the self appointed Oracle police, there is an answer.

Why not have a forum where readers vote posters off the board in a democratic fashion? Although this may sound somewhat inhumane on the surface, it makes sense to me. Users who don't want to confront the Oracle playground bullies could anonymously cast a vote which would contribute to the deactivation of the bullies account(s). This could be done by restricting IP, username, etc., with a voter only being able to cast one vote against any given "problem child". This would allow the group to moderate itself, giving the "unheard" voice a say in what it looks like. Granted, I haven't completely thought this out (once a week purge?), but it's a start...

Of course, this idea relies on the ability (or lack thereof) of Oracle aficionados (or just newbies looking for an answer to a one time "simple" question) to act like human beings, and not "have it in for" a given poster.

Are there legal issues with this? In other words, what if a "scorned poster" posts things from a different IP, etc., which get the forum owner in legal hot water? Would this idea itself drive away good contributors (the stooges are normally very sound, technically)?

Having said all of that, it sounds like a cool app to write, anyhoo!!!

Standby created without a standby controlfile?!!

Today, a junior DBA created a standby database by copying the controlfile to another host, restoring the 4TB database it serves, and recovering all available logs via RMAN. Sounds good, right? Well, it was until we went into SQL*PLUS and found that a "RECOVER STANDBY DATABASE;" command returned an exception that we were not using a standby controlfile. At this point, we were thinking that we had lost an entire day, when we were already behind on the project.

My initial thought was to issue a "alter database convert to physical standby;". However, I then found that the DBA had created the controlfile for the standby as a "normal" backup controlfile. Was this a good thing?...yeah, not so much. Oracle will not allow you to convert a backup controlfile to a standby, since a backup controlfile implies you are doing incomplete recovery, which a standby database, by definition, should not be.

To complicate matters, we were putting the datafiles on the standby in ASM, where they were on raw devices on the primary host. As a result, RMAN set newname commands were required.
After copying a "real" standby controlfile to the standby host, I found the set newname commands did not work, as this controfile did not "know" about our previously restored and renamed files into ASM on the standby. Since we obviously did not want to re-restore 4TB of datafiles, we ended up cataloging the datafile copies on the standby host so the new standby controlfile "knew" about them, i.e.,

catalog datafilecopy '+ASM_TIER2/XWCBMW/DATAFILE/AEID_LB1_4K.285.621280543' level 0;

...

This worked, and gave us much joy, as out set newname commands in RMAN now worked, and allowed recovery to commence :)

Java Lock class is great for test casing

OK, so while trying to understand Oracle's redo latching mechanisms (we have been experiencing a nightmare with log file sync since switching to a new DMX subsystem), I found that java 1.5 has a new Lock class. How cool is that?!!!

import java.io.*;
import java.util.concurrent.locks.*;

class MyThread implements Runnable {
static Lock l = new ReentrantLock();
Thread thrd;

MyThread(String name){
thrd = new Thread(this, name);
thrd.start(); // start the thread
}

public void run(){
System.out.println(thrd.getName() + " starting.");
try {
boolean b = l.tryLock(10000,java.util.concurrent.TimeUnit.MILLISECONDS);
if (b)
System.out.println("Got the lock for " + thrd.getName());
else
System.out.println("Didn't get the lock for " + thrd.getName());
//l.lock();
Thread.sleep(2000);
l.unlock();
}
catch(Exception e) {}
System.out.println(thrd.getName() + " terminating.");
}
}

class myLock {
public static void main(String args[]) {
for (int j = 1; j <= Integer.parseInt(args[0]); j++) {
MyThread mt1 = new MyThread("Child #" + j);
}
}
}