Shulou(Shulou.com)05/31 Report--

This article mainly introduces "the handling method of Oracle SQL execution plan exception". In the daily operation, I believe that many people have doubts about the handling method of Oracle SQL execution plan exception. The editor consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "Oracle SQL execution plan exception handling method". Next, please follow the editor to study!

Phenomenon:

The following statement has always been more efficient, the execution plan has gone through three nested loops after scanning the index range, and the results can be returned in 2 seconds, but today it took more than 1 minute as reflected by my colleagues!

The original SQL statement:

Select * From (Select Rownum As Rownumber__, T. * From (Select T1.Orderdate As "OrderDate", T1.Status As "Status", T1.Ordercode As "OrderCode", T1.Sumamt As "SumAmt", T1.Ordertype As "OrderType", T1.Questiondesc As "QuestionDesc" T1.Ordersource As "OrderSource", T2.Accepter As "Accepter", T2.City As "City", T1.Isquestion As "IsQuestion", T1.Issplit As "IsSplit", T1.Salemode As "SaleMode" T1.Stockout As "StockOut", T2.Encmobile As "EncMobile", T2.Encphone As "EncPhone", Decryptbykey (T2.Mobilephone) As "MobilePhone", T2.Province As "Province", T3.Checkercode As "CheckerCode" T3.Iscancel As "IsCancel", T3.Ischeck As "IsCheck", T3.Isclose As "IsClose", T3.Isfinish As "IsFinish" T1.Ischange As "IsChange" From Xs_Order T1 Join Xs_Orderpsaddress T2 On T1.Ordercode = T2.Ordercode Join Xs_Orderstatus T3 On T1.Ordercode = T3.Ordercode Order By T1.Ordercode Desc) t Where "OrderDate" > =: Orderdate0 And "StockOut" =: Stockout1) Temp Where Rownumber__ > 0and Rownumber__ 'a0wawpy5hfrt3' Plan_hash_value = > 2253704843,-- notice that the 2253704843 here is the normal PLAN_HASH_VALUEenabled after I have entered it with a constant = > 'YES') End

After execution, it is found that the statement still went to the wrong execution plan, and it was considered to evolve by capturing the baseline, but because the statement has bound variables, the session level is more difficult, so I thought of deleting the execution plan in the shared pool with the package. Let it do hard parsing again:

Exec dbms_shared_pool.purge ('0000000DE5E6B808, 2332516131,' c')

-the first parameter is address and hash_value in v$sqlarea, and the second is cursor)

After processing, the execution plan is generated again, and the 2253704843 execution plan is applied automatically, and the query efficiency is normal:

At this point, the study on "how to handle exceptions in the Oracle SQL execution plan" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.