Using the KEEP pool to improve performance
Posted at Tuesday, September 23, 2008
In certain circumstances, pinning segments into memory - using the KEEP pool - can improve performance dramatically. I normally resort to using this feature when administering a database that can't be tuned otherwise. For example, it is often hard to change/tune the segments that are part of a COTS product, such as in a vendor-supplied ERP system.
Of course this option uses, a critical resource, memory; therefore, it's not a viable solution when large tables/indexes are involved.
The KEEP pool can be used in Data Warehouse applications as well, namely to pin aggregate materialized views, etc.
In this example, I show how to set-up and use the KEEP pool. In my example I am pinning LOB data and index segments, but I could have just as easily crafted an example that simply pinned non-LOB table/index segments.
Set-up the KEEP pool
Create a Sample Table
Load Sample Data
When using the KEEP pool feature, I find it helpful to load the following custom view into the database as SYS. This view allows you to see into the data dictionary to determine how the KEEP pool is being utilized.
Load the objects_in_keep_pool view
Query the Sample Table
As you can see, there are no physical reads.
Query the objects_in_keep_pool
As you can see both LOB segments are in the KEEP pool.
Of course this option uses, a critical resource, memory; therefore, it's not a viable solution when large tables/indexes are involved.
The KEEP pool can be used in Data Warehouse applications as well, namely to pin aggregate materialized views, etc.
In this example, I show how to set-up and use the KEEP pool. In my example I am pinning LOB data and index segments, but I could have just as easily crafted an example that simply pinned non-LOB table/index segments.
Set-up the KEEP pool
SQL> alter system set db_keep_cache_size=64m scope=spfile;
SQL> startup;
Create a Sample Table
SQL> create table lob_pin_example ( lpe_id number(10),
lpe_desc varchar2(40),
lpe_blob BLOB)
tablespace users
LOB(lpe_blob) store as lpe_blob_data
(tablespace users disable storage in row storage
(buffer_pool KEEP) cache
index lpe_blob_index);
Table created.
Load Sample Data
SQL> declare
begin
for i in 1..6000 loop
insert into lob_pin_example values (i,'Test','01010101010101');
end loop;
commit;
end;
/
PL/SQL procedure successfully completed.
SQL> select count(*) from lob_pin_example;
COUNT(*)
----------
6000
SQL> analyze table lob_pin_example compute statistics;
Table analyzed.
When using the KEEP pool feature, I find it helpful to load the following custom view into the database as SYS. This view allows you to see into the data dictionary to determine how the KEEP pool is being utilized.
Load the objects_in_keep_pool view
CREATE OR REPLACE VIEW objects_in_keep_pool (bp_name,blocks,object_name,pool_used_size_kb,seg_size_kb)
AS
SELECT kcbwbpd.bp_name,
COUNT (*) blocks,
ob.name,
ROUND (bs.block_size * COUNT (*) / 1024) "POOL_USED_SIZE_KB",
ROUND (ds.BYTES / 1024 ) "SEG_SIZE_KB"
FROM x$kcbwds kcbwds,
x$kcbwbpd kcbwbpd,
x$bh bh,
obj$ ob,
(SELECT block_size
FROM v$buffer_pool
WHERE NAME = 'KEEP') bs,
dba_segments ds
WHERE kcbwds.set_id >= kcbwbpd.bp_lo_sid
AND kcbwds.set_id <= kcbwbpd.bp_hi_sid
AND kcbwbpd.bp_size != 0
AND kcbwds.addr = bh.set_ds
AND bh.state != 0
AND kcbwbpd.bp_name = 'KEEP'
AND bh.obj = ob.obj#(+)
AND ob.name = ds.segment_name(+)
GROUP BY kcbwbpd.bp_name, bh.obj, ob.name, bs.block_size, ds.BYTES;
Query the Sample Table
SQL> set autotrace on explain statistics ;
SQL> select dbms_lob.substr(lpe_blob,1) from lob_pin_example;
As you can see, there are no physical reads.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
18438 consistent gets
0 physical reads
0 redo size
81128 bytes sent via SQL*Net to client
4789 bytes received via SQL*Net from client
401 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6000 rows processed
Query the objects_in_keep_pool
SQL> select * from objects_in_keep_pool;
BP_NAME BLOCKS OBJECT_NAME POOL_USED_SIZE_KB SEG_SIZE_KB
---------- ---------- -------------------- ----------------- -----------
KEEP 48 LPE_BLOB_INDEX 384 384
KEEP 6112 LPE_BLOB_DATA 48896 49152
As you can see both LOB segments are in the KEEP pool.
Labels: Tuning
Identifying Resource-Intensive SQL
Posted at Wednesday, June 18, 2008
Here is a short example on how to identify resource-intensive SQL by querying the DBA_HIST_* views.
In my example, I query the AWR, ranking the top 20 SQL statements using a rough formula based upon CPU and IO-related statistics for the time period in question. In my case, I am querying the current day, but this can be changed to examine time periods further in the past as well.
Top 20 Resource-intensive SQL Example
Top 20 Resource-intensive SQL Example Source (Right-Click - Save Target As...)
In my example, I query the AWR, ranking the top 20 SQL statements using a rough formula based upon CPU and IO-related statistics for the time period in question. In my case, I am querying the current day, but this can be changed to examine time periods further in the past as well.
Top 20 Resource-intensive SQL Example
Top 20 Resource-intensive SQL Example Source (Right-Click - Save Target As...)
Labels: Tuning
11g New Feature: OLTP Table Compression
Posted at Tuesday, February 19, 2008
In 11g table compression has been expanded and is now suitable for conventional
DML operations, including previously supported direct-load operations (e.g. INSERT /*+ APPEND */). Therefore, it is now possible to use compression in OLTP environments as well. It is important to note, however, that this feature requires compatible to be 11.1.0 or higher.
The benefit of using such compression is that disk space is greatly reduced as is memory consumption (since less buffers need to be written into an instance's buffer cache). The downside is that there is additional CPU overhead for loading as well as DML operations.
Here is an example of applying the new table compression to an existing table:
Using SH.CUSTOMERS as the example, query its storage characteristics:
We now know how many blocks are employed by the table segment plus how many records are currently stored per block.
Create a copy of the table us CTAS and then observe whether the table compression feature stores the same number of records in fewer blocks or not.
Query the data dictionary to confirm the newly created segments have the desired attribues:
Query the compressed table's storage characteristics:
The resulting compressed table stores the same data in about half the space:
DML operations, including previously supported direct-load operations (e.g. INSERT /*+ APPEND */). Therefore, it is now possible to use compression in OLTP environments as well. It is important to note, however, that this feature requires compatible to be 11.1.0 or higher.
The benefit of using such compression is that disk space is greatly reduced as is memory consumption (since less buffers need to be written into an instance's buffer cache). The downside is that there is additional CPU overhead for loading as well as DML operations.
Here is an example of applying the new table compression to an existing table:
SQL> connect sh
Using SH.CUSTOMERS as the example, query its storage characteristics:
SQL> select count (distinct dbms_rowid.rowid_block_number(rowid)) "TABLE BLOCK COUNT"
2 from sh.customers;
TABLE BLOCK COUNT
-----------------
1454
SQL> select avg(count(*)) "AVERAGE RECORDS PER BLOCK"
2 from sh.customers group by dbms_rowid.rowid_block_number(rowid);
AVERAGE RECORDS PER BLOCK
-------------------------
38.170564
We now know how many blocks are employed by the table segment plus how many records are currently stored per block.
Create a copy of the table us CTAS and then observe whether the table compression feature stores the same number of records in fewer blocks or not.
SQL> create table sh.customers_compressed compress for all operations as select * from sh.customers;
Table created.
Query the data dictionary to confirm the newly created segments have the desired attribues:
SQL> select table_name, compression, compress_for
2 from user_tables where table_name in ('CUSTOMERS','CUSTOMERS_COMPRESSED');
TABLE_NAME COMPRESS COMPRESS_FOR
------------------------------ -------- ------------------
CUSTOMERS DISABLED
CUSTOMERS_COMPRESSED ENABLED FOR ALL OPERATIONS
Query the compressed table's storage characteristics:
SQL> select count (distinct dbms_rowid.rowid_block_number(rowid)) "TABLE BLOCK COUNT"
2 from sh.customers_compressed;
TABLE BLOCK COUNT
-----------------
763
SQL> select avg(count(*)) "AVERAGE RECORDS PER BLOCK"
2 from sh.customers_compressed group by dbms_rowid.rowid_block_number(rowid);
AVERAGE RECORDS PER BLOCK
-------------------------
72.7391874
The resulting compressed table stores the same data in about half the space:
SQL> select segment_name, round(bytes/1024/1024) "MB"
2 from dba_segments
3 where owner='SH'
4 and segment_name in ('CUSTOMERS','CUSTOMERS_COMPRESSED');
SEGMENT_NAME MB
------------------------------ ----------
CUSTOMERS_COMPRESSED 7
CUSTOMERS 12
RAC: Tuning the Interconnect
Posted at Sunday, February 03, 2008
In order to tune the Interconnect for your RAC cluster, Oracle provides a diagnostic script; you can find this script in the following Metalink Note: 135714.1
In the event that you don't have access to Metalink; here is the script and an example log file from a run against my 2-node, 10.2.0.3 RAC cluster on Enterprise Linux:
racdiag.sql (Rac Diagnostic Script)
Example racdiag.sql log file
In regards to how the Interconnect is performing, the aforementioned script runs a couple of key queries; here are a couple of the more important ones:
Oracle also maintains a RAC survival guide - which is quite helpful - here: 203226.1
In the event that you don't have access to Metalink; here is the script and an example log file from a run against my 2-node, 10.2.0.3 RAC cluster on Enterprise Linux:
racdiag.sql (Rac Diagnostic Script)
Example racdiag.sql log file
In regards to how the Interconnect is performing, the aforementioned script runs a couple of key queries; here are a couple of the more important ones:
-- GLOBAL CACHE CR PERFORMANCE
-- This shows the average latency of a consistent block request.
-- AVG CR BLOCK RECEIVE TIME should typically be about 15 milliseconds depending
-- on your system configuration and volume, is the average latency of a
-- consistent-read request round-trip from the requesting instance to the holding
-- instance and back to the requesting instance. If your CPU has limited idle time
-- and your system typically processes long-running queries, then the latency may
-- be higher. However, it is possible to have an average latency of less than one
-- millisecond with User-mode IPC. Latency can be influenced by a high value for
-- the DB_MULTI_BLOCK_READ_COUNT parameter. This is because a requesting process
-- can issue more than one request for a block depending on the setting of this
-- parameter. Correspondingly, the requesting process may wait longer. Also check
-- interconnect badwidth, OS tcp settings, and OS udp settings if
-- AVG CR BLOCK RECEIVE TIME is high.
--
set numwidth 20
column "AVG CR BLOCK RECEIVE TIME (ms)" format 9999999.9
select b1.inst_id, b2.value "GCS CR BLOCKS RECEIVED",
b1.value "GCS CR BLOCK RECEIVE TIME",
((b1.value / b2.value) * 10) "AVG CR BLOCK RECEIVE TIME (ms)"
from gv$sysstat b1, gv$sysstat b2
where b1.name = 'global cache cr block receive time' and
b2.name = 'global cache cr blocks received' and b1.inst_id = b2.inst_id
or b1.name = 'gc cr block receive time' and
b2.name = 'gc cr blocks received' and b1.inst_id = b2.inst_id ;
-- GLOBAL CACHE LOCK PERFORMANCE
-- This shows the average global enqueue get time.
-- Typically AVG GLOBAL LOCK GET TIME should be 20-30 milliseconds. the elapsed
-- time for a get includes the allocation and initialization of a new global
-- enqueue. If the average global enqueue get (global cache get time) or average
-- global enqueue conversion times are excessive, then your system may be
-- experiencing timeouts. See the 'WAITING SESSIONS', 'GES LOCK BLOCKERS',
-- 'GES LOCK WAITERS', and 'TOP 10 WAIT EVENTS ON SYSTEM' sections if the
-- AVG GLOBAL LOCK GET TIME is high.
--
set numwidth 20
column "AVG GLOBAL LOCK GET TIME (ms)" format 9999999.9
select b1.inst_id, (b1.value + b2.value) "GLOBAL LOCK GETS",
b3.value "GLOBAL LOCK GET TIME",
(b3.value / (b1.value + b2.value) * 10) "AVG GLOBAL LOCK GET TIME (ms)"
from gv$sysstat b1, gv$sysstat b2, gv$sysstat b3
where b1.name = 'global lock sync gets' and
b2.name = 'global lock async gets' and b3.name = 'global lock get time'
and b1.inst_id = b2.inst_id and b2.inst_id = b3.inst_id
or b1.name = 'global enqueue gets sync' and
b2.name = 'global enqueue gets async' and b3.name = 'global enqueue get time'
and b1.inst_id = b2.inst_id and b2.inst_id = b3.inst_id;
-- DLM TRAFFIC INFORMATION
-- This section shows how many tickets are available in the DLM. If the
-- TCKT_WAIT columns says "YES" then we have run out of DLM tickets which could
-- cause a DLM hang. Make sure that you also have enough TCKT_AVAIL.
--
set numwidth 5
select * from gv$dlm_traffic_controller
order by TCKT_AVAIL;
Oracle also maintains a RAC survival guide - which is quite helpful - here: 203226.1
Great Blog Post On Manually Running SQL Performance Analyzer (SPA)
Posted at Saturday, January 19, 2008
A few notes on Oracle and Parallel Execution
Posted at Wednesday, November 21, 2007
As Data Warehouses proliferate and as data volumes - in general - continue to increase, employing parallelism for relevant database operations has become much more important.
Setting up and using Oracle's parallel execution feature is pretty straight-forward and is covered adequately in available manuals, etc. However, there are some things that aren't quite as straight-forward.
For example, from a DBA's perspective, I want to know when I hop onto a system what parallel operations are currently being performed so that I can get a general idea of that system's profile.
I use the following query, which is restricted to solely parallel user operations. I can get a lot of information from this one: what degree of parallelism was asked for and whether this was factored down; how many physical reads each PX Slave has performed; how long each operation (at the Coordinator level) has lasted, and what the SQL_HASH_VALUE is (which I can, in turn, get the full statement from). Optionally, I can use the values from this high-level query to drill-down using other dynamic performance views such as V$SESSION_LONGOPS, V$SESSION_WAIT, and V$PX_SESSTAT.
Here is an example output of the above query: Sample Output
Here is a monitoring script from Oracle's docs which shows the wait events for the slaves:
You can find a couple more monitoring queries at this site by Doug Burns: Suck It Dry - Tuning Parallel Execution. A fancier pdf version can be found here: Tuning Parallel Execution
Of course, the use of parallelism can make tracing a process a real mess. Luckily with 10g, the ability to concatenate trace files using DBMS_MONITOR functionality finally makes this feasible.
Here is a great article regarding how to do this: Tracing Parallel Execution
Here is a quick example:
Here is the results from the trace file (notice the values of 'os threads','parse',and 'execute')
One of the biggest challenges in employing parallelism optimally is determining the appropriate Degree of Parallelism (DOP). This debate is probably as old as the feature itself. Here is a great article which shows just how subjective determining DOP can be: How many Slaves?. At the end of the day, the correct setting for DOP varies by system configuration and must take into account key usage characteristics.
In terms of help in setting global init.ora parameters, the following queries may be helpful:
The following are considered to be the basic parallelism parameters (values are samples):
Setting up and using Oracle's parallel execution feature is pretty straight-forward and is covered adequately in available manuals, etc. However, there are some things that aren't quite as straight-forward.
For example, from a DBA's perspective, I want to know when I hop onto a system what parallel operations are currently being performed so that I can get a general idea of that system's profile.
I use the following query, which is restricted to solely parallel user operations. I can get a lot of information from this one: what degree of parallelism was asked for and whether this was factored down; how many physical reads each PX Slave has performed; how long each operation (at the Coordinator level) has lasted, and what the SQL_HASH_VALUE is (which I can, in turn, get the full statement from). Optionally, I can use the values from this high-level query to drill-down using other dynamic performance views such as V$SESSION_LONGOPS, V$SESSION_WAIT, and V$PX_SESSTAT.
select decode(ps.server_set,'',s.program,' PX Slave') "Program",
ps.qcsid "Parent SID",
decode(ps.server_set,'',' --', ps.sid) "Child SID",
ps.degree ||decode(ps.degree,'',' --','/')||ps.req_degree "DOP/REQ",
decode(ps.server_set,'','Coordinator Process', ps.server_set) "Server Set",
nvl(p.server_name,' --') "PX Server",
nvl(p.status,' --') "PX Server Status",
pss.value "Physical Reads",
decode(ps.server_set,'',s.sql_hash_value,'') "SQL Hash Value",
decode(ps.server_set,'',s.last_call_et,'') "Seconds Elapsed"
from
v$px_session ps,
v$session s,
v$px_process p,
v$px_sesstat pss,
v$statname sn
where
s.sid = ps.qcsid (+)
and ps.sid = p.sid(+)
and ps.sid = pss.sid(+)
and pss.statistic# = sn.statistic# (+)
and sn.name='physical reads'
and s.status='ACTIVE'
and not s.program like '%(A%' --Eliminate Streams Apply Process
and not s.program like '%(C%' --Eliminate Streams Capture Process
order by ps.qcsid, "Server Set" desc, "PX Server";
Here is an example output of the above query: Sample Output
Here is a monitoring script from Oracle's docs which shows the wait events for the slaves:
SELECT px.SID "SID", p.pid, p.spid "SPID", px.inst_id "Inst",
px.server_group "Group", px.server_set "Set", px.DEGREE "Degree",
px.req_degree "Req Degree", w.event "Wait Event"
FROM gv$session s, gv$px_session px, gv$process p, gv$session_wait w
WHERE s.SID(+) = px.SID
AND s.inst_id(+) = px.inst_id
AND s.SID = w.SID(+)
AND s.inst_id = w.inst_id(+)
AND s.paddr = p.addr(+)
AND s.inst_id = p.inst_id(+)
ORDER BY DECODE (px.qcinst_id, NULL, px.inst_id, px.qcinst_id),
px.qcsid,
DECODE (px.server_group, NULL, 0, px.server_group),
px.server_set,
px.inst_id;
You can find a couple more monitoring queries at this site by Doug Burns: Suck It Dry - Tuning Parallel Execution. A fancier pdf version can be found here: Tuning Parallel Execution
Of course, the use of parallelism can make tracing a process a real mess. Luckily with 10g, the ability to concatenate trace files using DBMS_MONITOR functionality finally makes this feasible.
Here is a great article regarding how to do this: Tracing Parallel Execution
Here is a quick example:
SQL> exec dbms_session.set_identifier('pxtrace_test1');
SQL> alter session set tracefile_identifier = 'pxtrace_test1';
SQL> exec dbms_monitor.client_id_trace_enable(client_id=>'pxtrace_test1');
SQL> select /*+ PARALLEL(sales,8) */ count(*) from sh.sales;
COUNT(*)
----------
51455208
SQL> exec dbms_monitor.client_id_trace_disable(client_id=>'pxtrace_test1');Here is the results from the trace file (notice the values of 'os threads','parse',and 'execute')
select /*+ PARALLEL(sales,8) */ count(*)
from
sh.sales
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 9 0.05 0.05 0 0 0 0
Execute 9 33.84 1685.92 223362 449329 0 0
Fetch 2 0.01 215.03 0 0 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 20 33.91 1901.00 223362 449329 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 45 (recursive depth: 1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
PX Deq: Execution Msg 115 0.09 0.32
direct path read 8667 0.00 0.07
os thread startup 9 0.05 0.28
PX Deq: Join ACK 7 0.00 0.01
PX Deq: Parse Reply 7 0.01 0.03
SQL*Net message to client 2 0.00 0.00
PX Deq: Execute Reply 212 1.95 214.75
PX qref latch 12 0.02 0.02
PX Deq: Signal ACK 5 0.00 0.00
enq: PS - contention 1 0.00 0.00
SQL*Net message from client 2 20.92 20.92
********************************************************************************
One of the biggest challenges in employing parallelism optimally is determining the appropriate Degree of Parallelism (DOP). This debate is probably as old as the feature itself. Here is a great article which shows just how subjective determining DOP can be: How many Slaves?. At the end of the day, the correct setting for DOP varies by system configuration and must take into account key usage characteristics.
In terms of help in setting global init.ora parameters, the following queries may be helpful:
SELECT NAME, VALUE
FROM gv$sysstat
WHERE UPPER (NAME) LIKE '%PARALLEL OPERATIONS%'
OR UPPER (NAME) LIKE '%PARALLELIZED%'
OR UPPER (NAME) LIKE '%PX%';
SELECT *
FROM v$px_buffer_advice;
The following are considered to be the basic parallelism parameters (values are samples):
*.parallel_adaptive_multi_user=TRUE
*.parallel_execution_message_size=16384
*.parallel_max_servers=72
*.parallel_min_servers=0
*.parallel_threads_per_cpu=2
Labels: Data Warehousing, Parallelism, Tuning
Custom Statistics Gathering Package for 9i, 10g
Posted at Tuesday, November 20, 2007
In a previous post, I mentioned that 11g adds some great functionality which
makes using Oracle's auto statistics gathering job a much more attractive proposition. Specifically, you can set statistics-related parameters at the table-level as well as at the global level.
However, most shops are still on some combination of 9i and 10g.
For the aforementioned versions, I take the following general approach to statistics:
1.) Disable Oracle's default auto stats DBMS_SCHEDULER job, 'GATHER_STATS_JOB'
2.) Enable Monitoring on all tables (in 10g this is automatically done when
STATISTICS_LEVEL is 'TYPICAL' or greater)
3.) Install (and customize as appropriate) a PL/SQL package which is responsible for the periodic gathering of statistics for the instance. I have included the base package below. Depending upon the environment, I will tweak things such as DEGREE and ESTIMATE_PERCENT. Additionally, I may freeze the stats on staging tables and, when warranted, gather additional histograms for certain tables.
4.) Schedule a daily call to dba_stats_maint_pkg.gather_stats, ideally after any batch programs and/or ETL routines; in other words, after data changes. This procedure updates empty statistics and any segments that have changed more than a certain percentage (by default the package is coded to analyze once 5% of the data has changed, although this can be customized).
Description of the base DBA_STATS_MAINT_PKG PL/SQL package is below:
Source for my package can be found here: dba_stats_maint_pkg
Or, if you prefer, you can cut and paste the SQL script below:
makes using Oracle's auto statistics gathering job a much more attractive proposition. Specifically, you can set statistics-related parameters at the table-level as well as at the global level.
However, most shops are still on some combination of 9i and 10g.
For the aforementioned versions, I take the following general approach to statistics:
1.) Disable Oracle's default auto stats DBMS_SCHEDULER job, 'GATHER_STATS_JOB'
2.) Enable Monitoring on all tables (in 10g this is automatically done when
STATISTICS_LEVEL is 'TYPICAL' or greater)
3.) Install (and customize as appropriate) a PL/SQL package which is responsible for the periodic gathering of statistics for the instance. I have included the base package below. Depending upon the environment, I will tweak things such as DEGREE and ESTIMATE_PERCENT. Additionally, I may freeze the stats on staging tables and, when warranted, gather additional histograms for certain tables.
4.) Schedule a daily call to dba_stats_maint_pkg.gather_stats, ideally after any batch programs and/or ETL routines; in other words, after data changes. This procedure updates empty statistics and any segments that have changed more than a certain percentage (by default the package is coded to analyze once 5% of the data has changed, although this can be customized).
Description of the base DBA_STATS_MAINT_PKG PL/SQL package is below:
| PROGRAM UNIT | DESCRIPTION |
| delete_all_stats | Deletes all statistics in the database, including fixed objects |
| disable_auto_stats_job | Disables Oracle's auto stats gathering job |
| disable_auto_space_job | Disables Oracle's auto segment space advisor job |
| gather_data_dict_stats | Gathers stats for the data dictionary. Applies to system users such as SYS, SYSTEM, DBSNMP, and OUTLN |
| gather_fixed_stats | Gathers fixed stats. This should be done after upgrades and when the profile of the system changes substantially |
| gather_stats | Gathers optimizer stats for non-data dictionary objects. First gathers stats for empty segments and then gathers stats for segments that have had a lot of DML (by default 5%). This is the procedure you will want to modify for your specific environment |
| start_over | A test procedure that deletes all statistics and then gathers all statistics. A good procedure to test your customization end-to-end |
Source for my package can be found here: dba_stats_maint_pkg
Or, if you prefer, you can cut and paste the SQL script below:
GRANT CREATE SESSION TO &&user;
GRANT ANALYZE ANY TO &&user;
GRANT ANALYZE ANY DICTIONARY TO &&user;
GRANT SELECT ANY TABLE TO &&user;
GRANT SELECT ANY SEQUENCE TO &&user;
GRANT SELECT ANY DICTIONARY TO &&user;
GRANT EXECUTE ON DBMS_STATS TO &&user;
GRANT SELECT ON DBA_TAB_MODIFICATIONS TO &&user;
GRANT CREATE JOB TO &&user;
GRANT SCHEDULER_ADMIN TO &&user;
GRANT ALL ON SYS.GATHER_STATS_JOB TO &&user;
GRANT ALL ON SYS.AUTO_SPACE_ADVISOR_JOB TO &&user;
CREATE OR REPLACE PACKAGE &&user..dba_stats_maint_pkg
/* Package is intended for use when managing
the optimizer statistics for 9i and 10g
databases. Once on 11g, each tables' statistics
setting can be customized and used by Oracle's automatic
job
Assumptions:
For 10g, STATISTICS_LEVEL is set to a level
above basic, so that DBA_TAB_MODIFICATIONS
is populated (i.e. tables are monitored)
For 9i, relevant tables have their MONITORING
attribute set, so that DBA_TAB_MODIFICATIONS
view is populated
This package is either compiled as a privileged user
and/or it is compiled under someone else and
all applicable grants on system packages and
dynamic performance views are made
Has a dependency to dbms_lock.sleep procedure - this is assumed to be present
Explicit ANALYZE ANY grant has been made to DB_UTIL user
*/
AS
--Globals change factor setting
--This defines what % a segment must change by in order to update its statistics
gn_change_factor number(16,2) := 5.00;
--Removes all Statistic for the Database
PROCEDURE delete_all_stats;
--Procedure that disables the automatic gathering of stats
--since, until 10g, this 'canned' procedure is not adequate
--to gather statistics responsibly
PROCEDURE disable_auto_stats_job;
--Procedure that disables the automatic segment space advisor job
--The running of this job can use up a lot of resources, hence
--why many disable it
PROCEDURE disable_auto_space_job;
--Procedure gathers stats for SYSTEM and SYS schemas
--Basically a wrapped calls to the applicable DBMS_STATS procedure
PROCEDURE gather_data_dict_stats;
--Procedure gathers fixed object stats
--Whereas, data dictionary stats should be gathers constantly
--fixed object stats only need to be gathered after upgrades, or
--if the profile of the database's usage changes significantly
PROCEDURE gather_fixed_stats;
--Procedure that gathers non-fixed, non-locked table, index, partition, and subpartition statistics
--based upon whether there have been significant changes (i.e. DBA_TAB_MODIFICATIONS)
--and or whether stats are empty
--Package level defaults can be modified for your
PROCEDURE gather_stats;
--Procedure makes internal calls to first delete all stats
--and then gather them, including a gathering of system statistics
PROCEDURE start_over;
END dba_stats_maint_pkg;
/
SHOW ERRORS;
CREATE OR REPLACE PACKAGE BODY &&user..dba_stats_maint_pkg
/* Package is intended for use when managing
the optimizer statistics for 9i and 10g
databases. Once on 11g, each tables' statistics
setting can be customized and used by Oracle's automatic
job
Assumptions:
For 10g, STATISTICS_LEVEL is set to a level
above basic, so that DBA_TAB_MODIFICATIONS
is populated (i.e. tables are monitored)
For 9i, relevant tables have their MONITORING
attribute set, so that DBA_TAB_MODIFICATIONS
view is populated
This package is either compiled as a privileged user
and/or it is compiled under someone else and
all applicable grants on system packages and
dynamic performance views are made
*/
AS
--Removes all Statistic for the Database
PROCEDURE delete_all_stats
AS
BEGIN
DBMS_STATS.delete_dictionary_stats;
DBMS_STATS.delete_database_stats;
EXCEPTION
WHEN OTHERS
THEN
RAISE;
END;
--Procedure that disables the automatic gathering of stats
--since, until 10g, this 'canned' procedure is not adequate
--to gather statistics responsibly
PROCEDURE disable_auto_stats_job
AS
BEGIN
dbms_scheduler.disable('SYS.GATHER_STATS_JOB');
END;
--Procedure that disables the automatic segment space advisor job
--The running of this job can use up a lot of resources, hence
--why many disable it
PROCEDURE disable_auto_space_job
AS
BEGIN
dbms_scheduler.disable('SYS.AUTO_SPACE_ADVISOR_JOB');
END;
PROCEDURE gather_data_dict_stats
AS
BEGIN
DBMS_STATS.gather_dictionary_stats;
EXCEPTION
WHEN OTHERS
THEN
RAISE;
END;
PROCEDURE gather_fixed_stats
AS
BEGIN
DBMS_STATS.gather_fixed_objects_stats;
EXCEPTION
WHEN OTHERS
THEN
RAISE;
END;
PROCEDURE gather_stats
AS
/* Cursor with the names of all the non-partitioned, non-system, non-locked, non-iot overflow type segments
which don't currently have statistics */
lv_owner dba_tables.owner%TYPE;
lv_table_name dba_tables.table_name%TYPE;
lv_part_name dba_tab_partitions.partition_name%TYPE;
lv_change_factor NUMBER (16, 2);
lv_partitioned VARCHAR (10);
CURSOR lc_ns_np_nostats
IS
SELECT owner, table_name
FROM dba_tables
WHERE partitioned = 'NO'
AND NOT owner IN ('SYS','SYSTEM','OUTLN')
AND num_rows IS NULL
AND iot_type IS NULL
AND NOT (owner, table_name) IN (SELECT owner, table_name
FROM dba_tab_statistics
WHERE stattype_locked = 'ALL')
AND NOT (owner, table_name) IN (SELECT owner, table_name
FROM dba_external_tables);
CURSOR lc_ns_p_nostats
IS
SELECT dtp.table_owner, dtp.table_name, dtp.partition_name
FROM dba_tab_partitions dtp, dba_tables dt
WHERE dtp.num_rows IS NULL
AND dtp.table_name = dt.table_name
AND dtp.table_owner = dt.owner
AND dt.iot_type IS NULL
AND NOT dtp.table_owner IN ('SYS','SYSTEM','OUTLN')
AND NOT (dtp.table_owner, dtp.table_name) IN (
SELECT owner, table_name
FROM dba_tab_statistics
WHERE stattype_locked =
'ALL')
AND NOT (dtp.table_owner, dtp.table_name) IN (SELECT owner, table_name
FROM dba_external_tables);
CURSOR lc_ss
IS
SELECT dtm.table_owner, dtm.table_name, dtm.partition_name,
ROUND ( (dtm.inserts + dtm.updates + dtm.deletes)
/ dt.num_rows,
2
)
* 100 "CHANGE_FACTOR",
dt.partitioned
FROM sys.dba_tab_modifications dtm, dba_tables dt
WHERE dtm.table_owner = dt.owner
AND dtm.table_name = dt.table_name
AND NOT dtm.table_owner IN ('SYS','SYSTEM','OUTLN')
AND NOT dt.num_rows IS NULL
AND iot_type IS NULL
AND ( (dt.partitioned = 'YES' AND NOT dtm.partition_name IS NULL
)
OR (dt.partitioned = 'NO' AND dtm.partition_name IS NULL)
)
AND NOT (dtm.table_owner, dtm.table_name) IN (
SELECT dts.owner, dts.table_name
FROM dba_tab_statistics dts
WHERE dts.stattype_locked =
'ALL')
AND NOT (dtm.table_owner, dtm.table_name) IN (
SELECT det.owner,
det.table_name
FROM dba_external_tables det);
BEGIN
/**************************BEGIN EMPTYSTATS************************************/
-- First process the cursor above; alternatively, you could
-- do something similar via a call to DBMS_STATS with the 'GATHER EMPTY' option
OPEN lc_ns_np_nostats; -- open the cursor before fetching
LOOP
FETCH lc_ns_np_nostats
INTO lv_owner, lv_table_name; -- fetches 2 columns into variables
EXIT WHEN lc_ns_np_nostats%NOTFOUND;
-- Call stats package
DBMS_OUTPUT.put_line ( 'Gathering Stats for Table and Indexes of '
|| lv_owner
|| '.'
|| lv_table_name
|| ' because they are empty...'
);
-- For these tables we will use most of the oracle defaults
-- We are assuming that partitioned tables are to be treated differently in terms
-- of sample size, parallelism degree and the like
--trap any errors by placing in its own block
BEGIN
DBMS_STATS.gather_table_stats
(ownname => lv_owner,
tabname => lv_table_name,
granularity => 'AUTO',
method_opt => 'for all columns',
CASCADE => DBMS_STATS.auto_cascade,
estimate_percent => DBMS_STATS.auto_sample_size
);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line (SQLCODE || ' - ' || SQLERRM);
END;
END LOOP;
CLOSE lc_ns_np_nostats;
-- Next we process table partitions whose statistics are null/empty
-- We will use smaller samples and parallelism when appropriate
OPEN lc_ns_p_nostats; -- open the cursor before fetching
LOOP
FETCH lc_ns_p_nostats
INTO lv_owner, lv_table_name, lv_part_name;
-- fetches 3 columns into variables
EXIT WHEN lc_ns_p_nostats%NOTFOUND;
-- Call stats package
DBMS_OUTPUT.put_line ( 'Gathering Stats for Partition '
|| lv_owner
|| '.'
|| lv_table_name
|| '.'
|| lv_part_name
|| ' because they are empty...'
);
-- For these partitioned tables we will use smaller sample sizes
-- Trap Errors in anonymous block
BEGIN
DBMS_STATS.gather_table_stats (ownname => lv_owner,
tabname => lv_table_name,
partname => lv_part_name,
granularity => 'AUTO',
method_opt => 'for all columns',
CASCADE => DBMS_STATS.auto_cascade,
DEGREE => 1,
estimate_percent => .00001
);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line (SQLCODE || ' - ' || SQLERRM);
END;
END LOOP;
CLOSE lc_ns_p_nostats;
--Execute Global Database Stats Gathering with GATHER EMPTY option
--in order to catch any objects that were missed above because of errors, etc.
DBMS_OUTPUT.put_line ('Executing Global Stats Procedure to Catch Any Missed Empty Objects...');
BEGIN
dbms_stats.gather_database_stats(method_opt=>'for all columns',
granularity=>'AUTO',
estimate_percent=>DBMS_STATS.auto_sample_size,
degree=>DBMS_STATS.default_degree,
cascade=>DBMS_STATS.auto_cascade,
options=>'GATHER EMPTY');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line (SQLCODE || ' - ' || SQLERRM);
END;
/**************************END EMPTYSTATS************************************/
-- Lastly we flush the database monitoring information
-- and then use the information therein to gather stats
-- on significantly changed objects
/**************************BEGIN CHANGED OBJECTS STATS***********************/
DBMS_STATS.flush_database_monitoring_info ();
OPEN lc_ss; -- open the cursor before fetching
LOOP
FETCH lc_ss
INTO lv_owner, lv_table_name, lv_part_name, lv_change_factor,
lv_partitioned;
EXIT WHEN lc_ss%NOTFOUND;
-- If change factor is greater than change factor defined in the package header then we will analyze the changed object in question
IF lv_change_factor >= gn_change_factor
THEN
-- Now determine whether the Object is a partition
IF lv_partitioned = 'YES'
THEN
DBMS_OUTPUT.put_line
( 'Gathering Stats for Partition '
|| lv_owner
|| '.'
|| lv_table_name
|| '.'
|| lv_part_name
|| ' because its change factor exceeds '||gn_change_factor||'%...'
);
BEGIN
DBMS_STATS.gather_table_stats
(ownname => lv_owner,
tabname => lv_table_name,
partname => lv_part_name,
granularity => 'AUTO',
method_opt => 'for all columns',
CASCADE => DBMS_STATS.auto_cascade,
DEGREE => 1,
estimate_percent => .00001
);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line (SQLCODE || ' - ' || SQLERRM);
END;
ELSE
DBMS_OUTPUT.put_line
( 'Gathering Stats for Table and Indexes of '
|| lv_owner
|| '.'
|| lv_table_name
|| ' because its change factor exceeds '||gn_change_factor||'%...'
);
BEGIN
DBMS_STATS.gather_table_stats
(ownname => lv_owner,
tabname => lv_table_name,
granularity => 'AUTO',
method_opt => 'for all columns',
CASCADE => DBMS_STATS.auto_cascade,
estimate_percent => DBMS_STATS.auto_sample_size
);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line (SQLCODE || ' - ' || SQLERRM);
END;
END IF;
ELSE
NULL;
END IF;
END LOOP;
CLOSE lc_ss;
/**************************END CHANGED OBJECTS STATS**************************/
END;
--Procedure makes internal calls to first delete all stats
--and then gather them all over again
PROCEDURE start_over
AS
BEGIN
DBMS_OUTPUT.put_line ('Deleting all stats...');
delete_all_stats;
DBMS_OUTPUT.put_line ('Gathering fixed stats...');
gather_fixed_stats;
DBMS_OUTPUT.put_line ('Gathering user stats...');
gather_stats;
DBMS_OUTPUT.put_line ('Gathering dictionary stats...');
gather_data_dict_stats;
EXCEPTION
WHEN OTHERS
THEN
RAISE;
END;
END dba_stats_maint_pkg;
/
SHOW ERRORS;
GRANT ANALYZE ANY TO &&user;
GRANT ANALYZE ANY DICTIONARY TO &&user;
GRANT SELECT ANY TABLE TO &&user;
GRANT SELECT ANY SEQUENCE TO &&user;
GRANT SELECT ANY DICTIONARY TO &&user;
GRANT EXECUTE ON DBMS_STATS TO &&user;
GRANT SELECT ON DBA_TAB_MODIFICATIONS TO &&user;
GRANT CREATE JOB TO &&user;
GRANT SCHEDULER_ADMIN TO &&user;
GRANT ALL ON SYS.GATHER_STATS_JOB TO &&user;
GRANT ALL ON SYS.AUTO_SPACE_ADVISOR_JOB TO &&user;
CREATE OR REPLACE PACKAGE &&user..dba_stats_maint_pkg
/* Package is intended for use when managing
the optimizer statistics for 9i and 10g
databases. Once on 11g, each tables' statistics
setting can be customized and used by Oracle's automatic
job
Assumptions:
For 10g, STATISTICS_LEVEL is set to a level
above basic, so that DBA_TAB_MODIFICATIONS
is populated (i.e. tables are monitored)
For 9i, relevant tables have their MONITORING
attribute set, so that DBA_TAB_MODIFICATIONS
view is populated
This package is either compiled as a privileged user
and/or it is compiled under someone else and
all applicable grants on system packages and
dynamic performance views are made
Has a dependency to dbms_lock.sleep procedure - this is assumed to be present
Explicit ANALYZE ANY grant has been made to DB_UTIL user
*/
AS
--Globals change factor setting
--This defines what % a segment must change by in order to update its statistics
gn_change_factor number(16,2) := 5.00;
--Removes all Statistic for the Database
PROCEDURE delete_all_stats;
--Procedure that disables the automatic gathering of stats
--since, until 10g, this 'canned' procedure is not adequate
--to gather statistics responsibly
PROCEDURE disable_auto_stats_job;
--Procedure that disables the automatic segment space advisor job
--The running of this job can use up a lot of resources, hence
--why many disable it
PROCEDURE disable_auto_space_job;
--Procedure gathers stats for SYSTEM and SYS schemas
--Basically a wrapped calls to the applicable DBMS_STATS procedure
PROCEDURE gather_data_dict_stats;
--Procedure gathers fixed object stats
--Whereas, data dictionary stats should be gathers constantly
--fixed object stats only need to be gathered after upgrades, or
--if the profile of the database's usage changes significantly
PROCEDURE gather_fixed_stats;
--Procedure that gathers non-fixed, non-locked table, index, partition, and subpartition statistics
--based upon whether there have been significant changes (i.e. DBA_TAB_MODIFICATIONS)
--and or whether stats are empty
--Package level defaults can be modified for your
PROCEDURE gather_stats;
--Procedure makes internal calls to first delete all stats
--and then gather them, including a gathering of system statistics
PROCEDURE start_over;
END dba_stats_maint_pkg;
/
SHOW ERRORS;
CREATE OR REPLACE PACKAGE BODY &&user..dba_stats_maint_pkg
/* Package is intended for use when managing
the optimizer statistics for 9i and 10g
databases. Once on 11g, each tables' statistics
setting can be customized and used by Oracle's automatic
job
Assumptions:
For 10g, STATISTICS_LEVEL is set to a level
above basic, so that DBA_TAB_MODIFICATIONS
is populated (i.e. tables are monitored)
For 9i, relevant tables have their MONITORING
attribute set, so that DBA_TAB_MODIFICATIONS
view is populated
This package is either compiled as a privileged user
and/or it is compiled under someone else and
all applicable grants on system packages and
dynamic performance views are made
*/
AS
--Removes all Statistic for the Database
PROCEDURE delete_all_stats
AS
BEGIN
DBMS_STATS.delete_dictionary_stats;
DBMS_STATS.delete_database_stats;
EXCEPTION
WHEN OTHERS
THEN
RAISE;
END;
--Procedure that disables the automatic gathering of stats
--since, until 10g, this 'canned' procedure is not adequate
--to gather statistics responsibly
PROCEDURE disable_auto_stats_job
AS
BEGIN
dbms_scheduler.disable('SYS.GATHER_STATS_JOB');
END;
--Procedure that disables the automatic segment space advisor job
--The running of this job can use up a lot of resources, hence
--why many disable it
PROCEDURE disable_auto_space_job
AS
BEGIN
dbms_scheduler.disable('SYS.AUTO_SPACE_ADVISOR_JOB');
END;
PROCEDURE gather_data_dict_stats
AS
BEGIN
DBMS_STATS.gather_dictionary_stats;
EXCEPTION
WHEN OTHERS
THEN
RAISE;
END;
PROCEDURE gather_fixed_stats
AS
BEGIN
DBMS_STATS.gather_fixed_objects_stats;
EXCEPTION
WHEN OTHERS
THEN
RAISE;
END;
PROCEDURE gather_stats
AS
/* Cursor with the names of all the non-partitioned, non-system, non-locked, non-iot overflow type segments
which don't currently have statistics */
lv_owner dba_tables.owner%TYPE;
lv_table_name dba_tables.table_name%TYPE;
lv_part_name dba_tab_partitions.partition_name%TYPE;
lv_change_factor NUMBER (16, 2);
lv_partitioned VARCHAR (10);
CURSOR lc_ns_np_nostats
IS
SELECT owner, table_name
FROM dba_tables
WHERE partitioned = 'NO'
AND NOT owner IN ('SYS','SYSTEM','OUTLN')
AND num_rows IS NULL
AND iot_type IS NULL
AND NOT (owner, table_name) IN (SELECT owner, table_name
FROM dba_tab_statistics
WHERE stattype_locked = 'ALL')
AND NOT (owner, table_name) IN (SELECT owner, table_name
FROM dba_external_tables);
CURSOR lc_ns_p_nostats
IS
SELECT dtp.table_owner, dtp.table_name, dtp.partition_name
FROM dba_tab_partitions dtp, dba_tables dt
WHERE dtp.num_rows IS NULL
AND dtp.table_name = dt.table_name
AND dtp.table_owner = dt.owner
AND dt.iot_type IS NULL
AND NOT dtp.table_owner IN ('SYS','SYSTEM','OUTLN')
AND NOT (dtp.table_owner, dtp.table_name) IN (
SELECT owner, table_name
FROM dba_tab_statistics
WHERE stattype_locked =
'ALL')
AND NOT (dtp.table_owner, dtp.table_name) IN (SELECT owner, table_name
FROM dba_external_tables);
CURSOR lc_ss
IS
SELECT dtm.table_owner, dtm.table_name, dtm.partition_name,
ROUND ( (dtm.inserts + dtm.updates + dtm.deletes)
/ dt.num_rows,
2
)
* 100 "CHANGE_FACTOR",
dt.partitioned
FROM sys.dba_tab_modifications dtm, dba_tables dt
WHERE dtm.table_owner = dt.owner
AND dtm.table_name = dt.table_name
AND NOT dtm.table_owner IN ('SYS','SYSTEM','OUTLN')
AND NOT dt.num_rows IS NULL
AND iot_type IS NULL
AND ( (dt.partitioned = 'YES' AND NOT dtm.partition_name IS NULL
)
OR (dt.partitioned = 'NO' AND dtm.partition_name IS NULL)
)
AND NOT (dtm.table_owner, dtm.table_name) IN (
SELECT dts.owner, dts.table_name
FROM dba_tab_statistics dts
WHERE dts.stattype_locked =
'ALL')
AND NOT (dtm.table_owner, dtm.table_name) IN (
SELECT det.owner,
det.table_name
FROM dba_external_tables det);
BEGIN
/**************************BEGIN EMPTYSTATS************************************/
-- First process the cursor above; alternatively, you could
-- do something similar via a call to DBMS_STATS with the 'GATHER EMPTY' option
OPEN lc_ns_np_nostats; -- open the cursor before fetching
LOOP
FETCH lc_ns_np_nostats
INTO lv_owner, lv_table_name; -- fetches 2 columns into variables
EXIT WHEN lc_ns_np_nostats%NOTFOUND;
-- Call stats package
DBMS_OUTPUT.put_line ( 'Gathering Stats for Table and Indexes of '
|| lv_owner
|| '.'
|| lv_table_name
|| ' because they are empty...'
);
-- For these tables we will use most of the oracle defaults
-- We are assuming that partitioned tables are to be treated differently in terms
-- of sample size, parallelism degree and the like
--trap any errors by placing in its own block
BEGIN
DBMS_STATS.gather_table_stats
(ownname => lv_owner,
tabname => lv_table_name,
granularity => 'AUTO',
method_opt => 'for all columns',
CASCADE => DBMS_STATS.auto_cascade,
estimate_percent => DBMS_STATS.auto_sample_size
);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line (SQLCODE || ' - ' || SQLERRM);
END;
END LOOP;
CLOSE lc_ns_np_nostats;
-- Next we process table partitions whose statistics are null/empty
-- We will use smaller samples and parallelism when appropriate
OPEN lc_ns_p_nostats; -- open the cursor before fetching
LOOP
FETCH lc_ns_p_nostats
INTO lv_owner, lv_table_name, lv_part_name;
-- fetches 3 columns into variables
EXIT WHEN lc_ns_p_nostats%NOTFOUND;
-- Call stats package
DBMS_OUTPUT.put_line ( 'Gathering Stats for Partition '
|| lv_owner
|| '.'
|| lv_table_name
|| '.'
|| lv_part_name
|| ' because they are empty...'
);
-- For these partitioned tables we will use smaller sample sizes
-- Trap Errors in anonymous block
BEGIN
DBMS_STATS.gather_table_stats (ownname => lv_owner,
tabname => lv_table_name,
partname => lv_part_name,
granularity => 'AUTO',
method_opt => 'for all columns',
CASCADE => DBMS_STATS.auto_cascade,
DEGREE => 1,
estimate_percent => .00001
);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line (SQLCODE || ' - ' || SQLERRM);
END;
END LOOP;
CLOSE lc_ns_p_nostats;
--Execute Global Database Stats Gathering with GATHER EMPTY option
--in order to catch any objects that were missed above because of errors, etc.
DBMS_OUTPUT.put_line ('Executing Global Stats Procedure to Catch Any Missed Empty Objects...');
BEGIN
dbms_stats.gather_database_stats(method_opt=>'for all columns',
granularity=>'AUTO',
estimate_percent=>DBMS_STATS.auto_sample_size,
degree=>DBMS_STATS.default_degree,
cascade=>DBMS_STATS.auto_cascade,
options=>'GATHER EMPTY');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line (SQLCODE || ' - ' || SQLERRM);
END;
/**************************END EMPTYSTATS************************************/
-- Lastly we flush the database monitoring information
-- and then use the information therein to gather stats
-- on significantly changed objects
/**************************BEGIN CHANGED OBJECTS STATS***********************/
DBMS_STATS.flush_database_monitoring_info ();
OPEN lc_ss; -- open the cursor before fetching
LOOP
FETCH lc_ss
INTO lv_owner, lv_table_name, lv_part_name, lv_change_factor,
lv_partitioned;
EXIT WHEN lc_ss%NOTFOUND;
-- If change factor is greater than change factor defined in the package header then we will analyze the changed object in question
IF lv_change_factor >= gn_change_factor
THEN
-- Now determine whether the Object is a partition
IF lv_partitioned = 'YES'
THEN
DBMS_OUTPUT.put_line
( 'Gathering Stats for Partition '
|| lv_owner
|| '.'
|| lv_table_name
|| '.'
|| lv_part_name
|| ' because its change factor exceeds '||gn_change_factor||'%...'
);
BEGIN
DBMS_STATS.gather_table_stats
(ownname => lv_owner,
tabname => lv_table_name,
partname => lv_part_name,
granularity => 'AUTO',
method_opt => 'for all columns',
CASCADE => DBMS_STATS.auto_cascade,
DEGREE => 1,
estimate_percent => .00001
);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line (SQLCODE || ' - ' || SQLERRM);
END;
ELSE
DBMS_OUTPUT.put_line
( 'Gathering Stats for Table and Indexes of '
|| lv_owner
|| '.'
|| lv_table_name
|| ' because its change factor exceeds '||gn_change_factor||'%...'
);
BEGIN
DBMS_STATS.gather_table_stats
(ownname => lv_owner,
tabname => lv_table_name,
granularity => 'AUTO',
method_opt => 'for all columns',
CASCADE => DBMS_STATS.auto_cascade,
estimate_percent => DBMS_STATS.auto_sample_size
);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line (SQLCODE || ' - ' || SQLERRM);
END;
END IF;
ELSE
NULL;
END IF;
END LOOP;
CLOSE lc_ss;
/**************************END CHANGED OBJECTS STATS**************************/
END;
--Procedure makes internal calls to first delete all stats
--and then gather them all over again
PROCEDURE start_over
AS
BEGIN
DBMS_OUTPUT.put_line ('Deleting all stats...');
delete_all_stats;
DBMS_OUTPUT.put_line ('Gathering fixed stats...');
gather_fixed_stats;
DBMS_OUTPUT.put_line ('Gathering user stats...');
gather_stats;
DBMS_OUTPUT.put_line ('Gathering dictionary stats...');
gather_data_dict_stats;
EXCEPTION
WHEN OTHERS
THEN
RAISE;
END;
END dba_stats_maint_pkg;
/
SHOW ERRORS;
Labels: Statistics, Tuning
11g New Feature: Statistic Gathering and Publishing Decoupled/Statistics Maintenance Changes
Posted at Wednesday, October 31, 2007
In addition to the extended statistics functionality introduced by 11g, you can now gather statistics without having to publish them right away.
This allows one to gather the statistics and test the efficacy thereof before effecting a live production environment; in other words, existing explain plans will not be invalidated/regenerated.
Reference this 'Oracle by Example' article for a great demonstration: Gathering and Publishing Statistics Independently.
Additionally, Oracle has made some significant changes which makes using the out-of-the-box statistics collection program much more feasible. Namely, DBMS_STATS, has been changed so that you can set statistics collection preferences - think arguments passed to DBMS_STATS.GATHER_*_STATS procedures - at both the global and table levels. In turn, the automatic statistics gathering job will respect these preferences when it runs (during the applicable maintenance window).
In the past, I have always disabled the aforementioned 'AutoTask' job - for this very lack of granular control - in favor of a custom statistics gathering job. This was particularly necessary in Data Warehouse environments due to heavy use of partitioning as well as concomitant data volumes. In such environments, sampling only the partitions that have changed is critical to the conservation of system resources.
With the abovementioned scenario in mind, if you want to employ the out-of-the-box 'AutoTask' job then simply set table preferences (as appropriate) using DBMS_STATS. For instance, in order to gather large partitioned tables' statistics properly, you may wish to alter the following: INCREMENTAL, DEGREE, METHOD_OPT, STALE_PERCENT, and ESTIMATE_PERCENT.
For each, verify the current value for the table:
The current setting for the table looks like the system default.
Alter the value, just for the table in question:
Verify that the global setting and table-specific setting co-exist and are, in fact, different:
Here is another example, where we change METHOD_OPT for a table, analyze it using the DBMS_STATS package and then verify that the number of buckets we specify are created. It is important to note that the DBMS_STATS.SET_TABLE_PREFS doesn't seem to support the full syntax that METHOD_OPT allows:
Set the preference:
Gather stats - this should pick up the tables preference:
Verify that the number of histograms are created per the specificed METHOD_OPT:
11g, also introduces the ability to easily revert to previous statistics' settings. They have extended the DBMS_STATS package as well as provided new *_STATS_HISTORY views.
Building on our prior example, let's say I analyze the aforementioned table again (with different options), but then I want to revert to the previous statistics above:
Analyze the table:
Verify that the number of buckets, are in fact, different:
Query the timestamp for the prior collected stats:
Restore the old stats for the table:
Validate that the old stats have been put back:
Otherwise, Oracle has not changed a lot in terms of Statistics Maintenance. The same instances in which you have had to gather statistics manually still apply: System Statistics (those found in aux_stats$), Fixed Statistics, Volatile Tables, Tables post Direct-load, etc.
Reference the following Oracle docs:
Managing Optimizer Statistics
DBMS_STATS package reference
This allows one to gather the statistics and test the efficacy thereof before effecting a live production environment; in other words, existing explain plans will not be invalidated/regenerated.
Reference this 'Oracle by Example' article for a great demonstration: Gathering and Publishing Statistics Independently.
Additionally, Oracle has made some significant changes which makes using the out-of-the-box statistics collection program much more feasible. Namely, DBMS_STATS, has been changed so that you can set statistics collection preferences - think arguments passed to DBMS_STATS.GATHER_*_STATS procedures - at both the global and table levels. In turn, the automatic statistics gathering job will respect these preferences when it runs (during the applicable maintenance window).
In the past, I have always disabled the aforementioned 'AutoTask' job - for this very lack of granular control - in favor of a custom statistics gathering job. This was particularly necessary in Data Warehouse environments due to heavy use of partitioning as well as concomitant data volumes. In such environments, sampling only the partitions that have changed is critical to the conservation of system resources.
With the abovementioned scenario in mind, if you want to employ the out-of-the-box 'AutoTask' job then simply set table preferences (as appropriate) using DBMS_STATS. For instance, in order to gather large partitioned tables' statistics properly, you may wish to alter the following: INCREMENTAL, DEGREE, METHOD_OPT, STALE_PERCENT, and ESTIMATE_PERCENT.
For each, verify the current value for the table:
SQL> select dbms_stats.get_prefs('INCREMENTAL','SH','COSTS') from dual;
DBMS_STATS.GET_PREFS('INCREMENTAL','SH','COSTS')
--------------------------------------------------------------------------------
FALSE The current setting for the table looks like the system default.
Alter the value, just for the table in question:
SQL> exec dbms_stats.set_table_prefs('SH','COSTS','INCREMENTAL' ,'TRUE') ;
PL/SQL procedure successfully completed.SQL> select dbms_stats.get_prefs('INCREMENTAL','SH','COSTS') from dual;
DBMS_STATS.GET_PREFS('INCREMENTAL','SH','COSTS')
--------------------------------------------------------------------------------
TRUE Verify that the global setting and table-specific setting co-exist and are, in fact, different:
SQL> select dbms_stats.get_prefs('INCREMENTAL') from dual;
DBMS_STATS.GET_PREFS('INCREMENTAL')
--------------------------------------------------------------------------------
FALSEHere is another example, where we change METHOD_OPT for a table, analyze it using the DBMS_STATS package and then verify that the number of buckets we specify are created. It is important to note that the DBMS_STATS.SET_TABLE_PREFS doesn't seem to support the full syntax that METHOD_OPT allows:
Set the preference:
SQL> exec dbms_stats.set_table_prefs(ownname=>'SH',tabname=>'SALES',pname=>'METHOD_OPT',pvalue=>'FOR ALL COLUMNS SIZE 10');
Gather stats - this should pick up the tables preference:
SQL> exec dbms_stats.gather_table_stats(ownname=>'SH',tabname=>'SALES');
PL/SQL procedure successfully completed.
Verify that the number of histograms are created per the specificed METHOD_OPT:
SQL> select table_name, column_name, count(*) from user_histograms
2 where table_name='SALES'
3 group by table_name, column_name;
TABLE_NAME COLUMN_NAME COUNT(*)
------------------------------ ------------------------------ ----------
SALES PROD_ID 11
SALES CHANNEL_ID 4
SALES AMOUNT_SOLD 11
SALES CUST_ID 11
SALES PROMO_ID 4
SALES QUANTITY_SOLD 1
SALES TIME_ID 11
11g, also introduces the ability to easily revert to previous statistics' settings. They have extended the DBMS_STATS package as well as provided new *_STATS_HISTORY views.
Building on our prior example, let's say I analyze the aforementioned table again (with different options), but then I want to revert to the previous statistics above:
Analyze the table:
SQL> exec dbms_stats.gather_table_stats(ownname=>'SH',tabname=>'SALES', method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
PL/SQL procedure successfully completed.
Verify that the number of buckets, are in fact, different:
SQL> select table_name, column_name, count(*) from user_histograms
2 where table_name='SALES'
3 group by table_name, column_name;
TABLE_NAME COLUMN_NAME COUNT(*)
------------------------------ -------------------- ----------
SALES PROD_ID 72
SALES CHANNEL_ID 4
SALES AMOUNT_SOLD 250
SALES CUST_ID 255
SALES PROMO_ID 4
SALES QUANTITY_SOLD 1
SALES TIME_ID 2
Query the timestamp for the prior collected stats:
SQL> select count(*), stats_update_time
2 from user_tab_stats_history
3 where table_name='SALES'
4 group by stats_update_time;
COUNT(*) STATS_UPDATE_TIME
---------- ----------------------------------------
29 25-JAN-08 11.18.09.693351 AM -07:00
29 25-JAN-08 11.18.57.432585 AM -07:00
Restore the old stats for the table:
SQL> exec dbms_stats.restore_table_stats(ownname=>'SH',tabname=>'SALES',AS_OF_TIMESTAMP=>'25-JAN-08 11.18.57.432585 AM -07:00');
PL/SQL procedure successfully completed.
Validate that the old stats have been put back:
SQL> select table_name, column_name, count(*) from user_histograms
2 where table_name='SALES'
3 group by table_name, column_name;
TABLE_NAME COLUMN_NAME COUNT(*)
------------------------------ -------------------- ----------
SALES PROD_ID 11
SALES CHANNEL_ID 4
SALES AMOUNT_SOLD 11
SALES CUST_ID 11
SALES PROMO_ID 4
SALES QUANTITY_SOLD 1
SALES TIME_ID 11
Otherwise, Oracle has not changed a lot in terms of Statistics Maintenance. The same instances in which you have had to gather statistics manually still apply: System Statistics (those found in aux_stats$), Fixed Statistics, Volatile Tables, Tables post Direct-load, etc.
Reference the following Oracle docs:
Managing Optimizer Statistics
DBMS_STATS package reference
Labels: 11g, Statistics, Tuning
11g New Feature: Extended Optimizer Statistics
Posted at Tuesday, October 30, 2007
In 11g you can now correlate the relationship between 2 skewed columns. Here is a great 'Oracle by Example' article that exhibits how you can use extended statistics to aid the optimizer's explain plans: Using Extended Statistics to Optimize Multi-Column Relationships and Function-Based Statistics
Labels: 11g, Statistics, Tuning
11g New Feature: SQL Result Cache
Posted at Friday, October 26, 2007
In previous versions it was possible to manipulate the instance's memory structures to improve the performance of user queries. For instance, a table or index could be "pinned" into memory via usage of the db_keep_cache. Alternatively, tables could be "cached," prohibiting tables - accessed via a full scan - from being placed on the tail-end of the LRU.
In 11g Oracle introduces a new area of memory within the shared pool, in which it pins the blocks of appropriately marked user queries and/or deterministic PL/SQL programs. Once pinned, these blocks can be reused by future submissions of the query and/or PL/SQL calls in question; this significantly reduces I/O. This is referred to as server-side SQL Result Caching.
Oracle also, introduced an OCI-centric client-side SQL Result Caching feature with 11g, however this post will cover just the server-side functionality since this has much more promise in terms of widespread use.
The following example shows how to set-up and test this new feature.
Change instance parameters as appropriate
In my example, I am using Oracle's new Automatic Memory functionality. As you can see, I am letting Oracle manage the sizing of my instance, both pga_aggregate_target and sga_target. The only exception is that I define a lower-limit for the shared_pool_size. I do this so that I can explicit set the maximum size the result cache can grow to as specified by result_cache_max_size:
After cycling the instance, confirm that the ceiling set for the result cache has been properly set:
In my case should result in
Similarly,
Should result in
I create a couple of sample objects and load some sample data to demonstrate how using this feature can improve the performance of repetitive queries
At this point, I have a table with some sample data and a properly configured SQL Result Cache with which to demonstrate this functionality.
Sample Performance without the use of SQL Result Caching
Since our example assumes a query that is run repetitively, the second run of the aforementioned query should resemble roughly what the performance would be, because the plan will have already been established, etc.; this second run will be the baseline
When the database's result_cache_mode initialization parameter is set to 'MANUAL' the /*+ result_cache */ hint must be supplied in order to cache the query's results and/or for the optimizer to even consider using previously cached results towards satisfying a user request.
Flush the Database Buffer Cache, Shared Pool, and Result Cache. Afterwards, pin the results of the query in question
As you can see, the explain plan is a little different. It reflects the fact that Oracle automatically caches the results of the query. You can now query the system to validate this very fact.
Results in
The memory report also reflects the result cache is now being used.
Of course it is subsequent submissions of the same query which yield the performance gain.
As you can see, there are no physical reads and no consistent gets when the same query is executed subsequently. Notice that the explain plan shows that the previously establish cache id is used instead of performing the original plan's operations.
Vital statistics concerning the SQL Result Cache can be found here
It is important to watch the 'Invalidation Count' because this represents how many times the result sets were invalidated due to updates to the underlying table data. Ideally, your 'Find Count' would be high and your 'Invalidation Count' would be low in order to reap maximum performance gains.
The following functionality can be applied to PL/SQL programs as well. See this great Oracle by Example article Improving Application Performance with Result Cache. Also, refer to this article on the topic from a recent Oracle Magazine: On the PL/SQL Function Result Cache.
In 11g Oracle introduces a new area of memory within the shared pool, in which it pins the blocks of appropriately marked user queries and/or deterministic PL/SQL programs. Once pinned, these blocks can be reused by future submissions of the query and/or PL/SQL calls in question; this significantly reduces I/O. This is referred to as server-side SQL Result Caching.
Oracle also, introduced an OCI-centric client-side SQL Result Caching feature with 11g, however this post will cover just the server-side functionality since this has much more promise in terms of widespread use.
The following example shows how to set-up and test this new feature.
Change instance parameters as appropriate
SELECT name "Parameter"
, round(value/1024/1024) "MB"
FROM v$spparameter
WHERE name IN ('shared_pool_size',
'result_cache_max_size',
'memory_target',
'memory_max_target',
'sga_target',
'pga_aggregate_target')
ORDER BY 2 DESC;
In my example, I am using Oracle's new Automatic Memory functionality. As you can see, I am letting Oracle manage the sizing of my instance, both pga_aggregate_target and sga_target. The only exception is that I define a lower-limit for the shared_pool_size. I do this so that I can explicit set the maximum size the result cache can grow to as specified by result_cache_max_size:
Parameter MB
---------------------------------------- ----------
memory_max_target 256
memory_target 240
shared_pool_size 64
result_cache_max_size 16
pga_aggregate_target 0
sga_target 0
After cycling the instance, confirm that the ceiling set for the result cache has been properly set:
SELECT
(SELECT value
FROM V$RESULT_CACHE_STATISTICS
WHERE ID=4)/
(SELECT value
FROM V$RESULT_CACHE_STATISTICS
WHERE ID=1) "Result Set Max Size in MB" FROM DUAL;
In my case should result in
16
Similarly,
EXECUTE DBMS_RESULT_CACHE.MEMORY_REPORT;
Should result in
R e s u l t C a c h e M e m o r y R e p o r t
[Parameters]
Block Size = 1K bytes
Maximum Cache Size = 16M bytes (16K blocks)
Maximum Result Size = 16M bytes (16K blocks)
[Memory]
Total Memory = 5132 bytes [0.005% of the Shared Pool]
... Fixed Memory = 5132 bytes [0.005% of the Shared Pool]
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]
I create a couple of sample objects and load some sample data to demonstrate how using this feature can improve the performance of repetitive queries
DROP TABLE SCOTT.MY_EMPLOYEE;
CREATE TABLE SCOTT.MY_EMPLOYEE
(
EMPNO NUMBER(32) NOT NULL,
ENAME VARCHAR2(100),
SAL NUMBER(16,2),
GRADE NUMBER(1),
UPDATE_TS TIMESTAMP(6) DEFAULT systimestamp)
TABLESPACE USERS;
CREATE UNIQUE INDEX SCOTT.PK_MY_EMPLOYEE ON SCOTT.MY_EMPLOYEE
(EMPNO)
TABLESPACE USERS;
ALTER TABLE SCOTT.MY_EMPLOYEE ADD (CONSTRAINT PK_MY_EMPLOYEE PRIMARY KEY (EMPNO) USING INDEX);
set serverout on
declare
begin
execute immediate 'truncate table scott.my_employee';
for i in 1..10000000 loop
insert into scott.my_employee (empno, ename, sal, grade) values (i,'James'||i,i,1);
end loop;
commit;
execute immediate 'analyze table scott.my_employee estimate statistics sample 1 percent';
exception
when others then
dbms_output.put_line(sqlerrm);
end;
/
At this point, I have a table with some sample data and a properly configured SQL Result Cache with which to demonstrate this functionality.
Sample Performance without the use of SQL Result Caching
SQL> set timing on
SQL> set autotrace on
SQL> select count(*) from scott.my_employee;
COUNT(*)
----------
10000000
Elapsed: 00:00:01.56
Execution Plan
----------------------------------------------------------
Plan hash value: 2242393760
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20414 (1)| 00:04:05 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| PK_MY_EMPLOYEE | 10M| 20414 (1)| 00:04:05 |
---------------------------------------------------------------------------
Note
-----
- SQL plan baseline "SYS_SQL_PLAN_fd812f285bb05d3f" used for this statement
Statistics
----------------------------------------------------------
2457 recursive calls
0 db block gets
20424 consistent gets
19930 physical reads
0 redo size
418 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
84 sorts (memory)
0 sorts (disk)
1 rows processed
Since our example assumes a query that is run repetitively, the second run of the aforementioned query should resemble roughly what the performance would be, because the plan will have already been established, etc.; this second run will be the baseline
SQL> select count(*) from scott.my_employee;
COUNT(*)
----------
10000000
Elapsed: 00:00:01.50
Execution Plan
----------------------------------------------------------
Plan hash value: 2242393760
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20414 (1)| 00:04:05 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| PK_MY_EMPLOYEE | 10M| 20414 (1)| 00:04:05 |
---------------------------------------------------------------------------
Note
-----
- SQL plan baseline "SYS_SQL_PLAN_fd812f285bb05d3f" used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
19877 consistent gets
19876 physical reads
0 redo size
418 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
When the database's result_cache_mode initialization parameter is set to 'MANUAL' the /*+ result_cache */ hint must be supplied in order to cache the query's results and/or for the optimizer to even consider using previously cached results towards satisfying a user request.
Flush the Database Buffer Cache, Shared Pool, and Result Cache. Afterwards, pin the results of the query in question
SQL> execute dbms_result_cache.flush;
SQL> alter system flush shared_pool;
SQL> alter system flush buffer_cache;
SQL> select /*+ result_cache */ count(*) from scott.my_employee;
COUNT(*)
----------
10000000
Elapsed: 00:00:00.89
Execution Plan
----------------------------------------------------------
Plan hash value: 3630384065
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5581 (2)| 00:01:07 |
| 1 | RESULT CACHE | 9kuwb9z28jrdqdcpuxk1j0ry8v | | | |
| 2 | SORT AGGREGATE | | 1 | | |
| 3 | INDEX FAST FULL SCAN| PK_MY_EMPLOYEE | 10M| 5581 (2)| 00:01:07 |
---------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(SCOTT.MY_EMPLOYEE); attributes=(single-row); name="select /*+ result_cache */ count(*) from scott.my_employee"
Note
-----
- SQL plan baseline "SYS_SQL_PLAN_910af5d7eff1e4f3" used for this statement
Statistics
----------------------------------------------------------
1000 recursive calls
0 db block gets
20322 consistent gets
20113 physical reads
0 redo size
418 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
42 sorts (memory)
0 sorts (disk)
1 rows processed
As you can see, the explain plan is a little different. It reflects the fact that Oracle automatically caches the results of the query. You can now query the system to validate this very fact.
SELECT type,
cache_id,
object_no,
space_overhead
FROM V$RESULT_CACHE_OBJECTS;
Results in
TYPE CACHE_ID OBJECT_NO SPACE_OVERHEAD
---------- ---------------------------------------- ---------- --------------
Dependency SCOTT.MY_EMPLOYEE 71439 0
Result 9kuwb9z28jrdqdcpuxk1j0ry8v 0 238
The memory report also reflects the result cache is now being used.
R e s u l t C a c h e M e m o r y R e p o r t
[Parameters]
Block Size = 1K bytes
Maximum Cache Size = 16M bytes (16K blocks)
Maximum Result Size = 16M bytes (16K blocks)
[Memory]
Total Memory = 103528 bytes [0.095% of the Shared Pool]
... Fixed Memory = 5132 bytes [0.005% of the Shared Pool]
... Dynamic Memory = 98396 bytes [0.090% of the Shared Pool]
....... Overhead = 65628 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 30 blocks
........... Used Memory = 2 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 1 blocks
................... SQL = 1 blocks (1 count)
Of course it is subsequent submissions of the same query which yield the performance gain.
SQL> select /*+ result_cache */ count(*) from scott.my_employee;
COUNT(*)
----------
10000000
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 3630384065
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5581 (2)| 00:01:07 |
| 1 | RESULT CACHE | 9kuwb9z28jrdqdcpuxk1j0ry8v | | | |
| 2 | SORT AGGREGATE | | 1 | | |
| 3 | INDEX FAST FULL SCAN| PK_MY_EMPLOYEE | 10M| 5581 (2)| 00:01:07 |
---------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(SCOTT.MY_EMPLOYEE); attributes=(single-row); name="select /*+ result_cache */ count(*) from scott.my_employee"
Note
-----
- SQL plan baseline "SYS_SQL_PLAN_910af5d7eff1e4f3" used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
418 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
As you can see, there are no physical reads and no consistent gets when the same query is executed subsequently. Notice that the explain plan shows that the previously establish cache id is used instead of performing the original plan's operations.
Vital statistics concerning the SQL Result Cache can be found here
SQL> SELECT * FROM V$RESULT_CACHE_STATISTICS;
ID NAME VALUE
---------- -------------------------------------------------- ----------
1 Block Size (Bytes) 1024
2 Block Count Maximum 16384
3 Block Count Current 32
4 Result Size Maximum (Blocks) 16384
5 Create Count Success 1
6 Create Count Failure 0
7 Find Count 3
8 Invalidation Count 0
9 Delete Count Invalid 0
10 Delete Count Valid 0
It is important to watch the 'Invalidation Count' because this represents how many times the result sets were invalidated due to updates to the underlying table data. Ideally, your 'Find Count' would be high and your 'Invalidation Count' would be low in order to reap maximum performance gains.
The following functionality can be applied to PL/SQL programs as well. See this great Oracle by Example article Improving Application Performance with Result Cache. Also, refer to this article on the topic from a recent Oracle Magazine: On the PL/SQL Function Result Cache.
SQL Profiling - An Example
Posted at Wednesday, June 20, 2007
Often times I find myself in situations where poor SQL is submitted to the database on behalf of 3rd-party applications that generate code on the fly. One of the many tools that I find useful in responding to this is SQL Profiling. I have also found SQL Profiling to be effective in tuning SQL statements created by applications that have transitioned from rule-based to cost-based optimization without the requisite performance testing. Below are the sequence of steps that I use to determine and accept whether a SQL Profile is a viable option worth exploring for a particular sql statement:
First Create the SQL Tuning Task:
set serverout on;
DECLARE
stti VARCHAR2(100); /* Sql Tuning Identifier */
BEGIN
stti := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => '[sql id here]',
plan_hash_value => [hash value here],
scope => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,
time_limit => DBMS_SQLTUNE.TIME_LIMIT_DEFAULT,
task_name => '[task name here]',
description => '[description here]');
DBMS_OUTPUT.PUT_LINE('SQL Tuning Identifier:'||stti);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Exception: '||sqlerrm);
END;
/
Execute the previously created task:
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name=>'[task name here]');
END;
/
Examine the results of the Tuning Task:
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( '[task name here]') FROM DUAL;
Implement (accept) the profile (if applicable):
BEGIN
DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name =>'[task name here]', replace => TRUE);
END;
/
If you need to drop the profile:
BEGIN
DBMS_SQLTUNE.DROP_SQL_PROFILE(name =>'[name from dba_sql_profiles]');
END;
/
Labels: Tuning
How to Enable Asynchronous I/O - Linux
Posted at Tuesday, January 24, 2006
By default, Oracle's kernel is not linked to enable asynchronous I/O on Linux-distributions. Without asynchronous I/O enabled - or some other "quick" I/O subsystem - processes that perform I/O have to wait for their requests to complete before moving on to perform other tasks. This doesn't scale well when it comes to I/O-intensive applications; in particular: databases.
The note contained herein demonstrates how to enable asynchronous I/O on SUSE. Note that each platform is unique and therefore the exact steps may differ from distribution to distribution. The basic process, however, should be common.
How_To_Enable_Asynchronous_IO_Oracle.pdf
The note contained herein demonstrates how to enable asynchronous I/O on SUSE. Note that each platform is unique and therefore the exact steps may differ from distribution to distribution. The basic process, however, should be common.
How_To_Enable_Asynchronous_IO_Oracle.pdf
Labels: Tuning
_507.png)
Subscribe
