10bf2703ce481339c867621b31e2a6eb.ppt
- Количество слайдов: 59
SQL*Fingerprints David Kurtz Go-Faster Consultancy Ltd. david. kurtz@go-faster. co. uk www. go-faster. co. uk 1
Who am I? • DBA • Book – Independent consultant • System Performance tuning – www. psftdba. com – People. Soft ERP – Oracle RDBMS • UK Oracle User Group – Unix SIG • Oak Table – www. oaktable. net PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk 2
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 People. Soft for the Oracle DBA PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk 3
Performance Tuning • • What is Performance Tuning? Response Time Often down to poor SQL Oracle RDBMS – V$ views, SQL*Trace • People. Tools SQL Trace – People. Code headers • People. Soft Performance Monitor PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk 4
Oracle RDBMS SQL*Trace • Trace batch processes via trigger • Enable trace on PSAPPSRV processes • List of all the SQL Statements PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk 5
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 PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk 6
Tuning without code changes • Database Parameters • Indexes • Hints via Stored Outlines (Oracle only) PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk 7
If you can find it, then you can change it! This presentation is about how to find the SQL. www. go-faster. co. uk 8
DBMS_APPLICATION_INFO • Oracle implemented DBMS_APPLICATION_INFO module to solve this problem in Oracle Apps. – SET_MODULE • People. Soft only uses CLIENT_INFO – So you only know which OPRID is responsible for a statement PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk 9
• 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; PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk 10
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 10. 1. 05 EMPLID BY PSFT Tech PUG www. go-faster. co. uk 11
Component Processor • This SQL is interpreted from contents of People. Tools 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-HH 24. MI. SS. "000000" – One SQL loading one table per scroll – Predicated and sorted by the ‘key’ fields. PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk 12
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 PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk 13
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 PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk 14
Translate Values • Hint added from PT 8. 43 SELECT /*+ FIRST_ROWS */ NAME_TYPE, ORDER_BY_SEQ, NAME_TYPE_DESCR FROM PS_NAME_TYPE_TBL A ORDER BY NAME_TYPE PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk 15
People. Code • People. Soft’s proprietary 3 GL • Tokenised in People. Tools tables • Can also be executed by Application Engine PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk 16
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) PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk 17
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); PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk 18
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 PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk 19
Scroll. Select() SELECT SETID, DEPTID, POSITION_POOL_ID, SETID_JOBCODE, POSITION_NBR, EMPLID, EMPL_RCD, JOB_REQ_NBR, TRIGGER_RECORD, TIME_STAMP, TO_CHAR(TIME_STAMP, 'YYYY-MM-DDHH 24. 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, POSITION_NBR, EMPLID, EMPL_RCD, JOB_REQ_NBR, TRIGGER_RECORD, PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk 20 TIME_STAMP
Scroll. Select() Scroll. Select(1, Record. ENCUMB_TRIGGER, "Where TRIGGER_RECORD = 'J' and emplid = : 1 and EMPL_RCD = : 2 and PROCESSED = 'N'", &EMPLID, &EMPL_RCD); PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk 21
Scroll. Select() • Upper Case • Where clause – Select clause • All the columns/fields – From clause – As coded – Binds and/or literals • Table name – Order by clause • Key fields PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk 22
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. GOTOPNLACTION FROM PS_CO_PNLFIELD_VW FILL WHERE PNLNAME = : 1 and FIELDTYPE = 16 and LBLTYPE = 7 AND RECNAME = : 2 and FIELDNAME = : 3 PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk 23
Rowset Fill() function &Pnl. Field_Rs = Create. Rowset(Record. CO_PNLFIELD_VW); &Pnl. Field_Rs. Flush(); &Pnl. Field_Rs. Fill("WHERE PNLNAME = : 1 and FIELDTYPE = 16 and LBLTYPE = 7 AND RECNAME = : 2 and FIELDNAME = : 3", %Page, &Link. Rec. Name, &Link. Field. Name); PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk 24
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 PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk 25
A useful trick • Joining tables in Scroll/Fill functions &Pnl. Field_Rs = Create. Rowset(Record. X); &Pnl. Field_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. PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk 26
Searching People. Code • Application Designer ‘Find In…’ Utility – Effective but slow • Save all People. Code to a text file – Search for ; – Save result – Search text file with word PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk 27
Query SELECT A. EMPLID, A. ATTENDANCE, A. COURSE, B. DESCR, D. NAME, A. SESSION_NBR, TO_CHAR(A. STATUS_DT, 'YYYY-MMDD'), B. COURSE FROM PS_TRAINING A, PS_COURSE_TBL B, PS_PERSONAL_DTA_VW D, PS_PERS_SRCH_QRY D 1 WHERE D. EMPLID = D 1. EMPLID AND D 1. ROWSECCLASS = 'HCDPALL' AND ( A. COURSE = : 1 AND A. ATTENDANCE IN ('S', 'W') AND A. COURSE = B. COURSE PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk AND A. EMPLID = D. EMPLID ) 28
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'; PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk 29
But Which query? • One of these OPRID QRYNAME ---------------TRN 002__SESSION_ROSTER TRN 003__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. PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk 30
Batch Programs • You know from the session – a batch program – which type of batch program • v$session. process • v$session. program PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk 31
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. PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk 32
Cobol • Stored Statements – Delivered as Data Mover scripts – %PS_HOME%/src/cbl/base • Dynamic Statements – Strings built by Cobol programs during execution PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk 33
Stored Statement STORE GPPCANCL_D_WRKSTAT DELETE FROM PS_GP_PYE_STAT_WRK WHERE CAL_RUN_ID=: 1 AND EMPLID BETWEEN : 2 AND : 3 ; PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk 34
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 PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk 35
People. Tools 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 … PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk 36
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 ; PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk 37
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 People. Soft documentation won’t remind you! PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk 38
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 PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk 39
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. . . PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk 40
SQR let $Where = '' if not isblank($Paygroup) let $Where = ' AND GRP. GP_PAYGROUP = ''' || $Paygroup || '''' let $Where_B = ' AND B. GP_PAYGROUP = ''' || $Paygroup || '''' End-If PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk 41
Hints in SQR Begin-Select On-Error=SQL-Error /*+ALL_ROWS*/ m. model_statement m. parmcount m. statement_type. . . PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk 42
Hints in SQR begin-SELECT DISTINCT on-Error=SQL-Error /*SYSRECORD-13*/ RECNAME &Record 13_Rec. Name if (((#current-line + 1) = #sqr-max-lines) and $Detail. Err. Found = 'Y') or ($Detail. Err. Found = 'N') move 'Y' to $Detail. Err. Found do Print. Section. Headings end-if let #rows = #rows +1 print &Record 13_Rec. Name (+1, #Start 1) FROM PSRECDEFN , (SELECT /*+ALL_ROWS*/ 'x' FROM DUAL) WHERE RECTYPE = 7 AND SQLTABLENAME <> ' ' ORDER BY RECNAME end-SELECT PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk 43
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 PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk 44
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 PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk 45
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 … PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk 46
SQR Cursor Status Report PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk 47
Application Engine SELECT SERVERNAME FROM PSSERVERSTAT WHERE SERVERNAME <> 'PSNT' AND SERVERSTATUS = '3' AND ( ROUND((( SYSDATE) (LASTUPDDTTM)) * 1440, 0) < 10) / PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk 48
Application Engine %Select(PRCSPURGE_AET. SERVERNAME) SELECT SERVERNAME FROM PSSERVERSTAT WHERE SERVERNAME <> %Bind(PRCSPURGE_AET. SERVERNAMERU N) AND SERVERSTATUS = '3' AND ( %Date. Time. Diff(LASTUPDDTTM, %Current. Date. Time. In) < 10) PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk 49
Application Engine • Mostly, what you code is what you get • &Bind() variables mostly replaced with literal values – Lots of hard parsing – Reuse. Statement attribute on AE step • From People. Tools 8. x, rewritten in C++, and so can execute People. Code. • Bind variables in People. Code remain bind variables in SQL. PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk 50
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 DB 2 and releases to Oracle! - Perhaps that will change now!! PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk 51
Implementing Techniques • Component Processor – Views • Views of a single table are updatable • Scope of Hint – Function Based Indexes • For case insensitive search dialogues – Search. Save People. Code • Validate search criteria PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk 52
Implementing Techniques • Queries – Hints in Expressions – Move DISTINCT to Expression – Manually reorder FROM clause – SQL 92 Outer Join • Outer-join query secure records PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk 53
Effective Date/Sequence Processing • Query gratuitously adds EFFDT/EFFSEQ processing. PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk 54
Effective Date/Sequence Processing • Irrespective of whether key fields of not! PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk 55
Effective Date/Sequence Processing SELECT A. EMPLID, A. EMPL_RCD, A. NAME FROM PS_EMPLOYEES A, PS_EMPLMT_SRCH_QRY A 1 WHERE A. EMPLID = A 1. EMPLID AND A. EMPL_RCD = A 1. EMPL_RCD AND A 1. 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) ) PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk 56
Effective Date/Sequence Processing • You can (and should) delete it manually PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk 57
Questions? PSFT Tech PUG 10. 1. 05 www. go-faster. co. uk 58
SQL*Fingerprints David Kurtz Go-Faster Consultancy Ltd. david. kurtz@go-faster. co. uk www. go-faster. co. uk 59