Need advice to debug: SQL query to oracle using ADOdb taking way too long

Question

I'm accessing a view table with 6 million lines from a oracle database using the ADOdb for PHP library.

running the following SQL query from a web server in a SUSE machine takes about 1 min:

$adodb_handle->Execute("SELECT COUNT(BEPROTEXT) FROM (SELECT BEPROTEXT FROM V_E_LOG WHERE BEPROTEXT LIKE '%jaya%' )");

running the same SQL query from wamp on my laptop (also using ADOdb lib) takes about 7 sec

if i run the following SQL query:

$adodb_handle->Execute("SELECT COUNT(BEPROTEXT) FROM V_E_LOG");

it takes about 3 sec from both SUSE and laptop.

I have no idea why the first query from the SUSE machine takes so long, 1min? Could you guys give me some advice on how can i solve this or debug the problem out? I'm at a dead end here :(

Answer 1

1. I assume that "6 million lines" refers to the number of rows in the table.
2. What is the query plan for the two queries?

To generate the query plan from SQL*Plus

SQL> set autotrace traceonly;
SQL> <<run your query>>

The output will be the query plan and some execution statistics. The actual data your query returns won't be printed (set autotrace on will display both the query output and the query plan/ execution statistics). Post the results for both the fast and the slow query here.

Answer 2

why do you do a count on the subquery?

woudln't it be better to do something like:

$adodb_handle->Execute("SELECT COUNT(BEPROTEXT),BEPROTEXT FROM V_E_LOG WHERE BEPROTEXT LIKE '%jaya%'");

By the way a LIKE with two wildcards on a pretty big table can slow down your query execution time pretty fast depending on the growth rate of that table.

Answer 3

Hi guys, sorry for the delayed response. I managed to figure the the issue but first let me answer some of the questions you guys asked.

Justin Cave: I did check out the query plan using SqlDeveloper which pointed me to the right direction of why the query was taking so long.

Itroubs: I'm actually displaying the data on a webpage using the datatables jquery plugin. I needed to do the fullquery/subquery count to enable the pagination feature.

The issue i was having is that my SUSE machine takes bout 1 min while my laptop takes bout 3 sec to do the same SQL query statement to the same oracle database. After some investigation, i realized that prior to the SQL query from the SUSE machine i set the following:

$adodb_handle->Execute("alter session set NLS_SORT=BINARY_CI");
$adodb_handle->Execute("alter session set NLS_COMP=LINGUISTIC");

which enables case-insensitive filtering to the oracle database for that session. I didn't set that in my laptop. If i do not set the case-insensitive stuff, the query from the SUSE machine now takes bout 3 sec to complete which is the same as my laptop.

Thanks guys for all your advise and knowledge sharing. Cheers