Cause analysis of query plan errors

Undefined undefined SELECT *

From Cr_bkg_intmd_shmt_partition Bkgshmtresult

WHERE Bkgshmtresult. bkg_cfm_id =: B1

and bkgshmtresult.comp_id =: B2
From the v$session_longops, it is doing a full table SCAN to the table cr_bkg_intmd_shmt_partition. And the table cr_bkg_intmd_shmt_partition is a very large partition table, which we did before the optimization of the partitioned table (the case I have in the 11g new features-more flexible zoning strategy) mentioned that Partition key is comp_id, The partitioning policy is a partition for each VIP user, and all non-VIP users are in the default partition.

The query condition for this statement is simple, and a global Index is built on (bkg_cfm_id,comp_id). By directly parsing the query plan, it is found that it can hit the index correctly:

Undefined undefined SQL > EXPLAIN plan for

2 SELECT *

3 from Cr_bkg_intmd_shmt_partition Bkgshmtresult

4 WHERE Bkgshmtresult. bkg_cfm_id =: B1

5 and bkgshmtresult.comp_id =: B2;

explained.

SQL > select * FROM Table (Dbms_xplan.display ());

Plan_table_output

-- ------------------------------------------------------------------------------------------------------------- --------------------

Plan Hash value:772272200

-- ------------------------------------------------------------------------------------------------------------- --------------------

| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Pstart | Pstop |

-- ------------------------------------------------------------------------------------------------------------- --------------------

| 0 | SELECT STATEMENT | | 1 | 880 | 5 (0) | 00:00:01 | | |

| 1 | TABLE ACCESS by GLOBAL INDEX ROWID | cr_bkg_intmd_shmt_partition | 1 | 880 | 5 (0) | 00:00:01 | ROWID | ROWID |

|* 2 | INDEX RANGE SCAN | cr_bkg_intmd_partition_idx03 | 1 | | 4 (0) | 00:00:01 | | |

-- ------------------------------------------------------------------------------------------------------------- --------------------

predicate information (identified by Operation ID):

-- -------------------------------------------------

2-access ("Bkgshmtresult".) bkg_cfm_id "= To_number (: B1) and" Bkgshmtresult ". comp_id "=: B2) undefined undefined SQL > select Lpad (", 2 * (LEVEL-1)) | | Operation | | ' ' ||

2 decode (ID, 0, ' cost = ' | | position ') "OPERATION",

3 options,

4 object_name

5 from V$sql_plan

6 start with (sql_id = ' F0MWUQFXXMTMF ' and hash_value = 3151619694 and id = 0)

7 connect by Prior ID = parent_id

8 and Prior sql_id = sql_id

9 and Prior hash_value = Hash_value

by ID, position;

OPERATION OPTIONS object_name

-- -------------------------- ------------------------------------- ------------------------

SELECT STATEMENT Cost = 265

PARTITION LIST

TABLE ACCESS Full Cr_bkg_intmd_shmt_partition
This phenomenon is usually due to binding variable peering (bind Variable peeking): Peeking variable values are relatively special, resulting in a computed full table scan price lower than the index scan price. To identify the problem, we find the data that resolves the query plan to "peek":

Undefined undefined SQL > SELECT * from TABLE (Dbms_xplan. Display_cursor (' F0MWUQFXXMTMF ', 0, ' ADVANCED '));

Plan_table_output

-- ------------------------------------------------

sql_id F0MWUQFXXMTMF, child number 0

-- -----------------------------------

SELECT * from Cr_bkg_intmd_shmt_partition bkgshmtresult WHERE Bkgshmtresult. bkg_cfm_id =: v_bkg_cfm_id

and bkgshmtresult.comp_id =: v_comp_id

Plan Hash value:3035855418

-- ------------------------------------------------------------------------------------------------------------- ------

| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Pstart | Pstop |

-- ------------------------------------------------------------------------------------------------------------- ------

| 0 | SELECT STATEMENT | | | 265 (100) | | |

| 1 | PARTITION LIST Single | | 1 | 756 | 265 (1) | 00:00:04 | KEY | KEY |

|* 2 | TABLE ACCESS Full | cr_bkg_intmd_shmt_partition | 1 | 756 | 265 (1) | 00:00:04 | KEY | KEY |

-- ------------------------------------------------------------------------------------------------------------- ------

Query block Name/object Alias (identified by Operation ID):

-- -----------------------------------------------------------

1-sel$ 1

2-sel$ 1/bkgshmtresult @SEL $1

Outline Data

-- -----------

/* +

Begin_outline_data

Ignore_optim_embedded_hints

Optimizer_features_enable (' 10.2.0.3 ')

Opt_param (' _complex_view_merging ' false ')

All_rows

Outline_leaf (@ "sel$1")

Full (@ "sel$1" "Bkgshmtresult" @ "sel$1")

End_outline_data

*/

Peeked binds (identified by position):

-- ------------------------------------

1-: v_bkg_cfm_id (number): 592533

2-: v_comp_id (VARCHAR2), CSID = 873): ' BANDHK270600815 ' you can see that the values of two variables are v_bkg_cfm_id:592533, v_comp_id: ' BANDHK 270600815 '. Using these 2 values, and then reparse the query plan, is a full table scan:

Undefined undefined SQL > explain plan for

2 SELECT *

3 from Cr_bkg_intmd_shmt_partition Bkgshmtresult

4 WHERE Bkgshmtresult. bkg_cfm_id = 592533

5 and bkgshmtresult.comp_id = ' BANDHK270600815 ';

explained.

SQL > SELECT * from TABLE (Dbms_xplan. DISPLAY ());

Plan_table_output

-- ------------------------------------------------------------------------------------------------------------- ------

Plan Hash value:554208192

-- ------------------------------------------------------------------------------------------------------------- ------

| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Pstart | Pstop |

-- ------------------------------------------------------------------------------------------------------------- ------

| 0 | SELECT STATEMENT | | 1 | 756 | 265 (1) | 00:00:04 | | |

| 1 | PARTITION LIST Single | | 1 | 756 | 265 (1) | 00:00:04 | KEY | KEY |

|* 2 | TABLE ACCESS Full | cr_bkg_intmd_shmt_partition | 1 | 756 | 265 (1) | 00:00:04 | 14 | 14 |

-- ------------------------------------------------------------------------------------------------------------- ------

predicate information (identified by Operation ID):

-- -------------------------------------------------

2-filter ("Bkgshmtresult".) bkg_cfm_id "= 592536)
Note that there is a partition key:comp_id in the query criteria, so the query plan contains the partition List only for the partition (14)

Polling , the full Table Scan is actually a full Scan for a partition, and ' BANDHK270600815 ' is a VIP user. Let's look at the statistics data on the partition:

Undefined undefined SQL > select Num_rows, blocks from dba_tab_statistics where table_name = ' cr_bkg_intmd_shmt_partit ION ' and owner = ' Cs2_party_owner ' and partition_name = ' p_comp_bandhk270600815 ';

Num_rows BLOCKS

-- -------- ----------

18 8
The data on the partition is very small, so the cost of this full Scan is not high, the parsed query plan is full Table Scan, and when the data falls into the default partition (the maximum partition), its query plan remains Scan, causing performance problems! Continue with the query, There are also very few data found in a few partitions. This result is the same as when we did the POC: in the POC, all VIP users have more than 10K of data, but the production environment is very small number of VIP users. I'm afraid I need to find the answer from the development side of the BA/SA. As a DBA, our current task is to avoid the recurrence of performance problems that arise.

Because our system is running on weekdays (Monday to Friday), the system restarts every week. Therefore, in the Monday many statements will be hard parsed. In this case, it is difficult to avoid the hard parsing, the peep of the data into these small partitions again. To avoid performance problems again, consider the following methods:

Add hint to the related statement, forcing the index to be used. But such modifications involve too much surface, and if the schema changes in the future, the Code maintenance update is difficult;

Fixed query plan with stored outline for statements. Its disadvantage is similar to that of 1th;

Disables bind Variable peeking. Because our system restarts every week and if the risk is greater at DB level disable, we consider disabling at session level. Because the code for the module is called through package, the amount of code modified is very small: add the following statement to the entry function.

Undefined undefined execute immediate ' alter session set ' _optim_peek_user_binds ' = False ';
Postscript