Frederick Tang Weblog

When asked to tune a database, the most obvious place to look is the Top n queries by logical reads / physical reads / execution section of a Statspack report. It led me to think whether there are other places to look.

A few times, I am asked whether an index would help speed up the query performance  – maybe / maybe not. Reading the many forum replies on AskTom, I have learnt to always prove and test an idea, rather than reply with a yes or no sweeping statement.

In a very busy production OLTP system, I often use the v$session_longops to see what’s going on, and how long things are taking:

select * from v$session_longops where sofar < totalwork;

It starts getting interesting from here – some of the SQLs I observed are doing a Full Table Scan.

I looked a bit deeper to try and find a list of SQLs doing a Full Table Scan, using the SYS Performance Views?

select object_name, avg(p.bytes/1024/1024) ROWS_MB,
       avg(s.TAB_MB) TAB_MB,
       avg(l.elapsed_time/1000000) avg_elapsed_seconds,
       substr(access_predicates, 0, 300) access_predicates,
       substr(filter_predicates, 0, 300) filter_predicates
from (select * from v$sql_plan) p,
     (select segment_name, owner, bytes/1024/1924 as TAB_MB
      from dba_segments) s,
     (select * from v$sql) l
where operation=’TABLE ACCESS’ and options=’FULL’
  and object_owner=’&1′ and s.segment_name=p.object_name
  and s.owner=p.object_owner and p.hash_value(+)=l.hash_value
  and filter_predicates IS NOT NULL
group by object_name, access_predicates, filter_predicates
order by TAB_MB desc

Disclaimer: I don’t recommend running this SQL unless you are authorized and feel it is safe to do so, on a Live Production database.

This query will identify table name (object_name), size of data returned by SQL (rows_mb), size of table (tab_mb), average run time (avg_elapsed_seconds), access and filter predicates. “object_owner” in this query is the schema that you want to analyze.

What I found is quite interesting, some examples:

OBJECT_NAME         ROWS_MB     TAB_MB AVG_ELAPSED_SECONDS  
—————- ———- ———- ——————-
ACCESS_PREDICATES
———————————————————-
FILTER_PREDICATES
———————————————————-

PROPERTY_ASSET   .001555443 195.858628            6.059946

“ID”=1141794061642

PUBLISH_TABLE    .000032425 7.98336798            4.583978 

TO_NUMBER(“PUBID”)=(-1)

IDMAP            .000026703 2.12889813              .67757 

TO_NUMBER(“ID”)=1141772362747

PRODUCT          .000098575 .532224532          .207754455 

LOWER(“PRODUCTCODE”)=:1

PROPERTY_ASSET: The table itself is around 195MB in size, the query executed a Full Table Scan on this table to find an ID. The results returned is around 1KB, and most likely only one row was returned. It would be interesting to find out whether there’s a primary key index on ID. This query doesn’t look like it came from the application as it is not using bind variables, but then, it might be another thing to look at.

PUBLISH_TABLE and IDMAP: These two are more interesting, and reveals a common SQL coding error. When I “desc” both tables, I found both PUBLISH_TABLE.PUBID and IDMAP.ID is of VARCHAR2 datatype. However, what the filter predicate is telling me is that both SQLs were looking for a NUMBER rather than a VARCHAR2. For example:

select *  from publish_table where pubid=(-1);

select * from idmap where id=1141772362747;

rather than:

select *  from publish_table where pubid=’-1′;

select * from idmap where id=’1141772362747′;

When searching columns using incompatible datatypes, Oracle will attempt to do conversion for you, and the result is, if there were indexes created against these columns, they will not be used at all.

PRODUCT: This one may or may not be a concern. The query completed in 0.20 seconds on average – is this slow or is this fast? Is it used a lot or just a one-off query? It is up to your interpretation. There might be an index on PRODUCTCODE already, but as soon as a function (e.g. LOWER()) is used on the column, the index will not be used. In this case, a function-based index should be used instead.