Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Nov 2007
    Location
    Connecticut
    Posts
    40

    Talking Unanswered: dbms_job and execute immediate

    To make a long story short , I am attemting to build indexes as a precursor to excahnge partition. I need to build as quicky as possible. I currently have a proc that works creating indexes dynamically using parallel option , but all my indexes are created serially. Thus, I decided to try to use dbms_job

    For brevity -- heres the code snippet from the proc as a pl/sql block to illustrate what Iam trying to do

    Code:
    declare
       job_number  number;
    begin
      execute immediate 'dbms_job.submit (job_number,'begin execute immediate ''CREATE UNIQUE INDEX OES2_IDX_1 ON ORDER_EVENT_STAGE2 (EVENT_DATE_ID,EVENT_ID) nolo
    gging parallel 4'';end; ')';
    end;
    /

    I get the following error --

    ERROR at line 4:
    ORA-06550: line 4, column 51:
    PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following:
    . ( * @ % & = - + ; < / > at in is mod not rem return
    returning <an exponent (**)> <> or != or ~= >= <= <> and or
    like between into using || bulk
    The symbol ";" was substituted for "BEGIN" to continue.
    ORA-06550: line 4, column 77:
    PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:
    . ( * @ % & = - + ; < / > at in is mod not rem return
    returning <an exponent (**)> <> or != or ~= >= <= <> and or
    like between into using || bulk


    am I insane attempting top execute immediate dbms_job to execute immediate
    dont answer that :-)

    any help appreciated

    Note that the dbms_job alobe does execute from sql*plus
    Thanks in advance
    Brendan

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    At some point it time the load on the system will consume 100% of the most critical resource (CPU cycles, RAM, disk I/Os) & having more jos/process/session competing for that resource will result in LONGER elapsed time overall.

    When a single index using the parallel option is being created, are you hitting 100% consumption on any system resource?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2007
    Location
    Connecticut
    Posts
    40

    dbms_job and execute immediate

    Agreed , thanks for the response. I did think of the system resource utlilization but I thought I could throttle this a bit since the job_queue_processes init.ora is set to 2. Thus, I can have a max of 8 processes running at a time using parallel 4 for each index build. I have simulated this in ksh and have seen great improvements in overall elapsed time to build my indexes. I could use ksh, but Im trying to have this done all in plsql

    Thanks again

    brendan

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    What is OS name & version?
    What is Oracle version to 4 decimal places?
    How many actual hard disks are holding Oracle datafiles?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Nov 2007
    Location
    Connecticut
    Posts
    40
    SunOS sstm8452dor 5.8 Generic_117350-35 sun4u sparc SUNW,Sun-Fire-V440
    Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
    Corporate SAN using EMC disks

    anacedent -- I'm not sure how the above is relevant to my original question. I can only ascertain that you are questioning what I have already conveyed to you -- which is that I have already tested simultaneous create index commands to finish all my work faster. I benchmark everything first (as Tom Kyte would say -- test test test) -- I've seen your responses on this forum and many times they can be very helpful (you are obviously a bright creative thinker), but I sense a tone that is condescending and a bit arrogant -- feel free not to respond to my posts if you like as Im sure that will be your response to my criticism.

    If anyone can offer real help on my original question , it would be greatly appreciated

    Thanks
    Brendan

  6. #6
    Join Date
    Nov 2007
    Posts
    13
    Try to create a procedure to do this job, so you create a job to call this procedure.
    I don't know why you need to recreate this index. there are any problem?

    ex:

    /********** create your procedure to rebuild you index. **********************/
    create or replace procedure crtindex
    cmd varchar2(3200);
    is
    begin
    cmd := 'CREATE UNIQUE INDEX OES2_IDX_1 ON ORDER_EVENT_STAGE2 (EVENT_DATE_ID,EVENT_ID) nologging parallel 4';
    execute immediate (cmd);
    end;
    /********* how to create a job to execute your procedure ***********/
    declare
    job_num number;
    begin
    dbms_job.submit(job_num
    ,'crtindex();'
    ,sysdate+1
    ,'trunc(sysdate+1)+ 22/24');
    end;
    /
    commit;
    /

  7. #7
    Join Date
    Nov 2007
    Location
    Connecticut
    Posts
    40

    dbms_job

    Thanks Johnny , I appreciate the response. But my problem is a bit more complicated than what I orginally conveyed as to shorten the code I attached. I am building the indexes dynamically, thus I cant hardcode the create index command as per your example -- the full procedure code is below : I am attemting to create these indexes dynamically

    Code:
                      /******************************************************************************
                       NAME:       CREATE_STAGE2_INDEXES
                       PURPOSE:    Loop thru the tables in control table and create same indexes that
                                   exist on _main tables for the stage2 tables
                                   This procdure should be called after call to LOAD_STAGE2
    
                       REVISIONS:
                       Ver        Date        Author           Description
                       ---------  ----------  ---------------  ------------------------------------
    
                       1.0        11/28/07    B. Monahan         Initial Creation
    
                       NOTES:
                    *******************************************************************************/
    
    PROCEDURE CREATE_STAGE2_INDEXES
    IS
    /**********************
    * DECLARE VARIABLES
    **********************/
       cursor get_tables_cur is select * from CONTROL_TABLE where LOAD_FLAG='Y';
       cursor get_indexes_col_cur (l_table_name varchar2, l_index_name varchar2) is select * from user_ind_columns
                                                             where table_name = l_table_name
                                                             and index_name = l_index_name order by column_position ;
       cursor get_indexes_cur (l_table_name varchar2) is select * from user_indexes where table_name = l_table_name;
       l_table_name CONTROL_TABLE.table_name%TYPE;
       l_index_name user_indexes.index_name%TYPE;
       exec_string varchar2(200);
       err_num ERRORLOGS.ERRORCODE%TYPE;
       err_msg ERRORLOGS.ERRORMSG%TYPE;
       job_number number;
       null_job_number number;
    
    --
    BEGIN
    --
    /***************************************************************************
    * GET THE STAGE TABLE NAMES  FROM CONTROL TABLE AND CONVERT TO EQUIVALENT
    * NAMES FOR THE _MAIN TABLES
    ***************************************************************************/
       FOR get_table_row in get_tables_cur LOOP
          l_table_name:=substr(get_table_row.table_name,1,length(get_table_row.table_name)-6)||'MAIN';
    /****************************************************************
    * FOR EACH _MAIN TABLE , GET THE INDEX NAMES
    * AND START TO BUILD THE CREATE INDEX COMMAND IN exeec_string
    * CHECK FOR UNIQUENESS AND BITMAP INDEXES
    ****************************************************************/
          FOR get_index_row in get_indexes_cur(l_table_name) LOOP
             exec_string:='dbms_job.submit (job_number,'||''''||'begin execute immediate'||''''||''''|| 'CREATE';
    --         exec_string:='CREATE';
             IF get_index_row.uniqueness='UNIQUE' THEN
                exec_string:=exec_string||' UNIQUE';
             END IF;
    --
             IF get_index_row.index_type='BITMAP' THEN
                exec_string:=exec_string||' BITMAP';
             END IF;
    --
             exec_string:=exec_string||' INDEX '||get_table_row.TNAME_SHORT||'_IDX_'||get_indexes_cur%ROWCOUNT||' ON '
                                     || get_table_row.table_name||' (';
    /***************************************************************************
    * FOR EACH INDEX NAME , GET THE COLUMN NAMES (possibly concatenated index)
    * AND CONTINUE TO BUILD OUT THE CREATE INDEX COMMAND
    ***************************************************************************/
             FOR get_col_index_row in get_indexes_col_cur (l_table_name,get_index_row.index_name) LOOP
                exec_string:=exec_string ||get_col_index_row.column_name||',';
             END LOOP;
             exec_string:=rtrim(exec_string,',');
             exec_string:=exec_string||') nologging parallel 4' ||''''||''''||';end;'||''''||');';
             dbms_output.put_line (exec_string);
    /***********************************************************
    * EXECUTE THE CREATE INDEX COMMAND ON THE STAGE TABLES
    ***********************************************************/
             execute immediate 'begin ||exec_string||end;';
    --         dbms_job.submit (job_number,
    --                               'begin execute immediate ''exec_string''; end;');
             dbms_job.submit (null_job_number,'null;',sysdate,null,false);
             dbms_job.run (null_job_number);
    
          END LOOP;
       END LOOP;
    /******************
    * EXCEPTION BLOCK
    ******************/
       EXCEPTION WHEN OTHERS THEN
          err_num := SQLCODE;
          err_msg := SUBSTR(SQLERRM, 1, 100);
          insert into ERRORLOGS (MODULENAME,ERRORCODE,ERRORMSG) values ('CREATE_STAGE2_INDEXES',err_num,err_msg);
          commit;
          raise;
    END CREATE_STAGE2_INDEXES;

  8. #8
    Join Date
    Nov 2007
    Posts
    13
    I'm not understand your problem yet. why you need recreate this indexes? your table definition is mutant? you use alter table frequently? a analyze table don't solve your problem?
    I have a table on my BD with 42000000 rows and a analyze solve my problems.

    and if you need more performance if this is the case, try to use partition table. and indexes.

  9. #9
    Join Date
    Nov 2007
    Location
    Connecticut
    Posts
    40
    Sorry Johnny , In my last post I neglected to answer that question. I am loading data into a stage table that will eventually be used in an exchnage partition command -- Then sequence of my scripts (for best performance ,loads take place with no indexes) are as follows

    1) drop stage table indexes
    2) load data into stage tables (insert /*+append) into bla bla (select from 3) table@remotedb)
    3) create indexes on stage table to match part table I will exchange with
    4) exec exchange part command with indexes no validation

  10. #10
    Join Date
    Nov 2007
    Posts
    13
    Mad,

    Try :

    1) disable the index (don't drop)
    2) Load your data from you table
    3) enable your indexes.
    4) analyze table

    when you load data, you don't need indexes in this part, neder for table destiny nor for table source, because you make a full table scan, and in this case oracle don't use index.

    if you can explain with example i can help you more.

    hope to be helpfull

    JonnyW.

  11. #11
    Join Date
    Nov 2007
    Posts
    13
    sorry i make a mistake, i confuse indexes with trigguers.
    Indexes do not be disabled. sorry.

  12. #12
    Join Date
    Nov 2007
    Location
    Connecticut
    Posts
    40
    Johnny

    I was just going to Metalink to check enable / disable cuz I had thought that would only work for constraints :-) -- You got me thinking though

    The reason I drop and recreate is that I need to ensure that the indexes for the partitioned table are always the same as the stage table before the ex part command -- So if we decide to add a new index to the part table , the stage table will auto-build the same indexes and I dont need to be concerned about new index creation -- Hope that makes sense
    Brendan

  13. #13
    Join Date
    Nov 2007
    Posts
    13
    I have a idea!

    1) create a table cmd_to_execute (index varchar2(1000),command varchar2(3200));

    2) make a function that build a command "create index on table. bla bla bla..." for each table do you want to drop the index

    3) insert this comand in that table you alredy create ,cmd_to_execute, for each index from each table.

    4) after that drop the indexes

    5) start load data.

    6) start a select with execute immediate a simple PL/SQL that execute your command recorded in your table.

    7) be happy.

    Why do this way? because you can check the command in each line from your table and see any erros in create indexes. and you can manage and rapair you function that creates the command.

    I hope this works.

    Now I test a load data with temp tablespace and redolog files in a disk memory (shm). this is fast. but if your system hangs or your power suply the DB is lost.

  14. #14
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Of course you can disable an index.

    alter index index_name unusable;

    Do your import

    alter index index_name rebuild;


    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  15. #15
    Join Date
    Nov 2007
    Location
    Connecticut
    Posts
    40

    unusable

    All -- thanks for the feedback

    beilstwh -- have u ever tried to insert into a table with index status "unusable" -- I have -- thus the key word "unusable"

    Code:
    SQL> create table text (col1 varchar2(1));
    
    Table created.
    
    SQL> create index text_idx1 on text (col1);
    
    Index created.
    
    SQL> insert into text values ('1');
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> alter index text_idx1 unusable;
    
    Index altered.
    
    SQL>  insert into text values ('1');
     insert into text values ('1')
    *
    ERROR at line 1:
    ORA-01502: index 'ORACLE.TEXT_IDX1' or partition of such index is in unusable
    state
    
    
    SQL>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •