SQL*Fingerprints

1
SQLFingerprints

• David Kurtz
• Go-Faster Consultancy Ltd.
• david.kurtz_at_go-faster.co.uk
• 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

3
Resources

• If you cant 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
Performance Tuning

• What is Performance Tuning?
• Response Time
• Often down to poor SQL
• Oracle RDBMS
• V views, SQLTrace
• PeopleTools SQL Trace
• PeopleCode headers
• PeopleSoft Performance Monitor

5
Oracle RDBMS SQLTrace

• Trace batch processes via trigger
• Enable trace on PSAPPSRV processes
• List of all the SQL Statements

6
Where does the SQL Come From?

• Most performance tools will identify poor SQL
• But they wont tell you who or what submitted it
• Unless the application is instrumented

7
Tuning without code changes

• Database Parameters
• Indexes
• Hints via Stored Outlines (Oracle only)

8
If you can find it, then you can change it!

• This presentation is about how to find the SQL.

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

• 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
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 EMPLID1
• ORDER BY EMPLID

12
Component Processor

• This SQL is interpreted from contents of
  PeopleTools tables
• You wont 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
Search Dialogue

• SELECT DISTINCT EMPLID, EMPL_RCD, NAME,
  LAST_NAME_SRCH, SETID_DEPT, DEPTID, NAME_AC,
  PER_STATUS
• FROM PS_PERS_SRCH_GBL
• WHERE ROWSECCLASS1
• AND UPPER(NAME) LIKE UPPER('Smith') '' ESCAPE
  '\'
• ORDER BY NAME, EMPLID

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
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
PeopleCode

• PeopleSofts proprietary 3GL
• Tokenised in PeopleTools tables
• Can also be executed by Application Engine

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
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
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
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
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
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
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
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
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
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
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
Query

• SELECT A.EMPLID, A.ATTENDANCE, A.COURSE, B.DESCR,
  D.NAME, A.SESSION_NBR, TO_CHAR(A.STATUS_DT,'YYYY-M
  M-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
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
But Which query?

• One of these
• OPRID QRYNAME
• ----------------------------- --------------------
  ---------
• TRN002__SESSION_ROST
  ER
• TRN003__COURSE_WAITI
  NG_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
Batch Programs

• You know from the session
• a batch program
• which type of batch program
• vsession.process
• vsession.program

32
Cobol Stored Statement

• DELETE FROM PS_GP_PYE_STAT_WRK WHERE
  CAL_RUN_ID1 AND EMPLID
  BETWEEN 2 AND 3
• It is impossible to be certain by just looking at
  the SQL statement.

33
Cobol

• Stored Statements
• Delivered as Data Mover scripts
• PS_HOME/src/cbl/base
• Dynamic Statements
• Strings built by Cobol programs during execution

34
Stored Statement

• STORE GPPCANCL_D_WRKSTAT
• DELETE FROM PS_GP_PYE_STAT_WRK
• WHERE CAL_RUN_ID1
• AND EMPLID BETWEEN 2 AND 3

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
PeopleTools Trace COBOL

• Stored Statement
• GETSTMT StmtFSPJCOMB_S_COMGRP, length297 COM
  StmtSELECT A.PROCESS_GROUP
• Dynamic Statement
• DYNAMIC StmtFSPJECHF_U_CFERROR COM StmtUPDATE
  PS_PSA_ACCTDSTGL SET

37
Identify Stored Statements

• STORE GPPCANCL_D_WRKSTAT
• DELETE /GPPCANCL_D_WRKSTAT/
• FROM PS_GP_PYE_STAT_WRK
• WHERE CAL_RUN_ID1
• AND EMPLID BETWEEN 2 AND 3

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 wont remind you!

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
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
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
Hints in SQR

• Begin-Select On-ErrorSQL-Error
• /ALL_ROWS/
• m.model_statement
• m.parmcount
• m.statement_type
• ...

43
Hints in SQR

• begin-SELECT DISTINCT on-ErrorSQL-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 ltgt ' '
• ORDER BY RECNAME
• end-SELECT

44
Hints and Comments in SQR

• SELECT DISTINCT /SYSRECORD-13/ RECNAME
• FROM PSRECDEFN
• , (SELECT /ALL_ROWS/ 'x' FROM DUAL)
• WHERE RECTYPE 7
• AND SQLTABLENAME ltgt ' '
• ORDER BY RECNAME

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
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
SQR Cursor Status Report
48
Application Engine

• SELECT SERVERNAME
• FROM PSSERVERSTAT
• WHERE SERVERNAME ltgt 'PSNT'
• AND SERVERSTATUS '3'
• AND ( ROUND((( SYSDATE) - (LASTUPDDTTM)) 1440,
  0) lt 10)
• /

49
Application Engine

• Select(PRCSPURGE_AET.SERVERNAME)
• SELECT SERVERNAME
• FROM PSSERVERSTAT
• WHERE SERVERNAME ltgt Bind(PRCSPURGE_AET.SERVERNAM
  ERUN)
• AND SERVERSTATUS '3'
• AND ( DateTimeDiff(LASTUPDDTTM,
  CurrentDateTimeIn) lt 10)

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
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
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
Implementing Techniques

• Queries
• Hints in Expressions
• Move DISTINCT to Expression
• Manually reorder FROM clause
• SQL92 Outer Join
• Outer-join query secure records

54
Effective Date/Sequence Processing

• Query gratuitously adds EFFDT/EFFSEQ processing.

55
Effective Date/Sequence Processing

• Irrespective of whether key fields of not!

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 lt 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
Effective Date/Sequence Processing

• You can (and should) delete it manually

58
Questions?
59
SQLFingerprints

• David Kurtz
• Go-Faster Consultancy Ltd.
• david.kurtz_at_go-faster.co.uk
• www.go-faster.co.uk