Home » RDBMS Server » Performance Tuning » How to judge the slow query? (Windows 2003 SP2 Oracle 10.2.0.4 32-bit)
| How to judge the slow query? [message #403978] |
Tue, 19 May 2009 23:23  |
crazypeter2005
Messages: 29
Registered: September 2008
Location: Peking China
|
Junior Member |
|
|
The slow query:
1
SQL Stmt: <SELECT /*+ FIRST_ROWS (500) */ T.* FROM (SELECT DISTINCT GPSFolder_1.ITEMID, GPSFolder_1.COMPONENTID, GPSFolder_1.VERSIONID, 2343 AS COMPONENTTYPEID, 1533 AS ITEMTYPEID FROM ICMUT02343001 GPSFolder_1 WHERE (GPSFolder_1.ATTR0000002084 = 'GPSFolderDataD00{GPSROOT}') AND (((( ( (EXISTS (SELECT 1 FROM ICMSTCOMPILEDACL C , ICMSTITVIEWDEFS V WHERE 1533=V.ITEMTYPEID AND V.ITEMTYPEVIEWID IN (1533) AND (C.ACL = V.ACLCODE OR C.ACL = -1) AND C.UNUM=2 AND C.RPRIV='1' )) OR (EXISTS (SELECT 1 FROM ICMSTCOMPILEDACL C, ICMSTCOMPILEDPERM P , ICMSTITVIEWDEFS V WHERE 1533=V.ITEMTYPEID AND V.ITEMTYPEVIEWID IN (1533) AND (C.ACL = V.ACLCODE OR C.ACL = -1) AND C.UNUM=1 AND C.RPRIV='1' AND P.PRIVDEFCODE=121 AND P.UNUM=2 )) )))))) T , ICMSTITEMS001001 I WHERE T.ITEMID = I.ITEMID AND T.VERSIONID = I.VERSIONID>
2
SQL Stmt: <SELECT /*+ FIRST_ROWS (500) */ T.* FROM (SELECT DISTINCT ItemVersions_1.ITEMID, ItemVersions_1.COMPONENTID, ItemVersions_1.VERSIONID, ItemVersions_1.COMPONENTTYPEID, ItemVersions_1.ITEMTYPEID FROM ICMSVALLITEM001001 ItemVersions_1, ICMSTLINKS001001 Links_4, ICMSTNLSKEYWORDS NLSKeywords_6 WHERE ((ItemVersions_1.SEMANTICTYPE IN (2, 1000)) AND (((ItemVersions_1.ITEMID = Links_4.TARGETITEMID) AND ((((Links_4.LINKTYPE = NLSKeywords_6.KEYWORDCODE) AND (4 = NLSKeywords_6.KEYWORDCLASS)) AND ('ENU' = NLSKeywords_6.LANGUAGECODE)) AND (NLSKeywords_6.KEYWORDNAME = 'Contains'))) AND (Links_4.SOURCEITEMID = 'A1001001A09D21B65355H33550'))) AND ((( (ItemVersions_1.ITEMTYPEID IN (300, 301, 302, 303, 304, 400, 1000, 1001, 1002, 1003, 1004, 1005, 1006, 1007, 1008, 1009, 1010, 1011, 1012, 1013, 1014, 1015, 1016, 1017, 1018, 1019, 1020, 1021, 1022, 1023, 1024, 1025, 1026, 1027, 1028, 1030, 1031, 1532, 1533, 1534, 1535, 1537, 1538, 1539, 1540, 1541, 1542, 1543, 1544, 1545, 1546, 1547, 1548, 1549, 1550, 1551, 1552, 1553, 1556, 1557, 1558, 1559, 1560, 1561, 1562, 1563) AND ( (EXISTS (SELECT 1 FROM ICMSTCOMPILEDACL C , ICMSTITVIEWDEFS V WHERE ItemVersions_1.ITEMTYPEID=V.ITEMTYPEID AND V.ITEMTYPEVIEWID IN (300, 301, 302, 303, 304, 400, 1000, 1001, 1002, 1003, 1004, 1005, 1006, 1007, 1008, 1009, 1010, 1011, 1012, 1013, 1014, 1015, 1016, 1017, 1018, 1019, 1020, 1021, 1022, 1023, 1024, 1025, 1026, 1027, 1028, 1030, 1031, 1532, 1533, 1534, 1535, 1537, 1538, 1539, 1540, 1541, 1542, 1543, 1544, 1545, 1546, 1547, 1548, 1549, 1550, 1551, 1552, 1553, 1556, 1557, 1558, 1559, 1560, 1561, 1562, 1563) AND (C.ACL = V.ACLCODE OR C.ACL = -1) AND C.UNUM=2 AND C.RPRIV='1' )) OR (EXISTS (SELECT 1 FROM ICMSTCOMPILEDACL C, ICMSTCOMPILEDPERM P , ICMSTITVIEWDEFS V WHERE ItemVersions_1.ITEMTYPEID=V.ITEMTYPEID AND V.ITEMTYPEVIEWID IN (300, 301, 302, 303, 304, 400, 1000, 1001, 1002, 1003, 1004, 1005, 1006, 1007, 1008, 1009, 1010, 1011, 1012, 1013, 1014, 1015, 1016, 1017, 1018, 1019, 1020, 1021, 1022, 1023, 1024, 1025, 1026, 1027, 1028, 1030, 1031, 1532, 1533, 1534, 1535, 1537, 1538, 1539, 1540, 1541, 1542, 1543, 1544, 1545, 1546, 1547, 1548, 1549, 1550, 1551, 1552, 1553, 1556, 1557, 1558, 1559, 1560, 1561, 1562, 1563) AND (C.ACL = V.ACLCODE OR C.ACL = -1) AND C.UNUM=1 AND C.RPRIV='1' AND P.PRIVDEFCODE=121 AND P.UNUM=2 )) )) OR (ItemVersions_1.ITEMTYPEID IN (200, 201, 202, 600, 1536, 1554, 1555, 1564, 1565, 1566) AND ( (EXISTS (SELECT 1 FROM ICMSTCOMPILEDACL C WHERE (C.ACL = ItemVersions_1.ACLCODE OR C.ACL = -1) AND C.UNUM=2 AND C.RPRIV='1' )) OR (EXISTS (SELECT 1 FROM ICMSTCOMPILEDACL C, ICMSTCOMPILEDPERM P WHERE (C.ACL = ItemVersions_1.ACLCODE OR C.ACL = -1) AND C.UNUM=1 AND C.RPRIV='1' AND P.PRIVDEFCODE=121 AND P.UNUM=2 )) )))))) T , ICMSTITEMS001001 I WHERE T.ITEMID = I.ITEMID AND T.VERSIONID = I.VERSIONID>
Client Log:
ICMPLSLU ICMLISTUSER 00122 05/19 07:23:39.187 GMT ; AUTHOR2000 Entry
ICMPLSGU ICMLISTGRPFORUSER 00117 05/19 07:23:39.203 GMT ; AUTHOR2000 Entry
ICMPLSLP ICMLISTPRIVILEGE 00108 05/19 07:23:39.218 GMT ; AUTHOR2000 Entry
ICMPLSLP ICMLISTPRIVILEGE 00110 05/19 07:23:39.234 GMT ; AUTHOR2000 From SP
ICMPLSLU ICMLISTUSER 00122 05/19 07:23:39.250 GMT ; AUTHOR2000 Entry
ICMPLSPI ICMLISTPRIVINSET 00100 05/19 07:23:39.265 GMT ; AUTHOR2000 Entry
ICMPLSQU ICMSEARCH 00328 05/19 07:23:39.625 GMT ; AUTHOR2000 Entry
ICMPLSQU ICMSEARCH 00466 05/19 07:23:39.640 GMT ; AUTHOR2000 3 msec
ICMPLSQU ICMSEARCH 00328 05/19 07:23:46.390 GMT ; AUTHOR2000 Entry
You can see Obvious wait here.
Line 1323 : [05/19/2009 08:11:37.281] 1328 D: Creating file mutex: 'gpsfmtx_c:/progra~1/ibm/db2doc~1/dmserver/cache/libs/cm/13_views/13_dm_view.dat'
Line 1371 : [05/19/2009 08:11:37.468] 1328 D: Found the datastorepool at index 0 for the datastore name svtlsdb
Line 1408 : [05/19/2009 08:11:37.468] 1328 D: Item key: GPSFolderDataD00{GPSROOT}
Line 1410 : [05/19/2009 08:11:37.468] 1328 D: Evaluating root folder query: '/GPSFolder [@GPSObjKey = "GPSFolderDataD00{GPSROOT}"]'
Line 1411 : [05/19/2009 08:11:44.453] 1328 D: Root folder not found in 6985ms
Line 1412 : [05/19/2009 08:11:44.453] 1328 D: Number returned: 0
Line 1463 : [05/19/2009 08:11:44.515] 1328 D: Found the datastorepool at index 0 for the datastore name svtlsdb
Line 1498 : [05/19/2009 08:11:44.531] 1328 D: Get folder list command
Line 1501 : [05/19/2009 08:11:44.531] 1328 D: Item key: GPSFolderDataD00{GPSROOT}
Line 1503 : [05/19/2009 08:11:44.531] 1328 D: Evaluating root folder query: '/GPSFolder [@GPSObjKey = "GPSFolderDataD00{GPSROOT}"]'
Line 1504 : [05/19/2009 08:11:50.937] 1328 D: Root folder not found in 6406ms
You can see each Xquery takes 6 seconds
Attachement is Statpack，Please take a look at it。
It is the programe to translate the XQuery to SQL, and it is horrible.
Any suggestings?
[Updated on: Wed, 20 May 2009 01:19] by Moderator Report message to a moderator |
|
|
|
| How to judge the slow query? [message #403981 is a reply to message #403978] |
Tue, 19 May 2009 23:28  |
crazypeter2005
Messages: 29
Registered: September 2008
Location: Peking China
|
Junior Member |
|
|
最近客户的机器发生了很奇怪的现象
后端是Oracle10.2.0.4的数据库，前段是某个MIS系统任何用户登陆都很慢。
取到SQL语句和Statpack结果
1
SQL Stmt: <SELECT /*+ FIRST_ROWS (500) */ T.* FROM (SELECT DISTINCT GPSFolder_1.ITEMID, GPSFolder_1.COMPONENTID, GPSFolder_1.VERSIONID, 2343 AS COMPONENTTYPEID, 1533 AS ITEMTYPEID FROM ICMUT02343001 GPSFolder_1 WHERE (GPSFolder_1.ATTR0000002084 = 'GPSFolderDataD00{GPSROOT}') AND (((( ( (EXISTS (SELECT 1 FROM ICMSTCOMPILEDACL C , ICMSTITVIEWDEFS V WHERE 1533=V.ITEMTYPEID AND V.ITEMTYPEVIEWID IN (1533) AND (C.ACL = V.ACLCODE OR C.ACL = -1) AND C.UNUM=2 AND C.RPRIV='1' )) OR (EXISTS (SELECT 1 FROM ICMSTCOMPILEDACL C, ICMSTCOMPILEDPERM P , ICMSTITVIEWDEFS V WHERE 1533=V.ITEMTYPEID AND V.ITEMTYPEVIEWID IN (1533) AND (C.ACL = V.ACLCODE OR C.ACL = -1) AND C.UNUM=1 AND C.RPRIV='1' AND P.PRIVDEFCODE=121 AND P.UNUM=2 )) )))))) T , ICMSTITEMS001001 I WHERE T.ITEMID = I.ITEMID AND T.VERSIONID = I.VERSIONID>
2
SQL Stmt: <SELECT /*+ FIRST_ROWS (500) */ T.* FROM (SELECT DISTINCT ItemVersions_1.ITEMID, ItemVersions_1.COMPONENTID, ItemVersions_1.VERSIONID, ItemVersions_1.COMPONENTTYPEID, ItemVersions_1.ITEMTYPEID FROM ICMSVALLITEM001001 ItemVersions_1, ICMSTLINKS001001 Links_4, ICMSTNLSKEYWORDS NLSKeywords_6 WHERE ((ItemVersions_1.SEMANTICTYPE IN (2, 1000)) AND (((ItemVersions_1.ITEMID = Links_4.TARGETITEMID) AND ((((Links_4.LINKTYPE = NLSKeywords_6.KEYWORDCODE) AND (4 = NLSKeywords_6.KEYWORDCLASS)) AND ('ENU' = NLSKeywords_6.LANGUAGECODE)) AND (NLSKeywords_6.KEYWORDNAME = 'Contains'))) AND (Links_4.SOURCEITEMID = 'A1001001A09D21B65355H33550'))) AND ((( (ItemVersions_1.ITEMTYPEID IN (300, 301, 302, 303, 304, 400, 1000, 1001, 1002, 1003, 1004, 1005, 1006, 1007, 1008, 1009, 1010, 1011, 1012, 1013, 1014, 1015, 1016, 1017, 1018, 1019, 1020, 1021, 1022, 1023, 1024, 1025, 1026, 1027, 1028, 1030, 1031, 1532, 1533, 1534, 1535, 1537, 1538, 1539, 1540, 1541, 1542, 1543, 1544, 1545, 1546, 1547, 1548, 1549, 1550, 1551, 1552, 1553, 1556, 1557, 1558, 1559, 1560, 1561, 1562, 1563) AND ( (EXISTS (SELECT 1 FROM ICMSTCOMPILEDACL C , ICMSTITVIEWDEFS V WHERE ItemVersions_1.ITEMTYPEID=V.ITEMTYPEID AND V.ITEMTYPEVIEWID IN (300, 301, 302, 303, 304, 400, 1000, 1001, 1002, 1003, 1004, 1005, 1006, 1007, 1008, 1009, 1010, 1011, 1012, 1013, 1014, 1015, 1016, 1017, 1018, 1019, 1020, 1021, 1022, 1023, 1024, 1025, 1026, 1027, 1028, 1030, 1031, 1532, 1533, 1534, 1535, 1537, 1538, 1539, 1540, 1541, 1542, 1543, 1544, 1545, 1546, 1547, 1548, 1549, 1550, 1551, 1552, 1553, 1556, 1557, 1558, 1559, 1560, 1561, 1562, 1563) AND (C.ACL = V.ACLCODE OR C.ACL = -1) AND C.UNUM=2 AND C.RPRIV='1' )) OR (EXISTS (SELECT 1 FROM ICMSTCOMPILEDACL C, ICMSTCOMPILEDPERM P , ICMSTITVIEWDEFS V WHERE ItemVersions_1.ITEMTYPEID=V.ITEMTYPEID AND V.ITEMTYPEVIEWID IN (300, 301, 302, 303, 304, 400, 1000, 1001, 1002, 1003, 1004, 1005, 1006, 1007, 1008, 1009, 1010, 1011, 1012, 1013, 1014, 1015, 1016, 1017, 1018, 1019, 1020, 1021, 1022, 1023, 1024, 1025, 1026, 1027, 1028, 1030, 1031, 1532, 1533, 1534, 1535, 1537, 1538, 1539, 1540, 1541, 1542, 1543, 1544, 1545, 1546, 1547, 1548, 1549, 1550, 1551, 1552, 1553, 1556, 1557, 1558, 1559, 1560, 1561, 1562, 1563) AND (C.ACL = V.ACLCODE OR C.ACL = -1) AND C.UNUM=1 AND C.RPRIV='1' AND P.PRIVDEFCODE=121 AND P.UNUM=2 )) )) OR (ItemVersions_1.ITEMTYPEID IN (200, 201, 202, 600, 1536, 1554, 1555, 1564, 1565, 1566) AND ( (EXISTS (SELECT 1 FROM ICMSTCOMPILEDACL C WHERE (C.ACL = ItemVersions_1.ACLCODE OR C.ACL = -1) AND C.UNUM=2 AND C.RPRIV='1' )) OR (EXISTS (SELECT 1 FROM ICMSTCOMPILEDACL C, ICMSTCOMPILEDPERM P WHERE (C.ACL = ItemVersions_1.ACLCODE OR C.ACL = -1) AND C.UNUM=1 AND C.RPRIV='1' AND P.PRIVDEFCODE=121 AND P.UNUM=2 )) )))))) T , ICMSTITEMS001001 I WHERE T.ITEMID = I.ITEMID AND T.VERSIONID = I.VERSIONID>
客户端的登录都发现就是这几个SQL很慢的原因
ICMPLSLU ICMLISTUSER 00122 05/19 07:23:39.187 GMT ; AUTHOR2000 Entry
ICMPLSGU ICMLISTGRPFORUSER 00117 05/19 07:23:39.203 GMT ; AUTHOR2000 Entry
ICMPLSLP ICMLISTPRIVILEGE 00108 05/19 07:23:39.218 GMT ; AUTHOR2000 Entry
ICMPLSLP ICMLISTPRIVILEGE 00110 05/19 07:23:39.234 GMT ; AUTHOR2000 From SP
ICMPLSLU ICMLISTUSER 00122 05/19 07:23:39.250 GMT ; AUTHOR2000 Entry
ICMPLSPI ICMLISTPRIVINSET 00100 05/19 07:23:39.265 GMT ; AUTHOR2000 Entry
ICMPLSQU ICMSEARCH 00328 05/19 07:23:39.625 GMT ; AUTHOR2000 Entry
ICMPLSQU ICMSEARCH 00466 05/19 07:23:39.640 GMT ; AUTHOR2000 3 msec
ICMPLSQU ICMSEARCH 00328 05/19 07:23:46.390 GMT ; AUTHOR2000 Entry
可以看到有明显的等待，都是6s.
Line 1323 : [05/19/2009 08:11:37.281] 1328 D: Creating file mutex: 'gpsfmtx_c:/progra~1/ibm/db2doc~1/dmserver/cache/libs/cm/13_views/13_dm_view.dat'
Line 1371 : [05/19/2009 08:11:37.468] 1328 D: Found the datastorepool at index 0 for the datastore name svtlsdb
Line 1408 : [05/19/2009 08:11:37.468] 1328 D: Item key: GPSFolderDataD00{GPSROOT}
Line 1410 : [05/19/2009 08:11:37.468] 1328 D: Evaluating root folder query: '/GPSFolder [@GPSObjKey = "GPSFolderDataD00{GPSROOT}"]'
Line 1411 : [05/19/2009 08:11:44.453] 1328 D: Root folder not found in 6985ms
Line 1412 : [05/19/2009 08:11:44.453] 1328 D: Number returned: 0
Line 1463 : [05/19/2009 08:11:44.515] 1328 D: Found the datastorepool at index 0 for the datastore name svtlsdb
Line 1498 : [05/19/2009 08:11:44.531] 1328 D: Get folder list command
Line 1501 : [05/19/2009 08:11:44.531] 1328 D: Item key: GPSFolderDataD00{GPSROOT}
Line 1503 : [05/19/2009 08:11:44.531] 1328 D: Evaluating root folder query: '/GPSFolder [@GPSObjKey = "GPSFolderDataD00{GPSROOT}"]'
Line 1504 : [05/19/2009 08:11:50.937] 1328 D: Root folder not found in 6406ms
可以看到每个Xquery需要6秒，解析成的SQL在最上面
附件是Statpack，大牛们给看看吧。
这是程序自动把Xquery解析成的SQL，是很恐怖的，大家看看有啥建议啊？
|
|
|
|
|
|
|
|
|
|
| Re: How to judge the slow query? [message #403987 is a reply to message #403981] |
Tue, 19 May 2009 23:37 |
crazypeter2005
Messages: 29
Registered: September 2008
Location: Peking China
|
Junior Member |
|
|
The horrible SQL is parsed by the Client programe
Using Xquery
'/GPSFolder [@GPSObjKey = "GPSFolderDataD00{GPSROOT}"]'
The client login and query a Folder in the MIS called "Root Folder"
and then Query again to check if it contains other folder.
So the query will happen twice.
Same Xquery on DB2 backend will only take 2s but in Oracle Backend it will take 6s for each and doulbe should be 12s.
|
|
|
|
|
|
|
|
| Re: How to judge the slow query? [message #404090 is a reply to message #403978] |
Wed, 20 May 2009 05:23 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
1) Can you please read the orafaq forum guide that Blackswan has directed you to, especially the bit about code tags. Those queries are unreadable as they stand.
2) Then have a read of the performance tuning sticky at the top of this forum.
3) don't attach zip files - most people will not download them.
4) If you put up formatted queries that we can read along with explain plans we'll take a look.
5) Are both queries running slow?
|
|
|
|
| Re: How to judge the slow query? [message #404261 is a reply to message #403978] |
Thu, 21 May 2009 02:35 |
crazypeter2005
Messages: 29
Registered: September 2008
Location: Peking China
|
Junior Member |
|
|
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------
Plan hash value: 3938627814
--------------------------------------------------------------------------------
-----------------------
| Id | Operation | Name | Rows | Bytes |
Cost (%CPU)| Time |
--------------------------------------------------------------------------------
-----------------------
| 0 | SELECT STATEMENT | | 502 | 47690 |
13 (0)| 00:00:01 |
|* 1 | FILTER | | | |
| |
|* 2 | INDEX RANGE SCAN | ICMUX02343001001U | 502 | 47690 |
13 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 22 |
2 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID | ICMSTITVIEWDEFS | 1 | 12 |
1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | SYS_C004533 | 1 | |
0 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | ICMSXCOMPILEDACL1X | 1 | 10 |
1 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 1 | 30 |
2 (0)| 00:00:01 |
| 8 | NESTED LOOPS | | 1 | 20 |
1 (0)| 00:00:01 |
|* 9 | TABLE ACCESS BY INDEX ROWID| ICMSTITVIEWDEFS | 1 | 12 |
1 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | SYS_C004533 | 1 | |
0 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | ICMSXCOMPILEDPRM1U | 1 | 8 |
0 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | ICMSXCOMPILEDACL1X | 1 | 10 |
1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
-----------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT /*+ */ 0 FROM "ICMSTITVIEWDEFS" "V","ICMSTCOMPILED
ACL" "C" WHERE
"C"."UNUM"=2 AND "C"."RPRIV"='1' AND ("C"."ACL"="V"."ACLCODE" OR "
C"."ACL"=(-1)) AND
"V"."ITEMTYPEVIEWID"=1533 AND "V"."ITEMTYPEID"=1533) OR EXISTS (S
ELECT /*+ */ 0 FROM
"ICMSTITVIEWDEFS" "V","ICMSTCOMPILEDPERM" "P","ICMSTCOMPILEDACL" "
C" WHERE "C"."UNUM"=1 AND
"C"."RPRIV"='1' AND ("C"."ACL"="V"."ACLCODE" OR "C"."ACL"=(-1)) AN
D "P"."PRIVDEFCODE"=121 AND
"P"."UNUM"=2 AND "V"."ITEMTYPEVIEWID"=1533 AND "V"."ITEMTYPEID"=15
33))
2 - access("GPSFOLDER_1"."ATTR0000002084"='GPSFolderData♣D♠00♣{GPSROOT}')
4 - filter("V"."ITEMTYPEID"=1533)
5 - access("V"."ITEMTYPEVIEWID"=1533)
6 - access("C"."UNUM"=2 AND "C"."RPRIV"='1')
filter("C"."RPRIV"='1' AND ("C"."ACL"="V"."ACLCODE" OR "C"."ACL"=(-1)))
9 - filter("V"."ITEMTYPEID"=1533)
10 - access("V"."ITEMTYPEVIEWID"=1533)
11 - access("P"."UNUM"=2 AND "P"."PRIVDEFCODE"=121)
12 - access("C"."UNUM"=1 AND "C"."RPRIV"='1')
filter("C"."RPRIV"='1' AND ("C"."ACL"="V"."ACLCODE" OR "C"."ACL"=(-1)))
39 rows selected.
SQL>
|
|
|
|
| Re: How to judge the slow query? [message #404301 is a reply to message #403978] |
Thu, 21 May 2009 05:07 |
crazypeter2005
Messages: 29
Registered: September 2008
Location: Peking China
|
Junior Member |
|
|
Thanks everyone. I check the SQL plan and both are not slow.
It is the client problem
[05/21/2009 09:03:14.250] 1484 T: -- DKRESULTSETCURSORICM::ISVALID()
[05/21/2009 09:03:14.250] 1484 T: - DKRESULTSETCURSORICM::ISBEGIN()=> result: 1
[05/21/2009 09:03:14.250] 1484 T: -- DKRESULTSETCURSORICM::ISBEGIN()
[05/21/2009 09:03:14.250] 1484 T: -+ DKRESULTSETCURSORICM::LOADDDOBLOCK()
[05/21/2009 09:03:14.250] 1484 T: -+ DKRESULTSETCURSORICM::FETCHNEXTMATCH()
[We can see Jump Here, This Function might has problem]
[05/21/2009 09:03:21.656] 1484 T: -- DKRESULTSETCURSORICM::FETCHNEXTMATCH()
[05/21/2009 09:03:21.656] 1484 T: - DKRESULTSETCURSORICM::LOADDDOBLOCK()=> Storing block item: 0
But on DB2 backend, the function works well.
That is to say:
The Oracle has already compute the result but the client programe has not receieve it for 6s.
Need to rewrite the connect programe...
Thanks again!
|
|
|
|
| Re: How to judge the slow query? [message #405886 is a reply to message #403978] |
Sun, 31 May 2009 01:43 |
crazypeter2005
Messages: 29
Registered: September 2008
Location: Peking China
|
Junior Member |
|
|
1.gif is the sql that seems "slow", actually it might because of client programe caused.
2.gif is the fast sql that is normal.
2 sql has minor difference in the last sentence.
SELECT /*+ FIRST_ROWS (500) */ T.* FROM (SELECT DISTINCT GPSFolder_1.ITEMID, GPSFolder_1.COMPONENTID, GPSFolder_1.VERSIONID, 2343 AS COMPONENTTYPEID, 1533 AS ITEMTYPEID FROM ICMUT02343001 GPSFolder_1 WHERE (GPSFolder_1.ATTR0000002084 = 'GPSFolderDataD00{GPSROOT}') AND (((( ( (EXISTS (SELECT 1 FROM ICMSTCOMPILEDACL C , ICMSTITVIEWDEFS V WHERE 1533=V.ITEMTYPEID AND V.ITEMTYPEVIEWID IN (1533) AND (C.ACL = V.ACLCODE OR C.ACL = -1) AND C.UNUM=2 AND C.RPRIV='1' )) OR (EXISTS (SELECT 1 FROM ICMSTCOMPILEDACL C, ICMSTCOMPILEDPERM P , ICMSTITVIEWDEFS V WHERE 1533=V.ITEMTYPEID AND V.ITEMTYPEVIEWID IN (1533) AND (C.ACL = V.ACLCODE OR C.ACL = -1) AND C.UNUM=1 AND C.RPRIV='1' AND P.PRIVDEFCODE=121 AND P.UNUM=2 )) )))))) T , ICMSTITEMS001001 I WHERE T.ITEMID = I.ITEMID AND T.VERSIONID = I.VERSIONID
SELECT /*+ FIRST_ROWS (500) */ T.* FROM (SELECT DISTINCT GPSFolder_1.ITEMID, GPSFolder_1.COMPONENTID, GPSFolder_1.VERSIONID, 2343 AS COMPONENTTYPEID, 1533 AS ITEMTYPEID FROM ICMUT02343001 GPSFolder_1 WHERE (GPSFolder_1.ATTR0000002084 = 'GPSFolderDataD00{GPSROOT}') AND (((( ( (EXISTS (SELECT 1 FROM ICMSTCOMPILEDACL C , ICMSTITVIEWDEFS V WHERE 1533=V.ITEMTYPEID AND V.ITEMTYPEVIEWID IN (1533) AND (C.ACL = V.ACLCODE OR C.ACL = -1) AND C.UNUM=2 AND C.RPRIV='1' )) OR (EXISTS (SELECT 1 FROM ICMSTCOMPILEDACL C, ICMSTCOMPILEDPERM P , ICMSTITVIEWDEFS V WHERE 1533=V.ITEMTYPEID AND V.ITEMTYPEVIEWID IN (1533) AND (C.ACL = V.ACLCODE OR C.ACL = -1) AND C.UNUM=1 AND C.RPRIV='1' AND P.PRIVDEFCODE=121 AND P.UNUM=2 )) )))))) T
-
Attachment: 1.GIF
(Size: 34.59KB, Downloaded 944 times)
|
|
|
|
|
|
|
|
|
|
| Re: How to judge the slow query? [message #405944 is a reply to message #403978] |
Mon, 01 June 2009 01:33 |
crazypeter2005
Messages: 29
Registered: September 2008
Location: Peking China
|
Junior Member |
|
|
I have a question:
I have 2 ways to get statistic info on tables
1 exec dbms_stats.gather_schema_stats(ownname=>'svtlsadm',cascade=>TRUE);
2 Use analyse tables
This slow sql can be solved by using the second method.
What is the difference?
Why the first method fails to get the correct info, and leads a big table full scan?
Any ideas?
Thanks!
|
|
|
|
| Re: How to judge the slow query? [message #405948 is a reply to message #403978] |
Mon, 01 June 2009 01:40 |
crazypeter2005
Messages: 29
Registered: September 2008
Location: Peking China
|
Junior Member |
|
|
In this sql:
SELECT /*+ FIRST_ROWS (500) */ T.* FROM (SELECT DISTINCT GPSFolder_1.ITEMID, GPSFolder_1.COMPONENTID, GPSFolder_1.VERSIONID, 2343 AS COMPONENTTYPEID, 1533 AS ITEMTYPEID FROM ICMUT02343001 GPSFolder_1 WHERE (GPSFolder_1.ATTR0000002084 = 'GPSFolderDataD00{GPSROOT}') AND (((( ( (EXISTS (SELECT 1 FROM ICMSTCOMPILEDACL C , ICMSTITVIEWDEFS V WHERE 1533=V.ITEMTYPEID AND V.ITEMTYPEVIEWID IN (1533) AND (C.ACL = V.ACLCODE OR C.ACL = -1) AND C.UNUM=2 AND C.RPRIV='1' )) OR (EXISTS (SELECT 1 FROM ICMSTCOMPILEDACL C, ICMSTCOMPILEDPERM P , ICMSTITVIEWDEFS V WHERE 1533=V.ITEMTYPEID AND V.ITEMTYPEVIEWID IN (1533) AND (C.ACL = V.ACLCODE OR C.ACL = -1) AND C.UNUM=1 AND C.RPRIV='1' AND P.PRIVDEFCODE=121 AND P.UNUM=2 )) )))))) T , ICMSTITEMS001001 I WHERE T.ITEMID = I.ITEMID AND T.VERSIONID = I.VERSIONID
If I use dbms_stats.gather_table_stats, the sql will choose full scan large table ICMSTITEMS001001 which has 20M data.
But if I use analyze table on ICMSTITEMS001001 , the sql will use index on ICMSTITEMS001001.
Why is that?
Does oracle suggest us to use dbms_stats.gather_table_stats, it is provide by Oracle, isn`t it?
|
|
|
|
| Re: How to judge the slow query? [message #405978 is a reply to message #403978] |
Mon, 01 June 2009 04:49 |
crazypeter2005
Messages: 29
Registered: September 2008
Location: Peking China
|
Junior Member |
|
|
I finally find out why.
It is the tester who execute wrong tune db script:
dbms_stats.gather_schema_stats(ownname=>'svtlsadm',cascade=>TRUE);
when dbms_stats.gather_schema_stats run without option clause,
it will NOT collect Histograms. So oracle has choose a bad plan
large table full scan.
when I execute
execute dbms_stats.gather_schema_stats(ownname =>'svtlsadm',estimate_percent =>
dbms_stats.auto_sample_size,method_opt=>'for all columns size auto', cascade=>true);
Before analyse the table:
1: query is too slow,the resp time is 32s,u can find TABLE ACCESS FULL: ICMSTITEMS001001,this is not permitted here
SQL> select count(*) from ICMUT02343001;
COUNT(*)
----------
50718
SQL> select count(*) from ICMSTITEMS001001;
COUNT(*)
----------
2723582
SQL>
SQL> SELECT /*+ FIRST_ROWS (500) */
2 T.*
3 FROM (SELECT DISTINCT GPSFolder_1.ITEMID,
4 GPSFolder_1.COMPONENTID,
5 GPSFolder_1.VERSIONID,
6 2343 AS COMPONENTTYPEID,
7 1533 AS ITEMTYPEID
8 FROM svtlsadm.ICMUT02343001 GPSFolder_1
9 WHERE (GPSFolder_1.ATTR0000002084 = 'GPSFolderData^ED^F00^E{GPSROOT}')
10 AND ((((((EXISTS (SELECT 1
11 FROM svtlsadm.ICMSTCOMPILEDACL C, svtlsadm.ICMSTITVIEWDEFS V
12 WHERE 1533 = V.ITEMTYPEID
13 AND V.ITEMTYPEVIEWID IN (1533)
14 AND (C.ACL = V.ACLCODE OR C.ACL = -1)
15 AND C.UNUM = 2
16 AND C.RPRIV = '1')) OR
17 (EXISTS (SELECT 1
18 FROM svtlsadm.ICMSTCOMPILEDACL C,
19 svtlsadm.ICMSTCOMPILEDPERM P,
20 svtlsadm.ICMSTITVIEWDEFS V
21 WHERE 1533 = V.ITEMTYPEID
22 AND V.ITEMTYPEVIEWID IN (1533)
23 AND (C.ACL = V.ACLCODE OR C.ACL = -1)
24 AND C.UNUM = 1
25 AND C.RPRIV = '1'
26 AND P.PRIVDEFCODE = 121
27 AND P.UNUM = 2)))))))) T,
28 svtlsadm.ICMSTITEMS001001 I
29 WHERE T.ITEMID = I.ITEMID
30 AND T.VERSIONID = I.VERSIONID;
Elapsed: 00:00:32.43
| 0 | SELECT STATEMENT| | 502 | 61746 |15(0)|00:00:01 |
|* 1 | FILTER | | | | |
| 2 | NESTED LOOPS | 502 | 61746 |15(0)|00:00:01 |
| 3 | TABLE ACCESS FULL | ICMSTITEMS001001| 502 | 14056 |4 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID | ICMUT02343001 | 1 | 95 |1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | ICMUX023430011U | 1 | |0 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 22 |2 (0)| 00:00:01 |
|* 7 | TABLE ACCESS BY INDEX ROWID | ICMSTITVIEWDEFS | 1 | 12 |1 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | SYS_C004533 | 1 | | 0 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | ICMSXCOMPILEDACL1X | 1 | 10 |1 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | 1 | 30 |2 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | 1 | 20 |1 (0)| 00:00:01 |
|* 12 | TABLE ACCESS BY INDEX ROWID| ICMSTITVIEWDEFS | 1 | 12 |1 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | SYS_C004533 | 1 | |0 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | ICMSXCOMPILEDPRM1U | 1 | 8 |0 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | ICMSXCOMPILEDACL1X | 1 | 10 |1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
After analyse the table:
SQL>analyze table ICMSTITEMS001001 compute statistics for all indexes for all indexed columns;
SQL>analyze table ICMUT02343001 compute statistics for all indexes for all indexed columns;
SQL> SELECT /*+ FIRST_ROWS (500) */ T.*
2 FROM svtlsadm.ICMSTITEMS001001 I,(SELECT DISTINCT GPSFolder_1.ITEMID,
3 GPSFolder_1.COMPONENTID,
4 GPSFolder_1.VERSIONID,
5 2343 AS COMPONENTTYPEID,
6 1533 AS ITEMTYPEID
7 FROM svtlsadm.ICMUT02343001 GPSFolder_1
8 WHERE (GPSFolder_1.ATTR0000002084 = 'GPSFolderData^ED^F00^E{GPSROO
T}')
9 AND ((((((EXISTS (SELECT 1
10 FROM svtlsadm.ICMSTCOMPILEDACL C, svtlsadm.I
CMSTITVIEWDEFS V
11 WHERE 1533 = V.ITEMTYPEID
12 AND V.ITEMTYPEVIEWID IN (1533)
13 AND (C.ACL = V.ACLCODE OR C.ACL = -1)
14 AND C.UNUM = 2
15 AND C.RPRIV = '1')) OR
16 (EXISTS (SELECT 1
17 FROM svtlsadm.ICMSTCOMPILEDACL C,
18 svtlsadm.ICMSTCOMPILEDPERM P,
19 svtlsadm.ICMSTITVIEWDEFS V
20 WHERE 1533 = V.ITEMTYPEID
21 AND V.ITEMTYPEVIEWID IN (1533)
22 AND (C.ACL = V.ACLCODE OR C.ACL = -1)
23 AND C.UNUM = 1
24 AND C.RPRIV = '1'
25 AND P.PRIVDEFCODE = 121
26 AND P.UNUM = 2)))))))) T
27 WHERE T.ITEMID = I.ITEMID
28 AND T.VERSIONID = I.VERSIONID;
ITEMID COMPONENTID VERSIONID COMPONENTTYPEID
-------------------------- ------------------ ---------- ---------------
ITEMTYPEID
----------
A1001001A09E31B53346H12088 A09E31B53346H12088 1 2343
1533
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 3614278029
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 54 | 6426 |
5 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | NESTED LOOPS | | 54 | 6426 | 5 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | ICMUX02343001001U | 1 | 91 |3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID | ICMSTITEMS001001 | 54 | 1512 | 2 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | SYS_C004613 | 1 | | 1 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 22 | 2 (0)| 00:00:01 |
|* 7 | TABLE ACCESS BY INDEX ROWID | ICMSTITVIEWDEFS | 1 | 12 | 1 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | SYS_C004533 | 1 | | 0 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | ICMSXCOMPILEDACL1X | 1 | 10 | 1 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | 1 | 30 | 2 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | 1 | 20 | 1 (0)| 00:00:01 |
|* 12 | TABLE ACCESS BY INDEX ROWID| ICMSTITVIEWDEFS | 1 | 12 | 1 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | SYS_C004533 | 1 | | 0 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | ICMSXCOMPILEDPRM1U | 1 | 8 | 0 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | ICMSXCOMPILEDACL1X | 1 | 10 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
11 consistent gets
0 physical reads
0 redo size
708 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
Problem solved.
It is a shame that I am the tester that use the wrong script.
But I am glad to learn this.
I am a newbie on oracle and I should learn more from your guys.
Thanks BlackSwan.
You helped me a lot!
[Updated on: Mon, 01 June 2009 10:16] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Mon Nov 25 16:55:46 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
