Statistical Analysis of temporary Oracle tables awr

Business Needs: I created a table named t_0_mtbuf in Oracle and created it using a statement for creating a common table. In fact, this table serves as a temporary table, namely:

In java, select 1500 entries each time, insert them into the sqlserver database one by one in the loop, and delete the record in oracle.

However, this operation has been running for a while. Today, more than 100 pieces of data are cached in the Table. dozens or even of data records have been taken before. Now there are two and one rows of data, the data cannot be moved, and the problem is serious.

Therefore, the experts asked me to execute the following sentence:

[SQL]

1. AnalyzeTableT_0_mtbuf computeStatistics

It took some time to execute this sentence. After the execution, the speed was amazing!

--This statement re-analyzes the statistical information of the table.,

For example,

Your table has 10 records and 5 have been deleted, but the database does not know that you have deleted 5 Records and 10 other records.

In this way, the database is easily parked there.

This statement tells oracle to re-calculate the table information for me. Both sides are consistent and the problem is solved.

-- Q: Is there a good statement for deleting records? After the statement is deleted directly, both sides are synchronized.

-- A: Use truncate to delete a table, but clear the table. You cannot add the where condition to delete some records.

-- Q:Records must be deleted one by one

-- Answer:Add commit and submit the deletion. You can also perform some control to delete 200 commit records once.

-- Q: I cannot figure it out. There are only pieces of data in my table. There is no commit, and there are only 1500 records at most. How can this problem be solved?

-- Answer:This isCommon problems with using a data table as a temporary tableFrequent additions and deletions often lead to slow access.

View the physical size and partition of a table:

[SQL]

1. SelectSegment_name, bytes/1024/1024 MBFromUser_segmentsWhereSegment_name ='Emp'

Replace emp with your table name,The table name must be capitalized in single quotes..Generally, 300 million data is about MB. 300 m a day, about 10g a month. Partitions can be performed on a monthly basis, with 12 tables per month per year. Each partition table is a subpartition every day.

 

Awr statistical analysis

In addition, before finding the cause, we need to analyze what causes the data to be unable to move: Use awr statistics to see what causes wait

[SQL]

1. Select*FromDba_directories

Check whether there is directories. If not, create one.

[SQL]

1. SQL>Select*FromDba_directories;
3. OWNER DIRECTORY_NAME DIRECTORY_PATH
4. --------------------------------------------------------------------------------------------------------------------------------------------
5. SYS ADMIN_DIR C: \ ADE \ aime_10.2_nt_push \ oracle/md/admin
6. Sys subdir d: \ oracle \ product \ 10.2.0 \ db_1 \ demo \Schema\ Order_entry \/ 2002/Sep
7. SYS DATA_FILE_DIR D: \ oracle \ product \ 10.2.0 \ db_1 \ demo \Schema\ Sales_history \
8. SYS WORK_DIR C: \ ADE \ aime_10.2_nt_push \ oracle/Work
9. SYS LOG_FILE_DIR D: \ oracle \ product \ 10.2.0 \ db_1 \ demo \Schema\ Log \
10. SYS MEDIA_DIR D: \ oracle \ product \ 10.2.0 \ db_1 \ demo \Schema\ Product_media \
11. Sys xmldir d: \ oracle \ product \ 10.2.0 \ db_1 \ demo \Schema\ Order_entry \
12. SYS DATA_PUMP_DIR D: \ oracle \ product \ 10.2.0 \ db_1 \ admin \ orclyxkj \ dpdump \
14. 8RowsSelected

[SQL]

1. Create Or ReplaceDirectory wlldpdataAs 'D: \ dump';

Create one, followed by a path. Make sure this folder is available.

[SQL]

1. SQL>Select*FromDba_directories;
3. OWNER DIRECTORY_NAME DIRECTORY_PATH
4. --------------------------------------------------------------------------------------------------------------------------------------------
5. SYS ADMIN_DIR C: \ ADE \ aime_10.2_nt_push \ oracle/md/admin
6. Sys subdir d: \ oracle \ product \ 10.2.0 \ db_1 \ demo \Schema\ Order_entry \/ 2002/Sep
7. SYS DATA_FILE_DIR D: \ oracle \ product \ 10.2.0 \ db_1 \ demo \Schema\ Sales_history \
8. SYS WORK_DIR C: \ ADE \ aime_10.2_nt_push \ oracle/Work
9. SYS LOG_FILE_DIR D: \ oracle \ product \ 10.2.0 \ db_1 \ demo \Schema\ Log \
10. SYS MEDIA_DIR D: \ oracle \ product \ 10.2.0 \ db_1 \ demo \Schema\ Product_media \
11. Sys xmldir d: \ oracle \ product \ 10.2.0 \ db_1 \ demo \Schema\ Order_entry \
12. SYS DATA_PUMP_DIR D: \ oracle \ product \ 10.2.0 \ db_1 \ admin \ orclyxkj \ dpdump \
13. Sys wlldpdata e: \ dump
15. 9RowsSelected

[SQL]

1. ExecDBMS_SWRF_INTERNAL.AWR_EXTRACT (DMPFILE =>'Awr. dmp', DMPDIR =>'Wlldpdata');

Find awr. dmp in the d: \ dump path.