We see queries run from application , we get different sql_id, plan hash value when we take that sql text and rum from command line, sql worksheet or sql developer for those queries that get values at run time in the form of bind variables.
the thing that I am going explain here how can we run a query exactly the way it is get executed from an application.
Take that exact query:
Orignal query:
SELECT TABLE_OWNER FROM TABLE_VIEW_NAME WHERE FIELD_NAME='MARK' AND OWNER= :B1
this is how I ran
create or replace procedure temp_p_123( param in varchar2 )
as
type rc is ref cursor;
l_cursor rc;
begin
open l_cursor for SELECT TABLE_OWNER FROM TABLE_VIEW_NAME WHERE FIELD_NAME='MARK' AND OWNER= param;
end;
then
- PASS bind variable value exactly as it was passed from application.
begin
temp_p_123( param =>'INPUT' )
end;
then take the explain plan very after query got executed, if you could not find sql_id, get it from GV$sql, dba_hist_active_sess_history etc.
in my case , i see its in cursor cache.
SELECT * from table (dbms_xplan.display_cursor ('sql_id',NULL,'allstats advanced last'));
I found that the same sql_id for same statement that I ran in database is same as the SQL_ID of same statement that ran from application. including plan hash value.
- this test is done to see if sql_profile created is really helped.. and had to evaluate if sql_profile created for sql_id is really helped or execution is really made use of sql_profile.
look for Notes section:
Note
-----
- SQL profile PROFILE_xxxxxxxx used for this statement
