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@xxxxxxxxx> 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.
--
