Pete Jones Manager Data Applications Mgt Atradius 24

Pete Jones Manager, Data & Applications Mgt Atradius 24 March 2004 Peter Jones z. Series Oracle SIG – Page 1

Presentation Agenda : • Introductions. What ever happened to Jon Dartnell? • Brief overview of Atradius. • Implementing Oracle Cost Based Optimiser at Atradius. • Next Oracle objectives. • Oracle 9 i Initial Impressions and… a few thoughts on problem solving. (Paul Mansfield). 24 March 2004 Peter Jones z. Series Oracle SIG – Page 2

Introductions Robin Burge, Manager, Central Systems Infrastructure (CSI) Paul Mansfield, Senior z/Os Oracle Database Engineer (CSI) Pete Jones, Manager, Data & Applications Mgt. (D&AM) ATRADIUS: formerly GERLINGNCM, previously NCM. 24 March 2004 Peter Jones z. Series Oracle SIG – Page 3

Brief Overview of Atradius 24 March 2004 Peter Jones z. Series Oracle SIG – Page 4

Brief Overview of Atradius Main Datacentre: UK, Wales, Cardiff - 160 miles directly East of London, England. UK – houses the Datacentre and has the preponderance of IT resources for Atradius. UK is a Branch office (ITS plus business approx. 560 staff) Headquarters: Netherlands, Amsterdam. (ITS plus business approx. 640 staff) Other Europe: Cologne (Germany), Namur (Belgium), Paris (France), Italy (Rome), Nordic countries (Denmark, Sweden, Norway, Finland) Poland (other E. European countries). Rest of the World: USA, Canada, Mexico, Australia, New Zealand, Japan, China, India Total Staff: 3, 500 (310 IT Staff). Main Lines of Business: Credit Insurance administered through a Group Wide System, Credit Management, Bonding, Factoring. Financials: Number two in the world credit insurance market with a total turnover of € 1. 3 billion, a 25% market share worldwide. 24 March 2004 Peter Jones z. Series Oracle SIG – Page 5

Technical Overview Platforms: · IBM z 900 Series, 2064/2 C 5 (4 LPARS - 5 CPU’s, 10 gb memory) · 12 Terabytes EMC , IBM 3494 Tape library · IFL – ready for VM/Linux · AIX – 1 x P 690 (8 LPARS - 26 CPU’s, 81 gb memory), 8 x P 610’s. · Intel/Linux – New this year! http: //www. atradius. com 24 March 2004 Peter Jones z. Series Oracle SIG – Page 6

Core Business Applications Internal Users ‘SYMPHONY’ – Group System 10+yrs old! · Oracle Forms 6 - access via Citrix, otherwise Forms on Desktop. l d. B Oracle 8. 7. 1. 4. 50 z/OS (size 207 gb) l Oracle Financials v 11. 5. 8 l Other systems supporting Symphony: Imaging system (Filenet) l Cobol Batch (TWS 8. 2) l Customer Reports d. B (Oracle – AIX ) l Pricing d. B (Oracle - AIX) l DWH (Oracle - AIX). l l Many Test environments (x 16 full volume on z/OS) 24 March 2004 Peter Jones z. Series Oracle SIG – Page 7

Core Business Applications External Customers ‘SERV@NET’ - ‘http: //www. atradius. com/serv@net’ l IBM Apache and Web. Sphere v 4. 2 l CTG, CICS l Same Backend d. B as Symphony l Java development - outsourced (THBS) 24 March 2004 Peter Jones z. Series Oracle SIG – Page 8

What have we achieved in the past year? INTEGRATION, INTEGRATION… which has prevented us from do anything strategic with Oracle. But we did manage to squeeze in something…. Implemented Oracle Cost Base Optimiser (CBO). 24 March 2004 Peter Jones z. Series Oracle SIG – Page 9

Implementing Oracle Cost Based Optimiser at Atradius 24 March 2004 Peter Jones z. Series Oracle SIG – Page 10

Why CBO for Atradius? 1. RBO is no longer supported under 10 g. 2. Although it is not a physical prerequisite for migrating to 9 i, it would be a logical and common sense move to migrate to CBO first and get it stable. 3. There a host of CBO features that we cannot use or properly use and these are starting to rear their heads as Symphony is developed. SOLD! Do we have a Choice? that was the easy bit…… 24 March 2004 Peter Jones z. Series Oracle SIG – Page 11

Implemented CBO by ‘hook or by crook’ - a managers view… PRE Project planning: 1. Not an easy project! Goal was 3 months to do it in. 2. Lack of resource available right from the beginning (QA and developers) – Really needed more attention and commitment! 3. No clear strategy on how to implement CBO e. g. % of table to be analysed – there always conflicting information. 4. DBA Research! Test, analyse results and more research. 24 March 2004 Peter Jones z. Series Oracle SIG – Page 12

Implement CBO by ‘hook or by crook’ a managers view… Some challenges we faced: 1. Impacts on testing for on-going projects – when to cut them over to CBO. 2. Impact on the release management process – weekly changes, when do we need to re-analyse a Table/index… how to implement that in Test > Production. 3. How to manage CBO once in production. 4. Impact on performance! No firm idea until we were in production. 5. Contingency! we missed the first implementation date. 24 March 2004 Peter Jones z. Series Oracle SIG – Page 13

Implement CBO by ‘hook or by crook’ a managers view… Production preparation tips: 1. Finally received acceptance 6 months later (3 months late) – expect problems in Production! 2. For Week 1&2 ensure plenty of resource to react to production problems. 3. Communication and problem management - manage user expectations. 4. Implementation Timing: Choose a ‘quiet’ week e. g. school holiday, so system would be quieter by default. 5. A weekend implementation and perform as MUCH LIVE WORK as you can on that weekend. 6. Back-out plan!! Test it well first – requirement minimum down time… 7. As a manager book your vacation and get out of the country! ; 0) 24 March 2004 Peter Jones z. Series Oracle SIG – Page 14

Implement CBO by ‘hook or by crook’ a managers view… Post Implementation Ø First Day: Top SQL statements doing full table scans from disk – CPU up 100%. System was holding out! Skiing vacation was excellent! (Paul will walk through some of the issues in more detail). Ø Week 1: Very bad statements were fixed and going into second week CPU had dropped down, but still an increase of 25%. Ø Week 2: Skiing over : ( and a month later we are still stable and managing the remaining/new performance issues. 24 March 2004 Peter Jones z. Series Oracle SIG – Page 15

Implement CBO by ‘hook or by crook’ a managers view… Conclusion: Overall not impressed and disappointed! Ø Performance – yet to see any performance benefits. Ø DBA’s tell me its more difficult to manage e. g. DB reorgs more complicated and always risks that execution paths will change after a reorg – more performance testing, overhead on changes. Ø Two months on and we still need to reduce our CPU! Ø Still need to remove rule hints, which were used to quickly fix CBO performance problems. Ø Still a steep learning curve for all my DBA’s. CONCLUSION: A successful Project… CBO by hook or by crook… 24 March 2004 Peter Jones z. Series Oracle SIG – Page 16

Next ‘Oracle‘ objectives for Atradius 24 March 2004 Peter Jones z. Series Oracle SIG – Page 17

………………. . what next ? Ø Upgrade to 9. 2 – planning under way – Paul has some initial technical feedback on this. Ø Web Forms – Proof Of Concept - strategic option is to go to a J 2 EE application, but have to take Web Forms as first step! Worrying factor is how a generated ‘web’ application will perform? Ø Linux/VM – We are looking to push this forward starting with our web Infrastructure (Serv@net). Robin & I have yet to agree time scales for running Oracle on this platform. Ø 24 by 6. Ø Oracle Financials upgrade to 11. 5. 9 24 March 2004 Peter Jones z. Series Oracle SIG – Page 18

Oracle 9 i Initial Impressions and… a few thoughts on problem solving Author: Paul Mansfield, IT Services Date: March 24, 2004 24 March 2004 Peter Jones z. Series Oracle SIG – Page 19

A few gripes before I start • CBO under 8 i is pretty bad - hope it’s better under 9 i • A lot of the 9 i installation documentation is generic and it’s very confusing to know which bits apply to Z/OS • Patches very fragmented and. . . why do we end up with everything when we just selected a subset on the initial install • The ‘new’ route of going thru a middle level agent before speaking to mainframe support isn’t working - it just delays things 24 March 2004 Peter Jones z. Series Oracle SIG – Page 20

9 i Initial Thoughts • We wanted to run 8. 1. 7. 4 and 9. 2. 0. 4 in same LPAR with a minimal number of IPLs during cutover and we wanted to keep the same service names. • Changing TYPE= in your SERVICE definitions ( NET 8 to NET and ORA 8 to ORA) requires an IPL. • Documentation says you MUST change the TYPE= but not true under 9. 2. 0. 4. • Can get away with one IPL. 24 March 2004 Peter Jones z. Series Oracle SIG – Page 21

Oracle 8 and 9 services can run alongside each other under the same SSN but must use the new 9 i LLA modules DEFINE SERVICE ORAB DESC('Oracle ORAB Database') TYPE(ORA) PROC(ORAORAB) MAXAS(1) PARM('ORACLE. V 9 R 2 M 0. PARMLIB(O 9 PARM)') DEFINE SERVICE NET 9 PROCEDURE(ORANET 9) TYPE(NET) DESCRIPTION('NET 9 V 9. 2. 0 OSDI TEST') PARM('HPNS PORT(1551) OSUSER DUMP(ORACLE. NET 9. DUMP. OUTPUT)') DEFINE SERVICE ORAX PROCEDURE(ORAORAX) TYPE(ORA 8) DESCRIPTION('ORACLE DB V 8. 1. 7 ODSI ORAX TEST') MAXAS(2) PARM('ORACLE. V 8 R 1 M 7. PARMLIB(O 8 PARM)') DEFINE SERVICE NET 8 PROCEDURE(ORANET 8) TYPE(NET 8) DESCRIPTION('NET 8 V 8. 1. 7 OSDI TEST') PARM('HPNS PORT(1549) OSUSER DUMP(ORACLE. NET 8. DUMP 2. OUTPUT)') 24 March 2004 Peter Jones z. Series Oracle SIG – Page 22

You could try writing a program to issue commands to switch from an 8 i service to a 9 i service //ORABSWAP JOB (0000, OR), 'ORACLE SWAP', CLASS=A, // MSGCLASS=X, PRTY=15, MSGLEVEL=(1, 1), NOTIFY=&SYSUID //STC EXEC PGM=COMMAND, PARM='OSDI STOP ORAB' //WAIT EXEC PGM=WAIT, PARM='10' //STC EXEC PGM=COMMAND, // PARM='OSDI ALTER SERVICE ORAB PROC(ORAB 9204)' //STC EXEC PGM=COMMAND, // PARM='OSDI ALTER SERVICE ORAB PARM('ORACLE. V 9 R 2 M 0. PARMLIB(O 9 PARM)')' //WAIT EXEC PGM=WAIT, PARM='10' //STC EXEC PGM=COMMAND, PARM='OSDI START ORAB' // (sample kindly provided by Oracle Support) 24 March 2004 Peter Jones z. Series Oracle SIG – Page 23

A few changes we have to make to old jobs/procs etc • No more SVRMGRL • Audit records can no longer be cut to SMF datasets • Construct CONNECT ABC/EDF@Z: ORAB no longer works 24 March 2004 Peter Jones z. Series Oracle SIG – Page 24

Precompilers In the past we used both COBOL precompilers i. e PROCOB and PROCOB 18. We would like to cutover to one precompiler with 9 i but ‘tightening up’ in PROCOB precompiler means program changes are needed. • Must use NODYNAM option and therefore programs should be changed to use CALL WS-VAR rather than CALL “PROG 1”. (also applies to v 8) • Use PICX=VARCHAR 2 option (no longer the default) in precompiler step otherwise tests on fields with trailing blanks will include the blanks in comparisons. (also applies to v 8) • Compilations now produce a PM 3 object rather than a load module at Linkedit time. These must be stored in a PDSE. You can produce a traditional load module if required. • For the SYSPUNCH DD in the PROCOB step you must specify RECFM=FB as part of the DCB information i. e DCB=(RECFM=FB, LRECL=80, BLKSIZE=800) • All CICS programs have to be recompiled. 24 March 2004 Peter Jones z. Series Oracle SIG – Page 25

Access Manager for CICS • Documentation says you must set distributed_transactions parameter in INITORA. This is an obsolete parameter. • The LIBCLNTS module is massive (25 meg). I found I needed the CICS region size to be 40 meg greater than the EDSA size in the SIT to get it to load (why so big? !!). • Some of our programs still had a ‘CONNECT /’ this no longer works. 24 March 2004 Peter Jones z. Series Oracle SIG – Page 26

Upgrade process • Documentation rather frustrating. Seems to assume everyone going from MPM to OSDI. • Couldn’t get upgrade to work with 9. 2. 0. 2. (ORA-03113: end of file on communication channel) Tried using 9. 2. 0. 4 and went very smoothly. • Control Files now larger • Don’t use SGA_MAX_SIZE • DB_CACHE_SIZE instead of DB_BLOCK_BUFFERS, i. e db_block_buffers=500 becomes db_cache_size=2000 k • Even at 9. 2. 0. 4 experienced hangs when issuing OSDI STOP commands (oh no! not more patches). 24 March 2004 Peter Jones z. Series Oracle SIG – Page 27

DO YOU EVER FEEL LIKE THIS? ! DEVELOPMENT 24 March 2004 Peter Jones z. Series Oracle SIG – Page 28

A few thoughts on problem solving Some of the tools available: • Statspack or V$ queries • IPCS • LOGMNR • Traces • RMF monitor (enclave resource consumption) • SMF stats 24 March 2004 Peter Jones z. Series Oracle SIG – Page 29

My favourite V$ query SELECT SQL_TEXT, BUFFER_GETS, EXECUTIONS, DECODE(EXECUTIONS, 0, 1, BUFFER_GETS/EXECUTIONS) BUFF_EXEC, OPTIMIZER_MODE, FIRST_LOAD_TIME, LOADS, DISK_READS, PARSING_USER_ID FROM V£SQLAREA WHERE BUFFER_GETS > 10000 ORDER BY BUFFER_GETS; Look out for any newcomers at the bottom of the heap. We recently found the following: 24 March 2004 Peter Jones z. Series Oracle SIG – Page 30

select procedure_catalog, procedure_schema, procedure_name, parameter_name, ordinal_position, parameter_type, parameter_hasdefault, parameter_default, is_nullable, data_type, character_maximum_length character_octet_length, numeric_precision, numeric_scale, description, type_name, overload from (select null procedure_catalog, owner procedure_schema, decode(package_name, NULL, object_name, package_name||'. '||object_name) procedure_name, decode(position, 0, 'RETURN_VALUE', nvl(argument_name, chr(0))) parameter_name, position ordinal_position, decode(in_out, 'IN', 1, 'IN/OUT', 2, 'OUT', decode(argument_name, null, 4, 3), null) parameter_type, null parameter_hasdefault, null parameter_default, null is_nullable, decode(data_type, 'CHAR', 129, 'NCHAR', 129, 'DATE', 135, 'FLOAT', 139, 'LONG', 129, 'LONG RAW’ , 128, 'NUMBER', 139, 'RAW', 128, 'ROWID', 129, 'VARCHAR 2', 129, 'NVARCHAR 2', 129, 13) data_type, decode(data_type, 'CHAR', decode(data_length, null, 2000, data_length), 'LONG', 2147483647, 'LONG RAW', 2147483647, 'ROWID', 18, 'RAW', decode(data_length, null, 2000, data_length), 'VARCHAR 2', decode(data_length, null, 4000, data_length), 'DATE', null, 'FLOAT', null, 'NUMBER', null) character_maximum_length, decode(data_type, 'DATE', 19, 'FLOAT', 15, 'NUMBER', decode(data_precision, null, 0, data_precision), 'CHAR', null, 'NCHAR', null, 'LONG RAW', null, 'VARCHAR 2', null, 'NVARCHAR 2', null) numeric_precision, decode(data_type, 'DATE', 0, 'NUMBER', decode(data_scale, null, 0, data_scale), 'CHAR', null, 'NCHAR', null, 'FLOAT', null, 'LONG RAW', null, 'VARCHAR 2', null, 'NVARCHAR 2', null) numeric_scale, null description, data_type_name, overload from all_arguments where data_level = 0 and data_type is not null) procedure_parameters where procedure_name = 'GS_INSERT_SEARCH_RESULTS_SP' order by 1, 2, 3, 5 …. . GENERATED BY THIRD PARTY VB APPLICATION!! 24 March 2004 Peter Jones z. Series Oracle SIG – Page 31

Just a thought How about 2 new columns in V$SQLAREA • CPU_TIME • ELAPSED_TIME I’m impressed - they are there in Oracle 9 (in microseconds) Well done Oracle Tells me how costly a statement is without using trace -------------------------------------BUFFER_GETS EXECUTIONS BUFF_EXEC OPTIMIZER_MODE CPU TIME ELAPSED TIME -------------------UPDATE TBOR_NON_NCM_ORGANISATIONS SET D_ORCCT_ST_TOT_AMT=: b 1 WHERE ID = : b 2 3405291 67349 50. 5618643 CHOOSE 12. 46 56. 52 24 March 2004 Peter Jones z. Series Oracle SIG – Page 32

IPCS (interactive problem control facility) Ask Oracle if you can have a set of their IPCS routines to run on your machine. When a DUMP occurs the first thing you want to know is who caused it. If you can find this out 5 minutes after the dump occurs and can contact the user involved they may actually remember what they were doing. Meanwhile you can be FTPing your 2 gig dump to Oracle Support. 24 March 2004 Peter Jones z. Series Oracle SIG – Page 33

IPCS (contd) use the MAPMIRS command ------------- IPCS Subcommand Entry ---------------Enter a free-form IPCS subcommand or a CLIST or REXX exec invocation below: ===> mapmirs ------------ IPCS Subcommands and Abbreviations ----------ADDDUMP | DROPDUMP, DROPD | LISTMAP, LMAP | RUNCHAIN, RUNC ANALYZE | DROPMAP, DROPM | LISTSYM, LSYM | SCAN ARCHECK | DROPSYM, DROPS | LISTUCB, LISTU | SELECT ASCBEXIT, ASCBX | EQUATE, EQU, EQ | LITERAL | SETDEF, SETD ASMCHECK, ASMK | FIND, F | LPAMAP | STACK CBFORMAT, CBF | FINDMOD, FMOD | MERGE | STATUS, ST CBSTAT | FINDUCB, FINDU | NAME | SUMMARY, SUMM CLOSE | GTFTRACE, GTF | NAMETOKN | SYSTRACE COPYDDIR | INTEGER | NOTE, N | TCBEXIT, TCBX COPYDUMP | IPCS HELP, H | OPEN | VERBEXIT, VERBX COPYTRC | LIST, L | PROFILE, PROF | WHERE, W CTRACE | LISTDUMP, LDMP | RENUM, REN | 24 March 2004 Peter Jones z. Series Oracle SIG – Page 34

IPCS (contd) locate the entry with STAT=08 xxxxxx MIRS=7 EEE 39 D 0 KEY=00800048 8388680 STAT=0202000 A GATE=0000 ATHP=0 F 8 CB 5 AC >>JOBN=ORANET 8 OUSR=OSUSR USRN= TERM= PGMN=MINBNDS SAVE=1 D 7 CEB 90 CEET=1 D 7 C 32 C 8 CSTO=0009 E 218 HWST=0009 E 218 ASID=005 C HASN=0000 MIRS=7 EEBE 9 D 0 KEY=00760046 7733318 STAT=0202000 A GATE=0000 ATHP=0 F 1765 AC >>JOBN=ORANET 8 OUSR=OSUSR USRN= TERM= PGMN=MINBNDS SAVE=1 D 5 A 1 B 90 CEET=18 AFD 2 C 8 CSTO=0017 B 818 HWST=00186588 ASID=005 C HASN=0000 MIRS=7 EED 01 D 0 KEY=00480042 4718658 STAT=08020002 GATE=FFEE 0000 ATHP=0 F 8 CEB 2 C >>JOBN=ORANET 8 OUSR=OSUSR USRN= TERM= PGMN=MINBNDS SAVE=0000 CEET=1 A 7 AA 2 C 8 CSTO=00313848 HWST=0000 ASID=005 C HASN=0000 use the MIRS= address 0 EED 01 D 0 in the IPCS browse option. Scroll down a page and you will hopefully see. . . ORAN ET 8. . . . MINBNDS G 048 0042 GEORGE 01 24 March 2004 Peter Jones z. Series Oracle SIG – Page 35

Try writing your own IPCS routine Dump will contain block of stats e. g CPU used, Phys Reads, Log Reads etc for each user and pointer to SQL executed Sample output from 8. 1. 7. 4 dump analysis below: >>USRID= ORAWPI 5 SID= 9 LOGRD= 51208 CPU(MS)= 890 PHYRD= 12525 USER ROLLB= 0 SQL= BEGIN: b 1: =DBMS_PIPE. RECEIVE_MESSAGE('BU_AGENCY_SEARCH'); IF: b 1=0 THEN: b 2: =D >>USRID= ORAWPI 6 SID= 10 LOGRD= 1406 CPU(MS)= 181 PHYRD= 179 USER ROLLB= 0 SQL= BEGIN: b 1: =DBMS_PIPE. RECEIVE_MESSAGE('bu_get_is_buyer_data'); IF: b 1=0 THEN: b >>USRID= GLBFATW SID= 11 LOGRD= 0 CPU(MS)= 0 PHYRD= 0 USER ROLLB= 0 SQL= SELECT NGBTL. ID, NGBTL. CLA_BUCLT_ID, NGBTL. ORNNN_ID, NGBTL. WF_EVENT_TYPE FROM >>USRID= CFTELS 1 SID= 12 LOGRD= 2904 CPU(MS)= 21 PHYRD= 162 USER ROLLB= 0 SQL= >>USRID= GLBFATW SID= 13 LOGRD= 0 CPU(MS)= 0 PHYRD= 0 USER ROLLB= 0 SQL= DELETE FROM TBEA_BATCH_CHECKPOINT_DATA WHERE(EABSM_PROCESS_CODE=: b 1 AND EA >>USRID= SYS SID= 14 LOGRD= 0 CPU(MS)= 0 PHYRD= 0 USER ROLLB= 0 SQL= SELECT TO_CHAR(SYSDATE, 'YYDDDHH 24 MISS'), TO_CHAR(SYSDATE, 'DDMMYYYY'), TO_CHA ORABUP 0 HI LOGR = 124717277 ORABUP 0 HI PHYS READS = 6160749 ORABUP 0 HI CPU = 219262 ZZNDN 03 HI USER ROLLBACKS = 198 24 March 2004 Peter Jones z. Series Oracle SIG – Page 36

Log Miner • Copy log before use • Run queries against clone of our Prod system • Doesn’t work well with more than one large log Useful not just as an audit trail but also as a summary of who has been doing all the updates 24 March 2004 Peter Jones z. Series Oracle SIG – Page 37

Log Miner (contd) sample 1 connect / as sysdba; execute sys. dbms_logmnr. add_logfile(logfilename => '/dsn/CFCICS 1. BRINLOG. VSAM', options => sys. dbms_logmnr. new); exec sys. dbms_logmnr. start_logmnr ( dictfilename => '/oracle/logs/ORAZ/dictionary. ora', STARTTIME => to_date('09 -Oct-2003 13: 25: 00', 'DD-MON-YYYY HH 24: MI: SS'), ENDTIME => to_date('09 -Oct-2003 13: 28: 05', 'DD-MON-YYYY HH 24: MI: SS')); SET HEADING OFF SET PAGESIZE 0 set arraysize 1 select username, session#, sql_undo, sql_redo from v£logmnr_contents; exec sys. dbms_logmnr. end_logmnr ( ); 24 March 2004 Peter Jones z. Series Oracle SIG – Page 38

Log Miner (contd) sample 2 execute sys. dbms_logmnr. add_logfile(logfilename => '/dsn/CFCICS 1. BRINLOG. A 0017639. LOG', options => sys. dbms_logmnr. new); exec sys. dbms_logmnr. start_logmnr ( dictfilename => '/oracle/logs/ORAZ/dictionary. ora'); SET PAGESIZE 0 SELECT USERNAME, SESSION#, OPERATION, COUNT(*) FROM V£LOGMNR_CONTENTS GROUP BY USERNAME, SESSION#, OPERATION; exec sys. dbms_logmnr. end_logmnr ( ); 24 March 2004 Peter Jones z. Series Oracle SIG – Page 39

Tracing Logon Triggers CREATE OR REPLACE TRIGGER sys. LOG_TRIGGER AFTER LOGON ON DATABASE begin if user = 'ORAF 012' or user = 'ORAFPI 3' or user = 'ORAFPIP' then execute immediate 'alter session set sql_Trace=true'; end if; end; 24 March 2004 Peter Jones z. Series Oracle SIG – Page 40

Tracing (contd) Logon Trigger to show bind variables (beware abends) CREATE OR REPLACE TRIGGER LOG_TRIGGER_bind AFTER LOGON ON DATABASE begin if user = 'ORABPIP' or user = 'ORAB 001' then execute immediate 'alter session set events ''' || '10046 trace name context forever, level 4'''; end if; end; / 24 March 2004 Peter Jones z. Series Oracle SIG – Page 41

Tracing (contd) Issues with EXPLAIN PLAN when using CBO Why does explain plan in the trace show different path to explain plan when run against the offending statement? I still want to close my traces - bring back CLOSETRACE command. If I extend into another trace dataset does first one close? 24 March 2004 Peter Jones z. Series Oracle SIG – Page 42

RMF Monitoring RMFWDM Overview Enclave Resource Consumption Enclave *SUMMARY ENC 00079 ENC 00051 ENC 00068 ENC 00072 ENC 00024 Attribute CLS/GRP ORACLEHI ORACLEHI P Goal 1 1 1 % D X 75 75 75 Userid. . : GBSTHO 3 Subsystem Type: OSDI Owner: ORANET 8 EAppl% TCPU USG DLY IDL 8. 468 0. 866 0. 450 0. 371 0. 268 0. 237 1. 051 0. 772 6. 060 6. 490 4. 803 13 9. 0 2. 1 4. 1 6. 0 1. 7 2. 0 2. 1 0. 0 1. 2 70 20 76 86 76 System: SYS 1 • Can look back in time • Sometimes sessions running in a tight loop in Oracle do not show updates to CPU used figure in V$Sesstat 24 March 2004 Peter Jones z. Series Oracle SIG – Page 43

SMF Statistics • Some figures still a bit buggy but still can be very useful. • Standard report provided. • We extract top ten resource using sessions during the day. Summary of Oracle resource usage sent to IT managers • Any internal users using more than 250 secs CPU in one day receive an e-mail asking what they were doing. • Any developers found in production also get a mail. 24 March 2004 Peter Jones z. Series Oracle SIG – Page 44

Sample of Daily Report 24 March 2004 Peter Jones z. Series Oracle SIG – Page 45