oracle hints
google_protectAndRun("render_ads.js::google_render_ad", google_handleError, google_render_ad);
_uacct = "UA-221075-1";
urchinTracker();
/*+ hint */
/*+ hint(argument) */
/*+ hint(argument-1 argument-2) */
All hints except /*+ rule */ cause the CBO to be used. Therefore, it is good practise to analyze the underlying tables if hints are used (or the query is fully hinted.
There should be n;o schema names in hints. Hints must use aliases if alias names are used for table names. So the following is wrong:
select /*+ index(scott.emp ix_emp) */ from scott.emp emp_alias
better:
select /*+ index(emp_alias ix_emp) */ ... from scott.emp emp_alias
Why using hints
It is a perfect valid question to ask why hints should be used. Oracle comes with an optimizer that promises to optimize a query's execution plan. When this optimizer is really doing a good job, no hints should be required at all.
Sometimes, however, the characteristics of the data in the database are changing rapidly, so that the optimizer (or more accuratly, its statistics) are out of date. In this case, a hint could help.
It must also be noted, that Oracle allows to lock the statistics when they look ideal which should make the hints meaningless again.
Hint categories
Hints can be categorized as follows:
· Hints for Optimization Approaches and Goals,
· Hints for Access Paths, Hints for Query Transformations,
· Hints for Join Orders,
· Hints for Join Operations,
· Hints for Parallel Execution,
· Additional Hints
Documented Hints
Hints for Optimization Approaches and Goals
· ALL_ROWSOne of the hints that 'invokes' the Cost based optimizer ALL_ROWS is usually used for batch processing or data warehousing systems.
· FIRST_ROWSOne of the hints that 'invokes' the Cost based optimizer FIRST_ROWS is usually used for OLTP systems.
· CHOOSEOne of the hints that 'invokes' the Cost based optimizerThis hint lets the server choose (between ALL_ROWS and FIRST_ROWS, based on statistics gathered.
· RULEThe RULE hint should be considered deprecated as it is dropped from Oracle9i2.
See also the following initialization parameters: optimizer_mode,optimizer_max_permutations, optimizer_index_cost_adj,optimizer_index_caching and
Hints for Access Paths
· CLUSTERPerforms a nested loop by the cluster index of one of the tables.
· FULLPerforms full table scan.
· HASHHashes one table (full scan) and creates a hash index for that table. Then hashes other table and uses hash index to find corresponding records. Therefore not suitable for <> join conditions.
· ROWIDRetrieves the row by rowid
· INDEXSpecifying that index index_name should be used on table tab_name: /*+ index (tab_name index_name) */ Specifying that the index should be used the the CBO thinks is most suitable. (Not always a good choice). Starting with Oracle 10g, the index hint can be described: /*+ index(my_tab my_tab(col_1, col_2)) */. Using the index on my_tab that starts with the columns col_1 and col_2.
· INDEX_ASC
· INDEX_COMBINE
· INDEX_DESC
· INDEX_FFS
· INDEX_JOIN
· NO_INDEX
· AND_EQUALThe AND_EQUAL hint explicitly chooses an execution plan that uses an access path that merges the scans on several single-column indexes
Hints for Query Transformations
· FACTThe FACT hint is used in the context of the star transformation to indicate to the transformation that the hinted table should be considered as a fact table.
· MERGE
· NO_EXPAND
· NO_EXPAND_GSET_TO_UNION
· NO_FACT
· NO_MERGE
· NOREWRITE
· REWRITE
· STAR_TRANSFORMATION
· USE_CONCAT
Hints for Join Operations
· DRIVING_SITE
· HASH_AJ
· HASH_SJ
· LEADING
· MERGE_AJ
· MERGE_SJ
· NL_AJ
· NL_SJ
· USE_HASH
· USE_MERGE
· USE_NL
Hints for Parallel Execution
· NOPARALLEL
· PARALLEL
· NOPARALLEL_INDEX
· PARALLEL_INDEX
· PQ_DISTRIBUTE
Additional Hints
· ANTIJOIN
· APPENDIf a table or an index is specified with nologging, this hint applied with an insert statement produces a direct path insert which reduces generation of redo.
· BITMAP
· BUFFER
· CACHE
· CARDINALITY
· CPU_COSTING
· DYNAMIC_SAMPLING
· INLINE
· MATERIALIZE
· NO_ACCESS
· NO_BUFFER
· NO_MONITORING
· NO_PUSH_PRED
· NO_PUSH_SUBQ
· NO_QKN_BUFF
· NO_SEMIJOIN
· NOAPPEND
· NOCACHE
· OR_EXPAND
· ORDERED
· ORDERED_PREDICATES
· PUSH_PRED
· PUSH_SUBQ
· QB_NAME
· RESULT_CACHE (Oracle 11g)
· SELECTIVITY
· SEMIJOIN
· SEMIJOIN_DRIVER
· STARThe STAR hint forces a star query plan to be used, if possible. A star plan has the largest table in the query last in the join order and joins it with a nested loops join on a concatenated index. The STAR hint applies when there are at least three tables, the large table's concatenated index has at least three columns, and there are no conflicting access or join method hints. The optimizer also considers different permutations of the small tables.
· SWAP_JOIN_INPUTS
· USE_ANTI
· USE_SEMI
Undocumented hints:
· BYPASS_RECURSIVE_CHECKWorkaraound for bug 1816154
· BYPASS_UJVC
· CACHE_CB
· CACHE_TEMP_TABLE
· CIV_GB
· COLLECTIONS_GET_REFS
· CUBE_GB
· CURSOR_SHARING_EXACT
· DEREF_NO_REWRITE
· DML_UPDATE
· DOMAIN_INDEX_NO_SORT
· DOMAIN_INDEX_SORT
· DYNAMIC_SAMPLING
· DYNAMIC_SAMPLING_EST_CDN
· EXPAND_GSET_TO_UNION
· FORCE_SAMPLE_BLOCK
· GBY_CONC_ROLLUP
· GLOBAL_TABLE_HINTS
· HWM_BROKERED
· IGNORE_ON_CLAUSE
· IGNORE_WHERE_CLAUSE
· INDEX_RRS
· INDEX_SS
· INDEX_SS_ASC
· INDEX_SS_DESC
· LIKE_EXPAND
· LOCAL_INDEXES
· MV_MERGE
· NESTED_TABLE_GET_REFS
· NESTED_TABLE_SET_REFS
· NESTED_TABLE_SET_SETID
· NO_FILTERING
· NO_ORDER_ROLLUPS
· NO_PRUNE_GSETS
· NO_STATS_GSETS
· NO_UNNEST
· NOCPU_COSTING
· OVERFLOW_NOMOVE
· PIV_GB
· PIV_SSF
· PQ_MAP
· PQ_NOMAP
· REMOTE_MAPPED
· RESTORE_AS_INTERVALS
· SAVE_AS_INTERVALS
· SCN_ASCENDING
· SKIP_EXT_OPTIMIZER
· SQLLDR
· SYS_DL_CURSOR
· SYS_PARALLEL_TXN
· SYS_RID_ORDER
· TIV_GB
· TIV_SSF
· UNNEST
· USE_TTT_FOR_GSETS
Specifying a query block in a hint.
Q&A about RAC and Grid with the RAC Experts
I recently got the opportunity to sit down and talk to two RAC gurus and learn what RAC is and how it relates to Oracle's Grid technology.Listen to the discussion with Philip Newland and Scott Jesse, both from Oracle Corp. Philip is a technical manager with Oracle's RAC Pack team and Scott is an Oracle Support Services Manager for the RAC Assurance Team. Scott is also the co-author of two Oracle Press books, "Oracle9i for Windows 2000 Tips & Techniques" and "Oracle Database 10g High Availability with RAC, Flashback & DataGuard".
There were plenty of questions asked and answered. Here are five important questions. Listen to the podcast to hear the rest.
What does Oracle mean by Grid?
An Oracle Grid allows you to add computing capacity, CPUs or storage, on demand as needed without pre-purchasing monolithic hardware. On an Oracle Grid, you can add capacity one cheap PC at a time.
There is no product for sale called Oracle Grid. You can't just walk into a store and buy a grid. Oracle Grid is a technology composed of several innovative Oracle products.
What components make up an Oracle Grid?
RAC
Oracle Real Application Clusters (RAC) allows Oracle customers to add database capacity (by adding servers) to an existing cluster. RAC allows a database to be spread across multiple servers. You can dynamically add and remove nodes (servers) as required.
RAC allows an application to transparently scale, add performance and be available 24/7. RAC can be very affordable to scale because it allows you to add cheap servers when you need them.
ASM
Oracle Automatic Storage Management (ASM) eases storage management by abstracting file systems to where DBAs need them. ASM also allows consolidation of storage so that applications that need storage have it available when they need it.
Clusterware
Oracle Clusterware provides the intelligence for a cluster. A cluster is a series of servers acting as a single entity. Clusterware provides the management and monitoring of a cluster.
Oracle Clusterware is not limited to providing scalability and high availability for Oracle Databases. With Oracle Clusterware, you can provide these services for third-party databases, application servers and pretty much any other kind of application.
Cluster File System
Oracle Cluster File System (OCFS & ACFS2) allows an Oracle database cluster to share disk across many servers. OCFS ensures that Oracle sees a consistent image of the disks on each server.
Does RAC work with Oracle Standard Edition?
Oracle Standard Edition, the edition of Oracle for the SMB market, comes with a license for a 4 node RAC cluster. A standard edition license allows up to 4 CPU sockets in a cluster. Those 4 sockets may be in a single server or in two, three or four servers. As long as your cluster does not exceed 4 sockets, RAC is included as part of your Standard Edition License.
Standard Edition does not limit the size of your database in anyway so as long as the processing power for a four socket configuration suits your needs, this can be a great way to save. If you need to scale to a larger cluster, Standard Edition can easily be upgraded to Enterprise Edition allowing you up to 1000 nodes.
Standard Edition RAC configurations are required to use ASM. Because ASM saves time and effort, this is not really a limitation but more of a method for Oracle to help you ensure your success.
How do I monitor my Grid?
Oracle provides both DB Control and Grid Control for monitoring and managing your databases. DB control is a web based tool that allows you to manage one database at a time. Oracle Grid Control allows you to manage entire grids and is an add on package. Grid Control can simplify all of your grid management tasks.
Can I mix and match hardware?
Yes. Oracle expects that vendors are improving hardware constantly and that customers will need to upgrade hardware over time. Rather than force customers to maintain a single vendor or configuration, Oracle RAC will work across various hardware configurations. While one server may be a single CPU and 4GB of RAM, another server in the same cluster can be 4 CPUs (with dual cores) and 16GB of RAM.
The only requirement is that the Operating Systems must match: Windows to Windows, Linux to Linux, 32 bit to 32 bit, etc.
Summary
Grid Computing - Oracle allows you to scale by tying together cheap hardware in a cluster allowing multiple servers to act as one. Oracle provides the software that enables database grids (via RAC) as well as storage grids (via ASM and OCFS). Oracle's Grid allows a business to start as large or small as they need, spending money on hardware and other system resources only when it is actually needed. RAC and Grid is transparent to the application allowing painless and immediate scaling when required.
Oracle database Performance Tuning FAQ
From Oracle FAQ
Jump to: navigation, search
General Oracle database Performance Tuning FAQ. Remember: The best performance comes from the unnecessary work you don't do.
Contents
[hide]
1 Why and when should one tune?
2 Where should the tuning effort be directed?
3 What tools/utilities does Oracle provide to assist with performance tuning?
4 When is cost based optimization triggered?
5 How can one optimize %XYZ% queries?
6 Where can one find I/O statistics per table?
7 My query was fine last week and now it is slow. Why?
8 Does Oracle use my index or not?
9 Why is Oracle not using the damn index?
10 When should one rebuild an index?
11 How does one tune Oracle Wait event XYZ?
12 What is the difference between DBFile Sequential and Scattered Reads?
13 How does one tune the Redo Log Buffer?
if (window.showTocToggle) { var tocShowText = "show"; var tocHideText = "hide"; showTocToggle(); }
[edit] Why and when should one tune?
One of the biggest responsibilities of a DBA is to ensure that theOracle database is tuned properly. The Oracle RDBMS is highly tunable and allows the database to be monitored and adjusted to increase its performance.
One should do performance tuning for the following reasons:
The speed of computing might be wasting valuable human time (users waiting for response);
Enable your system to keep-up with the speed business is conducted; and
Optimize hardware usage to save money (companies are spending millions on hardware).
Although this site is not overly concerned with hardware issues, one needs to remember than you cannot tune a Buick into a Ferrari.
[edit] Where should the tuning effort be directed?
Consider the following areas for tuning. The order in which steps are listed needs to be maintained to prevent tuning side effects. For example, it is no good increasing the buffer cache if you can reduce I/O by rewriting a SQL statement.
Database Design (if it's not too late):
Poor system performance usually results from a poor database design. One should generally normalize to the 3NF. Selectivedenormalization can provide valuable performance improvements. When designing, always keep the "data access path" in mind. Also look at proper data partitioning, data replication, aggregation tables for decision support systems, etc.
Application Tuning:
Experience showed that approximately 80% of all Oracle system performance problems are resolved by coding optimal SQL. Also consider proper scheduling of batch tasks after peak working hours.
Memory Tuning:
Properly size your database buffers (shared_pool, buffer cache, log buffer, etc) by looking at your wait events, buffer hit ratios, system swapping and paging, etc. You may also want to pin large objects into memory to prevent frequent reloads.
Disk I/O Tuning:
Database files needs to be properly sized and placed to provide maximum disk subsystem throughput. Also look for frequent disk sorts, full table scans, missing indexes, row chaining, data fragmentation, etc.
Eliminate Database Contention:
Study database locks, latches and wait events carefully and eliminate where possible.
Tune the Operating System:
Monitor and tune operating system CPU, I/O and memory utilization. For more information, read the related Oracle FAQ dealing with your specific operating system.
What tools/utilities does Oracle provide to assist with performance tuning?
Oracle provide the following tools/ utilities to assist with performance monitoring and tuning:
ADDM (Automated Database Diagnostics Monitor) introduced inOracle 10g
TKProf
Statspack
Oracle Enterprise Manager - Tuning Pack (cost option)
Old UTLBSTAT.SQL and UTLESTAT.SQL - Begin and end stats monitoring
When is cost based optimization triggered?
It's important to have statistics on all tables for the CBO (Cost Based Optimizer) to work correctly. If one table involved in a statement does not have statistics, and optimizer dynamic sampling isn't performed, Oracle has to revert to rule-based optimization for that statement. So you really want for all tables to have statistics right away; it won't help much to just have the larger tables analyzed.
Generally, the CBO can change the execution plan when you:
Change statistics of objects by doing an ANALYZE;
Change some initialization parameters (for example: hash_join_enabled, sort_area_size, db_file_multiblock_read_count).
How can one optimize %XYZ% queries?
It is possible to improve %XYZ% (wildcard search) queries by forcing the optimizer to scan all the entries from the index instead of the table. This can be done by specifying hints.
If the index is physically smaller than the table (which is usually the case) it will take less time to scan the entire index than to scan the entire table.
Where can one find I/O statistics per table?
The STATSPACK and UTLESTAT reports show I/O per tablespace. However, they do not show which tables in the tablespace has the most I/O operations.
The $ORACLE_HOME/rdbms/admin/catio.sql script creates a sample_io procedure and table to gather the required information. After executing the procedure, one can do a simple SELECT * FROM io_per_object; to extract the required information.
For more details, look at the header comments in the catio.sql script.
My query was fine last week and now it is slow. Why?
The likely cause of this is because the execution plan has changed. Generate a current explain plan of the offending query and compare it to a previous one that was taken when the query was performing well. Usually the previous plan is not available.
Some factors that can cause a plan to change are:
Which tables are currently analyzed? Were they previously analyzed? (ie. Was the query using RBO and now CBO?)
Has OPTIMIZER_MODE been changed in INIT.ORA?
Has the DEGREE of parallelism been defined/changed on any table?
Have the tables been re-analyzed? Were the tables analyzed using estimate or compute? If estimate, what percentage was used?
Have the statistics changed?
Has the SPFILE/ INIT.ORA parameter DB_FILE_MULTIBLOCK_READ_COUNT been changed?
Has the INIT.ORA parameter SORT_AREA_SIZE been changed?
Have any other INIT.ORA parameters been changed?
What do you think the plan should be? Run the query with hints to see if this produces the required performance.
It can also happen because of a very high high water mark. Typically when a table was big, but now only contains a couple of records. Oracle still needs to scan through all the blocks to see it they contain data.
Does Oracle use my index or not?
One can use the index monitoring feature to check if indexes are used by an application or not. When the MONITORING USAGE property is set for an index, one can query the v$object_usage to see if the index is being used or not. Here is an example: SQL> CREATE TABLE t1 (c1 NUMBER);Table created. SQL> CREATE INDEX t1_idx ON t1(c1);Index created. SQL> ALTER INDEX t1_idx MONITORING USAGE;Index altered. SQL>SQL> SELECT table_name, index_name, monitoring, used FROM v$object_usage;TABLE_NAME INDEX_NAME MON USE------------------------------ ------------------------------ --- ---T1 T1_IDX YES NO SQL> SELECT * FROM t1 WHERE c1 = 1;no rows selected SQL> SELECT table_name, index_name, monitoring, used FROM v$object_usage;TABLE_NAME INDEX_NAME MON USE------------------------------ ------------------------------ --- ---T1 T1_IDX YES YES
To reset the values in the v$object_usage view, disable index monitoring and re-enable it: ALTER INDEX indexname NOMONITORING USAGE;ALTER INDEX indexname MONITORING USAGE;
[edit] Why is Oracle not using the damn index?
This problem normally only arises when the query plan is being generated by the Cost Based Optimizer (CBO). The usual cause is because the CBO calculates that executing a Full Table Scan would be faster than accessing the table via the index.
Fundamental things that can be checked are:
USER_TAB_COLUMNS.NUM_DISTINCT - This column defines the number of distinct values the column holds.
USER_TABLES.NUM_ROWS - If NUM_DISTINCT = NUM_ROWS then using an index would be preferable to doing a FULL TABLE SCAN. As the NUM_DISTINCT decreases, the cost of using an index increase thereby making the index less desirable.
USER_INDEXES.CLUSTERING_FACTOR - This defines how ordered the rows are in the index. If CLUSTERING_FACTOR approaches the number of blocks in the table, the rows are ordered. If it approaches the number of rows in the table, the rows are randomly ordered. In such a case, it is unlikely that index entries in the same leaf block will point to rows in the same data blocks.
Decrease the INIT.ORA parameter DB_FILE_MULTIBLOCK_READ_COUNT - A higher value will make the cost of a FULL TABLE SCAN cheaper.
Remember that you MUST supply the leading column of an index, for the index to be used (unless you use a FAST FULL SCAN or SKIP SCANNING).
There are many other factors that affect the cost, but sometimes the above can help to show why an index is not being used by the CBO. If from checking the above you still feel that the query should be using an index, try specifying an index hint. Obtain an explain plan of the query either using TKPROF with TIMED_STATISTICS, so that one can see the CPU utilization, or with AUTOTRACE to see the statistics. Compare this to the explain plan when not using an index.
When should one rebuild an index?
You can run the ANALYZE INDEX VALIDATE STRUCTURE command on the affected indexes - each invocation of this command creates a single row in the INDEX_STATS view. This row is overwritten by the next ANALYZE INDEX command, so copy the contents of the view into a local table after each ANALYZE. The 'badness' of the index can then be judged by the ratio of 'DEL_LF_ROWS' to 'LF_ROWS'.
For example, you may decide that index should be rebuilt if more than 20% of its rows are deleted: select del_lf_rows * 100 / decode(lf_rows,0,1,lf_rows) from index_statswhere name = 'index_ name';
How does one tune Oracle Wait event XYZ?
Here are some of the wait events from V$SESSION_WAIT and V$SYSTEM_EVENT views:
db file sequential read: Tune SQL to do less I/O. Make sure all objects are analyzed. Redistribute I/O across disks.
buffer busy waits: Increase DB_CACHE_SIZE (DB_BLOCK_BUFFERS prior to 9i)/ Analyze contention from SYS.V$BH
log buffer space: Increase LOG_BUFFER parameter or move log files to faster disks
log file sync: If this event is in the top 5, you are committing too often (talk to your developers)
log file parallel write: deals with flushing out the redo log buffer to disk. Your disks may be too slow or you have an I/O bottleneck.
Two useful sections in Oracle's Database Performance Tuning Guide:
Table of Wait Events and Potential Causes
Wait Events Statistics
What is the difference between DBFile Sequential and Scattered Reads?
Both "db file sequential read" and "db file scattered read" events signify time waited for I/O read requests to complete. Time is reported in 100's of a second for Oracle 8i releases and below, and 1000's of a second for Oracle 9i and above. Most people confuse these events with each other as they think of how data is read from disk. Instead they should think of how data is read into the SGA buffer cache.
db file sequential read:
A sequential read operation reads data into contiguous memory (usually a single-block read with p3=1, but can be multiple blocks). Single block I/Os are usually the result of using indexes. This event is also used for rebuilding the controlfile and reading datafile headers (P2=1). In general, this event is indicative of disk contention on index reads.
db file scattered read:
Similar to db file sequential reads, except that the session is reading multiple data blocks and scatters them into different discontinuous buffers in the SGA. This statistic is NORMALLY indicating disk contention on full table scans. Rarely, data from full table scans could be fitted into a contiguous buffer area, these waits would then show up as sequential reads instead of scattered reads.
The following query shows average wait time for sequential versus scattered reads: prompt "AVERAGE WAIT TIME FOR READ REQUESTS"select a.average_wait "SEQ READ", b.average_wait "SCAT READ"from sys.v_$system_event a, sys.v_$system_event bwhere a.event = 'db file sequential read'and b.event = 'db file scattered read';
How does one tune the Redo Log Buffer?
The size of the Redo log buffer is determined by the LOG_BUFFER parameter in your SPFILE/INIT.ORA file. The default setting is normally 512 KB or (128 KB * CPU_COUNT), whichever is greater. This is a static parameter and its size cannot be modified after instance startup.
SQL> show parameters log_bufferNAME TYPE value
------------------------------------ ----------- ------------------------------
log_buffer integer 262144
When a transaction is committed, info in the redo log buffer is written to a Redo Log File. In addition to this, the following conditions will trigger LGWR to write the contents of the log buffer to disk:
Whenever the log buffer is MIN(1/3 full, 1 MB) full; or
Every 3 seconds; or
When a DBWn process writes modified buffers to disk (checkpoint).
Larger LOG_BUFFER values reduce log file I/O, but may increase the time OLTP users have to wait for write operations to complete. In general, values between the default and 1 to 3MB are optimal. However, you may want to make it bigger to accommodate bulk data loading, or to accommodate a system with fast CPUs and slow disks. Nevertheless, if you set this parameter to a value beyond 10M, you should think twice about what you are doing.
SQL> SELECT name, value 2 FROM SYS.v_$sysstat 3 WHERE NAME in ('redo buffer allocation retries', 4 'redo log space wait time');NAME value
---------------------------------------------------------------- ----------
redo buffer allocation retries 3redo log space wait time 0
Statistic "REDO BUFFER ALLOCATION RETRIES" shows the number of times a user process waited for space in the redo log buffer. This value is cumulative, so monitor it over a period of time while your application is running. If this value is continuously increasing, consider increasing your LOG_BUFFER (but only if you do not see checkpointing and archiving problems).
"REDO LOG SPACE WAIT TIME" shows cumulative time (in 10s of milliseconds) waited by all processes waiting for space in the log buffer. If this value is low, your log buffer size is most likely adequate.
Retrieved from "http://www.orafaq.com/wiki/Oracle_database_Performance_Tuning_FAQ"
Categories: Frequently Asked Questions Performance tuning
function setAttributeOnload(object, attribute, val) {
if(window.addEventListener) {
window.addEventListener("load",
function(){ object[attribute] = val; }, false);
} else {
window.attachEvent('onload', function(){ object[attribute] = val; });
}
}
Earlier today, I attended a webinar by Hotsos. The webinar, titled Cost Based Optimizer: 1 of 2, was a basic introduction to the Cost Based Optimizer (CBO) in Oracle and the 10053 trace file. The CBO optimizes SQL queries and that optimization can be seen in a 10053 trace. It was not, what I would call, an advanced webinar but was done with plenty of examples and was very accessible for new comers to performance tuning. The webinar was given by Hotsos employee, Mahesh Vallanpati.Because the webinar was filled with plenty of examples, it doesn't lend itself to a blog post. I'm pretty sure they said an archive of the webinar would be available on the Hotsos site. I did take some notes though so here are a few things that do lend themselves to a blog post. If any of this is wrong, it is probably a typo on my part.The CBO is a complex, mathematical based optimizer. The CBO was introduced in Oracle 8, was improved in 9 and replaced the RBO completely in 10. While the CBO is driven by statistics many things affect it:
Database Parameters
Database Statistics
CPU and I/O Stats
DB Schema Configuration
Stored Outlines
The SQL Code Itself
The Oracle Query Cost Model (based on DB version)
Statistical data points collected by the CBO include (you can get this from the columns in stats data dictionary views):
Table Statistics
Number of rows
Number of blocks below the high water mark
Empty blocks
Average free space
Row length
Column Statistics
Distinct Values (very important)
Density
Low Value
High Value
Histogram values if they exist
Note: If you use a default date rather than null, make sure your date is not way off base as that may skew the optimizer
Index Statistics
Clustering
Depth of index
Leaf blocks
Distinct Values (very important)
Other averages
System Statistics
Average Block Read sizes (multiblock and singleblock)
CPU Speed
I/O Speed
Table Statistics
Table Statistics
Note: The frequency and % of stats collected should not be a drag on the system. You need to evaluate just what % makes the most sense as well as how frequently you need to collect them.Less logical I/O is not always better. In the session, Mahesh went over an example where, due to the dbfile_multi_block_read_count setting of 16, a full table scan with a LIO of 722 was actually chosen by the optimizer in lieu of an index scan with a LIO of 166. That means that in a full table scan, Oracle was reading 16 blocks at a time. By reading that much data at once, Oracle was actually getting more data faster than it would have by reading the index sequentially. Had the parameter been set at 8k, the index might have had better performance. Very nice detail.One good tip that Mahesh provided is: The first step of your statistics gathering should be to backup your old stats. That way you can recover if something goes wrong.Finally, Mahesh talked about a hint that I did not know existed, the CARDINALITY hint. Using the cardinality hint in a dev or test environment, you can test scalability when the amount of data in your tables grows. The hint tells Oracle that you have a different number of rows than you really have. You can run explain plans with various values and see how that impacts your plan.oracle optimizer cbo performance tuningmulti_block_read_count 10053
Views
_uacct = "UA-221075-1";
urchinTracker();
/*+ hint */
/*+ hint(argument) */
/*+ hint(argument-1 argument-2) */
All hints except /*+ rule */ cause the CBO to be used. Therefore, it is good practise to analyze the underlying tables if hints are used (or the query is fully hinted.
There should be n;o schema names in hints. Hints must use aliases if alias names are used for table names. So the following is wrong:
select /*+ index(scott.emp ix_emp) */ from scott.emp emp_alias
better:
select /*+ index(emp_alias ix_emp) */ ... from scott.emp emp_alias
Why using hints
It is a perfect valid question to ask why hints should be used. Oracle comes with an optimizer that promises to optimize a query's execution plan. When this optimizer is really doing a good job, no hints should be required at all.
Sometimes, however, the characteristics of the data in the database are changing rapidly, so that the optimizer (or more accuratly, its statistics) are out of date. In this case, a hint could help.
It must also be noted, that Oracle allows to lock the statistics when they look ideal which should make the hints meaningless again.
Hint categories
Hints can be categorized as follows:
· Hints for Optimization Approaches and Goals,
· Hints for Access Paths, Hints for Query Transformations,
· Hints for Join Orders,
· Hints for Join Operations,
· Hints for Parallel Execution,
· Additional Hints
Documented Hints
Hints for Optimization Approaches and Goals
· ALL_ROWSOne of the hints that 'invokes' the Cost based optimizer ALL_ROWS is usually used for batch processing or data warehousing systems.
· FIRST_ROWSOne of the hints that 'invokes' the Cost based optimizer FIRST_ROWS is usually used for OLTP systems.
· CHOOSEOne of the hints that 'invokes' the Cost based optimizerThis hint lets the server choose (between ALL_ROWS and FIRST_ROWS, based on statistics gathered.
· RULEThe RULE hint should be considered deprecated as it is dropped from Oracle9i2.
See also the following initialization parameters: optimizer_mode,optimizer_max_permutations, optimizer_index_cost_adj,optimizer_index_caching and
Hints for Access Paths
· CLUSTERPerforms a nested loop by the cluster index of one of the tables.
· FULLPerforms full table scan.
· HASHHashes one table (full scan) and creates a hash index for that table. Then hashes other table and uses hash index to find corresponding records. Therefore not suitable for <> join conditions.
· ROWIDRetrieves the row by rowid
· INDEXSpecifying that index index_name should be used on table tab_name: /*+ index (tab_name index_name) */ Specifying that the index should be used the the CBO thinks is most suitable. (Not always a good choice). Starting with Oracle 10g, the index hint can be described: /*+ index(my_tab my_tab(col_1, col_2)) */. Using the index on my_tab that starts with the columns col_1 and col_2.
· INDEX_ASC
· INDEX_COMBINE
· INDEX_DESC
· INDEX_FFS
· INDEX_JOIN
· NO_INDEX
· AND_EQUALThe AND_EQUAL hint explicitly chooses an execution plan that uses an access path that merges the scans on several single-column indexes
Hints for Query Transformations
· FACTThe FACT hint is used in the context of the star transformation to indicate to the transformation that the hinted table should be considered as a fact table.
· MERGE
· NO_EXPAND
· NO_EXPAND_GSET_TO_UNION
· NO_FACT
· NO_MERGE
· NOREWRITE
· REWRITE
· STAR_TRANSFORMATION
· USE_CONCAT
Hints for Join Operations
· DRIVING_SITE
· HASH_AJ
· HASH_SJ
· LEADING
· MERGE_AJ
· MERGE_SJ
· NL_AJ
· NL_SJ
· USE_HASH
· USE_MERGE
· USE_NL
Hints for Parallel Execution
· NOPARALLEL
· PARALLEL
· NOPARALLEL_INDEX
· PARALLEL_INDEX
· PQ_DISTRIBUTE
Additional Hints
· ANTIJOIN
· APPENDIf a table or an index is specified with nologging, this hint applied with an insert statement produces a direct path insert which reduces generation of redo.
· BITMAP
· BUFFER
· CACHE
· CARDINALITY
· CPU_COSTING
· DYNAMIC_SAMPLING
· INLINE
· MATERIALIZE
· NO_ACCESS
· NO_BUFFER
· NO_MONITORING
· NO_PUSH_PRED
· NO_PUSH_SUBQ
· NO_QKN_BUFF
· NO_SEMIJOIN
· NOAPPEND
· NOCACHE
· OR_EXPAND
· ORDERED
· ORDERED_PREDICATES
· PUSH_PRED
· PUSH_SUBQ
· QB_NAME
· RESULT_CACHE (Oracle 11g)
· SELECTIVITY
· SEMIJOIN
· SEMIJOIN_DRIVER
· STARThe STAR hint forces a star query plan to be used, if possible. A star plan has the largest table in the query last in the join order and joins it with a nested loops join on a concatenated index. The STAR hint applies when there are at least three tables, the large table's concatenated index has at least three columns, and there are no conflicting access or join method hints. The optimizer also considers different permutations of the small tables.
· SWAP_JOIN_INPUTS
· USE_ANTI
· USE_SEMI
Undocumented hints:
· BYPASS_RECURSIVE_CHECKWorkaraound for bug 1816154
· BYPASS_UJVC
· CACHE_CB
· CACHE_TEMP_TABLE
· CIV_GB
· COLLECTIONS_GET_REFS
· CUBE_GB
· CURSOR_SHARING_EXACT
· DEREF_NO_REWRITE
· DML_UPDATE
· DOMAIN_INDEX_NO_SORT
· DOMAIN_INDEX_SORT
· DYNAMIC_SAMPLING
· DYNAMIC_SAMPLING_EST_CDN
· EXPAND_GSET_TO_UNION
· FORCE_SAMPLE_BLOCK
· GBY_CONC_ROLLUP
· GLOBAL_TABLE_HINTS
· HWM_BROKERED
· IGNORE_ON_CLAUSE
· IGNORE_WHERE_CLAUSE
· INDEX_RRS
· INDEX_SS
· INDEX_SS_ASC
· INDEX_SS_DESC
· LIKE_EXPAND
· LOCAL_INDEXES
· MV_MERGE
· NESTED_TABLE_GET_REFS
· NESTED_TABLE_SET_REFS
· NESTED_TABLE_SET_SETID
· NO_FILTERING
· NO_ORDER_ROLLUPS
· NO_PRUNE_GSETS
· NO_STATS_GSETS
· NO_UNNEST
· NOCPU_COSTING
· OVERFLOW_NOMOVE
· PIV_GB
· PIV_SSF
· PQ_MAP
· PQ_NOMAP
· REMOTE_MAPPED
· RESTORE_AS_INTERVALS
· SAVE_AS_INTERVALS
· SCN_ASCENDING
· SKIP_EXT_OPTIMIZER
· SQLLDR
· SYS_DL_CURSOR
· SYS_PARALLEL_TXN
· SYS_RID_ORDER
· TIV_GB
· TIV_SSF
· UNNEST
· USE_TTT_FOR_GSETS
Specifying a query block in a hint.
Q&A about RAC and Grid with the RAC Experts
I recently got the opportunity to sit down and talk to two RAC gurus and learn what RAC is and how it relates to Oracle's Grid technology.Listen to the discussion with Philip Newland and Scott Jesse, both from Oracle Corp. Philip is a technical manager with Oracle's RAC Pack team and Scott is an Oracle Support Services Manager for the RAC Assurance Team. Scott is also the co-author of two Oracle Press books, "Oracle9i for Windows 2000 Tips & Techniques" and "Oracle Database 10g High Availability with RAC, Flashback & DataGuard".
There were plenty of questions asked and answered. Here are five important questions. Listen to the podcast to hear the rest.
What does Oracle mean by Grid?
An Oracle Grid allows you to add computing capacity, CPUs or storage, on demand as needed without pre-purchasing monolithic hardware. On an Oracle Grid, you can add capacity one cheap PC at a time.
There is no product for sale called Oracle Grid. You can't just walk into a store and buy a grid. Oracle Grid is a technology composed of several innovative Oracle products.
What components make up an Oracle Grid?
RAC
Oracle Real Application Clusters (RAC) allows Oracle customers to add database capacity (by adding servers) to an existing cluster. RAC allows a database to be spread across multiple servers. You can dynamically add and remove nodes (servers) as required.
RAC allows an application to transparently scale, add performance and be available 24/7. RAC can be very affordable to scale because it allows you to add cheap servers when you need them.
ASM
Oracle Automatic Storage Management (ASM) eases storage management by abstracting file systems to where DBAs need them. ASM also allows consolidation of storage so that applications that need storage have it available when they need it.
Clusterware
Oracle Clusterware provides the intelligence for a cluster. A cluster is a series of servers acting as a single entity. Clusterware provides the management and monitoring of a cluster.
Oracle Clusterware is not limited to providing scalability and high availability for Oracle Databases. With Oracle Clusterware, you can provide these services for third-party databases, application servers and pretty much any other kind of application.
Cluster File System
Oracle Cluster File System (OCFS & ACFS2) allows an Oracle database cluster to share disk across many servers. OCFS ensures that Oracle sees a consistent image of the disks on each server.
Does RAC work with Oracle Standard Edition?
Oracle Standard Edition, the edition of Oracle for the SMB market, comes with a license for a 4 node RAC cluster. A standard edition license allows up to 4 CPU sockets in a cluster. Those 4 sockets may be in a single server or in two, three or four servers. As long as your cluster does not exceed 4 sockets, RAC is included as part of your Standard Edition License.
Standard Edition does not limit the size of your database in anyway so as long as the processing power for a four socket configuration suits your needs, this can be a great way to save. If you need to scale to a larger cluster, Standard Edition can easily be upgraded to Enterprise Edition allowing you up to 1000 nodes.
Standard Edition RAC configurations are required to use ASM. Because ASM saves time and effort, this is not really a limitation but more of a method for Oracle to help you ensure your success.
How do I monitor my Grid?
Oracle provides both DB Control and Grid Control for monitoring and managing your databases. DB control is a web based tool that allows you to manage one database at a time. Oracle Grid Control allows you to manage entire grids and is an add on package. Grid Control can simplify all of your grid management tasks.
Can I mix and match hardware?
Yes. Oracle expects that vendors are improving hardware constantly and that customers will need to upgrade hardware over time. Rather than force customers to maintain a single vendor or configuration, Oracle RAC will work across various hardware configurations. While one server may be a single CPU and 4GB of RAM, another server in the same cluster can be 4 CPUs (with dual cores) and 16GB of RAM.
The only requirement is that the Operating Systems must match: Windows to Windows, Linux to Linux, 32 bit to 32 bit, etc.
Summary
Grid Computing - Oracle allows you to scale by tying together cheap hardware in a cluster allowing multiple servers to act as one. Oracle provides the software that enables database grids (via RAC) as well as storage grids (via ASM and OCFS). Oracle's Grid allows a business to start as large or small as they need, spending money on hardware and other system resources only when it is actually needed. RAC and Grid is transparent to the application allowing painless and immediate scaling when required.
Oracle database Performance Tuning FAQ
From Oracle FAQ
Jump to: navigation, search
General Oracle database Performance Tuning FAQ. Remember: The best performance comes from the unnecessary work you don't do.
Contents
[hide]
1 Why and when should one tune?
2 Where should the tuning effort be directed?
3 What tools/utilities does Oracle provide to assist with performance tuning?
4 When is cost based optimization triggered?
5 How can one optimize %XYZ% queries?
6 Where can one find I/O statistics per table?
7 My query was fine last week and now it is slow. Why?
8 Does Oracle use my index or not?
9 Why is Oracle not using the damn index?
10 When should one rebuild an index?
11 How does one tune Oracle Wait event XYZ?
12 What is the difference between DBFile Sequential and Scattered Reads?
13 How does one tune the Redo Log Buffer?
if (window.showTocToggle) { var tocShowText = "show"; var tocHideText = "hide"; showTocToggle(); }
[edit] Why and when should one tune?
One of the biggest responsibilities of a DBA is to ensure that theOracle database is tuned properly. The Oracle RDBMS is highly tunable and allows the database to be monitored and adjusted to increase its performance.
One should do performance tuning for the following reasons:
The speed of computing might be wasting valuable human time (users waiting for response);
Enable your system to keep-up with the speed business is conducted; and
Optimize hardware usage to save money (companies are spending millions on hardware).
Although this site is not overly concerned with hardware issues, one needs to remember than you cannot tune a Buick into a Ferrari.
[edit] Where should the tuning effort be directed?
Consider the following areas for tuning. The order in which steps are listed needs to be maintained to prevent tuning side effects. For example, it is no good increasing the buffer cache if you can reduce I/O by rewriting a SQL statement.
Database Design (if it's not too late):
Poor system performance usually results from a poor database design. One should generally normalize to the 3NF. Selectivedenormalization can provide valuable performance improvements. When designing, always keep the "data access path" in mind. Also look at proper data partitioning, data replication, aggregation tables for decision support systems, etc.
Application Tuning:
Experience showed that approximately 80% of all Oracle system performance problems are resolved by coding optimal SQL. Also consider proper scheduling of batch tasks after peak working hours.
Memory Tuning:
Properly size your database buffers (shared_pool, buffer cache, log buffer, etc) by looking at your wait events, buffer hit ratios, system swapping and paging, etc. You may also want to pin large objects into memory to prevent frequent reloads.
Disk I/O Tuning:
Database files needs to be properly sized and placed to provide maximum disk subsystem throughput. Also look for frequent disk sorts, full table scans, missing indexes, row chaining, data fragmentation, etc.
Eliminate Database Contention:
Study database locks, latches and wait events carefully and eliminate where possible.
Tune the Operating System:
Monitor and tune operating system CPU, I/O and memory utilization. For more information, read the related Oracle FAQ dealing with your specific operating system.
What tools/utilities does Oracle provide to assist with performance tuning?
Oracle provide the following tools/ utilities to assist with performance monitoring and tuning:
ADDM (Automated Database Diagnostics Monitor) introduced inOracle 10g
TKProf
Statspack
Oracle Enterprise Manager - Tuning Pack (cost option)
Old UTLBSTAT.SQL and UTLESTAT.SQL - Begin and end stats monitoring
When is cost based optimization triggered?
It's important to have statistics on all tables for the CBO (Cost Based Optimizer) to work correctly. If one table involved in a statement does not have statistics, and optimizer dynamic sampling isn't performed, Oracle has to revert to rule-based optimization for that statement. So you really want for all tables to have statistics right away; it won't help much to just have the larger tables analyzed.
Generally, the CBO can change the execution plan when you:
Change statistics of objects by doing an ANALYZE;
Change some initialization parameters (for example: hash_join_enabled, sort_area_size, db_file_multiblock_read_count).
How can one optimize %XYZ% queries?
It is possible to improve %XYZ% (wildcard search) queries by forcing the optimizer to scan all the entries from the index instead of the table. This can be done by specifying hints.
If the index is physically smaller than the table (which is usually the case) it will take less time to scan the entire index than to scan the entire table.
Where can one find I/O statistics per table?
The STATSPACK and UTLESTAT reports show I/O per tablespace. However, they do not show which tables in the tablespace has the most I/O operations.
The $ORACLE_HOME/rdbms/admin/catio.sql script creates a sample_io procedure and table to gather the required information. After executing the procedure, one can do a simple SELECT * FROM io_per_object; to extract the required information.
For more details, look at the header comments in the catio.sql script.
My query was fine last week and now it is slow. Why?
The likely cause of this is because the execution plan has changed. Generate a current explain plan of the offending query and compare it to a previous one that was taken when the query was performing well. Usually the previous plan is not available.
Some factors that can cause a plan to change are:
Which tables are currently analyzed? Were they previously analyzed? (ie. Was the query using RBO and now CBO?)
Has OPTIMIZER_MODE been changed in INIT
Has the DEGREE of parallelism been defined/changed on any table?
Have the tables been re-analyzed? Were the tables analyzed using estimate or compute? If estimate, what percentage was used?
Have the statistics changed?
Has the SPFILE/ INIT
Has the INIT
Have any other INIT
It can also happen because of a very high high water mark. Typically when a table was big, but now only contains a couple of records. Oracle still needs to scan through all the blocks to see it they contain data.
To reset the values in the v$object_usage view, disable index monitoring and re-enable it: ALTER INDEX indexname NOMONITORING USAGE;ALTER INDEX indexname MONITORING USAGE;
USER_TABLES.NUM_ROWS - If NUM_DISTINCT = NUM_ROWS then using an index would be preferable to doing a FULL TABLE SCAN. As the NUM_DISTINCT decreases, the cost of using an index increase thereby making the index less desirable.
There are many other factors that affect the cost, but sometimes the above can help to show why an index is not being used by the CBO. If from checking the above you still feel that the query should be using an index, try specifying an index hint. Obtain an explain plan of the query either using TKPROF with TIMED_STATISTICS, so that one can see the CPU utilization, or with AUTOTRACE to see the statistics. Compare this to the explain plan when not using an index.
For example, you may decide that index should be rebuilt if more than 20% of its rows are deleted: select del_lf_rows * 100 / decode(lf_rows,0,1,lf_rows) from index_statswhere name = 'index_ name';
db file sequential read: Tune SQL to do less I/O. Make sure all objects are analyzed. Redistribute I/O across disks.
buffer busy waits: Increase DB_CACHE_SIZE (DB_BLOCK_BUFFERS prior to 9i)/ Analyze contention from SYS.V$BH
log buffer space: Increase LOG_BUFFER parameter or move log files to faster disks
log file sync: If this event is in the top 5, you are committing too often (talk to your developers)
log file parallel write: deals with flushing out the redo log buffer to disk. Your disks may be too slow or you have an I/O bottleneck.
The following query shows average wait time for sequential versus scattered reads: prompt "AVERAGE WAIT TIME FOR READ REQUESTS"select a.average_wait "SEQ READ", b.average_wait "SCAT READ"from sys.v_$system_event a, sys.v_$system_event bwhere a.event = 'db file sequential read'and b.event = 'db file scattered read';
Whenever the log buffer is MIN(1/3 full, 1 MB) full; or
Every 3 seconds; or
When a DBWn process writes modified buffers to disk (checkpoint).
Statistic "REDO BUFFER ALLOCATION RETRIES" shows the number of times a user process waited for space in the redo log buffer. This value is cumulative, so monitor it over a period of time while your application is running. If this value is continuously increasing, consider increasing your LOG_BUFFER (but only if you do not see checkpointing and archiving problems).
"REDO LOG SPACE WAIT TIME" shows cumulative time (in 10s of milliseconds) waited by all processes waiting for space in the log buffer. If this value is low, your log buffer size is most likely adequate.
Categories: Frequently Asked Questions Performance tuning
function setAttributeOnload(object, attribute, val) {
if(window.addEventListener) {
window.addEventListener("load",
function(){ object[attribute] = val; }, false);
} else {
window.attachEvent('onload', function(){ object[attribute] = val; });
}
}
Earlier today, I attended a webinar by Hotsos. The webinar, titled Cost Based Optimizer: 1 of 2, was a basic introduction to the Cost Based Optimizer (CBO) in Oracle and the 10053 trace file. The CBO optimizes SQL queries and that optimization can be seen in a 10053 trace. It was not, what I would call, an advanced webinar but was done with plenty of examples and was very accessible for new comers to performance tuning. The webinar was given by Hotsos employee, Mahesh Vallanpati.Because the webinar was filled with plenty of examples, it doesn't lend itself to a blog post. I'm pretty sure they said an archive of the webinar would be available on the Hotsos site. I did take some notes though so here are a few things that do lend themselves to a blog post. If any of this is wrong, it is probably a typo on my part.The CBO is a complex, mathematical based optimizer. The CBO was introduced in Oracle 8, was improved in 9 and replaced the RBO completely in 10. While the CBO is driven by statistics many things affect it:
Database Parameters
Database Statistics
CPU and I/O Stats
DB Schema Configuration
Stored Outlines
The SQL Code Itself
The Oracle Query Cost Model (based on DB version)
Statistical data points collected by the CBO include (you can get this from the columns in stats data dictionary views):
Table Statistics
Number of rows
Number of blocks below the high water mark
Empty blocks
Average free space
Row length
Column Statistics
Distinct Values (very important)
Density
Low Value
High Value
Histogram values if they exist
Clustering
Depth of index
Leaf blocks
Distinct Values (very important)
Other averages
System Statistics
Average Block Read sizes (multiblock and singleblock)
CPU Speed
I/O Speed
Table Statistics
Table Statistics
Note: The frequency and % of stats collected should not be a drag on the system. You need to evaluate just what % makes the most sense as well as how frequently you need to collect them.Less logical I/O is not always better. In the session, Mahesh went over an example where, due to the dbfile_multi_block_read_count setting of 16, a full table scan with a LIO of 722 was actually chosen by the optimizer in lieu of an index scan with a LIO of 166. That means that in a full table scan, Oracle was reading 16 blocks at a time. By reading that much data at once, Oracle was actually getting more data faster than it would have by reading the index sequentially. Had the parameter been set at 8k, the index might have had better performance. Very nice detail.One good tip that Mahesh provided is: The first step of your statistics gathering should be to backup your old stats. That way you can recover if something goes wrong.Finally, Mahesh talked about a hint that I did not know existed, the CARDINALITY hint. Using the cardinality hint in a dev or test environment, you can test scalability when the amount of data in your tables grows. The hint tells Oracle that you have a different number of rows than you really have. You can run explain plans with various values and see how that impacts your plan.oracle optimizer cbo performance tuningmulti_block_read_count 10053
Views
No comments:
Post a Comment