RE: PL/SQL Wrapper Costs
- From: "Koppelaars, Toon" <T.Koppelaars@xxxxxxxxxxxxxxxxxxxx>
- To: <peters@xxxxxxxxx>
- Date: Thu, 14 Jun 2007 17:30:46 +0200
Peter,
The wait-time for "SQL-message from Client" reveals it, doesn't it?
Your client is too slow: it does not provide the database server with messages
fast enough, for it (the dbs-server) to complete the (anonymous block) pl/sql
request.
My hunch would be to look at the difference in CPU-speed of the client (running
the code that sends the pl/sql-block and needs to marshall the in/out
parameters) versus the CPU-speed of the server. Or maybe the client is
saturated (cpu-wise)...? Or maybe there is a serious network issue between this
client and the dbs-server.
Are the SQLnet versions (at client and dbs-server) in-sync?
Just my 2 cents on this.
Toon Koppelaars
On 6/13/07, Peter Sylvester <peters@xxxxxxxxx> wrote:
BEGIN "IDNT$U"."UNIFACE_IO"( :UNIFACE_IO_REQUEST, :XIDNT_ID, :XU_VERSION,
:XIDNT_XREF_ID, :XFOLDER_NBR, :XDETAINEE_ID, :XRECIDIVIST, :XPRFX_CD,
:XSFFX_CD, :XFIRST_NAME, :XMIDDLE_NAME, :XLAST_NAME, :XCOMPANY_NAME, :XSSN,
:XDOB, :XDOD, :XBIRTH_CNTY_CD, :XBIRTH_CITY_CD, :XBIRTH_ST_CD, :XSECRET,
:XINS_BY, :XINS_PRG, :XINS_DTTM, :XUPD_BY, :XUPD_PRG, :XUPD_DTTM,
:XCITY_TEXT, :XCOUNTY_TEXT, :XDRIVERS_LIC_NO, :XSID, :XBCI, :XFBI,
:XFULL_NAME_SOUNDEX, :XROWID, :WIDNT_ID, :WU_VERSION, :ONE_ROW_AFFECTED );
END;
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.00 0.00 0 0 0 0
Execute 1419 4.61 7.39 80 5676 0
1419
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1422 4.61 7.39 80 5676 0
1419
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 448
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1425 0.00 0.00
SQL*Net message from client 1425 3.86 6.99
latch: library cache 37 0.00 0.06
latch: shared pool 12 0.00 0.04
********************************************************************************
The select statement:
SELECT /*+ FIRST_ROWS */ "IDNT_ID", "U_VERSION", "IDNT_XREF_ID", "FOLDER_NBR",
"DETAINEE_ID", "RECIDIVIST", "PRFX_CD", "SFFX_CD", "FIRST_NAME",
"MIDDLE_NAME", "LAST_NAME", "COMPANY_NAME", "SSN", "DOB", "DOD",
"BIRTH_CNTY_CD", "BIRTH_CITY_CD", "BIRTH_ST_CD", "SECRET", "INS_BY",
"INS_PRG", "INS_DTTM", "UPD_BY", "UPD_PRG", "UPD_DTTM", "CITY_TEXT",
"COUNTY_TEXT", "DRIVERS_LIC_NO", "SID", "BCI", "FBI", "FULL_NAME_SOUNDEX"
FROM
"IDNT" WHERE "IDNT_ID" = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 8 0.00 0.00 0 0 0 0
Execute 1419 0.39 0.46 0 0 0 0
Fetch 1419 0.13 0.76 80 5676 0
1419
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2846 0.52 1.23 80 5676 0 1419
Misses in library cache during parse: 0
Optimizer mode: FIRST_ROWS
Parsing user id: 987 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
99 TABLE ACCESS BY INDEX ROWID IDNT (cr=396 pr=63 pw=0 time=457354 us)
99 INDEX UNIQUE SCAN IDNTP1 (cr=297 pr=29 pw=0 time=200551 us)(object id
285949)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 80 0.02 0.51
latch: library cache 10 0.00 0.02
latch: shared pool 22 0.00 0.04
********************************************************************************
thanks for any suggestions,
Peter Sylvester
MITRE Corp.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
