Home » Other » Training & Certification » Multithreading (merged 3)
Multithreading (merged 3) [message #320595] Thu, 15 May 2008 10:38 Go to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member
Hi all,

Please let me know the details of the following.
1.What is Multi-Threading?
2. How it is useful to increase the Performance of a SQL query.( How it is useful
in Optimizing the query.
3. How we'll use MultiThread concept in Partitions and Using Processors.( For Eg.. If I want to use MAX threads=14 and 16 processors in this situation how we use PArtition concept and how it will improve the bufffer size of the CPU)

Thanks in advance
Re: MultiThreading [message #320597 is a reply to message #320595] Thu, 15 May 2008 10:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://www.google.com/search?hl=en&q=multithreading&meta=
http://www.google.com/search?hl=en&q=multithreading+oracle

Regards
Michel
Re: MultiThreading [message #320605 is a reply to message #320597] Thu, 15 May 2008 11:12 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member
Thank you Michel...

Can u please explain how it is useful to improve the buffer size of CPU?
Re: MultiThreading [message #320611 is a reply to message #320605] Thu, 15 May 2008 11:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is buffer size of CPU?

Regards
Michel
Re: MultiThreading [message #320618 is a reply to message #320611] Thu, 15 May 2008 12:18 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member
Before using Multithreading concept PCTused is 90% then it will effect performance of the query ..am I right?
So if we use Multithread concept we can increase the PCTfree means used is 75% and Memory buffer is 25% but before we have only 10% of free space..

I know theoritically but I don't know practically.Can u explain it pls..
Re: MultiThreading [message #320620 is a reply to message #320618] Thu, 15 May 2008 12:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I really don't understand what you posted.
For me it is a completly nonsense.
What PCTFREE and PCTUSED has to do with multithreading?
What is memory buffer?
free space of what?

And still don't know what is "buffer size of CPU".

Regards
Michel
Re: MultiThreading [message #320621 is a reply to message #320620] Thu, 15 May 2008 12:31 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member
PCTUSED means : Percentage of space used
PCTFREE means : Percentage of free space remain

if I execute this query because of no space in CPU it is taking more time.
if I use Multithreading concept PCTFREE will be increased,so it will increase the performance..

if u need more details pls let me knoww...
can I send u the code...
Re: MultiThreading [message #320624 is a reply to message #320621] Thu, 15 May 2008 12:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't more details I need explaination.

pctfree, pctused -> which space?
What is space in CPU?

I still don't understand what you're saying.

Explain me what you're thinking in the same way you'd explain it to your Mom.

Regards
Michel
Multithreading with partitioning files in oracle [message #320700 is a reply to message #320595] Fri, 16 May 2008 00:22 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member
Hi all,
I used Max threads=12 and 16 processors(files) with partitions for SQL query for improving the performance.How Multithread concept will work with this Processors when we partitioned the se files.
eg: we have 12 threads.
Threads : 1 2 3 4 ..... 12

Files : 1 2 3 4 .......12
13 14 15 16

if we partitioned like it will improve the performance of the quer...( this works fine in my situation...)

I know it will work like this.then my doubt is I know theoretically. can any one please explain me practically how it works?

Thank you.

[Updated on: Fri, 16 May 2008 00:24]

Report message to a moderator

Re: Multithreading with partitioning files in oracle [message #320703 is a reply to message #320700] Fri, 16 May 2008 00:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
can any one please explain me practically how it works?

It depends on the query/statement and test, post it.

Regards
Michel

[Updated on: Fri, 16 May 2008 00:37]

Report message to a moderator

Re: Multithreading with partitioning files in oracle [message #320721 is a reply to message #320703] Fri, 16 May 2008 01:34 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member
dbuser=SCOTT/TIGER
dbname=PRODDB
connect_string=$dbuser@$dbname
tableowner="DEVS"
TABLEOWNER=$(echo $tableowner | tr '[a-z]' '[A-Z]')

#. ${MMHOME}/config.env
OUT_DIR=/inbox/data

# Set it to number of CPU on the server
MAX_THREAD=12
TASK_RUNNING=0
PROGRAM_NAME='Store_Source'

get_partname()
{
  sqlplus -s $connect_string <<!
  set pages 0
  set feedback off
  set heading off
  SELECT PARTITION_NAME, PARTITION_POSITION
      FROM  DBA_TAB_PARTITIONS
       WHERE TABLE_NAME='ITEM_LOC'
      ORDER BY PARTITION_POSITION;
  exit;
!
}

create_part_flow()
{
cat > sorc/outFlow_${PROGRAM_NAME}_$1.xml - <<EOF1

export PROGRAM_NAME="item_sale_$1"

. \${MMHOME}config.env
. \${LIB_DIR}/lib.ksh


message "Program started ..."

\${RE_EXE} \${RE_OPTIONS} << EOF

<FLOW name="output.flow">
  \${DBREAD}
 <PROPERTY name="query">
      <![CDATA[
SELECT <Stmt>
   ]]>
    </PROPERTY>
    <OPERATOR type="convert">
         <PROPERTY name="convertspec">
            <![CDATA[
               <CONVERTSPECS>
                  <CONVERT destfield="Item_no" sourcefield="Item_no" newtype="int16">
                     <CONVERTFUNCTION name="int16_from_dfloat"/>
                     <TYPEPROPERTY name="nullable" value="false"/>
                  </CONVERT>
               </CONVERTSPECS>
            ]]>
         </PROPERTY>
         <OUTPUT name="temp.v"/>
   </OPERATOR>
   </OPERATOR>
   <OPERATOR type="export">
    <INPUT name="temp.v"/>
    <PROPERTY name="outputfile" value="\${DATA_DIR}/src_i_$1.dat"/>
    <PROPERTY name="schemafile" value="\${SCHEMA_DIR}/item_on_off_sale.schema"/>
  </OPERATOR>
</FLOW>
EOF

checkerror -e $? -m "Program failed - check \${ERR_FILE}"

# Remove the status file
if [[ -f \${STATUS_FILE} ]]; then rm \${STATUS_FILE}; fi

message "Program completed successfully"


[Updated on: Fri, 16 May 2008 03:37] by Moderator

Report message to a moderator

Re: MultiThreading [message #320723 is a reply to message #320624] Fri, 16 May 2008 01:42 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Quote:
Explain me what you're thinking in the same way you'd explain it to your Mom.
Nevermind, I'll come over and fix it for you. Wink
Re: Multithreading with partitioning files in oracle [message #320767 is a reply to message #320721] Fri, 16 May 2008 03:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the code but can you explain what it does and how did launch it and post the result you had in different cases.
Also how the table was created in these cases?

Regards
Michel
Re: Multithreading with partitioning files in oracle [message #320785 is a reply to message #320767] Fri, 16 May 2008 04:31 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member
the performance of the query is not good.So I want to use Multithread (Multi-processor) concept to increase the performance.for Eg : I have 8 processors and 6 threads.If I execute these 8 processors in the 6 threads then performance will become good..(am I right? it's my opinion...)
if it works how it works?please let me know..
Re: Multithreading with partitioning files in oracle [message #320789 is a reply to message #320785] Fri, 16 May 2008 04:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You don't answer to what I asked.

Regards
Michel
Multithreading_codess [message #321037 is a reply to message #320595] Sun, 18 May 2008 12:29 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member
Hi all,
Here I am posting the code which is related to multithreading concept. can any one please explain me the code what it is doin exactly..
dbuser=SCOTT/TIGER
dbname=ORACLE_DEV
connect_string=$dbuser@$dbname
tableowner="PRODDB"
TABLEOWNER=$(echo $tableowner | tr '[a-z]' '[A-Z]') 

#. ${MMHOME}/config.env
OUT_DIR=/inbox/data

# Set it to number of CPU on the server
MAX_THREAD=12
TASK_RUNNING=0
PROGRAM_NAME='Store_Source'

get_partname()
{
  sqlplus -s $connect_string <<!
  set pages 0
  set feedback off
  set heading off
  SELECT PARTITION_NAME, PARTITION_POSITION
      FROM  DBA_TAB_PARTITIONS
       WHERE TABLE_NAME='ITEM_LOCATION'
      ORDER BY PARTITION_POSITION;
  exit;
!
}

create_part_flow()
{
cat > source/outFlow_${PROGRAM_NAME}_$1.xml - <<EOF1

export PROGRAM_NAME="item_sale_$1"

. \${MMHOME}config.env
. \${LIB_DIR}/lib.ksh


message "Program started ..."

\${RETAIL_EXE} \${RETAIL_OPTIONS} << EOF

<FLOW name="output.flow">
  \${DBREAD}
    <PROPERTY name="query">
      <![CDATA[
SELECT 
          itl.loc                        store,
          itl.item                       sku,
          v.case_size                    order_pack,
          itl.onsale_date                onsale_date,
          itl.offsale_date               offsale_date,
          iem.status                     status
 FROM     item_mas       iem,
          (SELECT * from ITEM_LOCATION partition ($2)) itl,
          store             s,
          v_case_size       v   
 WHERE    iem.pack_ind                   = 'N'
   AND    iem.item_level                 = iem.tran_level
   AND    iem.status                     = 'A'
   AND    iem.forecast_ind               = 'Y'
   AND    iem.item                       = itl.item
   AND    itl.loc_type                   = 'S'
   AND    iem.item                       = vcs.item
   
   AND NVL(s.STORE_CLOSE_DATE,'01-JAN-9999')>=TO_DATE('\${ITEMDATE}', 'YYYYMMDD')       
      ]]>
    </PROPERTY>
    <OPERATOR type="convert">
         <PROPERTY name="convertspec">
            <![CDATA[
               <CONVERTSPECS>
                  <CONVERT destfield="ORDER_PACK" sourcefield="ORDER_PACK" newtype="int16">
                     <CONVERTFUNCTION name="int16_from_dfloat"/>
                     <TYPEPROPERTY name="nullable" value="false"/>
                  </CONVERT>
               </CONVERTSPECS>
            ]]>
         </PROPERTY>
         <OUTPUT name="temp.v"/>
   </OPERATOR>
   </OPERATOR>
   <OPERATOR type="export">
    <INPUT name="temp.v"/>
    <PROPERTY name="outputfile" value="\${DATA_DIR}/dm0_src_i_$1.dat"/>
    <PROPERTY name="schemafile" value="\${SCHEMA_DIR}/item_on_off_sale.schema"/>
  </OPERATOR>
</FLOW>
EOF

checkerror -e $? -m "Program failed - check \${ERR_FILE}"

# Remove the status file
if [[ -f \${STATUS_FILE} ]]; then rm \${STATUS_FILE}; fi

message "Program completed successfully"

# cleanup and exit
items_terminate 0

EOF1
}

create_nopart_flow()
{
cat > outFlow_${PROGRAM_NAME}.xml - <<EOF
<FLOW name="output.flow">
  <OPERATOR type="oraread">
    <PROPERTY name="query">
      <![CDATA[
           <![CDATA[
SELECT 
          itl.loc                        store,
          itl.item                       sku,
          v.case_size                    order_pack,
          itl.onsale_date                onsale_date,
          itl.offsale_date               offsale_date,
          iem.status                     status
 FROM     item_mas       iem,
          (SELECT * from ITEM_LOCATION partition ($2)) itl,
          store             s,
          v_case_size       v   
 WHERE    iem.pack_ind                   = 'N'
   AND    iem.item_level                 = iem.tran_level
   AND    iem.status                     = 'A'
   AND    iem.forecast_ind               = 'Y'
   AND    iem.item                       = itl.item
   AND    itl.loc_type                   = 'S'
   AND    iem.item                       = vcs.item
   
   AND NVL(s.STORE_CLOSE_DATE,'01-JAN-9999')>=TO_DATE('\${ITEMDATE}', 'YYYYMMDD')
            ]]>
    </PROPERTY>
    <OUTPUT name="temp.v"/>
  </OPERATOR>

  <OPERATOR type="export">
    <INPUT name="temp.v"/>
    <PROPERTY name="outputfile" value="dm0_src_i_$1.dat"/>
  </OPERATOR>
</FLOW>
EOF
}

create_full_flow()
{
cat > source/item_sale_multi.xml << EOF1

export PROGRAM_NAME="item_sale_multi"

. \${MMHOME}/config.env
. \${LIB_DIR}/lib.ksh

ON_SALE_OUTPUT_FILE=\${DATA_DIR}/dm0_onseffdt_.txt
ON_SALE_OUTPUT_SCHEMA=\${SCHEMA_DIR}/item_on_sale.schema
OFF_SALE_OUTPUT_FILE=\${DATA_DIR}/dm0_ofseffdt_.txt
OFF_SALE_OUTPUT_SCHEMA=\${SCHEMA_DIR}/item_off_sale.schema

#export RFX_DEBUG=1

# input files:

ONOFF_DATE=\${DATA_DIR}/dm0_onseffdt_ofseffdt.dat
ONOFF_DATE_SCHEMA=\${SCHEMA_DIR}/item_on_off_sale.schema

message "Program started ..."

\${RETAIL_EXE} \${RETAIL_OPTIONS} << EOF

<FLOW name="Store_Source.flw">

   <OPERATOR  type="import">
      <PROPERTY  name="inputfile" value="\${ONOFF_DATE}"/>
      <PROPERTY  name="schemafile" value="\${ONOFF_DATE_SCHEMA}"/>
       <OPERATOR type="copy">
         <OUTPUT name="on_off_sale.v"/>
       </OPERATOR>
   </OPERATOR>

<!--START_INS_C2.1-->
   <OPERATOR type="copy">
      <INPUT  name="on_off_sale.v"/>
      <OUTPUT name="on_sale_out.v"/>
      <OUTPUT name="off_sale_out.v"/>
   </OPERATOR>
<!--END_INS_C2.1-->

   <OPERATOR type="fieldmod">
      <INPUT name="on_sale_out.v"/>
<!--START_DIS_C2.1
      <PROPERTY name="rename" value="ON_SALE_EFFECTIVE_DATE=STATUS_UPDATE_DATE"/>
END_DIS_C2.1-->

<!--START_INS_C2.1-->
      <PROPERTY name="rename" value="ON_SALE_EFFECTIVE_DATE=ONSALE_DATE"/>
<!--END_INS_C2.1-->

      <OPERATOR type="export">
         <PROPERTY name="outputfile" value="\${ON_SALE_OUTPUT_FILE}"/>
         <PROPERTY name="schemafile" value="\${ON_SALE_OUTPUT_SCHEMA}"/>
      </OPERATOR>
   </OPERATOR>

   <OPERATOR type="fieldmod">
      <INPUT    name="off_sale_out.v"/>
<!--START_DIS_C2.1
      <PROPERTY name="rename" value="OFF_SALE_EFFECTIVE_DATE=STATUS_UPDATE_DATE"/>
END_DIS_C2.1-->

<!--START_INS_C2.1-->
      <PROPERTY name="rename" value="OFF_SALE_EFFECTIVE_DATE=OFFSALE_DATE"/>
<!--END_INS_C2.1-->
      <OPERATOR type="export">
         <PROPERTY name="outputfile" value="\${OFF_SALE_OUTPUT_FILE}"/>
         <PROPERTY name="schemafile" value="\${OFF_SALE_OUTPUT_SCHEMA}"/>
      </OPERATOR>
   </OPERATOR>
</FLOW>
EOF

checkerror -e 0 -m "Program failed - check ${ERR_FILE}"

# Remove the status file
if [[ -f \${STATUS_FILE} ]]; then rm \${STATUS_FILE}; fi

message "Program completed successfully"

# cleanup and exit
items_terminate 0
EOF1
}

# Remove all individual xml files
rm outFlow_${PROGRAM_NAME}*.xml


NO_OF_PART=`get_partname $TABLEOWNER |wc -l`
if [ ${NO_OF_PART} -eq 0 ]; then
  echo Table not partitioned
  create_nopart_flow 
  time retail -c ${1} -f outFlow_${PROGRAM_NAME}.xml
else
  echo Table partitioned
  get_partname $TABLEOWNER |while read PARTNAME THREAD_SEQ 
  do
     echo PartName: $PARTNAME   POS: $THREAD_SEQ
     create_part_flow $THREAD_SEQ  ${PARTNAME} 
     time /scripts/retail_aim.sh outFlow_${PROGRAM_NAME}_${THREAD_SEQ}.xml&
     echo Running outFlow_${PROGRAM_NAME}_${THREAD_SEQ}.xml ....
     sleep 1
     #TASKS_RUNNING=`ps -ef|grep retail|grep -v java|grep -v grep|wc -l`
     TASKS_RUNNING=`ps -ef |grep ${PROGRAM_NAME} |grep -v java|grep -v grep |wc -l`
    echo Running: ${TASKS_RUNNING} Max: ${MAX_THREAD}
     while [ "${TASKS_RUNNING}" -ge "${MAX_THREAD}" ]; do
        sleep 2
        TASKS_RUNNING=`ps -ef |grep ${PROGRAM_NAME} |grep -v java|grep -v grep |wc -l`
     done
  done
  wait
  #Concatenate all files
  cat ${OUT_DIR}/dm0_src_i_*.dat > ${OUT_DIR}/dm0_onseffdt_ofseffdt.dat
  if [ $? -eq 0 ]; then
     rm  ${OUT_DIR}/dm0_src_i_*.dat
  fi
  create_full_flow

  time scripts/retil_aim.sh item_sale_multi.xml 
fi
Re: Multithreading_codess [message #321040 is a reply to message #321037] Sun, 18 May 2008 12:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Laughing
This is the code you posted last week and I asked you what it is doing, now you ask us the same thing but isn't it YOUR code?

Regards
Michel
Re: Multithreading_codess [message #321041 is a reply to message #321040] Sun, 18 May 2008 12:54 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member
Michel

The item_sale.ksh module takes 30 minutes (on average) to complete. This script is on the AIM batch release critical path and currently runs too long. We need to tune this script to ensure that we minimize the delay in releasing the AIM batch.

So I am going to resolve this issue using the posted script.
SO I want to understood what it is doin exactly..Then hope I can resolve this issue...So please explain me the issue.

If it is not u required pls let me know.
Thank you.
Re: Multithreading (merged 3) [message #321042 is a reply to message #320595] Sun, 18 May 2008 13:03 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Interesting code
# Set it to number of CPU on the server
MAX_THREAD=12

but I don't see where "MAX_THREAD" is ever used again in posted code.

Ready, Fire, AIM!
Re: Multithreading (merged 3) [message #321043 is a reply to message #321042] Sun, 18 May 2008 13:14 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member
Michel,

without this can u explain me the remaining please...
Re: Multithreading (merged 3) [message #321044 is a reply to message #320595] Sun, 18 May 2008 13:22 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

'u' is NOT a member of this forum; never has been & never will be!
Re: Multithreading_codess [message #321085 is a reply to message #321041] Mon, 19 May 2008 00:52 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member
Michel,

can you please explain me the code briefly...
Re: Multithreading_codess [message #321099 is a reply to message #321085] Mon, 19 May 2008 01:28 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know if you are talking to Ana or me (as you called Ana, Michel) but for myself I will not spend time to try to understand YOUR code, espcially when you don't give the whole informations that allow me to understand.

Regards
Michel
Previous Topic: pl/sql block to determine the top employees
Next Topic: table indexed
Goto Forum:
  


Current Time: Thu Mar 28 16:17:11 CDT 2024