set pages 1000
set lines 10000
set echo on
column schema format a20
column sql_text format a80
WITH force_matches AS
(SELECT force_matching_signature,
COUNT( * ) matches,
MAX(sql_id || child_number) max_sql_child,
DENSE_RANK() OVER (ORDER BY COUNT( * ) DESC)
ranking
FROM v$sql
WHERE force_matching_signature <> 0
AND parsing_schema_name <> 'SYS'
GROUP BY force_matching_signature
HAVING COUNT( * ) > 5)
SELECT sql_id, matches, parsing_schema_name schema, sql_text
FROM v$sql JOIN force_matches
ON (sql_id || child_number = max_sql_child)
WHERE ranking <= 21
ORDER BY matches DESC;
SQL_ID MATCHES SCHEMA SQL_TEXT
------------- ---------- -------------------- --------------------------------------------------------------
gzxsf4mv720t0 2392 SAPSR3 SELECT /*+ FIRST_ROWS (12) */ * FROM "M_MAT1M" WHERE ("MANDT"='400' AND "MATNR"
LIKE '2009%' AND "SPRAS"='S' ) AND ROWNUM <=12
gz68b5fq95hsu 1467 SAPSR3 SELECT /*+ FIRST_ROWS (12) */ * FROM "V_PRPMP" WHERE ("MANDT"='400' AND "POSID"
LIKE 'D.CDRU7%' ) AND ROWNUM <=12
gyy9tjs2quxdh 645 SAPSR3 SELECT /*+ FIRST_ROWS (2147483647) */ * FROM "M_PREMN" WHERE ("MANDT"='400' AND
"NCHMC" LIKE 'MONDINO%' ) AND ROWNUM <=2147483647
gya8r1358g028 436 SAPSR3 SELECT /*+ FIRST_ROWS (501) */ * FROM "M_KREDA" WHERE ("MANDT"='400' AND "MCOD1"
LIKE '%GE OIL%' ) AND ROWNUM <=501
gy70z72qzs16g 422 SAPSR3 SELECT DISTINCT "MSEG"."ANLN1","MSEG"."ANLN2","MSEG"."APLZL","MSEG"."AUFNR","MSE
G"."AUFPL","MKPF"."BKTXT","MKPF"."BLDAT","MSEG"."BPMNG","MSEG"."BPRME","MSEG"."B
STME","MSEG"."BSTMG","MKPF"."BUDAT","MSEG"."BUKRS","MSEG"."BWART","MSEG"."BWTAR"
,"MSEG"."CHARG","MKPF"."CPUDT","MKPF"."CPUTM","MSEG"."DMBTR","MSEG"."EBELN","MSE
G"."EBELP","MSEG"."ERFME","MSEG"."ERFMG","MSEG"."EXBWR","MSEG"."EXVKW","MKPF"."F
RBNR","MSEG"."GRUND","MSEG"."KDAUF","MSEG"."KDEIN","MSEG"."KDPOS","MSEG"."KOSTL"
,"MSEG"."KUNNR","MSEG"."KZBEW","MSEG"."KZVBR","MSEG"."KZZUG","MKPF"."LE_VBELN","
MSEG"."LFBNR","MSEG"."LFPOS","MSEG"."LGORT","MSEG"."LIFNR","MSEG"."MATNR","MSEG"
."MAT_KDAUF","MSEG"."MAT_KDPOS","MSEG"."MAT_PSPNR","MKPF"."MBLNR","MSEG"."MEINS"
,"MSEG"."MENGE","MKPF"."MJAHR","MSEG"."NPLNR","MSEG"."OID_EXTBOL","MSEG"."OID_MI
SCDL","MSEG"."PS_PSP_PNR","MSEG"."RSNUM","MSEG"."RSPOS","MSEG"."SAKTO","MSEG"."S
GTXT","MSEG"."SHKZG","MSEG"."SOBKZ","MKPF"."TCODE2","MKPF"."USNAM","MKPF"."VGART
","MSEG"."VKWRT","MSEG"."WAERS","MSEG"."
gzzj19ysppdq5 348 SAPSR3 SELECT /*+ FIRST_ROWS (501) */ * FROM "LFA1" WHERE ("MANDT"='400' AND "MCOD1" LI
KE '%STAPLES%' ) AND ROWNUM <=501
gx3a92vn65shj 342 SAPSR3 SELECT /*+ FIRST_ROWS (12) */ * FROM "M_KREDA" WHERE ("MANDT"='400' AND ("LIFNR"
LIKE '306210%' OR "LIFNR" LIKE '0306210%' OR "LIFNR" LIKE '00306210%' OR "LIFNR
" LIKE '000306210%' OR "LIFNR"='0000306210' )) AND ROWNUM <=12
gykkjxrcb46b3 256 SAPSR3 SELECT /*+ FIRST_ROWS (2147483647) */ * FROM "M_PREMN" WHERE ("MANDT"='400' AND
"NCHMC"='CAPONE' ) AND ROWNUM <=2147483647
gz0hca7va8cgz 235 SAPSR3 SELECT /*+ FIRST_ROWS (501) */ * FROM "V_PRPMP" WHERE ("MANDT"='400' AND "POSID"
LIKE '%1302%' ) AND ROWNUM <=501
gyqnnv2gw37zg 193 SAPSR3 SELECT /*+ FIRST_ROWS (12) */ * FROM "M_KOSTN" WHERE ("MANDT"='400' AND "KOSTL"
LIKE 'SUP%' AND "SPRAS"='S' ) AND ROWNUM <=12
gv1r7t4yxqwa0 177 SAPSR3 SELECT /*+ FIRST_ROWS (12) */ * FROM "M_KOSTN" WHERE ("MANDT"='400' AND "KOSTL"=
'AR10CD2240' AND "SPRAS"='S' ) AND ROWNUM <=12
gs173gbkw15cq 129 SAPSR3 SELECT /*+ FIRST_ROWS (501) */ * FROM "M_MAT1M" WHERE ("MANDT"='404' AND "MAKTG"
LIKE '%APC%' AND "SPRAS"='S' ) AND ROWNUM <=501
gx3m501tqvtha 121 SAPSR3 SELECT /*+ FIRST_ROWS (12) */ * FROM "M_KREDA" WHERE ("MANDT"='400' AND "LIFNR"
LIKE 'PE.%' ) AND ROWNUM <=12
gwpu7b8mnkcxn 117 SAPSR3 SELECT /*+ FIRST_ROWS (12) */ * FROM "M_MBEWM" WHERE ("MANDT"='400' AND "MATNR"=
'360206023200' AND "WERKS"='AR01' ) AND ROWNUM <=12
gy8prwt00m940 104 SAPSR3 SELECT /*+ FIRST_ROWS (11) */ * FROM "M_MAT1M" WHERE ("MANDT"='404' AND "MATNR"
LIKE '10002099%' AND "SPRAS"='S' ) AND ROWNUM <=11
gt1uzrnwptw6k 100 SAPSR3 SELECT /*+ FIRST_ROWS (501) */ * FROM "V_PRPMP" WHERE ("MANDT"='400' AND "POSTU"
LIKE '%1380%' ) AND ROWNUM <=501
gx73twmqrhjb4 95 SAPSR3 SELECT /*+ FIRST_ROWS (501) */ * FROM "SHP_VIEW_BOLN" WHERE ("MANDT"='400' AND "
BOLNR" LIKE '0105%' ) AND ROWNUM <=501
gqyzj3s6cz6qk 90 SAPSR3 SELECT /*+ FIRST_ROWS (12) */ * FROM "M_MEKKL" WHERE ("MANDT"='400' AND "LIFNR"=
'0000300344' ) AND ROWNUM <=12
gy5jcpurfcnh1 80 SAPSR3 SELECT /*+ FIRST_ROWS (501) */ * FROM "SHP_IDX_EXIB" WHERE ("MANDT"='400' AND "L
IFEX"='0183113302' ) AND ROWNUM <=501
guu1j97r8s2n8 80 SAPSR3 SELECT /*+ FIRST_ROWS (12) */ * FROM "M_ORDEB" WHERE ("MANDT"='400' AND "AUFNR"
LIKE 'LOG00000AR4%' ) AND ROWNUM <=12
gshf10zks36az 79 SAPSR3 SELECT /*+ FIRST_ROWS (12) */ * FROM "M_KREDA" WHERE ("MANDT"='400' AND ("LIFNR"
LIKE '30032%' OR "LIFNR" LIKE '030032%' OR "LIFNR" LIKE '0030032%' OR "LIFNR" L
IKE '00030032%' OR "LIFNR" LIKE '000030032%' OR "LIFNR"='0000030032' )) AND ROWN
UM <=12
gzyg8q9g5t5ck 78 SAPSR3 SELECT /*+ FIRST_ROWS (12) */ * FROM "M_KOSTN" WHERE ("MANDT"='400' AND "KOKRS"=
'1000' AND "KOSTL" LIKE 'AR10BCZ%' AND "SPRAS"='S' ) AND ROWNUM <=12
22 rows selected.
SQL>
Comments