1 / 18

Oracle Castor Administration

Oracle Castor Administration. Nilo Segura Chinchilla Oracle Support IT/DES CERN. Agenda. Database Service Database Software Database Creation Performance/Tuning Backups Service evolution Q/A. Database Service. Standard CERN disk server Dual CPU, 2Gb memory and RAID 1+0

brynn-miller
Download Presentation

Oracle Castor Administration

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Oracle Castor Administration Nilo Segura Chinchilla Oracle Support IT/DES CERN

  2. Agenda • Database Service • Database Software • Database Creation • Performance/Tuning • Backups • Service evolution • Q/A

  3. Database Service • Standard CERN disk server • Dual CPU, 2Gb memory and RAID 1+0 • RDBMS 10.2.0.2 + CPU October 2006 • No one-off patches • Using RMAN for backups (full + incremental) • Enabling change block tracking to reduce load during incremental backup • Patch Set 10.2.0.3 should arrive before Christmas • To be installed in the new Castor DB infrastructure • Some tests with Oracle 11g Alpha.

  4. Database Software: Installation • Use Oracle’s standard(supported) runInstaller • Working on automatic scriptable installations • Rpm wrapper around runInstaller silent script ? • Custom: no spatial, no http, no EM Console • Unix oracle account with gid <> dba • Forces re-link of executables (always good idea..) • Oracle’s Enterprise Management Agent + CERN Lemon system

  5. Database creation I • 8k db block size • 16k for DLF looks o.k.. • spfile (no init.ora files any longer) • Tablespaces : extent management local + segment space management auto + autoextend off • 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 II • sga_max_target for dynamic adjustment of several oracle caches • db_cache_size, shared_pool_size • pga_aggregate_target • workspace_policy=auto • sga_target disabled • db_cache_advice disabled in production • generates high contention on some internal latches with high load… • …but enabled until we get the right value for db_cache_size

  7. Peformance/Tuning • Most of the performance achieved via application tuning • Change of physical layout (iot,partitions) • Missing indexes (normal & Function based) • Automatic Workload Repository is the main tuning tool • $ORACLE_HOME/rdbms/admin/awrrpt • Top wait events, SQL ordered by consistent gets…

  8. Top 5 Timed Events Avg %Total ~~~~~~~~~~~~~~~~~~ wait Call Event Waits Time (s) (ms) Time WaitClass db file sequential read 1,431,831 4,707 3 50.6 User I/O resmgr:become active 5,045 2,891 573 31.1 Scheduler latch: cache buffers chains 31,616 750 24 8.1 Concurrency CPU time 670 7.2 log file sync 4,226 40 10 0.4 Commit

  9. 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 70hx3aq93qqw0 Module: stager@c2publicsrv03.cern.ch (TNS V1-V3) SELECT /*+ INDEX (CastorFile) INDEX (DiskCopy) INDEX (FileSystem) INDEX (DiskSer ver) 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 2usn5f6wbc75r Module: 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 gn88ssqqfgtrv Module: stager@c2publicsrv03.cern.ch (TNS V1-V3) UPDATE SubRequest SET status = 3 WHERE (decode(status,0,status,1,status,2,statu s,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,

  10. 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. Logical Owner Name Object Name Type Reads %Total CASTOR_STA STAGER_DAT SYS_C0015152 INDEX 46,968,912 25.45 CASTOR_STA STAGER_DAT SYS_C0015149 INDEX 46,965,456 25.45 CASTOR_STA STAGER_DAT SYS_C0015150 INDEX 30,657,792 16.61 CASTOR_STA STAGER_DAT SYS_C0015179 INDEX 15,050,080 8.16 CASTOR_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. Physical Owner Name Object Name Type Reads %Total CASTOR_STA STAGER_DAT SUBREQUEST TABLE 738,205 51.19 CASTOR_STA STAGER_DAT STAGEPREPARETOGETREQ TABLE 432,335 29.98 CASTOR_STA STAGER_DAT STAGEGETREQUEST TABLE 243,535 16.89 CASTOR_STA STAGER_DAT CASTORFILE TABLE 13,454 .93 CASTOR_STA STAGER_DAT I_SUBREQUEST_CASTORF INDEX 6,458 .45

  11. Perf&Tuning: Statistics • Froze DB statistics once we were happy with the results • Disabled automatic DB statistics gathering • To avoid unpleasant changes in the execution plans • Moved statistics from one DB to another to obtain same execution plans • Neither system nor Dictionary statistics • To be added later on (needs testing)

  12. Perf&Tuning:Things we tested… • Resource Manager : bad… • Caused database locks up when the activity was high (not possible to login) • Online table redefinition : good… • Useful to change physical table structure or remove the fragmentation of key tables

  13. Perf&Tuning: Problems • DISKCOPY/SUBREQUESTs tables are the weakest point in the chain.. • Experiments Denial of service attacks (aka ooops!) can cause huge increase in their size • 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: Problems • Application deadlocks cause sessions to be killed • Being addressed by the Castor Dev team • Emphasizes the importance of keeping up with current Castor releases • Require installation of hot fixes

  15. Perf&Tuning: Added problem • We (DB support team) do not know the Castor logic • More difficult for us to be helpful… • 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… • 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 layout • IOT, Partitioning, Cluster already applied to some tables • Materializev views for some quasi-static joins (filesystem,diskserver,diskpool2svclass) • PL/SQL code profiling

  17. Service evolution • Moving to Oracle certified RAC hardware (host + storage) on Red Hat Enterprise Edition 4 64bits • 2 Dual Core CPU + 8Gb memory • NAS storage (RAC certified filesystem) • No need of ASM :) • Expected to be in production 1Q07

  18. And now for something completely different… Questions? (ask first, shoot later)

More Related