OCP Blog
Super DBA
Concatenation and filter option in SQL PLAN
by Vazha Mantua Friday, April 8, 2011 8:38 PM
Hello All,
For example we have this statement:
select count(*) from customers
where acc_nbr = nvl(:v,acc_nbr)
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=137 Card=1 Bytes=7)
   1    0   SORT (AGGREGATE)
   2    1     CONCATENATION
   3    2       FILTER
   4    3         INDEX (FAST FULL SCAN) OF 'CUST_PK' (UNIQUE) (Cost
   5    2       FILTER
   6    5         INDEX (UNIQUE SCAN) OF 'CUST_PK' (UNIQUE) (Cost=2
In execution plan we see 2 filters and then concatenation. Reason of this is nvl comand, Oracle CBO rule . We have 2 possible scenario bind variable :v is null or is not null.
but for RBO rule change sql plan and filters and concatenation is not appear in SQL PLAN
Many examples say that in this case that CBO is better than RBO(Rule hint), but sometimes RBU rule is better.
How can we avoid 2 filters in concatenation,simple we have 2 solutions:
select /*+ rule */ count(*) from customers where acc_nbr = nvl(:v,acc_nbr)
or
select /*+ no_expand*/ count(*) from customers where acc_nbr = nvl(:v,acc_nbr).
Let describe hint no_expand:
The NO_EXPAND hint prevents the cost-based optimizer from considering OR-expansion for queries having OR conditions or IN-lists in the WHERE clause. Usually, the optimizer considers using OR expansion and uses this method if it decides that the cost is lower than not using it.

Tags: , ,

ORA-10567: Redo is inconsistent with data block (file# 25, block# 7652)
by Vazha Mantua Wednesday, March 9, 2011 10:57 AM

Good morning all,

 

On of our standby database stop apply process with error ORA-10567: Redo is inconsistent with data block (file# 25, block# 7652).

 

ORA-10567: Redo is inconsistent with data block (file# 25, block# 7652)

ORA-10564: tablespace USER_DATA

ORA-01110: data file 124: ‘/u01/oradata/ORAST/USER_DATA_01.DBF’

ORA-10561: block type ‘TRANSACTION MANAGED INDEX BLOCK’, data object# 7652

 

Cause

The Redo when shipped across network or somewhere along the way had some issues which caused the inconsistency, hence not able to syncup with the standby datafiles.

 

Solution

On primary database

1.ALTER tablespace USER_DATA begin backup;

2.copy USER_DATA01.dbf datafile from primary DB to standby DB

On primary database

3.ALTER tablespace USER_DATA end backup;

on standby database start recovery

4. recover managed standby database using current logfile disconnect;

 

Remark: Does not do this operation when production database has pick load, begin backup can decrease DB performance

Tags: ,

Oracle 10g RAC Single Node Installation on Solaris 10
by Vazha Mantua Monday, February 21, 2011 4:28 PM

Oracle 10g RAC Single Node Installation on Solaris 10

Pre-Installation Tasks

Create oracle user and appropriate groups

Login as a root user and create required OS groups and users.

Make the following directories (app and oracle) in /u0 :

cd /u0
mkdir –p app/oracle

Create oinstall and dba groups:

groupadd oinstall
groupadd dba

Create oracle user:

useradd –s /bin/ksh –d /u0/app/oracle –g oinstall –G dba oracle

Change the owner for app folder.

chown –R oracle:oinstall app

Change password.

passwd oracle

Configuring SSH

Create RSA and DSA keys on each node. Log in as the oracle user and go to the ORACLE_BASE directory.

Create directory .ssh

mkdir .ssh

Generate RSA and DSA keys

/usr/bin/ssh-keygen -t rsa
/usr/bin/ssh-keygen -t dsa

Add keys to an authorized key file, this file should exist in .ssh directory. If it doesn’t exist, create it. Change the direction to .ssh directory and run the following.

ssh charly1 cat /u0/app/oracle/.ssh/id_rsa.pub >> authorized_keys
ssh charly1 cat /u0/app/oracle/.ssh/id_dsa.pub >> authorized_keys

Enable ssh user equivalency

/usr/bin/ssh-agent $SHELL

/usr/bin/ssh-add

To prevent Oracle clusterware installation errors caused by stty commands, add the following text in oracle user’s profile.

if [ -t 0 ]; then

stty intr ^C

fi

Network Requirements

Create second IP interface. Open or create the file /etc/hostname.ce5 and enter the following text:

Host1-priv

Add the following entries into /etc/host file:

20.0.0.100 Host1 loghost

192.168.2.117 Host1-priv

20.0.0.105 Host1-vip

Where 192.168.2.117 is a private IP address and 20.0.0.105 is a virtual IP address of the server.

Connect as a root user and create /etc /hosts.equiv file. Then open this file for editing and enter the following text:

Host1 oracle

Configuring Kernel Parameters

To add the system parameters open /etc/system file and add the following entries:

set shmsys:shminfo_shmmax = 4294967295

set shmsys:shminfo_shmmin = 1

set shmsys:shminfo_shmmni = 100

set shmsys:shminfo_shmseg = 10

set semsys:seminfo_semmni = 100

set semsys:seminfo_semmns = 1024

set semsys:seminfo_semmsl = 256

set md:mirrored_root_flag=1

set noexec_user_stack=1

Identifying Required Software Directories

Creating an Oracle Base Directory

mkdir -p /u0/app/oracle
chown -R oracle:oinstall /u0/app/oracle
chmod -R 775 /u0/app/oracle

Creating the Oracle Clusterware Home Directory

mkdir -p /u0/app/oracle/product/10.2.0/crs

chown -R root:oinstall /u0/app/oracle/product/10.2.0/crs

chmod -R 775 /u0/app/oracle/product/10.2.0/crs

Installing Oracle Clusterware

First of all, you should download Oracle Clusterware from the oracle site.

Verifying Oracle Clusterware Requirements with CVU

Extract downloaded file and run runcluvfy.sh file, which should be located in clusterware/cluvfy folder.

./runcluvfy.sh comp nodecon -n Host1 –verbose

Installing Oracle Clusterware with OUI

Initialize the devices that you want to use for the Oracle Cluster Registry (OCR) and Voting Disk(VD)

1.Voting file- uses by the cluster synchronization service deamons for monitoring information across the cluster. It’s size around 20MB(use format utility and reserve 200MB on 5th slice)

2. Oracle cluster registry(OCR file) it maintain information about the high-availability components such as cluster node list, CRS application profiles(Virtual interconnect protocol address, services). It’s size around 200MB(use format utility and reserve on 7th slice)

For voting disk

dd if=/dev/zero of=/dev/rdsk/c5t200g35A100TK170Ta00ds5 bs=125829120 count=1

chown oracle:dba /dev/rdsk/c5t200g35A100TK170Ta00ds5

chmod 660 /dev/rdsk/c5t200g35A100TK170Ta00ds5

For OCR

dd if=/dev/zero of=/dev/rdsk/c5t200g35A100TK170Ta00ds7 bs=125829120 count=1

chown root:dba /dev/rdsk/c5t200g35A100TK170Ta00ds7

chmod 660 /dev/rdsk/c5t200g35A100TK170Ta00ds7

Connect as the oracle user and run runInstaller.sh, located in CRS installation.

./runInstaller

clip_image001

clip_image002

clip_image003

clip_image004

clip_image005

clip_image006

clip_image007

clip_image008

Run these scripts and then click OK.

clip_image009

Now we should run ./vipca and configure Host1-vip virtual ip address(20.0.0.105). Go to the installation window, click ok and retry install failed component.

Installing Oracle database files

clip_image010

clip_image011

clip_image012

clip_image013

clip_image014

Run this script and click OK, then click Exit button.

Configure oracle listener by netca.

Configuring ASM

clip_image015

clip_image016

clip_image017

clip_image018

Click Yes.

clip_image019

clip_image020

Run the following command, for to make oracle user owner for the following disk slice

chown oracle:oinstall /dev/rdsk/c5156D778A145a11d0s6

clip_image021

clip_image022

Click Finish.

clip_image023

Creating database using DBCA

clip_image024

clip_image016[1]

clip_image025

clip_image026

Click Next>> and check/uncheck Configure the Database with Enterprise Manager.

clip_image027

clip_image028

clip_image022[1]

clip_image029

clip_image030

clip_image031

clip_image032

clip_image033

Tags: , ,

DBMS_SHARED_POOL Package
by Vazha Mantua Tuesday, January 11, 2011 12:51 PM

Hello All,

Mary Christmas and happy New Year.

 

I want tell you about package dbms_shared_pool.

DBMS_SHARED_POOL can be used to manage the affect of the LRU mechanism on the shared pool. It can be used to pin PL/SQL objects, SQL cursors, triggers, and sequences. it enables you to display the sizes of objects in the shared pool, and mark them for keeping or unkeeping in order to reduce memory fragmentation.

 

Install the DBMS_SHARED_POOL package.

run script: $ORACLE_HOME/rdbms/admin/dbmspool.sql;

 

Check for objects in the Shared Pool larger than X KBytes

run procedure
begin
dbms_output.enable(null);  
dbms_shared_pool.sizes(200);
end;

Pin something into the Shared Pool

begin

dbms_shared_pool.keep('SYS.STANDARD');
end;

Unpin something from the Shared Pool

begin

dbms_shared_pool.unkeep('SYS.STANDARD');

end;

 

Check what objects are pinned

SELECT * FROM v$db_object_cache WHERE kept = 'YES';

Tags: ,

Once more scenario for library latch contention
by Vazha Mantua Friday, December 17, 2010 10:53 AM

In last posts we describe reason of library latch contention and the main reason was literal sql’s

 

Let’s see another scenario

it’ is version of SQL statements:

if two statements are textually identical but cannot be shared then these are called 'versions' of the same statement. If Oracle matches to a statement with many versions it has to check each version in turn to see if it is truely identical to the statement currently being parsed. Hence high version counts are best avoided by:

  • Standardizing the maximum bind lengths specified by the client
  • Avoid using identical SQL from lots of different schemas which use private objects. Eg: SELECT xx FROM MYTABLE; where each user has their own MYTABLE
  • Setting _SQLEXEC_PROGRESSION_COST to '0' in Oracle 8.1

 

In our case we have non standarting maximum m binds lengths

The most common causes are:
Bind Type mismatch

VARIABLE v1 VARCHAR2(60);
VARIABLE v1 VARCHAR2(30);

Language

ALTER SESSION SET NLS_LANGUAGE = 'AMERICAN';
ALTER SESSION SET NLS_LANGUAGE = �GERMAN';

SQL Trace

EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE;
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

Environmental variables that affect the optimizer

ALTER SESSION SET optimizer_mode = CHOOSE;
ALTER SESSION SET optimizer_mode = FIRST ROWS;

Note: how find SQL’s with many versions:

 

`

SELECT address, hash_value,
version_count ,
users_opening ,
users_executing,
substr(sql_text,1,40) "SQL"
FROM v$sqlarea
WHERE version_count > 10
order by version_count desc

Tags: , ,

APEX 4.0 Installation Guide
by Vazha Mantua Friday, November 12, 2010 3:14 PM

Oracle Application Express (Oracle APEX) is a rapid web application development tool for the Oracle database. Using only a web browser and limited programming experience, you can develop and deploy professional applications that are both fast and secure. Oracle APEX is a fully supported, no cost option of the Oracle database

Let’s start!

1.Oracle Application Express Installation Requirements

1.1 Oracle Database Requirement
Oracle Application Express version 4.0 requires an Oracle database (Enterprise Edition, Standard Edition or Standard Edition One) that is release 10.2.0.3 or higher. Application Express 4.0 can also be used with Oracle Database 10g Express.

1.1.1 Checking the shared_pool_size of the Target Database

Note:Ignore this requirement if your configuration uses non-null values for the database initialization parameters SGA_TARGET (in Oracle Database 10g and 11g) or MEMORY_TARGET (in Oracle Database 11g).
Oracle Application Express requires the shared_pool_size of the target database to be at least 100 MB.
To check the shared_pool_size of the target database:
1. Start the database:
SQL> STARTUP
2. If necessary, enter the following command to determine whether the system uses an initialization parameter file (initsid.ora) or a server parameter file (spfiledbname.ora):
SQL> SHOW PARAMETER PFILE;
3. Determine the current values of the shared_pool_size parameter:
SQL> SHOW PARAMETER SHARED_POOL_SIZE
4. If the system is using a server parameter file, set the value of the SHARED_POOL_SIZE initialization parameter to at least 100 MB:
SQL> ALTER SYSTEM SET SHARED_POOL_SIZE='100M' SCOPE=spfile;
5. Shut down the database:
SQL> SHUTDOWN
6. Restart the database:
SQL> STARTUP

1.2 Browser Requirements

To view or develop Oracle Application Express applications, Web browsers must support Java Script and the HTML 4.0 and CSS 1.0 standards. The following browsers are required to develop applications in Oracle Application Express:
· Microsoft Internet Explorer 7.0 or later version
· Mozilla Firefox 3.5 or later version
· Google Chrome 4.0 or later version
· Apple Safari 4.0 or later version
Application Express applications can be developed that support earlier Web browser versions, including Microsoft Explorer 6.0.

1.3 Disk Space Requirement

Oracle Application Express disk space requirements are as follows:
· Free space for Oracle Application Express software files on the file system: 450 MB if using English only download (apex_4_0_en.zip) and 1 GB if using full download (apex_4_0.zip).
· Free space in Oracle Application Express tablespace: 185 MB
· Free space in SYSTEM tablespace: 100 MB
· Free space in Oracle Application Express tablespace for each additional language (other than English) installed: 75 MB

1.4 Oracle XML DB Requirement

Oracle XML DB must be installed in the Oracle database that you want to use. If you are using a preconfigured database created either during an installation or by Database Configuration Assistant (DBCA), Oracle XML DB is already installed and configured.
See Also: Oracle XML DB Developer's Guide for more information about manually adding Oracle XML DB to an existing database
Tip: The installer does a prerequisite check for Oracle XML DB and will exit if it is not installed.
Tip: The installation of Oracle XML DB creates the user ANONYMOUS. In order for Oracle Application Express workspace provisioning to work properly, the ANONYMOUS user must not be dropped from the database.

1.5 PL/SQL Web Toolkit

Oracle Application Express requires the PL/SQL Web Toolkit version 10.1.2.0.6 or later. For instructions on determining the current version of the PL/SQL Web Toolkit, and for instructions on installing version 10.1.2.0.6, please review the README.txt file contained in the directory apex/owa.

2. install Oracle Application Express

2.1 Recommended Pre-installation Tasks

Before installing Oracle Application Express, Oracle recommends that you complete the following steps:

1. Shut down with normal or immediate priority the Oracle Database instances where you plan to install Oracle Application Express. On Oracle Real Application Clusters (Oracle RAC) systems, shut down all instances on each node.

2. Back up the Oracle Database installation. Oracle recommends that you create a backup of the current Oracle Database installation before you install Oracle Application Express. You can use Oracle Database Recovery Manager, which is included the Oracle Database installation, to perform the backup.
3. Start the Oracle Database instance that contains the target database. After backing up the system, you must start the Oracle instance that contains the target Oracle database. Do not start other processes such as the listener or Oracle HTTP Server. However, if you are performing a remote installation, make sure the database listener for the remote database has started.
2.2 Download and Install Oracle Application Express
To install Oracle Application Express:
1. For installations where the development will be in English only, download the file apex_4.0_en.zip from the Oracle Application Express download page. Where the development will include languages other than English, download apex_4.0.zip from the Oracle Application Express download page. See:
http://www.oracle.com/technology/products/database/application_express/download.html
Note that the actual file name may differ if a more recent release has shipped since this document was published.
2. Unzip downloaded zip file:
· UNIX and Linux: Unzip apex_4.0.zip
· Windows: Double click the file apex_4.0.zip in Windows Explorer
3. Change your working directory to apex.
4. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role. For example:
On Windows:
SYSTEM_DRIVE:\ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password
On UNIX and Linux:
$ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password
And run the command:
     SQL>@apexins SYSAUX SYSAUX TEMP /i/
When Oracle Application Express installs it creates three new database accounts:
· APEX_040000 - The account that owns the Oracle Application Express schema and metadata.
· FLOWS_FILES - The account that owns the Oracle Application Express uploaded files.
· APEX_PUBLIC_USER - The minimally privileged account used for Oracle Application Express configuration with Oracle HTTP Server and mod_plsql or Oracle Application Express Listener.
2.3 Change the Password for the ADMIN Account
To change the password for the ADMIN account:
1. Change your working directory to the apex directory where you unzipped the installation software.
2. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role. For example:
On Windows:
SYSTEM_DRIVE:\ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password
On UNIX and Linux:
$ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password
3. Run apxchpwd.sql. For example:
SQL>@apxchpwd
When prompted enter a password for the ADMIN account.
2.4 Restart Processes
After you install Oracle Application Express, you must restart the processes that you stopped before you began the installation, such as listener and other processes.
2.5 Configure the Embedded PL/SQL Gateway
2.5 .1 Running the apex_epg_config.sql Configuration Script
The embedded PL/SQL gateway installs with the Oracle Database 11g. However, you must configure it before you can use it with Oracle Application Express. To accomplish this, you run a configuration file and unlock the ANONYMOUS account.
To run the apex_epg_config.sql configuration script:
1. Change your working directory to the apex directory where you unzipped the Oracle Application Express software.
2. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role. For example:
On Windows:
SYSTEM_DRIVE:\ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password
On UNIX and Linux:
$ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password
3. Run apex_epg_config.sql passing the file system path to the base directory where the Oracle Application Express software was unzipped as shown in the following example:
On Windows:
@apex_epg_config SYSTEM_DRIVE:\TEMP
On UNIX and Linux:
@apex_epg_config /tmp
4. Enter the following statement to unlock the ANONYMOUS account:
ALTER USER ANONYMOUS ACCOUNT UNLOCK;
2.5 .2 Verifying the Oracle XML DB HTTP Server Port
The embedded PL/SQL gateway runs in the Oracle XML DB HTTP server in the Oracle database. You can determine if the Oracle XML DB HTTP server is enabled by verifying the associated port number.
To verify the port number where the Oracle XML DB HTTP Server is running:
1. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role:
On Windows:
SYSTEM_DRIVE:\ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password
On UNIX and Linux:
$ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password
2. Enter the following statement to verify the port number:
SELECT DBMS_XDB.GETHTTPPORT FROM DUAL;
If the port number returns 0, the Oracle XML DB HTTP Server is disabled.
3. To enable it, follow the instructions in 2.5 .3 Enabling Oracle XML DB HTTP Server.
2.5 .3 Enabling Oracle XML DB HTTP Server
The embedded PL/SQL gateway runs in the Oracle XML DB HTTP server in the Oracle database.
To enable Oracle XML DB HTTP server:
1. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role. For example:
On Windows:
SYSTEM_DRIVE:\ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password
On UNIX and Linux:
$ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password
2. Enter a statement similar to the following:
EXEC DBMS_XDB.SETHTTPPORT(port);
For example:
EXEC DBMS_XDB.SETHTTPPORT(8080);
2.6 Enable Network Services in Oracle Database 11g
By default, the ability to interact with network services is disabled in Oracle Database 11g release 1 or 2. Therefore, if you are running Oracle Application Express with Oracle Database 11g release 1 or 2, you must use the new DBMS_NETWORK_ACL_ADMIN package to grant connect privileges to any host for the APEX_040000 database user.
2. 6.1 Granting Connect Privileges
The following example demonstrates how to grant connect privileges to any host for the APEX_040000 database user. This example assumes you connected to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role.
DECLARE
  ACL_PATH  VARCHAR2(4000);
  ACL_ID    RAW(16);
BEGIN
  -- Look for the ACL currently assigned to '*' and give APEX_040000
  -- the "connect" privilege if APEX_040000 does not have the privilege yet.
 
  SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
   WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;
 
  -- Before checking the privilege, ensure that the ACL is valid
  -- (for example, does not contain stale references to dropped users).
  -- If it does, the following exception will be raised:
  --
  -- ORA-44416: Invalid ACL: Unresolved principal 'APEX_040000'
  -- ORA-06512: at "XDB.DBMS_XDBZ", line ...
  --
  SELECT SYS_OP_R2O(extractValue(P.RES, '/Resource/XMLRef')) INTO ACL_ID
    FROM XDB.XDB$ACL A, PATH_VIEW P
   WHERE extractValue(P.RES, '/Resource/XMLRef') = REF(A) AND
         EQUALS_PATH(P.RES, ACL_PATH) = 1;
 
  DBMS_XDBZ.ValidateACL(ACL_ID);
   IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_040000', 
     'connect') IS NULL THEN 
      DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH, 
     'APEX_040000', TRUE, 'connect'); 
  END IF;
 
EXCEPTION
  -- When no ACL has been assigned to '*'.
  WHEN NO_DATA_FOUND THEN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml',
    'ACL that lets power users to connect to everywhere',
    'APEX_040000', TRUE, 'connect');
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;
The following example demonstrates how to provide less privileged access to local network resources. This example would enable indexing the Oracle Application Express Online Help and could possibly enable email and PDF printing if those servers were also on the local host.
DECLARE
  ACL_PATH  VARCHAR2(4000);
  ACL_ID    RAW(16);
BEGIN
  -- Look for the ACL currently assigned to 'localhost' and give APEX_040000
  -- the "connect" privilege if APEX_040000 does not have the privilege yet.
  SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
   WHERE HOST = 'localhost' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;
 
  -- Before checking the privilege, ensure that the ACL is valid
  -- (for example, does not contain stale references to dropped users).
  -- If it does, the following exception will be raised:
  --
  -- ORA-44416: Invalid ACL: Unresolved principal 'APEX_040000'
  -- ORA-06512: at "XDB.DBMS_XDBZ", line ...
  --
 
  SELECT SYS_OP_R2O(extractValue(P.RES, '/Resource/XMLRef')) INTO ACL_ID
    FROM XDB.XDB$ACL A, PATH_VIEW P
   WHERE extractValue(P.RES, '/Resource/XMLRef') = REF(A) AND
         EQUALS_PATH(P.RES, ACL_PATH) = 1;
 
  DBMS_XDBZ.ValidateACL(ACL_ID);
   IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_040000', 
     'connect') IS NULL THEN 
      DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH, 
     'APEX_040000', TRUE, 'connect'); 
  END IF;
 
EXCEPTION
  -- When no ACL has been assigned to 'localhost'.
  WHEN NO_DATA_FOUND THEN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('local-access-users.xml',
    'ACL that lets power users to connect to everywhere',
    'APEX_040000', TRUE, 'connect');
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('local-access-users.xml','localhost');
END;
/
COMMIT;
2.7 About Managing JOB_QUEUE_PROCESSES

JOB_QUEUE_PROCESSES determine the maximum number of concurrently running jobs. In Oracle Application Express release 4.0, transactional support and SQL scripts require jobs.
If JOB_QUEUE_PROCESSES is not enabled and working properly, you cannot successfully execute a script.
2.7.1 Viewing JOB_QUEUE_PROCESSES from SQL*Plus

SELECT VALUE FROM v$parameter WHERE NAME = 'job_queue_processes'
(We had 128)
2.7.2 Changing the Number of JOB_QUEUE_PROCESSES
You can change the number of JOB_QUEUE_PROCESSES by running a SQL statement in SQL*Plus:
To update the number of JOB_QUEUE_PROCESSES:
1. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role:
On Windows:
SYSTEM_DRIVE:\ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password
On UNIX and Linux:
$ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password
2. In SQL*Plus run the following SQL statement:
ALTER SYSTEM SET JOB_QUEUE_PROCESSES = <number>
For example, running the statement ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 20 sets JOB_QUEUE_PROCESSES to 20.
2.8 Configuring the SHARED_SERVERS Parameter
The embedded PL/SQL gateway uses the shared server architecture of the Oracle Database. To achieve acceptable performance when using the embedded PL/SQL gateway, ensure the SHARED_SERVERS database initialization parameter is set to a reasonable value (that is, not 0 or 1). For a small group of concurrent users, Oracle recommends a value of 5 for SHARED_SERVERS.
Consider the following example:
1. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role. For example:
On Windows:
SYSTEM_DRIVE:\ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password
On UNIX and Linux:
$ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password
2. Run the following statement:
ALTER SYSTEM SET SHARED_SERVERS = 5 SCOPE=BOTH;
 
Note: If database is not started by spfile shutdown and start it by spfile.
Go to the link to check if it works
 
http://hostname:port/apex/apex_admin

Tags: ,

Huge Space Consumption by $ORACLE_HOME/.patch_storage
by Vazha Mantua Monday, November 8, 2010 12:21 PM

Hello All ,

When we applied Patchset 10.2.0.4 and some critical oracle patches, we got space problem on server.

We found large directories in solaris with command :du -ko /u0|sort -n | tail -10.

$ORACLE_HOME/.patch_storage was largest directory in oracle_home.

In .patch_storage is keeping your system up to date with Patchsets, Patch Bundles, Merge Label Requests (MLR) or Critical Patch Updates (CPU) for restore old versions.

for clearing this directory we can use MetaLink Note 550522.1
or simple: opatch util Cleanup

Tags:

The Real Application testing, Database Replay process
by Vazha Mantua Monday, November 8, 2010 12:20 PM

The Real Application testing, Database Replay process

The Real Application testing feature, which consists of two separate tools, Database Replay and the SQL performance Analyzer, is arguably the most significant new feature in the Oracle Database 11.1 Release. With this process we can simulate a real production workload on a test system; we can apply some patches, change data storage and see how our performance changes on test system.
In this post we step by step review Database replay process

1. Restart prod DB and startup restrict mode (this in optional)
2. We can use optional workload filters to restrict the workload capture to only a part of the actual production workload:
BEGIN
DBMS_WORKLOAD_CAPTURE.ADD_FILTER(FNAME => 'FILTER_NAME',FATTRIBUTE => 'USER',FVALUE => 'SCOTT');

END;
We can find filter’s in view DBA_WORKLOAD_FILTERS;
3. Start Workload capture, means start process which collect workload to the some files, which then will apply on test DB:
BEGIN
DBMS_WORKLOAD_CAPTURE.START_CAPTURE(NAME => '2010OCT',DIR => 'DATA_PUMP_DIR',DURATION => 1200);
END;

Duration is number of seconds for which workload will be captured this is optional parameter.
DIR is oracle directory which must be empty!
We can manually stop capture process with this statement:
BEGIN
DBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE();
END;

We can see status of process in view DBA_WORKLOAD_CAPTURES
4. Preprocess capture data means that capture data will procedure the metadata for captured workload and transform the captured workload datafiles into reply streams called reply files that you can now replay on the test system:
BEGIN
DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE(CAPTURE_DIR => 'DATA_PUMP_DIR');
END;

5. Now we must create test DB which should have same data structure as prod DB, we can use rman scripts(backup, restore), snapshot standby DB, Data pump utilities or simple cold backup for creating test DB
6. Check that oracle directory in test DB link on directory where replay files are located, in this scenario we create directory TEST_DIR
7. Initializing the replay Data means initialize the data, which loads the metadata into tables required by workload process:
BEGIN
DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY(REPLAY_NAME => 'TEST_REPLAY',REPLAY_DIR => 'TEST_DIR');
END;

8. Then we mush remap connections with statement:
BEGIN
DBMS_WORKLOAD_REPLAY.REMAP_CONNECTION(CONNECTION_ID => 111);
END;

9. Prepare the Workload for Replay with statement :
BEGIN
DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY();
END;

There we run procedure with default parameters, for example we can use parameter synchronization, by default true, meaning that the commit order of captured workload will be preserved during the workload replay
10. Set up Reply Clients. The reply driver is a special application that consumes the captured workload by sending replay requests to the test DB. The reply client in essence simulates the production system on the test DB. To Find optimal number of clients we must run this statement on test server in terminal window up to directory where is test_dir
wrc system/a mode=calibrate replay_dir=./test_dir
if we should run with 2 or more clients we must open new terminals and run process in other windows.
Run client with this statement:
wrc system/a mode=replay replaydir=./test_dir
11. Now we can start replay process with statement:
BEGIN
DBMS_WORKLOAD_REPLAY.START_REPLAY();
END:

You must start minimum of one wrc client before you can start the workload replay.
You can manually stop Replay process by statement, this is optional, because replay process will finished itself:
BEGIN
DBMS_WORKLOAD_REPLAY.CANCEL_REPLAY();
END;

Status and progress of replay process you can see in view : DBA_WORKLOAD_REPLAYS
12. Report and result of replay process you can see with this statement :



DECLARE
CAP_ID NUMBER;
REP_ID NUMBER;
REP_RPT CLOB;
BUFFER VARCHAR2(32767);
BUFFER_SIZE CONSTANT BINARY_INTEGER := 32767;
AMOUNT BINARY_INTEGER;
OFFSET NUMBER(38);
FILE_HANDLE UTL_FILE.FILE_TYPE;
DIRECTORY_NAME CONSTANT VARCHAR2(80) := 'TEST_DIR';
V_FILENAME CONSTANT VARCHAR2(80) := 'REPLAY.HTML';
BEGIN
CAP_ID := DBMS_WORKLOAD_REPLAY.GET_REPLAY_INFO(DIR => 'TEST_DIR');
SELECT MAX(ID)
INTO REP_ID
FROM DBA_WORKLOAD_REPLAYS
WHERE CAPTURE_ID = CAP_ID ;
REP_RPT := DBMS_WORKLOAD_REPLAY.REPORT(REPLAY_ID => REP_ID,
FORMAT => DBMS_WORKLOAD_REPLAY.TYPE_HTML);
-- WRITE REPORT TO FILE
DBMS_OUTPUT.ENABLE(100000);
-- --------------------------------
-- OPEN NEW XML FILE IN WRITE MODE
-- --------------------------------
FILE_HANDLE := UTL_FILE.FOPEN(LOCATION => 'TEST_DIR',
FILENAME => V_FILENAME,
OPEN_MODE => 'W',
MAX_LINESIZE => BUFFER_SIZE);
AMOUNT := BUFFER_SIZE;
OFFSET := 1;
WHILE AMOUNT >= BUFFER_SIZE
LOOP
DBMS_LOB.READ(LOB_LOC => REP_RPT,
AMOUNT => AMOUNT,
OFFSET => OFFSET,
BUFFER => BUFFER);
OFFSET := OFFSET + AMOUNT;
UTL_FILE.PUT(FILE => FILE_HANDLE,
BUFFER => BUFFER);
UTL_FILE.FFLUSH(FILE => FILE_HANDLE);
END LOOP;
UTL_FILE.FCLOSE(FILE => FILE_HANDLE);
END;







Tags: , ,

increase session_cached_cursors
by Vazha Mantua Monday, November 8, 2010 12:16 PM

Hello all,

We have performance problem on prod DB, and Oracle ADDM advice us increase size of session_cached_cursors parameter.

We decide to find reason of problem.

Here you can find statement which you should run where problem appears.

create table t1 as select sql_text from v$sqlarea;

alter table t1 add sql_text_wo_constants varchar2(3000);

create or replace function
remove_constants( p_query in varchar2 ) return varchar2
as
l_query long;
l_char varchar2(1);
l_in_quotes boolean default FALSE;
begin
for i in 1 .. length( p_query )
loop
l_char := substr(p_query,i,1);
if ( l_char = '''' and l_in_quotes )
then
l_in_quotes := FALSE;
elsif ( l_char = '''' and NOT l_in_quotes )
then
l_in_quotes := TRUE;
l_query := l_query || '''#';
end if;
if ( NOT l_in_quotes ) then
l_query := l_query || l_char;
end if;
end loop;
l_query := translate( l_query, '0123456789', '@@@@@@@@@@' );
for i in 0 .. 8 loop
l_query := replace( l_query, lpad('@',10-i,'@'), '@' );
l_query := replace( l_query, lpad(' ',10-i,' '), ' ' );
end loop;
return upper(l_query);
end;
/
update t1 set sql_text_wo_constants = remove_constants(sql_text);



now we can see result on statement



select sql_text_wo_constants, count(*)
from t1
group by sql_text_wo_constants
having count(*) > 100
order by 2 desc
/

First Results of selects is reason of performance problem.

In these selects please use bind variables, for decrease parsing sql statment

Tags:

Gather schema statistic exluding some tables
by Vazha Mantua Monday, November 8, 2010 12:12 PM

Whenever a valid SQL statement is processed Oracle has to decide how to retrieve the necessary data. This decision can be made using one of two methods:

* Rule Based Optimizer (RBO) - This method is used if the server has no internal statistics relating to the objects referenced by the statement. This method is no longer favoured by Oracle and will be desupported in future releases.
* Cost Based Optimizer (CBO) - This method is used if internal statistics are present. The CBO checks several possible execution plans and selects the one with the lowest cost, where cost relates to system resources.

If new objects are created, or the amount of data in the database changes the statistics will no longer represent the real state of the database so the CBO decision process may be seriously impaired. The mechanisms and issues relating to maintenance of internal statistics are explained below:


Using dbms_stat procedure:

dbms_stat procedure gather statistics for DB object, after gathering them, oracle build right sql_plans and optimize SQL statement.

Example gathering statistic for schema, this statement built SQL text.

select 'execute dbms_stats.gather_table_stats (ownname=>''' || upper(owner)
|| ''', tabname=> ''' || upper(table_name) || ''', degree=>10, cascade=> true);' from DBA_TABLES
where upper(owner) in ('SCOTT') and table_name not in
('TABLE1','TABLE2') order by owner, table_name;

note:
if you have very large tables you can exclude it for quickly gathering statistics

ownname means schema name
degree means that 10 oracle process will start gather information
cascade means that statistics gather for all indexes on tables

Tags:

Filter by APML

Calendar

<<  October 2017  >>
MoTuWeThFrSaSu
2526272829301
2345678
9101112131415
16171819202122
23242526272829
303112345

View posts in large calendar

TextBox