嗨，我正在运行一个脚本，我已经用了过去几个月，突然之间，它开始不返回所有列出的用户，基本上有超过4500个用户在系统中，现在它只返回1000至1500。奇怪的是，脚本没有改变，用户都还在。另外，当我刷新脚本时，返回的行数有时上升，有时下降。另一件奇怪的事是它用来一次返回所有的行，现在一次只返回50行？
任何帮助都会得到报答。
这是剧本
SELECT *
FROM
(SELECT INITTEAM(ounit.name) "Team",
tuser.name "User Name",
tuser.created_date "User Created Date" ,
extract(YEAR FROM
(SELECT max(logon_date)
FROM fife_logon_history
WHERE user_name = tuser.name)) "Year Last Logged In" ,
extract(MONTH FROM
(SELECT max(logon_date)
FROM fife_logon_history
WHERE user_name = tuser.name)) "Month Last Logged In" ,
initname(person.first_names) || ' ' || initname(person.family_name) "Worker Name" ,
bemplo.employment_end_date "Employ. End Date" ,
dba_users.account_status "Account Status" ,
(SELECT listagg(tugrou.description,/*chr(10)*/',')
within GROUP (ORDER BY tugmem.tuser_identifier,
tugrou.description)
FROM tugmem
JOIN tugrou ON tugrou.identifier = tugmem.tugrou_identifier
WHERE tugmem.tuser_identifier = tuser.identifier
GROUP BY tugmem.tuser_identifier) "Access Permissions" ,
CASE WHEN (SELECT listagg(tugrou.description, chr(10))
within GROUP (ORDER BY tugmem.tuser_identifier,
tugrou.description)
FROM tugmem
JOIN tugrou ON tugrou.identifier = tugmem.tugrou_identifier
WHERE tugmem.tuser_identifier = tuser.identifier
GROUP BY tugmem.tuser_identifier) LIKE '%AIS %' THEN 'AIS User '
WHEN (SELECT listagg(tugrou.description, chr(10))
within GROUP (ORDER BY tugmem.tuser_identifier,
tugrou.description)
FROM tugmem
JOIN tugrou ON tugrou.identifier = tugmem.tugrou_identifier
WHERE tugmem.tuser_identifier = tuser.identifier
GROUP BY tugmem.tuser_identifier) NOT LIKE '%AIS %' THEN 'SWIFT User '
|| to_char((SELECT max(logon_date)
FROM fife_logon_history
WHERE user_name = tuser.name),'DD-MM-YYYY HH24:MI')
ELSE decode((SELECT max(logon_date)
FROM fife_logon_history
WHERE user_name = tuser.name),NULL, NULL, 'Last Login '
|| to_char((SELECT max(logon_date)
FROM fife_logon_history
WHERE user_name = tuser.name),'DD-MM-YYYY HH24:MI'))
END "Application " ,
nvl((SELECT to_char(max(logon_date), 'DD-MON-RRRR hh24:mi')
FROM fife_logon_history
WHERE user_name = tuser.name),'Never') "Last Logged In"
FROM tuser
JOIN person ON person.identifier = tuser.person_identifier
JOIN bemplo ON bemplo.person_identifier = tuser.person_identifier
JOIN ounit ON ounit.identifier = bemplo.ounit_identifier
JOIN dba_users ON dba_users.username = tuser.name
WHERE (((bemplo.employment_end_date IS NULL)
AND (primary_employment = 'Y'))
OR (BEMPLO.EMPLOYMENT_END_DATE =
(SELECT MAX(EMPLOYMENT_END_DATE)
FROM BEMPLO B
WHERE B.PERSON_IDENTIFIER = TUSER.PERSON_IDENTIFIER))) )
WHERE ((("Last Logged In" != 'Never')
AND ("Last Logged In" < to_char((sysdate -182), 'DD-MON-RRRR hh24:mi')))
OR ("Last Logged In" = 'Never'))
AND "Account Status" != 'LOCKED'
ORDER BY 1,
4 ASC,
5 ASC这是计划报告
计划表输出
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6264 | 36M| 11944 |
| 1 | SORT AGGREGATE | | 1 | 15 | |
| 2 | TABLE ACCESS BY INDEX ROWID | FIFE_LOGON_HISTORY | 5 | 75 | 4 |
| 3 | INDEX RANGE SCAN | FLHIST_USER | 5 | | 1 |
| 4 | SORT AGGREGATE | | 1 | 15 | |
| 5 | TABLE ACCESS BY INDEX ROWID | FIFE_LOGON_HISTORY | 5 | 75 | 4 |
| 6 | INDEX RANGE SCAN | FLHIST_USER | 5 | | 1 |
| 7 | SORT GROUP BY | | 1 | 40 | 3 |
| 8 | NESTED LOOPS | | 2 | 80 | 3 |
| 9 | VIEW | index$_join$_005 | 114 | 3534 | 3 |
| 10 | HASH JOIN | | | | |
| 11 | INDEX FAST FULL SCAN | TUGROU_PK | 114 | 3534 | 1 |
| 12 | INDEX FAST FULL SCAN | TUGROU_TUGROU2_UK | 114 | 3534 | 1 |
| 13 | INDEX UNIQUE SCAN | TUGMEM_TUGMEM2_UK | 1 | 9 | 0 |
| 14 | SORT GROUP BY | | 1 | 40 | 3 |
| 15 | NESTED LOOPS | | 2 | 80 | 3 |
| 16 | VIEW | index$_join$_008 | 114 | 3534 | 3 |
| 17 | HASH JOIN | | | | |
| 18 | INDEX FAST FULL SCAN | TUGROU_PK | 114 | 3534 | 1 |
| 19 | INDEX FAST FULL SCAN | TUGROU_TUGROU2_UK | 114 | 3534 | 1 |
| 20 | INDEX UNIQUE SCAN | TUGMEM_TUGMEM2_UK | 1 | 9 | 0 |
| 21 | SORT GROUP BY | | 1 | 40 | 3 |
| 22 | NESTED LOOPS | | 2 | 80 | 3 |
| 23 | VIEW | index$_join$_011 | 114 | 3534 | 3 |
| 24 | HASH JOIN | | | | |
| 25 | INDEX FAST FULL SCAN | TUGROU_PK | 114 | 3534 | 1 |
| 26 | INDEX FAST FULL SCAN | TUGROU_TUGROU2_UK | 114 | 3534 | 1 |
| 27 | INDEX UNIQUE SCAN | TUGMEM_TUGMEM2_UK | 1 | 9 | 0 |
| 28 | SORT AGGREGATE | | 1 | 15 | |
| 29 | TABLE ACCESS BY INDEX ROWID | FIFE_LOGON_HISTORY | 5 | 75 | 4 |
| 30 | INDEX RANGE SCAN | FLHIST_USER | 5 | | 1 |
| 31 | SORT AGGREGATE | | 1 | 15 | |
| 32 | TABLE ACCESS BY INDEX ROWID | FIFE_LOGON_HISTORY | 5 | 75 | 4 |
| 33 | INDEX RANGE SCAN | FLHIST_USER | 5 | | 1 |
| 34 | SORT AGGREGATE | | 1 | 15 | |
| 35 | TABLE ACCESS BY INDEX ROWID | FIFE_LOGON_HISTORY | 5 | 75 | 4 |
| 36 | INDEX RANGE SCAN | FLHIST_USER | 5 | | 1 |
| 37 | SORT AGGREGATE | | 1 | 15 | |
| 38 | TABLE ACCESS BY INDEX ROWID | FIFE_LOGON_HISTORY | 5 | 75 | 4 |
| 39 | INDEX RANGE SCAN | FLHIST_USER | 5 | | 1 |
| 40 | SORT ORDER BY | | 6264 | 36M| 11944 |
| 41 | VIEW | | 6264 | 36M| 3973 |
| 42 | CONCATENATION | | | | |
| 43 | FILTER | | | | |
| 44 | HASH JOIN | | 5389 | 999K| 2914 |
| 45 | TABLE ACCESS FULL | ORGANISATIONAL_UNITS | 4046 | 106K| 21 |
| 46 | HASH JOIN | | 5389 | 857K| 2892 |
| 47 | TABLE ACCESS FULL | BASE_EMPLOYEES | 9996 | 175K| 33 |
| 48 | HASH JOIN | | 2710 | 383K| 2858 |
| 49 | TABLE ACCESS FULL | TS$ | 17 | 51 | 7 |
| 50 | NESTED LOOPS | | | | |
| 51 | NESTED LOOPS | | 2710 | 375K| 2851 |
| 52 | HASH JOIN | | 2745 | 324K| 104 |
| 53 | TABLE ACCESS FULL | TS$ | 17 | 51 | 7 |
| 54 | HASH JOIN | | 2745 | 316K| 96 |
| 55 | TABLE ACCESS FULL | TAB_USERS | 4778 | 121K| 14 |
| 56 | HASH JOIN | | 2868 | 257K| 81 |
| 57 | TABLE ACCESS FULL | USER_ASTATUS_MAP | 8 | 152 | 2 |
| 58 | HASH JOIN | | 3227 | 230K| 79 |
| 59 | TABLE ACCESS FULL | PROFNAME$ | 3 | 9 | 2 |
| 60 | HASH JOIN RIGHT OUTER | | 3227 | 220K| 76 |
| 61 | TABLE ACCESS FULL | RESOURCE_GROUP_MAPPING$ | 1 | 29 | 2 |
| 62 | HASH JOIN | | 3227 | 129K| 74 |
| 63 | MERGE JOIN CARTESIAN| | 2 | 36 | 4 |
| 64 | TABLE ACCESS FULL | PROFILE$ | 1 | 9 | 2 |
| 65 | BUFFER SORT | | 2 | 18 | 2 |
| 66 | TABLE ACCESS FULL | PROFILE$ | 2 | 18 | 2 |
| 67 | TABLE ACCESS FULL | USER$ | 4993 | 112K| 69 |
| 68 | INDEX UNIQUE SCAN | PERSON_PK | 1 | | 0 |
| 69 | TABLE ACCESS BY INDEX ROWID| PERSONS | 1 | 21 | 1 |
| 70 | SORT AGGREGATE | | 1 | 11 | |
| 71 | TABLE ACCESS BY INDEX ROWID | BASE_EMPLOYEES | 2 | 22 | 3 |
| 72 | INDEX RANGE SCAN | BEMPLO_PERSON_FK_I | 2 | | 1 |
| 73 | FILTER | | | | |
| 74 | HASH JOIN | | 875 | 162K| 1053 |
| 75 | TABLE ACCESS FULL | TS$ | 17 | 51 | 7 |
| 76 | HASH JOIN RIGHT OUTER | | 875 | 159K| 1046 |
| 77 | TABLE ACCESS FULL | RESOURCE_GROUP_MAPPING$ | 1 | 29 | 2 |
| 78 | HASH JOIN | | 875 | 135K| 1043 |
| 79 | NESTED LOOPS | | | | |
| 80 | NESTED LOOPS | | 875 | 111K| 1022 |
| 81 | HASH JOIN | | 886 | 97460 | 135 |
| 82 | TABLE ACCESS FULL | BASE_EMPLOYEES | 911 | 16398 | 33 |
| 83 | HASH JOIN | | 2745 | 246K| 101 |
| 84 | TABLE ACCESS FULL | TAB_USERS | 4778 | 121K| 14 |
| 85 | HASH JOIN | | 2868 | 184K| 86 |
| 86 | TABLE ACCESS FULL | TS$ | 17 | 51 | 7 |
| 87 | HASH JOIN | | 2868 | 176K| 79 |
| 88 | TABLE ACCESS FULL | USER_ASTATUS_MAP | 8 | 152 | 2 |
| 89 | HASH JOIN | | 3227 | 138K| 76 |
| 90 | TABLE ACCESS FULL | PROFNAME$ | 3 | 9 | 2 |
| 91 | HASH JOIN | | 3227 | 129K| 74 |
| 92 | MERGE JOIN CARTESIAN| | 2 | 36 | 4 |
| 93 | TABLE ACCESS FULL | PROFILE$ | 1 | 9 | 2 |
| 94 | BUFFER SORT | | 2 | 18 | 2 |
| 95 | TABLE ACCESS FULL | PROFILE$ | 2 | 18 | 2 |
| 96 | TABLE ACCESS FULL | USER$ | 4993 | 112K| 69 |
| 97 | INDEX UNIQUE SCAN | PERSON_PK | 1 | | 0 |
| 98 | TABLE ACCESS BY INDEX ROWID| PERSONS | 1 | 21 | 1 |
| 99 | TABLE ACCESS FULL | ORGANISATIONAL_UNITS | 4046 | 106K| 21 |
| 100 | SORT AGGREGATE | | 1 | 11 | |
| 101 | TABLE ACCESS BY INDEX ROWID | BASE_EMPLOYEES | 2 | 22 | 3 |
| 102 | INDEX RANGE SCAN | BEMPLO_PERSON_FK_I | 2 | | 1 |
-----------------------------------------------------------------------------------------------注意
“计划表”是旧版本
我是新的sql（重新学习），所以任何帮助将不胜感激
暂无答案！
目前还没有任何答案，快来回答吧！