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

The SQL statement with the most sorted queries:

WITH sql_workarea AS

(SELECT sql_id | |'_'| | child_number sql_id_child

Operation_type operation

Last_execution last_exec

Round (active_time / 1000000, 2) seconds

Optimal_executions | |'/'| | multipasses_executions olm

'' | | substr (sql_text, 1,155) sql_text

Rank () over (ORDER BY active_time DESC) ranking

FROM v$sql_workarea

JOIN v$sql

USING (sql_id, child_number))

SELECT sql_id_child "SQL ID-CHILD"

Seconds

Operation

Last_exec

Olm "Oswap 1mop M"

Sql_text

FROM sql_workarea

WHERE ranking select * from customers order by cust_last_name,cust_first_name,cust_year_of_birth

55500 rows selected.

Execution Plan

Plan hash value: 2792773903

-

| | Id | Operation | Name | Rows | Bytes | TempSpc | Cost (% CPU) | Time |

-

| | 0 | SELECT STATEMENT | | 55500 | 9810K | | 2609 (1) | 00:00:02 |

| | 1 | SORT ORDER BY | | 55500 | 9810K | 12m | 2609 (1) | 00:00:02 |

| | 2 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 9810K | | 405K (1) | 00:00:01 |

-

Statistics

12 recursive calls

15 db block gets

1456 consistent gets

2903 physical reads

0 redo size

6366362 bytes sent via SQL*Net to client

41213 bytes received via SQL*Net from client

3701 SQL*Net roundtrips to/from client

0 sorts (memory)

1 sorts (disk)

55500 rows processed

After indexing:

SQL > create index cust_namedob_i on customers (cust_last_name,cust_first_name,cust_year_of_birth)

Index created.

SQL > select / * + index (customers,cust_namedob_i) * / * from customers order by cust_last_name,cust_first_name,cust_year_of_birth

55500 rows selected.

Execution Plan

Plan hash value: 1819843466

-

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

-

| | 0 | SELECT STATEMENT | | 55500 | 9810K | 20550 (1) | 00:00:15 |

| | 1 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 55500 | 9810K | 20550 (1) | 00:00:15 |

| | 2 | INDEX FULL SCAN | CUST_NAMEDOB_I | 55500 | | 225 (0) | 00:00:01 |

-

Statistics

1 recursive calls

0 db block gets

26557 consistent gets

1708 physical reads

0 redo size

6366312 bytes sent via SQL*Net to client

41213 bytes received via SQL*Net from client

3701 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

55500 rows processed

Although sorting may no longer be required by using an index, the cost of reading both the index and the table block, and the inefficient scanning method of reading the table block sequentially, is much less expensive than reading the table block using a full table scan, which means that using an index to avoid sorting actually leads to worse performance. However, using an index is faster when retrieving the first row of records, because it returns as soon as the required record is retrieved. In contrast, the sorting method requires that all records must be retrieved and sorted before any record is returned. Therefore, when the optimizer targets FIRST_ROWS_N, the optimizer tends to use indexes, while when the target is ALL_ROWS, full table scans are used.

Another scenario where index-based fetching is better than scanning and then fetching is when memory is extremely limited. If the memory available for sorting is limited, the IO required to read and write temporary periods will exceed the additional IO overhead contained in indexes and table scans. Of course, if you can allocate more memory, it will perform much better, but if this is not possible, you may want to use INDEX hints to avoid sorting.

Aggregation operation

Aggregate hype, such as SUM and AVG, must process each row of input data, so they are usually associated with full table scans.

SQL > select sum (quantity_sold) from sales

Execution Plan

Plan hash value: 3519235612

-

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Pstart | Pstop |

-

| | 0 | SELECT STATEMENT | | 1 | 3 | 525 (2) | 00:00:01 |

| | 1 | SORT AGGREGATE | | 1 | 3 |

| | 2 | PARTITION RANGE ALL | | 918K | 2691K | 525 (2) | 00:00:01 | 1 | 28 |

| | 3 | TABLE ACCESS FULL | SALES | 918K | 2691K | 525 (2) | 00:00:01 | 1 | 28 | |

-

Statistics

2429 recursive calls

2 db block gets

5371 consistent gets

1714 physical reads

0 redo size

538 bytes sent via SQL*Net to client

524 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

183 sorts (memory)

0 sorts (disk)

1 rows processed

SQL > select / * + index (sales,index_sl) * / sum (quantity_sold) from sales

SUM (QUANTITY_SOLD)

-

918843

Execution Plan

Plan hash value: 3788238680

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | | 1 | 3 | 2316 (1) | 00:00:02 |

| | 1 | SORT AGGREGATE | | 1 | 3 |

| | 2 | INDEX FULL SCAN | INDEX_SL | 918K | 2691K | 2316 (1) | 00:00:02 |

Statistics

1 recursive calls

0 db block gets

2311 consistent gets

2314 physical reads

0 redo size

538 bytes sent via SQL*Net to client

524 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

Maximum and minimum

Unlike most other aggregation operations, MAX and MIN operations do not need to read every row of records if there is an index in the relevant column. If there is a B-tree index, we can determine the maximum or minimum value by checking the first or last index entry, which requires only 3-5 logical reads:

SQL > select max (amount_sold) from sales

MAX (AMOUNT_SOLD)

-

1782.72

Execution Plan

Plan hash value: 781264156

-

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

-

| | 0 | SELECT STATEMENT | | 1 | 5 | 3 (0) | 00:00:01 |

| | 1 | SORT AGGREGATE | | 1 | 5 |

| | 2 | INDEX FULL SCAN (MIN/MAX) | AMOUNT_SOLD_IDX | 1 | 5 | 3 (0) | 00:00:01 |

-

Statistics

1 recursive calls

0 db block gets

3 consistent gets

8 physical reads

0 redo size

536 bytes sent via SQL*Net to client

524 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

Find the maximum and minimum values at the same time

SQL > select max (amount_sold), min (amount_sold) from sales

MAX (AMOUNT_SOLD) MIN (AMOUNT_SOLD)

--

1782.72 6.4

Execution Plan

Plan hash value: 3519235612

-

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Pstart | Pstop |

-

| | 0 | SELECT STATEMENT | | 1 | 5 | 525 (2) | 00:00:01 |

| | 1 | SORT AGGREGATE | | 1 | 5 |

| | 2 | PARTITION RANGE ALL | | 918K | 4486K | 525K (2) | 00:00:01 | 1 | 28 |

| | 3 | TABLE ACCESS FULL | SALES | 918K | 4486K | 525K (2) | 00:00:01 | 1 | 28 | |

-

Statistics

1 recursive calls

0 db block gets

1635 consistent gets

1619 physical reads

0 redo size

618 bytes sent via SQL*Net to client

524 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

It is actually a better way to submit MAX and MIN queries separately and merge the results together:

SELECT max_sold, min_sold

FROM (SELECT MAX (amount_sold) max_sold FROM sales) maxt

2 3 (SELECT MIN (amount_sold) min_sold FROM sales) mint

MAX_SOLD MIN_SOLD

--

1782.72 6.4

Execution Plan

Plan hash value: 3650580342

-

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

-

| | 0 | SELECT STATEMENT | | 1 | 26 | 6 (0) | 00:00:01 |

| | 1 | NESTED LOOPS | | 1 | 26 | 6 (0) | 00:00:01 |

| | 2 | VIEW | | 1 | 13 | 3 (0) | 00:00:01 |

| | 3 | SORT AGGREGATE | | 1 | 5 |

| | 4 | INDEX FULL SCAN (MIN/MAX) | AMOUNT_SOLD_IDX | 1 | 5 | 3 (0) | 00:00:01 |

| | 5 | VIEW | | 1 | 13 | 3 (0) | 00:00:01 |

| | 6 | SORT AGGREGATE | | 1 | 5 |

| | 7 | INDEX FULL SCAN (MIN/MAX) | AMOUNT_SOLD_IDX | 1 | 5 | 3 (0) | 00:00:01 |

-

Statistics

1 recursive calls

0 db block gets

6 consistent gets

5 physical reads

0 redo size

602 bytes sent via SQL*Net to client

524 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

Top N query

How to get the first 10 rows of a table

Erroneous writing:

SQL > SELECT * FROM sales WHERE rownum

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.