Guerilla Oracle Tuning for Windchill Administrators

This material provides a detailed guide on Oracle performance tuning for Windchill Administrators by Stephen Vaillancourt, a Technical Fellow at PTC Platinum Technical Support. It covers identifying and resolving Oracle-related issues impacting system performance, dealing with Oracle performance, analyzing poor-running SQL statements, and obtaining necessary data for analysis, ultimately aiming to enhance system stability and user experience.

• Oracle Performance Tuning
• Windchill Administrators
• DBA Interaction
• SQL Optimization
• System Stability

khal_451 Follow

Uploaded on Sep 24, 2024 | 0 Views

Download Presentation

Please find below an Image/Link to download the presentation.

The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.

Presentation Transcript

1. Guerilla Oracle Tuning for Windchill Administrators Stephen Vaillancourt Technical Fellow PTC Platinum Technical Support PTCLive Global, June 16, 2014

2. Agenda What type of DBA do you have? Does the system have a problem? How to get the Data Identifying long running SQL statements Guerilla Tuning SQL Summary 2 2

3. Dealing Oracle Performance There are three main ways I ve seen Windchill Administrators interact with Oracle You have an Oracle DBA Keep the database running; but usually don t do much if any performance tuning There is DBA service you need to open tickets with Usually no tuning is done without a specific request You are the Oracle DBA . Lucky you Oracle is one of the two main key to Windchill performance and stability The other is the code running in the MethodServer This presentation is designed to put more control over Oracle back into your hands 3 3

4. Does your System have an Oracle problem? Every System of size can generally be made to run better User visible symptoms System response is uneven, sometimes fast but other times it seems sluggish or unresponsive Method servers are restarting Users are frustrated with responsiveness .. Unfortunately most users suffer in silence and things simmer for a while before blowing up Database Characteristics Wait events which are >> than CPU times SQL statements that place excessive load on the system SQL statements which take minutes (or longer) to run The Goal of Guerrilla tuning is to identify the biggest most impactful things and correct them 4 4

5. Getting the Data to Analyze 5

6. Getting the data to Identify Poor Running SQL There are several ways . The easiest is to ask a DBA (or DBA service provider) to: Generate an AWR/ASH report, for the time between A & B -Where A &B is the start and end times cover a busy period or a period when there was a problem Run the Gather Info Script see article CS51777 for file to download Use the detailed instructions here to generate ASH reports You are the Windchill Administrator Use the Top SQL Sample Intervals (https://<host>/<name>/wtcore/jsp/jmx/viewTopSQL.jsp) #Site #Utilities #Server Status #Monitoring Tools #Top SQL Sample Intervals (choose OK without inputting any dates) Run the gather info Script Through PSM 6 6

7. Administrators with Access to OEM: Generating an AWR report Standard URL for OEM - https://<database machine>:1158/em/ Long Running SQL Long Running SQL Use the link here on the top things to look at in the OEM to identify a performance problem 7 7

8. Reviewing the Data 8

9. What am I looking for? Usually it s all about the SQL A database could have all sorts of problems .. The list is long but weird things are generally rare for Windchill systems 95%+ of the time problems are caused by poor SQL and disk I/O problems Note: just because the Oracle datafiles are on the latest most advanced disks doesn t mean they won t ever have a problem with disk I/O Needing to add more SGA is common; there are multiple systems around the world which have an SGA of 50G or more (at least one has an SGA over 100G) What does a poor SQL look like? Long (20+ second) execution time Cumulative times much much larger than other SQL statements Using lots of system resources (usually disk I/O) 9 9

10. The one number I look at to know if the system is healthy Using the ESTD_PCT_OF_DB_TIME_FOR_READS in the gather info report Locate the table containing ESTD_PCT_OF_DB_TIME_FOR_READS and the row where SIZE_FACTOR equals 1 When ESTD_PCT_OF_DB_TIME_FOR_READS is less than 10, the system is good. Above 10, there is room for improvement. Above 45 or 50 the system is at times seriously impacted 10 10

11. Does Oracle need more memory allocated to it? See this blog posting to for SQL and guidelines on allocating more memory AWR: Workload Repository Report Search for Buffer Pool Advisory 22,595-9,354=13,241 saved disk reads by doubling memory. (94 mins) With average disk I/O performance a system can perform ~140 reads/sec Size Factor =1 is the current memory size, size=2 is double the current size 11 11

12. Check the System Waits for Biggest the Problems AWR/Automatic Workload Repository report Total time spent waiting most of this time is end use wait time User I/O being first is normal anything else of significance should have a Technical Support case opened Only 28% of time is CPU time, 72% of time spent is Wait related i.e. there are system bottlenecks From the Gather info report 12 12

13. AWR report for spotting disk I/O problems Disk I/O the #1 system related problem for Windchill PDMLink systems Almost 7 hours of disk I/O wait time i.e. users waiting A significant portion of the disk I/O is above 9 ms 13 13

14. IO metrics - some expected norms Slide copied from: How to get the most out of your I/O subsystem Djerdj Srdanov, Sr. DBA, Verizon Wireless Hotsos Oracle Performance Symposium -2013 Rough rule of thumb; 100 to 140 disk reads/sec RPM IOPS Seq MB/s Avg RW time <1ms ~3ms ~5ms ~9ms ~12ms <1ms ~3-5ms Disk I/O should ideally be less than 9ms SSD 15K 10K 7.2K 5.4K SAN - cache* 1M+ SAN - spindle* * - Operations with cache, if the resources are available, should be <1ms The actual values for IOPS, throughput will vary from frame to frame and from configuration to configuration 10 000+ ~200 ~150 ~100 ~50 400-500 180-200 150-180 120-150 100-120 10GB+ 10GB+ 100 000+ 14 14

15. Identifying Long Running SQL 15

16. #1:Using the Built in Monitoring Tools to identify slow SQL R10.x and later Using the built in Top SQL Sample Intervals No change in criteria is necessary (sometimes I subtract a day from start time ) #Server Status #Monitoring Tools Long Running SQL 16 16

17. PSM System Health Dashboard in locate SQL #Dashboard #Open #Host # 01 Monitoring(M)100 System Health 10.x Rollup Incident Indicators Response Times from Server Server` Response Times from Time Spent in Web Requests versus RMI 17

18. Database Drill Down: Identify problem SQL statements Sort Click on column headings to sort Problems 18

19. Using the AWR report to locate slow SQL statements Workload Repository Report Goal to identify 3-5 most impactful statements Search for SQL Statistics Four Key Reports to look at Worst performing SQL is at the top of each report 1970.7 seconds for one execution! 19 19

20. Finding the SQL statements in the Gather Info Report Gather info report Goal is to also identify the 3-5 most impactful statements See this blog posting for SQL to identify the statements in need of Tuning Using the Gather info report search for: Report End: Worst Total SQL by CPU Time Report End: Worst SQL by CPU Time per transaction Report End: Worst SQL By Factor Worst SQL is always at the bottom LINE# =0 is the start of a new SQL statement See CS51777 for instructions on how to interpret the gather info script output Sample output; one SQL statement is taking 949 seconds 20 20

21. You have a list of SQL statements to tune, now what? Search the Knowledge Base first Search the knowledge base for known solutions indexes or SPRs using the FROM and/or WHERE clause of the SQL statement Note: Searching using the columns in the select part of the statement could result in a lot of matches For example, this is a slow running SQL statement from a gather info report Search the KB with the string FROM StringValue A0 WHERE ((A0.idA3A4 Results in an article with index to solve the problem SQL 21 21

22. What if there isnt a match in the Knowledge base? A couple of options Open a Case with Technical Support There is a group dedicated to working on performance problems When opening a case, include any analysis done on slow running SQL and either: the report.txt file (gather info report output) SQLTHC (SQLT Health Check) output - file available from Oracle support included here for convenience Look to tune the SQL yourself using Guerilla SQL Tuning 22 22

23. Guerilla SQL Tuning 23

24. Guerilla SQL Tuning High Level Overview An easier way to tune SQL statements 1. Bring all information together into one file SQL statement to tune I. Related Table & Index data II. III. Explain plan if available 2. Estimate which input criteria should be the query driver(s) 3. Work out the solution(s) Generally there will be better or different indexes available I. Future solutions require more advanced analysis and maybe consideration beyond indexes i.e. statistics, histograms, partitioning, rewriting etc. II. Advanced analaysis required for <10% of statements I. 24 24

25. Guerilla SQL Tuning Step 1: Bring all the data into one file SQL Statement to tune Table Data: Do table statistics exist, are they up to date? I. Row Counts, Number of Blocks, Average Row size II. III. Columns Distinct values, Histograms? #of NULLS Index Report: Index definitions, columns and their order I. Distinct Keys (critical) II. Explain Plan if available I. 25 25

26. Guerilla SQL Tuning Step 2: Estimate which inputs are the query drivers Which WHERE clause input(s) will most reduce the overall number of blocks Oracle needs to touch? Estimate the number of rows which will be returned per input the fewer rows the better I. Multiple columns can be combined II. III. Repeat for each table in FROM clause(s) Goal is to estimate which inputs Oracle will use to drive the query execution Use as input to evaluate for indexes 26 26

27. Guerilla SQL Tuning Step 2 con t: Estimate which inputs are the query drivers SELECT A0.idA3B5,A0.idA3A5 FROM EPMMemberLink A0 WHERE (((((A0.depType <> :1 ) AND (A0.idA3B5 IN (SELECT /*+ CARDINALITY(wtot 100) */ * FROM TABLE(cast(:2 as TABLE_OF_NUMBER)))) AND (A0.idA3A5 IN (SELECT /*+ CARDINALITY(wtot 200) */ * FROM TABLE(cast(:3 as TABLE_OF_NUMBER)))) AND (A0.markForDeleteA2 = 0) Table data: # table of rows: 108,768,228 (from report.txt) TABLE_NAME COLUMN_NAME NUM_DISTINCT EPMMEMBERLINK DEPTYPE 13 EPMMEMBERLINK MARKFORDELETEA2 1 EPMMEMBERLINK IDA3A5 3,365,120 EPMMEMBERLINK IDA3B5 2,949,120 Calculations: Table_rows/num_distinct_idA3B5*(100) = 3,688 Rows Oracle *thinks* will be returned Table_rows/num_distinct_idA3A5*(200) = 6,464 Rows Oracle *thinks* will be returned Table_rows/deptype= 8,366,786 27 27

28. Guerilla SQL Tuning Step 3: Decide on the index type to create Type 1: Standard index One or more high cardinality columns need to be indexed I. II. A few low cardinality columns need to be indexed Look to compress some or most of the columns III. If there are 5 or more columns from the same table anywhere in the statement (including select or order by); type 1 index automatically Type 2: Reducing Table I/O index: covering indexes The columns in the 'select', 'where' and 'order by' clauses can fit in one index I. 5 or fewer columns in entire statement, likely a Type 2 index II. 28 28

29. Guerilla SQL Tuning Step 3 Con t: Index Type 1 (most common) considerations Examine input & join columns in the WHERE clause Goal: Find the minimum number of columns which will identify the fewest number of rows (there by reducing the # of blocks to be accessed) Identify column distinctness & group like distinct columns together I. Ignore columns which have a distinctness of "1 II. Workout expected "avg" rows to be returned for each input (ignore data skew & histograms for now) 29 29

30. Guerilla SQL Tuning Step 3 Con t: Index Type 1 - Making the index High Cardinality Column(s) index: Add the column which point at the fewest rows first Will often be the most distinct column, but with large inlists, ranges etc. this may not always be true Add minimum number of indexes for tables referenced in joins Low Cardinality Column(s) index: Least distinct first, if columns are worth compressing Very Low distinctness & wide values 30 30

31. Guerilla SQL Tuning Step 3 Con t: Index Type 2 (less common) considerations Identify ALL columns from the table in the statement Use an editor to search the statement to make sure all columns have been identified Columns have a funny way of hiding in plain sight Understand distinctness of each column If there is input criteria, workout rows to be returned like with in a Type 1 index Tricky: If there is no input criteria for the table Need to guestimate which table Oracle will start executing with if possible Column in table first access should be leading edge of index 31 31

32. Guerilla SQL Tuning Step 3 Con t: Index Type 2 (less common) Making the index Columns with input conditions go first Then the rest of the where clause columns I. II. Followed by the "select" and "order by" columns Order doesn't matter a lot unless some columns of low cardinality can be compressed If there are no input conditions for the table, the joining" column which will be first accessed should be first in the index This requires viewing or estimating the probable explain or plan in your head or on paper .. Not as hard to do as you might think 32 32

33. Guerilla SQL Tuning Making the index: What index to make? SQL (identified from AWR, PSM, report.txt etc) Type 2 index Create index on EPMMemberLink$preso on EPMMemberLink(ida3b5, ida3a5, depType, markfordeleteA2) online tablespace indx; SELECT A0.idA3B5,A0.idA3A5 FROM EPMMemberLink A0 WHERE (((((A0.depType <> :1 ) AND (A0.idA3B5 IN (SELECT /*+ CARDINALITY(wtot 100) */ * FROM TABLE(cast(:2 as TABLE_OF_NUMBER)))) AND (A0.idA3A5 IN (SELECT /*+ CARDINALITY(wtot 200) */ * FROM TABLE(cast(:3 as TABLE_OF_NUMBER)))) AND (A0.markForDeleteA2 = 0) Table data: # table of rows: 108,768,228 (from report.txt) TABLE_NAME COLUMN_NAME NUM_DISTINCT EPMMEMBERLINK DEPTYPE 13 EPMMEMBERLINK MARKFORDELETEA2 1 EPMMEMBERLINK IDA3A5 3,365,120 EPMMEMBERLINK IDA3B5 2,949,120 Calculations: Table_rows/num_distinct_idA3B5*(100) = 3,688 Table_rows/num_distinct_idA3A5*(200) = 6,464 Table_rows/deptype= 8,366,786 33 33

34. Guerilla SQL Tuning Last Thoughts . Getting good at tuning SQL takes time and practice Some Lessons learned the hard way.... Don't mix low cardinality columns (especially on the leading edge) with high cardinality columns in the same index, cartesian joins can occur or an index could be chosen for use inappropriatly because it looked very distinct . Use "online" when creating indexes in production systems If you re interested in learning more about Oracle performance checkout: Troubleshooting Oracle Performance Oracle SQL Tuning with Oracle SQLTXPLAIN 34 34

35. Summary Gather data to review Identify Major problems in the system Check for poor disk I/O performance Identify SQL statements causing problems Check KB for SQL Open case or Guerilla Tune the slow SQL 35 35

36. System Administrator Sessions PTC, Customer and Partner Lead Sessions Presenter(s) Day Time Title Location PTC103 PTC Windchill Business and System Admin Roadmap Walid Saad Monday 10:30AM 11:15AM 102A CUST104 We re Watching You: Improving PTC Windchill Performance with PSM and UEM PTC108 PTC System Monitor Deep-Dive: PTC Windchill Proactive Monitoring and Performance Troubleshooting PTC113 Improving PTC Windchill Performance John English (BAE Systems) Todd Votapka (BAE Systems Monday 10:30AM 11:15AM 104A Stephen Vaillancourt Monday 11:30AM 12:15PM 102B Ram Krishnamurthy Monday 1:30PM 2:15PM 103 PART100 PTC System Monitor: Extending to the End User Desktop Dan Breslin (Compuware) Monday 4:15PM 5:00PM 251 Dan Betry (Solar Turbines) PTC121 Guerrilla Oracle Tuning for PTC Windchill Stephen Vaillancourt Monday 4:15PM 5:00PM 102A PTC206 Windchill Architecture Deployment and Security Steve Dertien Tuesday 11:00AM 11:45AM 104A CUST215 Remote Sites, WANs and PTC Windchill Replicas: Put the Data Close to the User PTC210 Ask the Experts: PTC Windchill Mark Grothe (ITT Corp.) Ray Schussler (ITT Corp.) Tuesday 1:15PM 2:00PM 104A Will Kohler Tuesday 1:15PM 2:00PM 102A PTC217 Protecting Your Intellectual Property with PTC Windchill Steve Shaw Tuesday 2:15PM 3:00PM 104A PART213 PSM PLUS: Realizing and Extending the Value of PTC System Monitor (PSM) CUST317 Implementing PTC Windchill PDMLink with Security Labels: Pitfalls and Successes PTC311 Windchill Rehosting Utility: Present and Future Chris Stark (Compuware) Tuesday 2:40PM 3:00PM 256 Jeff Brodsky (Raytheon) Danny Poisson (Raytheon) Wednesday 10:15AM-11:00AM 103 Chris Watson Wednesday 11:15AM 12:00PM 102A 36 36

37. liveglobal.ptc.com