Oracle常用抓取SQL-標量子查詢等

抓有標量子查詢的SQL
檢查某個賬戶(這裡以SCOTT示例)下SQL是否使用標量子查詢
WITH fsql as
 (select /*+ materialize */
   sql_id, to_clob(upper(sql_fulltext)) as ftext
    from v$sql
   where parsing_schema_name = 'SCOTT'),
sqlid as
 (select /*+ materialize */
   parsing_schema_name, sql_id, sql_text
    from v$sql
   where parsing_schema_name = 'SCOTT'
   group by parsing_schema_name, sql_id, sql_text),
sql as
 (select parsing_schema_name,
         sql_id,
         sql_text,
         (select ftext
            from fsql
           where sql_id = a.sql_id
             and rownum <= 1) ftext
    from sqlid a)
select a.parsing_schema_name  "使用者", a.sql_id, a.sql_text,'SQL 使用了標量子查詢' notice,a.ftext
  from sql a, (select sql_id
  from (select sql_id, count(depth) depth_cnt
          from v$sql_plan
         where depth = 1
           AND object_owner = 'SCOTT'
         group by sql_id, child_number)
 where depth_cnt > 1) b
 where a.sql_id=b.sql_id;
測試sql：
select a.owner,a.object_name,(select object_type from test2 where object_id=a.object_id) object_type from test a 
抓有自定義函式的SQL
Execution 大於1   
WITH FSQL AS
 (SELECT /*+ materialize */
   SQL_ID, TO_CLOB(UPPER(SQL_FULLTEXT)) AS FTEXT
    FROM V$SQL
   WHERE PARSING_SCHEMA_NAME = 'SCOTT'),
SQLID AS
 (SELECT /*+ materialize */
   PARSING_SCHEMA_NAME, SQL_ID, SQL_TEXT
    FROM V$SQL
   WHERE PARSING_SCHEMA_NAME = 'SCOTT'
   GROUP BY PARSING_SCHEMA_NAME, SQL_ID, SQL_TEXT),
SQL AS
 (SELECT PARSING_SCHEMA_NAME,
         SQL_ID,
         SQL_TEXT,
         (SELECT FTEXT
            FROM FSQL
           WHERE SQL_ID = A.SQL_ID
             AND ROWNUM <= 1) FTEXT
    FROM SQLID A),
FUNC AS
 (SELECT
  /*+ materialize */
   OBJECT_NAME,
   CASE
     WHEN OBJECT_TYPE = 'FUNCTION' THEN '函式'
     WHEN OBJECT_TYPE = 'PACKAGE' THEN '包'
     WHEN OBJECT_TYPE = 'PROCEDURE' THEN '儲存過程'
   END OBJECT_TYPE
    FROM (SELECT OBJECT_NAME, OBJECT_TYPE
            FROM DBA_OBJECTS
           WHERE OBJECT_TYPE IN ('FUNCTION', 'PROCEDURE')
             AND CREATED >
                 TO_DATE('2013/12/15 21:05:01', 'YYYY/MM/DD HH24:MI:SS')
          UNION ALL
          SELECT OBJECT_NAME || '.' 　 || PROCEDURE_NAME AS OBJECT_NAME,
                 'PACKAGE' OBJECT_TYPE
            FROM DBA_PROCEDURES
           WHERE OBJECT_NAME IN
                 (SELECT OBJECT_NAME
                    FROM DBA_OBJECTS
                   WHERE OBJECT_TYPE IN ('FUNCTION', 'PACKAGE', 'PROCEDURE')
                     AND CREATED > TO_DATE('2013/12/15 21:05:01',
                                           'YYYY/MM/DD HH24:MI:SS'))
             AND PROCEDURE_NAME IS NOT NULL))
SELECT A.PARSING_SCHEMA_NAME "使用者",
       A.SQL_ID,
       A.SQL_TEXT,
       'SQL 呼叫自定義 ' || B.OBJECT_TYPE || ' ' || B.OBJECT_NAME NOTICE,
       A.FTEXT
  FROM SQL A, FUNC B
 WHERE INSTR(A.FTEXT, B.OBJECT_NAME) > 0;
抓有filter的SQL
WITH FSQL AS
 (SELECT /*+ materialize */
   SQL_ID, SQL_TEXT, TO_CLOB(UPPER(SQL_FULLTEXT)) AS FTEXT
    FROM V$SQL
   WHERE PARSING_SCHEMA_NAME = 'SCOTT')
SELECT SQL_ID,
       OBJECT_OWNER "使用者",
       (SELECT SQL_TEXT
          FROM FSQL
         WHERE SQL_ID = A.SQL_ID
           AND ROWNUM = 1) SQL_TEXT,
       OBJECT_NAME "表名",
       FILTER_PREDICATES "謂詞",
       LENGTH(PROJECTION) - LENGTH(REPLACE(PROJECTION, '], ', '] ')) + 1 "列訪問數",
       (SELECT FTEXT
          FROM FSQL
         WHERE SQL_ID = A.SQL_ID
           AND ROWNUM = 1) FTEXT
  FROM (SELECT DISTINCT SQL_ID,
                        OBJECT_OWNER,
                        OBJECT_NAME,
                        FILTER_PREDICATES,
                        PROJECTION
          FROM V$SQL_PLAN
         WHERE OBJECT_OWNER = 'SCOTT'
           AND OPERATION = 'TABLE ACCESS'
           AND OPTIONS = 'BY INDEX ROWID'
           AND FILTER_PREDICATES IS NOT NULL) A;
 
檢查有rownum的view(檢視中有order by)
檢視中也不能有order by，檢視中也不能有檢視。檢視不能當巢狀迴圈的被驅動表。
CREATE OR REPLACE FUNCTION LONG_2_VARCHAR(P_OWNER     IN DBA_VIEWS.OWNER%TYPE,
                                          P_VIEW_NAME IN DBA_VIEWS.VIEW_NAME%TYPE)
  RETURN VARCHAR2 AS
  L_TEXT LONG;
BEGIN
  SELECT TEXT INTO L_TEXT FROM DBA_VIEWS WHERE OWNER = P_OWNER AND VIEW_NAME = P_VIEW_NAME;
  RETURN SUBSTR(L_TEXT, 1, 4000);
END;
SELECT *
  FROM (SELECT OWNER,
               VIEW_NAME,
               LONG_2_VARCHAR(V.OWNER, V.VIEW_NAME) AS "VIEW_TEXT"
          FROM DBA_VIEWS V
         WHERE V.OWNER = UPPER('&OWNER')) N
 WHERE N.VIEW_TEXT LIKE '%ROWNUM%'
    OR N.VIEW_TEXT LIKE '%ORDER BY%'
UNION
SELECT '', '', ''
  FROM EMP;  
/*DBA_HIST_SQL_PLAN*/
SELECT X.OPTIMIZER,
       ID,
       PARENT_ID,
       RPAD(' ', X.PARENT_ID + 1, ' ') || X.OPERATION || ' ' || X.OPTIONS AS 執行計劃,
       X.OBJECT_NAME || ' ' || X.OBJECT_TYPE AS 物件名稱及型別,
       FILTER_PREDICATES AS "filter",
       X.ACCESS_PREDICATES AS "access",
       LENGTH(PROJECTION) - LENGTH(REPLACE(PROJECTION, '], ', '] ')) + 1 AS "SELECT列的個數",
       X.PROJECTION AS "被訪問到的列"
  FROM V$SQL_PLAN X
 WHERE SQL_ID = 'a23r1uchdaafg'  
索引被哪些SQL使用
SELECT A.SQL_TEXT, A.SQL_ID, B.OBJECT_OWNER, B.OBJECT_NAME, B.OBJECT_TYPE
  FROM GV$SQL A, GV$SQL_PLAN B
 WHERE A.SQL_ID = B.SQL_ID
   AND A.CHILD_NUMBER = B.CHILD_NUMBER
   AND B.OBJECT_OWNER IN
       (SELECT USERNAME FROM DBA_USERS WHERE CREATED > DATE '2018-10-30')
   AND B.OBJECT_TYPE LIKE '%INDEX%'
   AND B.TIMESTAMP > DATE '2018-12-10'
 ORDER BY 3, 4, 5;
巢狀迴圈返回行數較多的sql
SELECT *
  FROM (SELECT PARSING_SCHEMA_NAME SCHEMA,
               SQL_ID,
               SQL_TEXT,
               ROWS_PROCESSED / EXECUTIONS ROW_PROCESSED
          FROM V$SQL
         WHERE PARSING_SCHEMA_NAME IN
               (SELECT USERNAME
                  FROM DBA_USERS
                 WHERE CREATED > DATE '2018-10-30')
           AND EXECUTIONS > 0
           AND ROWS_PROCESSED / EXECUTIONS > 10000
         ORDER BY 4 DESC) A
 WHERE A.SQL_ID IN (SELECT SQL_ID
                      FROM V$SQL_PLAN
                     WHERE OPERATION LIKE '%NESTED LOOPS%'
                       AND ID <= 5);
SELECT /*column_name*/
 *
  FROM (SELECT A.OWNER,
               A.COLUMN_NAME,
               B.NUM_ROWS,
               A.NUM_DISTINCT CARDINALITY,
               ROUND(A.NUM_DISTINCT / B.NUM_ROWS * 100, 2) SELECTIVITY
          FROM DBA_TAB_COL_STATISTICS A, DBA_TABLES B
         WHERE A.OWNER = B.OWNER
           AND A.TABLE_NAME = B.TABLE_NAME
           AND A.OWNER = ('SCOTT')
           AND A.TABLE_NAME = ('TEST'))
 WHERE SELECTIVITY > 20
   AND (OWNER, COLUMN_NAME) IN
       (SELECT R.NAME OWNER, C.NAME COLUMN_NAME
          FROM SYS.COL_USAGE$ U, SYS.OBJ$ O, SYS.COL$ C, SYS.USER$ R
         WHERE O.OBJ# = U.OBJ#
           AND C.OBJ# = U.OBJ#
           AND C.COL# = U.INTCOL#
           AND R.NAME = 'SCOTT'
           AND O.NAME = 'TEST')
 
抓出可以建立組合索引的SQL 好像不太好用
SELECT A.SQL_ID,
       A.SQL_TEXT,
       F.TABLE_NAME,
       C.SIZE_MB,
       E.COLUMN_NAME,
       ROUND(E.NUM_DISTINCT / F.NUM_ROWS * 100, 2) SELECTIVITY
  FROM V$SQL A,
       V$SQL_PLAN B,
       (SELECT OWNER, SEGMENT_NAME, SUM(BYTES / 1024 / 1024) SIZE_MB
          FROM DBA_SEGMENTS
         GROUP BY OWNER, SEGMENT_NAME) C,
       DBA_TAB_COL_STATISTICS E,
       DBA_TABLES F
 WHERE A.SQL_ID = B.SQL_ID
   AND A.CHILD_NUMBER = B.CHILD_NUMBER
   AND B.OBJECT_OWNER = C.OWNER
   AND B.OBJECT_NAME = C.SEGMENT_NAME
   AND E.OWNER = F.OWNER
   AND B.OBJECT_OWNER = F.OWNER
   AND B.OBJECT_NAME = F.TABLE_NAME
   AND INSTR(B.FILTER_PREDICATES, E.COLUMN_NAME) > 0
   AND (E.NUM_DISTINCT / F.NUM_ROWS) > 0.1
   AND C.OWNER IN ('POINT_COIN_CORE_BASE',
                   'OPEN_CLIENT_BASE',
                   'BOPS_KKTRIBE_BASE',
                   'KKTRIBE_CORE_BASE',
                   'OPEN_NOTIFY_SEND_BASE',
                   'MEDAL_CORE_BASE',
                   'UAC_CORE_BASE',
                   'OPEN_BASE',
                   'LT_PURCHASE_BASE',
                   'KF_WEB_BASE',
                   'CONFIG',
                   'LT_PROXY_BASE',
                   'OPEN_NOTIFY_RECV_BASE',
                   'CIF_EXT_CORE_BASE',
                   'LT_EXT_DATA_BASE',
                   'KEY_CORE_BASE',
                   'KKTRIBE_WORKER_WEB_BASE',
                   'SS_SYN_OUT_BASE',
                   'WEIBO_CORE_BASE',
                   'KKTRIBE_MAPI_BASE',
                   'KKTRIBE_CRM_WEB',
                   'GOTONE_CORE_BASE',
                   'OPEN_DOCK_API_COMMON_BASE')
   AND B.OPERATION = 'TABLE ACCESS'
   AND B.OPERATION = 'BY INDEX ROWID'
   AND B.TIMESTAMP > DATE
 '2018-12-15'
   AND E.OWNER IN ('POINT_COIN_CORE_BASE',
                   'OPEN_CLIENT_BASE',
                   'BOPS_KKTRIBE_BASE',
                   'KKTRIBE_CORE_BASE',
                   'OPEN_NOTIFY_SEND_BASE',
                   'MEDAL_CORE_BASE',
                   'UAC_CORE_BASE',
                   'OPEN_BASE',
                   'LT_PURCHASE_BASE',
                   'KF_WEB_BASE',
                   'CONFIG',
                   'LT_PROXY_BASE',
                   'OPEN_NOTIFY_RECV_BASE',
                   'CIF_EXT_CORE_BASE',
                   'LT_EXT_DATA_BASE',
                   'KEY_CORE_BASE',
                   'KKTRIBE_WORKER_WEB_BASE',
                   'SS_SYN_OUT_BASE',
                   'WEIBO_CORE_BASE',
                   'KKTRIBE_MAPI_BASE',
                   'KKTRIBE_CRM_WEB',
                   'GOTONE_CORE_BASE',
                   'OPEN_DOCK_API_COMMON_BASE')
   AND F.NUM_ROWS <> 0
 ORDER BY 4 DESC;