Home » SQL & PL/SQL » SQL & PL/SQL » A single SQL command run for 8 minutes ?? Something wrong ??
A single SQL command run for 8 minutes ?? Something wrong ?? [message #234687] Wed, 02 May 2007 20:55 Go to next message
budiss
Messages: 8
Registered: May 2007
Location: Jakarta
Junior Member
Hi,

I have an application which has a command to access the database then run an SQL command.

The SQL is as below :

  SELECT
      survey_name,
      survey_type,
      processing_job_nm,
      processing_type,
      seis_nav_segm_full_name,
      seis_nav_segm_bo_sp_start,
      seis_nav_segm_bo_sp_end,
      statistics,
      processing_job_s,
      geom_set_s,
      seis_nav_segm_bo_s,
      nav_line_segm_s,
      seis_nav_segm_s,
      proc_seis_section_s,
      seis_nav_segm_bo_s
  FROM
      pb_bo_seis_nav_segm_role
  WHERE
      processing_job_s=29247182
  ORDER
  BY survey_name,
     processing_job_nm,
     seis_nav_segm_full_name



It takes 8 minutes until the result is returned. Whether the result is less than 10 lines or more that 20 lines, the time is around 8 minutes, which is for me, it's too long.

Any idea in how to check in order to reduce the execution time for that command ?? Maybe the table, index, etc ??

And how to do that ??


Thanks in advance.

budiss
[Mod-edit: Added code-tags. Please put code in code-tags instead of coloring it.]

[Updated on: Thu, 03 May 2007 00:53] by Moderator

Report message to a moderator

Re: A single SQL command run for 8 minutes ?? Something wrong ?? [message #234721 is a reply to message #234687] Thu, 03 May 2007 00:52 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
How many rows are there in your table?
And how many of those have processing_job_s=29247182 ?

If this number is a small percentage of the total number, you could benefit by an index on that column. Make sure you analyze your tables on a regular basis.
Re: A single SQL command run for 8 minutes ?? Something wrong ?? [message #234722 is a reply to message #234687] Thu, 03 May 2007 00:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sure, add an index on processing_job_s.

Regards
Michel
Re: A single SQL command run for 8 minutes ?? Something wrong ?? [message #234769 is a reply to message #234687] Thu, 03 May 2007 02:27 Go to previous messageGo to next message
budiss
Messages: 8
Registered: May 2007
Location: Jakarta
Junior Member
Actually, pb_bo_seis_nav_segm_role is a synonym.

This synonym is from a view.

This view is constructed from some tables and another views.


I run this command :

select count(*) from pb_bo_seis_nav_segm_role;

...and it also take 8 minutes to finish, which means adding an index for processing_job_s is not an issue. Correct me if I'm wrong.

(FYI, the above command result in 53236 rows).

If I have to focus on indexes, should I check all indexes belong to the tables which constructs the synonym ??

How to consider that certain indexes cause the problem ?


Thx,

budiss

Re: A single SQL command run for 8 minutes ?? Something wrong ?? [message #234773 is a reply to message #234769] Thu, 03 May 2007 02:30 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Post the view
Re: A single SQL command run for 8 minutes ?? Something wrong ?? [message #234778 is a reply to message #234769] Thu, 03 May 2007 02:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post ALL informations in the original question and avoir wasting our time by searching not accurate answer.

Regards
Michel
Re: A single SQL command run for 8 minutes ?? Something wrong ?? [message #234814 is a reply to message #234687] Thu, 03 May 2007 04:01 Go to previous messageGo to next message
budiss
Messages: 8
Registered: May 2007
Location: Jakarta
Junior Member
synonym PB_BO_SEIS_NAV_SEGM_ROLE is from view PB_BO_SEIS_NAV_SEGM .

view PB_BO_SEIS_NAV_SEGM :

select
  a.SEIS_NAV_SEGM_BO_S          SEIS_NAV_SEGM_BO_S,
  a.SEIS_NAV_SEGM_BO_BIN_START  SEIS_NAV_SEGM_BO_BIN_START,
  a.SEIS_NAV_SEGM_BO_BIN_END    SEIS_NAV_SEGM_BO_BIN_END,
  a.SEIS_NAV_SEGM_BO_SP_START   SEIS_NAV_SEGM_BO_SP_START,
  a.SEIS_NAV_SEGM_BO_SP_END     SEIS_NAV_SEGM_BO_SP_END,
  a.ROW_CREATE_DATE             ROW_CREATE_DATE,
  a.ROW_CREATOR                 ROW_CREATOR,
  a.SEIS_NAV_SEGM_S             SEIS_NAV_SEGM_S,
  a.SEIS_NAV_SEGM_T             SEIS_NAV_SEGM_T,
  a.SEIS_NAV_SEGM_NM            SEIS_NAV_SEGM_NM,
  a.SEIS_NAV_SEGM_BIN_START     SEIS_NAV_SEGM_BIN_START,
  a.SEIS_NAV_SEGM_BIN_END       SEIS_NAV_SEGM_BIN_END,
  a.SEIS_NAV_SEGM_SP_START      SEIS_NAV_SEGM_SP_START,
  a.SEIS_NAV_SEGM_SP_END        SEIS_NAV_SEGM_SP_END,
  c.PROCESSING_LINE_NM          PROCESSING_LINE_NM,
  c.TRACE_NR_START              TRACE_NR_START,
  c.TRACE_NR_END                TRACE_NR_END,
  c.BIN_START                   BIN_START,
  c.BIN_END                     BIN_END,
  c.PROC_SEIS_SECTION_S         PROC_SEIS_SECTION_S,
  nvl
  ( c.SAMPLE_START_TIME*
    decode
    ( c.SAMPLE_INTERVAL_U,
      'microseconds', 0.001,
      'us',           0.001,
                      1
    ),0
  )                             SAMPLE_START_TIME,
  c.SAMPLE_INTERVAL*
  decode
  ( c.SAMPLE_INTERVAL_U,
    'microseconds', 0.001,
    'us',           0.001,
                    1
  )                             SAMPLE_INTERVAL,
  (nvl(c.SAMPLE_START_TIME,0) + (c.SAMPLE_COUNT-1)*c.SAMPLE_INTERVAL)*
  decode
  ( c.SAMPLE_INTERVAL_U,
    'microseconds', 0.001,
    'us',           0.001,
                    1
  )                             SAMPLE_END_TIME,
  decode
  ( c.SAMPLE_INTERVAL_U,
    'microseconds', 'ms',
    'us',           'ms',
    'milliseconds', 'ms',
                    c.SAMPLE_INTERVAL_U
  )                             SAMPLE_UNIT,
  nvl2(j.OBJECT_S,'Y','N')      STATISTICS,
  c.CMP_XLINE_INC               CMP_XLINE_INC,
  e.PROCESSING_JOB_S            PROCESSING_JOB_S,
  e.PROCESSING_TYPE             PROCESSING_TYPE,
  e.PROCESSING_JOB_NM           PROCESSING_JOB_NM,
  e.PROC_DATE                   PROC_DATE,
  e.PROJECT_DATE                PROJECT_DATE,
  e.TIME_DEPTH                  TIME_DEPTH,
  e.PHASE                       PHASE,
  h.SURVEY_NAME                 SURVEY_NAME,
  h.SURVEY_TYPE                 SURVEY_TYPE,
  h.COUNTRY_NAME                COUNTRY_NAME,
  a.GEOM_SET_S                  GEOM_SET_S,
  a.LINE_NR                     LINE_NR,
  g.KIND                        KIND,
  g.GEOM_SET_NAME               GEOM_SET_NAME,
  a.SEGMENT_NAME                SEGMENT_NAME,
  a.SEGMENT_NR                  SEGMENT_NR,
  a.PT_IDX_START                PT_IDX_START,
  a.PT_IDX_END                  PT_IDX_END,
  a.NAV_LINE_SEGM_S             NAV_LINE_SEGM_S,
  a.SRC_PT_NOM_SPC              SRC_PT_NOM_SPC,
  a.SRC_PT_NOM_SPC_U            SRC_PT_NOM_SPC_U,
  a.LINE_LENGTH                 LINE_LENGTH,
  a.LINE_LENGTH_U               LINE_LENGTH_U,
  e.PROCESSING_JOB_NM||' <-> '||a.SEIS_NAV_SEGM_NM||' <-> '||e.PROCESSING_TYPE
                                SEIS_NAV_SEGM_FULL_NAME,
  a.MATCH_KIND                  MATCH_KIND
from
  PB_BO_SEIS_NAV_GRID_MATCH a,
  PB_PROC_SEIS_SECTION c,
  PB_PROCESSING_JOB e,
  PB_GEOM_SET g,
  PB_SURVEY h,
  PB_SEIS_STATISTICS j
where a.PROC_SEIS_SECTION_S = c.PROC_SEIS_SECTION_S
  and c.PROCESSING_JOB_S = e.PROCESSING_JOB_S
  and a.GEOM_SET_S = g.GEOM_SET_S
  and g.SURVEY_S = h.SURVEY_S
  and c.PROC_SEIS_SECTION_S = j.OBJECT_S (+)



view PB_PROC_SEIS_SECTION :

select
  SEIS_DATA_SET_S          PROC_SEIS_SECTION_S,
  SEIS_DATA_SET_NAME       PROCESSING_LINE_NM,
  R_NAMING_SYSTEM_KD       IDENTITY_TYPE,
  KIND,
  SEIS_PROC_ACT_S          PROCESSING_JOB_S,
  PB_TRACE_NR_START        TRACE_NR_START,
  PB_TRACE_NR_END          TRACE_NR_END,
  PB_BIN_START             BIN_START,
  PB_BIN_END               BIN_END,
  PB_LOCATION_TYPE         POINT_TYPE,
  SAMPLE_START_TIME,
  SAMPLE_INTERVAL_U        SAMPLE_START_TIME_U,
  SAMPLE_INTERVAL,
  SAMPLE_INTERVAL_U,
  SAMPLE_COUNT,
  DESCRIPTION,
  PB_CMP_XLINE_INC         CMP_XLINE_INC,
  PB_TRACE_INC             TRACE_INC
from SEIS_DATA_SET
where R_NAMING_SYSTEM_KD = 'trace'
  and KIND||'' = 'PB_PROC_SEIS_SECTION'



view PB_PROCESSING_JOB :

select
  SEIS_PROC_ACT_S         PROCESSING_JOB_S,
  ACTIVITY_NAME           PROCESSING_JOB_NM,
  TYPICAL_ACT_NAME        PROCESSING_TYPE,
  ACT_CLASS_NAME          ACT_CLASS_NAME,
  CONTAINING_ACT_S        CONTAINING_ACT_S,
  CONTAINING_ACT_T        CONTAINING_ACT_T,
  START_TIME              PROJECT_DATE,
  END_TIME                PROC_DATE,
  SEIS_GEOM_SET_S         SURVEY_S,
  R_EXISTENCE_KD_NM       EXISTENCE_KIND,
  ROW_CREATOR             ROW_CREATOR,
  ROW_CREATE_DATE         ROW_CREATE_DATE,
  PB_OWNER                OWNER,
  DESCRIPTION             DESCRIPTION,
  PB_LAST_UPDATED         LAST_UPDATED,
  PB_UPDATE_COMMENTS      UPDATE_COMMENTS,
  PRIVATE                 PRIVATE,
  PB_TIME_DEPTH           TIME_DEPTH,
  PB_PHASE                PHASE,
  PB_COMP_COUNT           COMP_COUNT,
  PB_MULTI_COMP           MULTI_COMP
from  SEIS_PROC_ACT
where ACT_CLASS_NAME is null
or ACT_CLASS_NAME = '4D seismic processing'
and ( PRIVATE='N'
      or
      sys_context('PB_ENV','PB_ROLE')='PDO'
      or
      sys_context('PB_ENV','COMPANY_CODE')=PB_OWNER
    )



view PB_GEOM_SET :

select
  SEIS_GEOM_SET_S        GEOM_SET_S,
  SEIS_GEOM_SET_ID       GEOM_SET_NAME,
  COMPONENT_GEOM_S       SURVEY_S,
  R_SEISMIC_GEOM_KD      KIND,
  PB_OWNER               OWNER,
  PB_PUBLIC              PRIVATE,          -- OBS!
  PB_COORDINATE_SYS      COORDINATE_SYS,
  ROW_CREATOR,
  ROW_CREATE_DATE,
  CHANNEL_UID_S          GRID_DEF_S,
  DESCRIPTION,
  PB_LAST_UPDATED        LAST_UPDATED,
  PB_UPDATE_COMMENTS     UPDATE_COMMENTS,
  GEOM_SET_LEVEL         GEOM_SET_LEVEL
from  SEIS_GEOM_SET
where GEOM_SET_LEVEL='GS'
and ( PB_PUBLIC='N'                        -- i.e. PRIVATE='N'
      or
      sys_context('PB_ENV','PB_ROLE')='PDO'
      or
      sys_context('PB_ENV','COMPANY_CODE')=PB_OWNER
    )



view PB_SURVEY :

select
  SEIS_GEOM_SET_S        SURVEY_S,
  SEIS_GEOM_SET_ID       SURVEY_NAME,
  R_SURVEY_ENVR_KIND     ENVIRONMENT_TYPE, -- previously ENVIRONMENT_TYPE
  R_SEISMIC_GEOM_KD      SURVEY_TYPE,      -- previously KIND
  PB_OWNER               OWNER,
  GEOP_ACQ_AREA          AREA,
  PB_PUBLIC              PRIVATE,          -- OBS!
  PB_UPDATE_COMMENTS     UPDATE_COMMENTS,
  PB_COUNTRY_NAME        COUNTRY_NAME,
  BIN_MATCH_FLAG         MERGED_SURVEY,
  ROW_CREATOR            ROW_CREATOR,
  ROW_CREATE_DATE        ROW_CREATE_DATE,
  DESCRIPTION            DESCRIPTION,
  PB_LAST_UPDATED        LAST_UPDATED,
  SURVEY_YEAR            SURVEY_YEAR,
  NOM_SURVEY_LENGTH      NOM_SURVEY_LENGTH,
  NOM_SURVEY_LEN_U       NOM_SURVEY_LENGTH_U,
  NOM_SURVEY_AREA        NOM_SURVEY_AREA,
  NOM_SURVEY_AREA_U      NOM_SURVEY_AREA_U,
  CHANNEL_UID_S          GRID_DEF_S
from  SEIS_GEOM_SET
where GEOM_SET_LEVEL='S'
  and ( PB_PUBLIC='N'                      -- i.e. PRIVATE='N'
        or
        sys_context('PB_ENV','PB_ROLE') = 'PDO'
        or
        sys_context('PB_ENV','COMPANY_CODE') = PB_OWNER
      )



view PB_SEIS_STATISTICS :

select
SEIS_STATISTICS_S,
OBJECT_S,
OBJECT_T,
ROW_CREATOR,
ROW_CREATE_DATE,
SAMPLE_MAX,
SAMPLE_MIN,
NOF_SAMPLES,
USER_ROLE,
NOF_TRACES,
SIGNED_AVERAGE,
UNSIGNED_AVERAGE,
SAMPLE_VARIANCE,
STANDARD_DEVIATION
from
PB_SEIS_STATISTICS_TABLE



view PB_BO_SEIS_NAV_GRID_MATCH :

select
  a.SEIS_NAV_SEGM_BO_S          SEIS_NAV_SEGM_BO_S,
  a.BIN_START                   SEIS_NAV_SEGM_BO_BIN_START,
  a.BIN_END                     SEIS_NAV_SEGM_BO_BIN_END,
  a.SP_START                    SEIS_NAV_SEGM_BO_SP_START,
  a.SP_END                      SEIS_NAV_SEGM_BO_SP_END,
  a.ROW_CREATE_DATE             ROW_CREATE_DATE,
  a.ROW_CREATOR                 ROW_CREATOR,
  a.SEIS_NAV_SEGM_S             SEIS_NAV_SEGM_S,
  a.PHYSICAL_OBJECT_T           SEIS_NAV_SEGM_T,
  b.SEIS_NAV_SEGM_NM            SEIS_NAV_SEGM_NM,
  b.BIN_START                   SEIS_NAV_SEGM_BIN_START,
  b.BIN_END                     SEIS_NAV_SEGM_BIN_END,
  b.SP_START                    SEIS_NAV_SEGM_SP_START,
  b.SP_END                      SEIS_NAV_SEGM_SP_END,
  b.PROC_SEIS_SECTION_S         PROC_SEIS_SECTION_S,
  c.GEOM_SET_S                  GEOM_SET_S,
  0                             LINE_NR,
  c.BINSET_NAME                 SEGMENT_NAME,
  0                             SEGMENT_NR,
  d.FIRST_CROSSLINE             PT_IDX_START,
  d.LAST_CROSSLINE              PT_IDX_END,
  d.GRID_DEF_S                  NAV_LINE_SEGM_S,
  NULL                          SRC_PT_NOM_SPC,
  NULL                          SRC_PT_NOM_SPC_U,
  NULL                          LINE_LENGTH,
  NULL                          LINE_LENGTH_U,
 'GRID_DEF'                     MATCH_KIND
from
  PB_SEIS_NAV_SEGM_BO a,
  PB_SEIS_NAV_SEGM b,
  PB_3D_BINSET_COVERAGE c,
  PB_GRID_DEF d
where a.SEIS_NAV_SEGM_S = b.SEIS_NAV_SEGM_S
  and b.BINSET_S = c.BINSET_GRID_S
  and d.GRID_DEF_S = c.GRID_DEF_S
union
select
  a.SEIS_NAV_SEGM_BO_S          SEIS_NAV_SEGM_BO_S,
  a.BIN_START                   SEIS_NAV_SEGM_BO_BIN_START,
  a.BIN_END                     SEIS_NAV_SEGM_BO_BIN_END,
  a.SP_START                    SEIS_NAV_SEGM_BO_SP_START,
  a.SP_END                      SEIS_NAV_SEGM_BO_SP_END,
  a.ROW_CREATE_DATE             ROW_CREATE_DATE,
  a.ROW_CREATOR                 ROW_CREATOR,
  a.SEIS_NAV_SEGM_S             SEIS_NAV_SEGM_S,
  a.PHYSICAL_OBJECT_T           SEIS_NAV_SEGM_T,
  b.SEIS_NAV_SEGM_NM            SEIS_NAV_SEGM_NM,
  b.BIN_START                   SEIS_NAV_SEGM_BIN_START,
  b.BIN_END                     SEIS_NAV_SEGM_BIN_END,
  b.SP_START                    SEIS_NAV_SEGM_SP_START,
  b.SP_END                      SEIS_NAV_SEGM_SP_END,
  b.PROC_SEIS_SECTION_S         PROC_SEIS_SECTION_S,
  d.GEOM_SET_S                  GEOM_SET_S,
  d.LINE_NR                     LINE_NR,
  c.SEGMENT_NAME                SEGMENT_NAME,
  c.SEGMENT_NR                  SEGMENT_NR,
  c.PT_IDX_START                PT_IDX_START,
  c.PT_IDX_END                  PT_IDX_END,
  c.NAV_LINE_SEGM_S             NAV_LINE_SEGM_S,
  e.SRC_PT_NOM_SPC              SRC_PT_NOM_SPC,
  e.SRC_PT_NOM_SPC_U            SRC_PT_NOM_SPC_U,
  e.LINE_LENGTH                 LINE_LENGTH,
  e.LINE_LENGTH_U               LINE_LENGTH_U,
  'NAV_LINE'                    MATCH_KIND
from
  PB_SEIS_NAV_SEGM_BO a,
  PB_SEIS_NAV_SEGM b,
  PB_NAV_LINE_SEGM c,
  PB_NAV_LINE d,
  PB_NAV_LINE_SEGM_GEOM_SUM e
where a.SEIS_NAV_SEGM_S = b.SEIS_NAV_SEGM_S
  and b.NAV_LINE_SEGM_S = c.NAV_LINE_SEGM_S
  and d.NAV_LINE_S = c.NAV_LINE_S
  and e.NAV_LINE_SEGM_S = c.NAV_LINE_SEGM_S



view PB_SEIS_NAV_SEGM_BO :

select
  PB_ACCESS_OBJECT_S                    SEIS_NAV_SEGM_BO_S,
  TECHNICAL_OBJECT_S                    SEIS_NAV_SEGM_S,
  TECHNICAL_OBJECT_T                    PHYSICAL_OBJECT_T,
  ROW_CREATE_DATE                       ROW_CREATE_DATE,
  ROW_CREATOR                           ROW_CREATOR,
  BIN_START                             BIN_START,
  BIN_END                               BIN_END,
  SP_START                              SP_START,
  SP_END                                SP_END
from PB_ACCESS_OBJECT
where TECHNICAL_OBJECT_T||'' = 'PB_SEIS_NAV_SEGM'



view PB_SEIS_NAV_SEGM :

select
      SEIS_DATA_SET_S           SEIS_NAV_SEGM_S,
      SEIS_DATA_SET_NAME        SEIS_NAV_SEGM_NM,
      R_NAMING_SYSTEM_KD        IDENTITY_TYPE,
      KIND,
      DERIVED_FROM_S            PROC_SEIS_SECTION_S,
      SEISMIC_GEOM_SET_S        NAV_LINE_SEGM_S,
      PB_BIN_START              BIN_START,
      PB_BIN_END                BIN_END,
      PB_SP_START               SP_START,
      PB_SP_END                 SP_END,
      PB_SEIS_LINE_LEN          SEIS_LINE_LENGTH,
      ROW_CREATOR,
      ROW_CREATE_DATE,
      BINSET_S,
      BINSET_T
from  SEIS_DATA_SET
where R_NAMING_SYSTEM_KD = 'trace'
  and KIND||'' = 'PB_SEIS_NAV_SEGM'



view PB_NAV_LINE_SEGM :

select
        SEIS_GEOM_SET_S      NAV_LINE_SEGM_S,
        SEIS_GEOM_SET_ID     SEGMENT_NAME,
        PB_RESHOOT_CODE      RESHOOT_CODE,
        COMPONENT_GEOM_S     NAV_LINE_S,
        PB_ACQUISITION_S     ACQUISITION_S,
        ROW_CREATOR,
        ROW_CREATE_DATE,
        R_SEISMIC_GEOM_KD    KIND,
        PB_DIRECTION         DIRECTION,
        ACQUISITION_LN_IDX   SEGMENT_NR,
        PT_IDX_START,
        LAT_START,
        LONG_START,
        PT_IDX_END,
        LAT_END,
        LONG_END,
        DESCRIPTION
from    SEIS_GEOM_SET
where   GEOM_SET_LEVEL='LS'



view PB_NAV_LINE :

select
  ACQUISITION_LN_IDX    LINE_NR,
  COMPONENT_GEOM_S      GEOM_SET_S,
  PB_MSTR_ROOT_LINE     MASTER_ROOT_LINE,
  PB_ROOT_LINE          ROOT_LINE,
  ROW_CREATE_DATE,
  ROW_CREATOR,
  R_SEISMIC_GEOM_KD     KIND,              -- previously KIND,
  SEIS_GEOM_SET_ID      LINE_NAME,
  SEIS_GEOM_SET_S       NAV_LINE_S,
  SEIS_GEOM_SET_UID     NAV_LINE_UID
from SEIS_GEOM_SET
where geom_set_level = 'L'



view PB_NAV_LINE_SEGM_GEOM_SUM :

select
P_SEIS_GEOM_SUM_S,
SEIS_GEOM_SET_S,
SRC_PT_NOM_SPC,
SRC_PT_NOM_SPC_U,
SOURCE_POINT_COUNT,
ROW_CREATOR,
ROW_CREATE_DATE,
SURFACE_LENGTH,
SURFACE_LENGTH_U
from P_SEIS_GEOM_SUM




Thx,

budiss
Re: A single SQL command run for 8 minutes ?? Something wrong ?? [message #234828 is a reply to message #234814] Thu, 03 May 2007 04:34 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
WTF!

Why on earth did you not tell us in the first place that you were querying half your database?
You must have known your initial question did not 'really' give a good image of what the actual problem is...
Re: A single SQL command run for 8 minutes ?? Something wrong ?? [message #234841 is a reply to message #234828] Thu, 03 May 2007 05:16 Go to previous messageGo to next message
budiss
Messages: 8
Registered: May 2007
Location: Jakarta
Junior Member
sorry sir....actually it's only 10 out of hundreds views only.

well, my question is only :

The single script at my initial posting takes 8 minutes to get done.

But when I drilled-down the view, it has another views involved.

According to the symptom, what is the most possibility that can cause it ?

I myself suspect that the index which causing the problem, and I want to get some advices from others.

Maybe somebody suspect another possible causes which don't cross my mind.


Thx,

budiss
Re: A single SQL command run for 8 minutes ?? Something wrong ?? [message #235006 is a reply to message #234687] Thu, 03 May 2007 13:37 Go to previous messageGo to next message
Ericle
Messages: 44
Registered: April 2006
Location: United States of America ...
Member

How many rows are in each of these tables? If they are in fact tables.

SEIS_DATA_SET
SEIS_PROC_ACT
SEIS_GEOM_SET
PB_SEIS_STATISTICS_TABLE
PB_3D_BINSET_COVERAGE
PB_GRID_DEF
PB_ACCESS_OBJECT
P_SEIS_GEOM_SUM

Regardless, tell us how many rows are in each of the tables accessed within all the views that are referenced.

[Updated on: Thu, 03 May 2007 13:51]

Report message to a moderator

Re: A single SQL command run for 8 minutes ?? Something wrong ?? [message #235026 is a reply to message #235006] Thu, 03 May 2007 15:35 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
An explain plan, autotrace and/or a tkprof output might be helpful too.
Re: A single SQL command run for 8 minutes ?? Something wrong ?? [message #235053 is a reply to message #235006] Thu, 03 May 2007 22:01 Go to previous messageGo to next message
budiss
Messages: 8
Registered: May 2007
Location: Jakarta
Junior Member
Ericle wrote on Thu, 03 May 2007 13:37
How many rows are in each of these tables? If they are in fact tables.

SEIS_DATA_SET
SEIS_PROC_ACT
SEIS_GEOM_SET
PB_SEIS_STATISTICS_TABLE
PB_3D_BINSET_COVERAGE
PB_GRID_DEF
PB_ACCESS_OBJECT
P_SEIS_GEOM_SUM

Regardless, tell us how many rows are in each of the tables accessed within all the views that are referenced.



I don't know what the syntaxs to do the audit, analyze, etc.

Here is the row's ammount for each table :

PB_ACCESS_OBJECT >> 335727
SEIS_DATA_SET >> 220553
BINSET_GRID >> 0
GRID_1D >> 1
SEIS_GEOM_SET >> 79342
P_SEIS_GEOM_SUM >> 41619
SEIS_PROC_ACT >> 662
PB_SEIS_STATISTICS_TABLE >> 138186

[Updated on: Thu, 03 May 2007 22:04]

Report message to a moderator

Re: A single SQL command run for 8 minutes ?? Something wrong ?? [message #235114 is a reply to message #234687] Fri, 04 May 2007 02:07 Go to previous messageGo to next message
budiss
Messages: 8
Registered: May 2007
Location: Jakarta
Junior Member
result of SET AUTOTRACE ON EXPLAIN

Execution Plan
----------------------------------------------------------                      
   0      SELECT STATEMENT Optimizer=RULE                                       
   1    0   SORT (ORDER BY)                                                     
   2    1     NESTED LOOPS (OUTER)                                              
   3    2       NESTED LOOPS                                                    
   4    3         NESTED LOOPS                                                  
   5    4           NESTED LOOPS                                                
   6    5             NESTED LOOPS                                              
   7    6               VIEW OF 'PB_BO_SEIS_NAV_GRID_MATCH'                     
   8    7                 SORT (UNIQUE)                                         
   9    8                   UNION-ALL                                           
  10    9                     TABLE ACCESS (BY INDEX ROWID) OF 'PB_ACCESS_OBJECT'           
  11   10                       NESTED LOOPS                                    
  12   11                         NESTED LOOPS                                  
  13   12                           NESTED LOOPS                                
  14   13                             TABLE ACCESS (FULL) OF 'SEIS_DATA_SET'          
  15   13                             TABLE ACCESS (BY INDEX ROWID) OF 'BINSET_GRID'         
  16   15                               INDEX (UNIQUE SCAN) OF 'BINSET_GRID_S_PK' (UNIQUE)           
  17   12                           TABLE ACCESS (BY INDEX ROWID) OF 'GRID_1D'            
  18   17                             INDEX (UNIQUE SCAN) OF 'GRID_1D_S_PK' (UNIQUE)          
  19   11                         INDEX (RANGE SCAN) OF 'I_ACCESS' (NON-UNIQUE)          
  20    9                     TABLE ACCESS (BY INDEX ROWID) OF 'PB_ACCESS_OBJECT'          
  21   20                       NESTED LOOPS                                    
  22   21                         NESTED LOOPS                                  
  23   22                           NESTED LOOPS                                
  24   23                             NESTED LOOPS                              
  25   24                               TABLE ACCESS (BY INDEX ROWID) OF 'SEIS_GEOM_SET'          
  26   25                                 INDEX (RANGE SCAN) OF 'I4_SEIS_GEOM_SET' (NON-UNIQUE)                                             
  27   24                               TABLE ACCESS (BY INDEX ROWID) OF 'SEIS_GEOM_SET'                                                    
  28   27                                 INDEX (RANGE SCAN) OF 'SEIS_GEOM_SET_I2' (NON-UNIQUE)                                             
  29   23                             TABLE ACCESS (BY INDEX ROWID) OF 'P_SEIS_GEOM_SUM'                                                    
  30   29                               INDEX (UNIQUE SCAN) OF 'UNQ_P_SEIS_GEOM_SUM1' (UNIQUE)                                              
  31   22                           TABLE ACCESS (BY INDEX ROWID) OF 'SEIS_DATA_SET'          
  32   31                             INDEX (RANGE SCAN) OF 'I1_SEIS_TO_NAV' (NON-UNIQUE)          
  33   21                         INDEX (RANGE SCAN) OF 'I_ACCESS' (NON-UNIQUE)          
  34    6               TABLE ACCESS (BY INDEX ROWID) OF 'SEIS_GEOM_SET'          
  35   34                 INDEX (UNIQUE SCAN) OF 'SEIS_GEOM_SET_S_PK' (UNIQUE)          
  36    5             TABLE ACCESS (BY INDEX ROWID) OF 'SEIS_GEOM_SET'          
  37   36               INDEX (UNIQUE SCAN) OF 'SEIS_GEOM_SET_S_PK' (UNIQUE)          
  38    4           TABLE ACCESS (BY INDEX ROWID) OF 'SEIS_DATA_SET'            
  39   38             INDEX (UNIQUE SCAN) OF 'SEIS_DATA_SET_S_PK' (UNIQUE)          
  40    3         TABLE ACCESS (BY INDEX ROWID) OF 'SEIS_PROC_ACT'              
  41   40           INDEX (UNIQUE SCAN) OF 'SEIS_PROC_ACT_S_PK' (UNIQUE)          
  42    2       INDEX (RANGE SCAN) OF 'INDX_OBJECT_S_1' (NON-UNIQUE)
Re: A single SQL command run for 8 minutes ?? Something wrong ?? [message #235119 is a reply to message #234687] Fri, 04 May 2007 02:22 Go to previous messageGo to next message
budiss
Messages: 8
Registered: May 2007
Location: Jakarta
Junior Member
set autotrace on statistics results :

Statistics
----------------------------------------------------------
       1883  recursive calls
       1480  db block gets
    1300201  consistent gets
      53822  physical reads
      37268  redo size
       2542  bytes sent via SQL*Net to client
        651  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          1  sorts (disk)
          8  rows processed
Re: A single SQL command run for 8 minutes ?? Something wrong ?? [message #235867 is a reply to message #234687] Tue, 08 May 2007 03:40 Go to previous messageGo to next message
budiss
Messages: 8
Registered: May 2007
Location: Jakarta
Junior Member
anybody can help me ??
Re: A single SQL command run for 8 minutes ?? Something wrong ?? [message #235894 is a reply to message #234687] Tue, 08 May 2007 05:39 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Do you have an index defined on SEIS_PROC_ACT_S column of
SEIS_PROC_ACT TABLE?

It's hard to advice anything without knowing.

HTH.
Michael


Re: A single SQL command run for 8 minutes ?? Something wrong ?? [message #235990 is a reply to message #234687] Tue, 08 May 2007 11:31 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
You should start with examining each view from the simplest ones to most complex. For each view also check whether uses best access (eg. INDEX SCAN where filtering big amount of rows by the condition on index).

Just two recommendations:
1) the WHERE condition of PB_SEIS_NAV_SEGM_BO
where TECHNICAL_OBJECT_T||'' = 'PB_SEIS_NAV_SEGM'
probably cannot use (possible) index on TECHNICAL_OBJECT_T. I would remove the concatenation with empty VARCHAR2 (''). The same situation is on PB_PROC_SEIS_SECTION and PB_SEIS_NAV_SEGM views. If you have function based index on that expression, just forget it. Or maybe optimizer is smart enough to use it. Just make a check.

2) the WHERE condition of PB_PROCESSING_JOB
where ACT_CLASS_NAME is null
or ACT_CLASS_NAME = '4D seismic processing'
and ( PRIVATE='N'
      or
      sys_context('PB_ENV','PB_ROLE')='PDO'
      or
      sys_context('PB_ENV','COMPANY_CODE')=PB_OWNER
    )
; I am afraid you miss brackets along the first two conditions (see the condition precedence, AND has bigger than OR).

Good luck.
Previous Topic: Grant Edit Privilege of Package to Another user
Next Topic: display mgrno, and salary exlude groups where sal<1000 sort in desc.
Goto Forum:
  


Current Time: Wed Dec 07 20:46:24 CST 2016

Total time taken to generate the page: 0.14594 seconds