Use Case Analysis of database Optimization Using Virtual Index and virtual Case Analysis

Use Case Analysis of database Optimization Using Virtual Index and virtual Case Analysis
When we optimize the production database, we mainly optimize SQL statements, including equivalent statement rewriting. However, a large part of the optimization is related to indexes. If appropriate indexes can be used properly, the logical reads generated by full table scans can be greatly reduced to improve database performance. Since indexes in Oracle databases occupy disk space, maintaining indexes requires a certain amount of overhead. How can we know that creating an index will improve data performance without making mistakes, I created an inappropriate index and finally had to delete it? In this case, we can use the virtual index provided by Oralce, that is, the nosegment index, which does not occupy disk resources, but adds a definition to the data dictionary. It provides a reference for DBAs to create indexes to improve database performance. The test and analysis are as follows:
SQL> startupORACLE instance started.
Total System Global Area 835104768 bytesFixed Size 2232960 bytesVariable Size 675286400 bytesDatabase Buffers 155189248 bytesRedo Buffers 2396160 bytesDatabase mounted. Database opened.
-- In the 11.2.0.3.0 environment, this test is slightly different from the 10g environment.SQL> select * from v $ version;
BANNER indexing Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit ProductionPL/SQL Release 11.2.0.3.0-ProductionCORE 11.2.0.3.0 ProductionTNS for Linux: Version 11.2.0.3.0-ProductionNLSRTL Version 11.2.0.3.0-Production
-- Create a test table fakeind and insert dataSQL> drop table fakeind_test; drop table fakeind_test * ERROR at line 1: ORA-00942: table or view does not exist

SQL> create table fakeind_test as select * from dba_objects;
Table created.
SQL> insert into fakeind_test select * from fakeind_test;
75540 rows created.
SQL>/
151080 rows created.
SQL>/
302160 rows created.
SQL> select count (*) from fakeind_test;
COUNT (*) ---------- 604320
-- Start the test and execute the querySQL> set line 130 pages 130
SQL> select object_id, object_name from fakeind_test where object_id in (select distinct object_id from fakeind_test where object_id> 44500 and object_id <45000 );
3992 rows selected.

Execution Plan ---------------------------------------------------------- Plan hash value: 1190425891
Role | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | percent | 0 | select statement | 3816 | 160K | 4667 (1) | 00:00:57 | * 1 | hash join right semi | 3816 | 160K | 4667 (1) | 00:00:57 | 2 | VIEW | VW_NSO_1 | 3819 | 49647 | 2333 (1) | 00:00:28 | * 3 | table access full | FAKEIND_TEST | 3819 | 19095 | 2333 (1) | 00:00:28 | 4 | table access full | FAKEIND_TEST | 604K | 17M | 2331 (1) | 00:00:28 | average -------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):---------------------------------------------------
1-access ("OBJECT_ID" = "OBJECT_ID") 3-filter ("OBJECT_ID"> 44500 AND "OBJECT_ID" <45000)

Statistics limit 23 recursive cballs 0 db block gets 17436 consistent gets 0 physical reads 0 redo size 144488 bytes sent via SQL * Net to client 3445 bytes encoded ed via SQL * Net from client 268 SQL * Net roundtrips to/from client 3 sorts (memory) 0 sorts (disk) 3992 rows processed
As you can see, the test table fakeind created with CTAS has no index currently. Therefore, in the generated execution plan, this SQL statement can only be used for full table scanning.
-- Create a virtual index (add nosegmnet after the common index creation command)SQL> create index ind_fake_id on fakeind_test (object_id) nosegment;
Index created.
-- Set implicit parameters to make the virtual index take effectSQL> alter session set "_ use_nosegment_indexes" = true; -- note that double quotation marks must be written.

Session altered.
SQL> set autot off
-- Check whether the table has been analyzedSQL> select table_name, last_analyzed from dba_tables where table_name = 'fakeind _ test ';
TABLE_NAME LAST_ANALYZED ------------------------------ -------------------- FAKEIND_TEST
-- Collect the statistics of the test tableSQL> exec dbms_stats.gather_table_stats (ownname => 'sys ', tabname => 'fakeind _ test', degree => 4, estimate_percent => 100, cascade => true );
PL/SQL procedure successfully completed.
-- Confirm the analysis of the table againSQL> select table_name, last_analyzed from dba_tables where table_name = 'fakeind _ test ';
TABLE_NAME LAST_ANALYZED ------------------------------ -------------------- FAKEIND_TEST 17-SEP-14
-- Query the test table againSQL> set autot traceSQL> select object_id, object_name from fakeind_test where object_id in (select distinct object_id from fakeind_test where object_id> 44500 and object_id <45000 );
3992 rows selected.

Execution Plan ---------------------------------------------------------- Plan hash value: 2531911586
Role | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | percent | 0 | select statement | 3904 | 308K | 12 (17) | 00:00:01 | 1 | VIEW | VM_NWVW_2 | 3904 | 308K | 12 (17) | 00:00:01 | 2 | hash unique | 3904 | 179K | 12 (17) | 00:00:01 | * 3 | hash join | 3904 | 179K | 11 (10) | 00:00:01 | * 4 | index range scan | IND_FAKE_ID | 3819 | 19095 | 2 (0) | 00:00:01 | 5 | table access by index rowid | FAKEIND_TEST | 3819 | 156K | 8 (0) | 00:00:01 | * 6 | index range scan | IND_FAKE_ID | 3819 | 2 (0) | 00:00:01 | average -----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):---------------------------------------------------
3-access ("OBJECT_ID" = "OBJECT_ID") 4-access ("OBJECT_ID"> 44500 AND "OBJECT_ID" <45000) 6-access ("OBJECT_ID"> 44500 AND "OBJECT_ID" <45000)

Statistics limit 1 recursive cballs 0 db block gets 17418 consistent gets 0 physical reads 0 redo size 144488 bytes sent via SQL * Net to client 3445 bytes encoded ed via SQL * Net from client 268 SQL * Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 3992 rows processed
SQL> set autot off
In the execution plan obtained by using the virtual index, the COST is reduced from more than 4000 to 12, and the execution time is also from s to 1 s. Therefore, we can judge that when the real index is added, performance will be greatly improved.

-- Create a real Index
SQL> create index ind_real_id on fakeind_test (object_id );

Index created.
SQL> set autot traceSQL> select object_id, object_name from fakeind_test where object_id in (select distinct object_id from fakeind_test where object_id> 45500 and object_id <50000 );
35992 rows selected.

Execution Plan ---------------------------------------------------------- Plan hash value: 2531911586
Operation | Id | Operation | Name | Rows | Bytes | TempSpc | Cost (% CPU) | Time | Bytes | 0 | select statement | 41816 | 3307K | 548 (1) | 00:00:07 | 1 | VIEW | VM_NWVW_2 | 41816 | 3307K | 548 (1) | 00:00:07 | 2 | hash unique | 41816 | 1919K | 2472K | 548 (1) | 00:00:07 | * 3 | hash join | 41816 | 1919K | 53 (2) | 00:00:01 | * 4 | index range scan | IND_FAKE_ID | 34375 | 167K | 3 (0) | 00:00:01 | 5 | table access by index rowid | FAKEIND_TEST | 34375 | 1409K | 49 (0) | 00:00:01 | * 6 | index range scan | IND_FAKE_ID | 34375 | 3 (0) | 00:00:01 | average -------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):---------------------------------------------------
3-access ("OBJECT_ID" = "OBJECT_ID") 4-access ("OBJECT_ID"> 45500 AND "OBJECT_ID" <50000) 6-access ("OBJECT_ID"> 45500 AND "OBJECT_ID" <50000)

Statistics limit 1 recursive cballs 0 db block gets 11017 consistent gets 82 physical reads 0 redo size 1293055 bytes sent via SQL * Net to client 26908 bytes encoded ed via SQL * Net from client 2401 SQL * Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 35992 rows processed
Although a real index is created, the database still uses a virtual index. In this case, the COST and TIME are a little more, so you need to disable the virtual index first.
SQL> alter session set "_ use_segment_indexes" = false;
-- Disable the virtual index and view the SQL statement.SQL> select object_id, object_name from fakeind_test where object_id in (select distinct object_id from fakeind_test where object_id> 45500 and object_id <50000 );
35992 rows selected.

Execution Plan ---------------------------------------------------------- Plan hash value: 750753197
Role | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | percent | 0 | select statement | 34375 | 1443K | 2414 (1) | 00:00:29 | * 1 | hash join right semi | 34375 | 1443K | 2414 (1) | 00:00:29 | 2 | VIEW | VW_NSO_1 | 34375 | 436K | 79 (0) | 00:00:01 | * 3 | index range scan | IND_REAL_ID | 34375 | 167K | 79 (0) | 00:00:01 | 4 | table access full | FAKEIND_TEST | 604K | 17M | 2331 (1) | 00:00:28 | average -------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):---------------------------------------------------
1-access ("OBJECT_ID" = "OBJECT_ID") 3-access ("OBJECT_ID"> 45500 AND "OBJECT_ID" <50000)

Statistics defaults 0 recursive cballs 0 db block gets 11017 consistent gets 0 physical reads 0 redo size 1293055 bytes sent via SQL * Net to client 26908 bytes received via SQL * Net from client 2401 SQL * Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 35992 rows processed
Although the use of real indexes does not improve performance as much as when using virtual indexes, it is at least 28 seconds faster than when no index was initially available, COST is also reduced by nearly half. After a real index is created, you can delete the virtual index to avoid occupying an object name. The delete syntax is the same as that for deleting a common index.
Note the following points for a virtual index:
-- The virtual index does not exist in the dba_indexes view.SQL> select index_name from dba_indexes where index_name = 'ind _ FAKE_ID ';
No rows selected
-- A real index with the same name as a virtual index cannot be created.SQL> create index ind_fake_id on fakeind_test (object_name); create index ind_fake_id on fakeind_test (object_name) * ERROR at line 1: ORA-00955: name is already used by an existing object
-- You cannot use the alter command to modify or recreate an index.SQL> alter index ind_fake_id rename to ind_fake_name; alter index ind_fake_id rename to ind_fake_name * ERROR at line 1: ORA-08114: can not alter a fake index

SQL> alter index ind_fake_id rebuild; alter index ind_fake_id rebuild * ERROR at line 1: ORA-08114: can not alter a fake index
-- View virtual IndexesSQL> set autot offSQL> SELECT index_owner, index_name 2 FROM dba_ind_columns 3 WHERE index_name NOT LIKE 'bin $ % '4 MINUS 5 SELECT owner, index_name 6 FROM dba_indexes;
INDEX_OWNER INDEX_NAME ------------------------------ SYS IND_FAKE_ID

Summary:
In fact, this test is not perfect, and the SQL statement is not well selected. Under normal circumstances, the table involved in the original statement must have at least one primary key index, non-indexed tables are not realistic in the OLTP production database. This article mainly serves as an example. When we are faced with a database optimization scenario, we need to test whether creating an index with a specific condition will improve the system performance, you can use virtual indexes for testing, because indexes cannot be created at will in the production database, and maintaining indexes is not a small overhead for the database. Besides, if indexes are improperly created, it may make the database performance worse.






Database Case Analysis and Design

30 RMB may be answered.
 
Monitoring Index

--
Alter index INDEX1 MONITORING USAGE
Alter index INDEX1 NOMONITORING USAGE
--