SQL Performance (MOSC)

MOSC Banner

Slow query without schema prefix

edited Sep 6, 2014 5:00AM in SQL Performance (MOSC) 4 commentsAnswered

Hi,

We have a Oracle Rdbms 11g (11.2.0.4.0) 64bit under Suse ES 11 (patchlevel 3) Standard Edition and we have a query which does a count(*) from fairly complex view:

select count(*) from <viewname> where <some conditions>

If this query is executed as the schema owner it takes consequently +/- 3.5 seconds to execute.

And if we execute the same query but this time with the schema owner pre-ffixed:

select count(*) from SHEMAOWNER.<viewname> where <some conditions>

The query now executes in 0.04 seconds. Apart from the SCHEMAOWNER prefix are both queries identical and are run under the schema owner itself.

The schema owner has 29000 objects. I've search in Metalink but haven't found a cause for this.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center