set serveroutput on
DECLARE
retVal CLOB;
BEGIN
retVal := dbms_auto_sqltune.report_auto_tuning_task(type => dbms_sqltune.type_text, level => dbms_sqltune.level_all, result_limit =>
10);
dbms_output.put_line(retVal);
END;
/
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : SYS_AUTO_SQL_TUNING_TASK
Tuning Task Owner : SYS
Tuning Task ID : 1
Workload Type : Automatic High-Load SQL Workload
Execution Count : 90
Current Execution : EXEC_3412
Execution Type : TUNE SQL
Scope : COMPREHENSIVE
Global Time Limit(seconds) : 3600
Per-SQL Time Limit(seconds) : 1200
Completion Status : COMPLETED
Started at : 05/06/2024 22:00:04
Completed at : 05/06/2024 22:00:06
Number of Candidate SQLs : 19
Cumulative Elapsed Time of SQL (s) : 13
-------------------------------------------------------------------------------
SUMMARY SECTION
-------------------------------------------------------------------------------
Global SQL Tuning Result Statistics
-------------------------------------------------------------------------------
Number of SQLs Analyzed : 19
Number of SQLs in the Report : 10
-------------------------------------------------------------------------------
DETAILS SECTION
-------------------------------------------------------------------------------
There are no recommendations to improve the statements in the workload.
-------------------------------------------------------------------------------
Statements WITHOUT Results Ordered by Object ID
-------------------------------------------------------------------------------
Object ID : 1055
Schema Name : APEX_230100
Container Name: FREEPDB1
SQL ID : 3y1cmrwb8r53k
SQL Text : SELECT VALUE FROM WWV_FLOW_PLATFORM_PREFS WHERE NAME = :B1
-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- This statement was skipped because it has already been tuned recently.
See
task execution "EXEC_3150" for the most recent tuning results.
-------------------------------------------------------------------------------
Object ID : 1056
Schema Name : SYS
Container Name: CDB$ROOT
SQL ID : 1xd1bdzsmmb0g
SQL Text : SELECT owner, name, referenced_owner, referenced_name, referenced_type
FROM dba_dependencies
WHERE owner LIKE '%XDB%'
-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- This statement was skipped because it has already been tuned recently.
See
task execution "EXEC_3285" for the most recent tuning results.
-------------------------------------------------------------------------------
Object ID : 1057
Schema Name : APEX_230100
Container Name: FREEPDB1
SQL ID : a6zgkya4fvkdg
SQL Text : INSERT INTO WWV_SCHEMA_AGGR_METRICS ( LOG_DAY, SCHEMA,
DATA_FILE_CNT, DATA_FILE_SIZE, FREE_SPACE, USED_SPACE,
JOBS_RUN_CNT, JOBS_ELAPSED_TIME_TOTAL_SEC,
JOBS_FAILED_LOGS_CNT, JOB_ELAPSED_TIME_10PCT_SEC,
JOB_ELAPSED_TIME_AVG_SEC, JOB_ELAPSED_TIME_MEDIAN_SEC,
JOB_ELAPSED_TIME_90PCT_SEC, JOB_ELAPSED_TIME_MAX_SEC,
MIN_INTERVAL_TIME_SEC, DB_LINKS_CNT ) WITH
WORKSPACE_TABLESPACE AS ( SELECT DEFAULT_TABLESPACE, USERNAME
FROM SYS.DBA_USERS DU WHERE EXISTS ( SELECT SCHEMA FROM
WWV_FLOW_COMPANY_SCHEMAS S, WWV_FLOW_COMPANIES C WHERE
C.PROVISIONING_COMPANY_ID = S.SECURITY_GROUP_ID AND S.SCHEMA
= DU.USERNAME ) ), DDF AS (SELECT FILE_ID, TABLESPACE_NAME,
FILE_NAME, DECODE (AUTOEXTENSIBLE, 'YES', GREATEST (BYTES,
MAXBYTES), BYTES) AS DF_SIZE, DECODE (AUTOEXTENSIBLE, 'YES',
CASE WHEN (MAXBYTES > BYTES) THEN (MAXBYTES - BYTES) ELSE 0
END, 0 ) AS GROWTH FROM SYS.DBA_DATA_FILES WHERE
TABLESPACE_NAME IN ( SELECT DEFAULT_TABLESPACE FROM
SYS.DBA_USERS DU WHERE EXISTS ( SELECT SCHEMA FROM
WWV_FLOW_COMPANY_SCHEMAS S, WWV_FLOW_COMPANIES C WHERE
C.PROVISIONING_COMPANY_ID = S.SECURITY_GROUP_ID AND S.SCHEMA
= DU.USERNAME ) ) ), SCHEMA_DATAFILES AS ( SELECT
DFS.USERNAME AS SCHEMA, COUNT(DFS.FILE_ID) AS DATA_FILES_CNT,
ROUND (SUM (DDF.DF_SIZE)) AS DATA_FILE_SIZE, ROUND ((SUM (NVL
(FREEBYTES, 0)) + SUM (GROWTH)) ) AS FREE_SPACE,
ROUND (SUM(DDF.DF_SIZE)) - ROUND ((SUM (NVL (FREEBYTES, 0)) + SUM
(GROWTH))) AS USED_SPACE FROM DDF, (SELECT D.FILE_ID,
WT.USERNAME,
SUM (D.BYTES) AS FREEBYTES FROM
SYS.DBA_FREE_SPACE D, WORKSPACE_TABLESPACE WT WHERE
D.TABLESPACE_NAME = WT.DEFAULT_TABLESPACE GROUP BY FILE_ID,
WT.USERNAME) DFS WHERE DDF.FILE_ID = DFS.FILE_ID(+) GROUP BY
DFS.USERNAME ), SPACE_WITH_INSTANCE_TBS AS ( SELECT
Q.USERNAME AS SCHEMA, Q.MAX_BYTES AS
MAX_BYTES, SUM(Q.MAX_BYTES - Q.BYTES) AS FREE_SPACE, SUM(Q.BYTES) AS
USED_SPACE, NVL(IT.COLUMN_VALUE,:B2 ) AS INSTANCE_TBS_STATUS
FROM SYS.DBA_TS_QUOTAS Q, WORKSPACE_TABLESPACE WT, (SELECT
COLUMN_VALUE FROM WWV_FLOW_STRING.SPLIT(:B1 ,',')) IT WHERE
WT.DEFAULT_TABLESPACE = Q.TABLESPACE_NAME AND WT.USERNAME =
Q.USERNAME AND WT.DEFAULT_TABLESPACE = IT.COLUMN_VALUE (+)
GROUP BY Q.USERNAME, NVL(IT.COLUMN_VALUE,:B2 ), Q.MAX_BYTES
), SCHEMA_STATS AS ( SELECT SD.SCHEMA, SD.DATA_FILES_CNT,
SD.DATA_FILE_SIZE, CASE WHEN :B1 IS NULL THEN SD.FREE_SPACE
ELSE CASE WHEN WIT.INSTANCE_TBS_STATUS != :B2 AND
WIT.MAX_BYTES != -1 THEN WIT.FREE_SPACE ELSE SD.FREE_SPACE
END END AS FREE_SPACE, CASE WHEN :B1 IS NULL THEN
SD.USED_SPACE ELSE CASE WHEN WIT.INSTANCE_TBS_STATUS != :B2
AND WIT.MAX_BYTES != -1 THEN WIT.USED_SPACE ELSE
SD.USED_SPACE END END AS USED_SPACE FROM SCHEMA_DATAFILES SD,
SPACE_WITH_INSTANCE_TBS WIT WHERE WIT.SCHEMA = SD.SCHEMA ),
JOB_DETAILS AS ( SELECT OWNER, JOB_NAME,
SYS_EXTRACT_UTC(ACTUAL_START_DATE) AS LAST_START_DATE,
LAG(SYS_EXTRACT_UTC(ACTUAL_START_DATE)) OVER (PARTITION BY
OWNER ORDER BY SYS_EXTRACT_UTC(ACTUAL_START_DATE)) AS
PREVIOUS_JOB_START_DATE, EXTRACT(SECOND FROM RUN_DURATION) +
EXTRACT(MINUTE FROM RUN_DURATION) * 60 + EXTRACT(HOUR FROM
RUN_DURATION) * 60 * 60 + EXTRACT(DAY FROM RUN_DURATION) * 60
* 60 * 24 AS JOB_RUN_TIME FROM SYS.DBA_SCHEDULER_JOB_RUN_DETAILS WHERE
SYS_EXTRACT_UTC(ACTUAL_START_DATE) BETWEEN :B4 AND :B3 AND
OWNER NOT IN ('SYS', SYS_CONTEXT('USERENV','CURRENT_USER'))
AND OWNER NOT IN (SELECT USERNAME FROM SYS.DBA_USERS WHERE
ORACLE_MAINTAINED = 'Y') ORDER BY
SYS_EXTRACT_UTC(ACTUAL_START_DATE) ), JOBS_COLUMNS AS (
SELECT OWNER, SUM(JD.JOB_RUN_TIME) AS
JOBS_ELAPSED_TIME_TOTAL_SEC, PERCENTILE_DISC(0.1
WITHIN GROUP (ORDER BY JD.JOB_RUN_TIME) AS
JOB_ELAPSED_TIME_10PCT_SEC, AVG(JOB_RUN_TIME) AS
JOB_ELAPSED_TIME_AVG_SEC, MEDIAN(JOB_RUN_TIME) AS
JOB_ELAPSED_TIME_MEDIAN_SEC, PERCENTILE_DISC(0.9) WITHIN
GROUP (ORDER BY JD.JOB_RUN_TIME) AS
JOB_ELAPSED_TIME_90PCT_SEC, MAX(JOB_RUN_TIME) AS
JOB_ELAPSED_TIME_MAX_SEC, MIN(EXTRACT(HOUR FROM
(JD.LAST_START_DATE - JD.PREVIOUS_JOB_START_DATE)) *
60 * 60 + EXTRACT(MINUTE FROM (JD.LAST_START_DATE -
JD.PREVIOUS_JOB_START_DATE)) * 60 + EXTRACT(SECOND FROM
(JD.LAST_START_DATE - JD.PREVIOUS_JOB_START_DATE))) AS
MIN_INTERVAL_TIME_SEC FROM JOB_DETAILS JD GROUP BY OWNER )
SELECT :B4 AS LOG_DAY, SD.SCHEMA, NVL(SD.DATA_FILES_CNT, 0)
AS DATA_FILES_CNT, NVL(SD.DATA_FILE_SIZE, 0) AS
DATA_FILE_SIZE, NVL(SD.FREE_SPACE, 0) AS FREE_SPACE,
NVL(SD.USED_SPACE, 0) AS USED_SPACE, ( SELECT COUNT(*) FROM
SYS.DBA_SCHEDULER_JOBS S WHERE S.OWNER = SD.SCHEMA AND
S.STATE IN ('SUCCEEDED', 'SCHEDULED', 'RUNNING', 'FAILED')
AND S.LAST_START_DATE BETWEEN :B4 AND :B3 AND S.OWNER NOT IN
('SYS', SYS_CONTEXT('USERENV','CURRENT_USER')) AND S.OWNER
NOT IN (SELECT USERNAME FROM SYS.DBA_USERS WHERE
ORACLE_MAINTAINED = 'Y')) AS JOBS_RUN_CNT,
NVL(JC.JOBS_ELAPSED_TIME_TOTAL_SEC, 0) AS
JOBS_ELAPSED_TIME_TOTAL_SEC, (SELECT COUNT(*) FROM
SYS.DBA_SCHEDULER_JOBS S WHERE S.OWNER = SD.SCHEMA AND
S.STATE = 'FAILED' AND S.LAST_START_DATE BETWEEN :B4 AND :B3
AND S.OWNER NOT IN ('SYS', SYS_CONTEXT('USERENV','CURRENT_USER
')) AND S.OWNER NOT IN (SELECT USERNAME FROM SYS.DBA_USERS
WHERE ORACLE_MAINTAINED = 'Y')) AS FAILED_JOBS_CNT,
NVL(JC.JOB_ELAPSED_TIME_10PCT_SEC, 0) AS
JOB_ELAPSED_TIME_10PCT_SEC, NVL(JC.JOB_ELAPSED_TIME_AVG_SEC, 0) AS
JOB_ELAPSED_TIME_AVG_SEC, NVL(JC.JOB_ELAPSED_TIME_MEDIAN_SEC, 0) AS
JOB_ELAPSED_TIME_MEDIAN_SEC, NVL(JC.JOB_ELAPSED_TIME_90PCT_SEC,
0) AS JOB_ELAPSED_TIME_90PCT_SEC, NVL(JC.JOB_ELAPSED_TIME_MAX_SEC, 0) AS
JOB_ELAPSED_TIME_MAX_SEC, NVL(JC.MIN_INTERVAL_TIME_SEC, 0) AS
MIN_INTERVAL_TIME_SEC, (SELECT COUNT(*) FROM SYS.DBA_DB_LINKS
WHERE USERNAME = SD.SCHEMA) AS DB_LINKS_CNT FROM SCHEMA_STATS
SD, JOBS_COLUMNS JC WHERE SD.SCHEMA = JC.OWNER (+) AND
SD.SCHEMA NOT IN ('ORDS_METADATA', 'SYS',
SYS_CONTEXT('USERENV','CURRENT_USER')) AND SD.SCHEMA NOT IN
(SELECT USERNAME FROM SYS.DBA_USERS WHERE ORACLE_MAINTAINED = 'Y')
-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- This statement was skipped because it has already been tuned recently.
See
task execution "EXEC_3369" for the most recent tuning results.
-------------------------------------------------------------------------------
Object ID : 1058
Schema Name : C##EG
Container Name: CDB$ROOT
SQL ID : f0hfu93pgungy
SQL Text : select count(*) from eg010
-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- This statement was skipped because it has already been tuned recently.
See
task execution "EXEC_3341" for the most recent tuning results.
-------------------------------------------------------------------------------
Object ID : 1059
Schema Name : SYS
Container Name: CDB$ROOT
SQL ID : d48tujq3b87d0
SQL Text : SELECT 'EG05' || '^' || dtp.grantee || '^' || dtp.owner ||
'^' || dtp.table_name || '^' ||
dtp.privilege || '^' ||
dtp.grantable || '^' ||
'2.0.1' || '^' || SYSTIMESTAMP
FROM dba_tab_privs dtp
WHERE dtp.grantee IN ('ADMIN', 'PUBLIC', 'SYSTEM')
ORDER BY 1
-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- This statement was skipped because it has already been tuned recently.
See
task execution "EXEC_3245" for the most recent tuning results.
-------------------------------------------------------------------------------
Object ID : 1060
Schema Name : SYS
Container Name: CDB$ROOT
SQL ID : amptccuy0hp4m
SQL Text : SELECT 'EG05' || '^' || dtp.grantee || '^' || dtp.owner ||
'^' || dtp.table_name || '^' ||
dtp.privilege || '^' || dtp.grantable || '^' || '2.0.1' || '^' ||
SYSTIMESTAMP
FROM dba_tab_privs dtp
WHERE dtp.grantee IN ('ADMIN', 'PUBLIC')
ORDER BY 1
-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- This statement was skipped because it has already been tuned recently.
See
task execution "EXEC_3245" for the most recent tuning results.
-------------------------------------------------------------------------------
Object ID : 1061
Schema Name : SYS
Container Name: CDB$ROOT
SQL ID : 895hj0z88bj01
SQL Text : SELECT UNIQUE 'EG07' || '^' || dp.owner || '^' ||
dp.object_name || '^' ||
dp.procedure_name || '^' ||
dp.object_type || '^' ||
'2.0.1' || '^' || SYSTIMESTAMP
FROM dba_procedures dp
WHERE (dp.procedure_name LIKE '%FILE%')
OR (dp.procedure_name LIKE '%WRITE%')
OR (dp.procedure_name LIKE '%CONNECT%')
OR (dp.procedure_name LIKE '%GRANT%')
OR (dp.procedure_name LIKE '%TCP%')
OR (dp.procedure_name LIKE '%OUTPUT%')
ORDER BY 1
-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- This statement was skipped because it has already been tuned recently.
See
task execution "EXEC_3245" for the most recent tuning results.
-------------------------------------------------------------------------------
Object ID : 1062
Schema Name : APEX_230100
Container Name: FREEPDB1
SQL ID : 87nrrbxhh6g2y
SQL Text : INSERT INTO WWV_WORKSPACE_AGGR_METRICS ( LOG_DAY,
WORKSPACE_ID, WORKSPACE_NAME,
ADMIN_USER_ACCOUNTS_CNT,
DEV_USER_ACCOUNTS_CNT, END_USER_ACCOUNTS_CNT,
LOCKED_USER_ACCOUNTS, DISTINCT_USERS_CNT, PAGE_EVENTS_CNT,
DAILY_MAX_ACTIVE_PAGES, LOGIN_CNT, FAILED_LOGINS_CNT,
CREATED_APPS_CNT, REMOVED_APPS_CNT, ACCESSED_APPS_CNT,
ERRORS_CNT, EMAILS_SENT_CNT, DISTINCT_SESSIONS_CNT,
DISTINCT_IP_ADDRESSES_CNT, WEB_SERVICE_REQ_CNT,
WEB_SERVICE_REQ_LIMIT_EXCEEDED, RENDER_TIME_10PCT_SEC,
RENDER_TIME_AVG_SEC, RENDER_TIME_MEDIAN_SEC,
RENDER_TIME_90PCT_SEC, RENDER_TIME_MAX_SEC, WORKSPACE_SIZE )
WITH USER_ACCOUNTS_COUNT AS ( SELECT SUM(CASE WHEN
INSTR(DEVELOPER_ROLE,'ADMIN') > 0 THEN 1 END ) AS
ADMIN_USER_ACCOUNTS_CNT, SUM(CASE WHEN
INSTR(DEVELOPER_ROLE,'ADMIN') = 0 THEN CASE WHEN
INSTR(DEVELOPER_ROLE,'EDIT') > 0 THEN 1 WHEN
INSTR(DEVELOPER_ROLE,'SQL') > 0 THEN 1 END END ) AS
DEV_USER_ACCOUNTS_CNT, SUM(CASE WHEN DEVELOPER_ROLE IS NULL
THEN 1 END ) AS END_USER_ACCOUNTS_CNT,
SUM(DECODE(U.ACCOUNT_LOCKED, 'Y', 1, 0)) AS
LOCKED_USER_ACCOUNTS, U.SECURITY_GROUP_ID FROM
WWV_FLOW_FND_USER U, WWV_FLOW_DEVELOPERS D WHERE U.USER_NAME
= D.USERID (+) AND U.SECURITY_GROUP_ID = D.SECURITY_GROUP_ID
(+) AND U.SECURITY_GROUP_ID NOT IN (0, 10, 11, 12) AND
U.CREATION_DATE < :B1 GROUP BY U.SECURITY_GROUP_ID ),
ACTIVITY_LOG_COUNT AS ( SELECT COUNT(DISTINCT USERID) AS
DISTINCT_USERS_CNT, COUNT(*) AS PAGE_EVENTS_CNT,
COUNT(DISTINCT CASE WHEN STEP_ID IS NOT NULL THEN STEP_ID
END) AS DAILY_MAX_ACTIVE_PAGES, COUNT(DISTINCT FLOW_ID) AS
ACCESSED_APPS_CNT, SUM(CASE WHEN SQLERRM IS NOT NULL THEN 1
ELSE 0 END) AS ERRORS_CNT, COUNT(DISTINCT SESSION_ID) AS
DISTINCT_SESSIONS_CNT, COUNT(DISTINCT IP_ADDRESS) AS
DISTINCT_IP_ADDRESSES_CNT, PERCENTILE_DISC(0.1) WITHIN GROUP
(ORDER BY ELAP) AS RENDER_TIME_10PCT_SEC, AVG(ELAP) AS
RENDER_TIME_AVG_SEC, MEDIAN(ELAP) AS RENDER_TIME_MEDIAN_SEC,
PERCENTILE_DISC(0.9) WITHIN GROUP (ORDER BY ELAP) AS
RENDER_TIME_90PCT_SEC, MAX(ELAP) AS RENDER_TIME_MAX_SEC,
SECURITY_GROUP_ID FROM WWV_FLOW_ACTIVITY_LOG WHERE TIME_STAMP
BETWEEN :B3 AND :B2 AND SECURITY_GROUP_ID NOT IN (0, 10, 11,
12) AND FLOW_ID NOT BETWEEN 4000 AND 4999 GROUP BY
SECURITY_GROUP_ID ), CONNECTIONS_COUNT AS ( SELECT COUNT(*)
AS LOGIN_CNT, SUM(DECODE(NVL(AUTHENTICATION_RESULT,0), 0, 0,
1)) AS FAILED_LOGINS_CNT, SECURITY_GROUP_ID FROM
WWV_FLOW_USER_ACCESS_LOG_V WHERE SECURITY_GROUP_ID NOT IN (0,
10, 11, 12) AND APPLICATION NOT BETWEEN 4000 AND 4999 AND
ACCESS_DATE BETWEEN :B3 AND :B2 GROUP BY SECURITY_GROUP_ID ),
APPS_COUNT AS ( SELECT SUM(CASE WHEN CREATED_ON BETWEEN :B3
AND :B2 THEN 1 ELSE 0 END) AS APPS_CNT_ONLY_YESTERDAY,
SUM(CASE WHEN CREATED_ON <= :B2 THEN 1 ELSE 0 END) AS
APPS_CNT_UNTIL_YESTERDAY, SUM(CASE WHEN CREATED_ON < :B2 - 1
THEN 1 ELSE 0 END) AS APPS_CNT_UNTIL_BEFOREYESTERDAY,
SECURITY_GROUP_ID FROM WWV_FLOWS WHERE SECURITY_GROUP_ID NOT
IN (0, 10, 11, 12) AND ID NOT BETWEEN 4000 AND 4999 GROUP BY
SECURITY_GROUP_ID ), PREVDAY_COUNT_APPS AS ( SELECT
WORKSPACE_ID, CREATED_APPS_CNT_PREV_METRIC FROM (SELECT
LOG_DAY, CREATED_APPS_CNT, WORKSPACE_ID,
NVL(LAG(CREATED_APPS_CNT) OVER (PARTITION BY
WORKSPACE_ID ORDER BY LOG_DAY), 0) AS CREATED_APPS_CNT_PREV_METRIC FROM
WWV_WORKSPACE_AGGR_METRICS WHERE WORKSPACE_ID NOT IN (0, 10,
11, 12)) WHERE LOG_DAY BETWEEN :B3 -1 AND :B2 -1 ),
WEBSERVICE_REQ_COUNT AS ( SELECT COUNT(*) AS
WEBSERVICE_REQ_CNT, CASE WHEN COUNT(*) - NVL(:B4 , 0) > 0
THEN 'Y' ELSE 'N' END AS WEB_SERVICE_REQ_LIMIT_EXCEEDED,
SECURITY_GROUP_ID FROM WWV_FLOW_WEBSERVICE_LOG WHERE
REQUEST_DATE BETWEEN :B3 AND :B2 AND SECURITY_GROUP_ID NOT IN
(0, 10, 11, 12) GROUP BY SECURITY_GROUP_ID ),
WORKSPACE_SIZE_NO_INSTANCE_TBS AS ( SELECT /*+
MATERIALIZE */ SECURITY_GROUP_ID, SUM(MAXBYTES) /1024/1024 AS
WORKSPACE_SIZE
FROM ( SELECT USERNAME, MAXBYTES FROM SYS.DBA_DATA_FILES F,
SYS.DBA_USERS U WHERE F.TABLESPACE_NAME =
U.DEFAULT_TABLESPACE) A, WWV_FLOW_COMPANY_SCHEMAS CS WHERE
CS.SCHEMA = A.USERNAME GROUP BY SECURITY_GROUP_ID ),
WORKSPACE_SIZE_INSTANCE_TBS AS ( SELECT SECURITY_GROUP_ID,
SUM(A.MAX_BYTES) /1024/1024 AS WORKSPACE_SIZE FROM ( SELECT
Q.USERNAME, Q.MAX_BYTES FROM SYS.DBA_TS_QUOTAS Q,
SYS.DBA_USERS U WHERE Q.USERNAME = U.USERNAME AND
REGEXP_INSTR(Q.TABLESPACE_NAME, '^APEX_\d+') = 0) A,
WWV_FLOW_COMPANY_SCHEMAS CS WHERE CS.SCHEMA = A.USERNAME
GROUP BY SECURITY_GROUP_ID ), EMAILS AS ( SELECT COUNT(*) AS
EMAILS_SENT, M.SECURITY_GROUP_ID FROM WWV_FLOW_MAIL_LOG M
WHERE M.LAST_UPDATED_ON BETWEEN :B3 AND :B2 GROUP BY
M.SECURITY_GROUP_ID ) SELECT :B3 AS LOG_DAY,
C.PROVISIONING_COMPANY_ID AS WORKSPACE_ID, C.SHORT_NAME AS
WORKSPACE_NAME, NVL(U.ADMIN_USER_ACCOUNTS_CNT, 0) AS
ADMIN_USER_ACCOUNTS_CNT, NVL(U.DEV_USER_ACCOUNTS_CNT, 0) AS
DEV_USER_ACCOUNTS_CNT, NVL(U.END_USER_ACCOUNTS_CNT, 0) AS
END_USER_ACCOUNTS_CNT, NVL(U.LOCKED_USER_ACCOUNTS, 0) AS
LOCKED_USER_ACCOUNTS, NVL(A.DISTINCT_USERS_CNT, 0) AS
DISTINCT_USERS_CNT, NVL(A.PAGE_EVENTS_CNT, 0) AS
PAGE_EVENTS_CNT, NVL(A.DAILY_MAX_ACTIVE_PAGES, 0) AS
DAILY_MAX_ACTIVE_PAGES, NVL(CC.LOGIN_CNT, 0) AS LOGIN_CNT,
NVL(CC.FAILED_LOGINS_CNT, 0) AS FAILED_LOGINS_CNT,
NVL(AC.APPS_CNT_ONLY_YESTERDAY, 0) AS CREATED_APPS_CNT,
NVL(CASE WHEN AC.APPS_CNT_UNTIL_BEFOREYESTERDAY +
PA.CREATED_APPS_CNT_PREV_METRIC + AC.APPS_CNT_ONLY_YESTERDAY
- AC.APPS_CNT_UNTIL_YESTERDAY > 0 THEN
AC.APPS_CNT_UNTIL_BEFOREYESTERDAY +
PA.CREATED_APPS_CNT_PREV_METRIC + AC.APPS_CNT_ONLY_YESTERDAY
- AC.APPS_CNT_UNTIL_YESTERDAY ELSE 0 END, 0) AS
REMOVED_APPS_CNT, NVL(A.ACCESSED_APPS_CNT, 0) AS
ACCESSED_APPS_CNT, NVL(A.ERRORS_CNT, 0) AS ERRORS_CNT,
NVL(E.EMAILS_SENT, 0) AS EMAILS_SENT_CNT,
NVL(A.DISTINCT_SESSIONS_CNT, 0) AS
DISTINCT_SESSIONS_CNT, NVL(A.DISTINCT_IP_ADDRESSES_CNT, 0) AS
DISTINCT_IP_ADDRESSES_CNT, NVL(W.WEBSERVICE_REQ_CNT, 0) AS
WEBSERVICE_REQ_CNT, NVL(W.WEB_SERVICE_REQ_LIMIT_EXCEEDED,
'N') AS WEB_SERVICE_REQ_LIMIT_EXCEEDED,
NVL(A.RENDER_TIME_10PCT_SEC, 0) AS RENDER_TIME_10PCT_SEC,
NVL(A.RENDER_TIME_AVG_SEC, 0) AS RENDER_TIME_AVG_SEC,
NVL(A.RENDER_TIME_MEDIAN_SEC, 0) AS RENDER_TIME_MEDIAN_SEC,
NVL(A.RENDER_TIME_90PCT_SEC, 0) AS RENDER_TIME_90PCT_SEC,
NVL(A.RENDER_TIME_MAX_SEC, 0) AS RENDER_TIME_MAX_SEC,
NVL(CASE WHEN :B5 IS NULL THEN (SELECT NIT.WORKSPACE_SIZE
FROM WORKSPACE_SIZE_NO_INSTANCE_TBS NIT WHERE
NIT.SECURITY_GROUP_ID = C.PROVISIONING_COMPANY_ID ) ELSE
(SELECT WIT.WORKSPACE_SIZE FROM WORKSPACE_SIZE_INSTANCE_TBS
WIT WHERE WIT.SECURITY_GROUP_ID = C.PROVISIONING_COMPANY_ID )
END, 0) AS WORKSPACE_SIZE FROM WWV_FLOW_COMPANIES C,
USER_ACCOUNTS_COUNT U, ACTIVITY_LOG_COUNT A,
CONNECTIONS_COUNT CC, APPS_COUNT AC, PREVDAY_COUNT_APPS PA,
WEBSERVICE_REQ_COUNT W, EMAILS E WHERE
C.PROVISIONING_COMPANY_ID = U.SECURITY_GROUP_ID (+) AND
C.PROVISIONING_COMPANY_ID = A.SECURITY_GROUP_ID (+) AND
C.PROVISIONING_COMPANY_ID = CC.SECURITY_GROUP_ID (+) AND
C.PROVISIONING_COMPANY_ID = AC.SECURITY_GROUP_ID (+) AND
C.PROVISIONING_COMPANY_ID = PA.WORKSPACE_ID (+) AND
C.PROVISIONING_COMPANY_ID = W.SECURITY_GROUP_ID (+) AND
C.PROVISIONING_COMPANY_ID = E.SECURITY_GROUP_ID (+)
-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- This statement was skipped because it has already been tuned recently. See
task execution "EXEC_3369" for the most recent tuning results.
-------------------------------------------------------------------------------
Object ID : 1063
Schema Name : APEX_230100
Container Name: FREEPDB1
SQL ID : gq3qr8hnzrxwp
SQL Text : SELECT T.OWNER, T.TABLE_NAME FROM SYS.DBA_TABLES T WHERE
T.OWNER = :B1 AND
T.EXTERNAL != 'YES'
-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- This statement was skipped because it has already been tuned recently. See
task execution "EXEC_2629" for the most recent tuning results.
-------------------------------------------------------------------------------
Object ID : 1064
Schema Name : APEX_230100
Container Name: FREEPDB1
SQL ID : 7s194f4j58998
SQL Text : DELETE FROM WWV_DICTIONARY_CACHE_OBJ WHERE OBJECT_OWNER = :B2
AND OBJECT_TYPE = 'TABLE' AND SECURITY_GROUP_ID = :B1 AND
OBJECT_TYPE||'.'||OBJECT_OWNER||'.'||OBJECT_NAME NOT IN (
SELECT O.OBJECT_TYPE||'.'||O.OWNER||'.'||O.OBJECT_NAME FROM
SYS.DBA_OBJECTS O WHERE O.OWNER = :B2 AND O.OBJECT_TYPE = 'TABLE')
-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- This statement was skipped because it has already been tuned recently. See
task execution "EXEC_2629" for the most recent tuning results.
-------------------------------------------------------------------------------
PL/SQL procedure successfully completed. |