Execution plan changes back and forth
Posted by: John Scott
Date: May 26, 2010 02:46PM
Date: May 26, 2010 02:46PM
Hello.
Our application issues a rather long query. Recently, and for no
apparent reason, peformance on this query has been wildly variable.
Usually the query returns in about half a second. Then, at random
times, the same query begins to take 25 seconds or more. After a while,
it reverts back to half a second. At first we thought it was a server
load issue, but then we discovered that the execution plan during the
slow times is much different from the plan during normal times.
There have been no ANALYZE TABLE commands run during this period. Is
there some randomness built into the optimizer? I am at a loss to
explain why the execution plan changes like this.
As a last resort, we will patch the application to use straight_join and
USE INDEX hints, but this is expensive in terms of our deployment change
control process.
Any help in understanding what is happening here would be greatly
appreciated.
The plans and query are below.
Our application issues a rather long query. Recently, and for no
apparent reason, peformance on this query has been wildly variable.
Usually the query returns in about half a second. Then, at random
times, the same query begins to take 25 seconds or more. After a while,
it reverts back to half a second. At first we thought it was a server
load issue, but then we discovered that the execution plan during the
slow times is much different from the plan during normal times.
There have been no ANALYZE TABLE commands run during this period. Is
there some randomness built into the optimizer? I am at a loss to
explain why the execution plan changes like this.
As a last resort, we will patch the application to use straight_join and
USE INDEX hints, but this is expensive in terms of our deployment change
control process.
Any help in understanding what is happening here would be greatly
appreciated.
The plans and query are below.
FAST EXECUTION PLAN: +----+--------------------+------------+-----------------+--------------------------------------------------------------------------------------------------------------+------------------------------------------------------------+---------+------------------------------------------+-------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+------------+-----------------+--------------------------------------------------------------------------------------------------------------+------------------------------------------------------------+---------+------------------------------------------+-------+--------------------------+ | 1 | PRIMARY | samples0_ | ref | biomaterial_experiment_fk | biomaterial_experiment_fk | 8 | const | 76 | Using where | | 1 | PRIMARY | term1_ | eq_ref | PRIMARY | PRIMARY | 8 | caarraydbprod.samples0_.MATERIAL_TYPE | 1 | | | 1 | PRIMARY | term2_ | eq_ref | PRIMARY | PRIMARY | 8 | caarraydbprod.samples0_.TISSUE_SITE | 1 | | | 1 | PRIMARY | term3_ | eq_ref | PRIMARY | PRIMARY | 8 | caarraydbprod.samples0_.CELL_TYPE | 1 | | | 1 | PRIMARY | term4_ | eq_ref | PRIMARY | PRIMARY | 8 | caarraydbprod.samples0_.DISEASE_STATE | 1 | | | 1 | PRIMARY | organism5_ | eq_ref | PRIMARY | PRIMARY | 8 | caarraydbprod.samples0_.ORGANISM | 1 | | | 1 | PRIMARY | term6_ | eq_ref | PRIMARY | PRIMARY | 8 | caarraydbprod.samples0_.LABEL | 1 | | | 2 | DEPENDENT SUBQUERY | s | unique_subquery | PRIMARY | PRIMARY | 8 | func | 1 | Using where | | 3 | DEPENDENT SUBQUERY | <derived4> | ALL | NULL | NULL | NULL | NULL | 13964 | Using where | | 4 | DERIVED | u | const | PRIMARY,UQ_LOGIN_NAME | UQ_LOGIN_NAME | 503 | | 1 | Using index | | 4 | DERIVED | p | const | PRIMARY,UQ_PRIVILEGE_NAME | UQ_PRIVILEGE_NAME | 102 | | 1 | Using index | | 4 | DERIVED | rp | ref | UQ_ROLE_PRIVILEGE_ROLE_ID,idx_PRIVILEGE_ID,idx_ROLE_ID | UQ_ROLE_PRIVILEGE_ROLE_ID | 8 | const | 1 | Using index | | 4 | DERIVED | ug | ref | idx_USER_ID,idx_GROUP_ID | idx_USER_ID | 8 | const | 3 | | | 4 | DERIVED | r | eq_ref | PRIMARY | PRIMARY | 8 | caarraydbprod.rp.ROLE_ID | 1 | Using index | | 4 | DERIVED | ugrpg | ref | idx_GROUP_ID,idx_ROLE_ID,idx_PROTECTION_GROUP_ID | idx_GROUP_ID | 9 | caarraydbprod.ug.GROUP_ID | 2164 | Using where | | 4 | DERIVED | pg | eq_ref | PRIMARY | PRIMARY | 8 | caarraydbprod.ugrpg.PROTECTION_GROUP_ID | 1 | Using index | | 4 | DERIVED | pgpe | ref | UQ_PROTECTION_GROUP_PROTECTION_ELEMENT_PROTECTION_GROUP_ID,idx_PROTECTION_ELEMENT_ID,idx_PROTECTION_GROUP_ID | idx_PROTECTION_GROUP_ID | 8 | caarraydbprod.ugrpg.PROTECTION_GROUP_ID | 1 | | | 4 | DERIVED | pe | eq_ref | PRIMARY,UQ_PE_PE_NAME_ATTRIBUTE_VALUE_APP_ID,idx_APPLICATION_ID | PRIMARY | 8 | caarraydbprod.pgpe.PROTECTION_ELEMENT_ID | 1 | Using where | +----+--------------------+------------+-----------------+--------------------------------------------------------------------------------------------------------------+------------------------------------------------------------+---------+------------------------------------------+-------+--------------------------+ SLOW EXECUTION PLAN: +----+--------------------+------------+-----------------+--------------------------------------------------------------------------------------------------------------+------------------------------------------------------------+---------+------------------------------------------+-------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+------------+-----------------+--------------------------------------------------------------------------------------------------------------+------------------------------------------------------------+---------+------------------------------------------+-------+--------------------------+ | 1 | PRIMARY | samples0_ | ref | biomaterial_experiment_fk | biomaterial_experiment_fk | 8 | const | 76 | Using where | | 1 | PRIMARY | term1_ | eq_ref | PRIMARY | PRIMARY | 8 | caarraydbprod.samples0_.MATERIAL_TYPE | 1 | | | 1 | PRIMARY | term2_ | eq_ref | PRIMARY | PRIMARY | 8 | caarraydbprod.samples0_.TISSUE_SITE | 1 | | | 1 | PRIMARY | term3_ | eq_ref | PRIMARY | PRIMARY | 8 | caarraydbprod.samples0_.CELL_TYPE | 1 | | | 1 | PRIMARY | term4_ | eq_ref | PRIMARY | PRIMARY | 8 | caarraydbprod.samples0_.DISEASE_STATE | 1 | | | 1 | PRIMARY | organism5_ | eq_ref | PRIMARY | PRIMARY | 8 | caarraydbprod.samples0_.ORGANISM | 1 | | | 1 | PRIMARY | term6_ | eq_ref | PRIMARY | PRIMARY | 8 | caarraydbprod.samples0_.LABEL | 1 | | | 2 | DEPENDENT SUBQUERY | s | unique_subquery | PRIMARY | PRIMARY | 8 | func | 1 | Using where | | 3 | DEPENDENT SUBQUERY | <derived4> | ALL | NULL | NULL | NULL | NULL | 13964 | Using where | | 4 | DERIVED | u | const | PRIMARY,UQ_LOGIN_NAME | UQ_LOGIN_NAME | 503 | | 1 | Using index | | 4 | DERIVED | p | const | PRIMARY,UQ_PRIVILEGE_NAME | UQ_PRIVILEGE_NAME | 102 | | 1 | Using index | | 4 | DERIVED | rp | ref | UQ_ROLE_PRIVILEGE_ROLE_ID,idx_PRIVILEGE_ID,idx_ROLE_ID | UQ_ROLE_PRIVILEGE_ROLE_ID | 8 | const | 1 | Using index | | 4 | DERIVED | r | eq_ref | PRIMARY | PRIMARY | 8 | caarraydbprod.rp.ROLE_ID | 1 | Using index | | 4 | DERIVED | pe | ref | PRIMARY,UQ_PE_PE_NAME_ATTRIBUTE_VALUE_APP_ID,idx_APPLICATION_ID | UQ_PE_PE_NAME_ATTRIBUTE_VALUE_APP_ID | 205 | | 13100 | Using where; Using index | | 4 | DERIVED | pgpe | ref | UQ_PROTECTION_GROUP_PROTECTION_ELEMENT_PROTECTION_GROUP_ID,idx_PROTECTION_ELEMENT_ID,idx_PROTECTION_GROUP_ID | UQ_PROTECTION_GROUP_PROTECTION_ELEMENT_PROTECTION_GROUP_ID | 8 | caarraydbprod.pe.PROTECTION_ELEMENT_ID | 1 | Using index | | 4 | DERIVED | pg | eq_ref | PRIMARY | PRIMARY | 8 | caarraydbprod.pgpe.PROTECTION_GROUP_ID | 1 | Using index | | 4 | DERIVED | ugrpg | ref | idx_GROUP_ID,idx_ROLE_ID,idx_PROTECTION_GROUP_ID | idx_PROTECTION_GROUP_ID | 8 | caarraydbprod.pgpe.PROTECTION_GROUP_ID | 2 | Using where | | 4 | DERIVED | ug | ref | idx_USER_ID,idx_GROUP_ID | idx_GROUP_ID | 8 | caarraydbprod.ugrpg.GROUP_ID | 1 | Using where | +----+--------------------+------------+-----------------+--------------------------------------------------------------------------------------------------------------+------------------------------------------------------------+---------+----------------------------------------+-------+--------------------------+ QUERY: select [...LARGE SELECT LIST DELETED...] from biomaterial samples0_ left outer join term term1_ on samples0_.material_type=term1_.id left outer join term term2_ on samples0_.tissue_site=term2_.id left outer join term term3_ on samples0_.cell_type=term3_.id left outer join term term4_ on samples0_.disease_state=term4_.id left outer join organism organism5_ on samples0_.organism=organism5_.id left outer join term term6_ on samples0_.label=term6_.id where samples0_.id in ( select s.ID from biomaterial s where s.discriminator = 'SA' and s.ID in ( select __caarray_filter_alias__.attribute_value from ( select pe.attribute_value from csm_protection_group pg, csm_protection_element pe, csm_pg_pe pgpe, csm_user_group_role_pg ugrpg, csm_user u, csm_role_privilege rp, csm_role r, csm_privilege p, csm_user_group ug where pe.object_id= 'domain.sample.Sample' and pe.attribute='id' and u.login_name='caarrayuser' and pe.application_id=2 and ugrpg.role_id=r.role_id and ugrpg.group_id = ug.group_id and ug.user_id = u.user_id and ugrpg.protection_group_id = pg.protection_group_id and pg.protection_group_id = pgpe.protection_group_id and pgpe.protection_element_id = pe.protection_element_id and r.role_id = rp.role_id and rp.privilege_id = p.privilege_id and p.privilege_name='READ' ) __caarray_filter_alias__ ) ) and samples0_.discriminator = 'SA' and samples0_.experiment_id=62
Subject
Views
Written By
Posted
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.