| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: When the DBA sequels - simple (phfilosophical?) tuning question
anacedent <anacedent_at_hotmail.com> wrote:
>Please post output from...
>DESC quot
SQL> desc quot
Name Null? Type ----------------------------------------- -------- QUOT_ID NOT NULL NUMBER(16) SAMPLE_ID NUMBER(16) OPERATOR_ID NUMBER(16) LOCATION_ID NUMBER(16) GROUP_ID NUMBER(16) PRIORITY NUMBER(38) MATRIX_TYPE VARCHAR2(255) CONCLUSION VARCHAR2(255) CONDITION VARCHAR2(255) AMOUNT NUMBER DATE_RESULTS_REQUIRED DATE EXPECTED_ON DATE EXPIRES_ON DATE CONTAINER_TYPE_ID NUMBER(16) QUOT_TEMPLATE_ID NUMBER(16) WORKFLOW_NODE_ID NOT NULL NUMBER(16) USAGE_COUNT NUMBER(10) STOCK_TEMPLATE_ID NUMBER(16) NAME VARCHAR2(255) DESCRIPTION VARCHAR2(4000) STATUS CHAR(1) OLD_STATUS VARCHAR2(255) CREATED_ON DATE COMPLETED_ON DATE AUTHORISED_ON DATE EVENTS VARCHAR2(4000) UNIT_ID NUMBER(16) NEEDS_REVIEW CHAR(1) INSPECTION_PLAN_ID NUMBER(16) RECEIVED_BY NUMBER(16) RECEIVED_ON DATE REPORTED CHAR(1) HAS_NOTES CHAR(1) HAS_AUDITS CHAR(1) QUOT_TYPE VARCHAR2(255) STORAGE VARCHAR2(4000) EXTERNAL_REFERENCE VARCHAR2(255) CREATED_BY NUMBER(16) COMPLETED_BY NUMBER(16) AUTHORISED_BY NUMBER(16) SUPPLIER_ID NUMBER(16) CHEMICAL_ID NUMBER(16) STOCK_TYPE_ID NUMBER(16) GRADE VARCHAR2(255) BATCH_NUMBER VARCHAR2(255) PURITY NUMBER PURITY NUMBER PLATE_ID NUMBER(16) PLATE_ORDER NUMBER(10) PLATE_ROW NUMBER(10) PLATE_COLUMN NUMBER(10) PLATE_QUOT_TYPE NUMBER(16)
>DESC quot_user
SQL> desc quot_user
Name Null? Type ----------------------------------------- -------- QUOT_ID NOT NULL NUMBER(16) U_QUOTTYPE VARCHAR2(2000) U_BEWERTUNG_LINIE_QUOT VARCHAR2(2000) U_ANZUCHTDATUM DATE U_PARENT_NAME VARCHAR2(2000) U_TYPE VARCHAR2(30) U_BEWERTUNG_KO_SAMEN VARCHAR2(2000) U_COLLECTION_VIAL VARCHAR2(2000) U_EXTRATION_CELL VARCHAR2(2000) U_INSTRUMENT NUMBER(16) U_CHAMBER_PHRASE VARCHAR2(30) U_COORDINATES VARCHAR2(2000) U_LCMS_INSTRUMENT_FILENAME VARCHAR2(2000) U_LCMS_OUTPUT_FILENAME VARCHAR2(2000) U_GCMS_OUTPUT_FILENAME VARCHAR2(2000) U_GCMS_INSTRUMENT_FILENAME VARCHAR2(2000) U_EXTRACTION_CELL VARCHAR2(2000) U_GCMS_OUTPUTFILENAME VARCHAR2(2000) U_REAGENZGLASNUMMER VARCHAR2(2000) U_CONSTRUCT_NUMBER VARCHAR2(2000) U_ANIMPFDATUM DATE U_SPECIES VARCHAR2(30) U_ECOTYPE VARCHAR2(30) U_KISTE VARCHAR2(2000) U_INTERNAL_REFERENCE VARCHAR2(2000) U_GENERATION VARCHAR2(30) U_IS_WILDTYPE VARCHAR2(30) U_NUMBER_T1_PLANTS NUMBER U_AUSSAAT_DATUM DATE U_PRIMARY_QUOT_ID NUMBER U_STATUS VARCHAR2(30) U_PIKIERDATUM DATE U_NUMBER_REPLICATES VARCHAR2(30) U_ROBOTIK_BA VARCHAR2(30) U_ASE_INSTRUMENT VARCHAR2(2000) U_ORF_NAME VARCHAR2(2000) U_FUNCTION VARCHAR2(2000) U_CONFIRMATION VARCHAR2(2000) U_PROJECT VARCHAR2(2000) U_DELIVERY DATE U_REGIA_ID VARCHAR2(2000) U_LC_PHRASE VARCHAR2(30) U_GC_PHRASE VARCHAR2(30) U_LINE_ID NUMBER U_PARENT_NAME_2 VARCHAR2(2000) U_VERMEHRUNG VARCHAR2(2000) U_PLOIDY_LEVEL VARCHAR2(2000) U_PHENOTYPE VARCHAR2(2000) U_GROWTH NUMBER U_DNA_TYPE VARCHAR2(2000) U_HARVEST_DATE DATE U_PARENT_NAME_3 VARCHAR2(2000) U_DONOR VARCHAR2(2000) U_PROMOTOR VARCHAR2(2000) U_TARGET VARCHAR2(2000) U_BODENTYP VARCHAR2(30) U_WANNE VARCHAR2(30) U_FEUCHTE NUMBER U_TROCKENGEWICHT NUMBER U_RESULT_GDNA VARCHAR2(30) U_GDNA_RESULT VARCHAR2(2000) U_ADAPTOR_ENZYME VARCHAR2(30) U_BORDER VARCHAR2(30) U_T_DNA_LOKUS_IDENTIFIED CHAR(1) U_DNA_TEMPLATE_ID VARCHAR2(2000) U_TEXT_FELD_100 VARCHAR2(2000) U_ORF_VERSION NUMBER U_RACK_NAME VARCHAR2(30) U_EXTRACTION_PHRASE VARCHAR2(2000) U_VECTORNAME VARCHAR2(2000)
> > only one row is returned
>One row from which table?
from table quot
>SELECT *
>FROM quot a
>WHERE flow_node_id = 711
> AND EXISTS (SELECT '1'
> FROM quot_user au
> WHERE a.quot_id = au.quot_id
> );
>
>What is returned by above SQL?
The same one row.
>What is EXPLAIN_PLAN for above SQL?
| Id | Operation | Name | Rows | Bytes | Cost | TQ |IN-OUT| PQ Distrib | -------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10306 | 3301K| 195 | | | | | 1 | NESTED LOOPS SEMI | | 10306 | 3301K| 195 | 69,01 | P->S | QC (RAND) | | 2 | TABLE ACCESS BY INDEX ROWID| QUOT | 10306 | 3240K| 191 | 69,00 | S->P | RND-ROBIN | | 3 | INDEX RANGE SCAN | FK_QUOT_FLOW_NODE| 10306 | | 27 | | | | | 4 | INDEX UNIQUE SCAN | PK_QUOT_USER | 1865K| 10M| 1 | 69,01 | PCWP | | --------------------------------------------------------------------------------------------------------------
Note: cpu costing is off, PLAN_TABLE' is old version
Interesting to note, that in the last line of the plan, the INDEX UNIQUE SCAN is rated with Cost=1 although 10M are read. Funny.
>How fast to above SQL run?
It runs 2 sec. So it is still 100 times slower than the "fast" version, but more than 10x faster than the "slow" version. I think the problem is the join, it is still there.
Bye
Rick Denoire
Received on Sun Jun 20 2004 - 06:15:33 CDT
 |
 |