Re: Query with Bind variables hangs but hard coded literals works fine

• 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.
>

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217


--
http://www.freelists.org/webpage/oracle-l