Delete Trigger example in Sybase

1> sp_helptext empage_delete_restrict
2>
# Lines of Text
---------------
1
(1 row affected)
text
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
create trigger empage_delete_restrict on empage
for delete
as
begin
print 'Delete of records not allowed in this table'
rollback transaction
end

(1 row affected)
(return status = 0)
1> select * from empage
2>
empid empage
----------- -----------
654 23
76 45
6 43
100 29
65 60
(5 rows affected)
1> delete from empage where empid = 100
2>
Delete of records not allowed in this table
1> select * from empage
2>
empid empage
----------- -----------
654 23
76 45
6 43
100 29
65 60
(5 rows affected)
1>

Update Trigger Example Sybase

create trigger empdata_to_upd_empage on empdata
for update
as
if update (empid)
begin
update empage set empage.empid = inserted.empid
from
inserted,empage,deleted
where deleted.empid=empage.empid
end

(1 row affected)
(return status = 0)
1> select * from empdata
2>
empname empid
-------------------- -----------
k 654
m 76
o 6
t 98
w 65
(5 rows affected)
1> select * from empage
2>
empid empage
----------- -----------
654 23
76 45
6 43
98 29
65 60
(5 rows affected)

1> update empdata set empid=100 where empname='t'
2>
(1 row affected)
1> select * from empdata
2>
empname empid
-------------------- -----------
k 654
m 76
o 6
t 100
w 65
(5 rows affected)
1> select * from empage
2>
empid empage
----------- -----------
654 23
76 45
6 43
100 29
65 60
(5 rows affected)
1>

Sybase ASE 15.0.2 Job Scheduler Setup

JOB SCHEDULER IN SYBASE


Step 1 Create a raw device for sybmgmtdb database


/opt/VRTSvxfs/sbin/qiomkfile -s 75m /OracleDumps/opctlsy2/sybmgmtdev
aloncvc1004:opctlsy2 >/opt/VRTSvxfs/sbin/qiomkfile -s 75m /OracleDumps/opctlsy2/sybmgmtdev

Step 2 Initialise unix raw device to create allocation units for dataserver

1> disk init
2> name=sybmgmtdev,
3> physname='/OracleDumps/opctlsy2/sybmgmtdev',
4> size='75M'


Step 3 Run the installjsdb script in $SYBASE/$SYBASE-ASE/scripts which will create the database sybmgmtdb on the device sybmgmtdev

aloncvc1004:opctlsy2 >isql -Usa -SGALLARDO -iinstalljsdb -oinstalljsdb.out
Password:*****

Starting the setup of sybmgmtdb.
Using device sybmgmtdev for the sybmgmtdb database.
CREATE DATABASE: allocating 33280 logical pages (65.0 megabytes) on disk
'sybmgmtdev'.
CREATE DATABASE: allocating 5120 logical pages (10.0 megabytes) on disk
'sybmgmtdev'.
Caution: You have set up this database to include space on disk 55 for both
data and the transaction log. This can make recovery impossible if that disk
fails.
Database 'sybmgmtdb' is now online.
Database option 'trunc log on chkpt' turned ON for database 'sybmgmtdb'.
Running CHECKPOINT on database 'sybmgmtdb' for option 'trunc log on chkpt' to
take effect.
(return status = 0)
Database option 'select into/bulkcopy/pllsort' turned ON for database
'sybmgmtdb'.
Running CHECKPOINT on database 'sybmgmtdb' for option 'select
into/bulkcopy/pllsort' to take effect.
(return status = 0)
Executing checkpoint in sybmgmtdb
Database log is 5120 pages, 30% log free is at 1536 pages, the last chance
threshold is at 376 pages.
installing sp_js_logthreshold at 1536 pages.
Adding threshold for segment 'logsegment' at '1536' pages.
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
(return status = 0)
Created js_jobs.
Created js_commands.
Created js_schedules.
Created js_scheduledjobs.
Created js_seqsql.
Created js_callouts.
Created js_history.
Created js_output.
Created js_keys.
Created js_templates.
Created js_xml.
Created js_daynames.

Step 4 Add the below entry into interfaces file, note that port you choose should be avaialable and not in use


GALLARDO_JSAGENT
master tcp ether opctlsy2 5030
query tcp ether opctlsy2 5030

Step 5 Add the server into sysserver table

1> sp_addserver SYB_JSAGENT,null,GALLARDO_JSAGENT
2>
Adding server 'SYB_JSAGENT', physical name 'GALLARDO_JSAGENT'
Server added.
(return status = 0)

Step 6 Add the local server as loopback server onto sysservers as this will be referred by JS Scheduler and JS Agent


1> sp_addserver loopback,ASEnterprise,GALLARDO
2>
Adding server 'loopback', physical name 'GALLARDO'
Server added.
(return status = 0)

1> sp_helpserver
2>
name network_name class status id cost
----------- ---------------- ------------ ------------------------------------------------------------------------ -- ----
FERRARI FERRARI ASEnterprise no timeouts, no net password encryption, writable , rpc security model A 4 1000
GALLARDO GALLARDO ASEnterprise 0 0
GALLAREP GALLAREP sql_server no timeouts, no net password encryption, writable , rpc security model A 5 1000
GALLARIBO GALLARIBO ASEnterprise no timeouts, no net password encryption, writable , rpc security model A 6 1000
SYB_BACKUP GALLARDO_BS ASEnterprise timeouts, no net password encryption, writable , rpc security model A 1 0
SYB_JSAGENT GALLARDO_JSAGENT ASEnterprise no timeouts, no net password encryption, writable , rpc security model A 7 1000
SYB_JSTASK GALLARDO ASEnterprise timeouts, no net password encryption, writable , rpc security model A 8 1000
local GALLARDO ASEnterprise no timeouts, no net password encryption, writable , rpc security model A 3 1000
loopback GALLARDO ASEnterprise no timeouts, no net password encryption, writable , rpc security model A 2 1000
(return status = 0)



Step 7 Set up Sybase configuration parameters to start Job Scheduler

1> sp_configure 'job'
2>
Msg 17411, Level 16, State 1:
Server 'GALLARDO', Procedure 'sp_configure', Line 278:
Configuration option is not unique.

Parameter Name Default Memory Used Config Value Run Value Unit Type
------------------------------ ----------- ----------- ------------ ------------ -------------------- ----------
enable job scheduler 0 0 0 0 switch dynamic
job scheduler interval 1 0 1 1 seconds dynamic
job scheduler tasks 32 0 32 32 number dynamic
maximum job output 32768 0 32768 32768 bytes dynamic

(1 row affected)
(return status = 1)
1> sp_configure 'enable job scheduler', 1
2>
Parameter Name Default Memory Used Config Value Run Value Unit Type
------------------------------ ----------- ----------- ------------ ------------ -------------------- ----------
enable job scheduler 0 0 1 1 switch dynamic

(1 row affected)
Configuration option changed. ASE need not be rebooted since the option is dynamic.
Changing the value of 'enable job scheduler' to '1' increases the amount of memory ASE uses by 18694 K.
(return status = 0)
1>


Step 8 Start up Sybase Job Scheduler

Even a server recycle will start the JS with following message in sybase log

00:00000:00013:2009/08/03 14:39:18.94 kernel Initializing Job Scheduler Task
00:00000:00013:2009/08/03 14:39:19.01 kernel Installed Job Scheduler sequencer code version 0.27 - 27 tokens
00:00000:00013:2009/08/03 14:39:19.12 kernel Job Scheduler Task started with a clean 'js_history' table
00:00000:00013:2009/08/03 14:39:20.20 kernel Job Scheduler Task state set to running, startcount 1.
00:00000:00013:2009/08/03 14:39:21.20 kernel Job Scheduler Task connected with Agent on port 5030

OR


1> sp_js_wakeup 'start_js', 1
2>
(1 row affected)
(return status = 1)
1>

Step 9 Add a login with js_admin_role, js_user_role, js_client_role which you will use to kick and create jobs

1> sp_addlogin job_scheduler, job_scheduler, sybmgmtdb,NULL,'JS ADMIN ROLE'
2>
Password correctly set.
Account unlocked.
New login created.
(return status = 0)
1> use sybmgmtdb
2>
1> sp_adduser job_scheduler,job_scheduler
2>
New user added.
(return status = 0)
1>

1> sp_role 'grant',js_admin_role,job_scheduler
2>
Authorization updated.
(return status = 0)
1> sp_role 'grant',js_user_role,job_scheduler
2>
Authorization updated.
(return status = 0)
1>


Step 10 Need to register the login as a remote login to all the target servers where you need to run jobs out of JS


1> sp_addexternlogin FERRARI,job_scheduler,job_scheduler,job_scheduler
2>
User 'job_scheduler' will be known as 'job_scheduler' in remote server 'FERRARI'.
(return status = 0)
1>

For Local server add the following

1> sp_addexternlogin loopback,job_scheduler,job_scheduler,job_scheduler
2>
User 'job_scheduler' will be known as 'job_scheduler' in remote server 'loopback'.
(return status = 0)
1>


Step 11 If you use Sybase Central you can install the forllowing sps and templates using which you can create jobs for dump, reorg, statistics, rebuild etc


TEMPLATES

aloncvc1004:opctlsy2 >pwd
/OracleDumps/opctlsy2/sybase/ASE-15_0/jobscheduler/Templates/sprocs
aloncvc1004:opctlsy2 >


aloncvc1004:opctlsy2 >./installTemplateProcs 'GALLARDO' 'sa' '*****'
Creating generic stored procedures used by Sybase Job Scheduler Template jobs
Generic stored procedure used by Sybase Job Scheduler Template jobs
sp_jst_check_reorg_space has been created
Generic stored procedure used by Sybase Job Scheduler Template jobs
sp_jst_database_names has been created
Generic stored procedure used by Sybase Job Scheduler Template jobs
sp_jst_get_index_names has been created
Generic stored procedure used by Sybase Job Scheduler Template jobs
sp_jst_get_datachange has been created
Generic stored procedure used by Sybase Job Scheduler Template jobs
sp_jst_get_datestr dropped
sp_jst_get_datestr has been created
Generic stored procedure used by Sybase Job Scheduler Template
jobs
sp_jst_get_freespace has been created

TEMPLATE PROCS

aloncvc1004:opctlsy2 >./installTemplateProcs 'GALLARDO' 'sa' '*****'
Creating generic stored procedures used by Sybase Job Scheduler Template jobs
Generic stored procedure used by Sybase Job Scheduler Template jobs
sp_jst_check_reorg_space has been created
Generic stored procedure used by Sybase Job Scheduler Template jobs
sp_jst_database_names has been created
Generic stored procedure used by Sybase Job Scheduler Template jobs
sp_jst_get_index_names has been created
Generic stored procedure used by Sybase Job Scheduler Template jobs
sp_jst_get_datachange has been created
Generic stored procedure used by Sybase Job Scheduler Template jobs
sp_jst_get_datestr dropped
sp_jst_get_datestr has been created
Generic stored procedure used by Sybase Job Scheduler Template
jobs
sp_jst_get_freespace has been created
Creating support Stored Procedure for Sybase Reconfigure Metadata Cache Template
created procedure sp_jst_get_mdcache_type
sp_jst_get_spaceused has been created
Creating Support Stored Procedure for Sybase Reconfigure Metadata Cache Template
created procedure sp_jst_reconf_mdcache_type
Generic stored procedure used by Sybase Job Scheduler Template jobs
sp_jst_verion_num has been created
Generic stored procedure used by Sybase Job Scheduler Template jobs
sp_jst_purge_alphachars has been created
sp_jst_valid_ase_version has been created
Creating job-specific stored procedures used by Sybase Job Scheduler Templates
Creating Sybase Delete Statistics Template stored procedures
sp_jst_delete_stats has been created
sp_jst_dump_db_to_disk has been created
Creating Stored Procedures for Sybase Backup Database to Disk Template
sp_jst_dump_dbs_to_disk has been created
Creating Sybase Back-up Log Template stored procedures
Creating Stored Procedures for Sybase Dump Logs Template
sp_jst_dump_tran_log_to_disk has been created
sp_jst_chk_row_threshold has been created
sp_jst_chk_time_threshold has been created


Step 12 Go ahead and create jobs and schedule them :)

Create job

declare @jobcmd varchar(255)
select @jobcmd='jcmd=YOUR COMMAND OR PROC,server=GALLARDO'
exec sp_sjobcreate 'sjname=3_job', @jobcmd

Attach a schedule

1> sp_sjobmodify @name='first_job', @option='sname=mysched'
2>
(return status = 0)
1> sp_sjobmodify 'sname=mysched' , @option='repeats=1day, starttime=03:00pm'
2>
(return status = 0)
1>

sp_sjobhelp


sjob_id: 6 name: 'first_job'
owner : sa
created : Aug 4 2009 2:15PM
state : enabled
job name : 7 - 'job_7'
schedule name : 7 - 'mysched'
server : GALLARDO
-- job --------:
description :
owner : sa
created : Aug 4 2009 2:15PM
-- schedule ---:
description :
owner : sa
created : Aug 4 2009 2:15PM
repeating : every day
starttime : 15:00
startdate : 04 Aug 2009


(1 row affected)
(return status = 0)
1> sp_sjobcmd 'first_job', 'list'
2>
job_id job_name jcmd_seqno jcmd_text
7 job_7 0 use master
7 job_7 1
7 job_7 2 go
7 job_7 3 exec sp_myproc
7 job_7 4
7 job_7 5 go



Default log location is the server log location :

GALLARDO_JSAGENT.log

Parameter Passing in proxy tables designed to store sp output

A. Pre requisites: Need to have cis properties enabled, loopback server added in sysservers, and setting up an externlogin to loopback from your local
B. Create existing table :
create existing table procinfo ( procname varchar(20), owner varchar(10), tranmode varchar(20) , _procname varchar(767) null )
external procedure at "loopback.sybsystemprocs.dbo.sp_procxmode"
Here the last column in actually the parameter to the procedure sp_procxmode with same name but distinguished by an _ ( underscore )
You can use this in the table select where clause as shown later:

C. Now start selecting ;)

1> select * from procinfo
2>
procname owner tranmode _procname
-------------------- ---------- -------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
sp_activeroles dbo Any Mode NULL
sp_add_qpgroup dbo Any Mode NULL
sp_add_resource_limi dbo Any Mode NULL
This will take the _procname as NULL as no where clause passed and thus give a listing of sp_procxmode output as stored in procinfo table


1> select * from procinfo where procname='sp_listsuspect_db'
2>
procname owner tranmode _procname
-------------------- ---------- -------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
sp_listsuspect_db dbo Any Mode NULL
This will pass the parameter @procname = 'sp_listsuspect_db' as this has been mentioned in where clause and return only for the procedure.
We can use this feature to capture results of stored procedure in tables by providing different parameters…
Cheers,
Ajit

Pagelinkage APL - Another way to check for fragmentation

SELECT name, first FROM sysindexes WHERE indid not in (0,1) AND id = object_id('PREVPOS')
Non clustered index.

The above gives you the frst page of the leaf level of the nci. Basically our aim is to see how much fragmentation is there in the index leaf level pages

dbcc pglinkage(4,808536,0,2,0,1)
We can view haphazard page chain which increases disk seek times, fragmentation
NCI highly fragmented

This will show you the links from one page to another, and you can observe if their are multiple jumps in between pages in different extets or the pages are organised in a sequential increasing manner.
A new index will have sequential pages, but as there will be more inserts, deletes on index keys, the index gets fragmented.

Storing a stored procedure output into a table

We can use proxy tables to store the output of a procedure into a table. This requires CIS to be enabled on your system


1> create existing table mytable ( LOGIN varchar(20), ALIASEDTO varchar(20),DATABASENAME varchar(20), ALIASUSER varchar(20) )
2> external procedure at "local.sybsystemprocs.dbo.sp_alias"
3>
1> select * from mytable
2>

This will execute the procedure and send you the result in table, which you can use in other queries.

Make sure to add a local server to the sysservers system table.

Tuning Backup Server dump and load operations

We can use following traces to give us a better picture about what goes on when a dump or load happens

Following traces are present as a part of backupserver binary:

BACKUP SERVER TRACES

Backup Server trace flag: 1 is on. This flag does the following: Prints blocksize used during a dump or a load.
Backup Server trace flag: 2 is on. This flag does the following: Prints i/o optimization parameters used during a dump or a load.
Backup Server trace flag: 3 is on. This flag does the following: Allow dumping to the /dev/null device.
Backup Server trace flag: 4 is on. This flag does the following: Prints allocation percentage of the allocation units during dump.
Backup Server trace flag: 5 is on. This flag does the following: Prints the database pagesize used during a dump or a load.

Commands to set them on/off

Using 0 will display the traces set

SYB_BACKUP...qatraceon 0,1,2,3,4,5
SYB_BACKUP...qatraceoff 1,2,3,4,5

Findings:

Increasing stripes upto a certain amount can minimise number of io made or time taken during a dump or load by sybmultbuf to read /write on dumps/devices.
Increasing shared memory available to backup server via -m on backup run server can also help on performance tuning of dumps and loads.

There are few important terms in traces which give us an idea about the io sizes, number of zones used

Dbiosize the database io size to be used on the dump file during dumping a particular stripe
Iocount Total number of IO made for the dump or load session
Zonesize Not very clear but aids performance if the numzones are higher in number
Blocksize the block size to be used
Numzones the more the numzones , the better is performance ( ranges from 1 to 5 )



Dump Cases Considered under tracing for qatraceon 1,2 and 3 :

A.dumping into one stripe with shared memory 100MB

1> dump database sag to '/OracleDumps/opctlsy2/sag.dmp'

2> Backup Server session id is: 33. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server.
Backup Server: 4.173.1.1: The database pagesize is '2048' bytes.
Backup Server: 6.28.1.1: Dumpfile name 'sag1000609C09 ' section number 1 mounted on disk file '/OracleDumps/opctlsy2/sag.dmp'
Backup Server: 4.166.1.1: Using dbiosize of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp.
Backup Server: 4.165.1.1: Using iocount of 7 for device /OracleDumps/opctlsy2/sag.dmp.
Backup Server: 4.166.1.2: Using zonesize of 458752 bytes for device /OracleDumps/opctlsy2/sag.dmp.
Backup Server: 4.166.1.3: Using blocksize of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp.
Backup Server: 4.165.1.2: Using numzones of 2 for device /OracleDumps/opctlsy2/sag.dmp.
Backup Server: 4.155.1.1: Using maximum block size of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp.

Notice that the iocount is 7, dbiosize is 65536 bytes, numzones are 2

B. dumping into one stripe with shared memory double

1> dump database sag to '/OracleDumps/opctlsy2/sag.dmp'
2>
Backup Server session id is: 12. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server.
Backup Server: 6.28.1.1: Dumpfile name 'sag100060A4F5 ' section number 1 mounted on disk file '/OracleDumps/opctlsy2/sag.dmp'
Backup Server: 4.166.1.1: Using dbiosize of 262144 bytes for device /OracleDumps/opctlsy2/sag.dmp.
Backup Server: 4.165.1.1: Using iocount of 7 for device /OracleDumps/opctlsy2/sag.dmp.
Backup Server: 4.166.1.2: Using zonesize of 1835008 bytes for device /OracleDumps/opctlsy2/sag.dmp.
Backup Server: 4.166.1.3: Using blocksize of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp.
Backup Server: 4.165.1.2: Using numzones of 3 for device /OracleDumps/opctlsy2/sag.dmp.
Backup Server: 4.155.1.1: Using maximum block size of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp.
Adaptive Server cpu time: 0 ms. Adaptive Server elapsed time: 4030 ms.

Notice the iocount 7 but dbiosize 262144 bytes, numzones 3, time is 4030 ms

C.dumping on to 2 stripes

dump database sag to '/OracleDumps/opctlsy2/sag.dmp1' stripe on '/OracleDumps/opctlsy2/sag.dmp2'
Backup Server session id is: 33. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server.
Backup Server: 6.28.1.1: Dumpfile name 'sag100060A9C7 ' section number 1 mounted on disk file '/OracleDumps/opctlsy2/sag.dmp1'
Backup Server: 4.166.1.1: Using dbiosize of 524288 bytes for device /OracleDumps/opctlsy2/sag.dmp1.
Backup Server: 4.165.1.1: Using iocount of 3 for device /OracleDumps/opctlsy2/sag.dmp1.
Backup Server: 4.166.1.2: Using zonesize of 1572864 bytes for device /OracleDumps/opctlsy2/sag.dmp1.
Backup Server: 4.166.1.3: Using blocksize of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp1.
Backup Server: 4.165.1.2: Using numzones of 4 for device /OracleDumps/opctlsy2/sag.dmp1.
Backup Server: 4.155.1.1: Using maximum block size of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp1.
Backup Server: 6.28.1.1: Dumpfile name 'sag100060A9C7 ' section number 1 mounted on disk file '/OracleDumps/opctlsy2/sag.dmp2'
Backup Server: 4.166.1.1: Using dbiosize of 524288 bytes for device /OracleDumps/opctlsy2/sag.dmp2.
Backup Server: 4.165.1.1: Using iocount of 3 for device /OracleDumps/opctlsy2/sag.dmp2.
Backup Server: 4.166.1.2: Using zonesize of 1572864 bytes for device /OracleDumps/opctlsy2/sag.dmp2.
Backup Server: 4.166.1.3: Using blocksize of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp2.
Backup Server: 4.165.1.2: Using numzones of 4 for device /OracleDumps/opctlsy2/sag.dmp2.
Backup Server: 4.155.1.1: Using maximum block size of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp2.
Adaptive Server cpu time: 0 ms. Adaptive Server elapsed time: 3980 ms.

Notice iocount reduced to 6, numzones are 4, dbiosize is 524288, time taken 3980 ms - Performance better as less io, less time


D.dumping to 3 stripes

dump database sag to '/OracleDumps/opctlsy2/sag.dmp1' stripe on '/OracleDumps/opctlsy2/sag.dmp2' stripe on '/OracleDumps/opctlsy2/sag.dmp3'
Backup Server: 6.28.1.1: Dumpfile name 'sag100060A5E2 ' section number 1 mounted on disk file '/OracleDumps/opctlsy2/sag.dmp2'
Backup Server: 4.166.1.1: Using dbiosize of 524288 bytes for device /OracleDumps/opctlsy2/sag.dmp2.
Backup Server: 4.165.1.1: Using iocount of 2 for device /OracleDumps/opctlsy2/sag.dmp2.
Backup Server: 4.166.1.2: Using zonesize of 1048576 bytes for device /OracleDumps/opctlsy2/sag.dmp2.
Backup Server: 4.166.1.3: Using blocksize of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp2.
Backup Server: 4.165.1.2: Using numzones of 4 for device /OracleDumps/opctlsy2/sag.dmp2.
Backup Server: 4.155.1.1: Using maximum block size of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp2.
Backup Server: 6.28.1.1: Dumpfile name 'sag100060A5E2 ' section number 1 mounted on disk file '/OracleDumps/opctlsy2/sag.dmp1'
Backup Server: 4.166.1.1: Using dbiosize of 524288 bytes for device /OracleDumps/opctlsy2/sag.dmp1.
Backup Server: 4.165.1.1: Using iocount of 2 for device /OracleDumps/opctlsy2/sag.dmp1.
Backup Server: 4.166.1.2: Using zonesize of 1048576 bytes for device /OracleDumps/opctlsy2/sag.dmp1.
Backup Server: 4.166.1.3: Using blocksize of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp1.
Backup Server: 4.165.1.2: Using numzones of 4 for device /OracleDumps/opctlsy2/sag.dmp1.
Backup Server: 4.155.1.1: Using maximum block size of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp1.
Backup Server: 6.28.1.1: Dumpfile name 'sag100060A5E2 ' section number 1 mounted on disk file '/OracleDumps/opctlsy2/sag.dmp3'
Backup Server: 4.166.1.1: Using dbiosize of 524288 bytes for device /OracleDumps/opctlsy2/sag.dmp3.
Backup Server: 4.165.1.1: Using iocount of 2 for device /OracleDumps/opctlsy2/sag.dmp3.
Backup Server: 4.166.1.2: Using zonesize of 1048576 bytes for device /OracleDumps/opctlsy2/sag.dmp3.
Backup Server: 4.166.1.3: Using blocksize of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp3.
Backup Server: 4.165.1.2: Using numzones of 4 for device /OracleDumps/opctlsy2/sag.dmp3.
Backup Server: 4.155.1.1: Using maximum block size of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp3.
Adaptive Server cpu time: 100 ms. Adaptive Server elapsed time: 4026 ms.

Notice iocount total 6, dbiosize 524288 but zonesize has reduced per dump still it uses numzones as 4, time is 4026ms


E. dumping to 4 stripes

dump database sag to '/OracleDumps/opctlsy2/sag.dmp1' stripe on '/OracleDumps/opctlsy2/sag.dmp2' stripe on '/OracleDumps/opctlsy2/sag.dmp3' stripe on '/OracleDumps/opctlsy2/sag.dmp4'
Backup Server session id is: 26. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server.
Backup Server: 4.41.1.1: Creating new disk file /OracleDumps/opctlsy2/sag.dmp4.
Backup Server: 6.28.1.1: Dumpfile name 'sag100060A65F ' section number 1 mounted on disk file '/OracleDumps/opctlsy2/sag.dmp4'
Backup Server: 4.166.1.1: Using dbiosize of 1048576 bytes for device /OracleDumps/opctlsy2/sag.dmp4.
Backup Server: 4.165.1.1: Using iocount of 1 for device /OracleDumps/opctlsy2/sag.dmp4.
Backup Server: 4.166.1.2: Using zonesize of 1048576 bytes for device /OracleDumps/opctlsy2/sag.dmp4.
Backup Server: 4.166.1.3: Using blocksize of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp4.
Backup Server: 4.165.1.2: Using numzones of 4 for device /OracleDumps/opctlsy2/sag.dmp4.
Backup Server: 4.155.1.1: Using maximum block size of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp4.
Backup Server: 6.28.1.1: Dumpfile name 'sag100060A65F ' section number 1 mounted on disk file '/OracleDumps/opctlsy2/sag.dmp1'
Backup Server: 4.166.1.1: Using dbiosize of 1048576 bytes for device /OracleDumps/opctlsy2/sag.dmp1.
Backup Server: 4.165.1.1: Using iocount of 1 for device /OracleDumps/opctlsy2/sag.dmp1.
Backup Server: 4.166.1.2: Using zonesize of 1048576 bytes for device /OracleDumps/opctlsy2/sag.dmp1.
Backup Server: 4.166.1.3: Using blocksize of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp1.
Backup Server: 4.165.1.2: Using numzones of 4 for device /OracleDumps/opctlsy2/sag.dmp1.
Backup Server: 4.155.1.1: Using maximum block size of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp1.
Backup Server: 6.28.1.1: Dumpfile name 'sag100060A65F ' section number 1 mounted on disk file '/OracleDumps/opctlsy2/sag.dmp2'
Backup Server: 4.166.1.1: Using dbiosize of 1048576 bytes for device /OracleDumps/opctlsy2/sag.dmp2.
Backup Server: 4.165.1.1: Using iocount of 1 for device /OracleDumps/opctlsy2/sag.dmp2.
Backup Server: 4.166.1.2: Using zonesize of 1048576 bytes for device /OracleDumps/opctlsy2/sag.dmp2.
Backup Server: 4.166.1.3: Using blocksize of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp2.
Backup Server: 4.165.1.2: Using numzones of 4 for device /OracleDumps/opctlsy2/sag.dmp2.
Backup Server: 4.155.1.1: Using maximum block size of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp2.
Backup Server: 6.28.1.1: Dumpfile name 'sag100060A65F ' section number 1 mounted on disk file '/OracleDumps/opctlsy2/sag.dmp3'
Backup Server: 4.166.1.1: Using dbiosize of 1048576 bytes for device /OracleDumps/opctlsy2/sag.dmp3.
Backup Server: 4.165.1.1: Using iocount of 1 for device /OracleDumps/opctlsy2/sag.dmp3.
Backup Server: 4.166.1.2: Using zonesize of 1048576 bytes for device /OracleDumps/opctlsy2/sag.dmp3.
Backup Server: 4.166.1.3: Using blocksize of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp3.
Backup Server: 4.165.1.2: Using numzones of 4 for device /OracleDumps/opctlsy2/sag.dmp3.
Backup Server: 4.155.1.1: Using maximum block size of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp3.
Adaptive Server cpu time: 0 ms. Adaptive Server elapsed time: 3983 ms.

Notice the iocount as 4 now, dbiosize of 1048576 bytes, numzones as 4

Similarly we can tune the loads based on the traced optimisation parameters.