Re: This is a resubmission on an earlier enquiry
From: joel garry <joel-garry_at_home.com>
Date: Tue, 16 Nov 2010 09:45:53 -0800 (PST)
Message-ID: <fd8f53b3-69a0-4e9d-b6e9-438b9c259aaa_at_fv1g2000vbb.googlegroups.com>
On Nov 15, 8:18 pm, Mick <mjms..._at_gmail.com> wrote:
> 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 TRUE
> SQL> show parameter db_file_multi
>
> NAME TYPE VALUE
> ------------------------------------ -----------
> ------------------------------
> db_file_multiblock_read_count integer 16
> SQL>
> 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 Total
> Waited
> ---------------------------------------- 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.
Date: Tue, 16 Nov 2010 09:45:53 -0800 (PST)
Message-ID: <fd8f53b3-69a0-4e9d-b6e9-438b9c259aaa_at_fv1g2000vbb.googlegroups.com>
On Nov 15, 8:18 pm, Mick <mjms..._at_gmail.com> wrote:
> 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 TRUE
> SQL> show parameter db_file_multi
>
> NAME TYPE VALUE
> ------------------------------------ -----------
> ------------------------------
> db_file_multiblock_read_count integer 16
> SQL>
> 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 Total
> Waited
> ---------------------------------------- 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.
Regarding Randolf's PGA_AGGREGATE_TARGET and sort_area_size suggestions, let us know what your settings are currently (including workarea_size_policy) and what the advisor tells you (there's a dbconsole display or a table v$pga_target_advice). Also, please verify whether you are using dedicated connections.
The upgrade to a current patch option is pretty important, you can spend a lot of time messing around with these bugs.
jg
-- _at_home.com is bogus. http://www.signonsandiego.com/news/2010/nov/14/tsa-ejects-oceanside-man-airport-refusing-security/Received on Tue Nov 16 2010 - 11:45:53 CST