Setting the Priority for Oracle Batch Jobs

Michael Wilson asks: 

Question:  What is the  mechanism to set priority for batch jobs? I want my stored procedures to have lower priority than the real time processes. We want the batch job access wait, while the application processes get a higher dispatching priority. The batch jobs are submitted via dbms_scheduler, and I want them to have less priority after they are submitted.

The expert says:

Answer: When referring to priority, we mean the CPU dispatching priority, also known as the queue that manages how Oracle processes receive cycles.   Oracle sessions create a process which is visible to the operating system. Once you have found the PID for these sessions associated with the PL/SQL stored procedure, you can use the "nice" and "priocntl" commands to change the dispatching priority.

Just remember, the higher the nice number, the LOWER the task priority.

When I "nice up" a batch job, I do it from the OS side:

1.    Schedule a cron job to start one minute after the Oracle job starts.

 2.    Use a "ps" command piped to "grep" to find the OS process associated with the PL/SQL stored procedure.

3.    Issue the "nice" or "priocntl" command to make the batch job lower in the CPU dispatching queue.

 If you want to do it all with Oracle, you can issue OS commands from inside Oracle, but it's tricky.

Follow the link below for further insight on setting priorities for Oracle batch jobs:

http://www.dba-oracle.com/t_setting_priority_oracle_batch_jobs.htm


*****************************************
Spot hidden Oracle performance trends!

Using the principles of decision support systems technology, BC has developed an important new Oracle tuning tool, Ion for Oracle. Ion quickly isolates the reports on the important optimization issues, separating the wheat from the chaff like no other performance tuning software.

After several years of hard work, Ion for Oracle is finally available for general release. Ion is unlike any other Oracle tool on the market, a tool that encapsulates expert techniques to allow a DBA to quickly visualize important trends and signatures.

Predictive analytics is the key to repairing Oracle problems before they cripple your database, and I created Ion for Oracle to allow any DBA to quickly identify changing workloads and unobtrusive patterns of data access. To try Ion for free:

http://www.ion-dba.com 

*****************************************

Need a Health Check?

Oracle is the world's most complex and robust database and there are hundreds of sub-optimal settings that can cripple your database performance.

Burleson Consulting has a great Oracle health check where we identify all database bottlenecks to ensure that your mission critical system is running at optimal speeds.

Just call 800-766-1884 to schedule your health check.

*****************************************

Need Oracle Training?

The very best Oracle training comes from Burleson Consulting, where you get an on-site visit by an experienced Oracle expert and author. Whether it's one-on-one mentoring or getting a customized on-site Oracle training class, there is no substitute for BC Oracle training. Just call 800-766-1884 for details, and check out our on-site Oracle training catalog at the following link:

http://www.dba-oracle.com/bc-catalog.pdf 

*****************************************

There's Always Room at the Top

Burleson Consulting continues to grow as the Oracle community recognizes that high quality Oracle support and training is a cost effective option in these tough economic times.

If you have distinguished yourself in your Oracle career and are ready to step up and enjoy the rewards of joining the ranks of America's top tier support consultants we would like to hear from you.

Evidence of high achievement as an Oracle professional might include publication of books and articles for the Oracle community, achieving an advanced degree from a competitive university and seeking out hands-on experience with complex databases. Follow the link for details and a full qualifications list:

http://www.dba-oracle.com/jobs.htm