RE: identify CTAS from v$ tables

From: Joseph Amalraj <joseph@xxxxxxxxxxxxxx>
To: ramick <ramick@xxxxxxxxxxx>
Date: Tue, 25 Apr 2006 19:19:35 -0700 (PDT)
I tried the sql against v$sql and v$sqlarea and it did not return any rows.
   
  Agreed, dictionary managed temporary tablespaces are a bad thing. Conversion 
to locally managed temporary tablespaces is to take place very soon.
   
  During the period when ST enqueue blocking lock occurs, many times there have 
been "no rows" from  v$sort_usage table. 

   
  Thanks
   
  Joseph 
  
ramick <ramick@xxxxxxxxxxx> wrote:
        v\:* {behavior:url(#default#VML);}  o\:* {behavior:url(#default#VML);}  
w\:* {behavior:url(#default#VML);}  .shape {behavior:url(#default#VML);}        
        You should be able to get the sql text from v$sqlarea and v$sqltext.
  Something like:
  select hash_value, sql_text from v$sqlarea where
  upper(sql_text) like 'CREATE TABLE%AS SELECT%';
  if the SQL statement is less than 1000 characters.
   
  If the statement is longer, use the hash_value above to get the pieces
  from v$sqltext.
   
  Are any of the CTAS doing sorting, merge or hash joins or grouping as part of 
their select?  If so, they may be using a dictionary managed tablespace of type 
temporary or a permanent tablespace for their sort areas ? this would require 
an ST enqueue for every allocation and de-allocation of extents.  Permanent 
tablespaces are the worst for sort type operations as the sort segments are not 
reused ? every process doing a disk sort will create and then drop it?s own 
sort segment.
   
  HTH
   
      
---------------------------------
  
  From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Joseph Amalraj
Sent: Tuesday, April 25, 2006 4:00 PM
To: ramick
Cc: ganstadba@xxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: identify CTAS from v$ tables

   
    In this case, dictionary managed tablespaces are being used, but the extents

    are properly sized. CTAS and drop table are the only sql that need space 
management. I have suggested, to move towards delete/insert and use of global 
temporary table where possible. 

     

    Most of the scripts use CTAS and run once in 30 minutes or 1 hour.

     

    Any suggestions are welcome.

     

    Regards

     

    Joseph

ramick <ramick@xxxxxxxxxxx> wrote:

      Apologies if I missed something, but are you sure the ST enqueue locks 
are the result of CTAS?

     

    They could be anything that requires the allocation or deallocation of 
extents for any space management that is dictionary-managed.? Could be 
?tablespaces of type temporary or permanent tablespaces.

     

      
---------------------------------
  
    From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 
On Behalf Of Joseph Amalraj
Sent: Tuesday, April 25, 2006 2:15 PM
To: Stephane Faroult
Cc: ganstadba@xxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: Re: identify CTAS from v$ tables


     

      In this database, there are 'ST' enqueue locks and i have to identify the 
SQL statements issued by blocking and blocked sessions. I cannot use DDL 
triggers.


       


      Thanks


       


      Joseph

Stephane Faroult <sfaroult@xxxxxxxxxxxx> wrote:


      Joseph,

Would a DDL trigger be an acceptable solution to you? I haven't 
tried it recently, but I *think* that you can catch the statement text 
in the trigger. I am not fully certain about that, because since DDL 
statements are nothing else (ultimately) than DML statements applied to 
dictionary tables some are broken into their constituent DML components 
so fast that they let no track in the SGA.
Otherwise perhaps that V$object_dependency could help too ...

HTH

Sté°¨ane Faroult

Joseph Amalraj wrote:

> After going thru' the v$fixed_view_definition for v$sql, v$open_cursor 
> and
> v$sqltext, I could only get the name of the table being created by the 
> following query.
> 
> sys@clfypurg > select kglnaobj from x$kgllk where 
> kgllkmod = 3;
> KGLNAOBJ
> ------------------------------------------------------------
> MY_LARGE_TABLE
>
> if "alter session set sql_trace = true" is run before executing CTAS 
> the trace
> does generate the CTAS sql statement.
> 
> Can the sql be found from any x$ table ??
> 
> Thanks
> 
> Joseph
>
> */Michael McMullen /* wrote:
>
> Here's what TOAD uses but as I recall you can't get the underlying
> DDL statement for CTAS. Maybe changed in 10g.
> SELECT sql_text
> FROM v$sqltext_with_newlines
> WHERE hash_value = TO_NUMBER (:HASH)
> ORDER BY piece
>
>
RE: identify CTAS from v$ tables

Other related posts:
