Too many unique SQL statements

[DmitryV]

Code: Select all

SQL> SELECT * FROM (
  2    SELECT UPPER(Substr(Sql_Text, 0, 70)) AS Sql_Text,
  3           SUM(Version_Count) AS Cnt, SUM(Parse_Calls) AS Parse,
  4           SUM(Executions) AS Exec, SUM(Sharable_Mem) AS Mem
  5      FROM V$SqlArea
  6     GROUP BY UPPER(SUBSTR(Sql_Text, 0, 70))
  7     ORDER BY Cnt DESC )
  8   WHERE ROWNUM < 2;
 
SQL_TEXT                                                                      CNT      PARSE       EXEC        MEM
---------------------------------------------------------------------- ---------- ---------- ---------- ----------
SELECT '' TABLE_CATALOG, IC.TABLE_OWNER TABLE_SCHEMA, IC.TABLE_NAME, '       1922       6608       6608 1001874339 
SQL> 

Wow...

Code: Select all

SELECT '' TABLE_CATALOG, IC.TABLE_OWNER TABLE_SCHEMA, IC.TABLE_NAME, '' INDEX_CATALOG, IC.INDEX_OWNER INDEX_SCHEMA, IC.INDEX_NAME, IC.COLUMN_NAME, IC.COLUMN_POSITION, DECODE(IC.DESCEND, 'ASC', 0, 1) DESCENDING FROM SYS.ALL_IND_COLUMNS IC, SYS.ALL_INDEXES I, SYS.ALL_CONSTRAINTS C WHERE IC.TABLE_OWNER = 'my_current_schema' AND IC.TABLE_NAME = 'my_table' AND I.UNIQUENESS = 'UNIQUE' AND        I.OWNER = IC.INDEX_OWNER AND I.INDEX_NAME = IC.INDEX_NAME AND       C.TABLE_NAME (+) = IC.TABLE_NAME AND C.INDEX_NAME (+) = IC.INDEX_NAME AND C.OWNER (+)= IC.TABLE_OWNER ORDER BY CASE WHEN IC.INDEX_OWNER = '"my_user_name"' THEN 0 WHEN IC.INDEX_OWNER = 'PUBLIC' THEN 1 ELSE 2 END, DECODE(C.CONSTRAINT_TYPE, 'P', 1, 'U', 2, 3), IC.INDEX_OWNER, IC.TABLE_OWNER, IC.TABLE_NAME, IC.INDEX_NAME, IC.COLUMN_POSITION

The function that creates such an expression was founded in module OraClasses.pas.

"Any-database-codemaking"?

Seriously, why not use a bind variables?

[AlexP]

Hello,

Thank you for the information. We will try to modify these queries for using parameters.

[DmitryV]

Thanks for the reply, Alex.
It would be good.