Dear Tom,
We have an Oracle 12c database used mainly as a data warehouse for scientific data. The database has a few very large tables (>1TB) and they are usually accessed via a full table scan. The database is mostly idle having no more than one active session most of the time. When a user issues a very expensive query (requiring a full table scan over one of the large tables), the database usually shows ~10-20MB/s IO activity checked with the Real Time SQL Monitor in SQL Developer. The machine can easily maintain > 300MB/s IO. The server doesn't run anything besides the Oracle instance.
It seems like Resource Manager is not running.
SQL> SELECT NAME, IS_TOP_PLAN FROM V$RSRC_PLAN;
NAME IS_TO
-------------------------------- -----
INTERNAL_PLAN TRUE
No major waits besides
User IO and
System IOSQL> SELECT "WAIT_CLASS", "WAIT_COUNT", "TIME_WAITED" FROM(
select
c.WAIT_CLASS,
sum(m.WAIT_COUNT) as WAIT_COUNT,
ROUND(sum(m.TIME_WAITED)/100, 3) as TIME_WAITED
from
V$WAITCLASSMETRIC_HISTORY m,
V$SYSTEM_WAIT_CLASS c
where
m.WAIT_CLASS# = c.WAIT_CLASS# and c.WAIT_CLASS != 'Idle'
GROUP BY C.wait_class
ORDER BY 1
); 2 3 4 5 6 7 8 9 10 11 12 13
WAIT_CLASS WAIT_COUNT TIME_WAITED
------------------------------ ---------- -----------
Application 76 .008
Commit 371 2.716
Concurrency 2454 7.887
Configuration 274 2.162
Network 96941 .053
Other 19653 55.454
System I/O 477345 2127.141
User I/O 3453162 23426.765
8 rows selected.
Same issue exists for an expensive DML operation such as deleting very large number of rows. Running DML in parallel sessions helps increase overall linearly without affecting each other's performance though.
We do not currently have a dedicated DBA on our team so I take care of both development and maintenance of the database. It seems like there's some IO throttling even though resource manager is not running.
If I am correct in my assumption, is there a way to allow a single session fully utilize IO (and other resources)?
Thank you,