Simple Database Load Testing using Oracle Application Testing Suite

Oracle Application Testing Suite is a very successful product .You can use this product for not only application testing purposes (web,fusion middleware, oracle forms etc…)  but  also  database load testing.

I’d like to show you how to install and prepare a simple database load testing using this product.

Installation  of this production is so simple. I will install this product to  the Win7  32-bit OS on the Oracle VM Virtual Box by using my laptop. You can also use 64-bit OS if possible.

I especially used Win7 since I can not add more CPU to the  Windows XP  by using Oracle VM Virtual Box. However , I can add more cpu if I use Win 7  on the Oracle VM VirtualBox.I think this  a problem related with  virtualization software.

Oracle Application Testing Suite Installation (Step by Step):

Download oats-full-12.2.0.1.288.zip file from otn:

http://www.oracle.com/technetwork/oem/app-test/index-084446.html

Then run setup.bat file for installation.

Choose “Complete” as a type of installation.

Then choose installation folder.

Enter password. This password will be used for  login to the “Load Testing” module.

Allow acces for firewall and start the installation.

Just wait until end of installation.

Click exit and begin to use this product.

Preparation of Test Script using OpenScript Editor (Step By Step)

Open the “OpenScript” Editor and start to create New Project and then select the Database template under General folder.

Type a script name such as TESTDB.

Now  empty script tree has created. Right click on “Run”  menu and start to add step components.

Click on “Other….”  for database components.

Choose  “Connect” component for  new  database connection.

Complete database definition . You should test database connection before following the next step.

Let’s add a “SQL Query “component for a simple query.

I added a simple sql statement which will fetch a single row.  This will provide  minimum overhead to the heap memory during  load testing.

I also added “Think Time” component.  Every database session will wait 5 seconds before disconnection.

Finally. I will add “Disconnect” component and save the script.

You can test this script using Playback.

This is a Result Report after running playback.

Load Testing & Monitoring (Step by Step)

Now, we are ready for Load Testing. We will login to Load  Testing console.

Default user is “administrator”. You should remember password which is the  given password during product  installation.

Choose “Build Scenarios” Tab Page:

You can add “TESTDB” script for  test scenarios. #Vus column shows Virtual User Number which   will run during load test.

Choose “Set up Autopilot”  Tab Page.

I changed “Add per step” to the 20 users. That means  All of the 20 VUs  will run at the same time.

Choose “Watch VU Grid” and RUN TEST by clicking on the Play Button.

All of the Vus is now with running status.

Let’s look at the Top Activity monitoring of the TEST database.  It seems there are CPU waits on the database.

You can observe sqlid in details….

At the end of the load testing. You can also observe  statistics of  load testing by using Reports & Graphs in details.

How to Change the DBID and the DBNAME of Oracle Databases by using NID

Changing  DBNAME and/or  DBID  is one of the steps for cloning Oracle Databases.

You can  change the  database name   by using controlfile creation scripts or using  DBNEWID utility.

Using  controlfile creation script:

You  can create controlfile creation script using this command  with sqlplus when database is on mount or open status:

sqlplus / as sysdba

SQL> alter database backup controlfile to trace as ‘/tmp/ctrl.sql’;

Database altered.

After a quick modify on the first line of  controlfile script into the ‘/tmp/ctrl.sql’ file,
We can create new controlfiles with new database name  on the cloned database.

In addition, we need to add SET command before  DATABASE  when changing DB_NAME.

For examle;

This is the first line of script:

CREATE CONTROLFILE REUSE DATABASE “LIVE” NORESETLOGS FORCE LOGGING ARCHIVELOG

Let’s change first line as:

CREATE CONTROLFILE REUSE SET DATABASE “CLONE“  RESETLOGS  NOARCHIVELOG

After, running  this new ctrl.sql file with nomount state, new controlfiles are created for CLONE database and CLONE database is automatically changed to mount state.Then , you have to open CLONE database with resetlogs option in order to recreate  redolog files for new database.

This method is very useful if you also want to change other controlfile parameters during  database rename  operation such as:

Disable archivelog  mode and disable force logging  (especially for test databases)
Changing  number of redolog groups , redolog members and size of redolog files.
Changing MAX parameters (MAXDATAFILES ,MAXLOGHISTORY  etc…)

You can manually create a copy of a database and give it a new database name (DBNAME) by re-creating the control file.  However, you could not change the database identifier (DBID) by re-creating the control file.

DBID still remains same value for LIVE and CLONE databases after  re-creating the control file on the CLONE database.

Why DBID?

DBID is very important if you are using RMAN  recovery catalog.
You can not register CLONE  database to recovery catalog  due to duplication of  DBID since RMAN recovery catalog uses DBID to distinguish  one  database from another.

Using  DBNEWID  utility:

DBNEWID is a database utility that can change the internal database identifier (DBID) and the database name (DBNAME) for an operational database.

The DBNEWID utility solves this problem by allowing you to change any of the following:

•    Only the DBID of a database
•    Only the DBNAME of a database
•    Both the DBNAME and DBID of a database

I prefer  to change both  DBNAME and DBID  at the same time   as a best practice during creation of  test environments.

Steps for Changing Both  DBNAME and DBID  with  The DBNEWID utility:

1.    We will change both db_name  to CLONE and dbid belongs to cloned database.

Check db_id and db_name for new environment  before operation.

select dbid,name from v$database;

DBID NAME
———- ———
2387774020 LIVE

2.    Startup instance with nomount state and set these parameters .

alter system set instance_name = CLONE scope=spfile;
alter system set db_name = LIVE scope=spfile;

ORACLE_SID parameter should be same with instance_name parameter. Then ,  shutdown the instance and startup with mount option.

export ORACLE_SID=CLONE
sqlplus / as sysdba
shutdown immediate;
startup mount;

3.    Execute nid command and check the log file “/tmp/nid.log” :

oradb@clonesrv:/home/oracle > nid target=/ dbname=CLONE logfile=/tmp/nid.log
oradb@clonesrv:/home/oracle > cat /tmp/nid.log

“/tmp/nid.log” [Read only] 53 lines, 3166 characters
DBNEWID: Release 9.2.0.4.0 – 64bit Production
Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

Connected to database LIVE (DBID=2387774020)

Control Files in database:
/clonedata/CLONE/control01.ctl
/clonedata/CLONE/control02.ctl
/clonedata/CLONE/control03.ctl

Changing database ID from 2387774020 to 2110202187
Changing database name from LIVE to CLONE
Control File /clonedata/CLONE/control01.ctl – modified
Control File /clonedata/CLONE/control02.ctl – modified
Control File /clonedata/CLONE/control03.ctl – modified
Datafile /clonedata/CLONE/system01.dbf – dbid changed, wrote new name
Datafile /clonedata/CLONE/undotbs01.dbf – dbid changed, wrote new name
Datafile /clonedata/CLONE/cwmlite01.dbf – dbid changed, wrote new name
Datafile /clonedata/CLONE/drsys01.dbf – dbid changed, wrote new name
Datafile /clonedata/CLONE/example01.dbf – dbid changed, wrote new name
Datafile /clonedata/CLONE/indx01.dbf – dbid changed, wrote new name
Datafile /clonedata/CLONE/odm01.dbf – dbid changed, wrote new name
Datafile /clonedata/CLONE/tools01.dbf – dbid changed, wrote new name
Datafile /clonedata/CLONE/users01.dbf – dbid changed, wrote new name
Datafile /clonedata/CLONE/xdb01.dbf – dbid changed, wrote new name
Datafile /clonedata/CLONE/perfstat01.dbf – dbid changed, wrote new name
Datafile /clonedata/CLONE/UTIL_THK_IDX2.dbf – dbid changed, wrote new name
Datafile /clonedata/CLONE/UTIL.dbf – dbid changed, wrote new name
Datafile /clonedata/CLONE/UTIL_ABO.dbf – dbid changed, wrote new name
Datafile /clonedata/CLONE/UTIL_THK.dbf – dbid changed, wrote new name
Datafile /clonedata/CLONE/UTIL_TAH.dbf – dbid changed, wrote new name
Datafile /clonedata/CLONE/UTIL_LOG.dbf – dbid changed, wrote new name
Datafile /clonedata/CLONE/UTIL_IDX.dbf – dbid changed, wrote new name
Datafile /clonedata/CLONE/UTIL_ABO_IDX.dbf – dbid changed, wrote new name
Datafile /clonedata/CLONE/UTIL_THK_IDX.dbf – dbid changed, wrote new name
Datafile /clonedata/CLONE/UTIL_TAH_IDX.dbf – dbid changed, wrote new name
Datafile /clonedata/CLONE/oem_repository.dbf – dbid changed, wrote new name
Datafile /clonedata/CLONE/maximo_data.dbf – dbid changed, wrote new name
Datafile /clonedata/CLONE/UTIL02.dbf – dbid changed, wrote new name
Datafile /clonedata/CLONE/UTIL03.dbf – dbid changed, wrote new name
Datafile /clonedata/CLONE/UTIL04.dbf – dbid changed, wrote new name
Control File /clonedata/CLONE/control01.ctl – dbid changed, wrote new name
Control File /clonedata/CLONE/control02.ctl – dbid changed, wrote new name
Control File /clonedata/CLONE/control03.ctl – dbid changed, wrote new name

Database name changed to CLONE.
Modify parameter file and generate a new password file before restarting.
Database ID for database CLONE changed to 2110202187.
All previous backups and archived redo logs for this database are unusable.
Shut down database and open with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID – Completed succesfully.

After DBNEWID  completed successful, instance has been also closed automatically.

4.    Startup instance with nomount option and change the db_name to CLONE.
Then shutdown and startup mount instance again for activate new db_name. At last, open database with resetlogs option.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1663012104 bytes
Fixed Size                   744712 bytes
Variable Size             805306368 bytes
Database Buffers          855638016 bytes
Redo Buffers                1323008 bytes

SQL> show parameter db_name

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_name                              string      LIVE

SQL> alter system set db_name=CLONE  scope=spfile;
System altered.

SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1663012104 bytes
Fixed Size                   744712 bytes
Variable Size             805306368 bytes
Database Buffers          855638016 bytes
Redo Buffers                1323008 bytes

SQL> show parameter db_name

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_name                              string      CLONE

SQL> alter database mount;

SQL> alter database open resetlogs;

5.    Control the value of dbid and name of the new database.

SQL> select dbid, name from v$database;

DBID NAME
———- ———
2110202187 CLONE

6.    You should  create new password file for the new environment if you need

cd $ORACLE_HOME/dbs
orapwd file=orapwCLONE  password=clone entries=3

Using OCFS2 Cluster Filesystem for Rman Backups on the Cluster Nodes

I have  test databases which are running  into  Oracle 11gR2 RAC  Linux 5.5 x86-64 bit.
TEST database is running  first node and KLON database is running on the second node.

[oracle@ds-dm01db01 backup]$ srvctl status database -d TEST
Instance TEST is running on node ds-dm01db01

[oracle@ds-dm01db02 ~]$ srvctl status database -d KLON
Instance KLON is running on node ds-dm01db02

I need to backup these databases to  disk using RMAN but I have only one backup disk for  all of the backupset files. The first choice and simple solution is using nfs in order to mount backup disk from first node  to second node .However, I decided to use OCFS2  Cluster Filesystem to present backup disk to both nodes just  for test purpose as an alternative to NFS.

I followed these steps by using OCFS2 on the test environment:

1. Check the kernel version using the “uname -a” command.

[root@ocfsrv1 tmp]# uname -a

Linux ocfsrv1.home.com 2.6.18-194.el5 #1 SMP Mon Mar 29 22:10:29 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux

2. Download the appropriate version of the OCFS2 kernel module and tools from the following locations

http://oss.oracle.com/projects/ocfs2/files/

http://oss.oracle.com/projects/ocfs2-tools/files/

3. Install these rpm files on the both nodes.

rpm -Uvh ocfs2-tools-1.6.3-2.el5.x86_64.rpm
rpm -Uvh ocfs2-2.6.18-194.el5-1.4.7-1.el5.x86_64.rpm
rpm -Uvh ocfs2console-1.6.3-2.el5.x86_64.rpm
rpm -Uvh ocfs2-tools-debuginfo-1.6.3-2.el5.x86_64.rpm

4. Before configuration,  check for a free port on the cluster nodes.

Port  7777 is not free thus I will use port 7780 during  the configuration.

[root@ds-dm01db01 ~]# telnet localhost 7777

Trying 127.0.0.1…

Connected to localhost.localdomain (127.0.0.1).

Escape character is ‘^]’.

5. Enable o2cb  by using these commands.

[root@ds-dm01db01 ~]# chkconfig o2cb on
[root@ds-dm01db01 ~]# /etc/init.d/o2cb enable
Writing O2CB configuration: OK
Starting O2CB cluster ocfs2: Failed
Cluster ocfs2 created
o2cb_ctl: Configuration error discovered while populating cluster ocfs2.  None of its nodes were considered local.  A node is considered local when its node name in the configuration matches this machine’s host name.
Stopping O2CB cluster ocfs2: OK
If you get a message “cluster not known”. This is normal for now.

6. Start the OCFS2 Console and configure nodes by issuing the following command as the root user.

[root@ds-dm01db01 ~]#ocfs2console

Click Choose Cluster->Configure Nodes…

We can ignore this warning message about o2cb service.

7. Configure Nodes:

Write the cluster nodes in with their local host names (what the command “hostname” returns).

If you get this error message:”o2cb_ctl: Unable to access cluster service while creating node”

Follow these steps and then try again.

[root@ds-dm01db01 ~]# /etc/init.d/ocfs2 stop
Stopping Oracle Cluster File System (OCFS2) [  OK  ]
[root@ds-dm01db01 ~]# /etc/init.d/o2cb offline ocfs2
[root@ds-dm01db01 ~]# /etc/init.d/o2cb unload
Unmounting ocfs2_dlmfs filesystem: OK
Unloading module “ocfs2_dlmfs”: OK
Unmounting configfs filesystem: OK
Unloading module “configfs”: OK
[root@ds-dm01db01 ~]# rm -f /etc/ocfs2/cluster.conf
[root@ds-dm01db01 ~]# ocfs2console

8. Propagate Configuration

Choose Cluster->Propagate Configuration…

9. make sure that the o2cb service is running on all nodes

/etc/init.d/o2cb start

[root@ds-dm01db01 ~]# /etc/init.d/o2cb start

Cluster ocfs2 already online

[root@ds-dm01db02 tmp]# /etc/init.d/o2cb start

Starting O2CB cluster ocfs2: OK

[root@ds-dm01db02 tmp]# /etc/init.d/o2cb start

Cluster ocfs2 already online

10. Reregister the o2cb service using the following commands.

[root@ds-dm01db01 ~]# chkconfig –del o2cb

[root@ds-dm01db01 ~]# chkconfig –add o2cb

[root@ds-dm01db01 ~]# chkconfig –list o2cb

o2cb            0:off   1:off   2:on    3:on    4:on    5:on    6:off

Configure the o2cb service using the following commands.

[root@ds-dm01db01 ~]# /etc/init.d/o2cb offline ocfs2

Stopping O2CB cluster ocfs2: OK

[root@ds-dm01db01 ~]# /etc/init.d/o2cb unload

Unmounting ocfs2_dlmfs filesystem: OK

Unloading module “ocfs2_dlmfs”: OK

Unmounting configfs filesystem: OK

Unloading module “configfs”: OK

[root@ds-dm01db01 ~]#  /etc/init.d/o2cb configure

Configuring the O2CB driver.

This will configure the on-boot properties of the O2CB driver.

The following questions will determine whether the driver is loaded on

boot.  The current values will be shown in brackets (‘[]‘).  Hitting

<ENTER> without typing an answer will keep that current value.  Ctrl-C

will abort.

Load O2CB driver on boot (y/n) [y]:

Cluster stack backing O2CB [o2cb]:

Cluster to start on boot (Enter “none” to clear) [ocfs2]:

Specify heartbeat dead threshold (>=7) [31]:

Specify network idle timeout in ms (>=5000) [30000]:

Specify network keepalive delay in ms (>=1000) [2000]:

Specify network reconnect delay in ms (>=2000) [2000]:

Writing O2CB configuration: OK

Loading filesystem “configfs”: OK

Mounting configfs filesystem at /sys/kernel/config: OK

Loading filesystem “ocfs2_dlmfs”: OK

Mounting ocfs2_dlmfs filesystem at /dlm: OK

Starting O2CB cluster ocfs2: OK

11. Create partition using fdisk

[root@ds-dm01db01 ~]# fdisk /dev/mapper/mpath41
The number of cylinders for this disk is set to 173750.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:

1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs

(e.g., DOS FDISK, OS/2 FDISK)

Command (m for help): n

Command action

e   extended
p   primary partition (1-4)

p

Partition number (1-4): 1
First cylinder (1-173750, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-173750, default 173750):
Using default value 173750
Command (m for help): w
The partition table has been altered!
Calling ioctl() to re-read partition table.
Syncing disks.

[root@ds-dm01db01 ~]# fdisk -l /dev/mapper/mpath41

Disk /dev/mapper/mpath41: 1429.1 GB, 1429150367744 bytes
255 heads, 63 sectors/track, 173750 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot      Start         End      Blocks   Id  System

/dev/mapper/mpath41p1               1      173750  1395646843+  83  Linux

12. Format  partitioned disk using ocfs2console

Start the OCFS2 Console on the first node, using the following command.

# ocfs2console

On the Console screen, select the “Tasks > Format” menu option. Select the appropriate partition and click the “OK” button and confirm the operation by clicking the “Yes” button on the subsequent screen.

1. Mount formatted disk using ocfs2console

Apply this step and mount /backup disk on the both nodes. Then check the backup disk is mounted on the both nodes

[root@ds-dm01db01 /]# df -h

Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup00-VGExaDb_LVDbSys1
39G   18G   20G  47% /
/dev/mapper/VolGroup00-VGExaDb_LVDbOra1
161G   57G   96G  38% /u01
/dev/sda1              99M   13M   81M  14% /boot
tmpfs                  32G  950M   32G   3% /dev/shm
/ISO/Enterprise-R5-U5-Server-x86_64-dvd.iso
3.4G  3.4G     0 100% /var/repo
zoomrecorder:/nfs/calls
12T  313G   12T   3% /archivecalls
/dev/dm-33            1.3T  2.4G  1.3T   1% /backup

[root@ds-dm01db02 tmp]# df -h

Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup00-VGExaDb_LVDbSys1
39G  8.2G   29G  23% /
/dev/mapper/VolGroup00-VGExaDb_LVDbOra1
30G   19G  9.9G  66% /u01
/dev/sda1              99M   13M   81M  14% /boot
tmpfs                  32G  238M   32G   1% /dev/shm
/ISO/Enterprise-R5-U5-Server-x86_64-dvd.iso
3.4G  3.4G     0 100% /var/repo
dsbergama:/zonekapadokya/root/data05
402G  221G  181G  55% /data05
/dev/dm-32            1.3T  2.4G  1.3T   1% /backup

13.  Add backup disk to  /etc/fstab to automount the filesystem.

For node1:

/dev/dm-33     /backup    ocfs2   _netdev,datavolume     0 0

For node2:

/dev/dm-32     /backup    ocfs2   _netdev,datavolume     0 0

References:

http://en.wikipedia.org/wiki/OCFS2

http://www.oracle-base.com/articles/linux/ocfs2-on-linux.php

http://linux.dell.com/wiki/index.php/Set_up_an_OCFS2_cluster_filesystem

http://d-h-n.de/blog/ocfs2console-o2cb_ctl-unable-to-access-cluster-service-while-creating-node

SQL WorkShop About Oracle Architecture

– DataBase Version : Oracle XE 10g
– Startup and shutdown database
– Create database
– Control files
– Redo log files
– Table spaces and data files
– Rollback segments
– Temporary segments

SQL > conn sys/pswd as sysdba;
SQL >SHUTDOWN ABORT
SQL >SHUTDOWN IMMEDIATE
SQL >SHUTDOWN NORMAL
SQL >SHUTDOWN TRANSACTIONAL
SQL >STARTUP PFILE='filepath/initXE.ora'

SQL >CREATE DATABASE BD1
 MAXLOGFILES 5
 MAXLOGMEMBERS 5
   MAXDATAFILES 100
LOGFILE
    GROUP 1 '[path]/log1a.rdo' SIZE 100 M,
    GROUP 2 '[path]/log2a.rdo' SIZE 100 M
DATAFILE
    '[path]/system01.dbf' size 200 M
SQL >exec catalog.sql -- create dictionary views
SQL >exec catproc.sql -- create pl/sql scripts for server 

– accessible for nomount and above

SQL >select * from v$parameter
SQL >select * from v$sga
SQL >select * from v$process
SQL >select * from v$session
SQL >select * from v$version
SQL >select * from v$instance

– accessible for mount and above

SQL >select * from v$thread
SQL >select * from v$controlfile
SQL >select * from v$database
SQL >select * from v$datafile
SQL >select * from v$logfile

– how to terminate a user session

SQL >select sid, serial# from v$session where username = 'HR';

SID SERIAL#
———- ———-
37 100

SQL> alter system kill session '37,100';
System altered.

– redo log files.

SQL> select group#, sequence#, bytes,members, status from v$log

SQL> select * from v$logfile

– adding new log file rdolog3.rdo

SQL> ALTER DATABASE ADD LOGFILE
('C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ONLINELOG\RDOLOG3.rdo') size 10M;

– add redo log file to group 1

SQL>ALTER DATABASE ADD LOGFILE MEMBER
'C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ONLINELOG\RDOLOG3.rdo' TO GROUP 1
SQL> ALTER DATABASE DROP LOGFILE GROUP 3

SQL> ALTER DATABASE DROP LOGFILE MEMBER 'C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ONLINELOG\RDOLOG3.rdo'
SQL>CREATE TABLESPACE app_data
DATAFILE '[path]/app01.dbf' SIZE 100M,
         '[path]/app02.dbf' SIZE 100M
MINIMUM EXTENT 500K
DEFAULT STORAGE (INITIAL 500K
                   NEXT 500K
                   MINEXTENTS 3
                   MAXEXTENTS 500
                   PCTINCREASE 50 );

– INITIAL size of first extent. size of blocks
– NEXT size of second extent. size of blocks
– MINEXTENTS num of extents for created segments
– PCTINCREASE extent size grows
– MAXEXTENTS max num of extents for segments

– adding new tablespace

SQL > ALTER TABLESPACE app_data ADD DATAFILE ('[file_path]/app03.dbf' SIZE 200M);
SQL > ALTER TABLESPACE app_data ADD DATAFILE '[file_path]/app04.dbf' SIZE 200M
AUTOEXTEND ON NEXT 10M MAXSIZE 500M;

– enable autoextension for a datafile

SQL > ALTER DATABASE DATAFIEL '[file_path]/app03.dbf'
AUTOEXTEND ON NEXT 10M MAXSIZE 500M;

– changing the size of Data Files Manually

SQL > ALTER DATABASE DATAFILE '[file_path]/app02.dbf' RESIZE 200M;

– make tablespace read only
– must not contain rollback segments
– must not involved in a online backup

SQL > ALTER TABLESPACE app_data READ ONLY;
SQL > select tablespace_name, contents, status from dba_tablespaces;
SQL > select tablespace_name, initial_extent,next_extent, max_extents, pct_increase, min_extlen
from dba_tablespaces;
SQL > select file_name, tablespace_name, bytes, autoextensible, maxbytes, increment_by from dba_data_files;

– DataBlock Parameters
– INITRANS
– MAXTRANS
– PCTFREE
– PCTUSED

– about storage

SQL > select segment_name, tablespace_name, extents, blocks from dba_segments where owner = 'SYS'
SQL > select extent_id, file_id, block_id, blocks
from dba_extents
where owner='SYS' and segment_name = 'ERROR$'
SQL > select tablespace_name, COUNT(*),max(blocks), sum(blocks) from dba_free_space group by tablespace_name;

– if changes made to rallback segment are also
– protected by redo log files we can rollback all changes
– after unconditional instance fails

– request a rollback segment for this transaction

SQL > select segment_name,SEGMENT_TYPE from dba_segments where owner = 'SYS' and segment_type = 'ROLLBACK'

– SET TRANSACTION USE ROLLBACK SEGMENT SYSTEM
– create new rollback segment

SQL > CREATE ROLLBACK SEGMENT rlbs01
TABLESPACE SYSTEM
STORAGE (INITIAL 100K
         NEXT 100 K
         OPTIMAL 4M
         MINEXTENTS 20
         MAXEXTENTS 100 );

– make it online

SQL > ALTER ROLLBACK SEGMENT rbls01 online
SQL > ALTER ROLLBACK SEGMENT rbls01 offline
SQL > DROP ROLLBACK SEGMENT rbls01;

– list rollback segments

SQL > select segment_name, tablespace_name, owner, status from dba_rollback_segs;

– show status of rollback segments

select usn,extents,rssize,optsize,hwmsize,xacts,status from v$rollstat

– if SORT_AREA_SIZE is full, swap data to temporary segments.

SQL > select * from dba_segments
SQL > select * from v$sort_segment
SQL > create view large_table_view as
select * from dba_source
   UNION ALL select * from dba_source
   UNION ALL select * from dba_source
   UNION ALL select * from dba_source;

– new session for sort operation

SQL > select DISTINCT * from large_table_view

– show currently active sorts (run using different session )

SQL > select * from v$sort_usage

– deallocate of unused (after extend defragmentation ) extents
– to be able to use by other segments

SQL > ALTER TABLE hr.t1 DEALLOCATE UNUSED;

– ANALYZE TABLE
– validate integrity of data blocks in the table.

SQL > ANALYZE TABLE hr.t1 VALIDATE STRUCTURE;

– compute statistics

SQL > ANALYZE TABLE hr.t1 COMPUTE STATISTICS;

SQL Trace and TKPROF

– start sql trace to the session.

set session for trace sql
alter session set timed_statistics = true;

alter session set sql_trace = true;

alter session set max_dump_file_size =unlimited;
alter session set STATISTICS_LEVEL = ALL ;

alter session set statistics_level = all ;
alter session set tracefile_identifier = ‘pfzerprb’;
alter session set events ’10046 TRACE NAME CONTEXT FOREVER, LEVEL 8′;

–stop sql trace for the session after execution.
alter session set sql_trace = false;

–where is sql trace files? find [filepath]
select value from v$parameter where name = ‘user_dump_dest’

– make trace files more readable using tkprof
tkprof [filepath]\filename.TRC D:\sql_trace.txt explain= [user/psw] sys=no waits=yes

Row Numbering and Ranking Functions

1. ROWNUM without Analytic Function

select employee_id, name , salary, ROWNUM
from employees ORDER BY salary;exec

2. ROW_NUMBER ( )

select employee_id, name, salary, ROW_NUMBER() OVER (ORDER BY salary  )toprank
FROM employees;exec

3. RANK ( )

select employee_id, name, salary, RANK() OVER (ORDER BY salary )toprank
FROM employees;exec

4. DENSE_RANK ( ) with WHERE clause

select * from
( select employee_id, name, salary, DENSE_RANK() OVER (ORDER BY salary  desc ) toprank
FROM employees )t
where t.toprank < 3;exec

5. RANK ( ) with order by

select employee_id, first_name, salary, RANK() OVER (ORDER BY salary  desc) toprank
FROM employees
order by salary;exec

6. RANK ( ) with a join

select e.employee_id, e.name, j.job_title, e.salary,
RANK() OVER (ORDER BY e.salary desc) rankorder
from employees e, jobs j
where e.salary < 3000
and e.job_id = j.job_id
order by salary;exec

7. RANK ( ) contains a join, group by and order by

select j.job_title, COUNT(*), MAX(e.salary) maxsalary,
MIN(e.salary) minsalary,
RANK() OVER (ORDER BY MAX(salary)) rankorder
FROM employees e, jobs j
where e.salary < 5000
AND e.job_id = j.job_id
GROUP BY j.job_title
ORDER BY j.job_title;exec

8. RANK and DENSE_RANK ( ); GROUP BY, HAVING and ORDER BY clause

More than one analytical function can be used in one sql.

select j.job_title,COUNT(*),MAX(salary) maxsalary,
MIN(salary) minsalary,RANK () OVER (ORDER BY MAX(salary)) rankorder,
DENSE_RANK () OVER (ORDER BY MIN(salary)) rankorder
FROM employees e, jobs j
WHERE e.salary < 6000
AND e.job_id = j.job_id
GROUP BY j.job_title
HAVING MIN(salary) > 2000
ORDER BY j.job_title desc;exec

9. ROW_NUMBER ( ) and NULLS LAST

select e.employee_id, e.name, e.salary curr_salary ,
ROW_NUMBER () OVER (ORDER BY salary desc NULLS LAST ) salary
from employees e
ORDER BY curr_salary desc NULLS LAST;exec

10. RANK ( ),  NVL function and NULLS LAST

SELECT e.employee_id, e.name, NVL (salary, 9999),
RANK () OVER (ORDER BY NVL (salary,9999) desc NULLS LAST) salary
from empnls e
where employee_id < 115
ORDER BY salary desc NULLS LAST;exec

11. RANK ( ), Order by after NLV function

SELECT employee_id,
name,
NVL(salary, 6666),
RANK() OVER (ORDER BY NVL (salary, 6666) desc) curr_salary
FROM empnls
ORDER BY curr_salary;exec

12. RANK ( ), Partitioning with PARTITION_BY

SELECT employee_id,
       name,
       department_id,
       salary,
       RANK() OVER(PARTITION BY department_id ORDER BY salary desc) rank
  FROM employees
  ORDER BY department_id;exec

13.  NTILE ( )

This function must apply same segment at the same intervals except last interval and if the remaining segment is equals or more than rownum, segment is always equals rownum.

Interval = (ROUND(rowcount /segment));

SELECT employee_id,
       name,
       salary,
       NTILE(4) OVER(ORDER BY salary desc) ntlist4,
       NTILE(8) OVER(ORDER BY salary desc) ntlist8,
       NTILE(100) OVER(ORDER BY salary desc) ntlist107,
       NTILE(120) OVER(ORDER BY salary desc) ntlist120
 FROM employees;exec

Case expression in sql

We can use case expression in PL/SQL basically or as a sql expression to extend query

flexibility.

Basic Sytax :

case when <condition> then <value>
when <condition> then <value>
...
else <value

end

Basic Example :

select sal, case when sal < 2000 then 'low' 
                 when sal < 3000 then 'middle' 
                 when sal < 4000 then 'high' 
                 else 'top' 
            end as "categories"
from emp;