| Oracle 11g alternates between two execution plans [message #549739] |
Tue, 03 April 2012 07:04  |
 |
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.
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
