oracle慢sql的where条件都是冒号加数字，怎么办

ZALBB

explain plan for UPDATE PEXAM_ITEMS_TITLE SET REPORT_IMAGE1_URL = :1 WHERE ITEMUUID = :2;
select * from table(dbsm_xplan.display);

explain plan for UPDATE exem_post SET patientid = :1, pname = :2, rpttime = :3, examPara = :4, impression = :5, description = :6, recommendation = :7, isabnormal = :8, rptdtname = :9, ckdtname = :10, exemposition = :11, ckdttime = :12, accessionno = :13 WHERE orderno = :14 and exempostid = :15

select * from table(dbsm_xplan.display);


能否解析SQL语句，与有无绑定变量值没关系，

czxin788

ZALBB 发表于 2020-10-26 13:51
explain plan for UPDATE PEXAM_ITEMS_TITLE SET REPORT_IMAGE1_URL = :1 WHERE ITEMUUID = :2;select * fr ...

感谢，可以解析，学到了。接下来，就要分析死锁的原因了。
SQL> explain plan for UPDATE exem_post SET patientid = :1, pname = :2, rpttime = :3, examPara = :4, impression = :5, description = :6, recommendation = :7, isabnormal = :8, rptdtname = :9, ckdtname = :10, exemposition = :11, ckdttime = :12, accessionno = :13 WHERE orderno = :14 and exempostid = :15;

Explained.



SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 732283317

--------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT             |                               |     1 |   417 |     2         (0)| 00:00:01 |
|   1 |  UPDATE                      | EXEM_POST               |       |       |            |               |
|*  2 |   TABLE ACCESS BY INDEX ROWID| EXEM_POST               |     1 |   417 |     2         (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN             | INDEX_EXEM_POST_ORDERNO |     1 |       |     1         (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("EXEMPOSTID"=:15)
   3 - access("ORDERNO"=:14)

16 rows selected.


SQL> explain plan for UPDATE PEXAM_ITEMS_TITLE SET REPORT_IMAGE1_URL = :1 WHERE ITEMUUID = :2;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2226390536

------------------------------------------------------------------------------------------
| Id  | Operation          | Name                 | Rows  | Bytes | Cost (%CPU)| Time         |
------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT  |                         |     1 |    35 |     4   (0)| 00:00:01 |
|   1 |  UPDATE           | PEXAM_ITEMS_TITLE         |         |         |              |          |
|*  2 |   INDEX RANGE SCAN| INDEX_PEXAM_ITEMUUID |     1 |    35 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------

   2 - access("ITEMUUID"=:2)

14 rows selected.

ZALBB

语句的执行计划很好，所以死锁原因，更多的是用代码实现业务逻辑时设计的问题，

lfree

看上去是主外键的问题.

sqysl

1、mysql中的绑定变量并没那么好用，所以，很多人不会用；
2、楼主这个，应该是个循环死锁，排查起来可能没那么简单和直接。