Task running slowly

I added functionality that checks a table, that I can only read from, and it has 27 million records and it has added a lot of time to my process. I was thinking on possibly using my source query to get all the records I need and then put those records into a local table. From that local table(typically 1.7 million records) I can bring in the ID of this huge table. That's all it does...It checks for an ID passing in parameters from the source query and if it has an ID I mark the Destination column as 1 or 0....
I put all my source query records into a local table and I was thinking maybe I can put the ID of this other large table into this local table as well? When I do a Left Join it gives me back way more than the 1.7 I already have and that's the number I need from the source query. All I want to do is add a field into this temp table from another large table where the POLNBRs equal...

---Here is my query to make my local table
INSERT INTO COVERAGETEST(
CARRID, POLNBR , COV_EFFDT , COV_EXPDT , CVG_ID , CVG_POL_SEQ , STATUS , CARR_ISSUING_ADDR_TYPE , CARR_ISSUING_STREET , CARR_ISSUING_CITY , CARR_ISSUING_STATE_CD , CARR_ISSUING_ZIP , WRAP_UP_IND
, TXN_CODE , LCF_IND , NAME, e.ENDORSE_ID)
SELECT c.CARRID, c.POLNBR , c.COV_EFFDT , c.COV_EXPDT , c.CVG_ID , c.CVG_POL_SEQ , c.STATUS , c.CARR_ISSUING_ADDR_TYPE , c.CARR_ISSUING_STREET , c.CARR_ISSUING_CITY , c.CARR_ISSUING_STATE_CD , c.CARR_ISSUING_ZIP
, c.WRAP_UP_IND , c.TXN_CODE , c.LCF_IND , car.NAME  
from COVERAGE c
INNER JOIN CARR car ON c.CARRID = car.CARRID
WHERE c.COV_EXPDT > '25-JUN-09'  AND (STATUS != 'V')      
ORDER BY c.CARRID ASC, c.COV_EFFDT ASC;

--This yields the 1.7 records

--I want to add the ENDORSEID below to the query above, if I can....
--I tried a LEFT JOIN But it obviously returned me many more rows then I was looking for
SELECT ENDORSE.ENDORSE_ID from ENDORSE where ENDORSE.POLNBR= :pPOLNBR AND ENDORSE.COV_EFFDT= :pCOV_EFFDT AND ENDORSE.ENDORSE_CD_NBR = '290307'

When I put this in my process the way it is now it added 2 hours to a 2.5 hour process. Im assuming its because of the large table being queried.

I really need help with this one.
Any help would really be appreciated!!
THANKS!
jknj72Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Not fully following what you are wanting.  I get that you want a column from ENDORSE added to the current query but I'm not seeing any common columns to join on.

What are the join columns between the tables involved when you had them joined?

What did the explain plan look like?
0
johnsoneSenior Oracle DBACommented:
You seem to create a temporary table called COVERAGETEST, but that table is not involved in the query you are saying takes time.

Size of the table should not make a difference in the amount of time the query takes to run.  Especially for what you are talking about (essentially checking for existence of a record).  You issue is going to be indexing.  You are missing an index, or you have an unusable index.  In fact, your entire existence check should be satisfied using an index only.

As the queries you posted don't seem related, I'm not sure anyone can suggest a proper index.  Posting the real query with the explain plan would be most helpful.
0
jknj72Author Commented:
The common fields are the POLNBR and the COVEFFDT which is what it uses from the source to get the existence of the record in the second query. First query runs and then loops through the recorset and checks for the ENDORSE record using the COVERAGE record as parameters
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

slightwv (䄆 Netminder) Commented:
I'm now more confused...

So you don't want to join the ENDORSE query to the main query?

The first query you posted references e.ENDORSE_ID (FYI:  You don't use table aliases in the column list of an insert) so I took that you wanted to merge the two queries into one.

Now you talk about running the first query, then checking for the existence.  That means two queries to me?

When I see 'existence' I see more of a Yes/No decision which should only take one row.  If so, the query against the ENDORSE table only needs one row returned.
0
jknj72Author Commented:
I didn't say I didn't want the join Im just not sure I can get back the same amount of records as the source query with the Endorse table included in it? That's why Im here to see if there is a way of getting the second query joined to the first and see if there is a EndorseID or not for that record. And yes, I am looking for the existence of the EndorseID
0
slightwv (䄆 Netminder) Commented:
>>to see if there is a way of getting the second query joined to the first

But you didn't post the sample where you attempted to join them.

As long as the tables have the correct columns, you should be able to join them.

I cannot say the correct way to do the join because I don't know your tables...

If you only need one value back, a Y/N, 1/0, etc..., make the ENDORSE query only return one row.  You might be able to just add  "and rownum=1" to the where clause?  That might be enough to make it run faster.

As we have also mentioned:  indexes.  Check what is there, what you think you should use and what ones aren't being used.
0
johnsoneSenior Oracle DBACommented:
This is a total guess on my part here.  I expect that it is incorrect, but there is a chance it may not be.

It isn't going to address performance as you haven't given any plans or index information, but if the indexes are there, it should be OK.
INSERT INTO coveragetest 
            (carrid, 
             polnbr, 
             cov_effdt, 
             cov_expdt, 
             cvg_id, 
             cvg_pol_seq, 
             status, 
             carr_issuing_addr_type, 
             carr_issuing_street, 
             carr_issuing_city, 
             carr_issuing_state_cd, 
             carr_issuing_zip, 
             wrap_up_ind, 
             txn_code, 
             lcf_ind, 
             name, 
             endorse_id) 
SELECT c.carrid, 
       c.polnbr, 
       c.cov_effdt, 
       c.cov_expdt, 
       c.cvg_id, 
       c.cvg_pol_seq, 
       c.status, 
       c.carr_issuing_addr_type, 
       c.carr_issuing_street, 
       c.carr_issuing_city, 
       c.carr_issuing_state_cd, 
       c.carr_issuing_zip, 
       c.wrap_up_ind, 
       c.txn_code, 
       c.lcf_ind, 
       car.name, 
       Nvl2(Min(edr.endorse_id), 1, 0) 
FROM   (coverage c 
        inner join carr car 
                ON c.carrid = car.carrid) 
       left outer join endorse edr 
                    ON edr.polnbr = c.polnbr 
                       AND edr.cov_effdt = c.cov_effdt 
WHERE  c.cov_expdt > '25-JUN-09' 
       AND ( status != 'V' ) 
GROUP  BY c.carrid, 
          c.polnbr, 
          c.cov_effdt, 
          c.cov_expdt, 
          c.cvg_id, 
          c.cvg_pol_seq, 
          c.status, 
          c.carr_issuing_addr_type, 
          c.carr_issuing_street, 
          c.carr_issuing_city, 
          c.carr_issuing_state_cd, 
          c.carr_issuing_zip, 
          c.wrap_up_ind, 
          c.txn_code, 
          c.lcf_ind, 
          car.name; 

Open in new window

Keep in mind you are adding a join, so it will take more time.  You cannot do more work and have no cost.

I removed the ORDER BY in the subquery.  It doesn't make sense and just wastes time.
0
Mark GeerlingsDatabase AdministratorCommented:
"When I do a Left Join it gives me back way more than the 1.7 I already have".
That's what a "left join" does by design: it brings back *ALL* records from the other table, whether they have matching values, or not.

"All I want to do is add a field into this temp table from another large table where the POLNBRs equal"
I think a nested select from the large table can do that for you.  My question: does the other large table have at least one matching record for every record in your 1.7-million row table?  Or, are there some records in this table that have no matching records in the other table?

If there is at least one matching record, you could use a simple (inner or standard join). This assumes of course, that you have column values available in your smaller table that match a good index in the larger table.

If the larger table does not have matching records for all records in your smaller table, then you have to use a nested select, not an inner or outer join.  That would look something like this:

SELECT c.CARRID, c.POLNBR ,  [additional columns...], nvl((select 1 from ENDORSE
   where ENDORSE.POLNBR= c.POLNBR
   AND ENDORSE.COV_EFFDT= c.COV_EFFDT
   AND ENDORSE.ENDORSE_CD_NBR = '290307'
   and rownum = 1),0)
 from COVERAGE c
 INNER JOIN CARR car ON c.CARRID = car.CARRID
 WHERE c.COV_EXPDT > '25-JUN-09'  AND (STATUS != 'V')      
 ORDER BY c.CARRID ASC, c.COV_EFFDT ASC;

This will return 1 (one) if a matching record is found in the endorse table, otherwise the "nvl" will supply a 0 (zero).
0
jknj72Author Commented:
Johnson - I should have mentioned that yes I did put the 1.7 mi records from my orig source(Coverage table)  into the Temp local table(COVERAGETEST table) to see if running locally would help the process time. So I am running off that to see if it helps. I guess I should also have pointed out that the Coverage and Endorse tables are heavily indexed and I am limited to what I can do on the Prod side. But I have seen the table and indexes so I know they are there. I ran the process this morning without the Endorse table logic and it went back to the 2:14 minute run time. I also thought about another check I do that looks at the Endorse table, which prob makes the whole process take the time that it does. I was thinking if I could add my EndorseID check to the existing logic I already do so maybe it wouldn't add any additional time to run. I should have thought about that a long time ago. They are ok with the 2 hours length of time compared to the time it takes now(8-9 hours). Just an fyi, Im not happy with it, but I have been on this too long I have to move on. A straight Insert into my Destination table with all the logic in the query would dramatically make the time it takes to run much less. But that's a work in progress. I am going to try both of queries above and will let you know the outcome.
Mark, your query was actually right on so I will try it when I get back to work and let you know.
0
jknj72Author Commented:
I shouldn't have said Prod side I meant on the tables Im reading from(Coverage and Endorse I have very little privs on.
0
slightwv (䄆 Netminder) Commented:
Since this is just the next question in a LONG series of closely related questions, I'll continue to post the same things...

You claim to have removed the in-memory tables.  This was an EXCELLENT first step.  Now work on removing the staging table.

I know you claim you need to do this because of additional processing but we have yet to see this additional processing...

You are loading about 10% of a complete table into a staging table and think this will be faster.  If the main table is overly indexed as you claim, I doubt the staging table is faster.

I mean, it might be, but I seriously doubt it based on all the previous questions you have asked.

FYI: Both markgeer and johnsone have the "Hire Me" enabled in their profiles.  I would have your people take them up on that...  If I needed Oracle help, I would hire either one of them!!!
0
jknj72Author Commented:
>>I know you claim you need to do this because of additional processing but we have yet to see this additional processing...
What do you need to see? If I can, I will gladly give you anything you'd like to see? I really don't know how to get the Explain Plan. Im sorry but I don't have much Oracle experience 'yet' and have never figured out how to get this?  
And fyi, Im not going to "hire" anyone. Im sure those guys are more than capable of doing this much better than me but I will do this task one way or another. I ask questions for some help and try to give as much info as I can. If you've asked for additional info and I missed it, I apologize.  I have taken out the local table and I am running off the main table cause it didn't help at all. I actually put indexes on that temp table the first time I ran it but it didn't help either.
0
johnsoneSenior Oracle DBACommented:
The documentation is always your friend.  To generate a plan -> http://docs.oracle.com/cd/E11882_01/server.112/e41573/ex_plan.htm#PFGRF009

The basic steps are:

explain plan for <your_query>;
select plan_table_output from table(dbms_xplan.display());

The setup required is in the documentation, but in theory it should have already been run.
0
slightwv (䄆 Netminder) Commented:
>>What do you need to see? If I can, I will gladly give you anything you'd like to see?

To date, I've only seen the one procedure and everything in it looks like it can be done in a single statement.  I've not seen any of the additional processing that requires a staging table.
0
jknj72Author Commented:
Thanks johnsone that helped me out a lot. Now I have to figure out what it all means....
I have attached a copy of my EXPLAIN PLANS (actually of three plans for 3 queries I feel are giving me issues)..
The first is a function and Im not sure how else I could get around running that better. The second is the problem query from the other day. It added 2 hours to my process and the last one is a call to get an address, which I should have attached my problem query to somehow? It does have a join in the statement though so Im not sure if it would work yet?
Anyway, any help would be appreciated.

FYI.....
Select count(*) from ENDORSE                        28,231,693 records
Select count(*) from INSURED_LOCATION    10,855,454 records
EXPLAIN-PLAN.txt
0
slightwv (䄆 Netminder) Commented:
Were those run on your production system?

Those plans don't show any performance issues at all.  They also only show a few rows being affected.
0
johnsoneSenior Oracle DBACommented:
I'm totally confused here.  You have explain plans for queries that don't involve all the tables.  To me, those are totally useless.  When you finally determine what your final query is going to look like, post the query and the plan.
0
Mark GeerlingsDatabase AdministratorCommented:
The "explain plan" outputs that you posted don't account for anywhere near the hours of time you say this process takes, since the longest one of these just covers six seconds.

However, I'm concerned by the literal values I see in the SQL statements in what you posted.  I had expected to see bind variables there.  For example, the file you posted has:
Select INSURED_ENTITY.INSURED_NAME from INSURED_ENTITY where POLNBR = 'WCP081225304' AND CVG_ID = '0199993' AND COV_EFFDT = '30-JUN-08' AND ROWNUM = 1;

Where I would expect to see something more like:
Select INSURED_ENTITY.INSURED_NAME from INSURED_ENTITY where POLNBR = :b1 AND CVG_ID = :b2 AND COV_EFFDT = :b3 AND ROWNUM = 1;

Oracle SQL statements that include bind variables can have a different "explain plan" (and very different response times!) than Oracle SQL statements that use bind variables.  Even though  statements with bind variables are simpler to understand and tune, Oracle is optimized for statements that use bind variables, since they can greatly reduce the amount of time that Oracle has to spend parsing statements and determining the access path (or "explain plan") that it will use to execute the statement.

There are multiple ways of collecting "explain plan" results in Oracle.  How or what technique did you use to collect these?
0
jknj72Author Commented:
Slight your right I should keep trying with that Insert statement. I do have some time so maybe I will go back to that.
I guess I should have included my source query in the last plan. The first Explain Plans I attached are run off of this source query and it provides then with the variables needed for those queries...I have attached my source query EXPLAIN PLAN
0
slightwv (䄆 Netminder) Commented:
>>I have attached my source query EXPLAIN PLAN

Forget to attach it?
0
jknj72Author Commented:
NOW I have attached my plan ;)
EXPLAIN-PLAN-Source.txt
0
johnsoneSenior Oracle DBACommented:
Assuming the statistics are up to date on those 2 tables, that isn't bad.  However, I would like to see it use an index on the COVERAGE table.

You are saying that query takes 2 hours, so I'm guessing that the statistics are not up to date as the estimate is 22 minutes.

As it is part of an INSERT statement, I would highly recommend removing the ORDER BY.  It is totally useless in an INSERT and doubles the time of the query.
0
Mark GeerlingsDatabase AdministratorCommented:
That's more helpful.  Now we can see clues to the problem: "TABLE ACCESS FULL".

Those are the lines that you normally do *NOT* want to find in your "explain plans".  They indicate that Oracle did not use indexes, but instead read every record of the table.  In some cases, that is better than using an index, like *IF* your program needs to process more than about 20% of the rows in the table, *AND* the rows you need are scattered among all (or most of) the data blocks in the table.

But normally, for best performance, you want Oracle to use an index and avoid full-table scans.
0
Mark GeerlingsDatabase AdministratorCommented:
And yes, I agree with johnsone's comment (now that I can see it): remove the "order by" in your query if your query is used to provide values for an insert statement.  That is, unless you know that the data will be indexed by the: CARRID ASC, COV_EFFDT values *AND* you know that it will often be queried by those same two values later.  If those are true, then it *MAY* be helpful to insert the record in sorted order.
0
jknj72Author Commented:
Johnson - the query doesn't take 2 hours its the looping through each record and performing all the logic that takes the 2 hours. The query itself takes about a couple of minutes to run?
0
johnsoneSenior Oracle DBACommented:
So, if that query isn't a problem, then why are we trying to tune it?

If that yields 1.5 million records and then you do an individual lookup for each of those records in a loop, why do you think it won't take any time?  Even if that lookup takes 0.1 seconds, you are still looking at over 40 minutes to complete the query that many times.

This is why combining your queries into one query may be beneficial.
0
jknj72Author Commented:
I agree, I was just trying to figure out the Endorse query(the one that looks for the EndorseID)  adding 2 hours of time to my process and if I could somehow include it in another, maybe the source query, to save the check for every iteration.
At one point I was trying to create one Insert statement with all the logic in the Select portion of the Insert

Insert into DestTable
Select ...do all my logic here...
from COVERAGE c
INNER JOIN CARR car ON c.CARRID = car.CARRID
INNER JOIN vw_Address v ON c.POLNBR = v.POLNBR
WHERE COV_EXPDT > l_CONST_covExpDate  AND (STATUS != 'V')

But I ran out of time to get this to work but have some time this week to try and get this to work
0
Mark GeerlingsDatabase AdministratorCommented:
I think this is the best way to get the job done:
"create one Insert statement with all the logic in the Select portion of the Insert"

That is, unless the "Select portion" then becomes too difficult to write (and/or maintain) or too slow to execute.
0
jknj72Author Commented:
From Marks query above I did an EXPLAIN plan with the addition of the EndorseID in the Source query.

SELECT c.CARRID, c.POLNBR ,  [additional columns...], nvl((select 1 from ENDORSE
    where ENDORSE.POLNBR= c.POLNBR
    AND ENDORSE.COV_EFFDT= c.COV_EFFDT
    AND ENDORSE.ENDORSE_CD_NBR = '290307'
    and rownum = 1),0)
 from COVERAGE c
  INNER JOIN CARR car ON c.CARRID = car.CARRID
  WHERE c.COV_EXPDT > '25-JUN-09'  AND (STATUS != 'V')      
  ORDER BY c.CARRID ASC, c.COV_EFFDT ASC;

and I have attached the EXPLAIN PLAN for the orig, without the ENDORSEID and with the EndorseID. How much longer would this take by looking at the EP? I will run it but I wanna know what the numbers look like to you guys...Thanks
EXPLAIN-PLAN-Source1.txt
0
johnsoneSenior Oracle DBACommented:
You query is incorrect.  You have a hard coded value for ENDORCE_CD_NBR, which would mean that every record will have the same value.  I suspect that it should be a column from one of the other 2 tables.
0
jknj72Author Commented:
Actually the ENDORSE.ENDORSE_CD_NBR field is hard coded to '290307' for the value I need. The POLNBR and COV_EFFDT will be different so it will bring me back different records for each row and yield different results.
0
Mark GeerlingsDatabase AdministratorCommented:
OK.  The query should work reasonably well now.
0
johnsoneSenior Oracle DBACommented:
So, all 1.5 million records have the same value in that field?  Then why would you be looping through the records in the first place?  Just run the query once instead of 1.5 million times.
0
jknj72Author Commented:
because it will return a different value for the when there is a diff polnbr an cov_effdt. Remember, this is evaluated for every iteration when the polnbr and coveffdt change
0
slightwv (䄆 Netminder) Commented:
I've really never liked selects embedded as columns.

I'm thinking this is the same but with regular joins:
SELECT c.CARRID, c.POLNBR ,  [additional columns...], case when e.polnbr is null then 0 else 1 end
  from COVERAGE c
   INNER JOIN CARR car ON c.CARRID = car.CARRID
   left outer join (select cov_effdt, polnbr from ENDORSE
                          where ENDORSE.ENDORSE_CD_NBR = '290307'
                          and rownum = 1) e 
          on c.polnbr=e.polnbr and c.cov_effdt=e.cov_effdt
   WHERE c.COV_EXPDT > '25-JUN-09'  AND (STATUS != 'V')       
   ORDER BY c.CARRID ASC, c.COV_EFFDT ASC;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jknj72Author Commented:
hey Slight that actually ran pretty fast. If I add Endorse_ID to the Select can I check for that value instead. Even though if there is a POLNBR then there is technically an Endorse_ID(PK)
0
jknj72Author Commented:
I apologize I thought I accepted this already. This is what I used...Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.