Troubleshooting of Oracle with high resource consumption but execution = 0

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.

Tags: Database SQL Oracle snapshot

Posted on Mon, 02 Dec 2019 17:25:14 -0500 by Domestics