Re: query performance following 12c upgrade
- From: Steve Bradshaw <sjb1970@xxxxxxxxx>
- To: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- Date: Wed, 23 Sep 2015 14:53:08 +0100
Hi,
Please see the attached. opti12 is the results when run with
optimizer_featured_enable=12.1.0.2, and opti11 it is 11.2.0.3
I've had to anonymyse the data/columns/tables etc so the formatting may be
a little out.
Thanks
Steve
On Wed, Sep 23, 2015 at 12:04 PM, Jonathan Lewis <
jonathan@xxxxxxxxxxxxxxxxxx> wrote:
Stephen,
They may be deceiving you.
Since the queries take only a few seconds to run you can do the following:
spool xxx
set serveroutput off
set linesize 180
set trimspool on
set pagesize 60
alter session set statistics_level = all;
{run the query}
select * from table(dbms_xplan.display_cursor(null,null,'allstats last
outline'));
alter session set statistics_level = typcial;
spool off
This will pull the actual execution plan from memory.
With the options I've given this will also include you some details about
the work done (allstats last), and perhaps some clues about the strategy
that the optimizer followed (outline).
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
------------------------------
*From:* Steve Bradshaw [sjb1970@xxxxxxxxx]
*Sent:* 23 September 2015 11:54
*To:* Jonathan Lewis
*Cc:* ORACLE-L
*Subject:* Re: query performance following 12c upgrade
Hi Jonathan,
Thanks for the reply.
The plans I've been looking at are from sqplus with autotrace on - is that
incorrect?
Steve
On Wed, Sep 23, 2015 at 11:30 AM, Jonathan Lewis <
jonathan@xxxxxxxxxxxxxxxxxx> wrote:
The difference in performance is so extreme that I'd first have to check
whether or not the plans were true, they look like plans from EXPLAIN PLAN
rather than plans pulled from memory. It would also help to see the
predicate sections anyway.
If these really are the run-time plans with such a massive difference in
performance I'd also want to check for any significant changes in data
content, or physical data location.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
------------------------------
*From:* oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on
behalf of Steve Bradshaw [sjb1970@xxxxxxxxx]
*Sent:* 23 September 2015 11:08
*To:* ORACLE-L
*Subject:* query performance following 12c upgrade
Hi,
Looking for ideas as to why a query has started taking a lot longer to
execute since upgrading to 12.1.0.2 from 11.2.0.3.
Its a simple 1 table query, that is performing an index skip scan.
Under 12c, the query is taking 12-13 seconds to return a row, whereas
previously it was a fraction of a second.
There is a difference in the plans between the 2 versions. Under 12c,
the table access is 'TABLE ACCESS BY INDEX ROWID BATCHED'.
From the 12c database:
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |
Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2
| 76 | 573 (80)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TABLE1 | 2
| 76 | 573 (80)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | TABLE1_IDX_7 | 7
| | 572 (80)| 00:00:01 |
------------------------------------------------------------------------------------------------------
From the 12c database (having set optimizer_features_enabled to 11.2.0.3
for the session):
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38
| 573 (80)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| TABLE1 | 1 | 38
| 573 (80)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | TABLE1_IDX_7 | 7
| | 572 (80)| 00:00:01 |
----------------------------------------------------------------------------------------------
Any ideas how I can fix this without changing the parameter at the
database level?
Thanks in advance,
Steve
Session altered.
CUSTCD SEQ ORDER_NO
------------------------- ----------- ----------
THEIR_REF
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PERIOD USER_ID LAST_UPDA
---------- ------------------------- ---------
AB 0 98765432
XYZ123456
201602 JOHNDOE 21-SEP-15
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 10n6d1qaca7ys, child number 1
-------------------------------------
select CUSTCD, SEQ, ORDER_NO, THEIR_REF,
period, user_id, last_update from table1
where CUSTCD = 'AB' and SEQ = 0 and THEIR_REF = 'XYZ123456'
and period = 201602 and user_id = 'JOHNDOE'
Plan hash value: 2846984865
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows
| A-Time | Buffers |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1
|00:00:00.01 | 145 |
|* 1 | TABLE ACCESS BY INDEX ROWID| TABLE1 | 1 | 1 | 1
|00:00:00.01 | 145 |
|* 2 | INDEX SKIP SCAN | TABLE1_IDX_7 | 1 | 7 | 6
|00:00:00.01 | 143 |
--------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('12.1.0.2')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_SS(@"SEL$1" "TABLE1"@"SEL$1" ("TABLE1"."CUSTCD"
"TABLE1"."VOUCHER_TYPE"
"TABLE1"."THEIR_REF"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("USER_ID"='JOHNDOE' AND "PERIOD"=201602 AND "SEQ"=0))
2 - access("CUSTCD"='AB' AND "THEIR_REF"='XYZ123456')
filter("THEIR_REF"='XYZ123456')
39 rows selected.
Session altered.
Session altered.
CUSTCD SEQ ORDER_NO
------------------------- ----------- ----------
THEIR_REF
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PERIOD USER_ID LAST_UPDA
---------- ------------------------- ---------
AB 0 98765432
XYZ123456
201602 JOHNDOE 21-SEP-15
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 10n6d1qaca7ys, child number 0
-------------------------------------
select custcd, SEQ, ORDER_NO, THEIR_REF,
period, user_id, last_update from table1
where custcd = 'AB' and SEQ = 0 and THEIR_REF = 'XYZ123456'
and period = 201602 and user_id = 'JOHNDOE'
Plan hash value: 1825820195
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows
| A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 1 |00:00:00.01 | 145 |
|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TABLE1 | 1 | 2
| 1 |00:00:00.01 | 145 |
|* 2 | INDEX SKIP SCAN | TABLE1_IDX_7 | 1 | 7
| 6 |00:00:00.01 | 143 |
----------------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
DB_VERSION('12.1.0.2')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_SS(@"SEL$1" "table1"@"SEL$1" ("table1"."custcd"
"table1"."VOUCHER_TYPE"
"table1"."THEIR_REF"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "table1"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("USER_ID"='JOHNDOE' AND "PERIOD"=201602 AND "SEQ"=0))
2 - access("custcd"='AB' AND "THEIR_REF"='XYZ123456')
filter("THEIR_REF"='XYZ123456')
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- 1 Sql Plan Directive used for this statement
45 rows selected.
Session altered.
Other related posts:
