RE: figure out which queries are not using bind variables?
- From: ryan_gaffuri@xxxxxxxxxxx
- To: "Baumgartel, Paul" <paul.baumgartel@xxxxxxxxxxxxxxxxx>
- Date: Thu, 04 May 2006 19:47:28 +0000
semi on the same topic. How do I tell how much area in the shared has been used
up by parsed queries. When I go to v$sgastat, I believe I am only see how much
total is available?
I believe this entry tells me how much space I have for compiled sql correct?
shared pool sql area
Is there one for how much I have used so far?
-------------- Original message --------------
From: "Baumgartel, Paul" <paul.baumgartel@xxxxxxxxxxxxxxxxx>
> To the best of my knowledge, Oracle does not maintain a directory of non-bind
> SQL. You have to inspect the contents of the shared pool. One way is to find
> queries that have the same plan hash value:
>
> break on plan_hash_value
> select plan_hash_value, count(*) from v$sql
> where plan_hash_value > 0
> group by plan_hash_value having count(*) > 4
> order by count(*)
> /
>
> (there was a suggestion on the list that a clause be added to this to ensure
> that the SQL is really the same, as very different SQL often gets the same
> plan...I don't remember details but I'm sure you can find it in the
> archives.)
>
> Then you can get the SQL with
>
> break on plan_hash_value
> select plan_hash_value, count(*) from v$sql
> where plan_hash_value > 0
> group by plan_hash_value having count(*) > 4
> order by count(*)
> /
>
> There is also a Tom Kyte script that returns SQL with the literals replaced
> by @
> (for character literals) and # (for numeric literals):
>
> drop table t1;
>
> create table t1 as select sql_text from v$sqlarea;
>
> alter table t1 add sql_text_wo_constants varchar2(1000);
>
> create or replace function
> remove_constants( p_query in varchar2 ) return varchar2
> as
> l_query long;
> l_char varchar2(1);
> l_in_quotes boolean default FALSE;
> begin
> for i in 1 .. length( p_query )
> loop
> l_char := substr(p_query,i,1);
> if ( l_char = '''' and l_in_quotes )
> then
> l_in_quotes := FALSE;
> elsif ( l_char = '''' and NOT l_in_quotes )
> then
> l_in_quotes := TRUE;
> l_query := l_query || '''#';
> end if;
> if ( NOT l_in_quotes ) then
> l_query := l_query || l_char;
> end if;
> end loop;
> l_query := translate( l_query, '0123456789', '@@@@@@@@@@' );
> for i in 0 .. 8 loop
> l_query := replace( l_query, lpad('@',10-i,'@'), '@' );
> l_query := replace( l_query, lpad(' ',10-i,' '), ' ' );
> end loop;
> return upper(l_query);
> end;
> /
> update t1 set sql_text_wo_constants = remove_constants(sql_text);
>
> select sql_text_wo_constants, count(*)
> from t1
> group by sql_text_wo_constants
> having count(*) > 100
> order by 2
> /
>
>
>
>
> Paul Baumgartel
> CREDIT SUISSE
> Information Technology
> DBA & Admin - NY, KIGA 1
> 11 Madison Avenue
> New York, NY 10010
> USA
> Phone 212.538.1143
> paul.baumgartel@xxxxxxxxxxxxxxxxx
> www.credit-suisse.com
>
>
>
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Alex Gorbachev
> Sent: Thursday, May 04, 2006 3:01 PM
> To: ryan_gaffuri@xxxxxxxxxxx
> Cc: oracle-l@xxxxxxxxxxxxx
> Subject: Re: figure out which queries are not using bind variables?
>
>
> Recently, in this list there was a proposal to group sql statements by
> execution plan. Chances are that they are actually the same! Not a 10g
> feature though, or not a feature at all.
>
> 2006/5/4, ryan_gaffuri@xxxxxxxxxxx :
> >
> > Oracle 10g. Does Oracle track which queries do not use bind variables?
>
> --
> Best regards,
> Alex Gorbachev
>
> http://oracloid.blogspot.com
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
> ==============================================================================
>
> Please access the attached hyperlink for an important electronic
> communications
> disclaimer:
>
> http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
> ==============================================================================
>
>
Other related posts:
