Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: When the DBA sequels - simple (phfilosophical?) tuning question

Re: When the DBA sequels - simple (phfilosophical?) tuning question

From: Rick Denoire <100.17706_at_germanynet.de>
Date: Sun, 20 Jun 2004 13:15:33 +0200
Message-ID: <edrad0hfksjv4qbj89mtlgd9atm830o9bk@4ax.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US