Oracle partition and Performance Analysis

In terms of Oracle partitioning and performance analysis, we perform Table Partitioning to improve performance and speed up query. However, sometimes the query speed is not accelerated, but slowed down. The example below is as follows: www.2cto.com suppose a table is sharded by primary key, and the table is created as follows: SQL code create table t (OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY) partition by hash (object_id) partitions 16 as select * from all_objects, query by owner, object_type, object_name, owner, object_type, and object_name. So we want to create a local index on these three columns. The SQL code create index t_idx on t (owner, object_type, object_name) runs a stored procedure to collect cpu usage and other information: SQL code begin dbms_stats.gather_table_stats (user,'t ', cascade => true); end;/modify several session attributes to allow TKPROF SQL code alter session set timed_statistics = true; alter session set events '10046 trace name context forever, level 12'; execute the following SQL statement: SQL code select * from t where owner = 'Scott 'and object_type = 'table' and object_nam E = 'emp'; select * from all_objects where owner = 'Scott 'and object_type = 'table' and object_name = 'emp'; view the performance log location recorded by TKPROF: SQL code select rtrim (c. value, '\') | '\' | d. instance_name | '_ ora _' | ltrim (to_char (. spid) | '. trc 'from v $ process a, v $ session B, v $ parameter c, v $ instance d where. addr = B. paddr and B. audsid = sys_context ('userenv', 'sessionid') and c. name = 'user _ dump_dest '; If the output is D: \ ORACLEL WS \ PRODUCT \ 10.2.0 \ ADMIN \ ORCL \ UDUMP \ orcl_ora_5928.trc open another command line and enter the following command to parse the log file SQL code tkprof D: \ ORACLELWS \ PRODUCT \ 10.2.0 \ ADMIN \ ORCL \ UDUMP \ orcl_ora_5928.trc a pop-up output = enter the directory for storing the file after parsing the log. You can enter c: \ tk. prof, must be the extension of prof. In this case, we can use NotePad to open tk. view the performance of the preceding two SQL statements in the prof file, the following SQL code is displayed on my machine: select * from t where owner = 'Scott 'and object_type = 'table' and object_name = 'emp' call count cpu elapsed disk query current rows. ------- ------ -------- ---------- Parse 1 0.00 0.00 0 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 0 Fetch 2 0.00 0.00 0 34 0 1 ------------------------------------------- -------- ---------- Total 4 0.00 0.00 0 34 0 1 select * from all_objects where owner = 'Scott 'and object_type = 'table' and object_name = 'emp' call count cpu elapsed disk query current rows ------- ------ -------- ---------- Parse 1 0.01 0.01 0.00 0 0 0 0 0 Execute 1 0.00 0 0 0 0 0 0 Fetch 2 0.00 0.00 0 8 0 1 ------------------------------------ ----- ---------- Total 4 0.01 0.01 0 8 0 the meaning of each field can be Baidu, we found that the query field partition is 34, not the partition is 8. Query indicates the number of I/O operations, but partitions increase the number of I/O operations, which is laborious and thankless. This is because the search condition field does not contain the partition key of the partition table, so that each table partition needs to be scanned, that is, each index partition needs to be scanned, and it is not as fast as scanning the entire table. If www.2cto.com does not create a local index, it creates a global index. The efficiency is similar to that of non-partition. Replace the SQL statement used to create a local partition with: SQL code create index t_idx on t (owner, object_type, object_name) global partition by hash (owner) partitions 16, for OLTP systems, partitions have no positive impact on data acquisition (query speed cannot be improved ). On the contrary, we are very careful to avoid negative effects, such as the example above. However, for highly concurrent modification environments, the partition may provide significant results, because the partition avoids data conflicts. You can modify the partition and modify the partition, this greatly reduces the possibility of conflicts. Therefore, do not blindly use partitions, which may not necessarily improve performance.