Re: [cx-oracle-users] Strange problem with query execution...

[Lassi T. <la...@ce...>]

Hi,

> I'm using cx_Oracle (actually in release 5.1.1, but with the same
> symptoms in 5.1 and 5.0.4) through SQLAlchemy to access an Oracle 10g
> database (release 10.2.0.4).
> 
> I'm building a web application querying a quite big table (nearly 3.5
> millions records), which is indexed correctly.
> 
> After activating SQLAlchemy "echo SQL" feature, I noticed that :
> - an SQLAlchemy request takes between 10 and 15 seconds
> - the same request executed from an SQL worksheet (in Tora) is nearly
>   immediate.
> 
> So I made a few tests using cx_Oracle without SQLAlchemy and the
> results are that :
> - if I execute the same query with parameters (via
>   "cursor.execute("select...", param1=value1,...)", which is the way
>   SQLAlchemy works), it still takes between 10 and 15 seconds;
> - but if I execute the same request with "hard-coded" parameters, the
>   result is immediate.
> 
> Any idea (perhaps on the DB side) ?

Yes, oracle peeks at bind variables as a part of query planning. My first
guess would be that your query plans are bad, which could be for any number
of reasons. Assuming you use standard cost-based plans, first check if you
are running with up-to-date table statistics, which implies having your
indices monitored. For example if your table stats say the table has one
row but it has million rows now, bind value peek can totally mislead the
query planner to make a bad choice.

In sqlplus you can get the query plans with 'explain plan for <query>',
then run '@?/rdbms/admin/utlxpls.sql'. This latter isn't in instant client
but you can grab it from the full oracle client and it should still work in
the instant client sqlplus.

There's a way to get that same info via APIs like cx-oracle, but I don't
remember the incantations off the top of my head. If you can attach OEM to
your database where you execute these queries, you can see the plans live,
which may be a better way to evaluate your application.

Below you can find some useful SQL to dump and manage table stats. Feel
free to search keywords on these, there's plenty of documentation.

Be *very* careful about running these in your production schema. If you
have not had table monitoring on, and activate it, all your query plans
can change dramatically. It *is* possible even if not likely the result
is disastrously bad query plans. Though note that some DBAs enable all
databases to run all of this automatically every day or some such scheme,
so it may be that none of this is related to your problem.

Hope this helps.

Lassi

1) Dump the current table / index stats with:

====
set lines 1000
set pages 1000

select
  table_name, inserts, updates, deletes,
  timestamp, truncated, drop_segments
from user_tab_modifications
order by table_name;

select
  index_name, num_rows, distinct_keys,
  leaf_blocks, clustering_factor, blevel,
  avg_leaf_blocks_per_key
from user_indexes
order by index_name;

select
  table_name, column_name, num_distinct,
  num_nulls, num_buckets, trunc(density,4)
from user_tab_col_statistics
where table_name in (select table_name from user_tables)
order by table_name, column_name;
====

2) Enable monitoring with something like this:

====
set serveroutput on size 100000

DECLARE
BEGIN
   FOR t IN (SELECT table_name FROM user_tables
             WHERE table_name NOT LIKE 'X%'
               AND monitoring != 'YES')
   LOOP
      dbms_output.put_line ('Enabling monitoring for table ' || t.table_name);
      execute immediate 'alter table ' || t.table_name || ' monitoring';
   END LOOP;

   FOR i IN (SELECT index_name FROM user_indexes
             WHERE index_name NOT LIKE 'SYS%'
               AND index_name NOT LIKE 'X%'
               AND index_type NOT LIKE 'IOT%'
               AND last_analyzed is null)
   LOOP
      dbms_output.put_line ('Enabling monitoring for index ' || i.index_name);
      execute immediate 'alter index ' || i.index_name || ' monitoring usage';
   END LOOP;
END;
/
====

3) Force table stats to update with something like this:

====
set serveroutput on size 100000
BEGIN
   dbms_stats.gather_schema_stats
      (ownname => user,
       options => 'GATHER AUTO',
       degree => 2,
       cascade => true,
       no_invalidate => false,
       force => true);
END;
/
====