Why Is My Query Sometimes Slower Than Other Times with Higher Consistent Gets Although No Change in Execution Plan?
Why Is My Query Sometimes Slower Than Other Times with Higher Consistent Gets Although No Change in Execution Plan?
Information in this document applies to any platform.
APPLIES TO:
Oracle Database - Enterprise Edition - Version 11.2.0.1 and laterInformation in this document applies to any platform.
SYMPTOMS
A new query is tested on a test database. When the same query is tested in a production environment, the elapsed time is higher.
CHANGES
The procedures in package DBMS_XPLAN indicate no change in execution plan between the two environments. The test environment is restored back-up of the production database with the same volume of data.
CAUSE
The difference is between executing the query in an isolated environment versus within an environment in which DML is being applied to the table while the query is executing.
In order to provide an example scenario, let us create a table called MY_OBJECTS, based upon the data held within DBA_OBJECTS.
SQL> CREATE TABLE my_objects
2 AS SELECT * FROM dba_objects;
Table created.
SQL>
SQL> begin
2 for i in 1..50 loop
3 begin
4 INSERT /*+ APPEND */ INTO my_objects
5 select * from dba_objects;
6 commit;
7 end;
8 end loop;
9 end;
10 /
PL/SQL procedure successfully completed.
2 AS SELECT * FROM dba_objects;
Table created.
SQL>
SQL> begin
2 for i in 1..50 loop
3 begin
4 INSERT /*+ APPEND */ INTO my_objects
5 select * from dba_objects;
6 commit;
7 end;
8 end loop;
9 end;
10 /
PL/SQL procedure successfully completed.
Here is the first test query against MY_OBJECTS, performing a count of objects by object type. At this time, there are no other sessions accessing the table. Let us examine specific rows within V$MYSTAT to examine the work involved in executing this query.
SQL> SELECT a.object_type, COUNT(*) HOW_MANY
2 FROM my_objects a, my_objects b
3 WHERE a.owner=b.owner AND a.object_name=b.object_name
4 AND a.object_type=b.object_type
5 GROUP BY a.object_type
6 ORDER BY 1;
OBJECT_TYPE HOW_MANY
------------------- ----------
CLUSTER 26010
CONSUMER GROUP 65025
CONTEXT 18207
DESTINATION 5202
DIRECTORY 10404
EDITION 2601
EVALUATION CONTEXT 36414
FUNCTION 775098
INDEX 9501453
INDEX PARTITION 6825024
INDEXTYPE 23409
JAVA CLASS 58936059
JAVA DATA 845325
JAVA RESOURCE 2177037
JAVA SOURCE 5202
JOB 36414
JOB CLASS 36414
LIBRARY 457776
LOB 2447541
LOB PARTITION 26010
MATERIALIZED VIEW 2601
OPERATOR 143055
PACKAGE 3149811
PACKAGE BODY 3006756
PROCEDURE 384948
PROGRAM 49419
QUEUE 104040
RESOURCE PLAN 26010
RULE 18207
RULE SET 78030
SCHEDULE 7803
SCHEDULER GROUP 10404
SEQUENCE 501993
SYNONYM 70708185
TABLE 6908256
TABLE PARTITION 6476490
TABLE SUBPARTITION 2663424
TRIGGER 1417545
TYPE 7230780
TYPE BODY 590427
UNDEFINED 28611
VIEW 12385962
WINDOW 23409
XML SCHEMA 135252
44 rows selected.
Elapsed: 00:01:50.09
SQL> SELECT name, value
2 FROM v$mystat, v$statname
3 WHERE v$mystat.statistic#=v$statname.statistic#
4 AND name IN ('consistent gets',
5 'db block gets','cleanouts and rollbacks - consistent read gets',
6 'cleanouts only - consistent read gets')
7 ORDER BY 1;
NAME VALUE
------------------------------------------------ ----------
cleanouts and rollbacks - consistent read gets 0
cleanouts only - consistent read gets 100
consistent gets 103044
db block gets 5
2 FROM my_objects a, my_objects b
3 WHERE a.owner=b.owner AND a.object_name=b.object_name
4 AND a.object_type=b.object_type
5 GROUP BY a.object_type
6 ORDER BY 1;
OBJECT_TYPE HOW_MANY
------------------- ----------
CLUSTER 26010
CONSUMER GROUP 65025
CONTEXT 18207
DESTINATION 5202
DIRECTORY 10404
EDITION 2601
EVALUATION CONTEXT 36414
FUNCTION 775098
INDEX 9501453
INDEX PARTITION 6825024
INDEXTYPE 23409
JAVA CLASS 58936059
JAVA DATA 845325
JAVA RESOURCE 2177037
JAVA SOURCE 5202
JOB 36414
JOB CLASS 36414
LIBRARY 457776
LOB 2447541
LOB PARTITION 26010
MATERIALIZED VIEW 2601
OPERATOR 143055
PACKAGE 3149811
PACKAGE BODY 3006756
PROCEDURE 384948
PROGRAM 49419
QUEUE 104040
RESOURCE PLAN 26010
RULE 18207
RULE SET 78030
SCHEDULE 7803
SCHEDULER GROUP 10404
SEQUENCE 501993
SYNONYM 70708185
TABLE 6908256
TABLE PARTITION 6476490
TABLE SUBPARTITION 2663424
TRIGGER 1417545
TYPE 7230780
TYPE BODY 590427
UNDEFINED 28611
VIEW 12385962
WINDOW 23409
XML SCHEMA 135252
44 rows selected.
Elapsed: 00:01:50.09
SQL> SELECT name, value
2 FROM v$mystat, v$statname
3 WHERE v$mystat.statistic#=v$statname.statistic#
4 AND name IN ('consistent gets',
5 'db block gets','cleanouts and rollbacks - consistent read gets',
6 'cleanouts only - consistent read gets')
7 ORDER BY 1;
NAME VALUE
------------------------------------------------ ----------
cleanouts and rollbacks - consistent read gets 0
cleanouts only - consistent read gets 100
consistent gets 103044
db block gets 5
Most of the work was involved in providing consistent gets to the query. Oracle Database provides all the blocks from MY_OBJECTS as they were at the point in time at which the query was started, despite any modifications to the table which may follow.
The query is executed again, but while the query is running the second time, another session will be executing a series of updates to MY_OBJECTS.
SQL> UPDATE my_objects
2 SET object_type='SYNONYM'
3 WHERE object_type='JAVA CLASS';
1155609 rows updated.
Elapsed: 00:00:40.17
SQL> COMMIT;
Commit complete.
Elapsed: 00:00:00.01
SQL> UPDATE my_objects
2 SET object_type='JAVA CLASS'
3 WHERE object_type='VIEW';
242862 rows updated.
Elapsed: 00:00:25.97
SQL> COMMIT;
Commit complete.
Elapsed: 00:00:00.04
SQL> UPDATE my_objects
2 SET object_type='VIEW'
3 WHERE object_type IN ('TABLE','INDEX');
321759 rows updated.
Elapsed: 00:00:20.89
SQL> COMMIT;
Commit complete.
Elapsed: 00:00:00.01
SQL> UPDATE my_objects
2 SET object_type='SYNONYM'
3 WHERE object_type IN ('TYPE','TYPE BODY');
153051 rows updated.
Elapsed: 00:00:16.10
SQL> COMMIT;
Commit complete.
Elapsed: 00:00:00.00
SQL> UPDATE my_objects
2 SET object_type='JAVA CLASS'
3 WHERE object_type='SYNONYM';
2695095 rows updated.
Elapsed: 00:01:16.64
SQL> COMMIT;
Commit complete.
Elapsed: 00:00:00.02
2 SET object_type='SYNONYM'
3 WHERE object_type='JAVA CLASS';
1155609 rows updated.
Elapsed: 00:00:40.17
SQL> COMMIT;
Commit complete.
Elapsed: 00:00:00.01
SQL> UPDATE my_objects
2 SET object_type='JAVA CLASS'
3 WHERE object_type='VIEW';
242862 rows updated.
Elapsed: 00:00:25.97
SQL> COMMIT;
Commit complete.
Elapsed: 00:00:00.04
SQL> UPDATE my_objects
2 SET object_type='VIEW'
3 WHERE object_type IN ('TABLE','INDEX');
321759 rows updated.
Elapsed: 00:00:20.89
SQL> COMMIT;
Commit complete.
Elapsed: 00:00:00.01
SQL> UPDATE my_objects
2 SET object_type='SYNONYM'
3 WHERE object_type IN ('TYPE','TYPE BODY');
153051 rows updated.
Elapsed: 00:00:16.10
SQL> COMMIT;
Commit complete.
Elapsed: 00:00:00.00
SQL> UPDATE my_objects
2 SET object_type='JAVA CLASS'
3 WHERE object_type='SYNONYM';
2695095 rows updated.
Elapsed: 00:01:16.64
SQL> COMMIT;
Commit complete.
Elapsed: 00:00:00.02
Now see how this simultaneous DML affected the performance of the second execution of the query.
SQL> SELECT a.object_type, COUNT(*) HOW_MANY
2 FROM my_objects a, my_objects b
3 WHERE a.owner=b.owner AND a.object_name=b.object_name
4 AND a.object_type=b.object_type
5 GROUP BY a.object_type
6 ORDER BY 1;
OBJECT_TYPE HOW_MANY
------------------- ----------
CLUSTER 26010
CONSUMER GROUP 65025
CONTEXT 18207
DESTINATION 5202
DIRECTORY 10404
EDITION 2601
EVALUATION CONTEXT 36414
.
.
.
WINDOW 23409
XML SCHEMA 135252
44 rows selected.
Elapsed: 00:03:19.35
NAME VALUE
------------------------------------------------ ----------
cleanouts and rollbacks - consistent read gets 19868
cleanouts only - consistent read gets 195
consistent gets 1645375
db block gets 5
2 FROM my_objects a, my_objects b
3 WHERE a.owner=b.owner AND a.object_name=b.object_name
4 AND a.object_type=b.object_type
5 GROUP BY a.object_type
6 ORDER BY 1;
OBJECT_TYPE HOW_MANY
------------------- ----------
CLUSTER 26010
CONSUMER GROUP 65025
CONTEXT 18207
DESTINATION 5202
DIRECTORY 10404
EDITION 2601
EVALUATION CONTEXT 36414
.
.
.
WINDOW 23409
XML SCHEMA 135252
44 rows selected.
Elapsed: 00:03:19.35
NAME VALUE
------------------------------------------------ ----------
cleanouts and rollbacks - consistent read gets 19868
cleanouts only - consistent read gets 195
consistent gets 1645375
db block gets 5
Instead of taking one minute and fifty seconds, this time the query took about three minutes and twenty seconds to complete. The query still required consistent gets in order to provide a read-consistent view of MY_OBJECTS. Calculating the difference between the value of 'consistent gets' now and the value following the first execution of the query shows 1.5 million vs 100,000 consistent blocks previously.
When data blocks are being modified as the query is executing, Oracle Database leverages the undo segments in order to provide earlier versions of the data block to the query. Recall that many of the blocks within MY_OBJECTS were modified more than one time, while the query was executed. Each time that a block is modified, Oracle Database creates a copy of the original contents of the block within the undo segment of the transaction which is making the change and enters a pointer within the current version of the block to this copy. When a block is modified several times, Oracle Database will, in effect, create a chain of blocks, providing the ability to follow the chain backwards in order to examine the contents of the block after each change. These blocks may reside in either the database buffer cache or on disk.
In order for the query against MY_OBJECTS to provide a read-consistent view, Oracle Database examines the most recent version of the data block. It may find that it is too recent to satisfy the point in time at which the query started and locates the next-to-latest version of the block within the undo segments. If this next-to-latest version is not old enough, then it repeats this process until it locates the age-appropriate version to cause the query to be read-consistent. This "peeling back" of the versions of the data block is reflected in the statistic, 'cleanouts and rollbacks - consistent read gets'. Notice that the second execution of the query required almost 20,000 block version rollbacks, whereas the first execution required none. While the server process is performing rollbacks, it also uses this time to perform another housekeeping task called delayed block cleanout.
If an index is created in an attempt to speed up the response time of a query written, it still won't be able to avoid the creation of multiple versions of a block, except that this time, Oracle Database will need to not only access the table blocks but also the index blocks. An index is only appropriate for a table at a certain point in time. Therefore, every update will require housekeeping against the indexes, causing the updates to take more time to complete.
SOLUTION
The activity described above is normal and necessary activity required for Oracle Database to maintain consistency of data. The only real way to solve this issue is one of following options:
- Schedule DML activity separately from reporting activity.
- If slight staleness of the data may be tolerated, then perform reporting activity upon a materialized view or upon a read-only copy of the production database which may be refreshed on a routine schedule.
- Execute reports against a Data Guard Standby Database opened in read-only mode.
=================================================================
Note---> This informationmation taken from oracle metalink. all copy rights oracle only.
Comments
Post a Comment
Oracle DBA Information