Mandalika's scratchpad | [ Work blog @Oracle | My Music Compositions ] |
Old Posts: 09.04 10.04 11.04 12.04 01.05 02.05 03.05 04.05 05.05 06.05 07.05 08.05 09.05 10.05 11.05 12.05 01.06 02.06 03.06 04.06 05.06 06.06 07.06 08.06 09.06 10.06 11.06 12.06 01.07 02.07 03.07 04.07 05.07 06.07 08.07 09.07 10.07 11.07 12.07 01.08 02.08 03.08 04.08 05.08 06.08 07.08 08.08 09.08 10.08 11.08 12.08 01.09 02.09 03.09 04.09 05.09 06.09 07.09 08.09 09.09 10.09 11.09 12.09 01.10 02.10 03.10 04.10 05.10 06.10 07.10 08.10 09.10 10.10 11.10 12.10 01.11 02.11 03.11 04.11 05.11 07.11 08.11 09.11 10.11 11.11 12.11 01.12 02.12 03.12 04.12 05.12 06.12 07.12 08.12 09.12 10.12 11.12 12.12 01.13 02.13 03.13 04.13 05.13 06.13 07.13 08.13 09.13 10.13 11.13 12.13 01.14 02.14 03.14 04.14 05.14 06.14 07.14 09.14 10.14 11.14 12.14 01.15 02.15 03.15 04.15 06.15 09.15 12.15 01.16 03.16 04.16 05.16 06.16 07.16 08.16 09.16 12.16 01.17 02.17 03.17 04.17 06.17 07.17 08.17 09.17 10.17 12.17 01.18 02.18 03.18 04.18 05.18 06.18 07.18 08.18 09.18 11.18 12.18 01.19 02.19 05.19 06.19 08.19 10.19 11.19 05.20 10.20 11.20 12.20 09.21 11.21 12.22
It is well known that the entire shared pool can be flushed with a simple ALTER SYSTEM
statement.
SQL> ALTER SYSTEM FLUSH SHARED_POOL; System altered.
What if the execution plan of a single SQL statement has to be invalidated or flushed out of the shared pool so the subsequent query execution forces a hard parse on that SQL statement. Oracle 11g introduced a new procedure called PURGE
in the DBMS_SHARED_POOL
package to flush a specific object such as a cursor, package, sequence, trigger, .. out of the object library cache.
The syntax for the PURGE
procedure is shown below.
procedure PURGE ( name VARCHAR2, flag CHAR DEFAULT 'P', heaps NUMBER DEFAULT 1)
Explanation for each of the arguments is documented in detail in $ORACLE_HOME/rdbms/admin/dbmspool.sql file.
If a single SQL statement has to be flushed out of the object library cache, the first step is to find the address of the handle and the hash value of the cursor that has to go away. Name of the object [to be purged] is the concatenation of the ADDRESS
and HASH_VALUE
columns from the V$SQLAREA
view. Here is an example:
SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '7yc%'; ADDRESS HASH_VALUE ---------------- ---------- 000000085FD77CF0 808321886 SQL> exec DBMS_SHARED_POOL.PURGE ('000000085FD77CF0, 808321886', 'C'); PL/SQL procedure successfully completed. SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '7yc%'; no rows selected
Note to Oracle 10g R2 Customers
The enhanced DBMS_SHARED_POOL
package with the PURGE
procedure is included in the 10.2.0.4 patchset release.
10.2.0.2 and 10.2.0.3 customers can download and install RDBMS patch 5614566 to get access to these enhancements in DBMS_SHARED_POOL
package.
Also see:
.. unless you are working with a database that is largely read-only or if the new index is supposed to be fixing a critical performance issue with no side effect(s).
Two topics covered in this blog entry with plenty of simple examples:
Indexes are double-edged swords that may improve the performance of targeted queries, but in some cases they may accidentally degrade the performance of other queries that are not targeted. In any case, exercise caution while adding a new index to the database. After adding a new index, monitor the overall performance of the database, not just the targeted query.
If DML statements that modify data (INSERT, UPDATE, or DELETE) are being executed large number of times on a table, make sure that the addition of a new index on the same table does not negatively affect the performance of those DML operations. Usually this is not a problem if the SQLs being executed are simply retrieving but not adding or modifying the existing data. In all other cases, there is some performance overhead induced by the addition of each new index. For example, if there are 10 indexes created on a table DUMMY
, adding a new row of data to the table DUMMY
may require updating all 10 indexes behind the scenes by the database management system.
Here is an example demonstrating the performance overhead of a new index on a table.
SQL> CREATE TABLE VIDEO 2 (BARCODE VARCHAR(10) NOT NULL, 3 TITLE VARCHAR2(25) NOT NULL, 4 FORMAT VARCHAR2(10), 5 PRICE NUMBER, 6 DATA_OF_RELEASE DATE) 7 / Table created. SQL> insert into VIDEO values ('9301224321', 'AVATAR', 'BLU-RAY', 19.99, '22-APR-2010'); 1 row created. .. SQL> insert into VIDEO values ('3782460017', 'THE SIMPSONS - SEASON 20', 'BLU-RAY', 29.99, '04-JUL-2009'); 1 row created. SQL> select * from VIDEO; BARCODE TITLE FORMAT PRICE DATA_OF_RELEASE --------------- ----------------------------------- --------------- ---------- --------------- 9301224321 AVATAR BLU-RAY 19.99 22-APR-10 7619203043 BEN-HUR VHS 9.79 12-MAR-63 7305832093 THE MATRIX DVD 12.29 03-DEC-99 4810218795 MEMENTO DVD 8.49 02-FEB-02 3782460017 THE SIMPSONS - SEASON 20 BLU-RAY 29.99 04-JUL-09 SQL> select * from USER_INDEXES where TABLE_NAME = 'VIDEO'; no rows selected SQL> alter session set events '10046 trace name context forever, level 8'; Session altered. SQL> select * from VIDEO where FORMAT = 'BLU-RAY'; BARCODE TITLE FORMAT PRICE DATA_OF_RELEASE --------------- ----------------------------------- --------------- ---------- --------------- 9301224321 AVATAR BLU-RAY 19.99 22-APR-10 3782460017 THE SIMPSONS - SEASON 20 BLU-RAY 29.99 04-JUL-09 SQL> alter session set events '10046 trace name context off'; Session altered.
SQL trace file has the following contents.
SQL ID: 0pu5s70nsdnzv Plan Hash: 3846322456 SELECT * FROM VIDEO WHERE FORMAT = :"SYS_B_0" call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 0 16 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 16 0 2 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 28 Rows Row Source Operation ------- --------------------------------------------------- 2 TABLE ACCESS FULL VIDEO (cr=16 pr=0 pw=0 time=3 us cost=4 size=100 card=2)
Let's create an index and see what happens.
SQL> create index VIDEO_IDX1 on VIDEO (FORMAT); Index created. SQL> alter session set events '10046 trace name context forever, level 8'; Session altered. SQL> select * from VIDEO where FORMAT = 'BLU-RAY'; BARCODE TITLE FORMAT PRICE DATA_OF_RELEASE --------------- ----------------------------------- --------------- ---------- --------------- 9301224321 AVATAR BLU-RAY 19.99 22-APR-10 3782460017 THE SIMPSONS - SEASON 20 BLU-RAY 29.99 04-JUL-09 SQL> alter session set events '10046 trace name context off'; Session altered.
The latest contents of the trace file are as follows. Notice the reduction in buffer gets from 16 to 4. That is, the new index improved the query performance by 75%.
SQL ID: 0pu5s70nsdnzv Plan Hash: 2773508764 SELECT * FROM VIDEO WHERE FORMAT = :"SYS_B_0" call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 0 4 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 4 0 2 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 28 (CS90) Rows Row Source Operation ------- --------------------------------------------------- 2 TABLE ACCESS BY INDEX ROWID VIDEO (cr=4 pr=0 pw=0 time=12 us cost=2 size=100 card=2) 2 INDEX RANGE SCAN VIDEO_IDX1 (cr=2 pr=0 pw=0 time=10 us cost=1 size=0 card=2)(object id 76899) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT MODE: ALL_ROWS 2 TABLE ACCESS (BY INDEX ROWID) OF 'VIDEO' (TABLE) 2 INDEX MODE: ANALYZED (RANGE SCAN) OF 'VIDEO_IDX1' (INDEX)
So far so good. Let's add a new row of data and examine the trace file one more time. From hereafter, keep an eye on the "current" column (logical IOs performed due to an INSERT, UPDATE or DELETE) and notice how it changes with different actions -- adding and removing: indexes, new row(s) of data etc.,
SQL ID: dnb2d8cpdj56p Plan Hash: 0 INSERT INTO VIDEO VALUES (:"SYS_B_0", :"SYS_B_1", :"SYS_B_2", :"SYS_B_3", :"SYS_B_4") call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 1 7 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 1 7 1 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 28 (CS90) Rows Row Source Operation ------- --------------------------------------------------- 0 LOAD TABLE CONVENTIONAL (cr=1 pr=0 pw=0 time=0 us) Rows Execution Plan ------- --------------------------------------------------- 0 INSERT STATEMENT MODE: ALL_ROWS 0 LOAD TABLE CONVENTIONAL OF 'VIDEO'
Now drop the index, re-insert the last row and get the tracing data again.
SQL> drop index VIDEO_IDX1; Index dropped. SQL> delete from VIDEO where BARCODE ='4457332907'; 1 row deleted. SQL> commit; Commit complete. SQL> alter session set events '10046 trace name context forever, level 8'; Session altered. SQL> insert into VIDEO values ('4457332907', 'KING OF THE HILL - ALL', 'DVD', 90.00, '01-JAN-2011'); 1 row created. SQL> alter session set events '10046 trace name context off'; Session altered.
The contents of the latest trace file are shown below.
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.01 0.00 0 2 5 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.01 0.00 0 2 5 1 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 28 (CS90) Rows Row Source Operation ------- --------------------------------------------------- 0 LOAD TABLE CONVENTIONAL (cr=1 pr=0 pw=0 time=0 us) Rows Execution Plan ------- --------------------------------------------------- 0 INSERT STATEMENT MODE: ALL_ROWS 0 LOAD TABLE CONVENTIONAL OF 'VIDEO'
This time create two indexes and see what happens.
SQL> CREATE INDEX VIDEO_IDX1 ON VIDEO (FORMAT); Index created. SQL> CREATE INDEX VIDEO_IDX2 ON VIDEO (TITLE); Index created. Trace file contents: call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 1 9 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 1 9 1
Notice the two additional logical IOs (look under "current" column). Those additional logical input/output operations are the result of the new indexes. The number goes up as we add more indexes and data to the table VIDEO
.
SQL> delete from VIDEO where BARCODE ='4457332907'; 1 row deleted. SQL> commit; Commit complete. SQL> create index VIDEO_IDX3 on VIDEO (PRICE, DATA_OF_RELEASE); Index created. SQL> alter session set events '10046 trace name context forever, level 8'; Session altered. SQL> insert into VIDEO values ('4457332907', 'KING OF THE HILL - ALL', 'DVD', 90.00, '01-JAN-2011'); 1 row created. SQL> alter session set events '10046 trace name context off'; Session altered. SQL trace: call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 1 11 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 1 11 1
You can try other operations such as UPDATE, DELETE on your own.
Since there are only few rows of data in the table VIDEO
, it is hard to notice the real performance impact in these examples. If you really want to see the negative performance impact due to the large number of indexes on a heavily updated table, try adding thousands or millions of rows of data and few more indexes.
Moral of the story: Indexes aren't always cheap - they may have some overhead associated with them. Be aware of those overheads and ensure that the index maintenance overhead do not offset the performance gains resulting from the indexes created on a particular table.
Now we know the possible disadvantage of having too many indexes on a heavily updated table. One way to reduce the index maintenance overhead is to instrument the indexes so we can monitor their usage from time to time and remove the unused indexes. To start monitoring the index usage, alter the index by specifying the keywords MONITORING USAGE
.
SQL> select index_name from user_indexes where table_name = 'VIDEO'; INDEX_NAME -------------------------------------------------------------------------------- VIDEO_IDX3 VIDEO_IDX1 VIDEO_IDX2 SQL> alter index VIDEO_IDX1 MONITORING USAGE; Index altered. SQL> alter index VIDEO_IDX2 MONITORING USAGE; Index altered. SQL> alter index VIDEO_IDX3 MONITORING USAGE; Index altered.
Once the indexes are instrumented, query the V$OBJECT_USAGE
view occasionally to see if the instrumented indexes are being used in executing SQL queries.
SQL> select * from VIDEO where BARCODE LIKE '%22%'; BARCODE TITLE FORMAT PRICE DATA_OF_RELEASE --------------- ----------------------------------- --------------- ---------- --------------- 9301224321 AVATAR BLU-RAY 19.99 22-APR-10 SQL> select * from VIDEO where FORMAT = 'VHS'; BARCODE TITLE FORMAT PRICE DATA_OF_RELEASE --------------- ----------------------------------- --------------- ---------- --------------- 7619203043 BEN-HUR VHS 9.79 12-MAR-63 SQL> select * from VIDEO where PRICE < 20; BARCODE TITLE FORMAT PRICE DATA_OF_RELEASE --------------- ----------------------------------- --------------- ---------- --------------- 4810218795 MEMENTO DVD 8.49 02-FEB-02 7619203043 BEN-HUR VHS 9.79 12-MAR-63 7305832093 THE MATRIX DVD 12.29 03-DEC-99 9301224321 AVATAR BLU-RAY 19.99 22-APR-10 SQL> select * from VIDEO where FORMAT = 'BLU-RAY' AND DATA_OF_RELEASE < '01-JAN-2010'; BARCODE TITLE FORMAT PRICE DATA_OF_RELEASE --------------- ----------------------------------- --------------- ---------- --------------- 3782460017 THE SIMPSONS - SEASON 20 BLU-RAY 29.99 04-JUL-09 SQL> column INDEX_NAME format A25 SQL> column START_MONITORING format A30 SQL> select INDEX_NAME, USED, START_MONITORING 2 from V$OBJECT_USAGE 3 where INDEX_NAME LIKE 'VIDEO_IDX%' 4 / INDEX_NAME USED START_MONITORING ------------------------- --------- ------------------------------ VIDEO_IDX1 YES 04/27/2010 01:10:20 VIDEO_IDX2 NO 04/27/2010 01:10:25 VIDEO_IDX3 YES 04/27/2010 01:10:31
In the above example, the index VIDEO_IDX2
was not in use during the period of index monitoring. If we are convinced that the queries that will be executed are similar to the ones that were executed during the index monitoring period, we can go ahead and remove the index VIDEO_IDX2
to reduce the performance overhead during updates on table VIDEO
.
To stop monitoring the index usage, alter the index with the keywords NOMONITORING USAGE
.
SQL> alter index VIDEO_IDX1 NOMONITORING USAGE; Index altered.
[12/09/09] Track #1 Hokum | ||
[12/09/09] Track #2 Thrum | ||
[12/18/09] Track #3 Kabuki Dance | ||
[01/05/10] Track #4 Phantasm | ||
[05/10/10] Track #5 Transgression |
UPDATE "schema"."TABLE" mtu SET mtu.base_process_order = NVL((SELECT mts1.process_order FROM "schema"."TABLE" mts1 WHERE ..
Export: Release 11.1.0.7.0 - 64bit Production on Wednesday, 31 March, 2010 6:56:50 Copyright (c) 2003, 2007, Oracle. All rights reserved. ;;; Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SCHMA"."SYS_EXPORT_TABLE_01": SCHMA/******** DIRECTORY=exp_dir DUMPFILE=SOME_DUMMY_PART_FULL.DMP TABLES=SOME_DUMMY_PART Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 20.56 GB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "SCHMA"."SOME_DUMMY_PART":"DUMMY_PART_P01" 1.143 GB 13788224 rows . . exported "SCHMA"."SOME_DUMMY_PART":"DUMMY_PART_P02" 1.143 GB 13788224 rows . . exported "SCHMA"."SOME_DUMMY_PART":"DUMMY_PART_P03" 1.143 GB 13788224 rows ... . . exported "SCHMA"."SOME_DUMMY_PART":"DUMMY_PART_P32" 151.1 MB 1789216 rows . . exported "SCHMA"."SOME_DUMMY_PART":"DUMMY_PART_P33" 11.37 MB 136046 rows . . exported "SCHMA"."SOME_DUMMY_PART":"DUMMY_PART_P00" 0 KB 0 rows Master table "SCHMA"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SCHMA.SYS_EXPORT_TABLE_01 is: /DBDUMP/SOME_DUMMY_PART_FULL.DMP Job "SCHMA"."SYS_EXPORT_TABLE_01" successfully completed at 11:22:36
2004-2019 |