Home » RDBMS Server » Performance Tuning » Oracle 11g alternates between two execution plans (11g Linux)
Oracle 11g alternates between two execution plans [message #549739] Tue, 03 April 2012 07:04 Go to next message
viktordt
Messages: 2
Registered: April 2012
Junior Member
Hello.

We have a query which makes Oracle behave very strangely. It is a straight-forward join between four tables of about 30.000 rows each, with some simple comparisons and some NOT LIKE:s.

When we run this query, it either takes about 1 second or more than 1.000 seconds to run and return the approximately 5.000 rows of the result. If we run the same query over and over again, it fluctuates back and forth between two different execution plans, apparently at random, 3 times out of 4 selecting the 1.000 second version and 1 time out of 4 the 1 second version.

There are no other connections to the database, the schema is not modified, the data is identical, the query is identical, and the response is identical, but the execution time alternates between 1 second and 1.000 seconds.

On the same database instance we have another schema which is identical, but with slightly less data, which is used for development. The 1.000 second run times did not happen in that schema, but only in the test system's database.

Therefore we would REALLY like to understand what happens and why, so that we can avoid triggering this in the future. We could try locking the 1 second execution plan, but then we're afraid of doing the same thing wrong again in the future.

Here are the two execution plans that Oracle switches between, more or less at random:

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
      5455       5455       5455  HASH JOIN  (cr=15663 pr=10536 pw=0 time=855673 us cost=82273 size=2707430769293 card=14028138701)
     79272      79272      79272   TABLE ACCESS FULL GROUPS (cr=1008 pr=0 pw=0 time=22154 us cost=277 size=10693 card=289)
    385836     385836     385836   HASH JOIN  (cr=14655 pr=10536 pw=0 time=1019103 us cost=15581 size=334082015904 card=2141551384)
     36817      36817      36817    HASH JOIN  (cr=10766 pr=10536 pw=0 time=203303 us cost=2921 size=1584289 card=17801)
      6540       6540       6540     TABLE ACCESS FULL ACCOUNT (cr=630 pr=479 pw=0 time=19492 us cost=173 size=111180 card=6540)
    163407     163407     163407     INDEX FAST FULL SCAN IDX_GROUP_MEMBERS (cr=10136 pr=10057 pw=0 time=1285634 us cost=2747 size=14434848 card=200484)(object id 118047)
    375411     375411     375411    TABLE ACCESS FULL GROUP_GROUPS_FLAT (cr=3889 pr=0 pw=0 time=58535 us cost=1029 size=25152738 card=375414)


Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
      5455       5455       5455  HASH JOIN  (cr=15664 pr=0 pw=0 time=778178696 us cost=30838477 size=741611997206725 card=3842549208325)
    375411     375411     375411   TABLE ACCESS FULL GROUP_GROUPS_FLAT (cr=3782 pr=0 pw=0 time=51533 us cost=1029 size=25152738 card=375414)
2918557224   55245693 2918557224   HASH JOIN  (cr=11882 pr=0 pw=0 time=778554141 us cost=3430847 size=177798827178 card=1411101803)
    163407     163407     163407    INDEX FAST FULL SCAN IDX_GROUP_MEMBERS (cr=10136 pr=0 pw=0 time=148427 us cost=2747 size=14434848 card=200484)(object id 118047)
 518438880  518438880  518438880    MERGE JOIN CARTESIAN (cr=1746 pr=0 pw=0 time=142455288 us cost=1800499 size=27995699520 card=518438880)
      6540       6540       6540     TABLE ACCESS FULL ACCOUNT (cr=738 pr=0 pw=0 time=49324 us cost=173 size=111180 card=6540)
 518438880  518438880  518438880     BUFFER SORT (cr=1008 pr=0 pw=0 time=69843749 us cost=1800326 size=2933064 card=79272)
     79272      79272      79272      TABLE ACCESS FULL GROUPS (cr=1008 pr=0 pw=0 time=25244 us cost=275 size=2933064 card=79272)


The query:
    select g.ucid, a.ucid
    from account a, groups g, group_members gm, group_groups_flat ggf
    where a.ucid = gm.ucid_member
    and gm.ucid_group = ggf.ucid_member
    and ggf.ucid_group = g.ucid
    and a.status = 'active'
    and g.unix_gid is not null
    and gm.valid_from <= sysdate
    and gm.valid_to >= sysdate
    and g.ucid not like '$_%' escape '$'
    and g.ucid not like 's$_%' escape '$'


And excerpts from the schema:
CREATE TABLE "PDB"."GROUPS"
(
  "UCID"        VARCHAR2(256 BYTE),    
  "UNIX_GID"    NUMBER(*,0),
  [...]

  PRIMARY KEY ("UCID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 3145728 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "PDB" ENABLE,

  CONSTRAINT "GN_FK" FOREIGN KEY ("UCID") REFERENCES "PDB"."NAMESPACE" ("UCID") ENABLE
)
CREATE TABLE "PDB"."ACCOUNT"
(
  "UCID"           VARCHAR2(256 BYTE),
  "STATUS"         VARCHAR2(10 BYTE) NOT NULL ENABLE,
  [...]

  PRIMARY KEY ("UCID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 2097152 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "PDB" ENABLE,

  FOREIGN KEY ("STATUS") REFERENCES "PDB"."ACCOUNT_STATUS" ("STATUS") ENABLE,
  CONSTRAINT "AN_FK" FOREIGN KEY ("UCID") REFERENCES "PDB"."NAMESPACE" ("UCID") ENABLE,
)
CREATE TABLE "PDB"."GROUP_MEMBERS"
(
  "UCID_GROUP"  VARCHAR2(256 BYTE) NOT NULL ENABLE,
  "UCID_MEMBER" VARCHAR2(256 BYTE) NOT NULL ENABLE,
  "VALID_FROM" DATE NOT NULL ENABLE,
  "VALID_TO" DATE NOT NULL ENABLE,
  CONSTRAINT "GROUP_MEMBERS_GROUPS_FK1" FOREIGN KEY ("UCID_GROUP") REFERENCES "PDB"."GROUPS" ("UCID") ENABLE,
  CONSTRAINT "GROUP_MEMBERS_MEMBER_FK1" FOREIGN KEY ("UCID_MEMBER") REFERENCES "PDB"."ACCOUNT" ("UCID") ENABLE
)
CREATE INDEX "PDB"."IDX_GROUP_MEMBERS_FROM" ON "PDB"."GROUP_MEMBERS"("VALID_FROM")
CREATE INDEX "PDB"."IDX_GROUP_MEMBERS_TO" ON "PDB"."GROUP_MEMBERS"("VALID_TO")
CREATE TABLE "PDB"."GROUP_GROUPS_FLAT"
(
  "UCID_GROUP"  VARCHAR2(256 BYTE),
  "UCID_MEMBER" VARCHAR2(256 BYTE),
  CONSTRAINT "GROUP_GROUPS_FLAT_GROUPS_FK1" FOREIGN KEY ("UCID_GROUP") REFERENCES "PDB"."GROUPS" ("UCID") ENABLE,
  CONSTRAINT "GROUP_GROUPS_FLAT_GROUPS_FK2" FOREIGN KEY ("UCID_MEMBER") REFERENCES "PDB"."GROUPS" ("UCID") ENABLE
)
CREATE INDEX "PDB"."IDX_GROUP_GROUPS_FLAT_GROUP" ON "PDB"."GROUP_GROUPS_FLAT("UCID_GROUP")
CREATE INDEX "PDB"."IDX_GROUP_GROUPS_FLAT_MEMBER" ON "PDB"."GROUP_GROUPS_FLAT("UCID_MEMBER")


Any and all ideas are welcome.
Re: Oracle 11g alternates between two execution plans [message #549740 is a reply to message #549739] Tue, 03 April 2012 07:13 Go to previous messageGo to next message
cookiemonster
Messages: 10852
Registered: September 2008
Location: Rainy Manchester
Senior Member
Are the table statistics up to date.
The card figures in both plans seem rather high for amount data you say you have.
Re: Oracle 11g alternates between two execution plans [message #549752 is a reply to message #549739] Tue, 03 April 2012 08:59 Go to previous messageGo to next message
LNossov
Messages: 284
Registered: July 2011
Location: Germany
Senior Member
At the first execution Oracle estimated cardinality of GROUPS as 289. At the second one as 79272 (i.e. correctly). May be because of cardinality feedback. Could you please verify this.

You should also check, if you have histograms for the columns gm.ucid_group and ggf.ucid_member.
Re: Oracle 11g alternates between two execution plans [message #550023 is a reply to message #549752] Thu, 05 April 2012 05:46 Go to previous messageGo to next message
viktordt
Messages: 2
Registered: April 2012
Junior Member
Cardinality feedback does indeed seem to be the cause of the altering. If i turn it off for the session, the execution always takes 1 second. Thank you for spotting this!

The question then becomes why Oracle, with the wildly inaccurate guessed cardinality of 289, generates a plan that is 1.000 times _quicker_ than it does when it knows the _actual_ cardinality of the table. How come the CBO generates a 1.000 times slower plan when it gets all the facts...?

Re: Oracle 11g alternates between two execution plans [message #550031 is a reply to message #550023] Thu, 05 April 2012 06:25 Go to previous message
LNossov
Messages: 284
Registered: July 2011
Location: Germany
Senior Member
I asked already, if you have histograms for the columns gm.ucid_group and ggf.ucid_member. Check it please.
Previous Topic: ora - 04031
Next Topic: Query tuning - index suggestion
Goto Forum:
  


Current Time: Tue Jul 29 09:27:32 CDT 2014

Total time taken to generate the page: 0.15056 seconds