The DBA in your case is way way off. There can be MANY cached, parsed representations of the same query in the shared pool (and that is not necessarily a good thing, but - your DBA is not correct when they say:

....
but the problem is that (according to my DBA) there's only 1 parsed copy of an SQL statement in the shared_pool. During the parsing/execution process of the statement the database finds out that the cached statement isn't the statement he's looking for because it's referring to other tables (although they have the same name) => hard parse => performance degradation.
........

that is entirely INCORRECT and easy to show incorrect using tkprof.

run a script like this:

cconnect /
drop user a cascade;
drop user b cascade;

grant connect, resource to a identified by a;
grant connect, resource to b identified by b;

connect a/a
create table t as select 'this is from a' data from dual;
connect b/b
create table t as select 'this is from b' data from dual;
connect /
alter session set sql_trace=true;
alter session set current_schema=a;
select * from t;
select * from t;
select * from t;
select * from t;
alter session set current_schema=b;
select * from t;
select * from t;
select * from t;
select * from t;
alter session set current_schema=a;
select * from t;
select * from t;
select * from t;
select * from t;
alter session set current_schema=b;
select * from t;
select * from t;
select * from t;
select * from t;


and you'll see the output go from A to B to A to B and tkprof will show:



select *
from
t


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 16 0.00 0.00 0 2 0 0
Execute 16 0.00 0.00 0 0 0 0
Fetch 32 0.01 0.00 10 48 0 16
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 64 0.01 0.00 10 50 0 16

Misses in library cache during parse: 2
Optimizer mode: ALL_ROWS
Parsing user id: 126

Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL T (cr=3 pr=5 pw=0 time=840 us)


The misses in library cache = hard parses, there were 2 - but 16 parses total - 14 where SOFT parses, 2 were hard.


so

1) they will not share the same parsed cursor, they cannot - they will however each have their own parsed cursor in the shared pool.

If you have many of these however (lots of copies in the shared pool), that will become a problem in that it will take more "searching" to find the right cursor every time you soft parse.


2) sure, version_count in v$sqlarea has that already, or aggregating v$sql and counting.

v$sql would have the user/schema ids


3) nope, I'd much much rather have a SINGLE schema, with a SINGLE set of code, with a SINGLE set of sql statements - using fine grained access control or just plain old views (given that the predicate would probably be the same - meaning the flexibility of fine grained access control might be more than you need).





Now, if you use plsql stored procedures - they will have to be INVOKERS RIGHTS routines if you go this multi-schema approach (which I do not really like either). Expert One on One goes into why in the chapter in invoker/definers rights and fine grained access control.