Slow create table statement

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 Received on Tue Jul 07 2020 - 23:48:15 CEST