Oracle process timeslice,setting priority

From: David <dfairman16_at_hotmail.com>
Date: 13 Dec 2002 07:13:10 -0800
Message-ID: <b4cefdce.0212130713.6d4f3b03@posting.google.com>

Everybody, I have a long job that is being very greedy in terms of CPU timeslice to the extent that the performance for everyone else using the database is being seriously degraded.

See the query below & you will see why. The problem is that both largetable1 and largetable2 are large, and then there is that terrible LIKE wildcard. Because of the data being generated/required, I am forced to use the LIKE double % wildcard. Fortunately this is a one off query and the information being generated is going to be used to construct some indexes to ensure that I will never have to do this again, triggers will be used to populate current data/indexes.

In the meantime however, I need to run this and other similar queries until I construct indexes on significant amounts of legacy data. I need to lower my process priority/priviledge (so others can use the database/network).

I have attempted to find documentation in the Oracle docs on how I can do this but have not been successful. I would appreciate a hyperlink to the documentation, or alteratively a line or two I can type into SQLPlus to allow others a larger timeslice that they currently have (if something like this exists).

So, in summary:
1. Can I/how do I lower a session priority to that others can use the system without performance degradation because of my CPU intensive task?
2. Can this be done so that between 8:30am and 6:00pm, my process priority is low, but outside these times, I can resort to my bad old ways & use more timeslice that I should normally.

Best regards
David

The query is something like:

DECLARE
 CURSOR c1 IS
  SELECT *
   FROM largetable1;
 c1Rec largetable1%ROWTYPE;
 done BOOLEAN := FALSE;
BEGIN
 OPEN c1;
 WHILE NOT done
  LOOP
   FETCH c1 INTO c1Rec;
   IF c1%NOTFOUND

     THEN
       done := TRUE;
     ELSE 
       UPDATE largetable2
        SET field1=c1Rec.something
         WHERE field2 LIKE '%' || c1Rec.something || '%';

   END IF;
  END LOOP;
  CLOSE c1;
END;

SQL> 
SQL> 
SQL> SELECT * FROM V$VERSION;

BANNER

---

Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production PL/SQL Release 8.1.7.0.0 - Production
CORE 8.1.7.0.0 Production
TNS for 32-bit Windows: Version 8.1.7.0.0 - Production NLSRTL Version 3.4.1.0.0 - Production

SQL>
SQL> Received on Fri Dec 13 2002 - 09:13:10 CST