Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Database performance is very very slow when I query metadata

775192Sep 30 2010 — edited Oct 14 2010
Hi,
I have installed Oracle 11G R2 recently and Its a oracle Standard edition single instance installation. When I run the below query it takes more than an 1minute to return the data. I have did another installation of oracle 11GR2 on on my laptop and it takes not more than 10sec. Why is this difference. Why is that query of meta data is taking so long. How to debug. Any help!

select component, final_size, end_time from v$sga_resize_ops where initial_size <> final_size order by end_time;

This is the query and its a direct select to a V$ table.

On one machine it takes 1min 31 sec.
SQL> r
1* select component, final_size, end_time from v$sga_resize_ops where initial_size <> final_size order by end_time

COMPONENT FINAL_SIZE END_TIME
---------------------------------------------------------------- ---------- ---------
shared pool 1157627904 30-SEP-10
large pool 16777216 30-SEP-10
DEFAULT buffer cache 3036676096 30-SEP-10
streams pool 16777216 30-SEP-10
java pool 16777216 30-SEP-10

Elapsed: 00:01:31.50
SQL>

ON LAPTOP:
java pool 4194304 03-SEP-10
streams pool 4194304 03-SEP-10
DEFAULT buffer cache 146800640 03-SEP-10
large pool 4194304 03-SEP-10
shared pool 230686720 03-SEP-10
DEFAULT buffer cache 142606336 03-SEP-10
shared pool 234881024 03-SEP-10
DEFAULT buffer cache 138412032 03-SEP-10
shared pool 239075328 03-SEP-10

TAKES <1 SEC

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Nov 11 2010
Added on Sep 30 2010
10 comments
1,343 views