SQL*Net message to client
From: astalavista <nobody_at_nowhere.com>
Date: Thu, 30 Oct 2008 21:05:53 +0100
Message-ID: <490a13a1$0$7859$426a34cc@news.free.fr>
46 FROM dcv_thingdata t, pf_thingtype tt 47 WHERE t.thingtypeid = tt.thingtypeid
60 UNION
61 SELECT '<SYSCONFIG'
81 FROM dcv_thingdata t, pf_thingtype tt 82 WHERE t.thingtypeid = tt.thingtypeid
| 0 | SELECT STATEMENT | |
2 | 480 | 78 |
| 1 | SORT UNIQUE | |
2 | 480 | 78 |
| 2 | UNION-ALL | |
| | |
access("PF_THINGTYPEATTRIBUTE"."THINGTYPEID"="PF_THINGTYPE"."THINGTYPEID")
30 - filter(UPPER("TT"."TYPEDESCRIPTION")='SYSTEM CONFIG') 36 -
access("PF_THINGTYPEATTRIBUTE"."CHAPTERID"="PF_ITEMCONTEXT"."CHAPTERID" AND
access("PF_THINGTYPEATTRIBUTE"."THINGTYPEID"="PF_THINGTYPE"."THINGTYPEID") 47 - access("PF_THINGTYPE"."THINGTYPEID"="TT"."THINGTYPEID") 50 - access("PF_THING"."THINGID"=:B1)
Date: Thu, 30 Oct 2008 21:05:53 +0100
Message-ID: <490a13a1$0$7859$426a34cc@news.free.fr>
Hi,
the query below waits a lot of time on
"SQL*Net message to client" (20/30 min)
and after on "latch free "
do you know why ?
Thanks in advance
Oracle 9.2.0.6
bind 97: dty=2 mxl=22(02) mal=00 scl=00 pre=00 oacflg=10 oacfl2=100 size=24 offset=0
bfp=20f7c6b0 bln=22 avl=02 flg=09
value=10
bind 98: dty=1 mxl=32(13) mal=00 scl=00 pre=00 oacflg=10 oacfl2=100 size=32
offset=0
bfp=20f7c680 bln=32 avl=13 flg=09
value="SYSTEM CONFIG"
bind 99: dty=1 mxl=32(15) mal=00 scl=00 pre=00 oacflg=10 oacfl2=100 size=32
offset=0
bfp=20f7c650 bln=32 avl=15 flg=09
value="TRIALDATEFORMAT"
EXEC #1:c=0,e=5431,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=10155724401
WAIT #1: nam='SQL*Net message to client' ela= 6 p1=1413697536 p2=1 p3=0
*** 2008-10-30 16:11:06.873
FETCH
#1:c=217406250,e=217753841,p=0,cr=16176489,cu=0,mis=0,r=0,dep=0,og=4,tim=10373478359
t57> explain plan for
2 SELECT '<SYSCONFIG'
3 || CHR (10) 4 || ' CONFIGNAME="' 5 || DECODE (attributename, 6 'ALLOWCPRESIZE', 'AllowCPResize', 7 'ALLOWPASSWORDREUSE', 'AllowPasswordReuse', 8 'AUTOANSWERMANUALQUERIES', 'AutoAnswerManualQueries', 9 'DAYSPASSWORDEXPIRATION', 'DaysPasswordExpiration', 10 'DEFAULTCPMAXIMIZED', 'DEFAULTCPMAXIMIZED', 11 'EMAILFORFORGOTPASSWORDNOTIFICATION', 'EmailForForgotPasswordNotification', 12 'EMAILFORNEWSITEANDUSERNOTIFICATION', 'EmailForNewSiteAndUserNotification', 13 'ENABLEFORGOTPASSWORD', 'EnableForgotPassword', 14 'ENFORCEVISITDATE', 'EnforceVisitDate', 15 'ENROLLWITHINCOMPLETEFORMS', 'EnrollWithIncompleteForms', 16 'INACTIVATERETRYCOUNT', 'InactivateRetryCount', 17 'MAXNUMOFRESUBMISSIONS', 'MaxNumOfResubmissions', 18 'MINPASSWORDLENGTH', 'MinPasswordLength', 19 'MINUTESREAUTHENTICATE', 'MinutesReauthenticate', 20 'MINUTESREIDENTIFICATION', 'MinutesReIdentification', 21 'NAVIGATIONMODE', 'NavigationMode', 22 'NUMCHARSCRFLENGTH', 'NumCharsCRFLength', 23 'NUMOFEXEPLANLISTENTHREADS', 'NumOfExePlanListenThreads', 24 'ONENONALPHANUMERICCHARACTER', 'OneNonAlphaNumericCharacter', 25 'ONENUMERICALCHARACTER', 'OneNumericalCharacter', 26 'ONEUPPERCASECHARACTER', 'OneUppercaseCharacter', 27 'POSTQUERYFORCONFLICTRESOLUTION', 'PostQueryForConflictResolution', 28 'QUERYMAXLENGTH', 'QueryMaxLength', 29 'RANDOMIZATIONSRC', 'RandomizationSrc', 30 'RANDSIMPLECENTRAL', 'RandSimpleCentral', 31 'REQUIRECOMMENTFORNA', 'RequireCommentForNA', 32 'SSLFLAG', 'SSLFlag', 33 'TRIALDATEFORMAT', 'TrialDateFormat', 34 'VIEWCRFSIGNLIST', 'ViewCRFSignList', 35 attributename 36 ) 37 || '"' 38 || CHR (10) 39 || ' VALUE="' 40 || DECODE (strvalue, NULL, TO_CHAR (numvalue), strvalue) 41 || '"' 42 || CHR (10) 43 || ' TYPE="0' 44 || '"/>' 45 || CHR (10)
46 FROM dcv_thingdata t, pf_thingtype tt 47 WHERE t.thingtypeid = tt.thingtypeid
48 AND UPPER (tt.typedescription) = 'SYSTEM CONFIG'
49 AND attributename NOT IN
50 ('COOKSERVER',
51 'EXEPLANSERVER',
52 'LDAPBDN',
53 'LDAPSERVER',
54 'REPORTINGAUTHENTICATIONNAMESPACE',
55 'REPORTINGSERVER',
56 'REPORTINGUSERROOT',
57 'SSLFLAG',
58 'TRIALDATEFORMAT'
59 )
60 UNION
61 SELECT '<SYSCONFIG'
62 || CHR (10) 63 || ' CONFIGNAME="' 64 || DECODE (attributename, 65 'TRIALDATEFORMAT', 'TrialDateFormat', 66 attributename 67 ) 68 || '"' 69 || CHR (10) 70 || ' VALUE="' 71 || DECODE (numvalue, 72 0, 'MONTH_DAY_YEAR', 73 1, 'DAY_MONTH_YEAR', 74 2, 'YEAR_MONTH_DAY' 75 ) 76 || '"' 77 || CHR (10) 78 || ' TYPE="0' 79 || '"/>' 80 || CHR (10)
81 FROM dcv_thingdata t, pf_thingtype tt 82 WHERE t.thingtypeid = tt.thingtypeid
83 AND UPPER (tt.typedescription) = 'SYSTEM CONFIG' 84 AND attributename = 'TRIALDATEFORMAT';
Explained.
Elapsed: 00:00:00.02
t57> t57> t57> select * from table(dbms_xplan.display); --------------------------------------------------------------------------------------------------Rows | Bytes | Cost |
| Id | Operation | Name |
| 0 | SELECT STATEMENT | |
2 | 480 | 78 |
| 1 | SORT UNIQUE | |
2 | 480 | 78 |
| 2 | UNION-ALL | |
| | |
|* 3 | FILTER | |
| | |
| 4 | SORT GROUP BY | |
1 | 240 | 37 | |* 5 | FILTER | |
| | |
|* 6 | TABLE ACCESS BY INDEX ROWID | PF_THINGTYPE | 1 | 19 | 2 |
| 7 | NESTED LOOPS | |
1 | 240 | 32 |
| 8 | NESTED LOOPS | |
1 | 221 | 30 |
| 9 | NESTED LOOPS | |
1 | 166 | 29 |
| 10 | NESTED LOOPS | |
1 | 137 | 26 |
| 11 | NESTED LOOPS | |
1 | 123 | 24 | |* 12 | HASH JOIN | | 1 | 101 | 22 | |* 13 | TABLE ACCESS FULL | PF_THINGTYPEATTRIBUTE | 160 | 5920 | 2 |
| 14 | TABLE ACCESS BY INDEX ROWID| PF_ITEMCONTEXT |
214 | 6848 | 17 |
| 15 | NESTED LOOPS | |
233 | 14912 | 19 |
| 16 | INDEX FAST FULL SCAN | XPKPF_THING |
1 | 32 | 2 | |* 17 | INDEX RANGE SCAN | XIE5PF_ITEMCONTEXT | 214 | | 2 | |* 18 | INDEX RANGE SCAN | XPKPF_ITEMDATA | 2 | 44 | 2 | |* 19 | INDEX RANGE SCAN | XPKPF_ITEMDATA | 2 | 28 | 2 |
| 20 | TABLE ACCESS BY INDEX ROWID | PF_CONTROLDATA |
1 | 29 | 3 | |* 21 | INDEX RANGE SCAN | XIF16PF_CONTROLDATA | 1 | | 2 | |* 22 | INDEX RANGE SCAN | XPKPF_THINGTYPE | 1 | 55 | 1 | |* 23 | INDEX RANGE SCAN | XPKPF_THINGTYPE | 1 | | 1 |
| 24 | SORT AGGREGATE | |
1 | 14 | |
| 25 | FIRST ROW | |
1 | 14 | 2 | |* 26 | INDEX RANGE SCAN (MIN/MAX) | XPKPF_THING | 1584 | | 2 | |* 27 | FILTER | |
| | |
| 28 | SORT GROUP BY | |
1 | 240 | 37 | |* 29 | FILTER | |
| | |
|* 30 | TABLE ACCESS BY INDEX ROWID | PF_THINGTYPE | 1 | 19 | 2 |
| 31 | NESTED LOOPS | |
1 | 240 | 32 |
| 32 | NESTED LOOPS | |
1 | 221 | 30 |
| 33 | NESTED LOOPS | |
1 | 166 | 29 |
| 34 | NESTED LOOPS | |
1 | 137 | 26 |
| 35 | NESTED LOOPS | |
1 | 123 | 24 | |* 36 | HASH JOIN | | 1 | 101 | 22 | |* 37 | TABLE ACCESS FULL | PF_THINGTYPEATTRIBUTE | 1 | 37 | 2 |
| 38 | TABLE ACCESS BY INDEX ROWID| PF_ITEMCONTEXT |
214 | 6848 | 17 |
| 39 | NESTED LOOPS | |
233 | 14912 | 19 |
| 40 | INDEX FAST FULL SCAN | XPKPF_THING |
1 | 32 | 2 | |* 41 | INDEX RANGE SCAN | XIE5PF_ITEMCONTEXT | 214 | | 2 | |* 42 | INDEX RANGE SCAN | XPKPF_ITEMDATA | 2 | 44 | 2 | |* 43 | INDEX RANGE SCAN | XPKPF_ITEMDATA | 2 | 28 | 2 |
| 44 | TABLE ACCESS BY INDEX ROWID | PF_CONTROLDATA |
1 | 29 | 3 | |* 45 | INDEX RANGE SCAN | XIF16PF_CONTROLDATA | 1 | | 2 | |* 46 | INDEX RANGE SCAN | XPKPF_THINGTYPE | 1 | 55 | 1 | |* 47 | INDEX RANGE SCAN | XPKPF_THINGTYPE | 1 | | 1 |
| 48 | SORT AGGREGATE | |
1 | 14 | |
| 49 | FIRST ROW | |
1 | 14 | 2 | |* 50 | INDEX RANGE SCAN (MIN/MAX) | XPKPF_THING | 1584 | | 2 | --------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
3 - filter("PF_ITEMDATA"."AUDITORDER"=MAX("A"."AUDITORDER")) 5 - filter("SYS_ALIAS_1"."THINGREVISIONNUMBER"= (SELECT /*+ */
MAX("PF_THING"."THINGREVISIONNUMBER") FROM "PF_THING"
"PF_THING" WHERE "PF_THING"."THINGID"=:
6 - filter(UPPER("TT"."TYPEDESCRIPTION")='SYSTEM CONFIG')
12 -
access("PF_THINGTYPEATTRIBUTE"."CHAPTERID"="PF_ITEMCONTEXT"."CHAPTERID" AND
"PF_THINGTYPEATTRIBUTE"."PAGEID"="PF_ITEMCONTEXT"."PAGEID" AND
"PF_THINGTYPEATTRIBUTE"."SECTIONID"="PF_ITEMCONTEXT"."SECTIONID"
AND
"PF_THINGTYPEATTRIBUTE"."ITEMID"="PF_ITEMCONTEXT"."ITEMID")
13 - filter("PF_THINGTYPEATTRIBUTE"."ATTRIBUTENAME"<>'COOKSERVER' AND
"PF_THINGTYPEATTRIBUTE"."ATTRIBUTENAME"<>'EXEPLANSERVER' AND
"PF_THINGTYPEATTRIBUTE"."ATTRIBUTENAME"<>'LDAPBDN' AND
"PF_THINGTYPEATTRIBUTE"."ATTRIBUTENAME"<>'LDAPSERVER' AND
"PF_THINGTYPEATTRIBUTE"."ATTRIBUTENAME"<>'REPORTINGAUTHENTICATIONNAMESPACE'
AND
"PF_THINGTYPEATTRIBUTE"."ATTRIBUTENAME"<>'REPORTINGSERVER' AND
"PF_THINGTYPEATTRIBUTE"."ATTRIBUTENAME"<>'REPORTINGUSERROOT'
AND
"PF_THINGTYPEATTRIBUTE"."ATTRIBUTENAME"<>'SSLFLAG' AND
"PF_THINGTYPEATTRIBUTE"."ATTRIBUTENAME"<>'TRIALDATEFORMAT')
17 - access("PF_ITEMCONTEXT"."SUBJECTKEYID"="SYS_ALIAS_1"."THINGID")
18 - access("PF_ITEMDATA"."CONTEXTID"="PF_ITEMCONTEXT"."CONTEXTID" AND
"PF_ITEMDATA"."INVALIDATINGREVISIONNUMBER"=0)
filter("PF_ITEMDATA"."INVALIDATINGREVISIONNUMBER"=0)
19 - access("A"."CONTEXTID"="PF_ITEMCONTEXT"."CONTEXTID")
21 - access("PF_ITEMDATA"."CONTEXTID"="PF_CONTROLDATA"."CONTEXTID" AND
"PF_ITEMDATA"."AUDITORDER"="PF_CONTROLDATA"."AUDITORDER" AND
"PF_CONTROLDATA"."INVALIDATINGREVISIONNUMBER"=0)
22 -
access("PF_THINGTYPEATTRIBUTE"."THINGTYPEID"="PF_THINGTYPE"."THINGTYPEID")
23 - access("PF_THINGTYPE"."THINGTYPEID"="TT"."THINGTYPEID")
26 - access("PF_THING"."THINGID"=:B1)
27 - filter("PF_ITEMDATA"."AUDITORDER"=MAX("A"."AUDITORDER"))
29 - filter("SYS_ALIAS_1"."THINGREVISIONNUMBER"= (SELECT /*+ */
MAX("PF_THING"."THINGREVISIONNUMBER") FROM "PF_THING"
"PF_THING" WHERE "PF_THING"."THINGID"=:
30 - filter(UPPER("TT"."TYPEDESCRIPTION")='SYSTEM CONFIG') 36 -
access("PF_THINGTYPEATTRIBUTE"."CHAPTERID"="PF_ITEMCONTEXT"."CHAPTERID" AND
"PF_THINGTYPEATTRIBUTE"."PAGEID"="PF_ITEMCONTEXT"."PAGEID" AND
"PF_THINGTYPEATTRIBUTE"."SECTIONID"="PF_ITEMCONTEXT"."SECTIONID"
AND
"PF_THINGTYPEATTRIBUTE"."ITEMID"="PF_ITEMCONTEXT"."ITEMID")
37 - filter("PF_THINGTYPEATTRIBUTE"."ATTRIBUTENAME"='TRIALDATEFORMAT')
41 - access("PF_ITEMCONTEXT"."SUBJECTKEYID"="SYS_ALIAS_1"."THINGID")
42 - access("PF_ITEMDATA"."CONTEXTID"="PF_ITEMCONTEXT"."CONTEXTID" AND
"PF_ITEMDATA"."INVALIDATINGREVISIONNUMBER"=0)
filter("PF_ITEMDATA"."INVALIDATINGREVISIONNUMBER"=0)
43 - access("A"."CONTEXTID"="PF_ITEMCONTEXT"."CONTEXTID")
45 - access("PF_ITEMDATA"."CONTEXTID"="PF_CONTROLDATA"."CONTEXTID" AND
"PF_ITEMDATA"."AUDITORDER"="PF_CONTROLDATA"."AUDITORDER" AND
"PF_CONTROLDATA"."INVALIDATINGREVISIONNUMBER"=0)
46 -
access("PF_THINGTYPEATTRIBUTE"."THINGTYPEID"="PF_THINGTYPE"."THINGTYPEID") 47 - access("PF_THINGTYPE"."THINGTYPEID"="TT"."THINGTYPEID") 50 - access("PF_THING"."THINGID"=:B1)
Note: cpu costing is off
110 rows selected.
Elapsed: 00:00:00.03
t57>
Received on Thu Oct 30 2008 - 15:05:53 CDT