 | |
SGA / Latch Question
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#11
 
| |||
| |||
|
Re: SGA / Latch Question -
12-30-2008
, 04:32 PM
|
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
#12
| |||
| |||
|
|
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
#13
| |||
| |||
|
|
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
#14
| |||
| |||
|
|
On Dec 30, 11:55*am, mrdjmag... (AT) aol (DOT) com wrote: 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 havea 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 Seehttp://download.oracle.com/docs/cd/B19306_01/server.102/b14211/memory....http://download.oracle.com/docs/cd/B.../b14231/create... 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/...51819884::::P1... 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...ett212021-...- Hide quoted text - - Show quoted text - |
MRDJMAG, you may want to check out the information in the following
views
v$sga_dynamic_components
v$sga_resize_ops
v$sga_current_resize_ops
HTH -- Mark D Powell --
#15
| |||
| |||
|
|
On Dec 30, 11:55*am, mrdjmag... (AT) aol (DOT) com wrote: 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 havea 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 Seehttp://download.oracle.com/docs/cd/B19306_01/server.102/b14211/memory....http://download.oracle.com/docs/cd/B.../b14231/create... 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/...51819884::::P1... 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...ett212021-...- Hide quoted text - - Show quoted text - |
MRDJMAG, you may want to check out the information in the following
views
v$sga_dynamic_components
v$sga_resize_ops
v$sga_current_resize_ops
HTH -- Mark D Powell --
#16
| |||
| |||
|
|
On Dec 30, 11:55*am, mrdjmag... (AT) aol (DOT) com wrote: 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 havea 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 Seehttp://download.oracle.com/docs/cd/B19306_01/server.102/b14211/memory....http://download.oracle.com/docs/cd/B.../b14231/create... 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/...51819884::::P1... 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...ett212021-...- Hide quoted text - - Show quoted text - |
MRDJMAG, you may want to check out the information in the following
views
v$sga_dynamic_components
v$sga_resize_ops
v$sga_current_resize_ops
HTH -- Mark D Powell --
#17
| |||
| |||
|
|
On Dec 30, 11:55*am, mrdjmag... (AT) aol (DOT) com wrote: 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 havea 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 Seehttp://download.oracle.com/docs/cd/B19306_01/server.102/b14211/memory....http://download.oracle.com/docs/cd/B.../b14231/create... 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/...51819884::::P1... 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...ett212021-...- Hide quoted text - - Show quoted text - |
MRDJMAG, you may want to check out the information in the following
views
v$sga_dynamic_components
v$sga_resize_ops
v$sga_current_resize_ops
HTH -- Mark D Powell --
|
« 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.