Shulou(Shulou.com)05/31 Report--

The main content of this article is "Analysis of SQL full table scanning process in oracle". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Now let the editor to take you to learn "SQL full table scanning process analysis in oracle"!

The following SQL takes a full table scan, resulting in a decline in efficiency, while the selectivity of predicate fields in SQL is very low, and the performance is provided through the histogram and from btree to bitmap, so this process is analyzed.

Select Count (*) From pmc.DesignXXXXX t Where 1 # 1 and OrganId='C00000220'And CategoryCode=2 and IsEnable=1 and isdelete=0 or (PublicStatus=1 and isdelete=0); COUNT (*)-1845

Poor execution plan: by scanning the table, logical reading requires 844525:

=

Execution Plan---Plan hash value: 527126818 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 1 | 19 | 229K (1) | 00:45:58 | | 1 | SORT AGGREGATE | 1 | 19 | * 2 | TABLE ACCESS FULL | DESIGNXXXXX | 4744K | 85m | 229K (1) | 00:45:58 |- -Predicate Information (identified by operation id):- -2-filter ("ISDELETE" = 0 AND ("PUBLICSTATUS" = 1 OR "ORGANID" = 'C00000220' AND "CATEGORYCODE" = 2 AND "ISENABLE" = 1)) Statistics--- 1 recursive calls 0 db block gets 844525 consistent gets 842418 physical reads 0 redo size 527 bytes sent via SQL*Net to client 527 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed

How the SQL chose the execution plan (tracked through 10053):

Oracle made several cost comparisons in the following ways:

1. The cost required for evaluation to pass a full table scan is 229760.92.

Access Path: TableScan Cost: 229760.92 Resp: 229760.92 Degree: 0 Cost_io: 229075.00 Cost_cpu: 25302994949 Resp_io: 229075.00 Resp_cpu: 25302994949

two。 The cost for evaluating the way through the bitmap index is 741028, which is the consumption that has been used to join both sides of the or in bitmap mode.

* trying bitmap/domain indexes *

....

Bitmap nodes:

Used IND_DESIGNXXXXX_ISENABLE_ORG

Cost = 35.099036, sel = 0.000494

Used IND_DESIGNXXXXX_CATEGORYCODE

Cost = 1281.621955, sel = 0.034894

Bitmap nodes:

Used IND_PUBLICSTATUS

Cost = 17275.447942, sel = 0.471383

Used bitmap node

Bitmap nodes:

Used bitmap node

Access path: Bitmap index-accepted

Cost: 741028.481879 Cost_io: 740534.527080 Cost_cpu: 18221443693.247154 Sel: 0.471392

Because there is or in this statement, that is, the access path consumption around or is calculated respectively, and then combined.

The right side of 3.or scans through the IND_PUBLICSTATUS index range. Cost is 429957.

Access Path: index (AllEqRange)

Index: IND_PUBLICSTATUS

Resc_io: 429587.00 resc_cpu: 13681713060

Ix_sel: 0.477347 ix_sel_with_filters: 0.477347

Cost: 429957.89 Resp: 429957.89 Degree: 1

The left side of 4.or calculates the consumption using the following indexes respectively

1) the consumption of DESIGNXXXXX_TIME_ORGANID is 88778.

Access Path: index (SkipScan)

Index: DESIGNXXXXX_TIME_ORGANID

Resc_io: 88761.00 resc_cpu: 643271006

Ix_sel: 0.000509 ix_sel_with_filters: 0.000509

Cost: 88778.44 Resp: 88778.44 Degree: 1

2) the consumption of IND_DESIGNXXXXX_CATEGORYCODE is 32961.

Access Path: index (AllEqRange)

Index: IND_DESIGNXXXXX_CATEGORYCODE

Resc_io: 32934.00 resc_cpu: 1020893102

Ix_sel: 0.036885 ix_sel_with_filters: 0.036885

Cost: 32961.67 Resp: 32961.67 Degree: 1

ColGroup Usage:: PredCnt: 2 Matches Full: # 2 Partial: Sel: 0.0005

ColGroup Usage:: PredCnt: 2 Matches Full: # 2 Partial: Sel: 0.0005

3) the consumption of IND_DESIGNXXXXX_CATEGORYCODE is 32961.

Access Path: index (AllEqRange)

Index: IND_DESIGNXXXXX_ISENABLE_ORG

Resc_io: 6499.00 resc_cpu: 57845156

Ix_sel: 0.000494 ix_sel_with_filters: 0.000494

Cost: 6500.57 Resp: 6500.57 Degree: 1

4) the consumption from IND_DESIGNXXXXX_ISENABLE_ORG and IND_DESIGNXXXXX_CATEGORYCODE to bitmap is 1406.

Bitmap nodes:

Used IND_DESIGNXXXXX_ISENABLE_ORG

Cost = 35.099036, sel = 0.000494

Used IND_DESIGNXXXXX_CATEGORYCODE

Cost = 1281.621955, sel = 0.034894

Access path: Bitmap index-accepted

Cost: 1406.374238 Cost_io: 1399.626467 Cost_cpu: 248917754.369408 Sel: 0.000017

It should be noted here that the left and right sides of the or are calculated respectively, and the final merger requires statistical calculation of the consumption on both sides, so all the above consumption estimates are:

Full table scan (Cost: 229760.92) < IND_PUBLICSTATUS index (Cost: 429957.89) + any access path on the left Select Count (*) From pmc.DesignXXXXX t where PublicStatus=1 and isdelete=0

COUNT (*)

-

1845

But until PUBLICSTATUS's data is distributed, CBO estimates that it only takes 6 to access cost through the IND_PUBLICSTATUS index. This is why the consumption of access tables can be more accurately assessed after collecting histograms.

Access Path: index (AllEqRange)

Index: IND_PUBLICSTATUS

Resc_io: 6.00 resc_cpu: 457729

Ix_sel: 0.000112 ix_sel_with_filters: 0.000112

Cost: 6.01 Resp: 6.01 Degree: 0

Then, in the case of 0 or 1, the mode of conversion to the bitmap index is selected.

In fact, if you do not choose btree to convert bitmap, there is no problem with the efficiency of directly using btree index to return to the table, but you need to split the or in sql into union statements.

Execution Plan---Plan hash value: 3766559296 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | | 1 | 13 | 105 (2) | 00:00:02 | | 1 | SORT AGGREGATE | | 1 | 13 | | 2 | VIEW | | 2 | 26 | 105 (2) | 00:00:02 | | 3 | SORT UNIQUE | | 2 | 22 | 105 (2) | 00:00:02 | | 4 | UNION-ALL | | 5 | SORT AGGREGATE | | 1 | 17 | 9 (12) | 00:00:01 | | * 6 | TABLE ACCESS BY INDEX ROWID | DESIGXXXXXXX | 1 | 17 | 8 (0) | 00:00:01 | | * 7 | INDEX RANGE SCAN | IND_DESIGNXXXXXX_ISENABLE_ORG | 6 | | 3 (0) | 00:00:01 | 8 | SORT AGGREGATE | | | 1 | 5 | 96 (2) | 00:00:02 | | * 9 | TABLE ACCESS BY INDEX ROWID | DESIGNXXXXXXX | 1874 | 9370 | 95 (0) | 00:00:02 | | * 10 | INDEX RANGE SCAN | IND_PUBLICSTATUS | 2046 | | 6 (0) | 00:00:01 |-| -Predicate Information (identified by operation id):- -- 6-filter ("CATEGORYCODE" = 2 AND "ISDELETE" = 0) 7-access ("ISENABLE" = 1 AND "ORGANID" = 'C00000281') 9-filter ("ISDELETE" = 0) 10-access ("PUBLICSTATUS" = 1) Statistics- -- 1 recursive calls 0 db block gets 2114 consistent gets 0 physical reads 0 redo size bytes sent via SQL*Net to client 527 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1 rows processed

It is important to pay attention to the performance problems of opening histogram and converting btree to Bitma, which may even cause abnormal performance problems.

At this point, I believe you have a deeper understanding of "SQL full table scan process analysis in oracle". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.