One accident caused by 0.6 billion data entries and one accident caused by 0.6 billion

One accident caused by 0.6 billion data entries and one accident caused by 0.6 billion
The business staff told me that the IO of a System Disk lasted as high as 300 MB/s, and the system platform was AIX, so the topas check was indeed the case.

Run the following script to go to the Oracle database:

SELECT Disk_Reads DiskReads, Executions, SQL_ID, SQL_Text SQLText,    SQL_FullText SQLFullText FROM(   SELECT Disk_Reads, Executions, SQL_ID, LTRIM(SQL_Text) SQL_Text,       SQL_FullText, Operation, Options,       Row_Number() OVER          (Partition By sql_text ORDER BY Disk_Reads * Executions DESC)          KeepHighSQL   FROM   (       SELECT Avg(Disk_Reads) OVER (Partition By sql_text) Disk_Reads,           Max(Executions) OVER (Partition By sql_text) Executions,           t.SQL_ID, sql_text, sql_fulltext, p.operation,p.options       FROM v$sql t, v$sql_plan p       WHERE t.hash_value=p.hash_value AND p.operation='TABLE ACCESS'        AND p.options='FULL' AND p.object_owner NOT IN ('SYS','SYSTEM')       AND t.Executions > 1   )    ORDER BY DISK_READS * EXECUTIONS DESC)WHERE KeepHighSQL = 1AND rownum <=5;

At that time, no records were saved. In short, the same SQL statements collected by AWR are shown below.

We can see that the physical reading of the first SQL is very high. Then I looked at the execution plan.

SYS@zbdba>select * from table(dbms_xplan.display_cursor('54043712',null,'advanced'));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------HASH_VALUE  54043712, child number 0------------------------------------SELECT D.MODEL_ID, D.OBJ_ID, D.OBJ_TYPE, D.DATA_TYPE, D.DATA_DATE, D.DATA_FROM_DATE, D.DATA_TO_DATE,D.DATA_FLAG  FROM zbdba1 D, zbdba2 C  WHERE D.MODEL_ID = C.MODEL_ID AND C.COLLECT_ID =:COLLECT_ID        AND D.DATA_DATE = :DATA_DATE AND D.DATA_TYPE = :DATA_TYPE AND D.VALUE_FLAG = 0Plan hash value: 1780662521-------------------------------------------------------------------------------------------------------------------------| Id  | Operation                    | Name                     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |-------------------------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT             |                          |       |       |     9 (100)|          |       |       ||   1 |  NESTED LOOPS                |                          |     1 |    61 |     9   (0)| 00:00:01 |       |       ||   2 |   PARTITION RANGE SINGLE     |                          |     1 |    51 |     8   (0)| 00:00:01 |   KEY |   KEY ||*  3 |    TABLE ACCESS FULL         | zbdba1 |     1 |    51 |     8   (0)| 00:00:01 |   KEY |   KEY ||*  4 |   TABLE ACCESS BY INDEX ROWID| zbdba2             |     1 |    10 |     1   (0)| 00:00:01 |       |       ||*  5 |    INDEX UNIQUE SCAN         | PK_zbdba2          |     1 |       |     0   (0)|          |       |       |-------------------------------------------------------------------------------------------------------------------------Query Block Name / Object Alias (identified by operation id):-------------------------------------------------------------   1 - SEL$1   3 - SEL$1 / D@SEL$1   4 - SEL$1 / C@SEL$1   5 - SEL$1 / C@SEL$1Outline Data-------------  /*+      BEGIN_OUTLINE_DATA      IGNORE_OPTIM_EMBEDDED_HINTS      OPTIMIZER_FEATURES_ENABLE('10.2.0.5')      ALL_ROWS      OUTLINE_LEAF(@"SEL$1")      FULL(@"SEL$1" "D"@"SEL$1")      INDEX_RS_ASC(@"SEL$1" "C"@"SEL$1" ("zbdba2"."MODEL_ID"))      LEADING(@"SEL$1" "D"@"SEL$1" "C"@"SEL$1")      USE_NL(@"SEL$1" "C"@"SEL$1")      END_OUTLINE_DATA  */Peeked Binds (identified by position):--------------------------------------   1 - :COLLECT_ID (VARCHAR2(30), CSID=852): '70350'   2 - :DATA_DATE (DATE): 06/01/15 00:00:00   3 - :DATA_TYPE (VARCHAR2(30), CSID=852): '02'Predicate Information (identified by operation id):---------------------------------------------------   3 - filter(("D"."DATA_TYPE"=:DATA_TYPE AND "D"."DATA_DATE"=:DATA_DATE AND "D"."VALUE_FLAG"=0))   4 - filter("C"."COLLECT_ID"=TO_NUMBER(:COLLECT_ID))   5 - access("D"."MODEL_ID"="C"."MODEL_ID")Column Projection Information (identified by operation id):-----------------------------------------------------------   1 - "D"."MODEL_ID"[NUMBER,22], "D"."OBJ_ID"[NUMBER,22], "D"."OBJ_TYPE"[VARCHAR2,2],       "D"."DATA_TYPE"[VARCHAR2,2], "D"."DATA_DATE"[DATE,7], "D"."DATA_FROM_DATE"[DATE,7], "D"."DATA_TO_DATE"[DATE,7],       "D"."DATA_FLAG"[NUMBER,22]   2 - "D"."MODEL_ID"[NUMBER,22], "D"."OBJ_ID"[NUMBER,22], "D"."OBJ_TYPE"[VARCHAR2,2],       "D"."DATA_TYPE"[VARCHAR2,2], "D"."DATA_DATE"[DATE,7], "D"."DATA_FROM_DATE"[DATE,7], "D"."DATA_TO_DATE"[DATE,7],       "D"."DATA_FLAG"[NUMBER,22]   3 - "D"."MODEL_ID"[NUMBER,22], "D"."OBJ_ID"[NUMBER,22], "D"."OBJ_TYPE"[VARCHAR2,2],       "D"."DATA_TYPE"[VARCHAR2,2], "D"."DATA_DATE"[DATE,7], "D"."DATA_FROM_DATE"[DATE,7], "D"."DATA_TO_DATE"[DATE,7],       "D"."DATA_FLAG"[NUMBER,22]   5 - "C".ROWID[ROWID,10]HASH_VALUE  54043712, child number 1SELECT D.MODEL_ID, D.OBJ_ID, D.OBJ_TYPE, D.DATA_TYPE, D.DATA_DATE, D.DATA_FROM_DATE, D.DATA_TO_DATE,D.DATA_FLAG  FROM zbdba1 D, zbdba2 C  WHERE D.MODEL_ID = C.MODEL_ID AND C.COLLECT_ID =:COLLECT_ID        AND D.DATA_DATE = :DATA_DATE AND D.DATA_TYPE = :DATA_TYPE AND D.VALUE_FLAG = 0NOTE: cannot fetch plan for HASH_VALUE: 54043712, CHILD_NUMBER: 1      Please verify value of HASH_VALUE and CHILD_NUMBER;      It could also be that the plan is no longer in cursor cache (check v$sql_plan)81 rows selected.

View the number of table rows:

SYS@zbdba>select num_rows,last_analyzed from dba_tables where table_name='zbdba1';  NUM_ROWS LAST_ANALYZE---------- ------------659764063 31-JUL-15SYS@zbdba>select num_rows,last_analyzed from dba_tables where table_name='zbdba2';  NUM_ROWS LAST_ANALYZE---------- ------------     76513 14-JUL-15

Explain an plan for this SQL statement to view the execution Plan: SYS @ zbdba> select * from table (dbms_xplan.display (); PLAN_TABLE_OUTPUT explain plan hash value: 2057366878 Bytes --- | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Pstart | Pstop | Period --- | 0 | select statement | 3 | 183 | 11 (0) | 00:00:01 | 1 | table access by global index rowid | zbdba1 | 1 | 51 | 4 (0) | 00:00:01 | ROWID | 2 | nested loops | 3 | 183 | 11 (0) | 00:00:01 | 3 | table access by index rowid | zbdba2 | 2 | 20 | 4 (0) | 00:00:01 | * 4 | index range scan | IDX_zbdba2COLLECTFLAG | 2 | 2 (0) | 00:00:01 | * 5 | index range scan | IDX_C_COLLECT_MD_001 | 1 | 3 (0) | 00:00:01 | condition --- Predicate Information (identified by operation id ): ------------------------------------------------- 4-access ("C ". "COLLECT_ID" = TO_NUMBER (: COLLECT_ID) 5-access ("D ". "MODEL_ID" = "C ". "MODEL_ID" AND "D ". "DATA_DATE" =: DATA_DATE AND "D ". "DATA_TYPE" =: DATA_TYPE AND "D ". "VALUE_FLAG" = 0) 19 rows selected.

I found that I had gone through the index, but why didn't I go through the index at the time. In addition, the full table scan is performed on the second node, and the index range scan is performed on the first node.


The version of this oracle database is 10.2.0.5, and ACS (Adaptive Cursor Sharing) was not introduced before 11G. Therefore, CBO will only peat the value of the variable during the first hard parsing, and generate the execution plan, and then use the same execution plan all the time.
Here I guess it is at 2 nodes. When you bind a variable for the first time, CBO considers that full table scan is more efficient, so the execution plan will be used in the future. However, when a node is bound to a variable for the first time, CBO considers that range scanning is more efficient. Therefore, the execution plans of Node 1 and node 2 are different.




Find the reason, and you can do it. Business personnel are afraid that the database load is too large to cause downtime, so they asked me to kill all the SQL-related processes (30 processes found. After the kill process is completed, the disk IO instantly drops to 50 MB.


This option will be available after changing the SQL statement. How can we avoid it?
Now that you understand the CBO practice, it will trigger it to execute hard parsing again to obtain the correct execution plan. There are four methods:
1. alter system flush shared_pool (run this command if you want to kneel down)
2. DDL operations on related tables
3. Collect statistics again
4. dbms_shared_pool.purge


The preceding three solutions have a great impact on the production system, so the fourth method is used.

SQL> select address,hash_value,executionsfrom v$sql where hash_value=54043712 ADDRESS          HASH_VALUE EXECUTIONS ---------------- ---------- ---------- -----------0000040229F039E0 54043712          1           SQL> alter session set events '5614566 trace name context forever';SQL> exec dbms_shared_pool.purge('0000040229F039E0,54043712','C');

Use the appropriate binding variables to run out the correct execution plan.


How can we keep it unchanged permanently?
Add hint to enforce the index.

explain plan for SELECT /*+ index(zbdba1,IDX_C_COLLECT_MD_001)* / D.MODEL_ID,       D.OBJ_ID,       D.OBJ_TYPE,       D.DATA_TYPE,       D.DATA_DATE,       D.DATA_FROM_DATE,       D.DATA_TO_DATE,       D.DATA_FLAG  FROM EIC2.zbdba1 D, EIC2.zbdba2 CWHERE D.MODEL_ID = C.MODEL_ID   AND C.COLLECT_ID = '70350'   AND D.DATA_DATE = to_date('06/01/15','MM/DD/YY')   AND D.DATA_TYPE = '02'   AND D.VALUE_FLAG = 0;

If the index changes later, it will also become invalid.

Of course, there is no such thing as optimization once and for a table with a size of more than 50 GB and a data size of up to 0.6 billion records, we still need to regularly check its SQL Execution Plan.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.