* Gather proper statistics, possibly using method_opt => 'FOR ALL
COLUMNS SIZE 2000'
* Explaing plan using /*+ GATHER_PLAN_STATISTICS */ hint and figure
out the difference between e-rows and a-rows.
* Trace the "hanging" session using SQL trace and see what are you
waiting for. SQL trace is the most important part of the process.
* Gather system statistics so that the system can figure out the
proper IO and CPU speed.
* Get a good DBA. The more autonomous the database is, the more
qualified the DBA supervising it has to be. That's why it's called
"autonomous database": with the proper DBA you can stop worrying and
learn to love the database. Any similarity with the title of a
certain old Peter Sellers movie is purely accidental. Peace on Earth!
On 11/25/19 8:23 PM, Steve Wales (AddOns) wrote:
--
Sorry for the length of this up front. Trying to get as much relevant info into the initial post as people might need to point me where I’m getting the problem
I have an Oracle 18c (18.8 if it matters) Standard Edition 2 database on Linux 7..
There’s a query from the ERP system that’s hanging the online screen and getting http timeouts because it’s not completing in a timely fashion.
I pulled the text of the query from v$sqltext and the bind variables from v$sql_bind_capture.
The query is searching a parts catalog for colloquial names for parts.
If I take the query, replace the bind variables from the query with string values and run it through SQLPLUS, it runs in about 0.7 seconds and returns the expected values.
