RE: CTAS question

Would like to make a few clarifications....

1) the select stmt was run from the database server using sqlplus with
the foll..
set time on
set timing on
set autotrace traceonly

It returns 60k records in 7 min...
10046 was done for the select...there are parallel waits but nothing
alarming..
Most of the time is spend on sending data between slaves and QC during
hash join process..

To the select stmt i just add ""create table x1 select ......." and it
completes in 25 sec..

statistics for select ...

NEW Statistics
----------------------------------------------------------
         25  recursive calls
         11  db block gets
     134721  consistent gets
     122239  physical reads
        828  redo size
    6381270  bytes sent via SQL*Net to client
      46184  bytes received via SQL*Net from client
       4141  SQL*Net roundtrips to/from client
         17  sorts (memory)
          4  sorts (disk)
      62088  rows processed

Thanks,

Sat

On Tue, 30 May 2006 18:31:43 -0500, "Ric Van Dyke"
<ric.van.dyke@xxxxxxxxxx> said:
> Only way to know what is going on is to trace it and see.  Could be some
> bad network latency or like issue.  I suspect that the query actually
> completes very fast, it's getting the results back to the client that is
> the issue.
> 
> Turn on trace (own session):
> alter session set events '10046 trace name context forever, level 12'
> 
> Turn off trace (own session):
> alter session set events '10046 trace name context off'
> 
> Ric Van Dyke
> Hotsos Enterprises
> Cell 248-705-0624
> -----------------------
> Hotsos Symposium March 4-8, 2007.  Be there.
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of sat0789@xxxxxxxxxxx
> Sent: Tuesday, May 30, 2006 7:06 PM
> To: oracle-l@xxxxxxxxxxxxx
> Subject: CTAS question
> 
> Hello All,
>  I had a very basic question regarding CTAS Vs select
> 
> One of our queries takes about 7 min to complete but if i convert that
> query to write to a table using CTAS, it takes 25 sec.
> 
> Could someone please tell me how come CTAS is able to select from query
> and create a table in 25 sec while just the query alone takes 7 min to
> execute..
> 
> Thanks,
> 
> Sat
> 
> 
> -- 
> http://www.fastmail.fm - Same, same, but different...
> 
> --
> http://www.freelists.org/webpage/oracle-l
> 
> 

-- 
http://www.fastmail.fm - I mean, what is it about a decent email service?

--
http://www.freelists.org/webpage/oracle-l

Other related posts: