Shulou(Shulou.com)06/02 Report--

This article mainly introduces the optimization examples of JAVA,Mybatis,Oracle variable type and field type inconsistent, partition table full scan, the article is very detailed, has a certain reference value, interested friends must read it!

The development response [incremental inventory] function is slow, and the feedback query has specified partition conditions, and the quantity is small.

1) through the sharing of the common sql of Oracle DBA (the sql being executed), it is found that the COST of the SQL is > 1400000.

2) through select * from table (dbms_xplan.display_cursor ('SQL_ID','0','advanced')); analyze the execution plan of the sql and bind variables to find

PARTITION RANGE ALL partition full scan;-> createtm partition field passed parameters why full scan?

| |

The createtm partition condition in the binding variable does not show a passed value.

| |

The partition condition createtm contains [INTERNAL_FUNCTION implicit conversion function];-> guess that the inconsistency between the parameter passing type and the DB field type leads to a full scan of the partition table.

| |

Through DBA_HIST_SQLBIND, it is found that the conditional parameters of createtm partition (14:20:00 on 2017-01-17-14:25:00 on 2017-01-17), the field type is timestamp, and the createtm field is date type.

Summary:

Develop mybatis framework used in java layer, convert date type fields to timestamp type [and createtm $gt:=# {params.begintm} and createtm $lt:# {params.endtm}], resulting in INTERNAL_FUNCTION implicit conversion, resulting in just-in-time partition conditions createtm passing partition conditions are also identified as unpassed variables, resulting in a full scan of the partition table

Optimized and createtm > = to_date (# {params.begintm}, 'yyyy-mm-dd hh34:mi:ss') and createtm

The above is all the contents of this article entitled "Optimization examples of inconsistent JAVA,Mybatis,Oracle variable types and field types and full scan of partition tables". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.