This is a resubmission on an earlier enquiry
Date: Mon, 15 Nov 2010 20:18:48 -0800 (PST)
Message-ID: <f9b9e2ac-2f18-4d44-9b73-6867a3b515fe_at_w18g2000vbe.googlegroups.com>
This is a resubmission on an earlier enquiry (Many thanks to Randolf for his advice on how to present the relevant information)
The following SQL statement has been identified as a poorly performing
query.
It currently takes up to 40 seconds.
In some situations this can take 7 seconds but this is usually a short
time after the instance is bounced.
This is the statement :
{code}
SELECT
objecttype.id_object
FROM PHY_ALL_OBJECTS objecttype
WHERE
objecttype.id_type_definition = 'duotA50'
AND objecttype.date_delete is null
AND
(
id_parent not in
(
SELECT id_object from folders f START WITH
f.id_object = 'fA464'
CONNECT BY prior f.id_object = f.id_parent
)
)
AND UPPER(objecttype.name) LIKE UPPER('%coghlan%')
;
{code}
The version of the database is 10.2.0.1.0
The parameters relevant to the optimizer are :
{code}
SQL>
SQL> show parameter optimizer
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_dynamic_sampling integer 2 optimizer_features_enable string 10.2.0.1 optimizer_index_caching integer 0 optimizer_index_cost_adj integer 100 optimizer_mode string ALL_ROWS optimizer_secure_view_merging boolean TRUESQL> show parameter db_file_multi
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_file_multiblock_read_count integer 16SQL>
SQL> show parameter db_block_size
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_block_size integer 8192
{code}
The explain plan output is as follows :
{code}
SQL>
SQL> explain plan for
3 SELECT
4 objecttype.id_object
5 FROM OBJECTIVE.PHY_ALL_OBJECTS objecttype
6 WHERE
7 objecttype.id_type_definition = 'duotA50'
8 AND objecttype.date_delete is null
9 AND
10 (
11 id_parent not in
12 (
13 SELECT id_object from objective.folders f
START WITH f.id_object = 'fA464'
14 CONNECT BY prior f.id_object =
f.id_parent
15 )
16 )
17 AND UPPER(objecttype.name) LIKE UPPER('%coghlan%')
18 ;
Explained.
Elapsed: 00:00:00.01
SQL> SQL> SQL> SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
Plan hash value: 3719432900
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
| 0 | SELECT STATEMENT
| | 13162 | 1529K| 2007 (1)|00:00:25 |
|* 1 | HASH JOIN RIGHT ANTI
| | 13162 | 1529K| 2007 (1)| 00:00:25 | | 2 | VIEW | VW_NSO_1 | 31 | 310 | 3 (0)| 00:00:01 | |* 3 | CONNECT BY WITH FILTERING | | | | | | | 4 | TABLE ACCESS BY INDEX ROWID| FOLDERS | | | | | |* 5 | INDEX RANGE SCAN | INDEX_FOLDERS_ID_OBJECT_PARENT | 1 | 9 | 3 (0)| 00:00:01 | | 6 | NESTED LOOPS | | | | | | | 7 | BUFFER SORT | | | | | | | 8 | CONNECT BY PUMP | | | | | | |* 9 | INDEX RANGE SCAN | INDEX_FOLDERS_ID_PARENT | 31 | 527 | 3 (0)| 00:00:01 | |* 10 | TABLE ACCESS FULL | FOLDERS | 31 | 527 | 3 (0)| 00:00:01 | |* 11 | TABLE ACCESS FULL | PHY_ALL_OBJECTS | 13250 | 1410K| 2003 (1)| 00:00:25|
Predicate Information (identified by operation id):
1 - access("ID_PARENT"="$nso_col_1")
3 - filter("F"."ID_OBJECT"='fA464')
5 - access("F"."ID_OBJECT"='fA464')
9 - access("F"."ID_PARENT"=NULL)
10 - access("F"."ID_PARENT"=NULL)
11 - filter(UPPER("OBJECTTYPE"."NAME") LIKE '%COGHLAN%' AND
"OBJECTTYPE"."DATE_DELETE" IS NULL AND
"OBJECTTYPE"."ID_TYPE_DEFINITION"='duotA50')
29 rows selected.
Elapsed: 00:00:00.51
{code}
Here is the output of SQL*Plus AUTOTRACE including the TIMING information :
{code}
SQL> SELECT
2 objecttype.id_object
3 FROM OBJECTIVE.PHY_ALL_OBJECTS objecttype
4 WHERE
5 objecttype.id_type_definition = 'duotA50'
6 AND objecttype.date_delete is null
7 AND
8 (
9 id_parent not in
10 (
11 SELECT id_object from objective.folders f
START WITH f.id_object = 'fA464'
12 CONNECT BY prior f.id_object =
f.id_parent
13 )
14 )
15 AND UPPER(objecttype.name) LIKE UPPER('%coghlan%')
16 ;
18 rows selected.
Elapsed: 00:00:16.26
Execution Plan
Plan hash value: 3719432900
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
| 0 | SELECT STATEMENT
| | 13162 | 1529K| 2007 (1)|00:00:25 |
|* 1 | HASH JOIN RIGHT ANTI
| | 13162 | 1529K| 2007 (1)| 00:00:25 | | 2 | VIEW | VW_NSO_1 | 31 | 310 | 3 (0)| 00:00:01 | |* 3 | CONNECT BY WITH FILTERING | | | | | | | 4 | TABLE ACCESS BY INDEX ROWID| FOLDERS | | | | | |* 5 | INDEX RANGE SCAN | INDEX_FOLDERS_ID_OBJECT_PARENT | 1 | 9 | 3 (0)| 00:00:01 | | 6 | NESTED LOOPS | | | | | | | 7 | BUFFER SORT | | | | | | | 8 | CONNECT BY PUMP | | | | | | |* 9 | INDEX RANGE SCAN | INDEX_FOLDERS_ID_PARENT | 31 | 527 | 3 (0)| 00:00:01 | |* 10 | TABLE ACCESS FULL | FOLDERS | 31 | 527 | 3 (0)| 00:00:01 | |* 11 | TABLE ACCESS FULL | PHY_ALL_OBJECTS | 13250 | 1410K| 2003 (1)| 00:00:25|
Predicate Information (identified by operation id):
1 - access("ID_PARENT"="$nso_col_1")
3 - filter("F"."ID_OBJECT"='fA464')
5 - access("F"."ID_OBJECT"='fA464')
9 - access("F"."ID_PARENT"=NULL)
10 - access("F"."ID_PARENT"=NULL)
11 - filter(UPPER("OBJECTTYPE"."NAME") LIKE '%COGHLAN%' AND
"OBJECTTYPE"."DATE_DELETE" IS NULL AND
"OBJECTTYPE"."ID_TYPE_DEFINITION"='duotA50')
Statistics
68 recursive calls
3863654 db block gets
350436 consistent gets
80537 physical reads
0 redo size
794 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
1 sorts (disk)
18 rows processed
{code}
The TKPROF output is as follows :
{code}
SELECT
objecttype.id_object
FROM OBJECTIVE.PHY_ALL_OBJECTS objecttype
WHERE
objecttype.id_type_definition = 'duotA50'
AND objecttype.date_delete is null
AND
(
id_parent not in
(
SELECT id_object from objective.folders f
START WITH f.id_object = 'fA464'
CONNECT BY prior f.id_object = f.id_parent
)
)
AND UPPER(objecttype.name) LIKE UPPER('%coghlan%')
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 2 10.76 15.84 80537 350436 3863654 18
------- ------ -------- ---------- ---------- ---------- ----------
total 4 10.76 15.84 80537 350436 3863654 18
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
18 HASH JOIN RIGHT ANTI (cr=350436 pr=80537 pw=11472
time=15723573 us)
532715 VIEW VW_NSO_1 (cr=341349 pr=77514 pw=11472 time=15839397 us)
532715 CONNECT BY WITH FILTERING (cr=341349 pr=77514 pw=11472 time=15306680 us)
2 TABLE ACCESS BY INDEX ROWID FOLDERS (cr=8 pr=0 pw=0 time=61 us)
2 INDEX RANGE SCAN INDEX_FOLDERS_ID_OBJECT_PARENT (cr=6 pr=0 pw=0 time=45 us)(object id 184772)
511191 NESTED LOOPS (cr=319884 pr=0 pw=0 time=1062081 us)
158874 BUFFER SORT (cr=0 pr=0 pw=0 time=39648 us)
168488 CONNECT BY PUMP (cr=0 pr=0 pw=0 time=9 us)
511191 INDEX RANGE SCAN INDEX_FOLDERS_ID_PARENT (cr=319884 pr=0
pw=0 time=446132 us)(object id 184771)
713323 TABLE ACCESS FULL FOLDERS (cr=21457 pr=18282 pw=0 time=63
us)
18 TABLE ACCESS FULL PHY_ALL_OBJECTS (cr=9087 pr=3023 pw=0
time=5606 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait TotalWaited
- Waited ----------
SQL*Net message to client 2 0.00 0.00 direct path write temp 656 0.08 3.56 db file sequential read 166 0.00 0.00 db file scattered read 1528 0.00 0.11 direct path read temp 52455 0.03 3.17 SQL*Net message from client 2 0.00 0.00
{code}
The DBMS_XPLAN.DISPLAY_CURSOR output is :
{code}
SQL> select * from table(dbms_xplan.display_cursor(null, null,
'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
SQL_ID 828yr6dtfzvar, child number 0
select /*+ gather_plan_statistics */ objecttype.id_object FROM OBJECTIVE.PHY_ALL_OBJECTS objecttype WHERE objecttype.id_type_definition = 'duotA50' AND
objecttype.date_delete is null AND
( id_parent not in ( SELECT id_object from objective.folders f START WITH f.id_object = 'fA464' CONNECT BY prior f.id_object = f.id_parent ) ) AND
UPPER(objecttype.name) LIKE UPPER('%coghlan%')
Plan hash value: 3719432900
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time| Buffers | Reads | Writes | OMem | 1Mem | Used-Mem |
|* 1 | HASH JOIN RIGHT ANTI
| | 1 | 13162 | 18 | 00:00:15.38 | 4214K| 92070 | 11472 | 17M| 3382K| 24M (0)| | 2 | VIEW | VW_NSO_1 | 1 | 31 | 532K|00:00:15.62 | 4205K| 82986 | 11472 | | | | |* 3 | CONNECT BY WITH FILTERING | | 1 | | 532K| 00:00:15.09 | 4205K| 82986 | 11472 | 63M| 2773K| 37M (1)| | 4 | TABLE ACCESS BY INDEX ROWID| FOLDERS | 2 | | 2 |00:00:00.01 | 8 | 4 | 0 | | | | |* 5 | INDEX RANGE SCAN | INDEX_FOLDERS_ID_OBJECT_PARENT | 2 | 1 | 2 |00:00:00.01 | 6 | 3 | 0 | | | | | 6 | NESTED LOOPS | | 2 | | 511K| 00:00:01.07 | 319K| 2298 | 0 | | | | | 7 | BUFFER SORT | | 2 | | 158K| 00:00:00.04 | 0 | 0 | 0 | 7642K| 1096K| 6792K (0)| | 8 | CONNECT BY PUMP | | 2 | | 168K| 00:00:00.01 | 0 | 0 | 0 | | | | |* 9 | INDEX RANGE SCAN | INDEX_FOLDERS_ID_PARENT | 158K| 31 | 511K|00:00:00.46 | 319K| 2298 | 0 | | | | | 10 | TABLE ACCESS FULL | FOLDERS | 1 | 31 | 713K|00:00:00.01 | 21457 | 21452 | 0 | | | | |* 11 | TABLE ACCESS FULL | PHY_ALL_OBJECTS | 1 | 13250 | 18 |00:00:00.01 | 9087 | 9084 | 0 | | | | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
1 - access("ID_PARENT"="$nso_col_1")
3 - filter("F"."ID_OBJECT"='fA464')
5 - access("F"."ID_OBJECT"='fA464')
9 - access("F"."ID_PARENT"=NULL)
11 - filter((UPPER("OBJECTTYPE"."NAME") LIKE '%COGHLAN%' AND
"OBJECTTYPE"."DATE_DELETE" IS NULL AND
"OBJECTTYPE"."ID_TYPE_DEFINITION"='duotA50'))
34 rows selected.
{code}
I welcome any suggestions on how I can improve the performance of this
statement.
Please note that I have not control of the SQL code because it is
vendor generated.
Having said that, if anyone can show a change to the code that can
cause a significant improvement then
the vendor may be open to some changing to the coding.
I am also open to other suggestions that may provide some performance
improvements, i.e.
- Changing of SGA parameters, sort_area_size, etc
- Upgrading to Oracle 11
- Suggestions for a materialized view.
Many thanks for considering this request. Received on Mon Nov 15 2010 - 22:18:48 CST