Oracle Histogram Article

Thursday, September 13, 2007

I subscribe to the Hotsos Monthly Newsletter, available to those who register for a free account at Hotsos.

In the most recent newsletter there is a case study article about using histograms to resolve a performance problem. I haven’t had much opportunity to use histograms so I discovered you can specify the column name and set the bucket size in the method_opt parameter in DBMS_STATS package.

Below is the example from the article itself.

BEGIN
DBMS_STATS.gather_table_stats(
ownname => ‘M’,
tabname => ‘WFA’,
method_opt =>’FOR COLUMNS PROCESSNAME SIZE 12, ESCROLE SIZE 5, ASSIGNSTATUS SIZE 7′);
END;

Due to my limited experience with this topic, it is unclear to me why those specific number of buckets were picked for each column. Was it arbitrary or was there an educated reason. Based on the author’s data distribution analysis, I thought it correlated to the number of distinct values, but that does not appear to be the case with the ESCROLE and ASSIGNSTATUS columns. I’ll add that to the next layer of the histogram onion to be peeled.

Anyway, I found the case study worth reading and thought I would share. Might come in handy someday soon.

References
Oracle9i Database Performance Tuning Guide and Reference Release 2 (9.2) – Using Histograms

Wolfgang Breitling Hotsos Symposium Papers/Presentations regarding histograms

Advertisements

Privileges to Create Materialized Views

Tuesday, September 11, 2007

It is rare in our environment that a developer will request privileges to create a materialized view. So when a developer requests the required privileges to create a materialized view, I have to look up the answer. Metalink Note 1079983.6 provides some assistance.

Below are the privileges to allow the developer to create a materialized view in Oracle9i. In my case I had to add the CREATE SNAPSHOT system privilege to the already existing privileges assigned.

EASY_DEV2@stah303> select * from user_sys_privs;


USERNAME             PRIVILEGE                      ADM

EASY_DEV2 CREATE TABLE NO EASY_DEV2 CREATE ANY VIEW NO EASY_DEV2 CREATE SNAPSHOT NO

In addition, the developer is attempting to create the materialized view with query rewrite. The problem is we are running Oracle9i Release 2 Standard Edition in which QUERY REWRITE is not an enabled feature.

To describe what I mean, I create a test table below upon which the materialized view will be based.

ORACLE@stad116> create table t1 as select owner,object_name,object_type from dba_objects;

Table created.

ORACLE@stad116> select count(*) from t1;

COUNT(*)
———-
5834

However when I attempt to create the materialized view with the “enable query rewrite” clause, I encounter the “feature not enabled” error.

ORACLE@stad116> create materialized view mv_test
2 refresh complete on commit
3 enable query rewrite
4 as
5 select object_type, count(*)
6 from t1
7 group by object_type;

from t1
*
ERROR at line 6:
ORA-00439: feature not enabled: Materialized view rewrite

I remove the “enable query rewrite” clause and the materialized view is created successfully.

ORACLE@stad116> create materialized view mv_test
2 refresh complete on commit
3 as
4 select object_type, count(*)
5 from t1
6 group by object_type;

Materialized view created.

Metalink Note 1019100.102 provides an explanation for this. Although the note refers to Oracle8i, it appears to be true in Oracle9i.

References
Oracle9i Database Release 2 Product Family


OPatch and Oracle9i on Windows

Wednesday, September 5, 2007

Need to install opatch for my Oracle9i Standard Edition (9.2.0.8) on Windows Server 2003 Standard Edition. Perl is required for executing opatch yet is not installed during the Oracle installation especially if you do not install the HTTP Server component.

Metalink Note 229626.1 states:

If you do not currently have Perl on your system, please use one of the following methods to obtain it:

a)Perl is included in the Oracle9i Release 2 (9.2.0.1) CD distribution, under APACHE. If you performed a ‘Typical’ installation of the Enterprise Server, perl was automatically installed under the Apache directory. If you have not performed a Typical installation, perl may be added by running the installer and selecting HTTP Server under the ‘Custom’ installation.

b)Download a script from MetaLink to install perl from the Oracle Server CD. This script can be downloaded from MetaLink as Patch 2417872.

Option ‘b’ gave me problems as I was unable to unzip the Perl.jar file on my system. I will save further investigation of that problem for another day when I have more time.

After finding Metalink Note 308916.1, I decided to install ActiveState Perl v5.6.1.638. Step #7 of the note explains setting your environment variables.

set ORACLE_HOME=<your Oracle Home>
set PERL5LIB=C:\Perl\lib
set ACTIVE_STATE_PERL=TRUE

N.B. use TRUE in upper case. Make sure there are no trailing spaces at the end of ‘TRUE’.

So to execute opatch and apply patch 6130293 to my 9.2.0.8 installation, I ran the command below:

> perl %ORACLE_HOME%\OPatch\opatch.pl apply

To review what has been installed, I execute this command:

> perl %ORACLE_HOME%\OPatch\opatch.pl lsinventory

Producing this output:

Oracle Interim Patch Installer version 1.0.0.0.55
Copyright (c) 2006 Oracle Corporation. All Rights Reserved..

We recommend you refer to the OPatch documentation under
OPatch/docs for usage reference. We also recommend using
the latest OPatch version. For the latest OPatch version
and other support related issues, please refer to document
293369.1 which is viewable from metalink.oracle.com

Oracle Home = I:\oracle\ora92
Location of Oracle Universal Installer components = I:\oracle\ora92\oui
Location of OraInstaller.jar = “I:\oracle\ora92\oui\jlib”
Oracle Universal Installer shared library = I:\oracle\ora92\oui\lib\win32\oraIntaller.dll
Location of Oracle Inventory Pointer = N/A
Location of Oracle Inventory = I:\oracle\ora92\inventory
Path to Java = “I:\oracle\ora92\jre\1.4.2\bin\java.exe”
Log file = I:\oracle\ora92/.patch_storage/<patch ID>/*.log

Creating log file “I:\oracle\ora92\.patch_storage\LsInventory__09-05-2007_15-38
08.log”

Result:

Installed Patch List:
=====================
1) Patch 6130293 applied on Wed Sep 05 15:28:51 BST 2007
[ Base Bug(s): 2775579 5116414 4969005 5901910 4689959 5977665 5530958 5369855 5631836 5671074 5263201 5652380 5068565 5275475 4905638 5609388 4628013 3036540 4695511 3639130 5703297 5887577 6079599 5958589 4998554 5385973 5566937 5188321 5887105 5509707 5744161 5726094 5065418 5639513 2558849 5915901 5728380 4401437 4480159 3755693 5933477 5842790 5491035 5742895 5731178 3521347 5576565 3308166 5523578 5660451 3870360 4660718 5562159 5214373 5211863 3378426 5650477 4483286 3578226 4254094 5523799 5354122 5845232 5149865 4901089 5629365 4057920 5169684 5530583 5345999 5225596 4372359 6038279 5895827 3959063 5391326 6130292 6130293 3920693 3667025 2965960 4966417 5140931 5505981 5845928 5495695 5865568 5901875 4541524 5129407 5172444 5284862 5031712 4593537 5602654 5762618 5084239 5223027 6079582 5723241 ]

OPatch succeeded.

References
Metalink Note 293369.1 – “OPatch documentation list”
Metalink Note 229626.1 – “How to apply interim patches and the functionality of opatch on windows”
Metalink Note 308916.1 – “How To Install Interim Patches With Opatch”
Metalink Note 189489.1 – “Oracle9i Data Server Interim Patch Installtion”
Metalink Note 274526.1 – “How to download and install opatch (generic platform)”