1 / 59

SQLFingerprints

muhammad
Télécharger la présentation

SQLFingerprints

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

    2. PSFT Tech PUG 10.1.05 www.go-faster.co.uk 2 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!

    3. PSFT Tech PUG 10.1.05 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

    4. PSFT Tech PUG 10.1.05 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

    5. PSFT Tech PUG 10.1.05 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

    6. PSFT Tech PUG 10.1.05 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

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

    8. 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.

    9. PSFT Tech PUG 10.1.05 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

    10. PSFT Tech PUG 10.1.05 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;

    11. PSFT Tech PUG 10.1.05 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

    12. PSFT Tech PUG 10.1.05 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.

    13. PSFT Tech PUG 10.1.05 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

    14. PSFT Tech PUG 10.1.05 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

    15. PSFT Tech PUG 10.1.05 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

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

    17. PSFT Tech PUG 10.1.05 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)

    18. PSFT Tech PUG 10.1.05 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);

    19. PSFT Tech PUG 10.1.05 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

    20. PSFT Tech PUG 10.1.05 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

    21. PSFT Tech PUG 10.1.05 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);

    22. PSFT Tech PUG 10.1.05 www.go-faster.co.uk 22 ScrollSelect() 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

    23. PSFT Tech PUG 10.1.05 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

    24. PSFT Tech PUG 10.1.05 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);

    25. PSFT Tech PUG 10.1.05 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

    26. PSFT Tech PUG 10.1.05 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.

    27. PSFT Tech PUG 10.1.05 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

    28. PSFT Tech PUG 10.1.05 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 )

    29. PSFT Tech PUG 10.1.05 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';

    30. PSFT Tech PUG 10.1.05 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.

    31. PSFT Tech PUG 10.1.05 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

    32. PSFT Tech PUG 10.1.05 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.

    33. PSFT Tech PUG 10.1.05 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

    34. PSFT Tech PUG 10.1.05 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 ;

    35. PSFT Tech PUG 10.1.05 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

    36. PSFT Tech PUG 10.1.05 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 …

    37. PSFT Tech PUG 10.1.05 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 ;

    38. PSFT Tech PUG 10.1.05 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!

    39. PSFT Tech PUG 10.1.05 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

    40. PSFT Tech PUG 10.1.05 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 ...

    41. PSFT Tech PUG 10.1.05 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

    42. PSFT Tech PUG 10.1.05 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 ...

    43. PSFT Tech PUG 10.1.05 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

    44. PSFT Tech PUG 10.1.05 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

    45. PSFT Tech PUG 10.1.05 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

    46. PSFT Tech PUG 10.1.05 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 …

    47. PSFT Tech PUG 10.1.05 www.go-faster.co.uk 47 SQR Cursor Status Report

    48. PSFT Tech PUG 10.1.05 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) /

    49. PSFT Tech PUG 10.1.05 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)

    50. PSFT Tech PUG 10.1.05 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.

    51. PSFT Tech PUG 10.1.05 www.go-faster.co.uk 51 Optimisation Techniques Indexes Disabling without hints From Clause Ordering ORDERED hints Leading Hint Transitive Closure Hugely effective on GP PSFT develops on Microsoft SQL Server, Stress Tests on DB2 and releases to Oracle! - Perhaps that will change now!!

    52. PSFT Tech PUG 10.1.05 www.go-faster.co.uk 52 Implementing Techniques Component Processor Views Views of a single table are updatable Scope of Hint Function Based Indexes For case insensitive search dialogues SearchSave PeopleCode Validate search criteria

    53. PSFT Tech PUG 10.1.05 www.go-faster.co.uk 53 Implementing Techniques Queries Hints in Expressions Move DISTINCT to Expression Manually reorder FROM clause SQL92 Outer Join Outer-join query secure records

    54. PSFT Tech PUG 10.1.05 www.go-faster.co.uk 54 Effective Date/Sequence Processing Query gratuitously adds EFFDT/EFFSEQ processing.

    55. PSFT Tech PUG 10.1.05 www.go-faster.co.uk 55 Effective Date/Sequence Processing Irrespective of whether key fields of not!

    56. PSFT Tech PUG 10.1.05 www.go-faster.co.uk 56 Effective Date/Sequence Processing SELECT A.EMPLID, A.EMPL_RCD, A.NAME FROM PS_EMPLOYEES A, PS_EMPLMT_SRCH_QRY A1 WHERE A.EMPLID = A1.EMPLID AND A.EMPL_RCD = A1.EMPL_RCD AND A1.ROWSECCLASS = 'HCDPALL' AND ( A.EFFDT = (SELECT MAX(A_ED.EFFDT) FROM PS_EMPLOYEES A_ED WHERE A.EMPLID = A_ED.EMPLID AND A.EMPL_RCD = A_ED.EMPL_RCD AND A_ED.EFFDT <= SYSDATE) AND A.EFFSEQ = (SELECT MAX(A_ES.EFFSEQ) FROM PS_EMPLOYEES A_ES WHERE A.EMPLID = A_ES.EMPLID AND A.EMPL_RCD = A_ES.EMPL_RCD AND A.EFFDT = A_ES.EFFDT) )

    57. PSFT Tech PUG 10.1.05 www.go-faster.co.uk 57 Effective Date/Sequence Processing You can (and should) delete it manually

    58. PSFT Tech PUG 10.1.05 www.go-faster.co.uk 58 Questions?

    59. 59 SQL*Fingerprints David Kurtz Go-Faster Consultancy Ltd. david.kurtz@go-faster.co.uk www.go-faster.co.uk

More Related