ORA-00933: SQL command not properly ended while using CROSS APPLY

0
votes

I would like to run a query on v$sql using Common table expression and CROSS APPLY.

Here is my SQL:

WITH CTE AS
  (SELECT
    SUM(ELAPSED_TIME/1000/1000)/SUM(EXECUTIONS) AS Avg_Elapsed_Time_sec,
    SUM(ELAPSED_TIME/1000/1000) AS Sum_Elapsed_Time_sec,
    SUM(ELAPSED_TIME/1000/1000/(executions))    AS Sum_Avg_Elapsed_Time_sec,
    SUM(EXECUTIONS)                       AS Sum_Executions,
    SUM(ROWS_PROCESSED)                   AS Sum_Row_Processed,
    SUM(ROWS_PROCESSED) / SUM(executions) AS Avg_Row_Processed,
    SUM(FETCHES)                          AS Sum_Fetches,
    SUM(FETCHES) / SUM(EXECUTIONS)        AS Avg_Fetch,
    SUM(DISK_READS)                       AS Sum_DiskRead,
    SUM(DISK_READS) / SUM(EXECUTIONS)     AS Avg_DiskRead,
    SUM(APPLICATION_WAIT_TIME)            AS Sum_Application_Wait_Time,
    SUM(CONCURRENCY_WAIT_TIME)            AS Sum_Concurrency_Wait_Time,
    SUM(USER_IO_WAIT_TIME)                AS Sum_User_IO_Wait_Time,
    SUM(PLSQL_EXEC_TIME)                  AS Sum_PlSql_Exec_Time,
    SUM(OPTIMIZER_COST)                   AS Sum_Optimizer_Cost,
    SQL_ID,
    HASH_VALUE,
    COUNT(*)                              AS Entries
  FROM
    v$sql
  WHERE
    executions > 1
  GROUP BY
    SQL_ID,
    HASH_VALUE
  ORDER BY
    Avg_Elapsed_Time_sec DESC
  )
SELECT  D.SQL_FULLTEXT,CTE.* FROM v$sql 
CROSS APPLY //Error in this line
(
   select SQL_FULLTEXT from v$sql where v$sql.SQL_ID=CTE.SQL_ID and rownum=1
) D

How can I fix this error? but I get this error:

ORA-00933: SQL command not properly ended 00933. 00000 - "SQL command not properly ended" *Cause:
*Action:

2
sqloracle
Should be using a lateral join. - Alec.
From here CROSS APPLY has functionalities, which JOIN does not have - Kaja
Instesd of CROSS APPLY I used lateral join, but I get another error _ missing keyword_ - Kaja

2 Answers

2
votes

The equivalent would be a cross join lateral. You also have the select statement that selects from the CTE wrong. You need to select from the CTE not from the v$sql 

WITH cte AS
(
  SELECT .... 
)
SELECT cte.*, d.sql_fulltext
FROM cte --<< select from the CTE, not from V$SQL here!
  CROSS JOIN LATERAL (
      SELECT sql_fulltext
      FROM v$sql 
      WHERE cte.sql_id = v$sql.sql_id
        AND rownum = 1
) d
ORDER BY Avg_Elapsed_Time_sec DESC;

The order by inside the CTE doesn't make sense and it won't be preserved if you join the CTE to something else. You need to move it out to the statement that selects from the CTE.

You can replace CROSS JOIN LATERAL with CROSS APPLY but CROSS JOIN LATERAL is standard SQL whereas the APPLY isn't

Edit for for Oracle 11, you need to use something like this:

WITH cte AS (
   SELECT ...
)
SELECT cte.*, 
       d.sql_fulltext
FROM cte --<< select from the CTE, not from V$SQL here!
  JOIN (
      SELECT sql_id, 
             sql_fulltext, 
             row_number() over (partition by sql_id order by child_number) as rn
      FROM v$sql 
) d ON d.sql_id = cte.sql_id and d.rn = 1
ORDER BY Avg_Elapsed_Time_sec DESC;

V$SQL can contain multiple rows for the same SQL_ID (for different child cursors). The above statements shows the SQL text for the first child cursor.

0
votes

In 

SELECT  D.SQL_FULLTEXT,CTE.* FROM v$sql 
CROSS APPLY //Error in this line
(
   select SQL_FULLTEXT from v$sql where v$sql.SQL_ID=CTE.SQL_ID and rownum=1
) D

you are not selecting from CTE (but both times directly from v$sql). But in the cross apply you reference CTE.SQL_ID.

You probably want to select FROM CTE instead, so the cross apply would know what you are talking about :-) 

SELECT  D.SQL_FULLTEXT,CTE.* FROM CTE
CROSS APPLY
(
   select SQL_FULLTEXT from v$sql where v$sql.SQL_ID=CTE.SQL_ID and rownum=1
) D

BTW: You don't need CROSS APPLY at all:

SELECT 
  (select SQL_FULLTEXT from v$sql where v$sql.SQL_ID = CTE.SQL_ID and rownum = 1),
  CTE.* 
FROM CTE;