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

The problem of collecting Statistical Information in the diagnosis of abrupt change in Oracle execution Plan

1. Situation description

DB version:11.2.0.4

WITH SQL1 AS (SELECT LAC, CI, TO_NUMBER (C.LONGITUDE) LONGITUDE, TO_NUMBER (C.LATITUDE) LATITUDE FROM MB_SYS_CELL_INFO C WHERE C.CONTY_NAME = 'Daofu County'), SQL2 AS (SELECT DISTINCT IMSI, LAC, CI FROM MB_BSS_USER_LOCATION WHERE HOUR IN (16,15,14,13) AND TIME = TO_TIMESTAMP), SQL3 AS (SELECT C.LONGITUDE, C.LATITUDE) WM_CONCAT (C.SITE_NAME) SITE_NAME FROM (SELECT DISTINCT TO_NUMBER (A.LONGITUDE) LONGITUDE, TO_NUMBER (A.LATITUDE) LATITUDE, A.SITE_NAME FROM MB_SYS_CELL_INFO A WHERE A.CONTY_NAME = 'Daofu County') C GROUP BY C.LONGITUDE, C.LATITUDE) SELECT SQL1.LONGITUDELNG, SQL1.LATITUDE LAT COUNT (DISTINCT SQL2.IMSI) COUNT, TO_CHAR (SQL3.SITE_NAME) SITE_NAME FROM SQL1, SQL2, SQL3 WHERE SQL2.LAC = SQL1.LAC AND SQL2.CI = SQL1.CI AND SQL1.LONGITUDE = SQL3.LONGITUDE AND SQL1.LATITUDE = SQL3.LATITUDEGROUP BY SQL1.LONGITUDE, SQL1.LATITUDE, TO_CHAR (SQL3.SITE_NAME) ORDER BY COUNTDESC

Initial error report, insufficient temporary table space

The above SQL is the development application SQL. When the SQL is executed, the temporary tablespace is monitored by the following command.

Use V$TEMPSEG_USAGE to monitor space usage and allocation:

SELECTsession_num, username, segtype, blocks, tablespaceFROMV$TEMPSEG_USAGE

Use V$SORT_SEGMENT to determine the percentage of true space usage:

SELECT (s.tot_used_blocks/f.total_blocks) * 100as pctusedFROM (SELECTSUM (used_blocks) tot_used_blocksFROMV$SORT_SEGMENTWHEREtablespace_name='TEMP') s, (SELECTSUM (blocks) total_blocksFROMDBA_TEMP_FILESWHEREtablespace_name='TEMP') f

It is found that a SQL can change the temporary tablespace exhaust of 64G, look at the corresponding row row, and find merge join cartesian.

This part can't be reproduced.

Supplement: the database is a new database, a large number of basic tables are synchronized by other libraries, and the application tables are real-time input tables (MB_BSS_USER_LOCATION), and I clearly remember that the auto maintaining task was opened at that time.

Check whether the statistics task is enabled:

Select client_name,statusfrom dba_autotask_client

two。 Processing steps

one

two

2.1 View the statistics of the large table select table_name, partition_name,last_analyzed, STATTYPE_LOCKED fromuser_tab_statistics where table_name = 'MB_BSS_USER_LOCATION';STATTYPE_LOCKED VARCHAR2 (5) Type ofstatistics lock: ■ DATA ■ CACHE ■ ALL

Last_analyzed, STATTYPE_LOCKED analysis shows that the table does not collect statistics, and the statistics are locked.

View statistics for other tables in the library.

Select count (distinct table_name) fromuser_tab_statistics where stattype_locked is not null

It was found that 98 table statistics were locked.

2.2 mandatory collection of corresponding table statistics SQL > exec dbms_stats.gather_table_stats (ownname = > 'GZ_SAFETY',tabname= >' MB_BSS_USER_LOCATION', force= > TRUE); PL/SQL proceduresuccessfully completed

Review the execution plan again.

-| Id | Operation | Name | Rows | Bytes | Cost | Time |- | 0 | SELECT STATEMENT | | 16 | 32608 | 41343 | 00:08:17 | | 1 | SORT ORDER BY | | 16 | 32608 | 41343 | 00:08:17 | | 2 | HASH GROUP BY | | 16 | 32608 | 41343 | 00 | : 08:17 | 3 | VIEW | VM_NWVW_1 | 16 | 32608 | 41341 | 00:08:17 | | 4 | HASH GROUP BY | | 16 | 33744 | 41341 | 00:08:17 | | * 5 | HASH JOIN | | 16 | 33744 | | | 41340 | 00:08:17 | * 6 | HASH JOIN | | 1 | 2069 | 00:00:02 | | * 7 | TABLE ACCESS FULL | MB_SYS_CELL_INFO | 448 | 18368 | 68 | 00:00:01 | 8 | VIEW | | 908544 | 70 | 00:00:01 | | 9 | SORT GROUP BY | | 26880 | 70 | 00:00:01 | 10 | VIEW | | 448 | 26880 | 69 | 00:00:01 | | 11 | HASH UNIQUE | | | 448 | 22400 | 69 | 00:00:01 | | * 12 | TABLE ACCESS FULL | MB_SYS_CELL_INFO | 22400 | 00:00:01 | 13 | PARTITION RANGE SINGLE | 3237748 | 129509920 | 41192 | 00:08:15 | | 14 | PARTITION LIST INLIST | | 3237748 | 129509920 | 41192 | 00:08: | 15 | | * 15 | TABLE ACCESS FULL | MB_BSS_USER_LOCATION | 3237748 | 129509920 | 41192 | 00:08:15 |

It is found that the Cartesian product merge join disappears and the execution plan is normal.

2.3 View the statistics of other tables (partitioned tables) select table_name,partition_name, last_analyzed, stattype_locked from user_tab_statistics wherestattype_locked is not null and object_type in ('PARTITION',' SUBPARTITION')

Because it is a test environment, do not pay attention to these tables for the time being, first lock and open the statistics of the MB_BSS_USER_ location table.

SQL > execdbms_stats.unlock_table_stats (ownname = > 'GZ_SAFETY',tabname= >' MB_BSS_USER_LOCATION'); PL/SQL procedure successfully completed, which can be viewed through user_tab_statistics.stattype_locked when opened. Fill: open the statistical information of the corresponding user. DBMS_STATS.UNLOCK_schema_STATS (user)

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.