Analyzing chained and migrated rows

Two articles from the renowned authors:

1. Analyze-this by J. Lewis
   Shortcut:
   

   -- gathering statistics populates among others user_tables.chain_cnt
   analyze table [tbl] compute statistics for table;
   -- check gathered data
   -- delete statistics 
   -- otherwise the optimizer will use the chain_cnt to modify 
   -- the cost of indexed access to the table
   analyze table [tbl] delete statistics; 
   exec dbms_stats.gather_table_stats([owner], [tbl]);
   

2. Detect-chained-and-migrated-rows-in-oracle by T.Poder
   Excerpt:
   One way to estimate the impact of chained rows is to just look into the "table fetch continued row" statistic - one can run query/workload and measure this metric from v$sesstat (with snapper for example). And one more way to estimate the total number of chained pieces would be to run something like SELECT /*+ FULL(t) */ MIN(last_col) FROM t and see how much the "table fetch continued row" metric increases throughout the full table scan. The last_col would be the (physical) last column of the table. Note that if a wide row is chained into let's say 4 pieces, then you'd see the metric increase by 3 for a row where 4th row piece had to be fetched.

Duplicating database manually on the same host

The assumption is to create a copy of the database on the same host in order to run 2 independent database instances. It can be done in many ways:

• with RMAN 
• with expdp and standard dump
• with expdp and transportable tablespaces
• with cp command and dbnewid tool

I present here the last way.

shutdown immediate;




shutdown immediate --shutdown cleanly a database to be copied
change the database directory name to something different - it is needed in order to preserve original datafiles, when we would change their paths. By default the Oracle RDBMS deletes original data files when they exist under both paths (ie. old one and the new one; we use OMF) - by directory change we prevent this behaviour - for example new directory name would be TEST.ORIG. After whole operation we return back the original name.
copy all the datafiles, logfiles and controlfiles to a new directory with cp command - for example cp -r TEST.ORIG TES2
create new init.ora with new path to the controlfiles, new unique name and other paths with would conflict with   - for example create pfile='inittes2.ora' from spfile
startup nomount - fix possible errors
mount the database - fix possible errors
change the data files paths - select 'alter database rename file '''||name||''' to '''||regexp_replace(name, '', '')||''';' ddl1 from v$datafile union all select 'alter database rename file '''||name||''' to '''||regexp_replace(name, '', '')||''';' ddl1 from v$tempfile union all select 'alter database rename file '''||member||''' to '''||regexp_replace(member, '', '')||''';' ddl1 from v$logfile
shutdown immediate
nid target=sys dbname=cd2; ensure open_cursors are more then all files of the database
change parameter file db_name to new name
startup mount
alter database open resetlogs
restore old files to the old path and startup the original database

Summarizing there are only 2 things, which one must care about - implicit removal of database files in old location (which must be prevented; not sure if this happens without OMF) and the open_cursors parameter, which must be set higher than the number of database files (not sure but better to count temporary and log files as well).

Login issues

The 11g version brought some changes to the DEFAULT profile. Previously the FAILED_LOGIN_ATTEMPTS parameter was always set to UNLIMITED and now it is now set to some value, which means schema locking after this value of failed logins will be crossed over.

I must say I am puzzled with this. In general I understand the reason behind the FAILED_LOGIN_ATTEMPTS - it is against password breaking brute force attacks. On the other hand it means that some 'lost' application host with wrong password becomes a point of a DoS attack. Which is better (or worse) hard to tell.
Usually a database is located after some firewall (or two) as this is quite deep layer in the application stack. I usually meet with databases, where the schemas are application schemas, so exists a client application interface between a human and a database - passwords are encoded in the application configuration and direct access to a database itself is strictly limited. On the other hand there are users (though not numerous), who are allowed to make a direct connection and among them may be hidden a 'malicious' one.

So, how do I imagine to deal with the configuration?
I believe for application it is better to create another profile, which keeps the FAILED_LOGIN_ATTEMPTS parameter to UNLIMITED, because it is not so rare that there exists some forgotten application or script, which would block the schema and thus practically disables the application. Of course there is a monitoring system, but usually the delay in information feedback to human is ~5 minutes, there come another issues (multiple application hosts, and only one of them with wrong password; few applications sharing the same schema; scripts run from cron on different shell accounts; etc) and we get a noticeable delay in application work, where possible it was meant to work in 24x7 mode. And this may happen quite frequently and there is no need for malice.
Further it would be reasonable to move direct users to another databases and possibly connect them through mix of additional schemas and/or database links, so that they would not be able to connect directly to the database with application schemas.

The drawbacks?

• the human users have got performance penalty, if connected through another database or may try to break passwords if there will be no such prevention measures - so if it is a database with plenty of direct human users then this would not be so great idea
• 11g: Multiple failed login attempt can block new application connections - shortly the 11g version has additional security feature against brute force attacks - if set to UNLIMITED there is enabled a delay when returning error message due to failed login attempt after first few attepmts. Due to the bug 7715339 such delayed session keeps library cache lock for prolonged period (due to enabled delay) and new sessions wait on this lock till the number hits sessions/processes ceiling. It is possible to disable the delay feature with event='28401 trace name context forever, level 1'

An issue with the UTL_FILE and file permissions

Configuration in which only local user is able to write to indicated directory through UTL_FILE running sqlplus, and for any remote access or access by other users, the code will not work properly Some time ago we met with a little weird behavior of a test database. The code in a package did some processing and wrote to specified directory. However it worked only if the user was logged on the database host as him. Calling the code remotely or locally from a different user (also the database owner) ended always with error about wrong operation on file (the writes were issued through UTL_FILE).
The os directory, which was aliased in the database, had mask 0777, so though not owned by oracle, should be accessible by the database.

The explanation is the following:
While the aliased directory was accessible to anybody, the parent directory to it (ie. home directory of the user) was accessible only to him and his group. Thus if any user tried to use the code, the database can not have written to the destination as it requires at least rX permissions on every directory level. To make the code run was to add the oracle user to the local user group or change permissions on the local user home or anything like that.
But why the local user was able to perform the code successfully? This is bound with the way the connection to the database is handed to a user client. When it is done through listener simplifying it prepares a process/dispatcher, then forwards the connection information to the client. If dealing with connection locally it is done differently - the database process becomes a child of the local user session and inherits user' permissions and thus is able to write to the otherwise inaccessible file.

Conditions in UPDATE statements

This is kind of a fix to common developer misuse. I noticed that sometimes see the solution for searching more complex constructs as this:

[..] where f1||f2||f3 in (select f1||f2||f3 from t1)

While in SELECT this is simply weird, because one may simply join 2 tables and specify conditions with f1=f1 etc., it may be considered when we play with DML. Of course this means the optimizer can not use indexes (unless there is some rather complex index on expression), but developers forget about very easy construct:

[..] where (f1, f2, f3) in (select f1, f2, f3 from t1)

Now plans look much better and we still may create condition on several fields at once.

Confusion at partition maintenance

Here an anecdote rather than a hint, but sometimes helps ;-). Once I have read about our "built-in" inability to find own errors and this is exactly about such case.

Lately I tried to add a new partition for 2013 year to some table. It was subpartitioned, so the syntax little more complex (below - please note, this is with error).

alter table schema1.tab1
add partition p1301 VALUES less than (201302) TABLESPACE tbs1 (
SUBPARTITION p1301_1 VALUES(1), 
SUBPARTITION p1301_2 VALUES(2),
SUBPARTITION p1301_3 VALUES(3), 
SUBPARTITION p1301_4 VALUES(4)));

I constructed the statement and tried to execute. But on and on I have got the same error:

ORA-14048: a partition maintenance operation may not be combined with other operations

. And it was not as complex as I may miss an additional operation. So I stuck. After several minutes I ask a colleague of mine for help. He came to me and I have explained him the issue. At the same moment I realize what was wrong - additional parenthesis.
If it would be missing, the error message would be correct

ORA-00907: missing right parenthesis

but with additional parenthesis the statement syntax suggests to the RDBMS something completely different, thus returning more enigmatic error, which in turn puzzled the RDBMS user - in this case me.

Some notes on materialized views

Few loosely notes on materialized views.

It is possible to build a mview on a pre-built table. I like this feature mainly because one is able to drop such view and recreate it with slightly changed definition, while the data is preserved, which is very valuable especially when dealing with large source tables.

Another observation is that sometimes the casting to right types is necessary. We met several times the situation when on Linux some mview definition worked properly while on AIX it failed due to not strong type casting, when the solution was to use CAST with precision to type and length (usually the problem core was longer VARCHAR2 than expected).