Custom SQL Best Practices

• Limit Direct SQL to the table matching the Data Query Type of the agent. For example, update the order_release table in an ORDER_RELEASE agent. Even updating a child table such as shipment_stop in a shipment agent can run the risk of database deadlocks.
• Don't use tables from unused functionality for other purposes. Tables are designed accounting for column indexing and volume/purging with a specific purpose in mind, and misuse can cause performance and instability problems.
• If Direct SQL Update is used, restrict it working with attribute fields, reference number, remark fields.
• Don't update internal tables from Direct SQL Update as this could corrupt/conflict with Oracle Transportation Management internal code.
• Custom SQL and reports should use the view name corresponding to desired table. The view name is the table name without “_T” suffix.
• Use the tools available for diagnosing custom SQL:
  • On the SQL Execution Interface screen, select the "Show Execution Plan" box to get an Explain Plan for the SQL you entered. Be sure to sign in with the same user that would run the SQL so you're running with the appropriate VPD.
  • Use the AWR Report screen to obtain an AWR Report for a time period you choose. Note that snapshots are written on the hour, so requested data may not be available until the top of the hour.
  • Use the AWR SQL Detail Report screen to obtain an Explain Plan of a SQL given the SQL ID from an AWR Report.