Re: Slow create table statement

Hi Ram,

Your count(*) query doesn't have to touch the S_CLN_EVNT table, it happily just runs a few thousand index scans to check existence. Your create table has to actually go to the table to read additional data, it has to read almost 15 million rows so that is definitely going to take a while, parallel or otherwise. There is also an additional CPU heavy step near the end to gather statistics on what it loads, but I doubt this is your problem.

I can see that your S_CLN_EVNT table is partitioned, is it partitioned by anything that would help with this query?

A guess would be to try hinting at a full scan and a hash join to your S_CLN_EVNT table. There's not a lot of other things you can to help, besides making a covering index - but one would assume you're not going to require running this query often so it's probably not worth the effort.

I should note that "parallel read" has nothing to do with parallel query (which is what you hinted), it is a way that Oracle can submit multiple IO requests from the same process before waiting on them all to get back. It is essentially batching "db file sequential read" events. The "PX%" waits are related to parallel query - the majority will mean that it's waiting on one of the parallel workers to get back to that process (acting as the query coordinator).

Hope that helps,
Andy

On Tue, 7 Jul 2020 at 22:49, Ram Raman <veeeraman_at_gmail.com> wrote:

> Hi,
>
> We are trying to create a table by pulling information from a big table,
> the create statement is taking too long. However when I run select count(*)
> with the same query it comes back in seconds. While the CREATE statement is
> running I see waits on parallel read almost all the time, however, I do not
> see the same waits during select count(*).
>
> Tried a few variations with parallelism degree in the Create statement.
> Also tried inserting after truncating the table, used INSERT with APPEND
> hint, same slow results. v12c. FORCE_LOGGING is not on at DB level,
> inserted table in nologging mode. During the run, the IO on the data disks
> is about 50 to 70% util. 2 cores are consistently at 99-100% (most of them
> are %iowait at ~95%) with the other cores idle during this run the last
> time I checked - I only checked the individual core usage this last time it
> was run. I was checking average before. The wait events are the same in all
> cases.
>
> The attached link
> <https://drive.google.com/file/d/1N8re2eAWLl0FHYdGs0c9I93e5ruoSNN6/view?usp=sharing>
> has all the commands and the information. Any help is appreciated.
>
> Thanks,
> Ram.
>
> --
>
>

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