Oracle Database Resource Manager 11g - Undocumented New Parameters

I've played around with Oracle Database Resource Manager in 10g and it's quite nice and might be very useful for high CPU usage systems, but I found the inability to limit I/O as a drawback since in most cases I've faced the need to limit I/O is more necessary than CPU limit. When you have, let's say, 8 CPU's on your machine, you need all the 8 to be 100% utilized by Oracle sessions for the resource manager start limit sessions. However, if your machine I/O capabilities are 50 mbps, you need only one or two sessions which perform intensive I/O (batch job/heavy report) to make the database very heavy. In Oracle Database 11g Release 1, Resource Manager has gotten some new features related to I/O. So I've installed the 11g, made some tests and found some interesting issues. I'm not going to write about Resource Manager basics or about 11g enhancements as some great articles have already been published about it. For example, you can read Tim's blog post - "Resource Manager Enhancements in Oracle Database 11g Release 1" But... I'm going to discuss one missing capability (in my opinion) that will hopefully be available with Oracle Database 11g Release 2 with 2 new parameters which have already available but inactive and undocumented. For those who are not familiar with Oracle Database Resource Manager I'll try to give a short basic introduction: Oracle Database Resource Manager helps us to prioritize sessions to optimize resource allocation within our database by: Creating groups of sessions (Consumer Groups) based on similar resource requirements Allocate resources to those groups (Resource Plan Directive) Resource Plan - is a container of Resource Plan Directives that allocate resources to Consumer Groups Only one Resource Plan is active at a time. When Oracle Database 11g was introduced, some new features for Resource Manager related to I/O have been revealed. Among them: I/O Calibration - New procedure which helps to assess the I/O capabilities of the database storage. The output of this procedure (CALIBRATE_IO) is max_iops, max_mbps and actual_latency.   Per Session I/O Limits - Unlike previous versions, you can limit I/O requests (or I/O MB) for a session before it moved to a new consumer group. (switch_io_megabytes and switch_io_reqs have been added to CREATE_PLAN_DIRECTIVE procedure)   Oracle have added the option to "capture" Oracle sessions by the I/O requests or by the megabytes of I/O they issued in order to move them to a lower priority consumer group. I have a very fundamental doubt about this enhancements as I don't get the meaning of "capturing" an intensive I/O session and move it to a low priority consumer group which can have only CPU limit ... ?!  The reason we "capture" this session is the amount of I/O it makes, and when we move it to a low priority consumer group we can just limit its CPU resources. We can't limit the amount of I/O for a Consumer Group. It could have been very useful if Oracle had added the ability to limit I/O for a Consumer Group, like we can limit CPU (with mgmt_pN) for a Consumer Group. What is missing here is the ability to limit I/O for a specific Consumer Group in terms of Maximum I/O per second or Maximum megabytes per second. Will Oracle enhance Resource Manager in 11g Release 2 to fulfill this capability? I don't have a confident answer for this question but I assume they will. While playing around I've noticed two new parameters to the CREATE_PLAN procedure - MAX_IOPS and MAX_MBPS. On first sight it looked like the answer to my question - the ability to limit I/O for session within a plan, but it's not... Those two parameter are undocumented and totally ignored in Oracle 11g Release 1 Documentation but available in 11g Release 1 database: -- create a new resource plan -- -- Input arguments: --   plan                       - name of resource plan --   comment                    - user's comment --   cpu_mth                    - allocation method for CPU resources --                                (deprecated) --   active_sess_pool_mth       - allocation method for max. active sessions --   parallel_degree_limit_mth  - allocation method for degree of parallelism --   queueing_mth               - type of queueing policy to use --   mgmt_mth                   - allocation method for CPU and I/O resources --   sub_plan                   - whether plan is sub_plan --   max_iops                   - maximum I/O requests per second --   max_mbps                   - maximum megabytes of I/O per second -- PROCEDURE create_plan(plan IN VARCHAR2,                       comment IN VARCHAR2,                       cpu_mth IN VARCHAR2 DEFAULT NULL,                       active_sess_pool_mth IN VARCHAR2                       DEFAULT 'ACTIVE_SESS_POOL_ABSOLUTE',                       parallel_degree_limit_mth IN VARCHAR2 DEFAULT                       'PARALLEL_DEGREE_LIMIT_ABSOLUTE',                       queueing_mth IN VARCHAR2 DEFAULT 'FIFO_TIMEOUT',                       mgmt_mth IN VARCHAR2 DEFAULT 'EMPHASIS',                       sub_plan IN BOOLEAN DEFAULT FALSE,                       max_iops IN NUMBER DEFAULT NULL,                       max_mbps IN NUMBER DEFAULT NULL                       ); I tried to create a new plan using one of these two parameters, but it returned an error for each value I tried. It turned out that the only valid value to MAX_IOPS and MAX_MBPS is null. begin   dbms_resource_manager.create_plan(plan => 'PLAN_EX_1'                                    ,comment => 'Plan Example 1'                                    ,max_iops => 100); end; / begin * ERROR at line 1: ORA-29355: NULL or invalid MAX_IOPS argument specified ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86 ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 38 ORA-06512: at line 2 I've confirmed it with Oracle support and their answer was: "This is currently an expected behaviour, we can not explicitly set either max_iops or max_mbps to any value other than null, that's why these parameters are currently not included in the documentation." So here is my guess: It looks like Oracle are going to enhance Resource Manager to allow I/O limit, but unlike CPU limit through Plan Directive (mgmt_pN), it's going to be a limit in Plan level, so you can create a sub plan with limited I/O resources allocated. Maybe the allocation method parameter (mgmt_mth) will get more valid options except for EMPHASIS and RATIO. I'll keep track of it as soon as Oracle Database 11g Release 2 will be announced and I'll update. You are welcome to leave a comment and/or share your opinion about this topic. Aviad

Oracle Discoverer Query Prediction functionality and Performance

Lately we noticed that our Discoverer reports runs very slow. Actually, the problem wasn’t the Discoverer reports query but the query prediction that for some reason took so long. What is Query Prediction in Discoverer? “Discoverer includes functionality to predict the time required to retrieve the information in a Discoverer query. The query prediction appears before the query begins, enabling Discoverer users to decide whether or not to run the query. This is a powerful facility that enables Discoverer users to control how long they wait for large reports.” (from Oracle doc’) The query prediction is the elapsed time while the following message appeared on the bottom left of Discoverer Desktop window: "Determining query time estimate". For each report we tested, we found query prediction runs 30%-50% (!!!) from the report’s total run time. Next phase was to start a SQL trace on Discoverer session to see what actually happens when running a Discoverer report. This is the relevant section from the SQL Trace: SELECT QS_ID, QS_COST, QS_ACT_CPU_TIME,        QS_ACT_ELAP_TIME, QS_EST_ELAP_TIME,        QS_CREATED_DATE, QS_OBJECT_USE_KEY,        QS_NUM_ROWS,QS_STATE FROM [EUL_USER].EUL5_QPP_STATS WHERE  QS_COST IS NOT NULL AND    QS_OBJECT_USE_KEY = :OBJECTUSEKEY ORDER BY QS_CREATED_DATE DESC As you can see, the query prediction functionality tries to retrieve statistics information from EUL5_QPP_STATS and it takes 35 seconds. (total time for this report is 55 seconds). The query prediction based, among other things, on the query prediction statistics table – EUL5_QPP_STATS. This table records query prediction statistics while running Discoverer reports. There is no logic by estimating query run time longer than the report’s query itself… Since the query prediction functionality is important to our users we avoid from disable this functionality (by setting the QPPEnable to 0). Furthermore, I found that we have statistics data in this table from 7 years ago…  There is no meaning to hold these statistics… I tried to find information about purging the EUL5_QPP_STATS and I found this: “How to delete old query prediction statistics” in Oracle® Business Intelligence Discoverer Administration Guide 10g Release 2 (10.1.2.1) There is a SQL script at [ORACLE_ HOME]\discoverer\util\eulstdel.sql – that deletes all query prediction statistics that were created before a specified date. Great! I executed this sql on my database, gave 90 days as a parameter and it deleted 460,000 (from 468,000) rows. I ran a Discoverer report again, but still query prediction takes too long, same like before. I checked the explain plan and the cost of the above SQL and it remains the same. I tried to gather statistics on EUL5_QPP_ENABLE table and rebuild its indexes but cost become higher… (More than 103, something like 800…). I had no choice but rebuild the EUL5_QPP_ENABLE table (by export, drop table and import). After recreation of EUL5_QPP_STATS table I ran a Discoverer report again and query prediction takes insignificant time, almost nothing…  :-) This is from the trace I took after: SELECT QS_ID, QS_COST, QS_ACT_CPU_TIME,        QS_ACT_ELAP_TIME, QS_EST_ELAP_TIME,        QS_CREATED_DATE, QS_OBJECT_USE_KEY,        QS_NUM_ROWS,QS_STATE FROM [EUL_USER].EUL5_QPP_STATS WHERE  QS_COST IS NOT NULL AND    QS_OBJECT_USE_KEY = :OBJECTUSEKEY ORDER BY QS_CREATED_DATE DESC The elapsed time for this sql reduced to 0.05 seconds!! (was 35 sec’ before) Sql cost reduced from 103 to 31! I checked this issue on Discoverer Desktop 10g (10.1.2.2) but it is relevant to the web tools (Discoverer viewer and Discoverer Plus) as well, since the query prediction functionality exist in these tools like in the client version. You are welcome to leave a comment. Aviad

Gather statistics for Oracle Applications 11i

Following the last post Monitoring DML operations, In this post I'm going to talk about Gathering Statistics in Oracle Applications 11i system. All of us gathering statistics in our databases, the question is whether it is the best way to do it? We are struggling with a lot of questions regarding for which schema needs to run? How much percent sampling? In what frequency it considered enough? Etc... So I will try to make some light in the dark... The topics in this post will be: • The classic way to gather statistics - by the "Gather Schema Statistics" concurrent with Gather Options parameter set to "GATHER". • Using "Monitoring" with Gather Schema Statistics to enhance Gather Statistics run time and efficiency. You can read more about monitoring in my previous post Monitoring DML operations The Gather Schema Statistics concurrent, based on fnd_stats package, gather statistics for the required schema(s) with some additional parameters: • Schema Name - the schema in which statistics should gathered. You can specify 'ALL' for gather statistics for all schemas registered in FND_PRODUCT_INSTALLATIONS table. • Estimate Percent - The sampling percents. Default value is 10. • Degree of parallelism - The degree of parallelism for gathering statistics. Default value is MIN (parallel_max_servers, cpu_count). (database init parameters) • Backup Flag - Indicate whether to backup last statistics (current situation). BACKUP/NOBACKUP - indicate to save/not save current statistics. • Restart Request Id - if Gather Schema Statistics concurrent failed, you can rerun it with this parameter set to the failed request_id. The concurrent will continue from where the failed request stopped. • History Mode - indicate how much history will save. History includes for each object when statistics gather for it, when it ends and more info'. Parameter options are: LAST_RUN - will save history only for the last time for each object. FULL - will save additional history each run. None - don't save history. • Gather Options - indicates for which objects statistics will gather. Parameter options are: GATHER (the classic & default) - gather stat' for all objects in the appropriate schema. GATHER_AUTO - This option considers how much DML operations executed on each object to decide whether gather statistics or not, I will explain later in details. GATHER_EMPTY - will gather stat only for tables/indexes with no statistics. LIST_AUTO - provide a list of objects for gather statistics if GATHER_AUTO is used. LIST_EMPTY - provide a list of objects for gather statistics if GATHER_EMPTY is used. • Modifications Threshold - relevant only when using GATHER_AUTO/LIST_AUTO in the previous parameter. This parameter specifies the percentage of modifications on objects that must execute before gathering statistics. I will explain later in details. • Invalidate Dependent - indicate whether to invalidate cursor for the analyzed objects. The default is 'Y'. The Gather Schema Statistics concurrent should schedule to run periodically (usually once a week/2 weeks). With gather options parameter set to GATHER and schema set to ALL, it will take a very long time to run since it gather statistics for all objects in database (for schemas registered in FND_PRODUCT_INSTALLATIONS table). Using "Monitoring" with Gather Schema Statistics To reduce Gather Schema Statistics concurrent run time, we can set the Gather Options parameter to "GATHER AUTO", it indicates to gather stats only for tables that have [Modifications Threshold] % changes since last analyze. First we should enable monitoring for the relevant schemas. We can do it by executing the following: exec fnd_stats.ENABLE_SCHEMA_MONITORING (SCHEMA_NAME); You can specify ‘ALL’ as parameter to enable monitoring for all tables in registered schemas. The Gather Schema Statistics concurrent will do the following: • Flush database monitoring info from SGA to dba_tab_modifications view • Gather statistics for tables that cumulative modifications compared to the dba_tables.num_rows are more than Modification Threshold percents. • Gather statistics for table that never analyzed (last_analyzed in NULL) • Gather statistics for tables which does not have monitoring enabled and afterwards enable monitoring for them so we have data in dba_tab_modifications next time we Gather Statistics. As you can see, it’s not necessary to enable monitoring before gather statistics with Gather Auto option, since it does it anyway. (But I did…:-)) In fact, gather schema statistics become significantly more efficient, less run time, since we gather statistics for heavy using tables only. This enables us to run Gather statistics more frequently for less time. Try it! For any questions or if you have additional information you are welcome to leave a comment.

Monitoring DML operations

A very useful feature introduced in Oracle DB 9i called MONITORING. This feature monitors DML operations - insert/update/delete (and truncate) on monitored tables. Table monitoring can be implemented on a table by executing the following statement: "alter table TABLE_NAME monitoring". Once monitor option is enable for a table, all DML operations on it are register in a view - sys.DBA_TAB_MODIFICATIONS (based on sys.mon_mods$ table). The DML counters from this view represent the number of DML operations since the last gather statistics. The overhead on performance is not to be considerable since this mechanism work on memory and flush changes to DBA_TAB_MODOFICATIONS view periodically. In 9i, tables should be implemented with monitoring by executing "alter table ... monitoring", but in 10g monitor is enabled by default. The advantage of this feature is when gathering statistics. Based on the dba_tab_modifications view we can gather statistics for only tables that have more than X% changes (compared to num_rows in user_tables) since last gather statistics. Actually, there is no reason for us to re-analyze tables that has no enough changes. Here is a simple example: SQL> SQL> create table test1 2 (A number 3 ,B varchar2(100)); Table created SQL> SQL> select monitoring from user_tables where table_name = 'TEST1'; MONITORING ---------- YES -- In 10g table automatically has monitor option enabled. -- For 9i you should execute the following: SQL> SQL> alter table test1 monitoring; Table altered SQL> SQL> select * from user_tab_modifications; TABLE_NAME PARTITION_NAME SUBPARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRUNCATED DROP_SEGMENTS ------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ----------- --------- ------------- SQL> SQL> insert into test1 values (1,'a'); 1 row inserted SQL> insert into test1 values (2,'b'); 1 row inserted SQL> insert into test1 values (3,'c'); 1 row inserted SQL> commit; Commit complete SQL> SQL> select * from user_tab_modifications; TABLE_NAME PARTITION_NAME SUBPARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRUNCATED DROP_SEGMENTS ------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ----------- --------- ------------- SQL> SQL> exec dbms_stats.flush_database_monitoring_info; PL/SQL procedure successfully completed SQL> select * from user_tab_modifications; TABLE_NAME PARTITION_NAME SUBPARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRUNCATED DROP_SEGMENTS ------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ----------- --------- ------------- TEST1 3 0 0 7/29/2007 1 NO 0 SQL> In the next post I will explain how to integrate monitoring feature with Gather Schema Statistics concurrent in Oracle Apps 11i. The monitoring discovered as very efficient when gathering statistics in Oracle Apps database or any quite large database. For questions, remarks, clarifications or any issue please leave a comment. Regards :-)