 |
|
|
|
Next: Can 2008 client tools be used to connect and quer..
|
| Author |
Message |
External
Since: Dec 16, 2008
Posts: 15
|
(Msg. 1) Posted: Tue Dec 30, 2008 5:02 am
Post subject: SQL Flooding Shared Memory
Archived from groups: comp>databases>oracle>server (more info?)
|
|
|
Hi All
I have identified the SQL occupying max shared pool space. Detailed as
follows:::
SQL> show parameter shared_pool_size
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
shared_pool_size big integer 754974720
SQL>select bytes/1024/1024 FREE_MB
from v$sgastat
where name = 'free memory'
and pool = 'shared pool'
FREE_MB
----------
32MB
SQL>SELECT substr(sql_text,1,100) "Stmt", count(*),
sum(sharable_mem) "Mem",
sum(users_opening) "Open",
sum(executions) "Exec"
FROM v$sql
GROUP BY substr(sql_text,1,100)
HAVING sum(sharable_mem) > 10000000
Stmt COUNT(*) Mem Open Exec
---------------------------------------------------------------------------------------------------
---------- --------- ----- ------
select objid, title, TO_CHAR(escalate_time, 'YYYY/MM/DD HH24:MI:SS')
from T where 11325 225210490 1 11323
I have taken 1 SQL Statement for sample from v$sql, there are 11325
such copies ::
select objid, title, TO_CHAR(escalate_time, 'YYYY/MM/DD
HH24:MI:SS'),
from T where
escalate_time < TO_DATE('2008-12-30-03-56-32', 'YYYY-MM-DD-HH24-MI-
SS')
and FLOOR( flags / 16 ) * 2 = FLOOR( flags / 8 )
order by escalate_time asc, creation_time asc, objid asc
--- There are around 11K copies of above SQL statement. WIth diffrence
only in
escalate_time < TO_DATE('2008-12-30-03-56-32', 'YYYY-MM-DD-HH24-MI-
SS')
portion.
This SQL is run by the Application code.
Does this show the poor use of bind variables. I mean no bind variable
is used and therefore every time the SQL is executed.. a new copy of
the SQL is loaded and executed.
How we can get rid of above problem.
Many thanks |
|
| Back to top |
|
 | |
External
Since: Oct 01, 2008
Posts: 118
|
(Msg. 2) Posted: Tue Dec 30, 2008 6:02 am
Post subject: Re: SQL Flooding Shared Memory [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
On Dec 30, 7:02 am, shweta.kapar... DeleteThis @googlemail.com wrote:
> Hi All
>
> I have identified the SQL occupying max shared pool space. Detailed as
> follows:::
>
> SQL> show parameter shared_pool_size
>
> NAME TYPE VALUE
> ------------------------------------ -----------
> ------------------------------
> shared_pool_size big integer 754974720
>
> SQL>select bytes/1024/1024 FREE_MB
> from v$sgastat
> where name = 'free memory'
> and pool = 'shared pool'
> FREE_MB
> ----------
> 32MB
>
> SQL>SELECT substr(sql_text,1,100) "Stmt", count(*),
> sum(sharable_mem) "Mem",
> sum(users_opening) "Open",
> sum(executions) "Exec"
> FROM v$sql
> GROUP BY substr(sql_text,1,100)
> HAVING sum(sharable_mem) > 10000000
>
> Stmt COUNT(*) Mem Open Exec
> ---------------------------------------------------------------------------------------------------
> ---------- --------- ----- ------
> select objid, title, TO_CHAR(escalate_time, 'YYYY/MM/DD HH24:MI:SS')
> from T where 11325 225210490 1 11323
>
> I have taken 1 SQL Statement for sample from v$sql, there are 11325
> such copies ::
>
> select objid, title, TO_CHAR(escalate_time, 'YYYY/MM/DD
> HH24:MI:SS'),
> from T where
> escalate_time < TO_DATE('2008-12-30-03-56-32', 'YYYY-MM-DD-HH24-MI-
> SS')
> and FLOOR( flags / 16 ) * 2 = FLOOR( flags / 8 )
> order by escalate_time asc, creation_time asc, objid asc
>
> --- There are around 11K copies of above SQL statement. WIth diffrence
> only in
> escalate_time < TO_DATE('2008-12-30-03-56-32', 'YYYY-MM-DD-HH24-MI-
> SS')
> portion.
>
> This SQL is run by the Application code.
> Does this show the poor use of bind variables. I mean no bind variable
> is used and therefore every time the SQL is executed.. a new copy of
> the SQL is loaded and executed.
>
> How we can get rid of above problem.
>
> Many thanks
It shows absolutely no use of bind variables. And not every time that
basic query is run would it be necessary for a hard parse as the exact
same date and time could be used (although, as you've noted, that
isn't a likely occurrence). Using a bind variable would reduce your
query count to 1 and would make the application a bit more efficient
as one hard parse and 11k soft parses would occur.
This is truly poor coding of an application.
David Fitzjarrell |
|
| Back to top |
|
| |
External
Since: Oct 01, 2008
Posts: 118
|
(Msg. 3) Posted: Tue Dec 30, 2008 11:38 am
Post subject: Re: SQL Flooding Shared Memory [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
On Dec 30, 10:30 am, "gym dot scuba dot kennedy at gmail"
wrote:
> wrote in message
>
>
>
>
>
> > Hi All
>
> > I have identified the SQL occupying max shared pool space. Detailed as
> > follows:::
>
> > SQL> show parameter shared_pool_size
>
> > NAME TYPE VALUE
> > ------------------------------------ -----------
> > ------------------------------
> > shared_pool_size big integer 754974720
>
> > SQL>select bytes/1024/1024 FREE_MB
> > from v$sgastat
> > where name = 'free memory'
> > and pool = 'shared pool'
> > FREE_MB
> > ----------
> > 32MB
>
> > SQL>SELECT substr(sql_text,1,100) "Stmt", count(*),
> > sum(sharable_mem) "Mem",
> > sum(users_opening) "Open",
> > sum(executions) "Exec"
> > FROM v$sql
> > GROUP BY substr(sql_text,1,100)
> > HAVING sum(sharable_mem) > 10000000
>
> > Stmt COUNT(*) Mem Open Exec
> > ---------------------------------------------------------------------------------------------------
> > ---------- --------- ----- ------
> > select objid, title, TO_CHAR(escalate_time, 'YYYY/MM/DD HH24:MI:SS')
> > from T where 11325 225210490 1 11323
>
> > I have taken 1 SQL Statement for sample from v$sql, there are 11325
> > such copies ::
>
> > select objid, title, TO_CHAR(escalate_time, 'YYYY/MM/DD
> > HH24:MI:SS'),
> > from T where
> > escalate_time < TO_DATE('2008-12-30-03-56-32', 'YYYY-MM-DD-HH24-MI-
> > SS')
> > and FLOOR( flags / 16 ) * 2 = FLOOR( flags / 8 )
> > order by escalate_time asc, creation_time asc, objid asc
>
> > --- There are around 11K copies of above SQL statement. WIth diffrence
> > only in
> > escalate_time < TO_DATE('2008-12-30-03-56-32', 'YYYY-MM-DD-HH24-MI-
> > SS')
> > portion.
>
> > This SQL is run by the Application code.
> > Does this show the poor use of bind variables. I mean no bind variable
> > is used and therefore every time the SQL is executed.. a new copy of
> > the SQL is loaded and executed.
>
> > How we can get rid of above problem.
>
> > Many thanks
>
> Use bind variables.- Hide quoted text -
>
> - Show quoted text -
Yes, to rid yourself of this problem the code absolutely needs to use
bind variables, but *you* can't do that, this is a task for the
application vendor as it's their code, not yours. You might get some
mileage out of setting cursor_sharing to FORCE (a change I don't
recommend), but be aware this change may affect other query plans.
And remember if you make this change it's merely a crutch to prop up
bad code; it doesn't really fix anything.
You need to contact the vendor and discuss this issue with them and
propose a solution, which also means you'll need to test any new code
they write.
Good luck with this.
David Fitzjarrell >> Stay informed about: SQL Flooding Shared Memory |
|
| Back to top |
|
| |
External
Since: Dec 20, 2007
Posts: 294
|
(Msg. 4) Posted: Tue Dec 30, 2008 11:43 am
Post subject: Re: SQL Flooding Shared Memory [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
On Dec 30, 8:02 am, shweta.kapar... DeleteThis @googlemail.com wrote:
> Hi All
>
> I have identified the SQL occupying max shared pool space. Detailed as
> follows:::
>
> SQL> show parameter shared_pool_size
>
> NAME TYPE VALUE
> ------------------------------------ -----------
> ------------------------------
> shared_pool_size big integer 754974720
>
> SQL>select bytes/1024/1024 FREE_MB
> from v$sgastat
> where name = 'free memory'
> and pool = 'shared pool'
> FREE_MB
> ----------
> 32MB
>
> SQL>SELECT substr(sql_text,1,100) "Stmt", count(*),
> sum(sharable_mem) "Mem",
> sum(users_opening) "Open",
> sum(executions) "Exec"
> FROM v$sql
> GROUP BY substr(sql_text,1,100)
> HAVING sum(sharable_mem) > 10000000
>
> Stmt COUNT(*) Mem Open Exec
> ---------------------------------------------------------------------------------------------------
> ---------- --------- ----- ------
> select objid, title, TO_CHAR(escalate_time, 'YYYY/MM/DD HH24:MI:SS')
> from T where 11325 225210490 1 11323
>
> I have taken 1 SQL Statement for sample from v$sql, there are 11325
> such copies ::
>
> select objid, title, TO_CHAR(escalate_time, 'YYYY/MM/DD
> HH24:MI:SS'),
> from T where
> escalate_time < TO_DATE('2008-12-30-03-56-32', 'YYYY-MM-DD-HH24-MI-
> SS')
> and FLOOR( flags / 16 ) * 2 = FLOOR( flags / 8 )
> order by escalate_time asc, creation_time asc, objid asc
>
> --- There are around 11K copies of above SQL statement. WIth diffrence
> only in
> escalate_time < TO_DATE('2008-12-30-03-56-32', 'YYYY-MM-DD-HH24-MI-
> SS')
> portion.
>
> This SQL is run by the Application code.
> Does this show the poor use of bind variables. I mean no bind variable
> is used and therefore every time the SQL is executed.. a new copy of
> the SQL is loaded and executed.
>
> How we can get rid of above problem.
>
> Many thanks
If this is canned code you might consider the changing the value of
the database parameter cursor_sharing. The default value is EXACT.
You might want to consider setting it to SIMILAR or even FORCE. I
would try SIMILAR before I tried FORCE. See the Oracle version#
Reference manual for a description of the parameter.
To the optimizer the SQL will be different and it is possible (likely
even) that some plans will change. Some of the changes may not be for
the better.
An application that creates SQL statements using constants in all its
calls is a poorly designed application. Such applications are often
subject to SQL injection weakness. If this is a home grown
application it needs to be changed to use bind variables in the SQL.
If vendor purchased it should be junked unless the vendor is made
aware of the issue and agrees to modify the application in a timely
fashion.
HTH -- Mark D Powell -- >> Stay informed about: SQL Flooding Shared Memory |
|
| Back to top |
|
| |
|
gym dot scuba dot kennedy
|
External
Since: Jun 08, 2008
Posts: 77
|
(Msg. 5) Posted: Tue Dec 30, 2008 12:25 pm
Post subject: Re: SQL Flooding Shared Memory [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
wrote in message
> Hi All
>
> I have identified the SQL occupying max shared pool space. Detailed as
> follows:::
>
>
> SQL> show parameter shared_pool_size
>
> NAME TYPE VALUE
> ------------------------------------ -----------
> ------------------------------
> shared_pool_size big integer 754974720
>
> SQL>select bytes/1024/1024 FREE_MB
> from v$sgastat
> where name = 'free memory'
> and pool = 'shared pool'
> FREE_MB
> ----------
> 32MB
>
>
>
> SQL>SELECT substr(sql_text,1,100) "Stmt", count(*),
> sum(sharable_mem) "Mem",
> sum(users_opening) "Open",
> sum(executions) "Exec"
> FROM v$sql
> GROUP BY substr(sql_text,1,100)
> HAVING sum(sharable_mem) > 10000000
>
>
> Stmt COUNT(*) Mem Open Exec
> ---------------------------------------------------------------------------------------------------
> ---------- --------- ----- ------
> select objid, title, TO_CHAR(escalate_time, 'YYYY/MM/DD HH24:MI:SS')
> from T where 11325 225210490 1 11323
>
>
>
> I have taken 1 SQL Statement for sample from v$sql, there are 11325
> such copies ::
>
> select objid, title, TO_CHAR(escalate_time, 'YYYY/MM/DD
> HH24:MI:SS'),
> from T where
> escalate_time < TO_DATE('2008-12-30-03-56-32', 'YYYY-MM-DD-HH24-MI-
> SS')
> and FLOOR( flags / 16 ) * 2 = FLOOR( flags / 8 )
> order by escalate_time asc, creation_time asc, objid asc
>
>
> --- There are around 11K copies of above SQL statement. WIth diffrence
> only in
> escalate_time < TO_DATE('2008-12-30-03-56-32', 'YYYY-MM-DD-HH24-MI-
> SS')
> portion.
>
>
> This SQL is run by the Application code.
> Does this show the poor use of bind variables. I mean no bind variable
> is used and therefore every time the SQL is executed.. a new copy of
> the SQL is loaded and executed.
>
> How we can get rid of above problem.
>
> Many thanks
>
>
Use bind variables. >> Stay informed about: SQL Flooding Shared Memory |
|
| Back to top |
|
| |
External
Since: Apr 01, 2008
Posts: 60
|
(Msg. 6) Posted: Wed Dec 31, 2008 5:25 am
Post subject: Re: SQL Flooding Shared Memory [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
ddf schreef:
> On Dec 30, 10:30 am, "gym dot scuba dot kennedy at gmail"
> wrote:
>> wrote in message
>>
>>
>>
>>
>>
>>> Hi All
>>> I have identified the SQL occupying max shared pool space. Detailed as
>>> follows:::
>>> SQL> show parameter shared_pool_size
>>> NAME TYPE VALUE
>>> ------------------------------------ -----------
>>> ------------------------------
>>> shared_pool_size big integer 754974720
>>> SQL>select bytes/1024/1024 FREE_MB
>>> from v$sgastat
>>> where name = 'free memory'
>>> and pool = 'shared pool'
>>> FREE_MB
>>> ----------
>>> 32MB
>>> SQL>SELECT substr(sql_text,1,100) "Stmt", count(*),
>>> sum(sharable_mem) "Mem",
>>> sum(users_opening) "Open",
>>> sum(executions) "Exec"
>>> FROM v$sql
>>> GROUP BY substr(sql_text,1,100)
>>> HAVING sum(sharable_mem) > 10000000
>>> Stmt COUNT(*) Mem Open Exec
>>> ---------------------------------------------------------------------------------------------------
>>> ---------- --------- ----- ------
>>> select objid, title, TO_CHAR(escalate_time, 'YYYY/MM/DD HH24:MI:SS')
>>> from T where 11325 225210490 1 11323
>>> I have taken 1 SQL Statement for sample from v$sql, there are 11325
>>> such copies ::
>>> select objid, title, TO_CHAR(escalate_time, 'YYYY/MM/DD
>>> HH24:MI:SS'),
>>> from T where
>>> escalate_time < TO_DATE('2008-12-30-03-56-32', 'YYYY-MM-DD-HH24-MI-
>>> SS')
>>> and FLOOR( flags / 16 ) * 2 = FLOOR( flags / 8 )
>>> order by escalate_time asc, creation_time asc, objid asc
>>> --- There are around 11K copies of above SQL statement. WIth diffrence
>>> only in
>>> escalate_time < TO_DATE('2008-12-30-03-56-32', 'YYYY-MM-DD-HH24-MI-
>>> SS')
>>> portion.
>>> This SQL is run by the Application code.
>>> Does this show the poor use of bind variables. I mean no bind variable
>>> is used and therefore every time the SQL is executed.. a new copy of
>>> the SQL is loaded and executed.
>>> How we can get rid of above problem.
>>> Many thanks
>> Use bind variables.- Hide quoted text -
>>
>> - Show quoted text -
>
> Yes, to rid yourself of this problem the code absolutely needs to use
> bind variables, but *you* can't do that, this is a task for the
> application vendor as it's their code, not yours. You might get some
> mileage out of setting cursor_sharing to FORCE (a change I don't
> recommend), but be aware this change may affect other query plans.
> And remember if you make this change it's merely a crutch to prop up
> bad code; it doesn't really fix anything.
>
> You need to contact the vendor and discuss this issue with them and
> propose a solution, which also means you'll need to test any new code
> they write.
>
> Good luck with this.
>
>
> David Fitzjarrell
Setting cursor_sharing to FORCE has proven many times to give wrong
results in queries.
Shakespeare >> Stay informed about: SQL Flooding Shared Memory |
|
| Back to top |
|
| |
External
Since: Oct 01, 2008
Posts: 118
|
(Msg. 7) Posted: Wed Dec 31, 2008 6:40 am
Post subject: Re: SQL Flooding Shared Memory [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
On Dec 31, 4:18 am, Shakespeare wrote:
> ddf schreef:
>
>
>
>
>
> > On Dec 30, 10:30 am, "gym dot scuba dot kennedy at gmail"
> > wrote:
> >> wrote in message
>
> >>
>
> >>> Hi All
> >>> I have identified the SQL occupying max shared pool space. Detailed as
> >>> follows:::
> >>> SQL> show parameter shared_pool_size
> >>> NAME TYPE VALUE
> >>> ------------------------------------ -----------
> >>> ------------------------------
> >>> shared_pool_size big integer 754974720
> >>> SQL>select bytes/1024/1024 FREE_MB
> >>> from v$sgastat
> >>> where name = 'free memory'
> >>> and pool = 'shared pool'
> >>> FREE_MB
> >>> ----------
> >>> 32MB
> >>> SQL>SELECT substr(sql_text,1,100) "Stmt", count(*),
> >>> sum(sharable_mem) "Mem",
> >>> sum(users_opening) "Open",
> >>> sum(executions) "Exec"
> >>> FROM v$sql
> >>> GROUP BY substr(sql_text,1,100)
> >>> HAVING sum(sharable_mem) > 10000000
> >>> Stmt COUNT(*) Mem Open Exec
> >>> ---------------------------------------------------------------------------------------------------
> >>> ---------- --------- ----- ------
> >>> select objid, title, TO_CHAR(escalate_time, 'YYYY/MM/DD HH24:MI:SS')
> >>> from T where 11325 225210490 1 11323
> >>> I have taken 1 SQL Statement for sample from v$sql, there are 11325
> >>> such copies ::
> >>> select objid, title, TO_CHAR(escalate_time, 'YYYY/MM/DD
> >>> HH24:MI:SS'),
> >>> from T where
> >>> escalate_time < TO_DATE('2008-12-30-03-56-32', 'YYYY-MM-DD-HH24-MI-
> >>> SS')
> >>> and FLOOR( flags / 16 ) * 2 = FLOOR( flags / 8 )
> >>> order by escalate_time asc, creation_time asc, objid asc
> >>> --- There are around 11K copies of above SQL statement. WIth diffrence
> >>> only in
> >>> escalate_time < TO_DATE('2008-12-30-03-56-32', 'YYYY-MM-DD-HH24-MI-
> >>> SS')
> >>> portion.
> >>> This SQL is run by the Application code.
> >>> Does this show the poor use of bind variables. I mean no bind variable
> >>> is used and therefore every time the SQL is executed.. a new copy of
> >>> the SQL is loaded and executed.
> >>> How we can get rid of above problem.
> >>> Many thanks
> >> Use bind variables.- Hide quoted text -
>
> >> - Show quoted text -
>
> > Yes, to rid yourself of this problem the code absolutely needs to use
> > bind variables, but *you* can't do that, this is a task for the
> > application vendor as it's their code, not yours. You might get some
> > mileage out of setting cursor_sharing to FORCE (a change I don't
> > recommend), but be aware this change may affect other query plans.
> > And remember if you make this change it's merely a crutch to prop up
> > bad code; it doesn't really fix anything.
>
> > You need to contact the vendor and discuss this issue with them and
> > propose a solution, which also means you'll need to test any new code
> > they write.
>
> > Good luck with this.
>
> > David Fitzjarrell
>
> Setting cursor_sharing to FORCE has proven many times to give wrong
> results in queries.
>
> Shakespeare- Hide quoted text -
>
> - Show quoted text -
Which is why I stated I do not recommend such a change. It IS,
however, an option for the OP if he/she chooses to consider it.
David Fitzjarrell >> Stay informed about: SQL Flooding Shared Memory |
|
| Back to top |
|
| |
External
Since: Dec 20, 2007
Posts: 294
|
(Msg. 8) Posted: Wed Dec 31, 2008 8:40 am
Post subject: Re: SQL Flooding Shared Memory [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
On Dec 31, 9:40 am, ddf wrote:
> On Dec 31, 4:18 am, Shakespeare wrote:
>
>
>
>
>
> > ddf schreef:
>
> > > On Dec 30, 10:30 am, "gym dot scuba dot kennedy at gmail"
> > > wrote:
> > >> wrote in message
>
> > >>
>
> > >>> Hi All
> > >>> I have identified the SQL occupying max shared pool space. Detailed as
> > >>> follows:::
> > >>> SQL> show parameter shared_pool_size
> > >>> NAME TYPE VALUE
> > >>> ------------------------------------ -----------
> > >>> ------------------------------
> > >>> shared_pool_size big integer 754974720
> > >>> SQL>select bytes/1024/1024 FREE_MB
> > >>> from v$sgastat
> > >>> where name = 'free memory'
> > >>> and pool = 'shared pool'
> > >>> FREE_MB
> > >>> ----------
> > >>> 32MB
> > >>> SQL>SELECT substr(sql_text,1,100) "Stmt", count(*),
> > >>> sum(sharable_mem) "Mem",
> > >>> sum(users_opening) "Open",
> > >>> sum(executions) "Exec"
> > >>> FROM v$sql
> > >>> GROUP BY substr(sql_text,1,100)
> > >>> HAVING sum(sharable_mem) > 10000000
> > >>> Stmt COUNT(*) Mem Open Exec
> > >>> ---------------------------------------------------------------------------------------------------
> > >>> ---------- --------- ----- ------
> > >>> select objid, title, TO_CHAR(escalate_time, 'YYYY/MM/DD HH24:MI:SS')
> > >>> from T where 11325 225210490 1 11323
> > >>> I have taken 1 SQL Statement for sample from v$sql, there are 11325
> > >>> such copies ::
> > >>> select objid, title, TO_CHAR(escalate_time, 'YYYY/MM/DD
> > >>> HH24:MI:SS'),
> > >>> from T where
> > >>> escalate_time < TO_DATE('2008-12-30-03-56-32', 'YYYY-MM-DD-HH24-MI-
> > >>> SS')
> > >>> and FLOOR( flags / 16 ) * 2 = FLOOR( flags / 8 )
> > >>> order by escalate_time asc, creation_time asc, objid asc
> > >>> --- There are around 11K copies of above SQL statement. WIth diffrence
> > >>> only in
> > >>> escalate_time < TO_DATE('2008-12-30-03-56-32', 'YYYY-MM-DD-HH24-MI-
> > >>> SS')
> > >>> portion.
> > >>> This SQL is run by the Application code.
> > >>> Does this show the poor use of bind variables. I mean no bind variable
> > >>> is used and therefore every time the SQL is executed.. a new copy of
> > >>> the SQL is loaded and executed.
> > >>> How we can get rid of above problem.
> > >>> Many thanks
> > >> Use bind variables.- Hide quoted text -
>
> > >> - Show quoted text -
>
> > > Yes, to rid yourself of this problem the code absolutely needs to use
> > > bind variables, but *you* can't do that, this is a task for the
> > > application vendor as it's their code, not yours. You might get some
> > > mileage out of setting cursor_sharing to FORCE (a change I don't
> > > recommend), but be aware this change may affect other query plans.
> > > And remember if you make this change it's merely a crutch to prop up
> > > bad code; it doesn't really fix anything.
>
> > > You need to contact the vendor and discuss this issue with them and
> > > propose a solution, which also means you'll need to test any new code
> > > they write.
>
> > > Good luck with this.
>
> > > David Fitzjarrell
>
> > Setting cursor_sharing to FORCE has proven many times to give wrong
> > results in queries.
>
> > Shakespeare- Hide quoted text -
>
> > - Show quoted text -
>
> Which is why I stated I do not recommend such a change. It IS,
> however, an option for the OP if he/she chooses to consider it.
>
> David Fitzjarrell- Hide quoted text -
>
> - Show quoted text -
The best source of version specific problems using cursor_sharing =
FORCE or SIMILAR is in Oracle support document #94036.1
Init.ora Parameter "CURSOR_SHARING" Reference Note
There is a long list of referenced bugs with versions that the bug
affects. Most of the bugs are associated with specific features or
functions so sites that do not use these features/functions are likely
to avoid the bugs.
All our installations use EXACT due to query performance issues
encountered on past attempts to change the parameter.
HTH -- Mark D Powell -- >> Stay informed about: SQL Flooding Shared Memory |
|
| Back to top |
|
| |
External
Since: Jan 12, 2008
Posts: 176
|
(Msg. 9) Posted: Thu Jan 01, 2009 8:28 am
Post subject: Re: SQL Flooding Shared Memory [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
On Tue, 30 Dec 2008 05:02:18 -0800, shweta.kaparwan wrote:
> I mean no bind variable
> is used and therefore every time the SQL is executed.. a new copy of the
> SQL is loaded and executed.
>
> How we can get rid of above problem.
By recoding the app to use bind variables. Not using bind variables is
especially popular with the applications written in scripting languages
like Perl or PHP. I've come across the following snippet many times:
my $SEL="select * from table where column=$value";
I always flag that as a coding error. The proper way to code that
would be the following:
my $SEL="select * from table where column=:VALUE";
my $sel=$dbh->prepare($SEL);
$sel->bind_param(":VALUE",$value);
$sel->execute();
If the statement in $SEL has to be re-executed with a new value, all that
needs to be done is to bind new value to the :VALUE placeholder. No
parsing will be done, no new SQL will be stored.
--
http://mgogala.freehostia.com >> Stay informed about: SQL Flooding Shared Memory |
|
| Back to top |
|
| |
External
Since: Apr 01, 2008
Posts: 60
|
(Msg. 10) Posted: Fri Jan 02, 2009 3:25 am
Post subject: Re: SQL Flooding Shared Memory [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
Mark D Powell schreef:
> On Dec 31, 9:40 am, ddf wrote:
>> On Dec 31, 4:18 am, Shakespeare wrote:
>>
>>
>>
>>
>>
>>> ddf schreef:
>>>> On Dec 30, 10:30 am, "gym dot scuba dot kennedy at gmail"
>>>> wrote:
>>>>> wrote in message
>>>>>
>>>>>> Hi All
>>>>>> I have identified the SQL occupying max shared pool space. Detailed as
>>>>>> follows:::
>>>>>> SQL> show parameter shared_pool_size
>>>>>> NAME TYPE VALUE
>>>>>> ------------------------------------ -----------
>>>>>> ------------------------------
>>>>>> shared_pool_size big integer 754974720
>>>>>> SQL>select bytes/1024/1024 FREE_MB
>>>>>> from v$sgastat
>>>>>> where name = 'free memory'
>>>>>> and pool = 'shared pool'
>>>>>> FREE_MB
>>>>>> ----------
>>>>>> 32MB
>>>>>> SQL>SELECT substr(sql_text,1,100) "Stmt", count(*),
>>>>>> sum(sharable_mem) "Mem",
>>>>>> sum(users_opening) "Open",
>>>>>> sum(executions) "Exec"
>>>>>> FROM v$sql
>>>>>> GROUP BY substr(sql_text,1,100)
>>>>>> HAVING sum(sharable_mem) > 10000000
>>>>>> Stmt COUNT(*) Mem Open Exec
>>>>>> ---------------------------------------------------------------------------------------------------
>>>>>> ---------- --------- ----- ------
>>>>>> select objid, title, TO_CHAR(escalate_time, 'YYYY/MM/DD HH24:MI:SS')
>>>>>> from T where 11325 225210490 1 11323
>>>>>> I have taken 1 SQL Statement for sample from v$sql, there are 11325
>>>>>> such copies ::
>>>>>> select objid, title, TO_CHAR(escalate_time, 'YYYY/MM/DD
>>>>>> HH24:MI:SS'),
>>>>>> from T where
>>>>>> escalate_time < TO_DATE('2008-12-30-03-56-32', 'YYYY-MM-DD-HH24-MI-
>>>>>> SS')
>>>>>> and FLOOR( flags / 16 ) * 2 = FLOOR( flags / 8 )
>>>>>> order by escalate_time asc, creation_time asc, objid asc
>>>>>> --- There are around 11K copies of above SQL statement. WIth diffrence
>>>>>> only in
>>>>>> escalate_time < TO_DATE('2008-12-30-03-56-32', 'YYYY-MM-DD-HH24-MI-
>>>>>> SS')
>>>>>> portion.
>>>>>> This SQL is run by the Application code.
>>>>>> Does this show the poor use of bind variables. I mean no bind variable
>>>>>> is used and therefore every time the SQL is executed.. a new copy of
>>>>>> the SQL is loaded and executed.
>>>>>> How we can get rid of above problem.
>>>>>> Many thanks
>>>>> Use bind variables.- Hide quoted text -
>>>>> - Show quoted text -
>>>> Yes, to rid yourself of this problem the code absolutely needs to use
>>>> bind variables, but *you* can't do that, this is a task for the
>>>> application vendor as it's their code, not yours. You might get some
>>>> mileage out of setting cursor_sharing to FORCE (a change I don't
>>>> recommend), but be aware this change may affect other query plans.
>>>> And remember if you make this change it's merely a crutch to prop up
>>>> bad code; it doesn't really fix anything.
>>>> You need to contact the vendor and discuss this issue with them and
>>>> propose a solution, which also means you'll need to test any new code
>>>> they write.
>>>> Good luck with this.
>>>> David Fitzjarrell
>>> Setting cursor_sharing to FORCE has proven many times to give wrong
>>> results in queries.
>>> Shakespeare- Hide quoted text -
>>> - Show quoted text -
>> Which is why I stated I do not recommend such a change. It IS,
>> however, an option for the OP if he/she chooses to consider it.
>>
>> David Fitzjarrell- Hide quoted text -
>>
>> - Show quoted text -
>
> The best source of version specific problems using cursor_sharing =
> FORCE or SIMILAR is in Oracle support document #94036.1
> Init.ora Parameter "CURSOR_SHARING" Reference Note
>
> There is a long list of referenced bugs with versions that the bug
> affects. Most of the bugs are associated with specific features or
> functions so sites that do not use these features/functions are likely
> to avoid the bugs.
>
> All our installations use EXACT due to query performance issues
> encountered on past attempts to change the parameter.
>
> HTH -- Mark D Powell --
It's not the performance issues after changing cursor_sharing from exact
to anything else that worries me the most. It's the fact that on more
than one occasion I have seen queries return different results
(different number of rows, empty columns) even without special DB
features turned on (but with Oracle Spatial it's a sure disaster).
Problem is you would have to (re)test your complete application(s) to be
sure these problems are not in your DB, and NEVER have a guarantee they
will not hit you afterwards.
Shakespeare >> Stay informed about: SQL Flooding Shared Memory |
|
| Back to top |
|
| |
| Related Topics: | Determining Memory Allocations - Dedicated/Shared Server - I've found "formulas" for determining the SGA/PGA sizing for a dedicated server. Using a formula from the Burleson Consulting web site I was able to come up with these figures: -- ...
SQL plan different in dedicated vs. shared connection - Is this possible? I would think it would be possible with different session settings (hash_area_size, sort_area_size, etc), but this (I think) can only happen with work_area_policy set to auto, on this system it was set to manual. Anything else I am..
access to dbms_fga package on shared server - Hi, I manage an Oracle Database 10g R2 on Red Hat Enterprise Linux 4, the server is shared between a few customers: each customer has access (CONNECT and RESOURCE priveges) to his own schema only, he cannot access other customers objects. One of our...
how to place a table in memory - Hello everyone!!! Thinking about trying to put a table in memory... for some reason cannot find any info online :-( could anyone tell me where to look or show the syntax??? I would really apreciate it... Thank you very much in advance for help!
Memory Sizing Advice - |
|
You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum
|
|
|
|
|
|
|
FAQ
Search
Profile
Private Messages
Log in
