Sometimes, performance analysis will turn up a problem SQL query that is probably a PS/Query. However, I need to know which PS/Query it is should I wish to alter it or talk to the user who wrote it.
Is it a PS/Query?
It is quite easy to spot SQL queries that are generated from queries defined in the PS/Query tool. These are typical characteristics:
- Single character row source aliases (eg. A, B, D)
- The same row source with a suffix 1 (eg. D1) for query security records.
- Effective date/sequence subqueries are always correlated back to the same table.
- Order by column position number rather than column names or aliases.
Sometimes, you may find SQL that looks like a PS/Query coming from other parts of PeopleSoft because a developer has copied the text of a PS/Query, usually into an Application Engine step.
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 )
The text of a PS/Query is not stored in the database. Instead, as with other objects in PeopleSoft, it is held as various rows in PeopleTools tables. The PSQRY% tables are used to generate the SQL on demand. We can query these tables to identify the query.
PSQRYRECORD holds a row for every record referenced in the query (not including effective date/sequence subqueries). My usual tactic is to write a SQL query on PSQRYRECORD, like the one below, that looks for PS/Queries that reference these tables with these table aliases (see
PeopleSoft for the Oracle DBA, Chapter 11).
REM findqry.sql
REM (c)Go-Faster Consultancy 2012
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'
/
The example PS/Query above is TRN003__COURSE_WAITING_LIST from the HCM demo database. However, my query on PSQRYRECORD found another PS/Queries with the same 3 records using the same row source aliases. It is worth looking at queries on the same tables as they often suffer from the same problems, and you might want to make the same fix.
Another source of results for this query (though not this time) can be when users copy a public PS/Query to a private one so they can alter it in isolation.
OPRID QRYNAME
------------------------------ ------------------------------
TRN002__SESSION_ROSTER
TRN003__COURSE_WAITING_LIST
Writing the query on PSQRYRECORD to find queries, which always is slightly different each time, is quite boring. So I have written a script that will dynamically generate the SQL to identify a PS/Query.
Start with a SQL_ID
A SQL tuning activity will usually identify the SQL_ID and plan hash value of a statement. If you are lucky, AWR will have captured the text and execution plan. If not, you may have to try looking for a different SQL_ID that produces the same execution plan. From the statement text, it is easy to see whether it might be a PS/Query.
In this example, I have cut the SQL statement and execution plan back to show just the tables and indexes referenced.
SQL_ID c3h6vf2w5fxgp
--------------------
SELECT …
FROM PSTREELEAF B, PSTREENODE C, PS_OPER_UNIT_TBL A, PS_PRODUCT_TBL G
…
UNION SELECT …
FROM PSTREENODE D,PS_TREE_NODE_TBL E, PSTREELEAF F
…
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------------
| * 7 | INDEX STORAGE FAST FULL SCAN | PSBPSTREELEAF | 426K| 19M| | 1178 (1)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID BATCHED| PS_PRODUCT_TBL | 1 | 41 | | 3 (0)| 00:00:01 |
| * 11 | INDEX RANGE SCAN | PS_PRODUCT_TBL | 1 | | | 2 (0)| 00:00:01 |
| * 14 | INDEX RANGE SCAN (MIN/MAX) | PS_PRODUCT_TBL | 1 | 21 | | 2 (0)| 00:00:01 |
| * 15 | TABLE ACCESS STORAGE FULL | PSTREENODE | 135K| 5709K| | 663 (1)| 00:00:01 |
| * 17 | INDEX STORAGE FAST FULL SCAN | PS_OPER_UNIT_TBL | 1791 | 35820 | | 4 (0)| 00:00:01 |
| * 20 | INDEX RANGE SCAN (MIN/MAX) | PS_PSTREENODE | 1 | 33 | | 3 (0)| 00:00:01 |
| * 23 | INDEX RANGE SCAN (MIN/MAX) | PSAPSTREELEAF | 1 | 32 | | 3 (0)| 00:00:01 |
| * 26 | INDEX RANGE SCAN (MIN/MAX) | PS_OPER_UNIT_TBL | 1 | 20 | | 2 (0)| 00:00:01 |
| 33 | TABLE ACCESS INMEMORY FULL | PS_TREE_NODE_TBL | 35897 | 1647K| | 6 (0)| 00:00:01 |
| * 35 | TABLE ACCESS STORAGE FULL | PSTREENODE | 167K| 9670K| | 663 (1)| 00:00:01 |
|- * 36 | INDEX RANGE SCAN | PS_PSTREELEAF | 1 | 39 | | 1267 (1)| 00:00:01 |
| 37 | INDEX STORAGE FAST FULL SCAN | PS_PSTREELEAF | 480K| 17M| | 1267 (1)| 00:00:01 |
| * 40 | INDEX RANGE SCAN (MIN/MAX) | PS_PSTREENODE | 1 | 33 | | 3 (0)| 00:00:01 |
| * 43 | INDEX RANGE SCAN (MIN/MAX) | PS_TREE_NODE_TBL | 1 | 28 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
…
7 - SEL$1 / B@SEL$1
10 - SEL$1 / G@SEL$1
11 - SEL$1 / G@SEL$1
…
15 - SEL$1 / C@SEL$1
17 - SEL$1 / A@SEL$1
…
33 - SEL$6 / E@SEL$6
35 - SEL$6 / D@SEL$6
36 - SEL$6 / F@SEL$6
37 - SEL$6 / F@SEL$6
…
I use this query on DBA_HIST_SQL_PLAN to extract the tables that have single-character row source aliases that correspond to PeopleSoft records, and put them into PLAN_TABLE. I use this table because it is delivered by Oracle as a global temporary table, so it is always there and I can make use of it even if I only have read-only access.
INSERT INTO plan_table (object_name, object_alias)
with p as ( --plan lines with single letter aliases
SELECT DISTINCT object_owner, object_type, object_name, regexp_substr(object_alias,'[[:alpha:]]',2,1) object_alias
from dba_hist_sql_plan p
, ps.psdbowner d
where p.sql_id = '&&sql_id' --put SQL ID here--
and p.object_name IS NOT NULL
and p.object_owner = d.ownerid
and regexp_like(object_alias,'"[[:alpha:]]"') --single character aliases
), r as ( --PeopleSoft table records and the table name
select r.recname, DECODE(r.sqltablename,' ','PS_'||r.recname,r.sqltablename) sqltablename
from psrecdefn r
where r.rectype = 0 --PeopleSoft table records
)
select r.recname, object_alias --referenced table
from p, r
where p.object_type like 'TABLE%'
and p.object_name = r.sqltablename
union --a query plan may reference an index and not the table
select r.recname, object_alias --table for referenced index
from p, r
, all_indexes i
where p.object_type like 'INDEX%'
and i.index_name = p.object_name
and i.owner = p.object_owner
and i.table_name = r.sqltablename
order by 2,1
/
I now have a list of records and row source aliases aliases
RECNAME O
--------------- -
OPER_UNIT_TBL A
PSTREELEAF B
PSTREENODE C
PSTREENODE D
TREE_NODE_TBL E
PSTREELEAF F
PRODUCT_TBL G
Next, I can run this anonymous PL/SQL block to dynamically build the SQL query on PSQRYRECORD (one reference for every table) and execute it to find the matching PS/Queries
DECLARE
l_sep1 VARCHAR2(20);
l_sep2 VARCHAR2(20);
l_counter INTEGER := 0;
l_sql CLOB := 'SELECT r1.oprid, r1.qryname';
l_where CLOB;
TYPE t_query IS RECORD (oprid VARCHAR2(30), qryname VARCHAR2(30));
TYPE a_query IS TABLE OF t_query INDEX BY PLS_INTEGER;
l_query a_query;
BEGIN
FOR i IN(
SELECT *
FROM plan_table
ORDER BY object_alias
) LOOP
l_counter := l_counter + 1;
dbms_output.put_line(i.object_alias||':'||i.object_name);
IF l_counter = 1 THEN
l_sep1 := ' FROM ';
l_sep2 := ' WHERE ';
ELSE
l_sep1 := ' ,';
l_sep2 := ' AND ';
l_where := l_where||' AND r1.oprid = r'||l_counter||'.oprid AND r1.qryname = r'||l_counter||'.qryname';
END IF;
l_sql := l_sql||l_sep1||'psqryrecord r'||l_counter;
l_where := l_where||l_sep2||'r'||l_counter||'.corrname = '''||i.object_alias||''' AND r'||l_counter||'.recname = '''||i.object_name||'''';
END LOOP;
l_sql := l_sql||l_where||' ORDER BY 1,2';
dbms_output.put_line(l_sql);
EXECUTE IMMEDIATE l_sql BULK COLLECT INTO l_query;
FOR indx IN 1 .. l_query.COUNT
LOOP
DBMS_OUTPUT.put_line (indx||':'||l_query(indx).oprid||'.'||l_query(indx).qryname);
END LOOP;
END;
/
The seven records found in my execution plan become a query of PSQRYRECORD 7 times, one for each record, joined on operator ID and query name.
SELECT r1.oprid, r1.qryname
FROM psqryrecord r1 ,psqryrecord r2 ,psqryrecord r3 ,psqryrecord r4 ,psqryrecord r5 ,psqryrecord r6 ,psqryrecord r7
WHERE r1.corrname = 'A' AND r1.recname = 'OPER_UNIT_TBL'
AND r1.oprid = r2.oprid AND r1.qryname = r2.qryname AND r2.corrname = 'B' AND r2.recname = 'PSTREELEAF'
AND r1.oprid = r3.oprid AND r1.qryname = r3.qryname AND r3.corrname = 'C' AND r3.recname = 'PSTREENODE'
AND r1.oprid = r4.oprid AND r1.qryname = r4.qryname AND r4.corrname = 'D' AND r4.recname = 'PSTREENODE'
AND r1.oprid = r5.oprid AND r1.qryname = r5.qryname AND r5.corrname = 'E' AND r5.recname = 'TREE_NODE_TBL'
AND r1.oprid = r6.oprid AND r1.qryname = r6.qryname AND r6.corrname = 'F' AND r6.recname = 'PSTREELEAF'
AND r1.oprid = r7.oprid AND r1.qryname = r7.qryname AND r7.corrname = 'G' AND r7.recname = 'PRODUCT_TBL'
ORDER BY 1,2
The query finds several queries. I can look at the public PS/Queries in the Query Manager tool. I can also see which users' private queries exist.
NB. You can only open public queries (where OPRID is a single space) or your own private queries. In the Query Manager, you cannot see a private query owned by another user.
…
3: .PS_TREE_PRODUCT
4: .QUERY_PRODUCT_TREE
5: .RM_TREE_PRODUCT
6:XXXXXX.PS_TREE_PRODUCT_XX
…
The new
findqry.sql script is available on Github.
Posts
