Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

How to analyze Oracle table

2025-01-25 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Servers >

Share

Shulou(Shulou.com)06/01 Report--

This article will explain in detail how to analyze the Oracle table. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

I. the concept of table analysis

1. Simply put, it collects information about tables and indexes, based on which CBO determines the best execution path for SQL. Through the analysis of the table, some statistical information can be generated, which can be optimized by the optimization program of oracle.

1) the purpose of collecting statistics is to make CBO-based execution plans more accurate.

2) the usefulness of the analyzed result data [all_tables] depends on how the Oracle initialization parameter optimal_mode is set.

3) analyze the statistical information of the update table, which may lead to a change in the implementation plan

two。 Use analyze or dbms_stats package to analyze statistical tables, which can determine the usage of the table, such as row migration, the amount of data in the table, etc., such as:

1) select * from all_tables where table_name='TEMP_TABLE'; (the table name must be capitalized)

If no statistical analysis has been done on the table, most of the field values of the query are empty

2) analyze table tableName compute statistics

The execution time is related to the amount of data in the table

3) select * from all_tables where table_name=' TEMP_TABLE'

For some fields that were previously empty, the query now has a value.

3. Oracle's online documentation describes the use of analyze:

Use the ANALYZE statement to collect non-optimizer statistics, for example, to:

-- use the analyze feature to collect statistics that are not optimized, such as:

Collect or delete statistics about an index or index partition, table or table partition

-clustering or deleting indexes or index partitions, tables or table partitions

Index-organized table, cluster, or scalar object attribute.

Statistics such as index tables, strings, or target attributes of cursors

Validate the structure of an index or index partition, table or table partition, index-organized table

-- increase the effectiveness of the following structures: index or index partition, table or table partition, index table

Cluster, or object reference (REF).

-- string, target reference

Identify migrated and chained rows of a table or cluster.

Identify columns that have been moved or locked in a table or string.

4. The function of the dbms_ stats package is mainly to replace the function of collecting statistics of analyze, and has made a considerable enhancement in this aspect.

5. ANALYZE operations with the ESTIMATE option can produce incorrect results for some tables, especially those with smaller samples. This is not a BUG, but a characteristic of each statistical sampling method. If the selected sample does not represent the entire data set, you cannot expect to produce the correct statistics.

6. Statistical analysis, the distribution information of column values can be saved in Oracle 10G

7. When considering table analysis, you need to consider the following issues:

1) for the ANALYZE of an indexed table, the relevant indexes are analyzed.

2) if you ANALYZE a table... ESTIMATE analysis, then it is wise to do ANALYZE COMPUTE analysis on its relevant indexes. This ensures that the statistics of the indexed fields are accurate.

3) Analytical indexes do not need to go to temporary tablespaces

4) if you analyze an index without analyzing its base table, CBO will not be selected on this single basis. If you need to use ESTIMATE- estimation (for example, due to time constraints), it is recommended that you do ANALYZE on several different sample sizes. ESTIMATE to determine the ideal sample size for each object. The overall goal is to find a sample size that produces accurate statistics in the shortest possible time. A better starting point is 10%-15%.

5) conduct more than 50% of ANALYZE. ESTIMATE will lead to / become ANALYZE. COMPUTE

About how to carry on the Oracle table analysis to share here, hope that the above content can have some help to everyone, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 265

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Servers

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report