Home » RDBMS Server » Performance Tuning » Slow query responce
 Slow query responce [message #235779] |
Tue, 08 May 2007 00:29  |
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 #236139 is a reply to message #235792] |
Wed, 09 May 2007 03:05  |
rleishman
Messages: 3728
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 |
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.
|
|
|
|
Goto Forum:
Current Time: Sat Nov 23 08:11:21 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
