| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: PL/SQL Wrapper Costs
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_at_mitre.org> 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
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.
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Jun 14 2007 - 10:30:46 CDT
 |
 |