1

Oracle Castor Oracle Castor AdministrationAdministration

Nilo Segura ChinchillaOracle Support IT/DES

CERN

2

AgendaAgenda

Database Service

Database Software

Database Creation

Performance/Tuning

Backups

Service evolution

Q/A

3

Database ServiceDatabase Service

Standard CERN disk server o Dual CPU, 2Gb memory and RAID 1+0

RDBMS 10.2.0.2 + CPU October 2006o No one-off patches

Using RMAN for backups (full + incremental)o Enabling change block tracking to reduce load during incremental backup

Patch Set 10.2.0.3 should arrive before Christmaso To be installed in the new Castor DB infrastructure

Some tests with Oracle 11g Alpha.

4

Database Software: Database Software: InstallationInstallation

Use Oracle’s standard(supported) runInstallero Working on automatic scriptable installations

• Rpm wrapper around runInstaller silent script ?

Custom: no spatial, no http, no EM Console

Unix oracle account with gid <> dbao Forces re-link of executables (always good idea..)

Oracle’s Enterprise Management Agent + CERN Lemon system

5

Database creation IDatabase creation I

8k db block size o 16k for DLF looks o.k..

spfile (no init.ora files any longer) Tablespaces : extent management local + segment space management auto + autoextend offo Required for ENABLE ROW MOVEMENT + ALTER TABLE.. SHRINK….

Automatic undo space management,Temporary tablespaces (tempfiles)

Four to five groups of redo log files, each 400-500Mb approx.

6

Database creation IIDatabase creation II

sga_max_target for dynamic adjustment of several oracle cacheso db_cache_size, shared_pool_sizeo pga_aggregate_target

• workspace_policy=auto

sga_target disabled db_cache_advice disabled in production

o generates high contention on some internal latches with high load…

o …but enabled until we get the right value for db_cache_size

7

Peformance/TuningPeformance/Tuning

Most of the performance achieved via application tuningo Change of physical layout (iot,partitions)

o Missing indexes (normal & Function based)

Automatic Workload Repository is the main tuning tool o $ORACLE_HOME/rdbms/admin/awrrpto Top wait events, SQL ordered by consistent gets…

Top 5 Timed Events Avg %Total~~~~~~~~~~~~~~~~~~ wait CallEvent Waits Time (s) (ms) Time WaitClass

db file sequential read 1,431,831 4,707 3 50.6 User I/Oresmgr:become active 5,045 2,891 573 31.1 Schedulerlatch: cache buffers chains 31,616 750 24 8.1 ConcurrencyCPU time 670 7.2log file sync 4,226 40 10 0.4 Commit

SQL ordered by Gets DB/Inst: CASTORSG/CASTORSG Snaps: 26112-26113-> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.-> Total Buffer Gets: 184,550,148-> Captured SQL account for 99.2% of Total

Gets CPU Elapsed Buffer Gets Executions per Exec %Total Time (s) Time (s) SQL Id

182,937,840 963 189,966.6 99.1 651.46 6195.24 70hx3aq93qqw0Module: stager@c2publicsrv03.cern.ch (TNS V1-V3)SELECT /*+ INDEX (CastorFile) INDEX (DiskCopy) INDEX (FileSystem) INDEX (DiskServer) INDEX (SubRequest) */ UNIQUE CASTORFILE.FILEID, CASTORFILE.NSHOST, DISKCOPY.ID, DISKCOPY.PATH, CASTORFILE.FILESIZE, NVL(DISKCOPY.STATUS, DECODE(SUBREQUEST.STATUS, 0,2, 3,2, -1)), DISKSERVER.NAME, FILESYSTEM.MOUNTPOINT, CASTORFILE.NBACC

31,837 2,889 11.0 0.0 0.81 2.66 2usn5f6wbc75rModule: stager@c2publicsrv03.cern.ch (TNS V1-V3)DELETE FROM Id2Type WHERE id = :1

24,780 1,180 21.0 0.0 0.50 1073.30 gn88ssqqfgtrvModule: stager@c2publicsrv03.cern.ch (TNS V1-V3)UPDATE SubRequest SET status = 3 WHERE (decode(status,0,status,1,status,2,status,NULL)) < 3 AND ROWNUM < 2 AND (SELECT type FROM Id2Type WHERE id = SubRequest.request) IN (35, 36, 119, 40, 37, 44, 38, 42, 95, 39) RETURNING id, retryCounter, fileName, protocol, xsize, priority, status, modeBits, flags INTO :1, :2, :3,

LSegments by Logical Reads DB/Inst: CASTORSG/CASTORSG Snaps: 26112-26113-> Total Logical Reads: 184,550,148-> Captured Segments account for 99.5% of Total

Tablespace Obj. LogicalOwner Name Object Name Type Reads %Total

CASTOR_STA STAGER_DAT SYS_C0015152 INDEX 46,968,912 25.45CASTOR_STA STAGER_DAT SYS_C0015149 INDEX 46,965,456 25.45CASTOR_STA STAGER_DAT SYS_C0015150 INDEX 30,657,792 16.61CASTOR_STA STAGER_DAT SYS_C0015179 INDEX 15,050,080 8.16CASTOR_STA STAGER_DAT SYS_C0015151 INDEX 14,613,504 7.92

Segments by Physical Reads DB/Inst: CASTORSG/CASTORSG Snaps: 26112-26113-> Total Physical Reads: 1,441,997-> Captured Segments account for 100.2% of Total

Tablespace Obj. PhysicalOwner Name Object Name Type Reads %Total

CASTOR_STA STAGER_DAT SUBREQUEST TABLE 738,205 51.19CASTOR_STA STAGER_DAT STAGEPREPARETOGETREQ TABLE 432,335 29.98CASTOR_STA STAGER_DAT STAGEGETREQUEST TABLE 243,535 16.89CASTOR_STA STAGER_DAT CASTORFILE TABLE 13,454 .93CASTOR_STA STAGER_DAT I_SUBREQUEST_CASTORF INDEX 6,458 .45

11

Perf&Tuning: StatisticsPerf&Tuning: Statistics

Froze DB statistics once we were happy with the results

Disabled automatic DB statistics gatheringo To avoid unpleasant changes in the execution plans

Moved statistics from one DB to another to obtain same execution plans

Neither system nor Dictionary statisticso To be added later on (needs testing)

12

Perf&Tuning:Things we Perf&Tuning:Things we tested…tested…

Resource Manager : bad…o Caused database locks up when the activity was high (not possible to login)

Online table redefinition : good…o Useful to change physical table structure or remove the fragmentation of key tables

13

Perf&Tuning: ProblemsPerf&Tuning: Problems

DISKCOPY/SUBREQUESTs tables are the weakest point in the chain..o Experiments Denial of service attacks (aka ooops!) can cause huge increase in their size

o Tables become Emmental cheese (holes due to deletes)• High Water Mark does not move, index space not properly reused due to the use of sequences

• Require regular de-fragmentation– DBMS_REDEFINITION for online reorganization– Do not use alter table move + alter table rebuild (only if you stop the Stagers first)

14

Perf&Tuning: ProblemsPerf&Tuning: Problems

Application deadlocks cause sessions to be killedo Being addressed by the Castor Dev team

o Emphasizes the importance of keeping up with current Castor releases

o Require installation of hot fixes

15

Perf&Tuning: Added problemPerf&Tuning: Added problem

We (DB support team) do not know the Castor logico More difficult for us to be helpful…o Can only offer improvements based on what it is written•But real optimized solution could mean to change the algorithm logic!!!

It is crucial to have close ties with the Castor Dev team

16

Perf&Tuning: Working on…Perf&Tuning: Working on…

Adding missing DB constraints KEEP/RECYCLE buffer cache feature Uniforms size extents in tablespaces PL/SQL Native compilation (enabled in Test instance)

Further improvements on physical table layouto IOT, Partitioning, Cluster already applied to some tables

o Materializev views for some quasi-static joins (filesystem,diskserver,diskpool2svclass)

PL/SQL code profiling

17

Service evolutionService evolution

Moving to Oracle certified RAC hardware (host + storage) on Red Hat Enterprise Edition 4 64bitso 2 Dual Core CPU + 8Gb memory

o NAS storage (RAC certified filesystem)•No need of ASM :)

o Expected to be in production 1Q07

18

And now for something And now for something completely different…completely different…

Questions?(ask first, shoot later)