Re: Improve query performance

Phil Singer <psinger1_at_chartermi.net> wrote in message news:<3FE24E5C.9D639888_at_chartermi.net>...
> 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.

this dont use create table as in production has been taken to an extreme by people who dont actually test what they read. you dont use create table as if you are doing it as part of a regularly run transactional system. As I said before if this is just a nightly batch process, the recursive sql is irrelevant(because its not running over and over and over again which is what causes the serialization). so create table as works fine. all you have to do is consider whether 'nologging' is acceptable or not. Depends on your environment.

dont just quote stuff and actually test. there are multiple types of scaling. Scaling by increasing users, scaling to increase throughput, and a variety of 'batch scaling' techniques. all sizes do NOT fit all.

however, the questions below are relavent. Though he probably doesnt know what the explain plan is or he wouldnt post it.
>
> 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
Received on Mon Dec 22 2003 - 09:53:02 CST