Set as Homepage   Add to Favourites   Recommend   Contact



Menu

∙ Home
∙ Index (All Topics)
∙ About Me
∙ About This Blog
∙ Favourite Links
∙ RSS Feed

Categories

∙ ASP & PHP (1)
∙ HTML, XML and CSS (2)
∙ C / C++ (5)
∙ Java, JSP and Servlet (0)
∙ SQL-Oracle-PL/SQL (28)
∙ Operating Systems (1)
∙ OFF-Topic (8)

Popular Topics (Top 10)

∙ Decode Function in Oracle SQL (67466)

∙ Derin anlamlı sözler - Bunlar da Türkçe olanlar :) (64465)

∙ Oracle performance analysis - Tracing and performance evaluation (50786)

∙ When a transaction begins? (41304)

∙ Differences between C and C++ (39907)

∙ Turkcell Staj Günlüğü - 1: Introduction to Oracle (37843)

∙ Implicit vs. Explicit cursors - Performance analysis (20639)

∙ Turkcell Staj Günlüğü - 4: Transaction Management (19286)

∙ Turkcell Staj Günlüğü - 9: "SQL, PL/SQL and Java" ve "Redo Internals" (18892)

∙ Finding and Removing Loop on a Singly-Linked List (17430)


Most Recent (Last 10)

∙ Matematik Asla Yalan Söylemez!

∙ Finding and Removing Loop on a Singly-Linked List

∙ Obfuscated C

∙ Is C a Vitamin? Yes, of course...

∙ Differences between C and C++

∙ Whence C? Why C? Whither C?

∙ Türkçe Karakterli Domain'lerin İç Yüzü

∙ Windows Source Codes

∙ Decode Function in Oracle SQL

∙ Hello World!


Recent Comments (Last 10)

∙ "tebrikler" By yasin on Turkcell Staj Günlüğü - 5: Startup, Shutdown

∙ "Gercekten Güzel Bir Çalışma" By Hüseyin Karabakla on Neden hazır blog'ları kullanmadım ki?

∙ "Konu paralelinde güzel bir özet ek okuma - " By TongucY on Oracle performance analysis - Tracing and performance evaluation

∙ "harika" By burak ozcan on Derin anlamlı sözler - Bunlar da Türkçe olanlar :)

∙ "Tebrikler" By Tarık Bayzın on Turkcell Staj Günlüğü - 1: Introduction to Oracle

∙ "Gayet Başarılı.." By Fahri ATES on Turkcell Staj Günlüğü - 1: Introduction to Oracle

∙ "Helal olsun" By ender onder on Turkcell Staj Günlüğü - 5: Startup, Shutdown

∙ "tebrikler.." By ender ondeer on Turkcell Staj Günlüğü - 4: Transaction Management

∙ "Adulation?" By fizikci on Matematik Asla Yalan Söylemez!

∙ "Rehberlik için çook teşekkürler" By Pınar Tanrıverdi on Kahin'e yolculuk nasıl başlamalı?


Archive (Last 12 Months)

∙ Feb, 2008 (4)
∙ Jan, 2008 (2)
∙ Dec, 2007 (1)
∙ Sep, 2007 (4)
∙ Aug, 2007 (9)
∙ Jul, 2007 (22)
∙ Jun, 2007 (3)
∙ Index (All Records)

Other Related Blogs

∙ Tom Kyte’s Blog
∙ Steven Feuerstein’s Blog
∙ Jonathan Lewis’s Blog
∙ H.Tonguç Yılmaz Oracle Blog
∙ Mennan Tekbir's Blog
∙ Hakkı Oktay’s Blog
∙ Osman Çam’s Blog

Stats

Total Topics
Total Topic Views
Total Comments
Unique Visitors
Total Visitors
: 45
: 790369
: 44

About this blog…
About this blog…
About Me
About Me
Favourite Links
Favourite Links
Neden hazır blog'ları kullanmadım ki?
Neden hazır blog'ları kullanmadım ki?
CSS is more powerful than you imagine
CSS is more powerful than you imagine
Turkcell Staj Günlüğü - 1: Introduction to Oracle
Turkcell Staj Günlüğü - 1: Introduction to Oracle
Turkcell Staj Günlüğü - 2: Data Blocks, Extends and Segments
Turkcell 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 Files
Turkcell Staj Günlüğü - 3: Tablespaces, Datafiles and Control Files
Turkcell Staj Günlüğü - 4: Transaction Management
Turkcell Staj Günlüğü - 4: Transaction Management
Image formats - Which to use when
Image formats - Which to use when
Turkcell Staj Günlüğü - 5: Startup, Shutdown
Turkcell Staj Günlüğü - 5: Startup, Shutdown
Turkcell Staj Günlüğü - 6: Oracle Architecture
Turkcell Staj Günlüğü - 6: Oracle Architecture
ASP - Locales and Codepages
ASP - Locales and Codepages
Oracle performance analysis - Tracing and performance evaluation
Oracle performance analysis - Tracing and performance evaluation
Oracle performance analysis - Autotrace workshop
Oracle performance analysis - Autotrace workshop
Oracle performance analysis - Runstats workshop
Oracle performance analysis - Runstats workshop
Oracle performance analysis - Tkprof workshop
Oracle performance analysis - Tkprof workshop
Some favourite quotes
Some 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 Consistency
Turkcell Staj Günlüğü - 7: Concurrency and Consistency
"Kurtuluş"un hikayesi
"Kurtuluş"un hikayesi
Turkcell Staj Günlüğü - 8: Statement Processing and CBO
Turkcell Staj Günlüğü - 8: Statement Processing and CBO
When a transaction begins?
When a transaction begins?
Implicit vs. Explicit cursors - Performance analysis
Implicit 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 CBO
Affect of gathering table stats to decision of CBO
Bind is bad :) - An interesting case of bind variables fails
Bind 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 Loader
Turkcell Staj Günlüğü - 10: Import, Export ve SQL Loader
Turkcell Staj Günlüğü - 11: Autonomous Transactions ve Dynamic SQL
Turkcell Staj Günlüğü - 11: Autonomous Transactions ve Dynamic SQL
Difference between db block gets and consistent gets
Difference between db block gets and consistent gets
Object-Oriented Features of Oracle - Part 1: Native Datatypes vs. Object Datatypes
Object-Oriented Features of Oracle - Part 1: Native Datatypes vs. Object Datatypes
Object-Oriented Features of Oracle - Part 2: Object Types and Collection types
Object-Oriented Features of Oracle - Part 2: Object Types and Collection types
Object-Oriented Features of Oracle - Part 3: Object Tables, Object Views and REFs
Object-Oriented Features of Oracle - Part 3: Object Tables, Object Views and REFs
Examining show_space
Examining show_space
Turkcell Staj Günlüğü - 12: Partitioning
Turkcell Staj Günlüğü - 12: Partitioning
Hello World!
Hello World!
Decode Demo #1
Decode Demo #1
Decode Demo #2
Decode Demo #2
Decode Demo #3
Decode Demo #3
Decode Demo #4
Decode Demo #4
Decode Function in Oracle SQL
Decode Function in Oracle SQL
Windows Source Codes
Windows 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 C
Obfuscated C
Finding and Removing Loop on a Singly-Linked List
Finding and Removing Loop on a Singly-Linked List
Matematik Asla Yalan Söylemez!
Matematik Asla Yalan Söylemez!
eXTReMe Tracker
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

Comments

No comments posted yet.



© Copyright. All rights reserved. Designed by Bilal Hatipoğlu. RSS Feed  Valid W3C XHTML 1.0 Document  Valid W3C CSS Document