An introduction to SQLTrace, TKPROF and Execution Plans

1. 1 An introduction to SQL*Trace, TKPROF and Execution Plans David Kurtz Go-Faster Consultancy Ltd. david.kurtz@go-faster.co.uk www.go-faster.co.uk An introduction to SQL*Trace, TKPROF and Execution Plans An introduction to SQL*Trace, TKPROF and Execution Plans

2. www.go-faster.co.uk 2 Resources If you can’t hear me say so now. Please feel free to ask questions as we go along. The presentation will be available from www.ukoug.org www.go-faster.co.uk

3. www.go-faster.co.uk 3 Who am I? DBA Independent consultant System Performance tuning PeopleSoft ERP Oracle RDBMS UK Oracle User Group Unix SIG Oak Table www.oaktable.net Book www.psftdba.com If who are into PeopleSoft I have written this book. For those of you who are not familiar with PeopleSoft. E-business suite is Oracle’s legacy ERP system and it will be replaced with PeopleSoft!If who are into PeopleSoft I have written this book. For those of you who are not familiar with PeopleSoft. E-business suite is Oracle’s legacy ERP system and it will be replaced with PeopleSoft!

4. www.go-faster.co.uk 4 UKOUG Unix SIG Next Meeting 26th May 2005 Midlands www.ukoug.org What are you going to present? RDBMS SIG 15th March 2005 Slough RAC SIG 16th June 2005

5. www.go-faster.co.uk 5 Agenda SQL Trace Execution Plans Timing Information How to use these to solve performance problems. I want to talk about how to use SQL Trace, how to process the trace files, a bit on how to read execution plans. We will also talk about the timing information that you can get. But before we get into all that stuff I want to talk about why you should bother with any of that.I want to talk about how to use SQL Trace, how to process the trace files, a bit on how to read execution plans. We will also talk about the timing information that you can get. But before we get into all that stuff I want to talk about why you should bother with any of that.

6. www.go-faster.co.uk 6 Motivation Performance problems are instinctively routed to the DBA Trace can will locate a database problem It will prove that a problems IS NOT a database problem. The user phoes support to say that the system is slow Support immedaiately phones the DBA to say that the system is slow so it must be the database Trace will tell you where the database is slow But it will also prove conclusively how much of the response time is not down to the database. With the ever increasing number of tiers in front of the database, there is more and more stuff to be done which is not the database. In many systems non-database problems fall to the DBA because the DBA is the most technically competant person to deal with them. Application server configuration, and sometiems also web configuration OS and disk subsystem.The user phoes support to say that the system is slow Support immedaiately phones the DBA to say that the system is slow so it must be the database Trace will tell you where the database is slow But it will also prove conclusively how much of the response time is not down to the database. With the ever increasing number of tiers in front of the database, there is more and more stuff to be done which is not the database. In many systems non-database problems fall to the DBA because the DBA is the most technically competant person to deal with them. Application server configuration, and sometiems also web configuration OS and disk subsystem.

7. www.go-faster.co.uk 7 YAPP R = S + W Where R = response time S = service time W = wait time Yet Another Performance Profiling Method Anjo Kolk Shari Yamaguchi Jim Viscusi Yet Another Performance Profile MethodYet Another Performance Profile Method

8. www.go-faster.co.uk 8 ‘A Practitioners Guide to Optimizing Response Time’ Optimizing Oracle Performance Cary Millsap with Jeff Holt O’Reilly www.hotsos.com This book is basically a manual of what I do. Read this and you can do it too. It discusses how to approach performance problems, and Oracle performance problems in particular. This book is basically a manual of what I do. Read this and you can do it too. It discusses how to approach performance problems, and Oracle performance problems in particular.

9. www.go-faster.co.uk 9 Another book – also recommended by Cary Millsap The Goal: A Process of Ongoing Improvement Eliyahu Goldratt, Jeff Cox This is a ‘novel’ about a man who runs a manufacturing plant somewhere in the US. It describes how he thinks he is running an efficient factory because all the efficiency measures on the individual parts of the factory indicate that all the parts are as efficient as possible, and so so the whole thing must be efficient. Except the factory is losing money and is threatened with closure. He meets his only physics teacher in an airport, who has become an tuning expert, and he It talks about wait times, and queuing and bottlenecks and things. In fact a lot of the language sounds like the language we use when we discuss performance problems and wait interfaces and things. I coudn’t put it down – I read nearly all of it in one go. What was the goal of this manufacturing plant: The Goal is to make money by increasing net profit while simultaneously increasing return on investment and simultaneously increasing cash flow. What does this mean to you and me as IT professions. It means we have the test to help us decide what is important, and what isn’t This is a ‘novel’ about a man who runs a manufacturing plant somewhere in the US. It describes how he thinks he is running an efficient factory because all the efficiency measures on the individual parts of the factory indicate that all the parts are as efficient as possible, and so so the whole thing must be efficient. Except the factory is losing money and is threatened with closure. He meets his only physics teacher in an airport, who has become an tuning expert, and he It talks about wait times, and queuing and bottlenecks and things. In fact a lot of the language sounds like the language we use when we discuss performance problems and wait interfaces and things. I coudn’t put it down – I read nearly all of it in one go. What was the goal of this manufacturing plant: The Goal is to make money by increasing net profit while simultaneously increasing return on investment and simultaneously increasing cash flow. What does this mean to you and me as IT professions. It means we have the test to help us decide what is important, and what isn’t

10. www.go-faster.co.uk 10 Aphorism Performance is exactly what the user perceives it to be. No more, no less. Performance is ‘poor’ when the user’s perception does not match their expectation. You really must measure what the user observes. Otherwise you are not measuring everything, and you may miss something, and that something might be the biggest cause of poor performance! And your users are some of the best placed people to tell you what the problems are You really must measure what the user observes. Otherwise you are not measuring everything, and you may miss something, and that something might be the biggest cause of poor performance! And your users are some of the best placed people to tell you what the problems are

11. www.go-faster.co.uk 11 Identify your bottlenecks Performance Tuning is a search for lost time. Time lost at a bottleneck is not just time lost at a bottleneck, it affects the whole system. If it isn’t a problem, or it isn’t going to be a problem, then don’t tune it. The Goal is to make money by increasing net profit while simultaneously increasing return on investment and simultaneously increasing cash flow. The mantra of ‘The Goal’ is to identify your bottlenecks So before you enable trace, or pick up tkprof decide what needs to be tuned and what doesn’t. If a 5 batch runs in a 6 hour batch window then it isn’t a problem. If it is growing at half and hour per month, then it will be a problem and you will have to address it sometime.The Goal is to make money by increasing net profit while simultaneously increasing return on investment and simultaneously increasing cash flow. The mantra of ‘The Goal’ is to identify your bottlenecks So before you enable trace, or pick up tkprof decide what needs to be tuned and what doesn’t. If a 5 batch runs in a 6 hour batch window then it isn’t a problem. If it is growing at half and hour per month, then it will be a problem and you will have to address it sometime.

12. www.go-faster.co.uk 12 What is SQL trace? Session or database level Oracle shadow process writes to a text file in user_dump_dest. Trace file contains User and recursive SQL statements Execution plan, physical & logical reads Timing information Wait and bind information (optional) So you have a bottleneck, what will SQL Tracing it give you. So you have a bottleneck, what will SQL Tracing it give you.

13. www.go-faster.co.uk 13 What is SQL trace? First appeared in Oracle 6. More instrumentation added at each release. Not friendly format, can be read by humans. And sometimes that is necessary Metalink note 39817.1 details structure of trace files Usually process it with tkprof

14. www.go-faster.co.uk 14 So what should I trace? If I know which business transactions are my bottleneck… …and if I understand the structure of the application in question… then I can determine which session I should trace and when I should trace it. It can get more complex than that. When an application server is involved you may have to trace particular application server processes. Sometimes I create a small application server just for me to test the transaction and I configure it with a single application server process and enable trace on that process’s session. It depends on the specific technology that you are working with.It can get more complex than that. When an application server is involved you may have to trace particular application server processes. Sometimes I create a small application server just for me to test the transaction and I configure it with a single application server process and enable trace on that process’s session. It depends on the specific technology that you are working with.

15. www.go-faster.co.uk 15 Initialisation parameters timed_statistics = TRUE user_dump_dest = '<directory name>' max_dump_file_size = <size> _trace_files_public = TRUE trace_file_identifier = '<string>'

16. www.go-faster.co.uk 16 timed_statistics = TRUE You must set this initialisation variable to true otherwise you won’t get any timing information anywhere in the database including the SQL trace files Low overhead Can be set at session level Recommend always set it true

17. www.go-faster.co.uk 17 max_dump_file_size Be careful that your trace files are not truncated. look at tail for message DUMP FILE SIZE IS LIMITED Your file may be truncated prior to the point of interest. Parameter expressed in O/S blocks, Kb, or Mb Dynamic parameter from Oracle 9.x hh

18. www.go-faster.co.uk 18 _trace_files_public = TRUE This parameter controls the OS permission of the trace file. Setting it true allows the trace file to be read outside the DBA group Not dynamically alterable. Consider security issue.

19. www.go-faster.co.uk 19 tracefile_identifier = <string> Set at init.ora or setting level Available from Oracle 8.1.7 Trace file identifier string appended to name of trace file Can alter after starting trace, and so get two trace files. Do not put space ( ) or hyphen (-) in the identifier. You get an invalid file error.

20. www.go-faster.co.uk 20 tracefile_identifier = <string> ALTER SESSION SET TRACE_FILE_IDENTIFIER = ‘nVision_Report_NVSRUN_159’; /u01/app/oracle/admin/F84SP1/udump/ f84sp1_ora_41776_nVision_Report_NVSRUN_159 .trc So now I can see immediately from the trace file what the trace relates to.So now I can see immediately from the trace file what the trace relates to.

21. www.go-faster.co.uk 21 How to enable SQL trace From UKOUG2002 Advanced Oracle Diagnostics Julian Dyke www.juliandyke.com 10 methods of enabling trace information in Oracle

22. www.go-faster.co.uk 22 How to enable SQL trace ALTER SESSION SET EVENTS '10046 trace name context forever, level <n>'

23. www.go-faster.co.uk 23 How to enable SQL trace At instance level # Enable SQL trace for instance sql_trace = TRUE or # Enable SQL*trace with binds for instance event = '10046 trace name context forever, level 4';

24. www.go-faster.co.uk 24 How to disable SQL trace SQL Trace parameter cannot be altered directly --Enable SQL trace for instance ALTER SYSTEM SET EVENTS'10046 trace name context forever, level 1; or --Disable trace for instance ALTER SYSTEM SET EVENTS'10046 trace name context off';

25. www.go-faster.co.uk 25 How to enable SQL trace Either --Enable SQL trace for instance ALTER SYSTEM SET SQL_TRACE=TRUE; or --Disable trace for instance ALTER SYSTEM SET SQL_TRACE=FALSE;

26. www.go-faster.co.uk 26 How to enable SQL trace In current session with supplied PL/SQL procedures --Enable SQL trace for session DBMS_SESSION.SET_SQL_TRACE ( FLAG BOOLEAN -- TRUE to enable;  -- FALSE to disable );

27. www.go-faster.co.uk 27 How to enable SQL trace In current session --Enable SQL trace for session DBMS_SUPPORT.START_TRACE ( WAITS BOOLEAN, -- Include waits (default FALSE) BINDS BOOLEAN -- Include binds (default FALSE) ); --Disable SQL Trace for session DBMS_SUPPORT.STOP_TRACE;

28. www.go-faster.co.uk 28 How to enable SQL trace In another session DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION ( SI NUMBER, -- SID SE NUMBER, -- Serial Number SQL_TRACE BOOLEAN -- TRUE to enable;  -- FALSE to disable ); SID and SERIAL# from V$SESSION.

29. www.go-faster.co.uk 29 How to enable SQL trace In another session DBMS_SUPPORT.START_TRACE_IN_SESSION ( SI NUMBER, -- SID SE NUMBER, -- Serial Number (can be 0) WAITS BOOLEAN, -- Include waits (default FALSE) BINDS BOOLEAN -- Include binds (default FALSE) );

30. www.go-faster.co.uk 30 How to disable SQL trace In another session DBMS_SUPPORT.STOP_TRACE_IN_SESSION ( SI NUMBER, -- SID SE NUMBER, -- Serial Number (can be 0) );

31. www.go-faster.co.uk 31 How to enable SQL trace Set event in another session DBMS_SYSTEM.SET_EV ( SI NUMBER, -- SID SE NUMBER, -- Serial Number (can be 0) EV NUMBER, -- Event Number e.g. 10046 LE NUMBER, -- Level e.g. 4 NM VARCHAR2 -- Action Name – can be '‘ ); Disable with level 0

32. www.go-faster.co.uk 32 New in Oracle 10g In another session DBMS_MONITOR.SESSION_TRACE_ENABLE ( SI NUMBER, -- SID SE NUMBER, -- Serial Number (can be 0) WAITS BOOLEAN, -- Include waits (default FALSE) BINDS BOOLEAN -- Include binds (default FALSE) );

33. www.go-faster.co.uk 33 New in Oracle 10g In another session DBMS_MONITOR.SESSION_TRACE_DISABLE ( SI NUMBER, -- SID SE NUMBER, -- Serial Number (can be 0) );

34. www.go-faster.co.uk 34 Using logon triggers to enable SQL trace Login trigger to trace from the very beginning. CREATE OR REPLACE TRIGGER sys.trace_logon_sysadm AFTER LOGON ON sysadm.schema BEGIN sys.dbms_session.set_sql_trace(true); END; /

35. www.go-faster.co.uk 35 Using triggers to enable SQL trace (i) Trigger fired by starting process. CREATE OR REPLACE TRIGGER sysadm.set_trace AFTER UPDATE OF runstatus ON sysadm.psprcsrqst FOR EACH ROW WHEN (new.runstatus = 7 and old.runstatus != 7 AND new.prcstype IN('Application Engine','COBOL SQL','Crystal','nVision-Report','SQR Process','SQR Report','SQR Report For WF Delivery') AND new.prcsname = ‘GPPDPRUN’ AND new.begindttm <= TO_DATE('200503111800','YYYYMMDDHH24MI')) In PeopleSoft batch processes are scheduled by an agent. The who process is tied together with a control table psprcsrqst. When the process starts it updates its own status on that table to 7. The when clause can be adjusted to restrict when the trigger fires, for what processes, for what operators etc.In PeopleSoft batch processes are scheduled by an agent. The who process is tied together with a control table psprcsrqst. When the process starts it updates its own status on that table to 7. The when clause can be adjusted to restrict when the trigger fires, for what processes, for what operators etc.

36. www.go-faster.co.uk 36 Using triggers to enable SQL trace (ii) BEGIN EXECUTE IMMEDIATE 'ALTER SESSION SET TIMED_STATISTICS = TRUE'; EXECUTE IMMEDIATE 'ALTER SESSION SET MAX_DUMP_FILE_SIZE = UNLIMITED'; EXECUTE IMMEDIATE 'ALTER SESSION SET TRACEFILE_IDENTIFIER = '''|| TRANSLATE(:new.prcstype||'_'||:new.prcsname,' -','__')||'_'|| :new.prcsinstance||''''; EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 8'''; END; /

37. www.go-faster.co.uk 37 What is in the SQL trace file See Metalink Note 39817.1 for details of trace file structure

38. www.go-faster.co.uk 38 SQL trace file sections Trace Sections DBMS_APPLICATION_INFO settings Cursors Operations Parse, exec, fetch, (sort) unmap Errors Stat Execution Plans Xctend End of transaction

39. www.go-faster.co.uk 39 SQL trace file sections With event 10046 Binds (level 4) Waits (level 8) Timings centisecond timings (Oracle 7 & 8) microsecond from Oracle 9

40. www.go-faster.co.uk 40 SQL trace file contents /u01/app/oracle/admin/F84SP1/udump/f84sp1_ora_41776_nVision_Report_NVSRUN_159.trc … Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production JServer Release 9.2.0.1.0 - Production ORACLE_HOME = /u01/app/oracle/product/9.2.0 System name: AIX Node name: xxxxxxxxx Release: 3 Version: 4 Machine: 0000C6BF4C00 Instance name: F84SP1 Redo thread mounted by this instance: 1 Oracle process number: 28 Unix process pid: 41776, image: oracle@xxxxxxxxx (TNS V1-V3) The head gives you information about the server versionThe head gives you information about the server version

41. www.go-faster.co.uk 41 SQL trace file contents PARSING IN CURSOR #3 len=58 dep=0 uid=24 oct=3 lid=24 tim=1045288408856870 hv=2952492898 ad='3b36970' SELECT VERSION FROM PSVERSION WHERE OBJECTTYPENAME = 'SYS' END OF STMT PARSE #3:c=0,e=1836,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4, tim=1045288408856862 EXEC #3:c=0,e=80,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4, tim=1045288408857230 WAIT #3: nam='SQL*Net message to client' ela= 3 p1=1413697536 p2=1 p3=0 FETCH #3:c=0,e=75,p=0,cr=2,cu=0,mis=0,r=1,dep=0,og=4, tim=1045288408857420 WAIT #3: nam='SQL*Net message from client' ela= 47544 p1=1413697536 p2=1 p3=0 STAT #3 id=1 cnt=1 pid=0 pos=1 obj=31453 op='TABLE ACCESS BY INDEX ROWID PSVERSION ' STAT #3 id=2 cnt=1 pid=1 pos=1 obj=31454 op='INDEX UNIQUE SCAN PS_PSVERSION '

42. www.go-faster.co.uk 42 tkprof Oracle supplied utility Transient Kernel Profiler Processes SQL trace files Provides a easy to read report Timings Sorted by CPU or Elapsed Timings Wait analysis from Oracle 9 Even on Oracle 8 trace! Execution plans Aggregate identical statements If you enable 10046,8 on Oracle 8 you can process that trace file with a V9 tkprof and get the wait analysis.If you enable 10046,8 on Oracle 8 you can process that trace file with a V9 tkprof and get the wait analysis.

43. www.go-faster.co.uk 43 tkprof sample SELECT VERSION FROM PSVERSION WHERE OBJECTTYPENAME = 'SYS' call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 0 2 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 2 0 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 24 Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID PSVERSION 1 INDEX UNIQUE SCAN PS_PSVERSION (object id 31454)

44. www.go-faster.co.uk 44 Row source statistics From Oracle 9.2.0.2 ALTER SESSION SET STATISTICS_LEVEL = ALL; Added to SQL Trace stat line Appears in tkprof output Performance overhead

45. www.go-faster.co.uk 45 Row source statistics SELECT A.START_DATE, A.STOP_DATE FROM PS_X_PYE_OVRD_ET A WHERE A.EMPLID= :1 AND A.EMPL_RCD= :2 AND A.PIN_NUM= :3 AND A.START_DATE<= :4 AND A.STOP_DATE>= :5 AND A.START_DATE<= :6 ORDER BY A.START_DATE DESC call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.02 0.17 0 0 0 0 Execute 116759 20.76 40.24 0 0 0 0 Fetch 116759 25.60 88.40 1910 440408 0 89622 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 233519 46.38 128.81 1910 440408 0 89622

46. www.go-faster.co.uk 46 Row source statistics Rows Row Source Operation ------- --------------------------------------------------- 89622 SORT ORDER BY (cr=440408 r=1910 w=0 time=72942929 us) 89622 PARTITION RANGE SINGLE PARTITION: KEY KEY (cr=440408 r=1910 w=0 time=58756548 us) 89622 TABLE ACCESS BY LOCAL INDEX ROWID OBJ#(165144) PARTITION: KEY KEY (cr=440408 r=1910 w=0 time=57904868 us) 89622 INDEX RANGE SCAN DESCENDING OBJ#(166035) PARTITION: KEY KEY (cr=350786 r=451 w=0 time=20700497 us) (object id 166035)

47. www.go-faster.co.uk 47 How to tune with tkprof Identify your bottlenecks Decide what to trace Enable SQL trace Tkprof Sort=(fchela,exeela,prsela) Waits=yes Print=10 Explain=<connect string>

48. www.go-faster.co.uk 48 Example 1: SQL Problem You have traced your process… you run tkprof… And at the top of the report you have a big block of SQL. What do you do next?

49. www.go-faster.co.uk 49 SQL statement INSERT INTO PS_PP_CUST_TMP2 ( PROCESS_INSTANCE, DEPOSIT_BU, DEPOSIT_ID, PAYMENT_SEQ_NUM, CUST_ID, PAYMENT_AMT, PAYMENT_DT, PP_METHOD, SETID, SUBCUST_QUAL1, SUBCUST_QUAL2, PP_HOLD, PP_MET_SW, PAYMENT_CURRENCY ) SELECT DISTINCT P.PROCESS_INSTANCE, P.DEPOSIT_BU, P.DEPOSIT_ID, P.PAYMENT_SEQ_NUM, C.CUST_ID, P.PAYMENT_AMT, P.PAYMENT_DT, O.PP_METHOD, O.SETID, C.SUBCUST_QUAL1, C.SUBCUST_QUAL2, O.PP_HOLD, 'N', P.PAYMENT_CURRENCY FROM PS_CUST_OPTION O, PS_ITEM I, PS_CUSTOMER C, PS_SET_CNTRL_REC S, PS_PAYMENT_ID_ITEM X, PS_PP_PAYMENT_TMP P WHERE P.PROCESS_INSTANCE = 212 AND S.RECNAME = 'CUSTOMER' AND S.SETID = C.SETID AND S.SETCNTRLVALUE = I.BUSINESS_UNIT AND I.CUST_ID = C.CUST_ID AND I.ITEM_STATUS = 'O' AND O.SETID = C.REMIT_FROM_SETID AND O.CUST_ID = C.REMIT_FROM_CUST_ID AND O.EFFDT = ( SELECT MAX(Z.EFFDT) FROM PS_CUST_OPTION Z WHERE Z.SETID = O.SETID AND Z.CUST_ID = O.CUST_ID AND Z.EFF_STATUS = 'A' AND Z.EFFDT <= P.PAYMENT_DT ) AND O.PP_METHOD <> ' ' AND P.DEPOSIT_BU = X.DEPOSIT_BU AND P.DEPOSIT_ID = X.DEPOSIT_ID AND P.PAYMENT_SEQ_NUM = X.PAYMENT_SEQ_NUM AND X.REF_QUALIFIER_CODE = 'I' AND X.REF_VALUE = I.ITEM The SQL statement is listed in the TKPROF output. All formatting is lost.The SQL statement is listed in the TKPROF output. All formatting is lost.

50. www.go-faster.co.uk 50 Statistics … count = number of times OCI procedure was executed cpu = cpu time in seconds executing elapsed = elapsed time in seconds executing disk = number of physical reads of buffers from disk query = number of buffers gotten for consistent read current = number of buffers gotten in current mode (usually for update) rows = number of rows processed by the fetch or execute call … call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.31 0.33 4 0 4 0 Execute 1 156.61 160.23 164769 3981545 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 156.92 160.56 164773 3981545 4 0 Then you get a set of timings for each statements Times are accurate to 1/100th of a second per executionThen you get a set of timings for each statements Times are accurate to 1/100th of a second per execution

51. www.go-faster.co.uk 51 Execution plan (from stat lines) Rows Execution Plan ------- --------------------------------------------------- 0 INSERT STATEMENT GOAL: CHOOSE 0 SORT (UNIQUE) 0 FILTER 0 NESTED LOOPS 8 NESTED LOOPS 1000040 NESTED LOOPS 360 NESTED LOOPS 10 NESTED LOOPS 10 TABLE ACCESS BY INDEX ROWID PS_PP_PAYMENT_TMP 11 INDEX (RANGE SCAN) (object id 9329) 20 INDEX (RANGE SCAN) OF (object id 8105) 360 TABLE ACCESS (BY INDEX ROWID) OF 'PS_SET_CNTRL_REC' 370 INDEX (RANGE SCAN) OF (object id 12020) 1000040 TABLE ACCESS (BY INDEX ROWID) OF 'PS_CUSTOMER' 1000400 INDEX (RANGE SCAN) OF (object id 4751) 1000048 INDEX (RANGE SCAN) OF 'PS#ITEM' (NON-UNIQUE) 8 TABLE ACCESS (BY INDEX ROWID) OF 'PS_CUST_OPTION' 16 INDEX (RANGE SCAN) OF (object id 4854) 0 SORT (AGGREGATE) 0 TABLE ACCESS (BY INDEX ROWID) OF 'PS_CUST_OPTION' 0 INDEX (RANGE SCAN) OF (object id 4854) From Oracle 8.1 you get this explain plan in the TKPROF output whether you connect TKPROF to the database or note. However, you do not get index names, only index object numbers. This is a version 8.1 trace because the index names are object Ids, from 9 you get index names in the stat lines.From Oracle 8.1 you get this explain plan in the TKPROF output whether you connect TKPROF to the database or note. However, you do not get index names, only index object numbers. This is a version 8.1 trace because the index names are object Ids, from 9 you get index names in the stat lines.

52. www.go-faster.co.uk 52 Execution plan (if you connect tkprof to the database) Rows Execution Plan ------- --------------------------------------------------- 0 INSERT STATEMENT GOAL: CHOOSE 0 SORT (UNIQUE) 0 FILTER 0 NESTED LOOPS 8 NESTED LOOPS 1000040 NESTED LOOPS 360 NESTED LOOPS 10 NESTED LOOPS 10 TABLE ACCESS (BY INDEX ROWID) OF 'PS_PP_PAYMENT_TMP' 11 INDEX (RANGE SCAN) OF 'PSAPP_PAYMENT_TMP' (NON-UNIQUE) 20 INDEX (RANGE SCAN) OF 'PSAPAYMENT_ID_ITEM' (NON-UNIQUE) 360 TABLE ACCESS (BY INDEX ROWID) OF 'PS_SET_CNTRL_REC' 370 INDEX (RANGE SCAN) OF 'PSBSET_CNTRL_REC' (NON-UNIQUE) 1000040 TABLE ACCESS (BY INDEX ROWID) OF 'PS_CUSTOMER' 1000400 INDEX (RANGE SCAN) OF 'PS#CUSTOMER' (NON-UNIQUE) 1000048 INDEX (RANGE SCAN) OF 'PS#ITEM' (NON-UNIQUE) 8 TABLE ACCESS (BY INDEX ROWID) OF 'PS_CUST_OPTION' 16 INDEX (RANGE SCAN) OF 'PS_CUST_OPTION' (UNIQUE) 0 SORT (AGGREGATE) 0 TABLE ACCESS (BY INDEX ROWID) OF 'PS_CUST_OPTION' 0 INDEX (RANGE SCAN) OF 'PS_CUST_OPTION' (UNIQUE) In any version of Oracle & TKPROF, if you connect to the database you get this version of the explain plan. The object numbers have been resolved to index names. In this particular case, it is quite clear from the sudden jump in the size of the numbers that that the PS_CUSTOMER table is being read inefficently. In order to found out why this is happening it is necessary to read the execution plan. It is important to view the execution plan in a non-proportional font. Start at the top of the execution plan and read down to the line before the first un-indent. A range scan of 11 rows from index PSAPP_PAYMENT_TMP is used to read 10 rows from table PS_PP_PAYMENT_TMP. .That is used to drive a nested loop join to table PS_PAYMENT_ID_ITEM, but all the data can in fact be derived from the index PSAPAYMENT_ID_ITEM. 20 rows are read from the index, but the product remains at 10 rows. .That result is then joined to the table PS_SET_CNTRL_REC. It is searched by index PSBSET_CNTRL_REC. 370 rows are read from the index to retrieve 360 rows from the table. The product grows to 360 rows. Next, table PS_CUSTOMER is join to the product, it is searched by index PS#CUSTOMER. 1000400 rows are read from the index that retrieves 1000040 rows from the table. The result set grows to 1000040 rows The PS_ITEM table is joined on. All the data is satisfied from the PS#ITEM index. 1000040 rows are read in, but the result ends up with 8 rows. .16 rows are read from the unique index PS_CUST_OPTION to retrieve 8 rows from the table PS_CUST_OPTION. The result set is now empty. .The effective date sub-query would be executed on PS_CUST_OPTION if there were any rows in the result set. It would be searched by the index PS_CUST_OPTION. The result set is still empty. .The result set is then sorted because of the distinct at the front of the select clause.In any version of Oracle & TKPROF, if you connect to the database you get this version of the explain plan. The object numbers have been resolved to index names. In this particular case, it is quite clear from the sudden jump in the size of the numbers that that the PS_CUSTOMER table is being read inefficently. In order to found out why this is happening it is necessary to read the execution plan. It is important to view the execution plan in a non-proportional font. Start at the top of the execution plan and read down to the line before the first un-indent. A range scan of 11 rows from index PSAPP_PAYMENT_TMP is used to read 10 rows from table PS_PP_PAYMENT_TMP. .That is used to drive a nested loop join to table PS_PAYMENT_ID_ITEM, but all the data can in fact be derived from the index PSAPAYMENT_ID_ITEM. 20 rows are read from the index, but the product remains at 10 rows. .That result is then joined to the table PS_SET_CNTRL_REC. It is searched by index PSBSET_CNTRL_REC. 370 rows are read from the index to retrieve 360 rows from the table. The product grows to 360 rows. Next, table PS_CUSTOMER is join to the product, it is searched by index PS#CUSTOMER. 1000400 rows are read from the index that retrieves 1000040 rows from the table. The result set grows to 1000040 rows The PS_ITEM table is joined on. All the data is satisfied from the PS#ITEM index. 1000040 rows are read in, but the result ends up with 8 rows. .16 rows are read from the unique index PS_CUST_OPTION to retrieve 8 rows from the table PS_CUST_OPTION. The result set is now empty. .The effective date sub-query would be executed on PS_CUST_OPTION if there were any rows in the result set. It would be searched by the index PS_CUST_OPTION. The result set is still empty. .The result set is then sorted because of the distinct at the front of the select clause.

53. www.go-faster.co.uk 53 1. Make the statement readable SELECT DISTINCT P.PROCESS_INSTANCE, P.DEPOSIT_BU, P.DEPOSIT_ID, P.PAYMENT_SEQ_NUM, C.CUST_ID, P.PAYMENT_AMT, P.PAYMENT_DT, O.PP_METHOD, O.SETID, C.SUBCUST_QUAL1, C.SUBCUST_QUAL2, O.PP_HOLD, 'N', P.PAYMENT_CURRENCY FROM PS_CUST_OPTION O, PS_ITEM I, PS_CUSTOMER C, PS_SET_CNTRL_REC S, PS_PAYMENT_ID_ITEM X, PS_PP_PAYMENT_TMP P WHERE P.PROCESS_INSTANCE = 212 AND S.RECNAME = 'CUSTOMER' AND S.SETID = C.SETID AND S.SETCNTRLVALUE = I.BUSINESS_UNIT AND I.CUST_ID = C.CUST_ID AND I.ITEM_STATUS = 'O' AND O.SETID = C.REMIT_FROM_SETID AND O.CUST_ID = C.REMIT_FROM_CUST_ID AND O.EFFDT = ( SELECT MAX(Z.EFFDT) FROM PS_CUST_OPTION Z WHERE Z.SETID = O.SETID AND Z.CUST_ID = O.CUST_ID AND Z.EFF_STATUS = 'A' AND Z.EFFDT <= P.PAYMENT_DT) AND O.PP_METHOD <> ' ' AND P.DEPOSIT_BU = X.DEPOSIT_BU AND P.DEPOSIT_ID = X.DEPOSIT_ID AND P.PAYMENT_SEQ_NUM = X.PAYMENT_SEQ_NUM AND X.REF_QUALIFIER_CODE = 'I' AND X.REF_VALUE = I.ITEM The next stage is to lay the statement out so that it is readable. No matter how the SQL was formatted when it went into the database, all that formatting will be lost by the time it comes out again in TKPROF The important thing is to concentrate on the query. The FROM and WHERE clauses are what really matters. This way you can easily see what tables are in the query and what joins are involved. The next stage is to lay the statement out so that it is readable. No matter how the SQL was formatted when it went into the database, all that formatting will be lost by the time it comes out again in TKPROF The important thing is to concentrate on the query. The FROM and WHERE clauses are what really matters. This way you can easily see what tables are in the query and what joins are involved.

54. www.go-faster.co.uk 54 2. Start to draw the query Although the SQL is now laid out in a easy to read format, it is still not clear what is going on. The following graphical technique may be used to illustrate the connections between tables made in the query. Start by simply putting the tables down. The order does not matter, but it helps if related tables are adjacent. This prevents a tangle of crossed lines later. Although the SQL is now laid out in a easy to read format, it is still not clear what is going on. The following graphical technique may be used to illustrate the connections between tables made in the query. Start by simply putting the tables down. The order does not matter, but it helps if related tables are adjacent. This prevents a tangle of crossed lines later.

55. www.go-faster.co.uk 55 4. Get the order from the plan 0 FILTER 0 NESTED LOOPS 8 NESTED LOOPS 1000040 NESTED LOOPS 360 NESTED LOOPS 10 NESTED LOOPS 10 (1)TABLE ACCESS (BY INDEX ROWID) OF 'PS_PP_PAYMENT_TMP' 11 INDEX (RANGE SCAN) OF 'PSAPP_PAYMENT_TMP' (NON-UNIQUE) 20 (2)INDEX (RANGE SCAN) OF 'PSAPAYMENT_ID_ITEM' (NON-UNIQUE) 360 (3)TABLE ACCESS (BY INDEX ROWID) OF 'PS_SET_CNTRL_REC' 370 INDEX (RANGE SCAN) OF 'PSBSET_CNTRL_REC' (NON-UNIQUE) 1000040 (4)TABLE ACCESS (BY INDEX ROWID) OF 'PS_CUSTOMER' 1000400 INDEX (RANGE SCAN) OF 'PS#CUSTOMER' (NON-UNIQUE) 1000048 (5)INDEX (RANGE SCAN) OF 'PS#ITEM' (NON-UNIQUE) 8 (6)TABLE ACCESS (BY INDEX ROWID) OF 'PS_CUST_OPTION' 16 INDEX (RANGE SCAN) OF 'PS_CUST_OPTION' (UNIQUE) 0 SORT (AGGREGATE) 0 TABLE ACCESS (BY INDEX ROWID) OF 'PS_CUST_OPTION' INDEX (RANGE SCAN) OF 'PS_CUST_OPTION' (UNIQUE) .The order in which the tables are processed within the query can be obtained from the explain plan. I have added the bracketed numbers .The order in which the tables are processed within the query can be obtained from the explain plan. I have added the bracketed numbers

56. www.go-faster.co.uk 56 5. Put the order in the diagram Although the SQL is now laid out in a easy to read format, it is still not clear what is going on. The following graphical technique may be used to illustrate the connections between tables made in the query. Start by simply putting the tables down. The order does not matter, but it helps if related tables are adjacent. This prevents a tangle of crossed lines later. Although the SQL is now laid out in a easy to read format, it is still not clear what is going on. The following graphical technique may be used to illustrate the connections between tables made in the query. Start by simply putting the tables down. The order does not matter, but it helps if related tables are adjacent. This prevents a tangle of crossed lines later.

57. www.go-faster.co.uk 57 6. This time, the answer is an index CREATE INDEX PSBITEM ON PS_ITEM (ITEM, CUST_ID, BUSINESS_UNIT, ITEM_STATUS) TABLESPACE PSINDEX STORAGE (INITIAL 10K NEXT 98K MAXEXTENTS 110 PCTINCREASE 0) / Add indexAdd index

58. www.go-faster.co.uk 58 New Execution Order Although the SQL is now laid out in a easy to read format, it is still not clear what is going on. The following graphical technique may be used to illustrate the connections between tables made in the query. Start by simply putting the tables down. The order does not matter, but it helps if related tables are adjacent. This prevents a tangle of crossed lines later. Although the SQL is now laid out in a easy to read format, it is still not clear what is going on. The following graphical technique may be used to illustrate the connections between tables made in the query. Start by simply putting the tables down. The order does not matter, but it helps if related tables are adjacent. This prevents a tangle of crossed lines later.

59. www.go-faster.co.uk 59 Statistics Before call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.31 0.33 4 0 4 0 Execute 1 156.61 160.23 164769 3981545 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 156.92 160.56 164773 3981545 4 0 After call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.35 2.56 3 0 3 0 Execute 1 0.03 0.05 38 289 3 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.38 2.61 41 289 6 0 The improvement in the execution time of the step is dramatic. The improvement in the execution time of the step is dramatic.

60. www.go-faster.co.uk 60 Example 2: Wait Events How do you know it’s not the database? Event 10046,8 – enable wait information.

61. www.go-faster.co.uk 61 CPU –v- elapsed time INSERT /*GPPRDMGR_I_ACUM*/ INTO PS_GP_RSLT_ACUM (CAL_RUN_ID ,EMPLID ,EMPL_RCD ,GP_PAYGROUP ,CAL_ID ,RSLT_SEG_NUM ,PIN_NUM ,EMPL_RCD_ACUM ,ACM_FROM_DT , ACM_THRU_DT ,USER_KEY1 ,USER_KEY2 ,USER_KEY3 ,USER_KEY4 ,SLICE_BGN_DT , SLICE_END_DT ,COUNTRY ,ACM_TYPE ,ACM_PRD_OPTN ,CALC_RSLT_VAL ,CALC_VAL , USER_ADJ_VAL ,PIN_PARENT_NUM ,CORR_RTO_IND ) VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21, :22,:23,:24)     call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 22510 73.30 1269.24 31542 45040 503607 558966 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 22511 73.30 1269.24 31542 45040 503607 558966

62. www.go-faster.co.uk 62 Wait events report discrepancy Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ----------------------------- Waited ---------- ------------ SQL*Net more data from client 27495 0.56 1036.26 db file sequential read 31542 0.97 148.99 SQL*Net message from client 22510 0.44 80.41 free buffer waits 19 1.02 4.74 SQL*Net message to client 22510 0.01 0.08 latch free 5 0.02 0.08 buffer busy waits 1 0.00 0.00 log file switch completion 1 0.06 0.06

63. www.go-faster.co.uk 63 After resolving network problem call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 22264 103.07 113.04 2521 86624 553966 662676 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 22265 103.07 113.04 2521 86624 553966 662676 Event waited on Times Max. Wait Total Waited ----------------------------- Waited ---------- ------------ SQL*Net message from client 22264 0.47 88.84 SQL*Net more data from client 29138 0.47 6.69

64. www.go-faster.co.uk 64 Example 3: Waits events UPDATE /*GPPDPDM4_U_TRIGGER*/ PS_GP_SEG_TRGR SET CAL_RUN_ID =:1 WHERE TRGR_EFFDT>=:2 AND TRGR_EFFDT<=:3 AND EMPLID=:4 AND COUNTRY=:5 AND SEG_TRGR_STATUS='A' AND CAL_RUN_ID=' ' AND (EMPL_RCD=:6 OR SEG_TRGR_LVL='E') AND TRGR_EVENT_ID IN (SELECT SEC.TRGR_EVENT_ID FROM PS_GP_SEG_EVT SEC WHERE SEC.SEG_TYPE='P' AND SEC.COUNTRY=:7 ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 5858 0.57 0.51 0 0 0 0 Execute 27973 7.60 1989.00 1630 95825 662 645 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 33831 8.17 1989.52 1630 95825 662 645

65. www.go-faster.co.uk 65 Wait Events Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited --------------------------- Waited ---------- ------------ enqueue 658 3.34 1973.10 SQL*Net message from client 27973 1.34 99.10 db file sequential read 1630 0.27 8.67 SQL*Net message to client 27973 0.00 0.05 latch free 2 0.00 0.00

66. www.go-faster.co.uk 66 Example 4: Idle wait? SQL*Net message from client Often call an idle wait It means that the database is idle What is a ‘batch’ process?

67. www.go-faster.co.uk 67 What is a ‘batch’ process? It is a process that runs for a period of time without waiting for user input. So if the database is idle then the client process must be busy. SQL*Net message from client =Application busy time

68. www.go-faster.co.uk 68 Summary at bottom of report Active process making lots of calls to the database OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 12197 3.85 4.50 621 25979 0 0 Execute 870343 537.36 750.89 70625 18372702 2047141 2657717 Fetch 710951 405.99 917.94 229999 10341004 73 3030039 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 1593491 947.20 1673.34 301245 28739685 2047214 5687756

69. www.go-faster.co.uk 69 Is it the database? Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ----------------------------- Waited ---------- ------------ SQL*Net message to client 881285 0.01 1.68 SQL*Net message from client 881285 1.22 2491.24 log file sync 185 1.01 28.18 SQL*Net more data from client 62845 0.40 5.27 SQL*Net more data to client 21008 0.00 1.54 db file sequential read 201760 2.39 451.60 direct path write 2632 0.67 49.76 direct path read 4034 0.71 28.97

70. www.go-faster.co.uk 70 Is it the database? Perhaps this time we should look at the application Batch 4790s SQL*Net message from client 2491s Database 1673.34 CPU Contention? Batch 4790s SQL*Net message from client 2491s Database 1673.34 CPU Contention?

71. www.go-faster.co.uk 71 Example 5: tkprof can mislead Different plans One from stat lines How the statement was actually executed One from ‘explain plan for’ command How the analyser might execute the statement in the future on the basis of the current statistics Some numbers can be nonsense

72. www.go-faster.co.uk 72 A simple SQL statement SELECT DISTINCT A.EMPLID, A.NATIONAL_ID FROM PS_PERS_NID A , PS_GP_PYE_STAT_WRK P WHERE P.CAL_RUN_ID =:1 AND P.EMPLID BETWEEN :2 AND :3 AND P.PRD_TYPE <> 'H' AND P.PYE_CALC_STAT < '50' AND P.EMPLID=A.EMPLID AND A.PRIMARY_NID= :4 ORDER BY A.EMPLID ASC call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 5598 1.06 1.07 0 0 0 0 Fetch 5598 4411.76 4644.73 127 183871489 0 1117812 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 11197 4412.82 4645.80 127 183871489 0 1117812

73. www.go-faster.co.uk 73 Plan from stat lines Rows Row Source Operation ------- --------------------------------------------------- 1117812 SORT UNIQUE 44603520 NESTED LOOPS 68123268 PARTITION RANGE ITERATOR PARTITION: KEY KEY 68123268 TABLE ACCESS BY LOCAL INDEX ROWID PS_GP_PYE_STAT_WRK PARTITION: KEY KEY 68123268 INDEX RANGE SCAN PARTITION: KEY KEY (object id 273486) 44603520 TABLE ACCESS BY INDEX ROWID PS_PERS_NID 112721190 INDEX RANGE SCAN (object id 157793)

74. www.go-faster.co.uk 74 Plan from ‘explain plan for’ Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 1117812 SORT (UNIQUE) 44603520 MERGE JOIN 68123268 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'PS_PERS_NID' 68123268 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'PS_PERS_NID' (UNIQUE) 68123268 SORT (JOIN) 44603520 PARTITION RANGE (ITERATOR) PARTITION:KEYKEY 112721190 TABLE ACCESS GOAL: ANALYZED (BY LOCAL INDEX ROWID) OF 'PS_GP_PYE_STAT_WRK' PARTITION:KEYKEY 0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'PS_GP_PYE_STAT_WRK' (UNIQUE) PARTITION:KEYKEY

75. www.go-faster.co.uk 75 Example 6: Transitive Closure If A=B and B=C you can infer that A=C You and I can, but Oracle can’t do this on column predicates Oracle can do this on value predicates If A=1 and A=B, Oracle knows that B=1

76. www.go-faster.co.uk 76 A simple query? SELECT ... FROM PS_GP_PYE_STAT_WRK S,PS_GP_PYE_PRC_STAT P2,PS_GP_RSLT_ACUM RA WHERE S.RUN_CNTL_ID=:1 AND S.OPRID=:2 AND S.EMPLID BETWEEN :3 AND :4 AND S.EMPLID=RA.EMPLID AND P2.EMPLID=RA.EMPLID AND S.EMPL_RCD=RA.EMPL_RCD AND P2.EMPL_RCD=RA.EMPL_RCD AND S.GP_PAYGROUP=RA.GP_PAYGROUP AND P2.GP_PAYGROUP=RA.GP_PAYGROUP AND S.CAL_ID=RA.CAL_ID AND P2.CAL_ID=RA.CAL_ID AND P2.CAL_RUN_ID=RA.CAL_RUN_ID AND P2.ORIG_CAL_RUN_ID=RA.ORIG_CAL_RUN_ID AND S.PRD_TYPE='R' AND S.RSLT_SEG_NUM=RA.RSLT_SEG_NUM AND S.PRIOR_VER_NUM=P2.RSLT_VER_NUM AND S.PRIOR_REV_NUM=P2.RSLT_REV_NUM AND RA.ACM_PRD_OPTN='1';

77. www.go-faster.co.uk 77 A three table join

78. www.go-faster.co.uk 78 Index on GP_RSLT_ACUM CREATE UNIQUE INDEX PS_GP_RSLT_ACUM ON PS_GP_RSLT_ACUM (EMPLID, CAL_RUN_ID, EMPL_RCD, GP_PAYGROUP, CAL_ID, ORIG_CAL_RUN_ID, RSLT_SEG_NUM, PIN_NUM, EMPL_RCD_ACUM, ACM_FROM_DT, ACM_THRU_DT, SLICE_BGN_DT, SLICE_END_DT, SEQ_NUM8) ...

79. www.go-faster.co.uk 79 Explicitly add implicit joins AND P2.EMPLID=S.EMPLID AND P2.EMPL_RCD=S.EMPL_RCD AND P2.GP_PAYGROUP=S.GP_PAYGROUP AND P2.CAL_ID=S.CAL_ID

80. www.go-faster.co.uk 80 With the extra index

81. www.go-faster.co.uk 81 Index on GP_RSLT_ACUM CREATE UNIQUE INDEX PS_GP_RSLT_ACUM ON PS_GP_RSLT_ACUM (EMPLID, CAL_RUN_ID, EMPL_RCD, GP_PAYGROUP, CAL_ID, ORIG_CAL_RUN_ID, RSLT_SEG_NUM, PIN_NUM, EMPL_RCD_ACUM, ACM_FROM_DT, ACM_THRU_DT, SLICE_BGN_DT, SLICE_END_DT, SEQ_NUM8) ...

82. www.go-faster.co.uk 82 Conclusion How to think about performance issues IDENTIFY YOUR BOTTLENECKS SQL Trace What to trace, How to enable, What’s in it? Tkprof What can it tell you? When does it mislead?

83. www.go-faster.co.uk 83 Questions?

84. 84 An introduction to SQL*Trace, TKPROF and Execution Plans David Kurtz Go-Faster Consultancy Ltd. david.kurtz@go-faster.co.uk www.go-faster.co.uk