Than Another 12 on Oracle Database 12c

201 Pages·2012·6.15 MB·English

More
Than
Another
12 on
Oracle
Database
12c
EOUC
 –
 EMEA
 User
 Groups
Back
 by
 Popular
 Demand

Rules

•  Clock
 and
 you
 stop
 at
 7
 minutes
 –
 no
 excuses

Timekeepers

–  Fiona
 MarCn
 UKOUG

–  Ralf
 Koelling
 DOAG

The
 Boss

–  me
Jonathan
Lewis

UKOUG
Less
 well-­‐known
 enhancements

of
 the
 12c
 OpCmizer

 Jonathan
 Lewis

 @JLOracle

 Oracle
 ACE
 Director,
 OakTable

 Jonathan
 Lewis
 is
 a
 well-­‐known
 figure
 in
 the
 Oracle
 world
 with
 more
 than
 27
 years
 experience
 using
 the
 soRware.

He
 has
 published
 three
 books
 about
 Oracle
 –
 the
 most
 recent
 being
 “Oracle
 Core”
 published
 by
 Apress
 Nov
 2011
 –

and
 contributed
 to
 three
 others.
 He
 has
 posted
 more
 than
 a
 thousand
 arCcles
 to

 his
 blog
 and
 contributes
 fairly

regularly
 to
 forums,
 online
 magazines
 and
 User
 Group
 events
 around
 the
 world.
Lesser
 known
 side
 effects
 of
 the

12c
 OpCmizer

Some
 Old
 Stuff

•  Column
 Groups
 (11g)
 -­‐
 a
 good
 thing
 ™

•  Result
 Cache
 (11g)

 -­‐
 a
 good
 thing
 ™

•  Dynamic
 Sampling
 (9i)
 -­‐
 a
 good
 thing
 ™
Lesser
 known
 side
 effects
 of
 the

12c
 OpCmizer

select
{list of columns}
from {single table}
where col1 = {constant}
and col2 = {constant}
and col3 = {constant}
and col4 = {constant} -- no bind variables
;
Time to completion
11.2.0.3: few hundredths of a second
12.1.0.2: 12 seconds
Same
 execuCon
 plan
 in
 both
 cases
Lesser
 known
 side
 effects
 of
 the

12c
 OpCmizer

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.11 0.11 11 13 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 1 145 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.11 0.12 12 158 0 1
RRoowwss RRooww SSoouurrccee OOppeerraattiioonn ((wwiitthh aa ffeeww ccoossmmeettiicc aaddjjuussttmmeennttss))
-------- ------------------------------------------------------------------------------------------------------------
11 TTAABBLLEE AACCCCEESSSS BBYY IINNDDEEXX RROOWWIIDD {{TTAABB}} ((ccrr==114455 pprr==11 ppww==00 ttiimmee==11006655 uuss ccoosstt==557733))
66 IINNDDEEXX SSKKIIPP SSCCAANN {{iinnddeexx}} ((ccrr==114433 pprr==00 ppww==00 ttiimmee==11000033 uuss ccoosstt==557722))
Better stats might help
For default system stats, cost = 572 => predicted run-time = 6.864 seconds
Lesser
 known
 side
 effects
 of
 the

12c
 OpCmizer

SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring
optimizer_features_enable(default) no_parallel result_cache(snapshot=3600)
OPT_ESTIMATE(@"innerQuery", TABLE, "ALIAS", ROWS=2.424321289) */
C1, C2, C3
FROM
(SELECT /*+ qb_name("innerQuery") INDEX( "ALIAS" "INDEX_NAME") */
COUNT(*) AS C1, 4294967295 AS C2,
SUM(CASE WHEN ("ALIAS"."COLUMN"= 201602) THEN 1 ELSE 0 END) AS C3
FROM "TABLE" "ALIAS" WHERE ("ALIAS"."COLUMN"='VALUE1')) innerQuery
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 2.97 2.98 15589 15637 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 2.98 2.99 15589 15637 0 0
Fast disks ? Or external cache ?
Lesser
 known
 side
 effects
 of
 the

12c
 OpCmizer

SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring
optimizer_features_enable(default) no_parallel result_cache(snapshot=3600)
OPT_ESTIMATE(@"innerQuery", TABLE, "ALIAS", ROWS=2.424321289) */
C1, C2, C3
FROM
(SELECT /*+ qb_name("innerQuery") INDEX( "ALIAS" "INDEX_NAME") */
COUNT(*) AS C1, 4294967295 AS C2,
SUM(CASE WHEN ("ALIAS"."COLUMN"= 201602) THEN 1 ELSE 0 END) AS C3
FROM "TABLE" "ALIAS" WHERE ("ALIAS"."COLUMN"='VALUE1')) innerQuery

Rows (avg) Row Source Operation
---------- ---------- ---------------------------------------------------
0 RESULT CACHE cgk8mpa52bbj1bbw7rgshtcdzv (cr=0 pr=0 pw=0 time=4 us)
0 VIEW (cr=0 pr=0 pw=0 time=29 us cost=77300 size=31 card=1)
0 SORT AGGREGATE (cr=0 pr=0 pw=0 time=27 us)
3279857 INDEX RANGE SCAN INDEX (cr=15637 pr=15589 pw=0 time=2831992 us)