Re: Improve query performance

Ryan Gaffuri wrote:
>
> Ursula Lee <ursula.lee_at_thales-is.com> wrote in message news:<br466r$hua19_at_imsp212.netvigator.com>...
> > Hi All,
> >
> > I need build the small tables extracting from the big tables called
> > HISVALUE. Problem is the build up time is extremely slow as the
> > insertion into the big tables will occur continuously.
> >
> > (That is Files loaded into table HISVALUE --> then table TEMP_SENSOR)
> >
> > Thus, I need to choose a build up time to those small tables, e.g. every
> > 15 min that won't affect the original insertion.
> >
> > Question:
> > 1. How can I improve the query performance? Any suggestion on how to
> > replace 'NOT EXISTS' in the following query?
> >
> > 2. When should I build that small tables? I have at least 6 of those
> > tables retrieving from the same big table (HISVALUE).
> >
> > Here is the query:
> >
> > INSERT INTO temp_sensor
> > (SELECT hisv.DATEANDTIME,
> > substr(hisv.POINTNAME, instr(hisv.POINTNAME, ':') +
> > 1,instr(hisv.POINTNAME, ':',1,2) - instr(hisv.POINTNAME, ':') - 1)
> > ,substr(hisv.POINTNAME, instr(hisv.POINTNAME, ':',1,2) +
> > 1,instr(hisv.POINTNAME, ':',1,3) - instr(hisv.POINTNAME, ':', 1, 2)
> > - 1)
> > ,eqlab.EQUIP_LABEL, hisv.VALUE, eqlab.TYPE
> > FROM hisvalue hisv, equipment_label eqlab
> > WHERE substr(hisv.POINTNAME, 1, instr(hisv.POINTNAME,
> > ':',1,4)-1) = eqlab.EQUIP_ALIAS
> > and (eqlab.EQUIP_LABEL like 'TSN%'
> > or eqlab.EQUIP_LABEL like 'RST%')
> > and not exists (select 1 from temp_sensor ts
> > where ts.DATEANDTIME = hisv.DATEANDTIME
> > and ts.LOCATION = substr(hisv.POINTNAME,
> > instr(hisv.POINTNAME, ':') + 1,instr(hisv.POINTNAME, ':',1,2) - instr(his
> > v.POINTNAME, ':') - 1)
> > and ts.SYSTEM = substr(hisv.POINTNAME,
> > instr(hisv.POINTNAME, ':',1,2) + 1,instr(hisv.POINTNAME, ':',1,3) - instr(h
> > isv.POINTNAME, ':',1, 2) - 1)
> > and ts.EQUIP_LABEL = eqlab.EQUIP_LABEL
> > and ts.VALUE = hisv.VALUE
> > and ts.TYPE = eqlab.TYPE
> > )
> > );
> >
> > Regards,
> > Ursula
>
> get rid of the insert. Do a 'create table as'. do nologging if you can
> afford to not have the data recoverable(if this is a nightly load, its
> fine). if ytou can scale it use the parallel option.
>
> insert is slow. especially if you have alot of indexes on the table.
> Then use dbms_job and create all the indexes on your new table in
> parallel. Just submit them as jobs. you can create those in parallel
> also.
>
> your query doesnt tell me anything. there is no explain plan. I dont
> know anything about the number of records in each table and other
> stuff.
>
> easiest way to speed up an insert is to turn it into a create table
> as. Now keep in mind, if you are doing alot of small inserts, do not
> use create table as statements, it wont scale up to accomodate a large
> number of people doing that. but for one or a small number of loads
> its not a problem.

No, you don't want to do a create table in production. And, in my experience, I don't think the insert statement alone would be a problem, at least for a 'small' table.

My guess (and I agree, all one can do is guess here) is that the query driving the insert is slow. Would like to see this:

1. How much time does the insert take? (set timing on!)
2. How much time does the select take? (take off the insert!)
3. What is the Explain Plan?
4. What are the indexes and constraints on the table?
5. What version of Oracle?

The select probably needs to be re-written without the subquery, but I know much too little about the data to take a guess as to whether that is possible.

From the

-- 
Phil Singer                |    psinger1ATchartermiDOTnet
Oracle DBA
Remove the Obvious to reply