Home » RDBMS Server » Performance Tuning » Slow query responce
icon8.gif  Slow query responce [message #235779] Tue, 08 May 2007 00:29 Go to next message
dbashailendra
Messages: 2
Registered: May 2007
Junior Member
Hi,
This is my query.

SELECT count(distinct doc.DOCUMENT_ID) 
		from  dep.INDX_DOCUMENT_SYN  doc,
			  dep.INDX_DOCUMENT_DETAIL_SYN docdetail,
              dep.DEP_CATEGORY_MAPPING catmap
		where doc.LOAN_NUMBER = to_number(LOAN_NUMBER) and
		  	  doc.PROCESS_TIME is not null and
		      doc.DOCUMENT_ID = docDetail.DOCUMENT_ID and
		      catMap.INDEX_CAT_ID = docDetail.CATEGORY_ID;

This query is taking too long to execute.(approx 5 minuts) i checked the execution plan and found thet its doing full table scan of INDX_DOCUMENT_SYN table which is having 7M rows. INDX_DOCUMENT_SYN table is having index but it not using the indexes.

Blow is the output of TKPROF report.

TKPROF: Release 10.1.0.2.0 - Production on Tue May 8 10:23:32 2007

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

Trace file: /opt/oracle/admin/prod/udump/prod_ora_13070.trc
Sort options: default

********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing 
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

The following statements encountered a error during parse:

SELECT count(distinct doc.DOCUMENT_ID) into l_indx_doc_count
		from  INDX_DOCUMENT_SYN  doc,
			  INDX_DOCUMENT_DETAIL_SYN docdetail,
              DEP_CATEGORY_MAPPING catmap
		where doc.LOAN_NUMBER = to_number(c1rec.LOAN_NUMBER) and
		  	  doc.PROCESS_TIME is not null and
		      doc.DOCUMENT_ID = docDetail.DOCUMENT_ID and
		      catMap.INDEX_CAT_ID = docDetail.CATEGORY_ID
*** 2007-05-08
Error encountered: ORA-00942
--------------------------------------------------------------------------------
SELECT count(distinct doc.DOCUMENT_ID) into l_indx_doc_count
		from  dep.INDX_DOCUMENT_SYN  doc,
			  dep.INDX_DOCUMENT_DETAIL_SYN docdetail,
              dep.DEP_CATEGORY_MAPPING catmap
		where doc.LOAN_NUMBER = to_number(c1rec.LOAN_NUMBER) and
		  	  doc.PROCESS_TIME is not null and
		      doc.DOCUMENT_ID = docDetail.DOCUMENT_ID and
		      catMap.INDEX_CAT_ID = docDetail.CATEGORY_ID
==============
Error encountered: ORA-00904
--------------------------------------------------------------------------------
SELECT count(distinct doc.DOCUMENT_ID) into l_indx_doc_count
		from  dep.INDX_DOCUMENT_SYN  doc,
			  dep.INDX_DOCUMENT_DETAIL_SYN docdetail,
              dep.DEP_CATEGORY_MAPPING catmap
		where doc.LOAN_NUMBER = to_number(LOAN_NUMBER) and
		  	  doc.PROCESS_TIME is not null and
		      doc.DOCUMENT_ID = docDetail.DOCUMENT_ID and
		      catMap.INDEX_CAT_ID = docDetail.CATEGORY_ID
==============
Error encountered: ORA-00905
********************************************************************************

alter session set sql_trace=true


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        1      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5  
********************************************************************************

select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1, 
  spare2 
from
 obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null 
  and linkname is null and subname is null


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.01       0.00          0          0          0           0
Execute      6      0.00       0.00          0          0          0           0
Fetch        6      0.00       0.00          1         14          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       14      0.01       0.00          1         14          0           2

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)
********************************************************************************

select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property,
  i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.lblkkey,
  i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#,
  nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mod(i.pctthres$,256),
  i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(c.deferrable#+c.valid#,0),
  nvl(i.spare1,i.intcols),i.spare4,i.spare2,i.spare6,decode(i.pctthres$,null,
  null,mod(trunc(i.pctthres$/256),256)),ist.cachedblk,ist.cachehit,
  ist.logicalread 
from
 ind$ i, ind_stats$ ist, (select enabled, min(cols) unicols,
  min(to_number(bitand(defer,1))) deferrable#,min(to_number(bitand(defer,4))) 
  valid# from cdef$ where obj#=:1 and enabled > 1 group by enabled) c where 
  i.obj#=c.enabled(+) and i.obj# = ist.obj#(+) and i.bo#=:1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      3      0.00       0.00          0          0          0           0
Fetch        7      0.01       0.00          0         26          0           4
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       11      0.01       0.01          0         26          0           4

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************

select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, 
  sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, 
  spare1, spare2, avgcln 
from
 hist_head$ where obj#=:1 and intcol#=:2


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute     11      0.01       0.00          0          0          0           0
Fetch       11      0.00       0.00          0         33          0          11
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       23      0.01       0.00          0         33          0          11

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: RULE
Parsing user id: SYS   (recursive depth: 3)
********************************************************************************

select pos#,intcol#,col#,spare1,bo#,spare2 
from
 icol$ where obj#=:1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      4      0.01       0.00          0          0          0           0
Fetch       12      0.00       0.00          0         24          0           8
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       17      0.01       0.00          0         24          0           8

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************

select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,
  nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182,
  scale,183,scale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,
  rowid,col#,property, nvl(charsetid,0),nvl(charsetform,0),spare1,spare2,
  nvl(spare3,0) 
from
 col$ where obj#=:1 order by intcol#


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      3      0.00       0.00          0          0          0           0
Fetch       20      0.01       0.00          0          9          0          17
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       24      0.01       0.00          0          9          0          17

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************

select node,owner,name 
from
 syn$ where obj#=:1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          6          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.00       0.00          0          6          0           2

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)
********************************************************************************

select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, 
  nvl(property,0),subname,d_attrs 
from
 dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order#


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        4      0.00       0.00          0         12          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        8      0.00       0.00          0         12          0           2

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT ORDER BY (cr=6 pr=0 pw=0 time=258 us)
      1   NESTED LOOPS OUTER (cr=6 pr=0 pw=0 time=210 us)
      1    TABLE ACCESS BY INDEX ROWID DEPENDENCY$ (cr=3 pr=0 pw=0 time=120 us)
      1     INDEX RANGE SCAN I_DEPENDENCY1 (cr=2 pr=0 pw=0 time=78 us)(object id 125)
      1    TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 pr=0 pw=0 time=77 us)
      1     INDEX UNIQUE SCAN I_OBJ1 (cr=2 pr=0 pw=0 time=44 us)(object id 36)

********************************************************************************

select order#,columns,types 
from
 access$ where d_obj#=:1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          4          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      0.00       0.00          0          4          0           0

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  TABLE ACCESS BY INDEX ROWID ACCESS$ (cr=2 pr=0 pw=0 time=74 us)
      0   INDEX RANGE SCAN I_ACCESS1 (cr=2 pr=0 pw=0 time=65 us)(object id 127)

********************************************************************************

select user#,type# 
from
 user$ where name=:1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          1          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          1          0           0

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)
********************************************************************************

SELECT count(distinct doc.DOCUMENT_ID) 
		from  dep.INDX_DOCUMENT_SYN  doc,
			  dep.INDX_DOCUMENT_DETAIL_SYN docdetail,
              dep.DEP_CATEGORY_MAPPING catmap
		where doc.LOAN_NUMBER = to_number(LOAN_NUMBER) and
		  	  doc.PROCESS_TIME is not null and
		      doc.DOCUMENT_ID = docDetail.DOCUMENT_ID and
		      catMap.INDEX_CAT_ID = docDetail.CATEGORY_ID

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1    353.52     460.68      72183   27915155         47           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3    353.52     460.69      72183   27915155         47           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5  
********************************************************************************

alter session set sql_trace=false


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5  



********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      3      0.00       0.00          0          0          0           0
Fetch        1    353.52     460.68      72183   27915155         47           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6    353.52     460.69      72183   27915155         47           1

Misses in library cache during parse: 2
Misses in library cache during execute: 1


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       12      0.01       0.01          0          0          0           0
Execute     34      0.02       0.02          0          0          0           0
Fetch       65      0.02       0.00          1        129          0          46
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      111      0.05       0.04          1        129          0          46

Misses in library cache during parse: 9
Misses in library cache during execute: 9

    3  user  SQL statements in session.
   12  internal SQL statements in session.
   15  SQL statements in session.


[mod: added CODE tags]

[Updated on: Wed, 09 May 2007 02:55] by Moderator

Report message to a moderator

Re: Slow query responce [message #235792 is a reply to message #235779] Tue, 08 May 2007 00:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read: How to format your posts

Regards
Michel
Re: Slow query responce [message #236139 is a reply to message #235792] Wed, 09 May 2007 03:05 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
SELECT count(distinct doc.DOCUMENT_ID) 
		from  dep.INDX_DOCUMENT_SYN  doc,
			  dep.INDX_DOCUMENT_DETAIL_SYN docdetail,
              dep.DEP_CATEGORY_MAPPING catmap
		where doc.LOAN_NUMBER = to_number(LOAN_NUMBER) and
		  	  doc.PROCESS_TIME is not null and
		      doc.DOCUMENT_ID = docDetail.DOCUMENT_ID and
		      catMap.INDEX_CAT_ID = docDetail.CATEGORY_ID;


What is to_number(LOAN_NUMBER)? Is it supposed to be a bind variable (ie. you are querying a single LOAN_NUMBER), or is it a join condition?

I suspect you want to treat "LOAN_NUMBER" as a variable passed in from the client (PL/SQL or somewhere else). Oracle is resolving the identifier "LOAN_NUMBER" not as a variable, but as a column on the INDX_DOCUMENT_SYN table.

So the line where doc.LOAN_NUMBER = to_number(LOAN_NUMBER) does not restrict the query to a single loan, it just makes sure that every loan_number is numeric. It will fall over if a loan_number is non-numeric, so in effect it actually does nothing (except slow the query down).

If LOAN_NUMBER is a PL/SQL variable, you should either change its name or qualify it in the SQL with the procedure/function/package/block name. This will force Oracle to treat it as a bind variable.

Ross Leishman
Re: Slow query responce [message #237627 is a reply to message #236139] Tue, 15 May 2007 04:39 Go to previous message
jai_subi
Messages: 7
Registered: May 2007
Location: Chennai
Junior Member
When an index is created on a table then the index and the table needs to be analyzed using the below command.

ANALYZE INDEX <INDEX NAME> VALIDATE STRUCTURE
ANALYZE TABLE < TABLE NAME> COMPUTE STATISTICS

To go for index scan ,include the below parameter.

optimizer_index_cost_adj

If this parameter is set, then it goes for index scan.Try andlet me know.

Rgds,
Subha. D.


Previous Topic: Setting sql trace parameter
Next Topic: My production statspack report
Goto Forum:
  


Current Time: Sat Dec 03 14:05:35 CST 2016

Total time taken to generate the page: 0.09646 seconds