Stored procedure locking the database 2

[LarrySteele]

New to Oracle and trying to learn the tools. Searched for solution, but found nothing to solve this problem.

BACKGROUND:
I have a number of procs that fetch data and load into local tables. Several more procs pull data from these local tables and puts the results into more local tables for reporting. All told, we're looking at about 25 procs, maybe 50 tables. Row count for all tables combined might be 1,000,000. So, we're not talking a lot of data, just have to go through quite a process to assemble it.

PROBLEM:
I have one proc that executes all the other procs in the required order. When I run this proc, we run out of memory and the system locks up. If I run each proc manually in SQL*Plus, they all run with no problem.

QUESTION:
How do I run all these procs in one pass w/o using up all my memory?

MY GUESS:
I'm presuming that memory does not get released until the proc completes its task. That would explain why the procs run with not problem manually, but cannot run as part of this parent proc. I'm sure there's probably a different method/tool for this, but I haven't been able to find it.

Thanks in advance!
Larry

 

[LarrySteele]

Things are moving right along. Next hurdle is the batch file. It's working great. I truncated a table and ran the file. Sure enough, I had data in my table.

Ah, but there's one little snag. I've left with a command window that says:
[tt]
Table dropped.

Table created.

Commit complete.[tt]

Yes, of course I'm happy to see this, but... I was hoping that I might see something like [tt]Refresh complete[/tt] and a prompt. Nope, just sits there. Not only do I not get a command prompt, I don't get a SQL*Plus prompt.

So, I dropped the silent mode switch and this caused SQL*Plus to return to it's command prompt. Once I entered quit; SQL*Plus returned control to my batch file and I got my "Refresh complete." message.

Here's what I have for my batch file (naturally I've masked username and connection in my post):

@echo off
cls

if [%1]==[] (goto bad_input)

sqlplus -s <username>/%1@<connection> @ctas_ee_country.sql

echo .
echo Refresh complete.
pause

goto end

:bad_input
echo Batchfile usage:
echo %0 {password}
echo ...
echo Syntax error, refresh did not run.
echo ...
pause

:end
echo batch data loader ended

Have I missed something obvious? It's not very important, because this is testing script. The kids in production will do their own scripts. I don't have a problem QUIT;ting if that's the only way SQL*Plus wants to play.

Thanks again (seem to be saying that a lot in this thread)

 

[LarrySteele]

Oops!

Silly me, I forgot to add the '/' at the end of my sql file.

Okay, I'm going to get back to work and hope I don't embarrass myself with too many more simpleton questions.

 

[Thargy]

Larry,

since a CTAS is DDL, and not DML, you don't need a commit statement.

If you want to exit sqlplus after running the file then put the word "quit" on the last line of the sql file. This will pop you back out to the dos prompt (which is what you want) so that you're ready for the next test run from the batch file command line.
You only need a trailing slash after a block of PL/SQL, not after sql.

Also, be careful with your script, once it's finally done. I deliver all my scripts with a bat file to invoke them. Because scripts are source code, they are always, always in source control (or whatever tool you use). Therefore in production, they only get to run my tinned script from the bat file. A properly tested and controlled script may be run in prod, but otherwise don't even allow a copy of the script off your local hard disk.

Regards

T

 

[LarrySteele]

T,

Yeah, I realized afterwords that quit is what I really wanted. Worked great. Good point on DDL vs DML. That explains why I was getting two Commit complete statements.

Oh, and I only run in the development environment. We have segregation of duties - as it should be. As a developer, I won't have access to production, and production will not use any development resources.

Time to start my weekend!!!
- Larry

 

[LarrySteele]

Well, I spent some time out of the office and diverted to other projects. I'm finally back on this, for a few minutes, then off to fight other data emergencies.

An interesting thing happened once I went through the full blown CTAS - processing speed pretty much came to a halt. What had taken as much as 2 minutes took 15. What happened???

Indexes. Of course, when I dropped my tables, the indexes went with 'em. So, I rebuilt the tables and indexes and returned to truncate/insert. However, this time I did things differently. Rather than putting things in a proc. Here's but one example:

OLD

CREATE OR REPLACE procedure prc_load_home as
-- load table of home address info for use as part of loading the employees table --
begin

-- empty table -- 
    execute immediate 'truncate table ee_home';

  -- fill the table -- 
  insert into hrtdmart.ee_home
  select pernr, 
         upper(substr(stras,1,24)) stras, 
         upper(substr(locat,1,24)) locat, 
         upper(substr(ort01,1,24)) ort01, 
         upper(substr(state,1,2)) state, 
         upper(substr(land1,1,3)) land1, 
         upper(substr(pstlz,1,10)) pstlz, 
         substr(telnr,1,15) telnr
  from pa0006 
  where endda='99991231' and mandt='020' and subty='1';

  commit;  

  exception when others then dbms_output.put_line(SQLERRM);           
  
end prc_load_home;
/

NEW

truncate table ee_home;

insert into ee_home
select pernr, 
       upper(substr(stras,1,24)) home_street1, 
       upper(substr(locat,1,24)) home_street2, 
       upper(substr(ort01,1,24)) home_city, 
       upper(substr(state,1,2)) home_state, 
       upper(substr(land1,1,3)) home_country, 
       upper(substr(pstlz,1,10)) home_zip, 
       substr(telnr,1,15) home_phone
from pa0006 
where endda='99991231'
  and subty='1';

alter index hrtdmart.ee_home_pernr rebuild; 

quit;

Instead of running the proc in TOAD, I'm running the SQL script from a batch file. I don't remember the time difference, but it's magnitudes faster. I'm still in the process of building the full batch loader - adding one SQL script at a time - but it builds 20 tables in 3 1/2 minutes - what previously took over 30 minutes. Actually, the fact that it's completing at all is great!

Part of this time decrease is because I was able to tweak a couple of queries to pull rows more efficiently. But the biggest impact was the methodology. Unfortunately, I didn't have any experience working procs/sql batch files, so didn't recognize the problems with the examples I found.

Again, thanks for your help.

- Larry

 

[Thargy]

Larry,

thanks for keeping us apprised. Do post again when time permits and let us know the final outcome. It's great that the speed is up and that the task completes - aren't scripted solutions so much easier than code?

W.R.T. the indexes, of course that's a genuine issue. I would still put in a plug for the CTAS approach, and then rebuild the indexes once at the end, using parallelism for performance. I think that way you might be able to still get the CTAS speed and have tables with indexes ready to go at the end. Rebuilding indexes "as you go" is usually a bad move in these situations. You can't possibly use the system half way through a major data change, so why suffer the overhead of the indexes? You don't even want them around, as they will slow inserts down.

Would you like to consider that, and/or have a look at the parallel indexes thing, or is that all a bit too much?

Regards

T

 

[LarrySteele]

One process I had previously took 30++ minutes to run. I knew it could be better - if only I had some time to work on it...

I analyzed the queries and found how I could streamline the selects dramatically. I also converted from 15 update statements to 15 insert statements. Bottom line: I decreased time from 30++ to 2:22 (in TOAD) - roughly 13* faster.

Here's a snippet from the old process:

    update mgr_composite m
    set m.mgr01_org_unit = (select h.sobid
                            from hrp1001 h
                            where h.objid=m.ee_org_unit 
                              and h.subty='A002'
                              and h.sclas='O'
                              and h.endda='99991231'),
        m.mgr01_pos_nbr = (select h.sobid
                           from hrp1001 h
                           where h.objid=m.ee_org_unit 
                             and h.subty='B012'
                             and h.sclas='S'
                             and h.endda='99991231');

Obviously the sub selects take a toll on the processing. Something I've wanted to address. So, I created a table that has the org_unit and pos_nbr and use said table rather than these sub selects. Here's a snippet from the revised script that creates the equivalent chunk of data:

  insert into mgr_composite_01
  select m.ee_pernr,
         m.ee_org_unit,
         o.org_unit mgr01_org_unit,
         o.pos_nbr mgr01_pos_nbr,
         p.pernr mgr01_pernr
  from mgr_composite_00 m,
       mgr_org_hier o,
       mgr_org_pos p
  where m.ee_org_unit=o.objid(+)
    and p.pos_nbr(+)=o.pos_nbr;

Oh, and I also figured out how to get the manager's info (mgrxx_pernr) in the first pass. Previously, I had a separate section devoted to filling in the holes.

Curiously, the script is significantly longer. Of course, I couldn't care less about the length of the script as long as it's accurate and efficient.

The way this is going, I might be able to get the whole process under 30 minutes - that's around 25 scripts that generate dozens of tables.

 

[Thargy]

Great feedback Larry, I really appreciate it.

Your performance improvements must be worth some brownie points with your superiors too - it may come in handy when negotiating a pay rise. Also, if the time spent is already yielding benefits, doesn't that make the case for giving you more time to work on performance?

Regards

T