During the daily inspection of a customer's production environment database, it was found that the AWR report had a high resource consumption but execution = 0
This phenomenon can be explained as that SQL does not complete once in this one hour snapshot cycle.
(1)
For the first article SQLID=1cc0y3fyndjcv
UPDATE User1.Tab1 A SET (col1, col2, col3, col4, col5, col6, col7) = (SELECT UTL_RAW.CAST_TO_VARCHAR2(colx) col1, col2, col3, col4, col5, col6, col7 FROM User2.Tab2@DBLink1 B WHERE A.col1=B.col1 AND 1=1) WHERE col8 = '01'
You can see that DBLINK1 is a cross instance update operation after troubleshooting. The data volume of the two tables is the same. Further analysis shows that B is a view
SQL> select count(*) from User1.Tab1; COUNT(*) ---------- 160366 SQL> select count(*) from User2.Tab2@DBLink1; COUNT(*) ---------- 160366 SQL> col object_name for a30 SQL> select object_name,object_type from dba_objects@DBLink1 where object_name = upper('Tab2'); OBJECT_NAME OBJECT_TYPE ------------------------------ ------------------- Tab2 VIEW
(two)
Check the execution plan of SQL and scan the whole table. When analyzing the selectivity of col8, the whole table has one value, no selectivity
SQL> select col1, col2, col3, col4, col5, col6, col7 from User1.Tab1 where col8 = '01' ; 160366 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 156998718 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 160K| 5480K| 473 (1)| 00:00:06 | |* 1 | TABLE ACCESS FULL| Tab1 | 160K| 5480K| 473 (1)| 00:00:06 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("col8"='01') Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 329996 consistent gets 0 physical reads 106944 redo size 4647458 bytes sent via SQL*Net to client 118125 bytes received via SQL*Net from client 10693 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 160366 rows processed SQL> select count(*),col from User1.Tab1 group by col8; COUNT(*) MFMANAUNIT ---------- -------------------- 160366 01
(three)
Analyze from the instance level, the script runs on the current database server host1; check the server process, and Oracle JOB process is ora ˊ j000; then look at the currently running JOB
SQL> @sql_now SID STATUS PROCESS SCHEMANAME OSUSER SQL QUERY PROGRAM ---- -------- ---------- ---------------- ---------------- ------------------------------------------------------------------------------------------------------------------------ ------------------------------ 226 ACTIVE 21800 DBUSRSYS oracle UPDATE User1.Tab1 A SET (col1, col2, col3, col4, col5, col6, col7) = (SELECT UTL_RAW.CAST_TO_VARCHAR2(colx) col1, col2, col3, col4, col5, col6, col7 FROM User2.Tab2@DBLink1 B WHERE A.col1=B.col1 AND 1=1) WHERE col8 = '01' oracle@host1 [root@host1~]# ps -ef|grep 21800 root 6954 5534 0 14:43 pts/2 00:00:00 grep 21800 oracle 21800 1 0 05:26 ? 00:03:25 ora_j000_orcl SQL> select * from dba_jobs_running; SID JOB FAILURES LAST_DATE LAST_SEC THIS_DATE THIS_SEC INSTANCE ---- ---------- ---------- --------- -------- --------- -------- ---------- 226 21 0 02-JAN-19 01:00:30 03-JAN-19 05:26:53 0
JOB sid == SQL sid, and the state is running, then it is the UPDATE operation invoked in JOB; JOB has been running for a long time.
(four)
View JOB content
SQL> select job,substr(what,1,100) from dba_jobs where job = 21; JOB SUBSTR(WHAT,1,100) ---------- ---------------------------------------------------------------------------------------------------- 21 package1.RUN; SQL> select dbms_metadata.get_ddl('PACKAGE','package1','User1') from dual; -------------------------------------------------------------------------------- CREATE OR REPLACE PACKAGE "User1"."package1" Is Procedure writelog ( vtab In Varchar2, vflag In Char, vlog In Varchar2, vdate In Date, vtype In Char Default 'S' ............ # No more than 400 lines
(five)
So far, the basic troubleshooting has been completed. Since the third party is responsible for the development and operation of the customer's application program, the above information is fed back to the communication group and received the feedback from the developer: This is a temporary business operation, which has been transferred to the development for modification.