| Query performance diff [message #528520] |
Tue, 25 October 2011 09:01  |
Vackar
Messages: 81
Registered: October 2007
|
Member |
|
|
HI,
I have a dev schema and a test schema, adn I have a problematic query.
When it runs on DEV, it's fast, however when it runs on test it is unbelievably slow.
The test schema has a tiny amount of data in it in comparison to the DEV schema, however both schemas join to a common third schema with fairly large tables (about 100-150 million rows)
I've noticed that the execution plans for the query are quite different for each schema. I think the reason it's slow is that it's doing a full table scan on UNIPROT_ENTRY_INFO, but regardless of which hints I specify it doesn't seem to want to the index on the UNIPROT_ENTRY_INFO table.
Can anyone offer any advice?
The query I'm running is:
SELECT
intensity.resultset_id AS intensity_resultset_id, intensity.externalid AS "proteinGroupExternalId",
intensity.raw_entity_id AS "rawEntity_id", intensity.experimentlabel AS label,
intensity.intensity AS intensity, intensity.gel_slice AS "gelSlice",
intensity.contaminant AS contaminant, intensity.reverse AS reverse,
intensity.pep AS pep, uniprot_entry_info.short_name AS gene,
uniprot.entry_name AS uniprot_id, uniprot_entry_info.entry_description AS uniprot_description,
rep.othersequencecoverage AS "sequenceCoverage", rep.otherpeptidecount AS "peptideCount",
protein.currentipi AS protein, hl.ratio AS "ratioHL", hl.normalised_ratio AS "normalisedRatioHL",
hl.significancea AS "significanceA_HL", hl.significanceb AS "significanceB_HL",
il.intensity AS "intensityLight", ih.intensity AS "intensityHeavy"
FROM
raw_entity_intensity_props intensity,
uniprot.uniprot_entry_info uniprot_entry_info,
uniprot.uniprot_entry uniprot,
protms_rawentityprotein rep,
protms_protein protein,
raw_entity_hl_props hl,
raw_entity_intensity_l_props il,
raw_entity_intensity_h_props ih,
protms_rawentitymapping map
WHERE
uniprot_entry_info.uniprot_entry_id = uniprot.id AND
map.accession = uniprot.primary_accession AND
rep.rawentity_id = intensity.raw_entity_id AND
hl.raw_entity_id = intensity.raw_entity_id AND
hl.resultset_id = intensity.resultset_id AND
il.raw_entity_id = intensity.raw_entity_id AND
il.resultset_id = intensity.resultset_id AND
ih.raw_entity_id = intensity.raw_entity_id AND
ih.resultset_id = intensity.resultset_id AND
nvl(hl.experimentlabel, 1) = nvl(intensity.experimentlabel, 1) AND
nvl(il.experimentlabel, 1) = nvl(intensity.experimentlabel, 1) AND
nvl(ih.experimentlabel, 1) = nvl(intensity.experimentlabel, 1) AND
map.rawentity_id = intensity.raw_entity_id AND
uniprot_entry_info.recommended = 1 AND
rep.protein_id = protein.id AND rep.leadingprotein = 1 AND
map.leading_protein = 1 AND
intensity.resultset_id = 3 ;
and the explain plan for the fast query is:
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3045027228
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 247 |
| 1 | NESTED LOOPS | | 1 | 247 |
| 2 | NESTED LOOPS | | 1 | 235 |
| 3 | NESTED LOOPS | | 1 | 214 |
| 4 | NESTED LOOPS | | 1 | 196 |
| 5 | NESTED LOOPS | | 1 | 170 |
| 6 | NESTED LOOPS | | 1 | 153 |
| 7 | NESTED LOOPS | | 1 | 133 |
| 8 | NESTED LOOPS | | 78 | 8814 |
| 9 | NESTED LOOPS | | 30510 | 2770K|
|* 10 | HASH JOIN | | 26420 | 1728K|
| 11 | TABLE ACCESS BY INDEX ROWID | PROTMS_RAWENTITY | 26423 | 206K|
|* 12 | INDEX RANGE SCAN | PROTMS_RAWENTITY_RESULTSET_ID | 26423 | |
|* 13 | HASH JOIN | | 26421 | 1522K|
| 14 | TABLE ACCESS BY INDEX ROWID | PROTMS_RAWENTITY | 26423 | 206K|
|* 15 | INDEX RANGE SCAN | PROTMS_RAWENTITY_RESULTSET_ID | 26423 | |
|* 16 | HASH JOIN | | 26422 | 1315K|
| 17 | NESTED LOOPS | | 26423 | 619K|
| 18 | NESTED LOOPS | | 1 | 16 |
| 19 | NESTED LOOPS | | 1 | 12 |
| 20 | NESTED LOOPS | | 1 | 8 |
|* 21 | INDEX UNIQUE SCAN | PRIMARY_60 | 1 | 4 |
|* 22 | INDEX UNIQUE SCAN | PRIMARY_60 | 1 | 4 |
|* 23 | INDEX UNIQUE SCAN | PRIMARY_60 | 1 | 4 |
|* 24 | INDEX UNIQUE SCAN | PRIMARY_60 | 1 | 4 |
| 25 | TABLE ACCESS BY INDEX ROWID| PROTMS_RAWENTITY | 26423 | 206K|
|* 26 | INDEX RANGE SCAN | PROTMS_RAWENTITY_RESULTSET_ID | 26423 | |
| 27 | TABLE ACCESS BY INDEX ROWID | PROTMS_RAWENTITY | 26423 | 696K|
|* 28 | INDEX RANGE SCAN | PROTMS_RAWENTITY_RESULTSET_ID | 26423 | |
|* 29 | TABLE ACCESS BY INDEX ROWID | PROTMS_RAWQUANTIFICATION | 1 | 26 |
|* 30 | INDEX RANGE SCAN | PROTMS_RAWQUANTIFICATION_RAWEN | 14 | |
|* 31 | TABLE ACCESS BY INDEX ROWID | PROTMS_RAWQUANTIFICATION | 1 | 20 |
|* 32 | INDEX RANGE SCAN | PROTMS_RAWQUANTIFICATION_RAWEN | 14 | |
|* 33 | TABLE ACCESS BY INDEX ROWID | PROTMS_RAWQUANTIFICATION | 1 | 20 |
|* 34 | INDEX RANGE SCAN | PROTMS_RAWQUANTIFICATION_RAWEN | 14 | |
|* 35 | TABLE ACCESS BY INDEX ROWID | PROTMS_RAWQUANTIFICATION | 1 | 20 |
|* 36 | INDEX RANGE SCAN | PROTMS_RAWQUANTIFICATION_RAWEN | 14 | |
|* 37 | TABLE ACCESS BY INDEX ROWID | PROTMS_RAWENTITYMAPPING | 2 | 34 |
|* 38 | INDEX RANGE SCAN | RAWENTITY_ID | 7 | |
| 39 | TABLE ACCESS BY INDEX ROWID | UNIPROT_ENTRY | 1 | 26 |
|* 40 | INDEX RANGE SCAN | IDX_UNIPROT_ENTRY_ACCESSION | 1 | |
|* 41 | TABLE ACCESS BY INDEX ROWID | UNIPROT_ENTRY_INFO | 1 | 18 |
|* 42 | INDEX RANGE SCAN | IDX_FK_UNIPROT_ENTRY_INFO | 1 | |
|* 43 | TABLE ACCESS BY INDEX ROWID | PROTMS_RAWENTITYPROTEIN | 1 | 21 |
|* 44 | INDEX RANGE SCAN | PROTMS_RAWENTITYPROTEIN_RAWENT | 3 | |
| 45 | TABLE ACCESS BY INDEX ROWID | PROTMS_PROTEIN | 1 | 12 |
|* 46 | INDEX UNIQUE SCAN | PRIMARY_50 | 1 | |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
10 - access("RAW_ENT"."ID"="RAW_ENT"."ID")
12 - access("RAW_ENT"."RESULTSET_ID"=3)
13 - access("RAW_ENT"."ID"="RAW_ENT"."ID")
15 - access("RAW_ENT"."RESULTSET_ID"=3)
16 - access("RAW_ENT"."ID"="RAW_ENT"."ID")
21 - access("RESULTSET"."ID"=3)
22 - access("RESULTSET"."ID"=3)
23 - access("RESULTSET"."ID"=3)
24 - access("RESULTSET"."ID"=3)
26 - access("RAW_ENT"."RESULTSET_ID"=3)
28 - access("RAW_ENT"."RESULTSET_ID"=3)
29 - filter("LABEL" IS NULL AND "LABEL1"=2 AND "LABEL2"=0)
30 - access("RAW_ENT"."ID"="RAW_QUANT"."RAWENTITY_ID")
31 - filter("LABEL" IS NULL AND "LABEL1" IS NULL AND "LABEL2" IS NULL AND
NVL("RAW_QUANT"."EXPERIMENTLABEL",'1')=NVL("RAW_QUANT"."EXPERIMENTLABEL",'1'))
32 - access("RAW_ENT"."ID"="RAW_QUANT"."RAWENTITY_ID")
33 - filter("LABEL1" IS NULL AND "LABEL2" IS NULL AND "LABEL"=0 AND
NVL("RAW_QUANT"."EXPERIMENTLABEL",'1')=NVL("RAW_QUANT"."EXPERIMENTLABEL",'1'))
34 - access("RAW_ENT"."ID"="RAW_QUANT"."RAWENTITY_ID")
35 - filter("LABEL1" IS NULL AND "LABEL2" IS NULL AND "LABEL"=2 AND
NVL("RAW_QUANT"."EXPERIMENTLABEL",'1')=NVL("RAW_QUANT"."EXPERIMENTLABEL",'1'))
36 - access("RAW_ENT"."ID"="RAW_QUANT"."RAWENTITY_ID")
37 - filter("MAP"."LEADING_PROTEIN"=1)
38 - access("MAP"."RAWENTITY_ID"="RAW_ENT"."ID")
40 - access("MAP"."ACCESSION"="UNIPROT"."PRIMARY_ACCESSION")
41 - filter("UNIPROT_ENTRY_INFO"."RECOMMENDED"=1)
42 - access("UNIPROT_ENTRY_INFO"."UNIPROT_ENTRY_ID"="UNIPROT"."ID")
43 - filter("REP"."LEADINGPROTEIN"=1)
44 - access("REP"."RAWENTITY_ID"="RAW_ENT"."ID")
46 - access("REP"."PROTEIN_ID"="PROTEIN"."ID")
and the slow query is:
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 4171728938
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cos
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 980 | 163
| 1 | NESTED LOOPS | | 1 | 980 | 163
| 2 | MERGE JOIN CARTESIAN | | 1 | 954 | 163
|* 3 | TABLE ACCESS BY INDEX ROWID | PROTMS_RAWQUANTIFICATION | 1 | 156 |
| 4 | NESTED LOOPS | | 1 | 936 | 1
| 5 | NESTED LOOPS | | 1 | 780 | 1
| 6 | NESTED LOOPS | | 1 | 663 | 1
| 7 | NESTED LOOPS | | 25 | 13650 | 1
| 8 | NESTED LOOPS | | 25 | 13000 | 1
| 9 | NESTED LOOPS | | 25 | 12350 | 1
| 10 | NESTED LOOPS | | 25 | 11700 | 1
|* 11 | HASH JOIN | | 29 | 10179 |
|* 12 | HASH JOIN | | 29 | 8294 |
|* 13 | TABLE ACCESS FULL | PROTMS_RAWENTITYPROTEIN | 581 | 37765 |
|* 14 | HASH JOIN | | 582 | 125K|
| 15 | NESTED LOOPS | | 582 | 75660 |
| 16 | NESTED LOOPS | | 1 | 52 |
| 17 | NESTED LOOPS | | 1 | 39 |
| 18 | NESTED LOOPS | | 1 | 26 |
|* 19 | INDEX UNIQUE SCAN | SYS_C002063048 | 1 | 13 |
|* 20 | INDEX UNIQUE SCAN | SYS_C002063048 | 1 | 13 |
|* 21 | INDEX UNIQUE SCAN | SYS_C002063048 | 1 | 13 |
|* 22 | INDEX UNIQUE SCAN | SYS_C002063048 | 1 | 13 |
|* 23 | TABLE ACCESS FULL | PROTMS_RAWENTITYMAPPING | 582 | 45396 |
|* 24 | TABLE ACCESS FULL | PROTMS_RAWENTITY | 11809 | 1049K|
| 25 | TABLE ACCESS FULL | PROTMS_PROTEIN | 2425 | 153K|
|* 26 | TABLE ACCESS BY INDEX ROWID| PROTMS_RAWQUANTIFICATION | 1 | 117 |
|* 27 | INDEX RANGE SCAN | PROTMS_RAWQUANTIFICATION_7E59B | 14 | |
|* 28 | TABLE ACCESS BY INDEX ROWID | PROTMS_RAWENTITY | 1 | 26 |
|* 29 | INDEX UNIQUE SCAN | SYS_C002063083 | 1 | |
|* 30 | TABLE ACCESS BY INDEX ROWID | PROTMS_RAWENTITY | 1 | 26 |
|* 31 | INDEX UNIQUE SCAN | SYS_C002063083 | 1 | |
|* 32 | TABLE ACCESS BY INDEX ROWID | PROTMS_RAWENTITY | 1 | 26 |
|* 33 | INDEX UNIQUE SCAN | SYS_C002063083 | 1 | |
|* 34 | TABLE ACCESS BY INDEX ROWID | PROTMS_RAWQUANTIFICATION | 1 | 117 |
|* 35 | INDEX RANGE SCAN | PROTMS_RAWQUANTIFICATION_7E59B | 14 | |
|* 36 | TABLE ACCESS BY INDEX ROWID | PROTMS_RAWQUANTIFICATION | 1 | 117 |
|* 37 | INDEX RANGE SCAN | PROTMS_RAWQUANTIFICATION_7E59B | 14 | |
|* 38 | INDEX RANGE SCAN | PROTMS_RAWQUANTIFICATION_7E59B | 14 | |
| 39 | BUFFER SORT | | 9075K| 155M| 163
|* 40 | TABLE ACCESS FULL | UNIPROT_ENTRY_INFO | 9075K| 155M| 161
|* 41 | TABLE ACCESS BY INDEX ROWID | UNIPROT_ENTRY | 1 | 26 |
|* 42 | INDEX UNIQUE SCAN | UNIPROT_ENTRY_PK1 | 1 | |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("LABEL" IS NULL AND "LABEL1"=2 AND "LABEL2"=0 AND
NVL("RAW_QUANT"."EXPERIMENTLABEL",U'1')=NVL("RAW_QUANT"."EXPERIMENTLABEL",U'1'))
11 - access("REP"."PROTEIN_ID"="PROTEIN"."ID")
12 - access("REP"."RAWENTITY_ID"="RAW_ENT"."ID")
13 - filter("REP"."LEADINGPROTEIN"=1)
14 - access("MAP"."RAWENTITY_ID"="RAW_ENT"."ID")
19 - access("RESULTSET"."ID"=3)
20 - access("RESULTSET"."ID"=3)
21 - access("RESULTSET"."ID"=3)
22 - access("RESULTSET"."ID"=3)
23 - filter("MAP"."LEADING_PROTEIN"=1)
24 - filter("RAW_ENT"."RESULTSET_ID"=3)
26 - filter("LABEL1" IS NULL AND "LABEL2" IS NULL AND "LABEL" IS NULL)
27 - access("RAW_ENT"."ID"="RAW_QUANT"."RAWENTITY_ID")
28 - filter("RAW_ENT"."RESULTSET_ID"=3)
29 - access("RAW_ENT"."ID"="RAW_ENT"."ID")
30 - filter("RAW_ENT"."RESULTSET_ID"=3)
31 - access("RAW_ENT"."ID"="RAW_ENT"."ID")
32 - filter("RAW_ENT"."RESULTSET_ID"=3)
33 - access("RAW_ENT"."ID"="RAW_ENT"."ID")
34 - filter("LABEL1" IS NULL AND "LABEL2" IS NULL AND "LABEL"=0 AND
NVL("RAW_QUANT"."EXPERIMENTLABEL",U'1')=NVL("RAW_QUANT"."EXPERIMENTLABEL",U'1'))
35 - access("RAW_ENT"."ID"="RAW_QUANT"."RAWENTITY_ID")
36 - filter("LABEL1" IS NULL AND "LABEL2" IS NULL AND "LABEL"=2 AND
NVL("RAW_QUANT"."EXPERIMENTLABEL",U'1')=NVL("RAW_QUANT"."EXPERIMENTLABEL",U'1'))
37 - access("RAW_ENT"."ID"="RAW_QUANT"."RAWENTITY_ID")
38 - access("RAW_ENT"."ID"="RAW_QUANT"."RAWENTITY_ID")
40 - filter("UNIPROT_ENTRY_INFO"."RECOMMENDED"=1)
41 - filter("MAP"."ACCESSION"=SYS_OP_C2C("UNIPROT"."PRIMARY_ACCESSION"))
42 - access("UNIPROT_ENTRY_INFO"."UNIPROT_ENTRY_ID"="UNIPROT"."ID")
Note
-----
- dynamic sampling used for this statement
Perhaps an additional observation would be that dynamic sampling was used in the sloq query, whereas not in the fast query.
Thanks in advance,
Vackar
CM: multi-lined the select. It's really hard to read if you have to scroll across like that.
[Updated on: Tue, 25 October 2011 09:17] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Query performance diff [message #528527 is a reply to message #528525] |
Tue, 25 October 2011 09:24  |
Vackar
Messages: 81
Registered: October 2007
|
Member |
|
|
Oh dear,
Quite a few rows returned:
TABLE_NAME COLUMN_NAME
------------------------------ ------------------------
AUTOGENERATEDPROTEINLIST ID
BIN$nqy3Wn+53cngQAAKEgdH9A==$0 FRACTIONRAWENTITY_ID
BIN$nqy3Wn+53cngQAAKEgdH9A==$0 PROTEINIDENTIFIER
BIN$nqy3Wn+53cngQAAKEgdH9A==$0 UNIPROT_ID
BIN$nqy3Wn+c3cngQAAKEgdH9A==$0 FRACTION
BIN$nqy3Wn+c3cngQAAKEgdH9A==$0 ID
BIN$nqy3Wn+c3cngQAAKEgdH9A==$0 PROTEINIDENTIFIER
BIN$nqy3Wn+j3cngQAAKEgdH9A==$0 PROTEINIDENTIFIER
BIN$nqy3Wn+j3cngQAAKEgdH9A==$0 SEQUENCE
BIN$nqy3Wn+q3cngQAAKEgdH9A==$0 PROTEINIDENTIFIER
BIN$nqy3Wn+q3cngQAAKEgdH9A==$0 SEQUENCE
BIN$nqy3Wn+w3cngQAAKEgdH9A==$0 FRACTION
BIN$nqy3Wn+w3cngQAAKEgdH9A==$0 FRACTIONRAWENTITY_ID
BIN$nqy3Wn/C3cngQAAKEgdH9A==$0 ID
BIN$nqy3Wn/C3cngQAAKEgdH9A==$0 PROTEINIDENTIFIER
BIN$nqy3Wn/I3cngQAAKEgdH9A==$0 UNIQUE_ID
BIN$nqy3Wn/R3cngQAAKEgdH9A==$0 FRACTION
BIN$nqy3Wn/R3cngQAAKEgdH9A==$0 ID
BIN$nqy3Wn/R3cngQAAKEgdH9A==$0 PROTEINIDENTIFIER
BIN$nqy3Wn/Y3cngQAAKEgdH9A==$0 FRACTION
BIN$nqy3Wn/Y3cngQAAKEgdH9A==$0 PROTEINIDENTIFIER
BIN$ov3/N9qZL0TgQAAKEgd7/A==$0 ID
BIN$ov3/N9qZL0TgQAAKEgd7/A==$0 REF_OBJECT_ID
BIN$ov3/N9qZL0TgQAAKEgd7/A==$0 SEVERITY
BIN$ov3/N9qkL0TgQAAKEgd7/A==$0 ID
BIN$ov3/N9rtL0TgQAAKEgd7/A==$0 DERIVED_CONNECTION_ID_FK
BIN$ov3/N9rtL0TgQAAKEgd7/A==$0 SRC_ID
BIN$ov3/N9t8L0TgQAAKEgd7/A==$0 SCHEMA_ID_FK
BIN$ov3/N9t8L0TgQAAKEgd7/A==$0 SYS_NC00015$
BIN$p9w+ewgX1V/gQAAKAyUuOA==$0 ID
BIN$prPai5cQyHHgQAAKAyV05Q==$0 EVIDENCE_ID
BIN$prPai5cQyHHgQAAKAyV05Q==$0 MOD_PEPTIDE_ID
BIN$qKWgU0g0wVTgQAAKAyUkiw==$0 EVIDENCE_ID
BIN$qKWgU0g0wVTgQAAKAyUkiw==$0 MOD_PEPTIDE_ID
BIN$qKa0uCqbI5HgQAAKAyVRdw==$0 EVIDENCE_ID
BIN$qKa0uCqbI5HgQAAKAyVRdw==$0 MOD_PEPTIDE_ID
BIN$qKa8gYXAtgjgQAAKAyVR9w==$0 EVIDENCE_ID
BIN$qKa8gYXAtgjgQAAKAyVR9w==$0 MOD_PEPTIDE_ID
BIN$qKbTLqI//djgQAAKAyVTWw==$0 EVIDENCE_ID
BIN$qKbTLqI//djgQAAKAyVTWw==$0 MOD_PEPTIDE_ID
BIN$qKbTLqI1/djgQAAKAyVTWw==$0 EVIDENCE_ID
BIN$qKbTLqI1/djgQAAKAyVTWw==$0 MOD_PEPTIDE_ID
BIN$qKbTLqI6/djgQAAKAyVTWw==$0 EVIDENCE_ID
BIN$qKbTLqI6/djgQAAKAyVTWw==$0 MOD_PEPTIDE_ID
BIN$qKbTLqIS/djgQAAKAyVTWw==$0 EVIDENCE_ID
BIN$qKbTLqIS/djgQAAKAyVTWw==$0 MOD_PEPTIDE_ID
BIN$qKbTLqIX/djgQAAKAyVTWw==$0 EVIDENCE_ID
BIN$qKbTLqIX/djgQAAKAyVTWw==$0 MOD_PEPTIDE_ID
BIN$qKbTLqIc/djgQAAKAyVTWw==$0 EVIDENCE_ID
BIN$qKbTLqIc/djgQAAKAyVTWw==$0 MOD_PEPTIDE_ID
BIN$qKbTLqIh/djgQAAKAyVTWw==$0 EVIDENCE_ID
BIN$qKbTLqIh/djgQAAKAyVTWw==$0 MOD_PEPTIDE_ID
BIN$qKbTLqIm/djgQAAKAyVTWw==$0 EVIDENCE_ID
BIN$qKbTLqIm/djgQAAKAyVTWw==$0 MOD_PEPTIDE_ID
BIN$qKbTLqIr/djgQAAKAyVTWw==$0 EVIDENCE_ID
BIN$qKbTLqIr/djgQAAKAyVTWw==$0 MOD_PEPTIDE_ID
BIN$qKbTLqIw/djgQAAKAyVTWw==$0 EVIDENCE_ID
BIN$qKbTLqIw/djgQAAKAyVTWw==$0 MOD_PEPTIDE_ID
BIN$qKbTLqJ2/djgQAAKAyVTWw==$0 EVIDENCE_ID
BIN$qKbTLqJ2/djgQAAKAyVTWw==$0 MOD_PEPTIDE_ID
BIN$qKbTLqJ7/djgQAAKAyVTWw==$0 EVIDENCE_ID
BIN$qKbTLqJ7/djgQAAKAyVTWw==$0 MOD_PEPTIDE_ID
BIN$qKbTLqJE/djgQAAKAyVTWw==$0 EVIDENCE_ID
BIN$qKbTLqJE/djgQAAKAyVTWw==$0 MOD_PEPTIDE_ID
BIN$qKbTLqJJ/djgQAAKAyVTWw==$0 EVIDENCE_ID
BIN$qKbTLqJJ/djgQAAKAyVTWw==$0 MOD_PEPTIDE_ID
BIN$qKbTLqJO/djgQAAKAyVTWw==$0 EVIDENCE_ID
BIN$qKbTLqJO/djgQAAKAyVTWw==$0 MOD_PEPTIDE_ID
BIN$qKbTLqJT/djgQAAKAyVTWw==$0 EVIDENCE_ID
BIN$qKbTLqJT/djgQAAKAyVTWw==$0 MOD_PEPTIDE_ID
BIN$qKbTLqJY/djgQAAKAyVTWw==$0 EVIDENCE_ID
BIN$qKbTLqJY/djgQAAKAyVTWw==$0 MOD_PEPTIDE_ID
BIN$qKbTLqJd/djgQAAKAyVTWw==$0 EVIDENCE_ID
BIN$qKbTLqJd/djgQAAKAyVTWw==$0 MOD_PEPTIDE_ID
BIN$qKbTLqJi/djgQAAKAyVTWw==$0 EVIDENCE_ID
BIN$qKbTLqJi/djgQAAKAyVTWw==$0 MOD_PEPTIDE_ID
BIN$qKbTLqJn/djgQAAKAyVTWw==$0 EVIDENCE_ID
BIN$qKbTLqJn/djgQAAKAyVTWw==$0 MOD_PEPTIDE_ID
BIN$qKbTLqJs/djgQAAKAyVTWw==$0 EVIDENCE_ID
BIN$qKbTLqJs/djgQAAKAyVTWw==$0 MOD_PEPTIDE_ID
BIN$qKbTLqJx/djgQAAKAyVTWw==$0 EVIDENCE_ID
BIN$qKbTLqJx/djgQAAKAyVTWw==$0 MOD_PEPTIDE_ID
BIN$qKbTLqKA/djgQAAKAyVTWw==$0 EVIDENCE_ID
BIN$qKbTLqKA/djgQAAKAyVTWw==$0 MOD_PEPTIDE_ID
BIN$qKbTLqKF/djgQAAKAyVTWw==$0 EVIDENCE_ID
BIN$qKbTLqKF/djgQAAKAyVTWw==$0 MOD_PEPTIDE_ID
BIN$qKbTLqKK/djgQAAKAyVTWw==$0 EVIDENCE_ID
BIN$qKbTLqKK/djgQAAKAyVTWw==$0 MOD_PEPTIDE_ID
BIN$qKbTLqKP/djgQAAKAyVTWw==$0 EVIDENCE_ID
BIN$qKbTLqKP/djgQAAKAyVTWw==$0 MOD_PEPTIDE_ID
BIN$qQ/rd6WHuzrgQAAKAyV56g==$0 CRYTALISATION_METHOD
BIN$qQ/rd6WHuzrgQAAKAyV56g==$0 ID
BIN$qQ/rd6WHuzrgQAAKAyV56g==$0 PDB_CODE
BIN$qQ/rd6WHuzrgQAAKAyV56g==$0 SWISS_PROT_CODE
BIN$qQ/rd6WHuzrgQAAKAyV56g==$0 UNIPROT_CODE
BIN$qQ/rd6WOuzrgQAAKAyV56g==$0 ID
BIN$qQ/rd6WOuzrgQAAKAyV56g==$0 LANE_ID
CACHE_PICKLEDATASET RESULTSET_ID
CELERY_TASKMETA ID
CELERY_TASKMETA TASK_ID
CELERY_TASKSETMETA ID
CELERY_TASKSETMETA TASKSET_ID
DJCELERY_CRONTABSCHEDULE ID
DJCELERY_INTERVALSCHEDULE ID
DJCELERY_PERIODICTASK CRONTAB_ID
DJCELERY_PERIODICTASK ID
DJCELERY_PERIODICTASK INTERVAL_ID
DJCELERY_PERIODICTASK NAME
DJCELERY_PERIODICTASKS IDENT
DJCELERY_TASKSTATE HIDDEN
DJCELERY_TASKSTATE ID
DJCELERY_TASKSTATE NAME
DJCELERY_TASKSTATE STATE
DJCELERY_TASKSTATE TASK_ID
DJCELERY_TASKSTATE TSTAMP
DJCELERY_TASKSTATE WORKER_ID
DJCELERY_WORKERSTATE HOSTNAME
DJCELERY_WORKERSTATE ID
DJCELERY_WORKERSTATE LAST_HEARTBEAT
EXT_GENEONTOLOGYREFS DESCRIPTION
LABTRACKER_PROTOCOLMATERIAL ID
LABTRACKER_PROTOCOLSTEP ID
LABTRACKER_PROTOCOLTEMPLATE ID
LKP_LABEL ID
LKP_LABEL TERM
LKP_LABEL TERM_CODE
PFL_DIMEXPERIMENT ID
PFL_DIMGENEONTOLOGY ID
PFL_DIMPROTEIN ID
PFL_FACT1 ID
PFL_FACT1_copy ID
PROTMS_BEADPROTEOMEPROTEIN BEADPROTEOME_ID
PROTMS_BEADPROTEOMEPROTEIN ID
PROTMS_EXPERIMENTALDESIGN EXPERIMENTLABEL
PROTMS_EXPERIMENTIPPROTEIN UNIPROT_ID
PROTMS_FAVOURITEPROTEIN UNIPROT_ID
PROTMS_GENEONTOLOGYREFS GOID
PROTMS_GENEONTOLOGYREFS UNIPROT_ID
PROTMS_GENEONTOLOGYREFS UNIQUE_ID
PROTMS_GROUP ID
PROTMS_PROTEIN CURRENTIPI
PROTMS_RAWENTITY PARENT
PROTMS_RAWENTITYMAPPING RAWENTITY_ID
PROTMS_RAWENTITYMAPPING UNIPROT_ID
PROTMS_RAWENTITYPROTEIN LEADINGPROTEIN
PROTMS_RAWENTITYPROTEIN OTHERSEQUENCECOVERAGE
PROTMS_RAWQUANTIFICATION EXPERIMENTLABEL
PROTMS_TEMPLATE_EXP_IPPROTEIN IPPROTEIN
PROTMS_UNIPROT DESCRIPTION
PROTMS_UNIPROT GENE
PROTMS_UNIPROT ID
PROTMS_UNIPROT SYS_NC00006$
PROTMS_UNIPROT SYS_NC00007$
PROTMS_UNIPROT SYS_NC00008$
PROTMS_UNIPROT UNIQUE_ID
PROTMS_USERGROUP GROUP_ID
PROTMS_USERGROUP ID
PROTMS_USERGROUP USER_ID
PROTMS_USERPROFILE LAB_ID
PROTMS_USERPROTEINGROUP_COPY ID
PROTMS_USERPROTEINGROUP_COPY RESULTSET_ID
PROTMS_USERPROTEINGROUP_COPY USER_ID
REVERSION_REVISION ID
REVERSION_REVISION USER_ID
REVERSION_VERSION CONTENT_TYPE_ID
REVERSION_VERSION ID
REVERSION_VERSION REVISION_ID
TURNOVER_DEGRADATIONPROFILE PROTEINIDENTIFIER
TURNOVER_PEPTIDES PROTEINIDENTIFIER
TURNOVER_PROTEINMAPPING SYS_NC00004$
TURNOVER_PROTEINMAPPING UNIPROT
TURNOVER_PROTEINSEQUENCE PROTEINIDENTIFIER
TURNOVER_SPATIALEXPVALUES UNIQUE_ID
TURNOVER_TURNOVERPROFILE FRACTION
TURNOVER_TURNOVERPROFILE PROTEINIDENTIFIER
|
|
|
|
|
|
| Re: Query performance diff [message #528529 is a reply to message #528528] |
Tue, 25 October 2011 09:36 |
Vackar
Messages: 81
Registered: October 2007
|
Member |
|
|
Well... not exactly.
The test schema is autogenerated by a test suite for testing certain behaviours. Tables should be roughly the same, and indexes shouldn't make a real difference in performance as each table only has around 20 rows in it.
The dev schema does have indexes on certain tables as it has millions of entries.
But I'd have thought that full table scans on tables with 20-30 rows shouldn't be giving the difference that I'm seeing.
So I', really confused as to what me be casuing this.
|
|
|
|
|
|
| Re: Query performance diff [message #528532 is a reply to message #528530] |
Tue, 25 October 2011 10:00 |
Vackar
Messages: 81
Registered: October 2007
|
Member |
|
|
Actually just noticed that there is a merge join cartesian on protms_rawquantification and the buffer soft of the full table access.
Is this something that would usually be considered cause for concern?
Thanks,
Vackar
|
|
|
|
|
|
| Re: Query performance diff [message #528534 is a reply to message #528533] |
Tue, 25 October 2011 10:11 |
Vackar
Messages: 81
Registered: October 2007
|
Member |
|
|
BlackSwan wrote on Tue, 25 October 2011 10:03>
I am not concerned; but others may differ.
Sorry, wanted to double check, are you not concerned becuase it's not the cause, or because you personally are not concerned.
|
|
|
|
| Re: Query performance diff [message #529029 is a reply to message #528534] |
Fri, 28 October 2011 06:21 |
Vackar
Messages: 81
Registered: October 2007
|
Member |
|
|
Ok, finally found out why this was slow.
The hint was the filter predicate on line 41:
41 - filter("MAP"."ACCESSION"=SYS_OP_C2C("UNIPROT"."PRIMARY_ACCESSION"))
Oracle was converting between character sets. The reason => the 'slow' schema was using NVARCHAR2, and the other was using VARCHAR2, so it looks like values in the table can't be comared without a characterset conversion, hence the slow speed.
After fixing this query times went from > 24hrs to less than half a second.
Hope this can help someone in the future.
Vackar
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
