1 / 59

SQL*Fingerprints

SQL*Fingerprints. David Kurtz Go-Faster Consultancy Ltd. david.kurtz@go-faster.co.uk www.go-faster.co.uk. 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. Who am I?.

meryl
Download Presentation

SQL*Fingerprints

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. SQL*Fingerprints David Kurtz Go-Faster Consultancy Ltd. david.kurtz@go-faster.co.uk www.go-faster.co.uk

  2. 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 Who am I? www.go-faster.co.uk

  3. 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 • Further reading: • Chapter 11 of PeopleSoft for the Oracle DBA www.go-faster.co.uk

  4. Performance Tuning • What is Performance Tuning? • Response Time • Often down to poor SQL • Oracle RDBMS • V$ views, SQL*Trace • PeopleTools SQL Trace • PeopleCode headers • PeopleSoft Performance Monitor www.go-faster.co.uk

  5. Oracle RDBMS SQL*Trace • Trace batch processes via trigger • Enable trace on PSAPPSRV processes • List of all the SQL Statements www.go-faster.co.uk

  6. Where does the SQL Come From? • Most performance tools will identify poor SQL • But they won’t tell you who or what submitted it • Unless the application is instrumented www.go-faster.co.uk

  7. Tuning without code changes • Database Parameters • Indexes • Hints via Stored Outlines (Oracle only) www.go-faster.co.uk

  8. If you can find it, then you can change it! This presentation is about how to find the SQL. www.go-faster.co.uk

  9. DBMS_APPLICATION_INFO • Oracle implemented DBMS_APPLICATION_INFO module to solve this problem in Oracle Apps. • SET_MODULE • PeopleSoft only uses CLIENT_INFO • So you only know which OPRID is responsible for a statement www.go-faster.co.uk

  10. In the following slides I am going to show a number of SQL statements. • Can you work out what produced them? ALTER SESSION SET _AUDIENCE_PARTICIPATION_ENABLED=TRUE; www.go-faster.co.uk

  11. Component Processor SELECT EMPLID, PER_STATUS, TO_CHAR(BIRTHDATE,'YYYY-MM-DD'), BIRTHPLACE, BIRTHCOUNTRY,BIRTHSTATE, TO_CHAR(DT_OF_DEATH,'YYYY-MM-DD'), TO_CHAR(ORIG_HIRE_DT,'YYYY-MM-DD'), HIGHLY_COMP_EMPL_C, HIGHLY_COMP_EMPL_P FROM PS_PERSON WHERE EMPLID=:1 ORDER BY EMPLID www.go-faster.co.uk

  12. Component Processor • This SQL is interpreted from contents of PeopleTools tables • You won’t find this SQL anywhere in code. • Generally all UPPER CASE • Dates converted to strings in the format YYYY-MM-DD • Date-times converted to strings in the format YYYY-MM-DD-HH24.MI.SS."000000" • One SQL loading one table per scroll • Predicated and sorted by the ‘key’ fields. www.go-faster.co.uk

  13. Search Dialogue SELECT DISTINCT EMPLID, EMPL_RCD, NAME, LAST_NAME_SRCH, SETID_DEPT, DEPTID, NAME_AC, PER_STATUS FROM PS_PERS_SRCH_GBL WHERE ROWSECCLASS=:1 AND UPPER(NAME) LIKE UPPER('Smith') || '%' ESCAPE '\' ORDER BY NAME, EMPLID www.go-faster.co.uk

  14. Component Processor Variations • Search Dialogue queries are • always DISTINCT • Often contain ROWSECCLASS • User search criteria as literals • UPPER() function when case insensitive • Wildcard added automatically when search string shorter than column www.go-faster.co.uk

  15. Translate Values • Hint added from PT8.43 SELECT /*+ FIRST_ROWS */ NAME_TYPE, ORDER_BY_SEQ, NAME_TYPE_DESCR FROM PS_NAME_TYPE_TBL A ORDER BY NAME_TYPE www.go-faster.co.uk

  16. PeopleCode • PeopleSoft’s proprietary 3GL • Tokenised in PeopleTools tables • Can also be executed by Application Engine www.go-faster.co.uk

  17. SQLExec() Select A.BEN_STATUS from PS_ACTN_REASON_TBL A where A.ACTION = :1 and A.ACTION_REASON = (Select min(AA.ACTION_REASON) from PS_ACTN_REASON_TBL AA where AA.ACTION = A.ACTION) and A.EFFDT = (Select max(AAA.EFFDT) from PS_ACTN_REASON_TBL AAA where AAA.ACTION = A.ACTION and AAA.ACTION_REASON = A.ACTION_REASON) www.go-faster.co.uk

  18. SQLExec() SQLExec("Select A.BEN_STATUS from PS_ACTN_REASON_TBL A where A.ACTION = :1 and A.ACTION_REASON = (Select min(AA.ACTION_REASON) from PS_ACTN_REASON_TBL AA where AA.ACTION = A.ACTION) and A.EFFDT = (Select max(AAA.EFFDT) from PS_ACTN_REASON_TBL AAA where AAA.ACTION = A.ACTION and AAA.ACTION_REASON = A.ACTION_REASON)", &ACTION, &FETCH_STATUS); www.go-faster.co.uk

  19. SQLExec() • What you code is what you get • Mixed Case • More complex • Joins several tables • Hand Coded • Multi character row source identifiers (table aliases) • Mostly using bind variables • literals possible if dynamically generate SQL www.go-faster.co.uk

  20. ScrollSelect() SELECT SETID, DEPTID, POSITION_POOL_ID, SETID_JOBCODE, JOBCODE, POSITION_NBR, EMPLID, EMPL_RCD, JOB_REQ_NBR, TRIGGER_RECORD, TIME_STAMP, TO_CHAR(TIME_STAMP,'YYYY-MM-DD-HH24.MI.SS."000000"'), PROCESSED FROM PS_ENCUMB_TRIGGER Where TRIGGER_RECORD = 'J' and emplid = :1 and EMPL_RCD = :2 and PROCESSED = 'N' ORDER BY SETID, DEPTID, POSITION_POOL_ID, SETID_JOBCODE, JOBCODE, POSITION_NBR, EMPLID, EMPL_RCD, JOB_REQ_NBR, TRIGGER_RECORD, TIME_STAMP www.go-faster.co.uk

  21. ScrollSelect() ScrollSelect(1, Record.ENCUMB_TRIGGER, Record.ENCUMB_TRIGGER, "Where TRIGGER_RECORD = 'J' and emplid = :1 and EMPL_RCD = :2 and PROCESSED = 'N'", &EMPLID, &EMPL_RCD); www.go-faster.co.uk

  22. Upper Case Select clause All the columns/fields From clause Table name Order by clause Key fields Where clause As coded Binds and/or literals ScrollSelect() www.go-faster.co.uk

  23. Rowset Fill() function SELECT FILL.PNLNAME, FILL.PNLFLDID, FILL.FIELDNUM, FILL.PNLFIELDNAME, FILL.FIELDTYPE, FILL.RECNAME, FILL.FIELDNAME, FILL.LBLTYPE, FILL.GOTOPORTALNAME, FILL.GOTONODENAME, FILL.GOTOMENUNAME, FILL.GOTOPNLGRPNAME, FILL.GOTOMKTNAME, FILL.GOTOPNLNAME, FILL.GOTOPNLACTION FROM PS_CO_PNLFIELD_VW FILL WHERE PNLNAME = :1 and FIELDTYPE = 16 and LBLTYPE = 7 AND RECNAME = :2 and FIELDNAME = :3 www.go-faster.co.uk

  24. Rowset Fill() function &PnlField_Rs = CreateRowset(Record.CO_PNLFIELD_VW); &PnlField_Rs.Flush(); &PnlField_Rs.Fill("WHERE PNLNAME = :1 and FIELDTYPE = 16 and LBLTYPE = 7 AND RECNAME = :2 and FIELDNAME = :3", %Page, &LinkRecName, &LinkFieldName); www.go-faster.co.uk

  25. Rowset functions • Replacing older scroll functions • [OT: Although I think they are less efficient at run time!] • Complexities hidden inside view • Upper Case select clause • Mixed case where clause www.go-faster.co.uk

  26. A useful trick • Joining tables in Scroll/Fill functions &PnlField_Rs = CreateRowset(Record.X); &PnlField_Rs.Fill(",PS_Y A WHERE FILL.MYCOL = A.MYCOL …",…); • And this is what you get SELECT FILL.… FROM PS_X FILL ,PS_Y A WHERE FILL.MYCOL = A.MYCOL … • This can be more efficient than burying everything in a view • especially if a group function is involved. www.go-faster.co.uk

  27. Searching PeopleCode • Application Designer ‘Find In…’ Utility • Effective but slow • Save all PeopleCode to a text file • Search for ; • Save result • Search text file with word www.go-faster.co.uk

  28. Query SELECT A.EMPLID, A.ATTENDANCE, A.COURSE, B.DESCR, D.NAME, A.SESSION_NBR, TO_CHAR(A.STATUS_DT,'YYYY-MM-DD'),B.COURSE FROM PS_TRAINING A, PS_COURSE_TBL B, PS_PERSONAL_DTA_VW D, PS_PERS_SRCH_QRY D1 WHERE D.EMPLID = D1.EMPLID AND D1.ROWSECCLASS = 'HCDPALL' AND ( A.COURSE = :1 AND A.ATTENDANCE IN ('S','W') AND A.COURSE = B.COURSE AND A.EMPLID = D.EMPLID ) www.go-faster.co.uk

  29. But Which query? SELECT a.oprid, a.qryname FROM psqryrecord a , psqryrecord b , psqryrecord d WHERE a.oprid = b.oprid AND a.qryname = b.qryname AND a.oprid = d.oprid AND a.qryname = d.qryname AND a.corrname = 'A' AND a.recname = 'TRAINING' AND b.corrname = 'B' AND b.recname = 'COURSE_TBL' AND d.corrname = 'D' AND d.recname = 'PERSONAL_DTA_VW'; www.go-faster.co.uk

  30. But Which query? • One of these OPRID QRYNAME ----------------------------- ----------------------------- TRN002__SESSION_ROSTER TRN003__COURSE_WAITING_LIST • CAUTION: Unrestricted use of query will bring a system to its knees! • Users often clone public queries to their own private queries, and make a few tweaks. www.go-faster.co.uk

  31. Batch Programs • You know from the session • a batch program • which type of batch program • v$session.process • v$session.program www.go-faster.co.uk

  32. Cobol Stored Statement DELETE FROM PS_GP_PYE_STAT_WRK WHERE CAL_RUN_ID=:1 AND EMPLID BETWEEN :2 AND :3 ; • It is impossible to be certain by just looking at the SQL statement. www.go-faster.co.uk

  33. Cobol • Stored Statements • Delivered as Data Mover scripts • %PS_HOME%/src/cbl/base • Dynamic Statements • Strings built by Cobol programs during execution www.go-faster.co.uk

  34. Stored Statement STORE GPPCANCL_D_WRKSTAT DELETE FROM PS_GP_PYE_STAT_WRK WHERE CAL_RUN_ID=:1 AND EMPLID BETWEEN :2 AND :3 ; www.go-faster.co.uk

  35. Stored Statements • Stored statements usually contain bind variables • Dynamic statements sometimes contain literal values • But not always! • Individually named in timings report • Can be difficult to distinguish www.go-faster.co.uk

  36. PeopleTools Trace COBOL • Stored Statement • GETSTMT Stmt=FSPJCOMB_S_COMGRP, length=297 COM Stmt=SELECT A.PROCESS_GROUP … • Dynamic Statement • DYNAMIC Stmt=FSPJECHF_U_CFERROR COM Stmt=UPDATE PS_PSA_ACCTDSTGL SET … www.go-faster.co.uk

  37. Identify Stored Statements STORE GPPCANCL_D_WRKSTAT DELETE /*GPPCANCL_D_WRKSTAT*/ FROM PS_GP_PYE_STAT_WRK WHERE CAL_RUN_ID=:1 AND EMPLID BETWEEN :2 AND :3 ; www.go-faster.co.uk

  38. Identify Stored Statements • Stored in PS_SQLSTMT_TBL • Can add comment with PL/SQL • Download code for book • Ch.11: stmtid.sql (Oracle Only) • If you customise stored statements then add the comment manually to the .dms script • Remember to compare DMS scripts at upgrade time • The PeopleSoft documentation won’t remind you! www.go-faster.co.uk

  39. SQR • What you code is what you get • Search for the SQL in the SQR • And the included SQC files • Can also embed string variables in SQL • content of variable dynamically becomes a part of the SQL statement. • Source of hard parsing www.go-faster.co.uk

  40. SQR ... FROM PS_GP_CAL_RUN_DTL A, PS_GP_CALENDAR B, PS_GP_CAL_PRD C WHERE A.CAL_RUN_ID = $Cal_Run_ID [$Where] AND B.GP_PAYGROUP = A.GP_PAYGROUP AND B.CAL_ID = A.CAL_ID AND C.CAL_PRD_ID = B.CAL_PRD_ID ... www.go-faster.co.uk

  41. SQR let $Where = '' if not isblank($Paygroup) let $Where = ' AND GRP.GP_PAYGROUP = ''' || $Paygroup || '''' let $Where_B = ' AND B.GP_PAYGROUP = ''' || $Paygroup || '''' End-If www.go-faster.co.uk

  42. Hints in SQR Begin-Select On-Error=SQL-Error /*+ALL_ROWS*/ m.model_statement m.parmcount m.statement_type ... www.go-faster.co.uk

  43. Hints in SQR begin-SELECT DISTINCT on-Error=SQL-Error /*SYSRECORD-13*/ RECNAME &Record13_RecName if (((#current-line + 1) = #sqr-max-lines) and $DetailErrFound = 'Y') or ($DetailErrFound = 'N') move 'Y' to $DetailErrFound do PrintSectionHeadings end-if let #rows = #rows +1 print &Record13_RecName (+1,#Start1) FROM PSRECDEFN , (SELECT /*+ALL_ROWS*/ 'x' FROM DUAL) WHERE RECTYPE = 7 AND SQLTABLENAME <> ' ' ORDER BY RECNAME end-SELECT www.go-faster.co.uk

  44. Hints and Comments in SQR SELECT DISTINCT /*SYSRECORD-13*/ RECNAME FROM PSRECDEFN , (SELECT /*+ALL_ROWS*/ 'x' FROM DUAL) WHERE RECTYPE = 7 AND SQLTABLENAME <> ' ' ORDER BY RECNAME www.go-faster.co.uk

  45. SQR Cursor Status Report • -s parameter causes SQR to print cursor status report • after execution • Static SQL only • Parsed before execution begins. • If there is a syntax error in static SQL then program will fail immediately www.go-faster.co.uk

  46. SQR Cursor Status Report Cursor Status: … Cursor #34: SQL = SELECT substr(C.PRCSNAME, 1, 5), C.PRCSNAME FROM PS_PRCSDEFN C WHERE C.PRCSTYPE like 'SQR%' Compiles = 2 Executes = 1 Rows = 11 … www.go-faster.co.uk

  47. SQR Cursor Status Report www.go-faster.co.uk

  48. Application Engine SELECT SERVERNAME FROM PSSERVERSTAT WHERE SERVERNAME <> 'PSNT' AND SERVERSTATUS = '3' AND ( ROUND((( SYSDATE) - (LASTUPDDTTM)) * 1440, 0) < 10) / www.go-faster.co.uk

  49. Application Engine %Select(PRCSPURGE_AET.SERVERNAME) SELECT SERVERNAME FROM PSSERVERSTAT WHERE SERVERNAME <> %Bind(PRCSPURGE_AET.SERVERNAMERUN) AND SERVERSTATUS = '3' AND ( %DateTimeDiff(LASTUPDDTTM, %CurrentDateTimeIn) < 10) www.go-faster.co.uk

  50. Application Engine • Mostly, what you code is what you get • &Bind() variables mostly replaced with literal values • Lots of hard parsing • ReuseStatement attribute on AE step • From PeopleTools 8.x, rewritten in C++, and so can execute PeopleCode. • Bind variables in PeopleCode remain bind variables in SQL. www.go-faster.co.uk

More Related