Home » RDBMS Server » Server Administration » Query Ocasionally hangs
| Query Ocasionally hangs [message #59699] |
Wed, 17 December 2003 05:21  |
Dan White
Messages: 48
Registered: September 2003
|
Member |
|
|
We are running the following query through JDBC This is being generated by a 3rd party application. The only issue I see withthe query is that in the where clause there are 2 colums (project_status_code and ci_implem_analyst_person_id ) That are actually number data types and the third party app is quoting them ' '.
This query will hang approxametly 1 our of every 5 executions.
select c.participant_id,
c.business_name,
b.project_id,
b.project_class_code,
i.project_type_name project_type_name,
b.date_wms_created,
NVL(trim(h.person_first_name||' '||h.person_last_name),'N/A') submitter,
b.project_activation_date,
b.scheduled_completion_date,
b.proj_compl_delay_reason_code,
b.actual_completion_date,
b.billing_generated_date,
NVL(trim(d.person_first_name ||' '||d.person_last_name),'N/A') account_executive,
NVL(trim(e.person_first_name ||' '||e.person_last_name),'N/A') ci_implem_analyst,
wms_reporting_pkg.wms_GetManager(b.ci_implem_analyst_person_id) ci_implem_manager,
b.project_status_code,
b.status_reason_code,
b.estimated_annual_revenue,
NVL(trim(p.person_first_name ||' '||p.person_last_name),'N/A') SALES_EXECUTIVE,
DECODE(i.project_type_code,'A',1,'P',2,'G',3) project_type_code
FROM WMS_PROJECT b,
WMS_BUSINESS C,
WMS_PROJ_TYPE i,
WMS_TYPES_PER_PROJECT k,
WMS_PERSON d,
WMS_PERSON e,
WMS_PERSON h ,
WMS_PERSON p
WHERE b.participant_business_id = C.business_id
AND k.project_id = b.PROJECT_ID
AND i.PROJ_TYPE_ID = k.PROJ_TYPE_ID
AND d.person_id(+) = b.acct_executive_person_id
AND e.person_id(+) = b.ci_implem_analyst_person_id
AND h.PERSON_ID(+) = b.wms_submitted_by_person_id
AND p.person_id(+) = b.SALES_EXECUTIVE_PERSON_ID
and b.ci_implem_analyst_person_id = '204'
AND B.PROJECT_STATUS_CODE = '20'
order by b.project_id desc ,
decode(i.project_type_code,'A',1,'P',2,'G',3),
date_wms_created
here is the explain plan
STRATEGY
--------------------------------------------------------------------------------
SELECT STATEMENT 1.65
SORT ORDER BY 2.1 COST= 1
MERGE JOIN OUTER 3.1
HASH JOIN OUTER 4.1
HASH JOIN OUTER 5.1
HASH JOIN OUTER 6.1
HASH JOIN 7.1
NESTED LOOPS 8.1
NESTED LOOPS 9.1
TABLE ACCESS FULL WMS_PROJECT 10.1
TABLE ACCESS BY INDEX ROWID WMS_BUSINESS 10.2
INDEX UNIQUE SCAN WMS_BUSINESS_PK 11.1
INDEX RANGE SCAN WMS_TYPES_PER_PROJECT_PK 9.2
TABLE ACCESS FULL WMS_PROJ_TYPE 8.2
TABLE ACCESS FULL WMS_PERSON 7.2
TABLE ACCESS FULL WMS_PERSON 6.2
TABLE ACCESS FULL WMS_PERSON 5.2
BUFFER SORT 4.2
TABLE ACCESS BY INDEX ROWID WMS_PERSON 5.1
INDEX UNIQUE SCAN WMS_PERSON_PK 6.1
20 rows selected.
|
|
|
|
| Re: Query Ocasionally hangs [message #59702 is a reply to message #59699] |
Wed, 17 December 2003 08:31  |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
You can trace the session(level 12) , with the queries executed say 10 times and then do a TKPROF with aggregate=no waits=yes so that you can have a better idea of whats going on when the query hangs. Note that wait information can be interpreted by TKPROF only from 9i onwards.
If the statistics(cpu time,query,elapsed time etc) remain the same for all the queries,including the hanging ones(you can ignore the disk reads ,for this test), it might be something to do with network,application configs and may not be a database issue at all.
If the statistics for those 'hanging' ones are different(excessive query gets,cpu time ), see if the execution plan is differrent. I hope you have analyzed all the tables in question recently.
If only the 'ela' or 'cpu' time is excessive ,then it might indicate io waits incurred by the I/O subsystem or some kind of latch contention or other database waits( look at v$session_wait, V$session_Event and v$sesstat for the wait events and session statistics)
The best way to tackle this problem will be interpreting the raw trace file directly,if you can, but tkprof should be able to give you the wait information .
-Thiru
|
|
|
|
Goto Forum:
Current Time: Fri Sep 20 08:31:48 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
