Slow query against ALL_OBJECTS view
From: Shujda <nlalovic_at_comcast.net>
Date: Tue, 18 Feb 2003 00:20:21 -0500
Message-ID: <u1qdnbKhweGMWMyjXTWcog_at_comcast.com>
Hello everyone,
I have about 700 users in database and about 300,000 rows in all_objects table/view.
More then 90% of the objects in the database are the private synonyms. The average user has about 450 private synonyms. When I logon to database, and execute the query: SELECT username FROM all_users WHERE EXISTS(SELECT 'X' FROM all_objects WHERE owner = username), I get the results within 3-4 seconds. When I logon as someone else, the same query takes forever to execute (more then 30 minutes). The difference between me and the other user is that I have connect, resource and dba roles and he has connect and resource only. When I grant him dba role, the same query gets executed very fast. When I revoke it, it becomes slow again. I know that all_users is a view and that it returns different recordset depending on who is logged on, but I didn't know that it can be such difference in performance... The biggest problem is that this query gets generated by SQL Navigator when you click on "Schemas" node, and I can't tune it up. What is the trick? How can I make it run faster. Did I hit some limits?
Thanks.
Shujda Received on Tue Feb 18 2003 - 06:20:21 CET
Date: Tue, 18 Feb 2003 00:20:21 -0500
Message-ID: <u1qdnbKhweGMWMyjXTWcog_at_comcast.com>
Hello everyone,
I have about 700 users in database and about 300,000 rows in all_objects table/view.
More then 90% of the objects in the database are the private synonyms. The average user has about 450 private synonyms. When I logon to database, and execute the query: SELECT username FROM all_users WHERE EXISTS(SELECT 'X' FROM all_objects WHERE owner = username), I get the results within 3-4 seconds. When I logon as someone else, the same query takes forever to execute (more then 30 minutes). The difference between me and the other user is that I have connect, resource and dba roles and he has connect and resource only. When I grant him dba role, the same query gets executed very fast. When I revoke it, it becomes slow again. I know that all_users is a view and that it returns different recordset depending on who is logged on, but I didn't know that it can be such difference in performance... The biggest problem is that this query gets generated by SQL Navigator when you click on "Schemas" node, and I can't tune it up. What is the trick? How can I make it run faster. Did I hit some limits?
Thanks.
Shujda Received on Tue Feb 18 2003 - 06:20:21 CET