Daniel Westermann's Blog

by now we know that we can tell the optimizer to write its decisions to a trace file. this file can tell us what access plans for the sql in question were considered and what statistics were used to come up with the final costs.

as introduced in the first post one parameter which influences the correctness of the statistics is the estimate_percent parameter of the the gather_*_stats procedures. wouldn’t it be great if we could compare statistics which were gathered with different values for the parameters to help us to decide which will be the best strategy for our database and application ?

recall the table containing the house- and phonenumbers which was created in the second post. we already noticed that the num_rows statistic reported different values once the estimate_percent changed. so let’s see if we can report the changes for different sets of parameters for the gather_*_stats procedures.

the first thing we’ll need is a table where we can export some sets of statistics to:

BEGIN
dbms_stats.create_stat_table ( ownname => USER
, stattab => 'MY_STATS'
, tblspace => 'USERS'
);
END;
/


as oracle recommends using the default value for the estimate_percent ( which is DBMS_STATS.AUTO_SAMPLE_SIZE ) this shall be our first set of statistics:

BEGIN
dbms_stats.gather_table_stats ( ownname => USER
, tabname => 'T1' );
END;
/


we already noticed in the first post that the values will not be that correct:

SELECT num_rows
FROM user_tab_statistics
WHERE table_name = 'T1';
NUM_ROWS
----------
9966

the actual number of rows is still 10’000:

SELECT count(*)
FROM t1;
COUNT(*)
----------
10000


before playing around with the estimate_parameter save the current statistics to the statistic table created above:

BEGIN
dbms_stats.export_table_stats ( ownname => USER
, tabname => 'T1'
, statid => 'DEFAULT_ESTIMATE_PERCENT'
, stattab => 'MY_STATS'
);
END;
/


now there is stored set of statistics we may use to compare with other sets. as we know a value of 100 for the estimate_percent parameter should produce better statistics let’s re-generate with the increased value:

BEGIN
dbms_stats.gather_table_stats ( ownname => USER
, tabname => 'T1'
, estimate_percent => 100 );
END;
/

… and quickly check the result for the num_rows statistic:

SELECT num_rows
FROM user_tab_statistics
WHERE table_name = 'T1';
NUM_ROWS
----------
10000

looks correct. again, let’s save the statistics:

BEGIN
dbms_stats.export_table_stats ( ownname => USER
, tabname => 'T1'
, statid => 'ESTIMATE_PERCENT_100'
, stattab => 'MY_STATS'
);
END;
/


to see what values changed for the different sets we can now compare the sets of statistics:

SET LONG 20000
SET LINES 164
SET PAGES 999
SELECT *
FROM TABLE ( dbms_stats.diff_table_stats_in_stattab ( USER
, 'T1'
, 'MY_STATS'
, 'MY_STATS'
, NULL
, 'DEFAULT_ESTIMATE_PERCENT'
, 'ESTIMATE_PERCENT_100'
)
);


this will produce a report similar to this one:

REPORT MAXDIFFPCT
-------------------------------------------------------------------------------- ----------
###############################################################################
STATISTICS DIFFERENCE REPORT FOR:
.................................
TABLE : T1
OWNER : OW
SOURCE A : User statistics table MY_STATS
: Statid : DEFAULT_ESTIMATE_PERCENT
: Owner : OW
SOURCE B : User statistics table MY_STATS
: Statid : ESTIMATE_PERCENT_100
: Owner : OW
PCTTHRESHOLD :
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
TABLE / (SUB)PARTITION STATISTICS DIFFERENCE:
.............................................
OBJECTNAME TYP SRC ROWS BLOCKS ROWLEN SAMPSIZE
...............................................................................
T1 T A 9966 20 8 4764
B 10000 20 8 10000
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
COLUMN STATISTICS DIFFERENCE:
.............................
COLUMN_NAME SRC NDV DENSITY HIST NULLS LEN MIN MAX SAMPSIZ
...............................................................................
HOUSENUMBER A 9941 .000100593 NO 0 4 C104 C302 4752
B 10000 .0001 NO 0 4 C102 C302 10000
PHONENUMBER A 5503 .000181719 NO 0 4 C104 C2646 4852
B 6376 .000156838 NO 0 4 C102 C2646 10000
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
INDEX / (SUB)PARTITION STATISTICS DIFFERENCE:
.............................................
OBJECTNAME TYP SRC ROWS LEAFBLK DISTKEY LF/KY DB/KY CLF LVL SAMPSIZ
...............................................................................
INDEX: I_HOUSENUMBER
....................
I_HOUSENUMBER I A 10000 21 10000 1 1 18 1 10000
B 10000 21 10000 1 1 18 1 10000
INDEX: I_PHONENUMBER
....................
I_PHONENUMBER I A 10000 21 6376 1 1 9411 1 10000
B 10000 21 6376 1 1 9411 1 10000
###############################################################################


take a look at the report and you can easily see how the statistics changed when increasing the estimate_percent parameter ( of course you can change any other parameter, too ).

this is a great feature if you want to play around with different settings for the gather_*_procedures and want to check which settings are the best for your case. and always remember that you can set different parameters down to the table level. for a quick description check this post.

this post will continue the post optimizer basics (2) and further look at how the optimizer decides if an index will be used or not. the clustering_factor was already introduced as one important statistic the optimizer uses for making its decisions.

recall the two statements from the last post:

-- first statement
SELECT phonenumber
FROM t1
WHERE housenumber < 100;
-- second statement
SELECT housenumber
FROM t1
WHERE phonenumber < 100;


while for the first statement the appropriate index ( I_HOUSENUMBER ) was used the index on the phone number column ( I_PHONENUMBER ) was ignored for the second statement. until now the assistant of the phone company realized that it makes a difference on how well the table is ordered in relation to the index and that this is expressed in a statistic called the clustering_factor. what is missing to complete the picture is how the optimizer chooses its plan.

as the oracle database is highly instrumented we are lucky and can tell the optimizer to write its decisions to a trace file. the event one needs to set for this is: 10053.

let’s create two trace files, one for each of the statements from above:

alter session set tracefile_identifier='I_HOUSENUMBERS';
alter session set events '10053 trace name context forever, level 12';
SELECT phonenumber
FROM t1
WHERE housenumber < 100;
alter session set events '10053 trace name context forever, level 0';

alter session set tracefile_identifier='I_PHONENUMBERS';
alter session set events '10053 trace name context forever, level 12';
SELECT housenumber
FROM t1
WHERE phonenumber < 100;
alter session set events '10053 trace name context forever, level 0';


remember that you can can ask the database if you’re not sure where to find the trace files:
show parameter background_dump_dest

if you open the trace files and scroll down to the section called “QUERY BLOCK TEXT” you will see the statement. right after that the interesting stuff begins:
-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
Using NOWORKLOAD Stats
CPUSPEEDNW: 1998 millions instructions/sec (default is 100)
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
MBRC: NO VALUE blocks (default is 8)

these are the statistical values which are valid for the system:

• CPUSPEEDNW: the speed of the cpu
• IOTFRSPEED: transfer speed for singel I/O read requests
• IOSEEKTIM: I/O seek time
• MBRC: multiblock read count

for a detailed description of these statistics check the oracle documentation.
how this statistics are gathered and how they are modified will be a topic for another post. for the scope of this post just realize the ones present in the trace file.

the next section in the trace file reports the statistics for the table and its indexes:
Table Stats::
Table: T1 Alias: T1
#Rows: 10000 #Blks: 20 AvgRowLen: 8.00 ChainCnt: 0.00
Index Stats::
Index: I_HOUSENUMBER Col#: 1
LVLS: 1 #LB: 21 #DK: 10000 LB/K: 1.00 DB/K: 1.00 CLUF: 18.00
Index: I_PHONENUMBER Col#: 2
LVLS: 1 #LB: 21 #DK: 6376 LB/K: 1.00 DB/K: 1.00 CLUF: 9411.00

these are the same values that you can query directly from the database:
SELECT NUM_ROWS "#rows"
, BLOCKS "#Blks"
, AVG_ROW_LEN "AvgRowLen"
, CHAIN_CNT "ChainCnt"
FROM user_tab_statistics
WHERE table_name = 'T1';
#rows #Blks AvgRowLen ChainCnt
---------- ---------- ---------- ----------
10000 20 8 0
SELECT BLEVEL "LVLS"
, LEAF_BLOCKS "#LB"
, DISTINCT_KEYS "#DK"
, AVG_LEAF_BLOCKS_PER_KEY "LB/K"
, AVG_DATA_BLOCKS_PER_KEY "DB/K"
, CLUSTERING_FACTOR "CLUF"
FROM user_ind_statistics
WHERE index_name IN ( 'I_HOUSENUMBER','I_PHONENUMBER' );
LVLS #LB #DK LB/K DB/K CLUF
---------- ---------- ---------- ---------- ---------- ----------
1 21 10000 1 1 18
1 21 6376 1 1 9411


let’s look at the first statement. the trace file reports that the following access paths were considered:
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T1[T1]
Column (#1): HOUSENUMBER(
AvgLen: 4 NDV: 10000 Nulls: 0 Density: 0.000100 Min: 1 Max: 10000
Table: T1 Alias: T1
Card: Original: 10000.000000 Rounded: 99 Computed: 99.01 Non Adjusted: 99.01
Access Path: TableScan
Cost: 7.09 Resp: 7.09 Degree: 0
Cost_io: 7.00 Cost_cpu: 2144409
Resp_io: 7.00 Resp_cpu: 2144409
Access Path: index (RangeScan)
Index: I_HOUSENUMBER
resc_io: 3.00 resc_cpu: 58364
ix_sel: 0.009901 ix_sel_with_filters: 0.009901
Cost: 3.00 Resp: 3.00 Degree: 1
******** Begin index join costing ********
****** trying bitmap/domain indexes ******
Access Path: index (IndexOnly)
Index: I_HOUSENUMBER
resc_io: 2.00 resc_cpu: 34243
ix_sel: 0.009901 ix_sel_with_filters: 0.009901
Cost: 2.00 Resp: 2.00 Degree: 0
Bitmap nodes:
Used I_HOUSENUMBER
Cost = 2.001985, sel = 0.009901
****** finished trying bitmap/domain indexes ******
******** End index join costing ********
Best:: AccessPath: IndexRange
Index: I_HOUSENUMBER
Cost: 3.00 Degree: 1 Resp: 3.00 Card: 99.01 Bytes: 0

clearly the IndexRange scan reports the lowest cost. so this is the plan to choose for the first statement. for the second statement:
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T1[T1]
Column (#2): PHONENUMBER(
AvgLen: 4 NDV: 6376 Nulls: 0 Density: 0.000157 Min: 1 Max: 9998
Table: T1 Alias: T1
Card: Original: 10000.000000 Rounded: 99 Computed: 99.03 Non Adjusted: 99.03
Access Path: TableScan
Cost: 7.10 Resp: 7.10 Degree: 0
Cost_io: 7.00 Cost_cpu: 2342429
Resp_io: 7.00 Resp_cpu: 2342429
Access Path: index (RangeScan)
Index: I_PHONENUMBER
resc_io: 96.00 resc_cpu: 720658
ix_sel: 0.009903 ix_sel_with_filters: 0.009903
Cost: 96.03 Resp: 96.03 Degree: 1
******** Begin index join costing ********
****** trying bitmap/domain indexes ******
Access Path: index (IndexOnly)
Index: I_PHONENUMBER
resc_io: 2.00 resc_cpu: 34243
ix_sel: 0.009903 ix_sel_with_filters: 0.009903
Cost: 2.00 Resp: 2.00 Degree: 0
Bitmap nodes:
Used I_PHONENUMBER
Cost = 2.001986, sel = 0.009903
****** finished trying bitmap/domain indexes ******
******** End index join costing ********
Best:: AccessPath: TableScan
Cost: 7.10 Degree: 1 Resp: 7.10 Card: 99.03 Bytes: 0

… the cost for using the index ( 96.03 ) is far to high in comparison to the cost of the full tablescan ( 7.10 ). that’s why the full table scan will be the plan of choice.

if you are interesed in how these numbers gets calculated: there are lots of smart people out there who spent a lot of work in describing this, for example:
Richard Foote
Randolf Geist
John Brady

… and of course Jonathan Lewis’ book

in short every I/O is a cost + some costing for CPU.

conclusion: if you know how your data is organized you should be able to predict what the optimizer will do for the statements in question. you always should think about the indexes before you decide to create them. not every index will be used by oracle and every additional index will increase the work oracle needs to do in case of insert/updates/deletes to the table. sequential reads are very fast today so don’t be surprised if a full tablescan is the plan of choice …

nowadays everybody is talking about consolidation to reduce costs and ( maybe ) to simplify management of the infrastructure. the current trend seems to be:

• buy big boxes ( exadata, for example ) and put many databases on one physical host.
• buy big boxes, use one of the virtualization or partition technologies ( vmware, lpars, ldoms, oracle vm, solaris zones, …. ) and put the database on the virtual hosts
• use RAC in combination with cheaper boxes and try to scale by adding more cheaper boxes once the workload increases

what less people seem to think about ( correct me if I’m wrong ): why not consolidate some of the smaller applications which do not need that much resources into the same database ? this will reduce licensing costs and ( maybe ) simplifies administration, too. of course, there are things to consider:

• each application must use the same database version
• if you need to patch, all applications will be affected
• if you need to upgrade, all applications will be affected
• if there is unplanned or planned downtime, all applications will be affected
• security inside the database becomes more important: permissions, roles …
• if one of the applications goes crazy how do you manage that it does not affect the other applications that much that they are not usable anymore ?
• how to quickly identify an application which has troubles ?

having thought about these points maybe you’ll give it a try. especially for identifying the applications quickly and react on issues one concept that should be implemented is services. if you ever worked with RAC, services should not be new to you, but services are useful in single instances, too. as services do not require any changes to the application implementing them is straight forward:

• if you use oracle restart or a clustered grid infratructure the easiest way to manage services is by using the srvctl utility
• if you don’t use oracle restart, single instance services should be managed by the dbms_service package

this post will discuss both methods and give some examples. obviously the first step you need to do is to create a service ( I will create two services to show how one can connect to a specific service later ).

the srvctl way:

srvctl add service -d DB112 -s BEER -y AUTOMATIC -B SERVICE_TIME

… where:

• -d: is the database unique name
• -s: is the service name
• -y: is the flag for the management policy
• -B: is the runtime load balancing goal

note: there are some other parameters one can specify, but as these parameter are important for failover, load balancing, dataguard and rac I will not discuss them right now.

service created. does oracle restart report the newly created service ? yes, of course:

crsctl stat res ora.db112.beer.svc
NAME=ora.db112.beer.svc
TYPE=ora.service.type
TARGET=OFFLINE
STATE=OFFLINE

does the database report the service ?

SYS@DB112> select name from all_services;
NAME
----------------------------------------------------------------
SYS$BACKGROUND
SYS$USERS
DB112

… not yet. lets start the service:

srvctl start service -d DB112 -s beer

… and check what oracle restart reports for the service now:

crsctl stat res ora.db112.beer.svc
NAME=ora.db112.beer.svc
TYPE=ora.service.type
TARGET=ONLINE
STATE=ONLINE on oracleplayground

seems to be online. does the database report the service now? :

SYS@DB112> select name from all_services;
NAME
----------------------------------------------------------------
SYS$BACKGROUND
SYS$USERS
DB112
BEER

yes. once the service is started the database reports the service in the appropriate views. to quickly check the attributes and state of the service:

srvctl config service -d DB112 -s beer -v
Service name: BEER
Service is enabled
Cardinality: SINGLETON
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Failover type: NONE
Failover method: NONE
TAF failover retries: 0
TAF failover delay: 0
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: SERVICE_TIME
TAF policy specification: NONE
Edition: 

thats all you need to do. a new service is created and the service is up and running. one more thing to consider: will the service be started automatically once the database is restarted? :

srvctl stop database -d DB112
srvctl start database -d DB112
crsctl stat res ora.db112.beer.svc
NAME=ora.db112.beer.svc
TYPE=ora.service.type
TARGET=ONLINE
STATE=ONLINE on oracleplayground

yes, it will.

for creating the second service i will use the dbms_service package.

the dbms_service way:

BEGIN
  DBMS_SERVICE.CREATE_SERVICE (
      service_name => 'MOREBEER'
    , network_name => 'MOREBEER'
    , goal => DBMS_SERVICE.GOAL_THROUGHPUT
    , dtp => NULL
    , aq_ha_notifications => NULL
    , failover_method => NULL
    , failover_type => NULL
    , failover_retries => NULL
    , failover_delay => NULL
    , clb_goal => NULL
    , edition => NULL
   );
END;
/

in contrast to the srvctl method the database already knows about the service now:

select name from all_services;
NAME
----------------------------------------------------------------
SYS$BACKGROUND
SYS$USERS
DB112
BEER
MOREBEER

lets start the service:

BEGIN
  dbms_service.start_service (
     service_name => 'MOREBEER'
   , instance_name => 'DB112'
  );
END;
/

thats it. service up and running.

to make things a little easier for us oracle already registered the services with listener:

lsnrctl services listener_db112
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 22-MAY-2012 12:46:25
Copyright (c) 1991, 2011, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_DB112)))
Services Summary...
Service "BEER" has 1 instance(s).
  Instance "DB112", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "DB112" has 1 instance(s).
  Instance "DB112", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "MOREBEER" has 1 instance(s).
  Instance "DB112", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
The command completed successfully

… and modified the services parameter:

show parameter service_names
NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
service_names			     string	 BEER, MOREBEER

quickly check if the database starts up the second service if we bounce the instance:

srvctl stop database -d db112
srvctl start database -d db112
lsnrctl services listener_db112
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 22-MAY-2012 12:53:45
Copyright (c) 1991, 2011, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_DB112)))
Services Summary...
Service "BEER.fun" has 1 instance(s).
  Instance "DB112", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "DB112.fun" has 1 instance(s).
  Instance "DB112", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
The command completed successfully

no. only the service which was created through srvctl is up and running. if you want services to startup automatically you created with the dbms_service package you’ll need to create triggers similar to this:

CREATE OR REPLACE TRIGGER MOREBEER_STARTUP
AFTER STARTUP ON DATABASE
BEGIN
  dbms_service.start_service (
     service_name => 'MOREBEER'
   , instance_name => 'DB112'
  );  
END;
/
CREATE OR REPLACE TRIGGER MOREBEER_SHUTDOWN
BEFORE SHUTDOWN ON DATABASE
BEGIN
  dbms_service.stop_service (
     service_name => 'MOREBEER'
   , instance_name => 'DB112'
  );  
END;
/

restart the database and check if it works:

srvctl stop database -d db112
srvctl start database -d db112
lsnrctl services listener_db112
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 22-MAY-2012 12:58:15
Copyright (c) 1991, 2011, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_DB112)))
Services Summary...
Service "BEER.fun" has 1 instance(s).
  Instance "DB112", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "DB112.fun" has 1 instance(s).
  Instance "DB112", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "MOREBEER.fun" has 1 instance(s).
  Instance "DB112", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
The command completed successfully

works. both services will now survive a database restart. time to connect to the services…

as I will use sqlplus for the connections I will need two more entries in my tnsnames.ora:

BEER.fun =
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS= (PROTOCOL = TCP)(Host = oracleplayground.fun)(Port = 1521))
  )
    (CONNECT_DATA =
      (SERVICE_NAME = BEER.fun)
    )
  )
MOREBEER.fun =
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS= (PROTOCOL = TCP)(Host = oracleplayground.fun)(Port = 1521))
  )
    (CONNECT_DATA =
      (SERVICE_NAME = MOREBEER.fun)
    )
  )

… quickly check if I can reach the listener:

tnsping beer
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 22-MAY-2012 13:03:44
Copyright (c) 1997, 2011, Oracle.  All rights reserved.
Used parameter files:
/opt/oracle/product/base/11.2.0.3/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS_LIST= (ADDRESS= (PROTOCOL = TCP)(Host = oracleplayground.fun)(Port = 1521))) (CONNECT_DATA = (SERVICE_NAME = BEER)))
OK (10 msec)

tnsping morebeer
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 22-MAY-2012 13:04:04
Copyright (c) 1997, 2011, Oracle.  All rights reserved.
Used parameter files:
/opt/oracle/product/base/11.2.0.3/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS_LIST= (ADDRESS= (PROTOCOL = TCP)(Host = oracleplayground.fun)(Port = 1521))) (CONNECT_DATA = (SERVICE_NAME = MOREBEER)))
OK (10 msec)

fine. for the following I just did a seperate connection to each service and generated some load. once the connections are established you can use the service views to query data about your services:

ALL_SERVICES
V$SERVICEMETRIC
V$SERVICEMETRIC_HISTORY
V$SERVICES
V$SERVICE_EVENT
V$SERVICE_STATS
V$SERVICE_WAIT_CLASS
V$ACTIVE_SERVICES

if there are performance issues awr displays some performance data by service, too:

service stats in awr

as well as the ash report:

service stats in ash

this is the foundation you may use when thinking about application consolidation into a single database. of course you should go further and integrate the services with the database resource manager to minimize impacts between the services or applications … and by the way: splitting a single application into services might be a good idea, too