 | |
SGA / Latch Question
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
SGA / Latch Question -
12-30-2008
, 12:12 PM
#2
| |||
| |||
|
|
Hi, This just seems weird. *I look at our SPFILE: **.shared_pool_size=2G I query v$parameter: 1073741824 I ran a query: SHARED_POOL_USED SHARED_POOL_SIZE SHARED_POOL_AVAILPCT_SHARED_POOL_AVL ---------------- ---------------- ------------------------------------ * * * 2107.04836 * * * * * * 1024 * * * * * * * * 0205.766441 Ok, so if I have 2GB in my spfile, and I bounced my database, why is it reading 1GB, unless I'm a dork and cannot read properly.......Size = 1GB and used = 2GB?? Our sql library cache miss ratio says 99%, using that Spotlight utility and the latch waits are about 10%. *So, we figure we have a problem..... DUH?? |
Full Oracle version
values of database parameters:
sga_max_size
sga_target
pga_target
workarea_size_policy
shared_pool_size
db_cache_size
The actual query or queries you are using since the source cannot
always be determined just from seeing output.
HTH -- Mark D Powell --
#3
| |||
| |||
|
|
Hi, This just seems weird. *I look at our SPFILE: **.shared_pool_size=2G I query v$parameter: 1073741824 I ran a query: SHARED_POOL_USED SHARED_POOL_SIZE SHARED_POOL_AVAILPCT_SHARED_POOL_AVL ---------------- ---------------- ------------------------------------ * * * 2107.04836 * * * * * * 1024 * * * * * * * * 0205.766441 Ok, so if I have 2GB in my spfile, and I bounced my database, why is it reading 1GB, unless I'm a dork and cannot read properly.......Size = 1GB and used = 2GB?? Our sql library cache miss ratio says 99%, using that Spotlight utility and the latch waits are about 10%. *So, we figure we have a problem..... DUH?? |
Full Oracle version
values of database parameters:
sga_max_size
sga_target
pga_target
workarea_size_policy
shared_pool_size
db_cache_size
The actual query or queries you are using since the source cannot
always be determined just from seeing output.
HTH -- Mark D Powell --
#4
| |||
| |||
|
|
Hi, This just seems weird. *I look at our SPFILE: **.shared_pool_size=2G I query v$parameter: 1073741824 I ran a query: SHARED_POOL_USED SHARED_POOL_SIZE SHARED_POOL_AVAILPCT_SHARED_POOL_AVL ---------------- ---------------- ------------------------------------ * * * 2107.04836 * * * * * * 1024 * * * * * * * * 0205.766441 Ok, so if I have 2GB in my spfile, and I bounced my database, why is it reading 1GB, unless I'm a dork and cannot read properly.......Size = 1GB and used = 2GB?? Our sql library cache miss ratio says 99%, using that Spotlight utility and the latch waits are about 10%. *So, we figure we have a problem..... DUH?? |
Full Oracle version
values of database parameters:
sga_max_size
sga_target
pga_target
workarea_size_policy
shared_pool_size
db_cache_size
The actual query or queries you are using since the source cannot
always be determined just from seeing output.
HTH -- Mark D Powell --
#5
| |||
| |||
|
|
Hi, This just seems weird. *I look at our SPFILE: **.shared_pool_size=2G I query v$parameter: 1073741824 I ran a query: SHARED_POOL_USED SHARED_POOL_SIZE SHARED_POOL_AVAILPCT_SHARED_POOL_AVL ---------------- ---------------- ------------------------------------ * * * 2107.04836 * * * * * * 1024 * * * * * * * * 0205.766441 Ok, so if I have 2GB in my spfile, and I bounced my database, why is it reading 1GB, unless I'm a dork and cannot read properly.......Size = 1GB and used = 2GB?? Our sql library cache miss ratio says 99%, using that Spotlight utility and the latch waits are about 10%. *So, we figure we have a problem..... DUH?? |
Full Oracle version
values of database parameters:
sga_max_size
sga_target
pga_target
workarea_size_policy
shared_pool_size
db_cache_size
The actual query or queries you are using since the source cannot
always be determined just from seeing output.
HTH -- Mark D Powell --
#6
| |||
| |||
|
|
On Dec 30, 1:12*pm, mrdjmag... (AT) aol (DOT) com wrote: Hi, This just seems weird. *I look at our SPFILE: **.shared_pool_size=2G I query v$parameter: 1073741824 I ran a query: SHARED_POOL_USED SHARED_POOL_SIZE SHARED_POOL_AVAILPCT_SHARED_POOL_AVL ---------------- ---------------- ------------------------------------ * * * 2107.04836 * * * * * * 1024 * * * * ** * * 0205.766441 Ok, so if I have 2GB in my spfile, and I bounced my database, why is it reading 1GB, unless I'm a dork and cannot read properly.......Size = 1GB and used = 2GB?? Our sql library cache miss ratio says 99%, using that Spotlight utility and the latch waits are about 10%. *So, we figure we have a problem..... DUH?? Please provide the folowing information: Full Oracle version values of database parameters: sga_max_size sga_target pga_target workarea_size_policy shared_pool_size db_cache_size The actual query or queries you are using since the source cannot always be determined just from seeing output. HTH -- Mark D Powell -- |
Database: 10gr2
sga_max_size=12G
sga_target=10G
pga_aggregate_target=800M (No pga_target defined)
workarea_size_policy='AUTO'
shared_pool_size=2G
db_cache_size=3000M
#7
| |||
| |||
|
|
On Dec 30, 1:12*pm, mrdjmag... (AT) aol (DOT) com wrote: Hi, This just seems weird. *I look at our SPFILE: **.shared_pool_size=2G I query v$parameter: 1073741824 I ran a query: SHARED_POOL_USED SHARED_POOL_SIZE SHARED_POOL_AVAILPCT_SHARED_POOL_AVL ---------------- ---------------- ------------------------------------ * * * 2107.04836 * * * * * * 1024 * * * * ** * * 0205.766441 Ok, so if I have 2GB in my spfile, and I bounced my database, why is it reading 1GB, unless I'm a dork and cannot read properly.......Size = 1GB and used = 2GB?? Our sql library cache miss ratio says 99%, using that Spotlight utility and the latch waits are about 10%. *So, we figure we have a problem..... DUH?? Please provide the folowing information: Full Oracle version values of database parameters: sga_max_size sga_target pga_target workarea_size_policy shared_pool_size db_cache_size The actual query or queries you are using since the source cannot always be determined just from seeing output. HTH -- Mark D Powell -- |
Database: 10gr2
sga_max_size=12G
sga_target=10G
pga_aggregate_target=800M (No pga_target defined)
workarea_size_policy='AUTO'
shared_pool_size=2G
db_cache_size=3000M
#8
| |||
| |||
|
|
On Dec 30, 1:12*pm, mrdjmag... (AT) aol (DOT) com wrote: Hi, This just seems weird. *I look at our SPFILE: **.shared_pool_size=2G I query v$parameter: 1073741824 I ran a query: SHARED_POOL_USED SHARED_POOL_SIZE SHARED_POOL_AVAILPCT_SHARED_POOL_AVL ---------------- ---------------- ------------------------------------ * * * 2107.04836 * * * * * * 1024 * * * * ** * * 0205.766441 Ok, so if I have 2GB in my spfile, and I bounced my database, why is it reading 1GB, unless I'm a dork and cannot read properly.......Size = 1GB and used = 2GB?? Our sql library cache miss ratio says 99%, using that Spotlight utility and the latch waits are about 10%. *So, we figure we have a problem..... DUH?? Please provide the folowing information: Full Oracle version values of database parameters: sga_max_size sga_target pga_target workarea_size_policy shared_pool_size db_cache_size The actual query or queries you are using since the source cannot always be determined just from seeing output. HTH -- Mark D Powell -- |
Database: 10gr2
sga_max_size=12G
sga_target=10G
pga_aggregate_target=800M (No pga_target defined)
workarea_size_policy='AUTO'
shared_pool_size=2G
db_cache_size=3000M
#9
| |||
| |||
|
|
On Dec 30, 1:12*pm, mrdjmag... (AT) aol (DOT) com wrote: Hi, This just seems weird. *I look at our SPFILE: **.shared_pool_size=2G I query v$parameter: 1073741824 I ran a query: SHARED_POOL_USED SHARED_POOL_SIZE SHARED_POOL_AVAILPCT_SHARED_POOL_AVL ---------------- ---------------- ------------------------------------ * * * 2107.04836 * * * * * * 1024 * * * * ** * * 0205.766441 Ok, so if I have 2GB in my spfile, and I bounced my database, why is it reading 1GB, unless I'm a dork and cannot read properly.......Size = 1GB and used = 2GB?? Our sql library cache miss ratio says 99%, using that Spotlight utility and the latch waits are about 10%. *So, we figure we have a problem..... DUH?? Please provide the folowing information: Full Oracle version values of database parameters: sga_max_size sga_target pga_target workarea_size_policy shared_pool_size db_cache_size The actual query or queries you are using since the source cannot always be determined just from seeing output. HTH -- Mark D Powell -- |
Database: 10gr2
sga_max_size=12G
sga_target=10G
pga_aggregate_target=800M (No pga_target defined)
workarea_size_policy='AUTO'
shared_pool_size=2G
db_cache_size=3000M
#10
| |||
| |||
|
|
On Dec 30, 1:34*pm, Mark D Powell <Mark.Pow... (AT) eds (DOT) com> wrote: On Dec 30, 1:12*pm, mrdjmag... (AT) aol (DOT) com wrote: Hi, This just seems weird. *I look at our SPFILE: **.shared_pool_size=2G I query v$parameter: 1073741824 I ran a query: SHARED_POOL_USED SHARED_POOL_SIZE SHARED_POOL_AVAILPCT_SHARED_POOL_AVL ---------------- ---------------- ------------------------------------ * * * 2107.04836 * * * * * * 1024 * * * * * * * * 0205.766441 Ok, so if I have 2GB in my spfile, and I bounced my database, why is it reading 1GB, unless I'm a dork and cannot read properly.......Size = 1GB and used = 2GB?? Our sql library cache miss ratio says 99%, using that Spotlight utility and the latch waits are about 10%. *So, we figure we have a problem..... DUH?? Please provide the folowing information: Full Oracle version values of database parameters: sga_max_size sga_target pga_target workarea_size_policy shared_pool_size db_cache_size The actual query or queries you are using since the source cannot always be determined just from seeing output. HTH -- Mark D Powell -- Database: *10gr2 sga_max_size=12G sga_target=10G pga_aggregate_target=800M * *(No pga_target defined) workarea_size_policy='AUTO' shared_pool_size=2G db_cache_size=3000M |
http://download.oracle.com/docs/cd/B....htm#sthref404
This shared pool parameter is being automatically tuned by Oracle, so
looking at the setting that would be used if you were "manual" may be
a bit confusing. You might want to google around for good
descriptions of workarea_size_policy. But be careful, there are a lot
of myths and falsehoods floating about. The advice tables for setting
pga and those things seem pretty good in my experience, though some
things just keep asking for more and more. But it is nice and easy to
just use EM to check them out.
Here's a start: http://asktom.oracle.com/pls/asktom/...75 9826405304
Since you have another thread that seems to show strange code, it is
possible the advice tables and anything based on them may be fooled.
They are just tools, and any such tools have built-in assumptions.
There is no pga_target.
Some other important pieces to the puzzle:
Are you using shared servers? (ohhh, that difference isn't supposed
to be true anymore...)
Which _exact_ version of Oracle are you using? 10gR2 is not good
enough, it should be something like 10.2.0.4.0
Which platform are you on? (OS, version, hardware)
And as Mark asked, show the query you are using to make the
determination.
jg
--
@home.com is bogus.
$40M/year: http://www3.signonsandiego.com/stori...le-/?uniontrib
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.