How improve the perfomance on form [message #520372] |
Mon, 22 August 2011 04:24 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
mm_kanish05
Messages: 493 Registered: January 2007 Location: Chennai
|
Senior Member |
![mm_kanish05%40yahoo.co.in](/forum/theme/orafaq/images/yahoo.png)
|
|
Hi,
The following query running under sqlplus is fine.
SELECT pmtrlcode,prd_code,MOVEMENT_TYPE, TRUCK_NO, CARR_CODE, SHIP_NO,
DOC_NO, DOC_QTY, FIRST_WT , FIRST_MAN_FLAG, FIRST_EWB_ID,
SECOND_WT,SECOND_MAN_FLAG,SECOND_EWB_ID,IN_DATE,FIRST_WT_TIME,
SECOND_WT_TIME,FIRST_WT_DATE,SECOND_WT_DATE,pucode
FROM t_ewb_det
WHERE vep_no = 85295
the explain plan under sqlplus
SQL> @sample.sql
7 /
PM PRD_CODE MO TRUCK_NO CARR_CODE SHI DOC_NO DOC_QTY FIRST_WT FIRST_MAN_FLAG
-- --------- -- --------------- --------- --- ---------------- --------- --------- --------------
FIRST_EWB_ID SECOND_WT SECOND_MAN_FLAG SECOND_EWB_ID IN_DATE FIRST_WT_ SECOND_WT FIRST_WT_
------------ --------- --------------- ------------- --------- --------- --------- ---------
SECOND_WT PU
--------- --
85 IS TN59H1143 61 2.78 0
1 7.84 0 1 22-AUG-11 22-AUG-11 22-AUG-11
22-AUG-11
real: 94
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=1 Bytes=101
)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T_EWB_DET' (TABLE) (Cost
=4 Card=1 Bytes=101)
2 1 INDEX (RANGE SCAN) OF 'PK_EWB_DET' (INDEX (UNIQUE)) (Cos
t=3 Card=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
1338 bytes sent via SQL*Net to client
423 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
When i run the sample query i takes about 30 to 40 second.
why what is the reason.
Please help in this regards
Kanish
|
|
|
|
|
|
|
|
|
|
Re: How improve the perfomance on form [message #520407 is a reply to message #520392] |
Mon, 22 August 2011 06:00 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
mm_kanish05
Messages: 493 Registered: January 2007 Location: Chennai
|
Senior Member |
![mm_kanish05%40yahoo.co.in](/forum/theme/orafaq/images/yahoo.png)
|
|
Dear Michel
As you suggested i done all the things and sql_trace with tk_prof output.
TKPROF: Release 10.1.0.2.0 - Production on Mon Aug 22 16:21:32 2011
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Trace file: d:\testing1\udump\testing1_ora_3316_mysqltrace.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
********************************************************************************
error connecting to database using: PIGEON/PIGEON
ORA-12560: TNS:protocol adapter error
EXPLAIN PLAN option disabled.
********************************************************************************
SELECT PMTRLCODE,PRD_CODE,MOVEMENT_TYPE,TRUCK_NO,CARR_CODE,SHIP_NO,DOC_NO,
DOC_QTY,FIRST_WT,FIRST_MAN_FLAG,FIRST_EWB_ID,SECOND_WT,SECOND_MAN_FLAG,
SECOND_EWB_ID,IN_DATE,FIRST_WT_TIME,SECOND_WT_TIME,FIRST_WT_DATE,
SECOND_WT_DATE,PUCODE
FROM
T_EWB_DET WHERE VEP_NO = :b1
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 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 4 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 73
********************************************************************************
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 2 0.00 0.20 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.20 0 0 0 0
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 73
********************************************************************************
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.20 0 0 0 0
Fetch 1 0.00 0.00 0 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.00 0.20 0 4 0 1
Misses in library cache during parse: 0
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 0 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
2 user SQL statements in session.
0 internal SQL statements in session.
2 SQL statements in session.
0 statements EXPLAINed in this session.
********************************************************************************
Trace file: d:\testing1\udump\testing1_ora_3316_mysqltrace.trc
Trace file compatibility: 10.01.00
Sort options: default
0 session in tracefile.
2 user SQL statements in trace file.
0 internal SQL statements in trace file.
2 SQL statements in trace file.
2 unique SQL statements in trace file.
18 lines in trace file.
1 elapsed seconds in trace file.
I am new to this area i dont know what now i want to do.
Please guide in this area.
kanish
|
|
|
Re: How improve the perfomance on form [message #520409 is a reply to message #520407] |
Mon, 22 August 2011 06:11 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
I don't know Forms and the problem is in Forms as the query executes well in SQL*Plus (and in your trace where it shows a response time of < 0.01s).
Wait a little bit, there are many Forms skilled people here that will ask you the good questions and provide you useful advices.
Regards
Michel
[Updated on: Mon, 22 August 2011 06:12] Report message to a moderator
|
|
|
|