got it! thankyou

Dear Tom,

Appreicate your services to the oracle community. What is the column combinations which is leading to SQL_ID.

is SQL_ID = hash_value + address ?

Regards
Lalitha

Hi tom,

if we fire same sql statement from different schemas. would sql_id be same? Schema1 and Schema2 both contains same objects but data in them differs. Please note that query is refering to its own schema objects.

Thanks in advance...

ops$tkyte%ORA11GR2> create user a identified by a default tablespace users quota unlimited on users;

User created.

ops$tkyte%ORA11GR2> create user b identified by b default tablespace users quota unlimited on users;

User created.

ops$tkyte%ORA11GR2> grant create table, create session to a;

Grant succeeded.

ops$tkyte%ORA11GR2> grant create table, create session to b;

Grant succeeded.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> connect a/a
Connected.
a%ORA11GR2> create table ttt ( x int );

Table created.

a%ORA11GR2> select * from ttt;

no rows selected

a%ORA11GR2> 
a%ORA11GR2> connect b/b
Connected.
b%ORA11GR2> create table ttt ( x int );

Table created.

b%ORA11GR2> select * from ttt;

no rows selected

b%ORA11GR2> 
b%ORA11GR2> connect /
Connected.
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select sql_id, PARSING_SCHEMA_NAME, sql_text from v$sql where sql_text = 'select * from ttt';

SQL_ID        PARSING_SCHEMA_NAME
------------- ------------------------------
SQL_TEXT
-------------------------------------------------------------------------------
ffuh3a5qharqh A
select * from ttt

ffuh3a5qharqh B
select * from ttt

Thanks for the explanation on Cursors. One related question.

Will I get same sql_id for a SQL even after I flush shared pool as oracle use hash algorithm to generate sql_id?

Same question for different oracle instances? Will I get same sql_id if I send same statement to two different instances of Oracle?

We've a 11gr2 database with cursor sharing set to similar. One of our load runs wherein we pick records from a table (DEVICE_AGE_AT_OCCURRENCE) and update matching records in table (ACCOUNT_PROFILE_DATA_MART).. something like:

declare
cursor c1 is
select device_id, return_nbr, device_age_at_occurance
from DEVICE_AGE_AT_OCCURRENCE;
begin
for v_c1 in c1 loop
execute immediate 'update ACCOUNT_PROFILE_DATA_MART set DEVICE_AGE_AT_OCCURANCE = v_c1.device_age_at_occurance where device_id = v_c1.devie_id and return_nbr = v_c1.return_nbr;

etc.

Now, when I join these 2 tables, I get 66180 odd records:

select count(*) from ddsowner.DEVICE_AGE_AT_OCCURRENCE a , ddsowner.ACCOUNT_PROFILE_DATA_MART b
where a.DEVICE_ID = b.DEVICE_ID
and a.RETURN_NBR = b.RETURN_NBR;

COUNT(*)
--------
   66817

But there are like over 2 million executions for this statement.. when I lookup v$sql_shared_cursors I see over 9000 cild cursors..

    CPU                  CPU per             Elapsd                     Old
  Time (s)   Executions  Exec (s)  %Total   Time (s)    Buffer Gets  Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
#########      681,101       0.70  557.9  #########       2,945,355 3125442085
Module: SQL*Plus
update ACCOUNT_PROFILE_DATA_MART set DEVICE_AGE_AT_OCCURRENCE =
:"SYS_B_0" where DEVICE_ID = :"SYS_B_1" and RETURN_NBR = :"SYS_B
_2"

Why there are som many executions when it should execute only 66180 times.. are there so many child cursors as this table has histograms & oracle get a different plan for popular values.. why is ACS not kicking in (how do I make ACS kick in)?

The sql_text shows all alike:

update ACCOUNT_PROFILE_DATA_MART set DEVICE_AGE_AT_OCCURRENCE = :"SYS_B_0" where DEVICE_ID = :"SYS_B_1" and RETURN_NBR = :"SYS_B_2"
update ACCOUNT_PROFILE_DATA_MART set DEVICE_AGE_AT_OCCURRENCE = :"SYS_B_0" where DEVICE_ID = :"SYS_B_1" and RETURN_NBR = :"SYS_B_2"
update ACCOUNT_PROFILE_DATA_MART set DEVICE_AGE_AT_OCCURRENCE = :"SYS_B_0" where DEVICE_ID = :"SYS_B_1" and RETURN_NBR = :"SYS_B_2"
update ACCOUNT_PROFILE_DATA_MART set DEVICE_AGE_AT_OCCURRENCE = :"SYS_B_0" where DEVICE_ID = :"SYS_B_1" and RETURN_NBR = :"SYS_B_2"
update ACCOUNT_PROFILE_DATA_MART set DEVICE_AGE_AT_OCCURRENCE = :"SYS_B_0" where DEVICE_ID = :"SYS_B_1" and RETURN_NBR = :"SYS_B_2"
update ACCOUNT_PROFILE_DATA_MART set DEVICE_AGE_AT_OCCURRENCE = :"SYS_B_0" where DEVICE_ID = :"SYS_B_1" and RETURN_NBR = :"SYS_B_2"

check parameter cursor_sharing
It should be force.

Tom,

I came across certain posts that says "Oracle creates different parent cursors in case if the sql text is different"

http://oracleinaction.com/parent-child-curosr/
&
Troubleshooting Oracle performance by Christian Antognini

Like if I fire the below queries:

1) select * from employee e where e.name like 'Blake%'
2) SELECT * FROM EMPLOYEE E WHERE E.NAME like 'Blake%'

will I be getting 2 parent cursors?

Why's that Oracle won't do an upper/lower of the SQL statement that's fired except for literals and create only one parent cursor?

like if I fire 1, it would understand that I am firing 2 and create only 1 parent cursor?

PS: I do not have access to Oracle as of now, I will login and check tomorrow and confirm if this behaviour is correct

Thanks,
Ravi.

Team:

the below demo was from 19.9 database, could you please help us to understand why new child cursors are getting created with "enable_parallel_dml" hint ?

demo@PDB1> create table stage as select * from all_objects;

Table created.

demo@PDB1> create table t as select * from stage;

Table created.

demo@PDB1> set serveroutput off
demo@PDB1> alter session enable parallel dml;

Session altered.

demo@PDB1> insert /*+ look_for_me1 parallel */ into t
  2  select *
  3  from stage;

67352 rows created.

demo@PDB1> commit;

Commit complete.

demo@PDB1>
demo@PDB1> insert /*+ look_for_me1 parallel */ into t
  2  select *
  3  from stage;

67352 rows created.

demo@PDB1> commit;

Commit complete.

demo@PDB1> alter session disable parallel dml;

Session altered.

demo@PDB1> select sql_id,child_number,executions,optimizer_env_hash_value
  2  from v$sql
  3  where lower(sql_text) like 'insert /*+ look_for_me1 parallel%';

SQL_ID        CHILD_NUMBER EXECUTIONS OPTIMIZER_ENV_HASH_VALUE
------------- ------------ ---------- ------------------------
9vhydgajw3tn0            1          2                693527528

demo@PDB1> insert /*+ look_for_me2 enable_parallel_dml parallel */ into t
  2  select *
  3  from stage;

67352 rows created.

demo@PDB1> commit;

Commit complete.

demo@PDB1>
demo@PDB1> insert /*+ look_for_me2 enable_parallel_dml parallel */ into t
  2  select *
  3  from stage;

67352 rows created.

demo@PDB1> commit;

Commit complete.

demo@PDB1>
demo@PDB1> select sql_id,child_number,executions,optimizer_env_hash_value
  2  from v$sql
  3  where lower(sql_text) like 'insert /*+ look_for_me2 enable_parallel_dml%';

SQL_ID        CHILD_NUMBER EXECUTIONS OPTIMIZER_ENV_HASH_VALUE
------------- ------------ ---------- ------------------------
4hxhnj22cv7yj            1          1                693527528
4hxhnj22cv7yj            2          1                693527528

demo@PDB1> select child_number,pdml_env_mismatch
  2  from v$sql_shared_cursor
  3  where sql_id ='4hxhnj22cv7yj';

CHILD_NUMBER P
------------ -
           1 N
           2 Y

demo@PDB1> select name,value
  2  from v$sql_optimizer_env
  3  where sql_id ='4hxhnj22cv7yj'
  4  and child_number =1
  5  minus
  6  select name,value
  7  from v$sql_optimizer_env
  8  where sql_id ='4hxhnj22cv7yj'
  9  and child_number =2 ;

no rows selected

demo@PDB1> select name,value
  2  from v$sql_optimizer_env
  3  where sql_id ='4hxhnj22cv7yj'
  4  and child_number =2
  5  minus
  6  select name,value
  7  from v$sql_optimizer_env
  8  where sql_id ='4hxhnj22cv7yj'
  9  and child_number =1 ;

no rows selected

demo@PDB1>

SQL> select sql_id,child_number,executions,optimizer_env_hash_value
  2  from v$sql
  3  where lower(sql_text) like 'insert /*+ look_for_me enable_parallel_dml%';

SQL_ID        CHILD_NUMBER EXECUTIONS OPTIMIZER_ENV_HASH_VALUE
------------- ------------ ---------- ------------------------
8gfph74bjdukh            1          1               3706774582
8gfph74bjdukh            2          1               3706774582
8gfph74bjdukh            3          1               3706774582
8gfph74bjdukh            4          1               3706774582
8gfph74bjdukh            5          1               3706774582
8gfph74bjdukh            6          1               3706774582
8gfph74bjdukh            7          1               3706774582
8gfph74bjdukh            8          1               3706774582
8gfph74bjdukh            9          1               3706774582
8gfph74bjdukh           10          1               3706774582
8gfph74bjdukh           11          1               3706774582

11 rows selected.