|
|
|
|
|
Stats |
Total Topics
Total Topic Views
Total Comments
Unique Visitors
Total Visitors |
: 45
: 120076
: 36
|
|
|
|
|
|
About this blog…About this blog…
About MeAbout Me
Favourite LinksFavourite Links
Neden hazır blog'ları kullanmadım ki?Neden hazır blog'ları kullanmadım ki?
CSS is more powerful than you imagineCSS is more powerful than you imagine
Turkcell Staj Günlüğü - 1: Introduction to OracleTurkcell Staj Günlüğü - 1: Introduction to Oracle
Turkcell Staj Günlüğü - 2: Data Blocks, Extends and SegmentsTurkcell Staj Günlüğü - 2: Data Blocks, Extends and Segments
Kahin'e yolculuk nasıl başlamalı?Kahin'e yolculuk nasıl başlamalı?
Turkcell Staj Günlüğü - 3: Tablespaces, Datafiles and Control FilesTurkcell Staj Günlüğü - 3: Tablespaces, Datafiles and Control Files
Turkcell Staj Günlüğü - 4: Transaction ManagementTurkcell Staj Günlüğü - 4: Transaction Management
Image formats - Which to use whenImage formats - Which to use when
Turkcell Staj Günlüğü - 5: Startup, ShutdownTurkcell Staj Günlüğü - 5: Startup, Shutdown
Turkcell Staj Günlüğü - 6: Oracle ArchitectureTurkcell Staj Günlüğü - 6: Oracle Architecture
ASP - Locales and CodepagesASP - Locales and Codepages
Oracle performance analysis - Tracing and performance evaluationOracle performance analysis - Tracing and performance evaluation
Oracle performance analysis - Autotrace workshopOracle performance analysis - Autotrace workshop
Oracle performance analysis - Runstats workshopOracle performance analysis - Runstats workshop
Oracle performance analysis - Tkprof workshopOracle performance analysis - Tkprof workshop
Some favourite quotesSome favourite quotes
Derin anlamlı sözler - Bunlar da Türkçe olanlar :)Derin anlamlı sözler - Bunlar da Türkçe olanlar :)
Turkcell Staj Günlüğü - 7: Concurrency and ConsistencyTurkcell Staj Günlüğü - 7: Concurrency and Consistency
"Kurtuluş"un hikayesi"Kurtuluş"un hikayesi
Turkcell Staj Günlüğü - 8: Statement Processing and CBOTurkcell Staj Günlüğü - 8: Statement Processing and CBO
When a transaction begins?When a transaction begins?
Implicit vs. Explicit cursors - Performance analysisImplicit vs. Explicit cursors - Performance analysis
Turkcell Staj Günlüğü - 9: "SQL, PL/SQL and Java" ve "Redo Internals"Turkcell Staj Günlüğü - 9: "SQL, PL/SQL and Java" ve "Redo Internals"
Affect of gathering table stats to decision of CBOAffect of gathering table stats to decision of CBO
Bind is bad :) - An interesting case of bind variables failsBind is bad :) - An interesting case of bind variables fails
When the explanation doesn't sound quite right...When the explanation doesn't sound quite right...
Turkcell Staj Günlüğü - 10: Import, Export ve SQL LoaderTurkcell Staj Günlüğü - 10: Import, Export ve SQL Loader
Turkcell Staj Günlüğü - 11: Autonomous Transactions ve Dynamic SQLTurkcell Staj Günlüğü - 11: Autonomous Transactions ve Dynamic SQL
Difference between db block gets and consistent getsDifference between db block gets and consistent gets
Object-Oriented Features of Oracle - Part 1: Native Datatypes vs. Object DatatypesObject-Oriented Features of Oracle - Part 1: Native Datatypes vs. Object Datatypes
Object-Oriented Features of Oracle - Part 2: Object Types and Collection typesObject-Oriented Features of Oracle - Part 2: Object Types and Collection types
Object-Oriented Features of Oracle - Part 3: Object Tables, Object Views and REFsObject-Oriented Features of Oracle - Part 3: Object Tables, Object Views and REFs
Examining show_spaceExamining show_space
Turkcell Staj Günlüğü - 12: PartitioningTurkcell Staj Günlüğü - 12: Partitioning
Hello World!Hello World!
Decode Demo #1Decode Demo #1
Decode Demo #2Decode Demo #2
Decode Demo #3Decode Demo #3
Decode Demo #4Decode Demo #4
Decode Function in Oracle SQLDecode Function in Oracle SQL
Windows Source CodesWindows Source Codes
Türkçe Karakterli Domain'lerin İç YüzüTürkçe Karakterli Domain'lerin İç Yüzü
Whence C? Why C? Whither C?Whence C? Why C? Whither C?
Differences between C and C++Differences between C and C++
Is C a Vitamin? Yes, of course...Is C a Vitamin? Yes, of course...
Obfuscated CObfuscated C
Finding and Removing Loop on a Singly-Linked ListFinding and Removing Loop on a Singly-Linked List
Matematik Asla Yalan Söylemez!Matematik Asla Yalan Söylemez!
|
|
|
|
|
|
Affect of gathering table stats to decision of CBO
|
Category: SQL-Oracle-PL/SQL
Date: 31.07.2007 18:49:30
|
Optimizer statistics are a collection of data that describe more details about the database and the objects in the database. These statistics are used by the query optimizer to choose the best execution plan for each SQL statement.
The optimizer statistics are stored in the data dictionary. They can be viewed using data dictionary views.
Optimizer statistics include the following:
Table statistics:
• Number of rows
• Number of blocks
• Average row length
Column statistics:
• Number of distinct values (NDV) in column
• Number of nulls in column
• Data distribution (histogram)
Index statistics:
• Number of leaf blocks
• Levels
• Clustering factor
System statistics:
• I/O performance and utilization
• CPU performance and utilization
Because the objects in a database can be constantly changing, statistics must be regularly updated so that they accurately describe these database objects. Statistics are maintained automatically by Oracle or you can maintain the optimizer statistics manually using the DBMS_STATS package.
You can use the DBMS_STATS package or the ANALYZE statement to gather statistics about the physical storage characteristics of a table, index, or cluster. These statistics are stored in the data dictionary and can be used by the optimizer to choose the most efficient execution plan for SQL statements accessing analyzed objects.
Oracle recommends using the more versatile DBMS_STATS package for gathering optimizer statistics, but you must use the ANALYZE statement to collect statistics unrelated to the optimizer, such as empty blocks, average space, and so forth.
The DBMS_STATS package allows both the gathering of statistics, including utilizing parallel execution, and the external manipulation of statistics. Statistics can be stored in tables outside of the data dictionary, where they can be manipulated without affecting the optimizer. Statistics can be copied between databases or backup copies can be made.
AUTOMATIC STATISTICS GATHERING
The recommended approach to gathering statistics is to allow Oracle to automatically gather the statistics. Oracle gathers statistics on all database objects automatically and maintains those statistics in a regularly-scheduled maintenance job. Automated statistics collection eliminates many of the manual tasks associated with managing the query optimizer, and significantly reduces the chances of getting poor execution plans because of missing or stale statistics.
WHEN TO USE MANUAL STATISTICS
Automatic statistics gathering should be sufficient for most database objects which are being modified at a moderate speed. However, there are cases where automatic statistics gathering may not be adequate. Because the automatic statistics gathering runs during an overnight batch window, the statistics on tables which are significantly modified during the day may become stale. There are typically two types of such objects:
• Volatile tables that are being deleted or truncated and rebuilt during the course of the day.
• Objects which are the target of large bulk loads which add 10% or more to the object's total size.
MANUAL STATISTICS GATHERING
If you choose not to use automatic statistics gathering, then you need to manually collect statistics in all schemas, including system schemas. If the data in your database changes regularly, you also need to gather statistics regularly to ensure that the statistics accurately represent characteristics of your database objects.
The DBMS_STATS package can gather statistics on table and indexes, and well as individual columns and partitions of tables. It does not gather cluster statistics; however, you can use DBMS_STATS to gather statistics on the individual tables instead of the whole cluster.
You can use the DBMS_STATS package or the ANALYZE statement to gather statistics about the physical storage characteristics of a table, index, or cluster. These statistics are stored in the data dictionary and can be used by the optimizer to choose the most efficient execution plan for SQL statements accessing analyzed objects.
When you generate statistics for a table, column, or index, if the data dictionary already contains statistics for the object, then Oracle updates the existing statistics. The older statistics are saved and can be restored later if necessary.
TEST ENVIRONMENT
Database: Oracle 10g Enterprise Edition 10.2.0.1.0
Server: Ubuntu 7.04 Desktop, on VMware 5.5.3, 512 MB of RAM, Pentium M 1.7 Ghz
Client: Oracle 10g XE Client, SQL*Plus on Windows Console
TEST CODE EXECUTION AND RESULTS
SQL> conn test/test@bilalEE
Connected.
SQL> set autotrace traceonly
SQL> set timing on
SQL> alter session set statistics_level=BASIC;
Session altered.
Elapsed: 00:00:00.08
SQL> drop index t1_inx_owner;
drop index t1_inx_owner
*
ERROR at line 1:
ORA-01418: specified index does not exist
Elapsed: 00:00:00.03
SQL> drop table t1 purge;
drop table t1 purge
*
ERROR at line 1:
ORA-00942: table or view does not exist
Elapsed: 00:00:01.91
SQL> create index t1_inx_owner on t1(owner);
Index created.
Elapsed: 00:00:05.79
SQL> select table_name,num_rows,blocks,empty_blocks,last_analyzed from all_tables
where table_name='T1';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS LAST_ANAL
------------------------------ ---------- ---------- ------------ ---------
T1
Elapsed: 00:00:01.79
SQL> select index_name, table_name, blevel, leaf_blocks, clustering_factor, num_rows,
last_analyzed from all_indexes where index_name='T1_INX_OWNER';
INDEX_NAME TABLE_NAME BLEVEL LEAF_BLOCKS
------------------------------ ------------------------------ ---------- -----------
CLUSTERING_FACTOR NUM_ROWS LAST_ANAL
----------------- ---------- ---------
T1_INX_OWNER T1 2 687
6214 292428 23-JUL-07
Elapsed: 00:00:01.10
SQL> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.03
SQL> select name from t1 where owner='SYSMAN';
128423 rows selected.
Elapsed: 00:00:03.63
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 181K| 6028K| 1391 (2)| 00:00:17 |
|* 1 | TABLE ACCESS FULL| T1 | 181K| 6028K| 1391 (2)| 00:00:17 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SYSMAN')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
244 recursive calls
0 db block gets
8835 consistent gets
6197 physical reads
0 redo size
871122 bytes sent via SQL*Net to client
28621 bytes received via SQL*Net from client
2570 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
128423 rows processed
SQL> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.05
SQL> select name from t1 where owner='SYSMAN';
128423 rows selected.
Elapsed: 00:00:02.81
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 181K| 6028K| 1391 (2)| 00:00:17 |
|* 1 | TABLE ACCESS FULL| T1 | 181K| 6028K| 1391 (2)| 00:00:17 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SYSMAN')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8729 consistent gets
6174 physical reads
0 redo size
871122 bytes sent via SQL*Net to client
28621 bytes received via SQL*Net from client
2570 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
128423 rows processed
SQL> analyze table t1 compute statistics;
Table analyzed.
Elapsed: 00:00:07.27
SQL> select table_name,num_rows,blocks,empty_blocks,last_analyzed,sample_size
from all_tables where table_name='T1';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS LAST_ANAL SAMPLE_SIZE
------------------------------ ---------- ---------- ------------ --------- -----------
T1 292432 6281 119 27-JUL-07 292432
Elapsed: 00:00:00.27
SQL> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.46
SQL> select name from t1 where owner='SYSMAN';
128423 rows selected.
Elapsed: 00:00:01.22
Execution Plan
----------------------------------------------------------
Plan hash value: 3177033591
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15391 | 270K| 368 (1)| 00:0
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 15391 | 270K| 368 (1)| 00:0
|* 2 | INDEX RANGE SCAN | T1_INX_OWNER | 15391 | | 39 (0)| 00:0
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SYSMAN')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
6878 consistent gets
1762 physical reads
0 redo size
871122 bytes sent via SQL*Net to client
28621 bytes received via SQL*Net from client
2570 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
128423 rows processed
SQL> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.02
SQL> select name from t1 where owner='SYSMAN';
128423 rows selected.
Elapsed: 00:00:01.05
Execution Plan
----------------------------------------------------------
Plan hash value: 3177033591
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15391 | 270K| 368 (1)| 00:0
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 15391 | 270K| 368 (1)| 00:0
|* 2 | INDEX RANGE SCAN | T1_INX_OWNER | 15391 | | 39 (0)| 00:0
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SYSMAN')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6878 consistent gets
1762 physical reads
0 redo size
871122 bytes sent via SQL*Net to client
28621 bytes received via SQL*Net from client
2570 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
128423 rows processed
Download Code
You can download the presentation and presentation notes from here.
COMPARISON
CONCLUSION
Here, we see in the first case, Oracle didn't have any statistics and idea about
the data, so, to use the CBO, it uses dynamic sampling (randomly reads some data
from table and makes a decision from that statistics, before executing the query)
and decides to full table scan. The strange thing is, although Oracle has statistics
for the index on the table, and they are true, real an up-to-date statistics, it
did not use them. When we gather table statistics, CBO uses it and then decided to use
index.
• For CBO, having the most up-to-date statistics is important to have a better decision
• Don’t forget the extra cost of gathering statistics (especially for large tables)
• Oracle automatically gathers *some* statistics for its internal use and for CBO, that you can’t turn off (10g). We can turn off some statistics, also we can gather extra statistics (histograms)
|
|
Links & References |
|
|
|
|
|
|
|
|
Set as Homepage
Add to Favourites
Recommend
Contact
