Re: SQL Area Reloads
Title: SQL Area Reloads You'll have reloads if your SQL is invalidated because of DDL or analyzing on tables. - Original Message - From: Ashish Sahasrabudhe To: Multiple recipients of list ORACLE-L Sent: Thursday, December 11, 2003 12:14 AM Subject: SQL Area Reloads The statspack report is showing 10,684 reload for SQL AREA with 2.2% misses. == Library Cache Activity for DB: MAIN Instance: MAIN Snaps: 18089 -18090 ->"Pct Misses" should be very low Get Pct Pin Pct Invali- Namespace Requests Miss Requests Miss Reloads dations --- -- -- -- -- BODY 12,186 0.0 12,186 0.0 0 0 CLUSTER 95 0.0 10 0.0 0 0 INDEX 466 0.0 461 0.0 0 0 SQL AREA 44,142 0.1 500,987 2.2 10,684 0 TABLE/PROCEDURE 208,498 0.1 584,219 0.0 1 0 TRIGGER 2,746 0.0 2,746 0.0 0 0 == The 250 MB of shared pool is free. Why would it cause reloads if there is so much free space available? Any help is appreciated. Ashish
Re: SQL Area Reloads
Title: Re: SQL Area Reloads Sure, it’s free now. But has that always been the case? on 12/10/03 3:14 PM, Ashish Sahasrabudhe at [EMAIL PROTECTED] wrote: The statspack report is showing 10,684 reload for SQL AREA with 2.2% misses. == Library Cache Activity for DB: MAIN Instance: MAIN Snaps: 18089 -18090 ->"Pct Misses" should be very low Get Pct Pin Pct Invali- Namespace Requests Miss Requests Miss Reloads dations --- -- -- -- -- BODY 12,186 0.0 12,186 0.0 0 0 CLUSTER 95 0.0 10 0.0 0 0 INDEX 466 0.0 461 0.0 0 0 SQL AREA 44,142 0.1 500,987 2.2 10,684 0 TABLE/PROCEDURE 208,498 0.1 584,219 0.0 1 0 TRIGGER 2,746 0.0 2,746 0.0 0 0 == The 250 MB of shared pool is free. Why would it cause reloads if there is so much free space available? Any help is appreciated. Ashish
RE: SQL Area Reloads
Title: Re: SQL Area Reloads Shared pool is free all the time. There are no DDLs executed, no analyze or no package compiles still there are SQL AREA reloads. The database is on 9i RAC. The reloads are appearing on RAC instances. -Original Message-From: Tim Gorman [mailto:[EMAIL PROTECTED]Sent: Wednesday, December 10, 2003 11:05 PMTo: Multiple recipients of list ORACLE-LSubject: Re: SQL Area ReloadsSure, it's free now. But has that always been the case?on 12/10/03 3:14 PM, Ashish Sahasrabudhe at [EMAIL PROTECTED] wrote: The statspack report is showing 10,684 reload for SQL AREA with 2.2% misses. == Library Cache Activity for DB: MAIN Instance: MAIN Snaps: 18089 -18090 ->"Pct Misses" should be very low Get Pct Pin Pct Invali- Namespace Requests Miss Requests Miss Reloads dations --- -- -- -- -- BODY 12,186 0.0 12,186 0.0 0 0 CLUSTER 95 0.0 10 0.0 0 0 INDEX 466 0.0 461 0.0 0 0 SQL AREA 44,142 0.1 500,987 2.2 10,684 0 TABLE/PROCEDURE 208,498 0.1 584,219 0.0 1 0 TRIGGER 2,746 0.0 2,746 0.0 0 0 == The 250 MB of shared pool is free. Why would it cause reloads if there is so much free space available? Any help is appreciated. Ashish
RE: SQL Area Reloads
OK, fair enough. Something to consider: the concept of "reload" probably also includes the concept of "initial/first load". I don't know whether this is the case for certain, but one way to check might be to query the number of items in the V$SQLAREA (i.e. "select count(*) from v$sqlarea"). After all, each of those entries had to be loaded (a.k.a. "reloaded") at least once, I think... > Shared pool is free all the time. > There are no DDLs executed, no analyze or no package > compiles still there are SQL AREA reloads. > The database is on 9i RAC. The reloads are appearing on > RAC instances. > -Original Message- > Sent: Wednesday, December 10, 2003 11:05 PM > To: Multiple recipients of list ORACLE-L > > > Sure, it's free now. But has that always been the case? > > > on 12/10/03 3:14 PM, Ashish Sahasrabudhe at > [EMAIL PROTECTED] wrote: > > > > The statspack report is showing 10,684 reload for SQL AREA > with 2.2% misses. > > == > == == > Library Cache Activity for DB: MAIN Instance: MAIN > Snaps: 18089 -18090 ->"Pct Misses" should be very low > > Get PctPinPct > Invali- > Namespace Requests Miss Requests Miss > Reloads dations > --- -- -- -- > -- > BODY 12,1860.0 12,1860.0 > 0 0 > CLUSTER 950.0 100.0 > 0 0 > INDEX4660.04610.0 > 0 0 > SQL AREA 44,1420.1500,9872.2 >10,684 0 > TABLE/PROCEDURE 208,4980.1584,2190.0 > 1 0 > TRIGGER2,7460.0 2,7460.0 > 0 0 > == > == == > > The 250 MB of shared pool is free. Why would it cause > reloads if there is so much > free space available? > > Any help is appreciated. > > Ashish > > > > > > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL Area Reloads
Title: RE: SQL Area Reloads No, "reload" does not include initial load. "reload" - Any PIN of an object that is not the first PIN performed since the object handle was created, and which requires loading the object from disk. "First_loads" = pins - pinhits - reloads Alex. -Original Message- From: Tim Gorman [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 11, 2003 8:39 AM To: Multiple recipients of list ORACLE-L Subject: RE: SQL Area Reloads OK, fair enough. Something to consider: the concept of "reload" probably also includes the concept of "initial/first load". I don't know whether this is the case for certain, but one way to check might be to query the number of items in the V$SQLAREA (i.e. "select count(*) from v$sqlarea"). After all, each of those entries had to be loaded (a.k.a. "reloaded") at least once, I think... > Shared pool is free all the time. > There are no DDLs executed, no analyze or no package > compiles still there are SQL AREA reloads. > The database is on 9i RAC. The reloads are appearing on > RAC instances. > -Original Message- > Sent: Wednesday, December 10, 2003 11:05 PM > To: Multiple recipients of list ORACLE-L > > > Sure, it's free now. But has that always been the case? > > > on 12/10/03 3:14 PM, Ashish Sahasrabudhe at > [EMAIL PROTECTED] wrote: > > > > The statspack report is showing 10,684 reload for SQL AREA with 2.2% > misses. > > == > == == > Library Cache Activity for DB: MAIN Instance: MAIN > Snaps: 18089 -18090 ->"Pct Misses" should be very low > > Get Pct Pin Pct > Invali- > Namespace Requests Miss Requests Miss > Reloads dations > --- -- -- -- > -- > BODY 12,186 0.0 12,186 0.0 > 0 0 > CLUSTER 95 0.0 10 0.0 > 0 0 > INDEX 466 0.0 461 0.0 > 0 0 > SQL AREA 44,142 0.1 500,987 2.2 > 10,684 0 > TABLE/PROCEDURE 208,498 0.1 584,219 0.0 > 1 0 > TRIGGER 2,746 0.0 2,746 0.0 > 0 0 > == > == == > > The 250 MB of shared pool is free. Why would it cause > reloads if there is so much > free space available? > > Any help is appreciated. > > Ashish > > > > > > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).