MySQL Forums
Forum List  »  Optimizer & Parser

Execution plan changes back and forth
Posted by: John Scott
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.

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Execution plan changes back and forth
2989
May 26, 2010 02:46PM


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.