Have to read and remember

Hi,

Third post today (all very short) but I have to clean up my workspace and close all tab in Firefox.
As I always has problem with bookmarks there is a list of interesting documents which I have read and kept open from some time to add it to blog.

Jonathan Lewis - old but very important about indexes.
James Morle's blog - new stuff is coming
Oracle CBO development group blog - some new papers ex. Upgrade from 10g to 11g - CBO changes
Uwe Hesses blog - is count(col) better than count(*)
Miladin Modrakovic's blog - New Oradebug feature in 11R2

Update:
I have forgot about Richard Foote's blog - two entries - Open World Highlights
and another entries about index rebuilding - I'm on Richard side and I prefer to go out on Sunday afternoon ;)

VMWare and CentOS - 2 virtual CPU

If you want to run virtual machine with 2 virtual CPU and CentOS 5 (RedHat 5) as a guest OS just read that note. Without a special settings a CentOS doesn't boot properly and will hung on UDEV service.
I have tried it and now I can simulate a 2 CPU in my VMPlayer and test some Jonathan Lewis staff about index explosion.

Naming Convention and Oracle Forms

I have been asked to create a test / dev environment to migrate Oracle Forms application from Forms 6i (Windows based) to Forms 10g on Linux box. There was about 30 files so whole operation looked very easy – just copy files and setup a Forms service.
I was really surprised when I test a main form – it didn’t start at all – just blank screen in browser.
I have compiled it and all related PL/SQL libraries once again in Forms Builder and there were no errors. So I decided to compile that same form and libraries on Linux box. Five from six libraries have been compiled without any errors but one has had an error message. Very strange for me – that other library has been not found. I have left it for a while and I was trying to compile form itself. This same kind of error appears – some of PL/SQL functions have not been found. But why ? There were PLX and PLL files with “missing” PL/SQL procedures and functions. I have used “strace” tool and I have compiled a forms again. In “strace” output file I have found references to missing files – OK not missing but with a little bit different names – “p” instead of “P”.
And this is a clue of this writing – developers didn’t use any naming convention for file names and links for that files – Windows is not case sensitive so if you call form “Form1” or “FORM1” this is still this same file – but not on case sensitive OS like Linux or other UNIX based systems. I have seen a lot of form which called other forms using not case sensitive names – don’t do that because in case of migration you will have a lot of additional work just to change forms names inside code.
Forms builder added his part too – if you attached a library it is using a file name as a reference to library and it is very important to keep file name case sensitive.

What can happen if you have "\p" in your ORACLE_HOME

Hi, Today I have hit very strange error when I was trying to install Grid Control 10.2.0.5 on Windows. This installation is split into two parts: installation of Grid Control 10.2.0.1 and then patch to 10.2.0.5. First part has been done without any problems but I have made a mistake. I have setup a beginning of all ORACLE_HOMEs to “e:\oracle\product\10.2.0\”. Grid Control creates itself 3 different ORACLE_HOME (one per DB, OMS and agent) using entered path as a prefix. A whole installation of 10.2.0.1 has been performed without any problems and I have started an upgrade to 10.2.0.5. During a configuration phase an OC4J deployment has been finished with error. I start to investigate and I was looked into OUI log file C:\Program Files\Oracle\Inventory\logs\installAction<...>.log

Can't find unicode character property definition via main->r or r.pl at unicode/Is/r.pl line
oracle.ias.sysmgmt.repository.plugin.advanced.apache.parser.ParserException: parsing e:\oracle\product\10.2.0\oms10g\Apache\Apache\conf\ssl.conf:  

Similar error is described in Metalink note 563468.1 but it is related to Oracle Application Server and OPatch utility. A cause of that error is a ORACLE_HOME value. Perl is interpreting the string "\p" in the Oracle home path as a regular expression. It is a little bit funny as Oracle is recommending an ORACLE HOME looking like ‘\oracle\product\’ I didn’t find any patch related to that error so I have just installed a Grid Control using a patch without “\p” string.

Trace access errors in Oracle.

There are several possible causes for object access error in application:

• Application upgrade
• Deploying new rules for security
• Hardcoded schema names

Oracle common errors for those situations are:

ORA-00942: table or view does not exist
ORA-01031: insufficient privileges

Depends of application those errors can appear in application log files or only on user screen. Sometimes it is very difficult to find out a root cause of that kind of error – especially when it is out of the box application without proper logging and tracing possibility.
Oracle logging interface is very powerful and it allow a system administrator to setup a logging of above errors into Oracle alert.log file and into sessions trace files. To enable that functionality a trace level for error has to be increased – it can be done on system or session level. Of course for application a system level is more useful.
Following steps has to be performed on system or session level to trace a ORA-00942 error.

- for all sessions

alter system set events '942 trace name errorstack level 1';

for current session

alter session set events '942 trace name errorstack level 1'; 

Test user is executing following SQL statement:

SQL> select username from dba_users;
select username from dba_users
ERROR at line 1:
ORA-00942: table or view does not exist

Results in alter.log and trace files

Tue Sep 22 11:31:58 2009
Errors in file /oracle/app/diag/rdbms/pioro/pioro/trace/pioro_ora_23409.trc:
ORA-00942: table or view does not exist

Trace file details

Trace file details
*** 2009-09-22 11:31:58.607
*** SESSION ID:(152.1313) 2009-09-22 11:31:58.607
*** CLIENT ID:() 2009-09-22 11:31:58.607
*** SERVICE NAME:(SYS$USERS) 2009-09-22 11:31:58.607
*** MODULE NAME:(SQL*Plus) 2009-09-22 11:31:58.607
*** ACTION NAME:() 2009-09-22 11:31:58.607

----- Error Stack Dump -----
ORA-00942: table or view does not exist
----- Current SQL Statement for this session (sql_id=chvsmttqjzjkn) -----
select username from dba_users

Using above information from trace file DBA can figure out where a problem is and find a proper way to solve it.

regards
Marcin

SwingBench

First time I have seen that tool a few years ago and I was really surprise how useful it was. Now I have come back to it and a new version (release 2.3) in my opinion is very mature and can be used for many various performance tests.
Let me present show main features:

• Few different benchmark test
  
  • OLTP Order Entry PL/SQL version
  • OLTP Order Entry Java version
  • OLTP Calling Circle
  • Stress test
  • DSS Sales History
  • Custom
  
  


• Possible outputs
  
  • Transaction per minute
  • Transaction Response time
  • DML operation per minute
  
  
  
  
• Many configuration options ex:
  
  • Number of concurrent users
  • Min and max delay time between transactions
  • Logon delays
  • Using of connection pooling
  
  
  
  
• Distributed client coordinator
  

All features are described on Dominic Giles (author) homepage in documentation section. This document describe version 2.2 and a newest version is 2.3 and some additional configuration possibilities has been added from XML to GUI.

There are a lot of possible configurations and tests covered by SwingBench. Combining a customer defined transactions in tool with application knowledge SwingBench can be used as a small brother of Oracle Real Application Testing – of course it required much more work to define a workload but it is for free.

How to use it – it is very simple please take a look on my quick start guide.

1. Download a package
2. Unzip into target directory – ex. c:\SwingBench
3. Edit a SwingBenchenv.bat file – only path below should be changed
   
   REM Set the following to reflect the root directory of your Java installation
   
   set JAVAHOME=C:\Program Files\Java\jre1.6.0_07
   
   REM Set the following to the directory where you installed swingbench
   
   set SWINGHOME=C:\swingbench
   
   REM Set the following to the location of your TimesTen install (optional)
   
   set TTHOME=C:\TimesTen\tt70
   
   REM If you don't have the ORACLE_HOME environment variable set uncomment the following and change it to reflect your environment
   
   set ORACLE_HOME=C:\oracle\product\10.2.0\db_1


4. Go to C:\swingbench\winbin and start oewizard.bat - configuration wizard
   
   
   
   
5. Click next and choose a create schema
   
   
   
   
6. Enter a server IP, database name and sys password
   
   
   
   
7. Enter schema name and tablespace location - if you have a EE edition you can clik Use Partitioning option
   
   
   
   
8. Choose a size of demo schema using sliders
   
   
   
   
9. Confirm all settings and click Finish
   
   
   
   
10. Wizard will display a additional window with Wizard log
    
    
    
    
11. Click OK on confirmation screen
    
    
    
    
12. Click Cancel in main Wizard Window
    
    
    
    
13. You can start now a SwingBench - c:\swingbench\winbin\swingbench.bat
    
    
    
    And change a ConnectionString to proper one for your environment
    
    
14. Click Start button and enjoy a performance test
    
    
    

This is all for a first time. Now I'm trying to find a fastest way to catch up
a workload on existing database and transform it into a SwingBench tests.

I hope it help some people to test a performance of existing and future installations.

regards,
Marcin

Slow network connection in 11g

Today I found a thread on Oracle forum about a slow connection and I recall that I solved it in past.
Now I have found a few notes on Metalink (ex. 803838.1) which confirm my investigation that Oracle 11g is using DNS to resolve names instead of /etc/hosts file on Unix box. If your server is not registered in DNS or if there is no DNS server which was specified in /etc/resolv.conf you can have a connection problem.

A fastest way to solve it on Linux is to remove "nameserver" entry from /etc/resolv.conf file if you don't need a DNS on the server otherwise you have to be sure that your Oracle server is registered in DNS.

On Solaris a solution is a little bit different - change line in /etc/nsswitch.conf into

ipnodes: files [NOTFOUND=continue] dns

and do not delete line starting with hosts.

regards,
Marcin