Содержание
- ORA-12008 and ORA-00942 Error Reported During Materialized View Refresh Across Schema (Doc ID 2559390.1)
- Applies to:
- Symptoms
- Cause
- To view full details, sign in with your My Oracle Support account.
- Don’t have a My Oracle Support account? Click to get started!
- Materialized View Refresh Failing with errors ORA-12008 ORA-31600 ORA-30372 (Doc ID 2738707.1)
- Applies to:
- Symptoms
- Changes
- Cause
- To view full details, sign in with your My Oracle Support account.
- Don’t have a My Oracle Support account? Click to get started!
- Ora-12008 And Ora-01732 When Doing Complete Refresh On Materialized View (Doc ID 732614.1)
- Applies to:
- Symptoms
- Changes
- Cause
- To view full details, sign in with your My Oracle Support account.
- Don’t have a My Oracle Support account? Click to get started!
- ORA-12008: error in materialized view refresh path
- Answers
- DBMS_REDEFINITION package throwing error ORA-12008 & ORA-01410: invalid ROWID (Doc ID 1572193.1)
- Applies to:
- Symptoms
- Changes
- Cause
- To view full details, sign in with your My Oracle Support account.
- Don’t have a My Oracle Support account? Click to get started!
ORA-12008 and ORA-00942 Error Reported During Materialized View Refresh Across Schema (Doc ID 2559390.1)
Last updated on FEBRUARY 28, 2021
Applies to:
Symptoms
When usera has a base table, userb has a materialized view based on usera’s table.
userc executed userb’s materialized view, and failed with ORA-12008 and ORA-00942.
The test case is as following:
Event when using sysdba to execute userb’s materialized view, still failed.
Cause
To view full details, sign in with your My Oracle Support account.
Don’t have a My Oracle Support account? Click to get started!
In this Document
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.
Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit oracle.com. пїЅ Oracle | Contact and Chat | Support | Communities | Connect with us | 
| 
| 
| Legal Notices | Terms of Use
Источник
Materialized View Refresh Failing with errors ORA-12008 ORA-31600 ORA-30372 (Doc ID 2738707.1)
Last updated on JULY 21, 2022
Applies to:
Symptoms
On : 19.3.0.0.0 version, Materialised Views
Materialized ViewВ Refresh Failing below errors.
The creation of the materialized view works fine, but the refresh fails.
SQL> exec BEGIN DBMS_MVIEW.REFRESH(‘ .xxx’, method => ‘CF’,atomic_refresh => FALSE,out_of_place => TRUE); END;
BEGIN BEGIN DBMS_MVIEW.REFRESH(‘VALUATION.WS_PROPERTY_SEARCH_VW’, method => ‘CF’,atomic_refresh => FALSE,out_of_place => TRUE); END; END;
*
ERROR at line 1:
ORA-12008: error in materialized view or zonemap refresh path
ORA-06512: at «SYS.DBMS_SNAPSHOT_KKXRCA», line 3012
ORA-06512: at «SYS.DBMS_SNAPSHOT_KKXRCA», line 2424
ORA-31600: invalid input value COMPATIBLE for parameter VERSION in function
OPEN
ORA-06512: at «SYS.DBMS_SNAP_INTERNAL», line 1487
ORA-06512: at «SYS.DBMS_METADATA», line 6516
ORA-06512: at «SYS.DBMS_SYS_ERROR», line 105
ORA-06512: at «SYS.DBMS_METADATA», line 6422
ORA-06512: at «SYS.DBMS_METADATA», line 6762
ORA-06512: at «SYS.DBMS_SNAP_INTERNAL», line 1427
ORA-06512: at line 1
ORA-06512: at «SYS.DBMS_SNAPSHOT_KKXRCA», line 88
ORA-06512: at «SYS.DBMS_SNAPSHOT_KKXRCA», line 253
ORA-06512: at «SYS.DBMS_SNAPSHOT_KKXRCA», line 2405
ORA-06512: at «SYS.DBMS_SNAPSHOT_KKXRCA», line 2968
ORA-06512: at «SYS.DBMS_SNAPSHOT_KKXRCA», line 3255
ORA-06512: at «SYS.DBMS_SNAPSHOT_KKXRCA», line 3287
ORA-06512: at «SYS.DBMS_SNAPSHOT», line 16
ORA-06512: at line 1
В
Changes
Refresh works fine with «USING TRUSTED CONSTRAINTS» , but fails when MView created with «USING ENFORCED CONSTRAINTS» .
ORA-30372 error found on internal objects only.
No policy found, No Fine grained access policy found on objects or no VPD access found.В
And With «out_of_place» => FALSE works fine but TRUE fails.
Cause
To view full details, sign in with your My Oracle Support account.
Don’t have a My Oracle Support account? Click to get started!
In this Document
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.
Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit oracle.com. пїЅ Oracle | Contact and Chat | Support | Communities | Connect with us | | | | Legal Notices | Terms of Use
Источник
Ora-12008 And Ora-01732 When Doing Complete Refresh On Materialized View (Doc ID 732614.1)
Last updated on FEBRUARY 01, 2022
Applies to:
Oracle Database — Enterprise Edition — Version 10.2.0.2 and later
Oracle Database Cloud Schema Service — Version N/A and later
Oracle Database Exadata Cloud Machine — Version N/A and later
Oracle Cloud Infrastructure — Database Service — Version N/A and later
Oracle Database Backup Service — Version N/A and later
Information in this document applies to any platform.
Symptoms
NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.В
When try to refresh the materialized view with complete method, the following error is displayed:В
SQL> EXEC DBMS_MVIEW.REFRESH(‘ ‘);
BEGIN DBMS_MVIEW.REFRESH(‘ ‘); END;
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-01732: data manipulation operation not legal on this view
ORA-06512: at «SYS.DBMS_SNAPSHOT», line 2255
ORA-06512: at «SYS.DBMS_SNAPSHOT», line 2461
ORA-06512: at «SYS.DBMS_SNAPSHOT», line 2430
ORA-06512: at line 1
Changes
Cause
To view full details, sign in with your My Oracle Support account.
Don’t have a My Oracle Support account? Click to get started!
In this Document
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.
Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit oracle.com. пїЅ Oracle | Contact and Chat | Support | Communities | Connect with us | | | | Legal Notices | Terms of Use
Источник
ORA-12008: error in materialized view refresh path
I am a newbie DBA and while doing daily health check found the below errors.
ORA-12012: error on auto execute of job 576872
ORA-12008: error in materialized view refresh path
ORA-00980: synonym translation is no longer valid
ORA-06512: at «SYS.DBMS_SNAPSHOT», line 2256
ORA-06512: at «SYS.DBMS_SNAPSHOT», line 2462
ORA-06512: at «SYS.DBMS_IREFRESH», line 685
ORA-06512: at «SYS.DBMS_REFRESH», line 195
ORA-06512: at line 1
now I can only understand that error occurred in some materialized view refresh job.
Any help to dig down the issue and with some solution for the same?
Answers
I think you have a problem with a synonym:
ORA-00980: synonym translation is no longer valid Cause: A synonym did not translate to a legal target object. This could happen for one of the following reasons:
1. The target schema does not exist.
2. The target object does not exist.
3. The synonym specifies an incorrect database link.
4. The synonym is not versioned but specifies a versioned target object.
Action: Change the synonym definition so that the synonym points at a legal target object.
thanks a bunch . Any help in finding in which job and in which mview it is located and has failed?
DB version: Oracle 10.2.0.5
OS:SUn solaris 5.10
You could check the alert log for starters (but it looks like you did already).
Also, since you have the job identifier, you could query DBA_JOBS or ALL_JOBS
here’s the query from DBA_JOBS
SQL> select JOB,BROKEN,FAILURES,SCHEMA_USER,LAST_DATE,LAST_SEC,INTERVAL from DBA_JOBS where JOB=576872;
JOB BROKEN FAILURES SCHEMA_USER LAST_DATE LAST_SEC INTERVAL
576872 N 14 APOLLO 02-JUL-14 18:20:14 SYSDATE + 1
I think i am asking too much, but it would be great if you could also help me out finding the failed materialized view refresh name. please
I found the owner and the related mview also.
OWNER MVIEW_NAME LAST_REFRESH
APOLLO MV_APOLLO_RES 08-JUL-14
APOLLO MV_BSE3 08-JUL-14
APOLLO MV_BSE2 08-JUL-14
APOLLO MV_MIN 08-JUL-14
APOLLO MV_MINAPN 08-JUL-14
APOLLO MV_BSE 08-JUL-14
APOLLO MV_NODIR 08-JUL-14
want to know what caused the problem and want to dig down to the root cause now.
Could be something got dropped, could be something changed in a manner different than you expected. I’ve seen the latter in obscure circumstances like a procedure is in use, so it is not flushed from the library cache when someone tries to recompile it (or even flush the cache), and some other procedure is recompiled and expects it to have changed. Or something like that, I get these things backwards sometimes. The key to understanding a particular situation is to understand everything that can invalidate a synonym.
>want to know what caused the problem
somebody changed something.
software does not spontaneously break.
BTW, all changed data can be found using DBMS_LOGMNR
or you could ask who did DDL that broke this job.
Источник
DBMS_REDEFINITION package throwing error ORA-12008 & ORA-01410: invalid ROWID (Doc ID 1572193.1)
Last updated on APRIL 09, 2020
Applies to:
Symptoms
Trying to partition a table with a LOB column using the DBMS_REDEFINITION package and getting the below error message
exec dbms_redefinition.start_redef_table( uname => ‘TEST’, orig_table => ‘TAB1’, int_table => ‘INTERIM_PARTITION’, options_flag => dbms_redefinition.cons_use_pk );
BEGIN dbms_redefinition.start_redef_table( uname => ‘TEST’, orig_table => ‘TAB1’, int_table => ‘INTERIM_PARTITION’, options_flag => dbms_redefinition.cons_use_pk ); END;
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-01410: invalid ROWID
ORA-06512: at «SYS.DBMS_REDEFINITION», line 50
ORA-06512: at «SYS.DBMS_REDEFINITION», line 1343
ORA-06512: at line 1
Changes
Tablespace has been created with large extent size 1025 M( > 1 GB )
Cause
To view full details, sign in with your My Oracle Support account.
Don’t have a My Oracle Support account? Click to get started!
In this Document
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.
Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit oracle.com. пїЅ Oracle | Contact and Chat | Support | Communities | Connect with us | | | | Legal Notices | Terms of Use
Источник
Troubleshooting Materialized View (MV) Problems in PeopleSoft Reporting.
Step1 — Notification: Email will notify PeopleSoft DBA
team on Materialized View issue:
Sample
Email to PeopleSoft DBA’s:
Materialized
Views Refreshes Out of Date as of Thu Sep 10 03:22:03 EDT 2009 on psrptprd
SQL>
select Name, owner, refresh_mode,
2 to_char(last_refresh, ‘dd-MON-yyyy hh:mi PM’)
last_refresh,
3 to_char(Start_with,’dd-MON-yyyy hh:mi PM’)
next_refresh
4 from all_snapshots
5 where start_with < sysdate — 2/24;
NAME OWNER REFRESH_MODE LAST_REFRESH NEXT_REFRESH
—————————— —————
————— ————————— —————————
MV_PS_PROJ_RESOURCE_PART MVIEW_FIN PERIODIC 09-SEP-2009 11:58 PM 10-SEP-2009 12:58 AM
Step2 – Determining
status of MV: Check status of job to see if it
is broken or failed. If failed column below is > 0 then go to step 3
otherwise if failed column is = 0 and job is not broken go to step 4.
Example:
Determining status of MV_PS_PROJ_RESOURCE_PART
SELECT
SUBSTR(job,1,6) «Job»,
SUBSTR(log_user,1,5) «User»,
SUBSTR(schema_user,1,5) «Schema»,
SUBSTR(To_CHAR(last_date, ‘DD.MM.YYY
HH24:MI’),1,16) «Last Date»,
SUBSTR(To_CHAR(next_date, ‘DD.MM.YYY
HH24:MI’),1,16) «Next Date»,
SUBSTR(broken,1,2) «B»,
SUBSTR(failures,1,8) «Failed»,
SUBSTR(what,1,100) «Refresh»
FROM DBA_JOBS
Where what like ‘%PS_PROJ_RESOURCE%’;
If job is not broken (B=N)
or has not failed (Failed=0). No further action required by DBA except to
check database performance and health — job is still running, go to step 4.
Job User Schema Last Date Next Date B
—— ————— ————— —————
————— —
Failed
———
Refresh
—————————————————————————-
3250 MVIEW MVIEW 10.09.009 05:57 10.09.009 06:57 N
0
dbms_refresh.refresh(‘»MVIEW_FIN».»MV_PS_PROJ_RESOURCE_PART»‘);
If job is broken (B=Y) or has failed (Failed>0). Check database alert log to
investigate underlying cause. An attempt to refresh the MV may provide an
Oracle error. Go to step 3.
Job User Schema Last Date Next Date B
—— ————— ————— —————
————— —
Failed
———
Refresh
—————————————————————————-
3250 MVIEW MVIEW 10.09.009 05:57 10.09.009 06:57 Y
1
dbms_refresh.refresh(‘»MVIEW_FIN».»MV_PS_PROJ_RESOURCE_PART»‘);
Step3 – MV failed: If job fails it will try multiple times before setting the job status
to broken (N). Depending on the state of
the job error when you first encounter it you may be require to break the job
manually if it hasn’t been broken already and kill related session. Ensure that you allow sessions to rollback
completely otherwise a full refresh may hang.
Example
1: Check on job status for MV_PS_PROJ_RESOURCE_PART.
SQL> SELECT a.job, a.sid, what,
TO_CHAR(SYSDATE, ‘mm/dd/yyyy hh24:mi:ss’) now,
TO_CHAR(a.this_date, ‘mm/dd/yyyy hh24:mi:ss’)
this
FROM
dba_jobs_running a, dba_jobs b
WHERE
a.job = b.job and a.job=3250;
no rows selected
(no sessions to kill).
Example 2: Active session — kill session and monitor
rollback to completion.
SELECT a.sid, a.serial#, a.username, b.xidusn,
b.used_urec, b.used_ublk
FROM v$session a, v$transaction b
WHERE a.saddr = b.ses_addr and a.sid=71;
SQL> SELECT a.sid, a.serial#, a.username,
b.xidusn, b.used_urec, b.used_ublk
2 FROM v$session a, v$transaction b
3 WHERE a.saddr = b.ses_addr and a.sid=71;
SID SERIAL# USERNAME XIDUSN USED_UREC
———- ———- ——————————
———- ———-
USED_UBLK
———-
71 6720 MVIEW_FIN 119 159383334
4699036
The result of the about query should return “no
rows selected” for a success rollback of the transaction for the session killed.
Next test whether a fast refresh is possible. Test fast refresh of MV_PS_PROJ_RESOURCE_PART
to see if you encounter invalid ROWID error.
If you encounter «ORA-12008:
error in materialized view refresh path» then a full refresh is required.
Example:
Test for fast fresh of MV_PS_PROJ_RESOURCE_PART.
SQL> conn mview_fin/xxxxxxx@psrptprd
Connected.
SQL> EXECUTE
DBMS_MVIEW.REFRESH(‘MV_PS_PROJ_RESOURCE_PART’,’F’);
BEGIN DBMS_MVIEW.REFRESH(‘MV_PS_PROJ_RESOURCE_PART’,’F’);
END;
*
ERROR at line 1:
ORA-12008: error in materialized view refresh
path
ORA-01410: invalid ROWID
ORA-02063: preceding line from FINANCE
ORA-06512: at «SYS.DBMS_SNAPSHOT»,
line 2545
ORA-06512: at «SYS.DBMS_SNAPSHOT», line
2751
ORA-06512: at «SYS.DBMS_SNAPSHOT»,
line 2720
ORA-06512: at line 1
Finally check alert log for ora-600 errors that indicate a shared memory
errors and flush the shared pool three times to clear this. Run full refresh and notify Greg Noble; Tripurari
Sinha; Gareth Hale; Manish Verma. Full
Refresh usually takes 5 and 1/2 hours for MV_PS_PROJ_RESOURCE_PART.
Example:
SQL> conn mview_fin/xxxxxxx@psrptprd
Connected.
SQL> EXECUTE
DBMS_MVIEW.REFRESH(‘MV_PS_PROJ_RESOURCE_PART’,’COMPLETE’,atomic_refresh=>FALSE);
Ensure that statistics is gathered for the
complete mview refresh.
Example:
exec dbms_stats.gather_table_stats(‘MVIEW_FIN’,
‘MV_PS_PROJ_RESOURCE_PART’, CASCADE=>TRUE);
Step 4 – No major
action required, MV is running and has not failed or broken: Check status of MV job again to ensure job has not broken and is
stilling running. Also, check the general
health of master and reporting databases
(by checking the alert log, MV log and undo tablepaces and database performance
(focusing on the shared_pool) to ensure that there are no general issues with
the databases such lack of database memory or lack of space on key tablespaces
or issues with the MV log.
Example: MV_PS_PROJ_RESOURCE_PART job has not
failed or broken.
SELECT
SUBSTR(job,1,6) «Job»,
SUBSTR(log_user,1,5) «User»,
SUBSTR(schema_user,1,5) «Schema»,
SUBSTR(To_CHAR(last_date, ‘DD.MM.YYY
HH24:MI’),1,16) «Last Date»,
SUBSTR(To_CHAR(next_date, ‘DD.MM.YYY
HH24:MI’),1,16) «Next Date»,
SUBSTR(broken,1,2) «B»,
SUBSTR(failures,1,8) «Failed»,
SUBSTR(what,1,100) «Refresh»
FROM DBA_JOBS
Where what like ‘%PS_PROJ_RESOURCE%’;
Job
User Schema Last Date Next Date B
—— ————— —————
————— ————— —
Failed
———
Refresh
———————————————————————————
3250
MVIEW MVIEW 25.09.009 02:24 25.09.009 03:24 N
0
dbms_refresh.refresh(‘»MVIEW_FIN».»MV_PS_PROJ_RESOURCE_PART»‘);
Example: MV_PS_PROJ_RESOURCE_PART job is still
running.
oracle >SQL> SELECT a.job, a.sid, what,
TO_CHAR(SYSDATE, ‘mm/dd/yyyy hh24:mi:ss’) now,
TO_CHAR(a.this_date, ‘mm/dd/yyyy hh24:mi:ss’)
this
FROM
dba_jobs_running a, dba_jobs b
WHERE
a.job = b.job and a.job=3250;
JOB SID
———- ———-
WHAT
———————————————————————————
NOW THIS
——————- ——————-
3250 123
dbms_refresh.refresh(‘»MVIEW_FIN».»MV_PS_CA_ACCTG_LN_PC_PART»‘);
09/25/2009 06:35:52 09/25/2009 03:24:17
Appendix
For
a complete refresh (using dbms_mview.refresh) sys.snap$ and sys.mlog$ are
updated to reflect the time of the refresh. The materialized base view is
deleted and all rows selected from the master table are inserted into the
snapshot base table and then sys.slog$ is updated to reflect the time of the
refresh.
For
a fast refresh, the steps are sys.snap$ and sys.mlog$ are updated to reflect
the time of the refresh. Rows in the
materialized base view are deleted and all rows selected from the master table
are inserted into the snapshot base table and then sys.slog$ is updated to
reflect the time of the refresh. Rows
that are not needed anymore for a refresh by any materialized view are deleted
from the materialized view log (e.g. SYSFS.MLOG$_PS_PROJ_RESOURCE).
A
long running refresh will have a PC_REPORTS_USER session in the F90xxx or
H90xxx databases and a long running SQL operation for that session normally
gives an indication of how long the refresh will take to complete. For a full refresh there may be an automatic
fast refresh after the full refresh that gets the materialized view up to the
current time.
See below for examples of fast and complete
refresh for MV_PS_PROJ_RESOURCE_PART.
Consider using atomic_refresh=FALSE option for complete refresh – the
default for Oracle 11g is atomic_refresh=TRUE (DELETE instead of a TRUNCATE).
Example:
EXECUTE
DBMS_MVIEW.REFRESH(‘MV_PS_PROJ_RESOURCE_PART’,’F’); — fast refresh
EXECUTE
DBMS_MVIEW.REFRESH(‘MV_PS_PROJ_RESOURCE_PART’,’COMPLETE’,atomic_refresh=>FALSE);
— complete refresh by truncating MV.
Use
DBMS_JOB and ALTER MATERIALIZED VIEW statements to manage and schedule MV jobs.
Example: MV_PS_PROJ_RESOURCE_PART is
scheduled to run every hour.
exec dbms_job.broken(3250, FALSE);
ALTER MATERIALIZED VIEW
MVIEW_FIN.MV_PS_PROJ_RESOURCE_PART
REFRESH
NEXT sysdate + 1/24;
Ensure that
statistics is gathered for complete refresh.
Example:
exec dbms_stats.gather_table_stats(‘MVIEW_FIN’,
‘MV_PS_PROJ_RESOURCE_PART’, CASCADE=>TRUE);
Thanks Thomas.
How to Create a New Materialized View.
In
F8XPRD
create
materialized view log on sysfs.PS_XXXXX tablespace
with
rowid, sequence excluding new values;
grant
select on sysfs.PS_XXXXX to
pc_reports_user;
grant
select on sysfs.MLOG$_PS_XXXXX to pc_reports_user;
in
PSRPTPRD as Mview_F8X
DROP
MATERIALIZED VIEW MVIEW_F8X.MV_PS_XXXXX;
CREATE
MATERIALIZED VIEW MVIEW_F8X.MV_PS_XXXXX
TABLESPACE
MVIEW_F8X
NOCACHE
NOLOGGING
COMPRESS
NOPARALLEL
BUILD
IMMEDIATE
USING
INDEX
REFRESH
FAST
START
WITH sysdate
NEXT
SYSDATE + 1/24
WITH
ROWID
AS
SELECT
* FROM «SYSFS».»PS_XXXX»@F8X «PS_XXXXX»;
CREATE
INDEX MView_F8X.MV_PS_XXXXX_IX1 ON MView_F8X.MV_PS_XXXXX
(COL1,
COL2, COL3)
LOGGING
TABLESPACE
PSINDEX
NOPARALLEL;
ALTER
TABLE MVIEW_F8X.MV_PS_XXXXX ADD (
CONSTRAINT PS_XXXXX_PK
UNIQUE (COL1, COL2, COL3)
DEFERRABLE INITIALLY IMMEDIATE);
GRANT
SELECT ON MVIEW_F8X.MV_PS_XXXXX TO SYSFS;
DROP
PUBLIC SYNONYM PS_XXXXX;
CREATE
PUBLIC SYNONYM PS_XXXXX FOR MVIEW_F8X.MV_PS_XXXXX;
Materialized View Monitoring
The
check is simply to ensure that the Materialized view has been refreshed within
2 hours of its scheduled time by checking all_snapshots.
select Name, owner, refresh_mode,
to_char(last_refresh, ‘dd-MON-yyyy hh:mi PM’) last_refresh,
to_char(Start_with,’dd-MON-yyyy hh:mi PM’) next_refresh
from all_snapshots
where start_with < sysdate — 2/24;
Предварительно усилив параллельность выполнения — как несложный и действенный метод ускорения FULL SCAN-ов, HASH JOIN-ов, сокращения direct path temp read / write- операций и потребления TEMP пространства, соответственно:
12.1.0.2@ SQL> alter session force parallel QUERY parallel 32; Session altered
, получаем:
SQL> exec dbms_mview.refresh('"BI"."MV_EXPENSES_DETAIL"', atomic_refresh => TRUE, parallelism => 32) -- parallelism => 32 - особой роли не играет, скорее как индикатор
PL/SQL procedure successfully completed
Executed in 140,578 seconds
— стартовый рез-т, SQL MONITOR которого показывает много direct path read на MAT_VIEW ACCESS FULL (*) + всё ещё заметное, несмотря на параллельное выполнение, потребление TEMP (11G) с сопутствующими direct path temp — операциями при выполнении вышележащего HASH JOIN (**):
SQL Monitoring Report SQL Text ------------------------------ INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "BI"."MV_EXPENSES_DETAIL" SELECT "from$_subquery$_001"..."from$_subquery$_002"..."from$_subquery$_003"... -- *** Global Information ------------------------------ ... SQL ID : ad2nyg9d5skgg ... Duration : 111s ... Global Stats ============================================================================================================================================== | Elapsed | Cpu | IO | Application | Concurrency | Cluster | PL/SQL | Other | Buffer | Read | Read | Write | Write | Cell | | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Time(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes | Offload | ============================================================================================================================================== | 1008 | 226 | 647 | 0.00 | 0.01 | 0.00 | 0.00 | 135 | 4M | 135K | 27GB | 122K | 12GB | -31.58% | -- Read 27GB / Write 12GB ============================================================================================================================================== Parallel Execution Details (DOP=16 , Servers Allocated=32) SQL Plan Monitoring Details (Plan Hash Value=3848627193) ============================================================================================================================================================================================================== | Id | Operation | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Temp | Activity | Activity Detail | | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | (Max) | (Max) | (%) | (# samples) | ============================================================================================================================================================================================================== | 0 | INSERT STATEMENT | | | 28 | +84 | 1 | 0 | | | | | | | 0.52 | Cpu (5) | | 1 | LOAD TABLE CONVENTIONAL | | | 24 | +88 | 1 | 0 | 3 | 24576 | | | | | 2.39 | Cpu (23) | | 2 | PX COORDINATOR | | | 24 | +88 | 33 | 1M | | | | | | | 0.10 | Cpu (1) | | 3 | PX SEND QC (RANDOM) | 7594 | 37341 | 24 | +88 | 16 | 1M | | | | | | | 0.21 | Cpu (2) | | 4 | VIEW | 7594 | 37341 | 24 | +88 | 16 | 1M | | | | | | | 0.31 | Cpu (3) | | 5 | WINDOW SORT | 7594 | 37341 | 28 | +84 | 16 | 1M | 20587 | 2GB | 32047 | 2GB | 1G | 900M | 8.72 | Cpu (17) | | | | | | | | | | | | | | | | | direct path read temp (39) | | | | | | | | | | | | | | | | | direct path write temp (28) | | 6 | PX RECEIVE | 7594 | 37340 | 3 | +84 | 16 | 1M | | | | | | | 0.10 | Cpu (1) | | 7 | PX SEND HASH | 7594 | 37340 | 2 | +83 | 16 | 1M | | | | | | | 0.21 | Cpu (2) | | 8 | VIEW | 7594 | 37340 | 2 | +83 | 16 | 1M | | | | | | | | | | 9 | VIEW | | | 2 | +83 | 16 | 1M | | | | | | | 0.10 | Cpu (1) | | 10 | SORT GROUP BY ROLLUP COLLECTOR | 7594 | 37340 | 17 | +68 | 16 | 1M | | | | | 806M | | 0.93 | Cpu (9) | | 11 | PX RECEIVE | 7594 | 37340 | 17 | +68 | 16 | 1M | | | | | | | 0.10 | Cpu (1) | | 12 | PX SEND HASH | 7594 | 37340 | 17 | +68 | 16 | 1M | | | | | | | 0.10 | Cpu (1) | | 13 | SORT GROUP BY ROLLUP DISTRIBUTOR | 7594 | 37340 | 28 | +57 | 16 | 1M | 12682 | 667MB | 3333 | 667MB | 250M | 700M | 2.18 | Cpu (9) | | | | | | | | | | | | | | | | | direct path read temp (12) | | 14 | HASH GROUP BY | 7594 | 37340 | 11 | +58 | 16 | 1M | | | | | 22M | | | | | 15 | HASH JOIN OUTER | 7594 | 37339 | 19 | +50 | 16 | 1M | 4955 | 581MB | 4955 | 581MB | 586M | 650M | 0.93 | Cpu (3) | | | | | | | | | | | | | | | | | direct path read temp (4) | | | | | | | | | | | | | | | | | direct path write temp (2) | | 16 | PX RECEIVE | 7587 | 37053 | 18 | +50 | 16 | 1M | | | | | | | | | | 17 | PX SEND HASH | 7587 | 37053 | 7 | +50 | 16 | 1M | | | | | | | | | | 18 | HASH JOIN | 7587 | 37053 | 52 | +5 | 16 | 1M | | | | | 7M | | 0.10 | Cpu (1) | | 19 | PX RECEIVE | 1 | 2 | 1 | +5 | 16 | 16 | | | | | | | | | | 20 | PX SEND BROADCAST | 1 | 2 | 1 | +2 | 16 | 16 | | | | | | | | | | 21 | PX BLOCK ITERATOR | 1 | 2 | 1 | +2 | 16 | 1 | | | | | | | | | | 22 | MAT_VIEW ACCESS FULL | 1 | 2 | 1 | +2 | 1 | 1 | | | | | | | | | | 23 | HASH JOIN | 83455 | 37051 | 52 | +5 | 16 | 11M | | | | | 23M | | 0.21 | Cpu (2) | | 24 | MAT_VIEW ACCESS FULL | 3557 | 2 | 1 | +5 | 16 | 56912 | | | | | | | | | | 25 | HASH JOIN | 83708 | 37049 | 52 | +5 | 16 | 11M | 81360 | 9GB | 81360 | 9GB | 8G | 11G | 45.07 | Cpu (194) | -- ** | | | | | | | | | | | | | | | | direct path read temp (189) | | | | | | | | | | | | | | | | | direct path write temp (51) | | 26 | JOIN FILTER CREATE | 10911 | 36673 | 36 | +5 | 16 | 17M | | | | | | | 0.31 | Cpu (3) | | 27 | PX RECEIVE | 10911 | 36673 | 36 | +5 | 16 | 17M | | | | | | | 0.21 | Cpu (2) | | 28 | PX SEND BROADCAST | 10911 | 36673 | 48 | +2 | 16 | 17M | | | | | | | 2.08 | Cpu (20) | | 29 | HASH JOIN | 10911 | 36673 | 48 | +2 | 16 | 1M | | | | | 21M | | | | | 30 | PX RECEIVE | 150 | 5 | 4 | +2 | 16 | 2400 | | | | | | | | | | 31 | PX SEND BROADCAST | 150 | 5 | 1 | +5 | 16 | 2400 | | | | | | | | | | 32 | MERGE JOIN CARTESIAN | 150 | 5 | 1 | +5 | 16 | 150 | | | | | | | | | | 33 | PX BLOCK ITERATOR | 6 | 2 | 1 | +5 | 16 | 6 | | | | | | | | | | 34 | TABLE ACCESS FULL | 6 | 2 | 1 | +5 | 43 | 6 | | | | | | | | | | 35 | BUFFER SORT | 25 | 3 | 1 | +5 | 6 | 150 | | | | | | | | | | 36 | MAT_VIEW ACCESS FULL | 25 | 2 | 1 | +5 | 1 | 25 | | | | | | | | | | 37 | PX BLOCK ITERATOR | 5M | 36668 | 48 | +2 | 16 | 6M | | | | | | | | | | 38 | MAT_VIEW ACCESS FULL | 5M | 36668 | 50 | +0 | 218 | 6M | 15427 | 15GB | | | | | 35.10 | Cpu (14) | | | | | | | | | | | | | | | | | direct path read (324) | -- * | 39 | JOIN FILTER USE | 5M | 375 | 3 | +50 | 16 | 876K | | | | | | | | | | 40 | PX BLOCK ITERATOR | 5M | 375 | 3 | +50 | 16 | 876K | | | | | | | | | | 41 | MAT_VIEW ACCESS FULL | 5M | 375 | 11 | +42 | 205 | 876K | | | | | | | | | | 42 | PX RECEIVE | 160K | 286 | 10 | +58 | 16 | 160K | | | | | | | | | | 43 | PX SEND HASH | 160K | 286 | 12 | +56 | 16 | 160K | | | | | | | | | | 44 | PX BLOCK ITERATOR | 160K | 286 | 12 | +56 | 16 | 160K | | | | | | | | | | 45 | MAT_VIEW ACCESS FULL | 160K | 286 | 12 | +56 | 201 | 160K | | | | | | | | | ==============================================================================================================================================================================================================
— кроме того, в первый раз я наблюдал столь причудливое отличие используемого системой при наполнении MATVIEW (***):
INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "BI"."MV_EXPENSES_DETAIL" SELECT "from$_subquery$_001"..."from$_subquery$_002"..."from$_subquery$_003"...
от запроса, использованного при создании MATVIEW, где, разумеется конструкций типа «from$_subquery$_001» не использовалось — что похоже на последствия/этап синтаксического разбора by CBO
После помещения проблемного сегмента GROSS_BOOK_REP_STORE в INMEMORY:
SQL> ALTER TABLE BIMATVIEW.GROSS_BOOK_REP_STORE INMEMORY PRIORITY HIGH;
Table altered
Executed in 108,922 seconds -- иногда неожиданно долго, учитывая, что сразу по завершению этой операции сегмент в GV$IM_SEGMENTS не появляется!
SQL> select * from gv$im_segments;
INST_ID OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME INMEMORY_SIZE BYTES BYTES_NOT_POPULATED POPULATE_STATUS INMEMORY_PRIORITY INMEMORY_DISTRIBUTE INMEMORY_DUPLICATE INMEMORY_COMPRESSION CON_ID
------- ----------- --------------------- --------------- ------------------ ---------------- ------------- ---------- ------------------- --------------- ----------------- ------------------- ------------------ -------------------- ----------
1 BIMATVIEW GROSS_BOOK_REP_STORE TABLE BIMATVIEW 2921070592 1593529139 8631820288 COMPLETED HIGH AUTO NO DUPLICATE FOR QUERY LOW 0
2 BIMATVIEW GROSS_BOOK_REP_STORE TABLE BIMATVIEW 3510697984 1593529139 7303471104 COMPLETED HIGH AUTO NO DUPLICATE FOR QUERY LOW 0
SQL> exec dbms_mview.refresh('"BI"."MV_EXPENSES_DETAIL"',parallelism => 32)
PL/SQL procedure successfully completed
Executed in 89,578 seconds
Global Information
------------------------------
...
SQL ID : ad2nyg9d5skgg
...
Duration : 60s
...
DOP Downgrade : 50% -- *
Global Stats
========================================================================================================
| Elapsed | Cpu | IO | Cluster | PL/SQL | Buffer | Read | Read | Write | Write | Cell |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Time(s) | Gets | Reqs | Bytes | Reqs | Bytes | Offload |
========================================================================================================
| 230 | 155 | 75 | 0.00 | 0.00 | 3M | 58424 | 5GB | 54454 | 5GB | -49.25% | -- Read 5GB / Write 5GB
========================================================================================================
Parallel Execution Details (DOP=8 , Servers Requested=32 , Servers Allocated=16)
...
SQL Plan Monitoring Details (Plan Hash Value=3848627193)
=======================================================================================================================================================================================================
| Id | Operation | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Temp | Activity | Activity Detail |
| | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | (Max) | (Max) | (%) | (# samples) |
=======================================================================================================================================================================================================
| 0 | INSERT STATEMENT | | | 31 | +30 | 1 | 0 | | | | | | | | |
| 1 | LOAD TABLE CONVENTIONAL | | | 21 | +40 | 1 | 0 | | | | | | | | |
| 2 | PX COORDINATOR | | | 21 | +40 | 17 | 1M | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | 7594 | 5167 | 22 | +39 | 8 | 1M | | | | | | | | |
| 4 | VIEW | 7594 | 5167 | 22 | +39 | 8 | 1M | | | | | | | | |
| 5 | WINDOW SORT | 7594 | 5167 | 32 | +29 | 8 | 1M | 22534 | 2GB | 32887 | 2GB | 814M | 924M | | |
| 6 | PX RECEIVE | 7594 | 5166 | 10 | +29 | 8 | 1M | | | | | | | | |
| 7 | PX SEND HASH | 7594 | 5166 | 9 | +28 | 8 | 1M | | | | | | | | |
| 8 | VIEW | 7594 | 5166 | 9 | +28 | 8 | 1M | | | | | | | | |
| 9 | VIEW | | | 9 | +28 | 8 | 1M | | | | | | | | |
| 10 | SORT GROUP BY ROLLUP COLLECTOR | 7594 | 5166 | 15 | +22 | 8 | 1M | 8923 | 805MB | 4061 | 805MB | 792M | 427M | | |
| 11 | PX RECEIVE | 7594 | 5166 | 7 | +22 | 8 | 1M | | | | | | | | |
| 12 | PX SEND HASH | 7594 | 5166 | 7 | +22 | 8 | 1M | | | | | | | | |
| 13 | SORT GROUP BY ROLLUP DISTRIBUTOR | 7594 | 5166 | 15 | +14 | 8 | 1M | 12840 | 676MB | 3379 | 676MB | 240M | 709M | | |
| 14 | HASH GROUP BY | 7594 | 5166 | 9 | +14 | 8 | 1M | | | | | 11M | | | |
| 15 | HASH JOIN OUTER | 7594 | 5165 | 13 | +10 | 8 | 1M | 5015 | 588MB | 5015 | 588MB | 326M | 657M | | |
| 16 | PX RECEIVE | 7587 | 4879 | 11 | +10 | 8 | 1M | | | | | | | | |
| 17 | PX SEND HASH | 7587 | 4879 | 11 | +10 | 8 | 1M | | | | | | | | |
| 18 | HASH JOIN | 7587 | 4879 | 19 | +2 | 8 | 1M | | | | | 4M | | | |
| 19 | PX RECEIVE | 1 | 2 | 1 | +2 | 8 | 8 | | | | | | | | |
| 20 | PX SEND BROADCAST | 1 | 2 | 1 | +2 | 8 | 8 | | | | | | | | |
| 21 | PX BLOCK ITERATOR | 1 | 2 | 1 | +2 | 8 | 1 | | | | | | | | |
| 22 | MAT_VIEW ACCESS FULL | 1 | 2 | 1 | +2 | 1 | 1 | | | | | | | | |
| 23 | HASH JOIN | 83455 | 4877 | 19 | +2 | 8 | 11M | | | | | 12M | | | |
| 24 | MAT_VIEW ACCESS FULL | 3557 | 2 | 1 | +2 | 8 | 28456 | | | | | | | | |
| 25 | HASH JOIN | 83708 | 4875 | 20 | +1 | 8 | 11M | 9112 | 1GB | 9112 | 1GB | 6G | 1G | | | -- ***
| 26 | JOIN FILTER CREATE | 10911 | 4499 | 7 | +2 | 8 | 9M | | | | | | | | |
| 27 | PX RECEIVE | 10911 | 4499 | 8 | +1 | 8 | 9M | | | | | | | | |
| 28 | PX SEND BROADCAST | 10911 | 4499 | 8 | +1 | 8 | 9M | | | | | | | | |
| 29 | HASH JOIN | 10911 | 4499 | 7 | +2 | 8 | 1M | | | | | 10M | | | |
| 30 | PX RECEIVE | 150 | 5 | 1 | +2 | 8 | 1200 | | | | | | | | |
| 31 | PX SEND BROADCAST | 150 | 5 | 1 | +2 | 8 | 1200 | | | | | | | | |
| 32 | MERGE JOIN CARTESIAN | 150 | 5 | 1 | +2 | 8 | 150 | | | | | | | | |
| 33 | PX BLOCK ITERATOR | 6 | 2 | 1 | +2 | 8 | 6 | | | | | | | | |
| 34 | TABLE ACCESS FULL | 6 | 2 | 1 | +2 | 43 | 6 | | | | | | | | |
| 35 | BUFFER SORT | 25 | 3 | 1 | +2 | 6 | 150 | | | | | | | | |
| 36 | MAT_VIEW ACCESS FULL | 25 | 2 | 1 | +2 | 1 | 25 | | | | | | | | |
| 37 | PX BLOCK ITERATOR | 5M | 4493 | 7 | +2 | 8 | 6M | | | | | | | | |
| 38 | MAT_VIEW ACCESS INMEMORY FULL | 5M | 4493 | 8 | +1 | 59 | 6M | | | | | | | | | -- **
| 39 | JOIN FILTER USE | 5M | 375 | 11 | +10 | 8 | 876K | | | | | | | | |
| 40 | PX BLOCK ITERATOR | 5M | 375 | 11 | +10 | 8 | 876K | | | | | | | | |
| 41 | MAT_VIEW ACCESS FULL | 5M | 375 | 13 | +8 | 155 | 876K | | | | | | | | |
| 42 | PX RECEIVE | 160K | 286 | 7 | +14 | 8 | 160K | | | | | | | | |
| 43 | PX SEND HASH | 160K | 286 | 1 | +20 | 8 | 160K | | | | | | | | |
| 44 | PX BLOCK ITERATOR | 160K | 286 | 1 | +20 | 8 | 160K | | | | | | | | |
| 45 | MAT_VIEW ACCESS FULL | 160K | 286 | 1 | +20 | 128 | 160K | | | | | | | | |
=======================================================================================================================================================================================================
при неизменном Plan Hash Value=3848627193 (для которого операции MAT_VIEW ACCESS FULL / ACCESS INMEMORY FULL, очевидно, не различаются), несмотря на DOP Downgrade (*), запрос выполняется почти вдвое быстрее за счёт:
— скорости MAT_VIEW ACCESS INMEMORY FULL против MAT_VIEW ACCESS FULL, что ожидаемо (**)
— сокращения на порядок потребления MEMORY(PGA) и TEMP и соответствующих операций при HASH JOIN, что логично неожиданно (***)
Новая полезная фича 12c Out-of-Place (OOP) Matview Refresh, предназначенная для ускорения процесса — может не сработать:
SQL> exec dbms_mview.refresh('"BI"."MV_EXPENSES_DETAIL"', method => 'C', atomic_refresh => false, out_of_place => TRUE)
begin dbms_mview.refresh('"BI"."MV_EXPENSES_DETAIL"', method => 'C', atomic_refresh => false, out_of_place => TRUE); end;
ORA-12008: error in materialized view refresh path
ORA-00900: invalid SQL statement
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2821
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3058
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3017
ORA-06512: at line 2
SQL> select QUERY_LEN from dba_mviews where mview_name = 'MV_EXPENSES_DETAIL';
QUERY_LEN
---------------------------------------
12954
— по причине, напоминающей Bug 13146719 Materialized View defining query length > 64k raises ORA-32411, даже несмотря на незапредельную длину запроса
Решается аналогично, через пересоздание Materialized View в форме SELECT * FROM VIEW, после чего:
SQL> alter session force parallel QUERY parallel 32;
Session altered
SQL> exec dbms_mview.refresh('"BI"."XXX_MV_EXPENSES_DETAIL"', method => 'C', atomic_refresh => false, out_of_place => TRUE, parallelism => 32) -- /* MV_REFRESH (ITB) */INSERT INTO "BI"."RV$97769" ... ****
PL/SQL procedure successfully completed
Executed in 75,75 seconds
— получается небыстро, пока (как в рассматриваемой системе) не установлен патч Bug 20933264 — Out-of-Place (OOP) Refresh Not Using Direct Load When Populating RV$xxxxx Table (Doc ID 20933264.8) (****)
До установки патча развить успех можно, формально отказавшись от атомарного подхода (но сохранив почти непрерывную доступность MatView), с помощью технологии «двойных/дублирующих матвью» в виде prebuilt partitioned table with a single partition, либо другими методами, в этом случае:
SQL> exec dbms_mview.refresh('"BI"."MV_EXPENSES_DETAIL"', method => 'C', atomic_refresh => FALSE, out_of_place => FALSE, parallelism => 32)
PL/SQL procedure successfully completed
Executed in 18,109 seconds -- оч.хорошо!
SQL Monitor shows
SQL Text
------------------------------
INSERT /*+ BYPASS_RECURSIVE_CHECK APPEND */ INTO "BI"."MV_EXPENSES_DETAIL" SELECT "from$_subquery$_001"... -- запрос столь же необычен
Global Information
------------------------------
...
SQL ID : gfrtdrwchhh0t
...
Duration : 10s -- лучший рез-т!!!
...
DOP Downgrade : 50%
Global Stats
======================================================================================================================
| Elapsed | Cpu | IO | Concurrency | Cluster | PL/SQL | Buffer | Read | Read | Write | Write | Cell |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Time(s) | Gets | Reqs | Bytes | Reqs | Bytes | Offload |
======================================================================================================================
| 156 | 93 | 62 | 0.19 | 0.07 | 0.01 | 1M | 20661 | 2GB | 35949 | 3GB | -61.29% |
======================================================================================================================
Parallel Execution Details (DOP=16 , Servers Requested=64 , Servers Allocated=32)
SQL Plan Monitoring Details (Plan Hash Value=2964677505)
==================================================================================================================================================================================================
| Id | Operation | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Temp | Activity | Activity Detail |
| | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | (Max) | (Max) | (%) | (# samples) |
==================================================================================================================================================================================================
| 0 | INSERT STATEMENT | | | 1 | +11 | 25 | 48 | | | | | | | | |
| 1 | PX COORDINATOR | | | 1 | +11 | 25 | 48 | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | 8548 | 2589 | 1 | +11 | 12 | 48 | | | | | | | | |
| 3 | LOAD AS SELECT (HIGH WATER MARK BROKERED) | | | 4 | +8 | 12 | 48 | 51 | 408KB | 3886 | 941MB | | | | |
| 4 | OPTIMIZER STATISTICS GATHERING | 8548 | 2589 | 4 | +8 | 12 | 1M | | | | | | | | |
| 5 | VIEW | 8548 | 2589 | 4 | +8 | 12 | 1M | | | | | | | | |
| 6 | WINDOW SORT | 8548 | 2589 | 9 | +3 | 12 | 1M | 20459 | 2GB | 32037 | 2GB | 1G | 895M | | |
| 7 | PX RECEIVE | 8548 | 2588 | 3 | +4 | 12 | 1M | | | | | | | | |
| 8 | PX SEND HASH | 8548 | 2588 | 1 | +3 | 12 | 1M | | | | | | | | |
| 9 | VIEW | 8548 | 2588 | 1 | +3 | 12 | 1M | | | | | | | | |
| 10 | VIEW | | | 1 | +3 | 12 | 1M | | | | | | | | |
| 11 | SORT GROUP BY ROLLUP COLLECTOR | 8548 | 2588 | 3 | +1 | 12 | 1M | | | | | 805M | | | |
| 12 | PX RECEIVE | 8548 | 2588 | 2 | +2 | 12 | 1M | | | | | | | | |
| 13 | PX SEND HASH | 8548 | 2588 | 6 | +1 | 12 | 1M | | | | | | | | |
| 14 | SORT GROUP BY ROLLUP DISTRIBUTOR | 8548 | 2588 | 5 | +2 | 12 | 1M | | | | | 403K | | | |
| 15 | HASH GROUP BY | 8548 | 2588 | 5 | +2 | 12 | 1M | | | | | 17M | | | |
| 16 | HASH JOIN | 8548 | 2587 | 6 | +1 | 12 | 1M | | | | | 10M | | | |
| 17 | TABLE ACCESS FULL | 6 | 2 | 1 | +2 | 12 | 72 | | | | | | | | |
| 18 | HASH JOIN RIGHT OUTER | 3M | 2585 | 5 | +2 | 12 | 5M | | | | | 348M | | | |
| 19 | PX RECEIVE | 160K | 143 | 1 | +2 | 12 | 2M | | | | | | | | |
| 20 | PX SEND BROADCAST | 160K | 143 | 1 | +2 | 12 | 2M | | | | | | | | |
| 21 | PX BLOCK ITERATOR | 160K | 143 | 1 | +2 | 12 | 160K | | | | | | | | |
| 22 | MAT_VIEW ACCESS FULL | 160K | 143 | 1 | +2 | 151 | 160K | | | | | | | | |
| 23 | HASH JOIN | 3M | 2441 | 6 | +1 | 12 | 5M | | | | | 20M | | | |
| 24 | PX RECEIVE | 25 | 2 | 1 | +2 | 12 | 300 | | | | | | | | |
| 25 | PX SEND BROADCAST | 25 | 2 | 1 | +2 | 12 | 300 | | | | | | | | |
| 26 | PX BLOCK ITERATOR | 25 | 2 | 1 | +2 | 12 | 25 | | | | | | | | |
| 27 | MAT_VIEW ACCESS FULL | 25 | 2 | 1 | +2 | 1 | 25 | | | | | | | | |
| 28 | HASH JOIN | 4M | 2439 | 7 | +0 | 12 | 6M | | | | | 831M | | | |
| 29 | PX RECEIVE | 472K | 192 | 3 | +0 | 12 | 6M | | | | | | | | |
| 30 | PX SEND BROADCAST | 472K | 192 | 3 | +0 | 12 | 6M | | | | | | | | |
| 31 | HASH JOIN | 472K | 192 | 1 | +2 | 12 | 505K | | | | | 14M | | | |
| 32 | JOIN FILTER CREATE | 364 | 4 | 1 | +2 | 12 | 4368 | | | | | | | | |
| 33 | PX RECEIVE | 364 | 4 | 1 | +2 | 12 | 4368 | | | | | | | | |
| 34 | PX SEND BROADCAST | 364 | 4 | 1 | +2 | 12 | 4368 | | | | | | | | |
| 35 | HASH JOIN | 364 | 4 | 1 | +2 | 12 | 364 | | | | | 3M | | | |
| 36 | JOIN FILTER CREATE | 1 | 2 | 1 | +2 | 12 | 12 | | | | | | | | |
| 37 | MAT_VIEW ACCESS FULL | 1 | 2 | 1 | +2 | 12 | 12 | | | | | | | | |
| 38 | JOIN FILTER USE | 3557 | 2 | 1 | +2 | 12 | 364 | | | | | | | | |
| 39 | PX BLOCK ITERATOR | 3557 | 2 | 1 | +2 | 12 | 364 | | | | | | | | |
| 40 | MAT_VIEW ACCESS FULL | 3557 | 2 | 1 | +2 | 43 | 364 | | | | | | | | |
| 41 | JOIN FILTER USE | 5M | 187 | 1 | +2 | 12 | 505K | | | | | | | | |
| 42 | PX BLOCK ITERATOR | 5M | 187 | 1 | +2 | 12 | 505K | | | | | | | | |
| 43 | MAT_VIEW ACCESS FULL | 5M | 187 | 3 | +0 | 156 | 505K | | | | | | | | |
| 44 | PX BLOCK ITERATOR | 5M | 2247 | 5 | +2 | 12 | 6M | | | | | | | | |
| 45 | MAT_VIEW ACCESS INMEMORY FULL | 5M | 2247 | 6 | +1 | 59 | 6M | 2 | 16384 | | | | | | |
==================================================================================================================================================================================================
— SQL Monitor в последних 2-х примерах не показал профиля ожиданий (возможно, ввиду недостаточно продолжительности запроса, хотя предпоследний запрос 60s (?), однако имеющиеся строки ASH можно просмотреть любым доступным спобом / скриптом ASH_SQLMON2.SQL:
SQL> @ash_sqlmon2 gfrtdrwchhh0t "" 16777219 ""
LAST_PLSQL PLAN_HASH_VALUE ID PLAN_OPERATION OBJECT_ALIAS PX MAX_PGA_ALLOCATED MAX_TEMP_ALLOCATED ASH_ROWS WAIT_PROFILE
----------- --------------- ---- --------------------------------------------------------------------------------- ------------------------- ---- ----------------- ------------------ ---------- -----------------------------------------------------------------
Main Query 2964677505 0 INSERT STATEMENT
1 PX COORDINATOR
2 PX SEND QC (RANDOM)
3 LOAD AS SELECT (HIGH WATER MARK BROKERED) 2 210255872 900726784 3 ON CPU(3)
4 OPTIMIZER STATISTICS GATHERING 4 210255872 900726784 5 ON CPU(5)
5 VIEW from$_subquery$_001@SEL$1 0 210255872 900726784 1 ON CPU(1)
6 WINDOW SORT 15 1168392192 900726784 72 direct path write temp(30); direct path read temp(27); ON CPU(15)
7 PX RECEIVE
8 PX SEND HASH 0 61640704 0 1 ON CPU(1)
9 VIEW from$_subquery$_002@SEL$2
10 VIEW
11 SORT GROUP BY ROLLUP COLLECTOR 12 2029903872 0 19 ON CPU(19)
12 PX RECEIVE 0 1967697920 0 1 ON CPU(1)
13 PX SEND HASH 4 2029903872 0 5 ON CPU(5)
14 SORT GROUP BY ROLLUP DISTRIBUTOR 1 2029903872 0 2 ON CPU(2)
15 HASH GROUP BY
16 HASH JOIN 1 2029903872 0 2 ON CPU(2)
17 TABLE ACCESS FULL M@SEL$4
18 HASH JOIN RIGHT OUTER 2 2029903872 0 3 ON CPU(3)
19 PX RECEIVE
20 PX SEND BROADCAST
21 PX BLOCK ITERATOR
22 MAT_VIEW ACCESS FULL PH@SEL$4
23 HASH JOIN
24 PX RECEIVE
25 PX SEND BROADCAST
26 PX BLOCK ITERATOR
27 MAT_VIEW ACCESS FULL L@SEL$4
28 HASH JOIN 3 2029903872 0 4 ON CPU(4)
29 PX RECEIVE
30 PX SEND BROADCAST
31 HASH JOIN
32 JOIN FILTER CREATE
33 PX RECEIVE
34 PX SEND BROADCAST
35 HASH JOIN
36 JOIN FILTER CREATE
37 MAT_VIEW ACCESS FULL LFS@SEL$4
38 JOIN FILTER USE
39 PX BLOCK ITERATOR
40 MAT_VIEW ACCESS FULL LF@SEL$4
41 JOIN FILTER USE
42 PX BLOCK ITERATOR
43 MAT_VIEW ACCESS INMEMORY FULL RCC_FSG@SEL$4
44 PX BLOCK ITERATOR
45 MAT_VIEW ACCESS INMEMORY FULL T@SEL$4 10 2029903872 0 15 ON CPU(15)
SQL Summary 0 ASH fixed 1 execs from 29 sessions 133 ash rows were fixed from 21.12.2017 15:49:48 to 21.12.2017 15:49:57
Для сравнения, неатомарное обновление без INMEMORY ожидаемо получается в разы медленнее при неизменном плане выполнения(*****):
SQL> ALTER TABLE BIMATVIEW.GROSS_BOOK_REP_STORE no INMEMORY; -- вернул из INMEMORY на землю
Table altered
SQL> exec dbms_mview.refresh('"BI"."MV_EXPENSES_DETAIL"', method => 'C', atomic_refresh => false, out_of_place => FALSE, parallelism => 32)
PL/SQL procedure successfully completed
Executed in 43,156 seconds -- без INMEMORY в 3+ раза хуже (*****)
SQL> @ash_sqlmon2 gfrtdrwchhh0t "" 16777223 ""
LAST_PLSQL PLAN_HASH_VALUE ID PLAN_OPERATION OBJECT_ALIAS PX MAX_PGA_ALLOCATED MAX_TEMP_ALLOCATED ASH_ROWS WAIT_PROFILE
----------- --------------- ---- --------------------------------------------------------------------------------- ------------------------- ---- ----------------- ------------------ ---------- --------------------------------------------------------------------
Main Query 2964677505 0 INSERT STATEMENT
1 PX COORDINATOR
2 PX SEND QC (RANDOM)
3 LOAD AS SELECT (HIGH WATER MARK BROKERED) 4 209403904 900726784 6 ON CPU(5); direct path write(1)
4 OPTIMIZER STATISTICS GATHERING 2 209403904 900726784 3 ON CPU(3)
5 VIEW from$_subquery$_001@SEL$1 1 209403904 900726784 2 ON CPU(2)
6 WINDOW SORT 14 1175994368 900726784 78 direct path read temp(30); direct path write temp(27); ON CPU(21)
7 PX RECEIVE 1 1100644352 7340032 2 ON CPU(2)
8 PX SEND HASH 4 1100644352 7340032 5 ON CPU(5)
9 VIEW from$_subquery$_002@SEL$2 1 1100644352 7340032 2 ON CPU(2)
10 VIEW 0 1100644352 7340032 1 ON CPU(1)
11 SORT GROUP BY ROLLUP COLLECTOR 9 1997930496 7340032 15 ON CPU(15)
12 PX RECEIVE 0 1922396160 0 1 ON CPU(1)
13 PX SEND HASH 4 1922396160 0 5 ON CPU(5)
14 SORT GROUP BY ROLLUP DISTRIBUTOR
15 HASH GROUP BY
16 HASH JOIN 1 1997930496 0 2 ON CPU(2)
17 TABLE ACCESS FULL M@SEL$4
18 HASH JOIN RIGHT OUTER 0 1517096960 0 2 ON CPU(2)
19 PX RECEIVE
20 PX SEND BROADCAST
21 PX BLOCK ITERATOR
22 MAT_VIEW ACCESS FULL PH@SEL$4
23 HASH JOIN 1 1867661312 0 2 ON CPU(2)
24 PX RECEIVE
25 PX SEND BROADCAST
26 PX BLOCK ITERATOR
27 MAT_VIEW ACCESS FULL L@SEL$4
28 HASH JOIN 10 1868681216 0 13 ON CPU(13)
29 PX RECEIVE 4 877223936 0 5 ON CPU(5)
30 PX SEND BROADCAST 4 877223936 0 5 ON CPU(5)
31 HASH JOIN 0 877223936 0 1 ON CPU(1)
32 JOIN FILTER CREATE
33 PX RECEIVE
34 PX SEND BROADCAST
35 HASH JOIN
36 JOIN FILTER CREATE
37 MAT_VIEW ACCESS FULL LFS@SEL$4
38 JOIN FILTER USE
39 PX BLOCK ITERATOR
40 MAT_VIEW ACCESS FULL LF@SEL$4
41 JOIN FILTER USE
42 PX BLOCK ITERATOR
43 MAT_VIEW ACCESS INMEMORY FULL RCC_FSG@SEL$4 0 877223936 0 1 ON CPU(1)
44 PX BLOCK ITERATOR
45 MAT_VIEW ACCESS INMEMORY FULL T@SEL$4 15 1997930496 0 361 direct path read(346); ON CPU(15)
SQL Summary 0 ASH fixed 1 execs from 31 sessions 512 ash rows were fixed from 21.12.2017 16:57:21 to 21.12.2017 16:57:54
— собственно INSERT занял ~ 34 секунды, что в точности соответствует данным SQL Monitor, при том же DOP [Downgrade]:)
Global Information ------------------------------ Status : DONE Instance ID : 1 Session : SYS (1395:34512) SQL ID : gfrtdrwchhh0t SQL Execution ID : 16777223 Execution Started : 12/21/2017 16:57:21 First Refresh Time : 12/21/2017 16:57:21 Last Refresh Time : 12/21/2017 16:57:54 Duration : 33s -- ***** Module/Action : PL/SQL Developer/Command Window - New Service : TEST Program : plsqldev.exe DOP Downgrade : 50% Global Stats ==================================================================================================================================== | Elapsed | Cpu | IO | Application | Concurrency | Cluster | PL/SQL | Buffer | Read | Read | Write | Write | Cell | | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Time(s) | Gets | Reqs | Bytes | Reqs | Bytes | Offload | ==================================================================================================================================== | 503 | 92 | 411 | 0.00 | 0.08 | 0.05 | 0.01 | 2M | 36010 | 16GB | 35944 | 3GB | -13.64% | ==================================================================================================================================== Parallel Execution Details (DOP=16 , Servers Requested=64 , Servers Allocated=32)
Полезный обзор способов обновления в блоге Jonathan Lewis.12c MView refresh с неотъемлемо важными комментариями Михаила Великих
Комментарии к записи 12c: Простейшее использование INMEMORY при обновлении Materialized View отключены