Execution Plan error caused by an implicit conversion

Check the awr report and record the index failure caused by implicit conversion.

In awr, statements consume more than 40 million logical reads.

Variables are tested quickly.

SQL  a.order_prod_amount, b.prod_cost_price  user_sess a, PR_DO_T b  a.prod_id  b.prod_id  a.order_no  :: hash value:  Id   Operation                            Name         Rows   Bytes  Cost (CPU) Time      Pstart Pstop       STATEMENT                                                    () ::                     NESTED LOOPS                                                       () ::                       ACCESS  GLOBAL  ROWID user_sess                      () ::  ROWID  ROWID          SCAN                 SYS_C008504                      () ::                       ACCESS   ROWID        PR_DO_T                  () ::                        SCAN                 SYS_C008459                      () ::                    access("A"."ORDER_NO"  access("A"."PROD_ID"            bytes sent via SQLNet   bytes received via SQLNet   SQLNet roundtrips   sorts (  rows processed

View historical execution plans by statement sqlid

SQL  a.order_prod_amount, b.prod_cost_price  user_sess a, PR_DO_T b  a.prod_id  b.prod_id  a.order_no  88f4paj5t26xb

User_sess clearly misperformed the execution plan and used full table scan.

Suddenly, the user_sess table field order_no is varchar2, which is assumed to be implicit conversion.
Test with single quotes removed

SQL  a.order_prod_amount, b.prod_cost_price  user_sess a, PR_DO_T b  a.prod_id  b.prod_id  a.order_no  :: hash value:  Id   Operation                     Name         Rows   Bytes  Cost (CPU) Time      Pstart Pstop       STATEMENT                                        106K  () ::                     NESTED LOOPS                                           106K  () ::                      PARTITION RANGE                                    106K  () ::                    ACCESS           user_sess                106K  () ::                    ACCESS   ROWID PR_DO_T                         () ::                        SCAN          SYS_C008459                      () ::                    filter(TO_NUMBER("A"."ORDER_NO")  access("A"."PROD_ID"            bytes sent via SQLNet   bytes received via SQLNet   SQLNet roundtrips   sorts (  rows processed

Ask the developer to add the quotation marks to avoid implicit conversion and then restore