Does Oracle database server stored runtime dynamic SQL?

Question

I have a plsql below that executes dynamic sql.

DECLARE
    sql_stmt VARCHAR2(200);
BEGIN
    sql_stmt := 'SELECT employee_name FROM employees WHERE employee_id = :1';
    EXECUTE IMMEDIATE sql_stmt USING 3360;
END;

Question: After running the PL/SQL above, does Oracle database server stored the runtime dynamic SQL somewhere?

For example, after running the code above, is there anyway to retrieve the code below?

EXECUTE IMMEDIATE SELECT employee_name FROM employees WHERE employee_id = 3360

I have tried v$sql and execution plan, but both of them contain the same copy of the PL/SQL (first code above), not the second code above.

I am using Oracle server 19.0.0.0.0 and accessing the server using dbeaver.

Answer 1

If you have Diagnostic pack license you can try:

• execute the query 10000x in a loop, so the query will get noticed by performance sampling

• Create AWS snapshot manually: EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

• Create AWR report(awrrpti)

  @$ORACLE_HOME/rdbms/admin/awrrpt.sql
  @$ORACLE_HOME/rdbms/admin/awrrpti.sql
  

Another option would be to execute the SQL and them imediatelly:

select prev_sql_id from v$session where sid in (select distinct sid from v$mystat.

V$SESSION knows the current and the previous SQL ID, so you can display it from the same session.

Answer 2

Your dynamic SQL (EXECUTE IMMEDIATE argument) will in fact create its own cursor in the shared pool and can be queried in v$sql (your PL/SQL block will also be its own cursor and also be visible there). However, it will have the bind variable :1 rather than the literal, as the literal isn't part of the SQL statement. That's the whole point of bind variables.

So, if you are querying v$sql looking for LIKE '%3360%' you won't find the SELECT statement (which is exactly 'SELECT employee_name FROM employees WHERE employee_id = :1'), though you will find the PL/SQL block because there it is embedded as a literal. If you needed for investigative performance reasons to get some bind values to plug into a SQL obtained from the shared pool, you can find them in v$sql_bind_capture, but that is more of a representative sample per cursor and doesn't capture every execution. It's meant for DBAs, not programmers, to help diagnose things like cursor sharing problems, predicate datatype mismatches that throw out index use, and manually test-run SQL obtained from the shared pool that uses binds which the DBA may not know anything about. It isn't a logging mechanism.

Rather than trying to get what you need from the shared pool, typically programmers will simply log the contents (using a variable to hold it, of course) of their EXECUTE IMMEDIATE to a log table, or if debugging might output it with dbms_output.put_line, and examine it that way. If they further need bind values, it's coming from their code and they're fully in control - they can log those bind values too.