E N D
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