PL/SQL :: Resource Planning And Jobs Grouping In Oracle
Mar 20, 2013
I have a requirement where I need to group sessions which takes time as given below when ran individually .There are a total of 32 sesions.I want to divide them into 7 groups.I want to submit each one of the 7 groups in a job.So there will be seven jobs on the whole.Execution flow will be group 1 completes ,then group 2 starts like that.Actually I don't want to bombard the database at a time with 32 jobs ,hence the idea of grouping 32 jobs among 7 groups.grouping the sessions so that on the whole ,resources are equally allocated among sessions in a group and which in turn makes the process complete faster. I want to achieve parallelism but at the same time database should not be loaded too much.I just want to know is there any good practice from oracle side while doing this type of grouping. I am planning to group the most time taking one with least time taking jobs.
A batch job was running for a long time which executes a stored procedure that was doing physical deletes on an oracle table. This job ran for about 3 hrs and is still executing. Meanwhile another job(which was making indexes unusable in another table) has cancelled saying "resource busy and acquire with NOWAIT specified", i think it was ORA-0054! So i killed the first job and reran the second thinking the first one was taking up resources which resulted in the cancel of the second job. Now the second job ran successfully. Both these tables are not related to each other whatsoever...but why was there a cancel?
I want to select the IDs which have no IN or PLANNING status in AREA 9
ID 1 has AREA 9 and STATUS IN so dont select ID 2 SELECT ID 3 has area 9 BUT STATUS is OUT so Select.
So if an ID has has IN or PLANNING status in AREA 9 i do not wish to see it!
CREATE TABLE DAN_T1 ( ID varchar(8), AREA varchar(8), UNIT varchar(8), STATUS varchar(8) ) INSERT INTO DAN_T1 (ID,AREA,UNIT,STATUS) VALUES (1,6,'AA','IN'); [code]......
ID AREA UNIT STATUS 16AAIN 17ABPLANNING 17ACOUT 19ADIN 25ACIN 25AAOUT 35BAPLANNING 36DAIN 35CAIN 39CGOUT
I have a procedure that needs to be scheduled as a job. This procedure takes an average of 20-25 minutes to complete. The constraint is that these jobs are submitted using dbms_job.submit with an interval of 5 minutes.
So, will Job2 execute only after Job1 completes [even though Job1 takes 25 minutes to complete] or is there any other mechanism for setting incompatibility for a job with itself.
Recently we are planning to migrate oracle 10g to 11g. We are migrating PHASE WISE (Schema wise) as this is a 50TB of Production instance. We are using Oracle Golden gate for this purpose.
Is there is any way that we can migrate oracle jobs from 10g to 11g?
My Oracle jobs are not running. I can run them manually but they don't start automatically as per interval. The job_queue_processes=100 and CJQ process is not running. I started CJQ0 process by resetting it to 0 and then to 100. But as soon as I enabled a job this background process stopped.
The job also did not execute in its next execution time. I have created the database manually without using DBCA. Is the problem because of this? I have set crontab immediately to get rid of it.
installing Oracle for a new Data-Center(still in planning).It will be an OLTP shop working 24/7, with 250 concurrent users.
1. Can it, in theory run with good performance on a quad processor(something like Intel Xeon Series 56XX) , with 32GB of memory? 2. What is the Oracle Database Edition required for this configuration? 3. What additional things should one take into account to plan the most cost-effective configuration?
1) to define a job in oracle9i for solaris 2) to schedule this job
Remember i have prior experience deploying jobs at windows platform but when i try it on (9i for ) solaris, the script does not work as it does not accept a submit job request.
prepare data: CREATE TABLE a AS SELECT LEVEL ID ,decode(mod(LEVEL,10),1,'CODE'||LEVEL) CODE from dual CONNECT BY LEVEL<20001;
sql1: CREATE TABLE TEST AS SELECT ID, NVL(CODE,LAG(CODE ignore nulls) OVER(ORDER BY ID )) CODE FROM a;
the sql is slow,most of the waits on session is resmgr:cpu quantum and almost all of the cpu resource is occupied
sql2: CREATE TABLE TEST AS SELECT ID, NVL(CODE,last_value(CODE ignore nulls) OVER(ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)) CODE FROM a;
this sql is fast
How lag function is implemented if the offset is not deterministic?
With this plan active PLAN GROUP_OR_SUBPLAN TYPE CPU_P1 CPU_P2 CPU_P3 x_PLAN x_APP_GROUP CONSUMER_GROUP 75 0 0 x_PLAN x_BO_HIGH_GROUP CONSUMER_GROUP 10 0 0 x_PLAN x_BO_LOW_GROUP CONSUMER_GROUP 5 0 0 x_PLAN OTHER_GROUPS CONSUMER_GROUP 10 0 0
if there is only an user of X_APP_GROUP that is working in a specified moment, he will use the 100% of CPU or il will be limited at 75% as specified in the plan?
I am trying to get to where I can add a RAD (Resource Access Descriptor) to allow connection to an oracle database. Right now I have "No Configuration Privilege" when logging onto Oracle Identity Management.
How to I get to the configuration tab? What privilege do I need?
i have one procedure that run at same time for two different partition.In the begin of the processes i do the truncate partition and after that the insert of data in the partition.Since i have two processes running at the same time, i will fill up data in two different partition.
But it occurred one error : "ORA-00054: resource busy and acquire with NOWAIT specified"
The problem is caused by the truncate partition. I'm doing two simultaneous execution of the same process, but when one process is doing the truncate other is insert data.How can i do the truncate of this two partition, or insert in to, without get any error. I think the two simultaneous insert is not a problem.
So how can i lock the table in the first truncate and after that release the table for the next truncate?
I want to change the processes parameter in RAC system.I am using spfile.I have ASM environment and two instances.
I followed the below steps from first server but i m getting error.
1) connected as sys user 2) alter system set cluster_database=false scope=spfile sid='<firstinstanceid>'; 3) exited from oracle 4) stoped second instance from linux. $ srvctl stop instance -d <DBname> -i <secondinstanceid> 5) Again i connected as sys user. 6) create spfile from pfile; 7) shutting down the db and started.
[code]....
But second instance is not starting and it is giving error.
PRKP-1001 : Error starting instance <sencondinstanceid> on node st-habam-db2
CRS-0215: Could not start resource 'ora.<DBname>.<secondInstanceid>.inst'.
i am working in scott's schema and i want to create foreign key on new table that is "test" table ,structure is----
create table test(id number, name varchar2(20),dno number,pno number);
and now create a foreign key on id column of "test" table reffering the empno of "emp" table which has pk already. but it shows an error-------"ora-ORA-00054-resource busy and acquire with NOWAIT specified or timeout expired"
while i have create the "test" table just 1 min ago.
am trying to load java resource in oracle llgr2 using load java. It shows resource loaded but I am not able find it in database dictionary. I configured JVM also on database and gave all the neccessary grants. whenever I am trying to encrpt it give me
SQL> select banner from v$version 2 ; Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production PL/SQL Release 11.2.0.1.0 - Production CORE 11.2.0.1.0 Production TNS for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production
I am database developer and suddenly i have been given a DBA work. I have to monitor database for resource intensive queries , whether i/o is more , process memory and many other stuffs.
Provide me some sql queries or scripts which shows that process are over loaded and memory needs to be allocated, queries are taking long time , i/o operation are more instead of memory.
I am using dbms_sqltune package to tune some resource intensive queries. In this I am looking to know how Sql profile works
I created task for one sql using dbms_sqltune, then in Report it was recommended to accept its sql profile (which will potentially benefits 65%) then accepted that sql profile with
and re-executed same query but NO difference in time. Then, what that mentioned potentially benefit 65% is about.
1) Originally query is taking 10 sec to execute, with this 65% i thought that it will execute within 3-4 sec. Is it right? 2)Also, the query for which i have created tuning task has some hardcoded input values, what if i change input values next time, will that profile works with new input values?
FINDINGS SECTION (1 finding) ------------------------------------------------------------------------------- 1- SQL Profile Finding (see explain plans section below) -------------------------------------------------------- A potentially better execution plan was found for this statement.
I have a requirement to get the records group wise.Ex: For each departments, i need to get the employee details as a coma seperated.It means that the output must have the department name in first column and the second column must contain all the employees in that particular department (As a coma seperated).
In the below data, a container is moving from one city to another. 1,2 ,3 can be any number which i want to generate and use as keys to group the cities. Eg: AUH, JEB, CIW belong to the same key=2; SIN, IKT belong to a new group 4. The City where difference between the Seq# is greater than 1 (eg between S8W and AUH), a new group starts.
Name Hours date a810/11/2011 a 510/12/2011 a610/13/2011 a710/14/2011 a710/15/2011 a810/16/2011 a710/17/2011 a810/18/2011 a810/19/2011 a710/20/2011 a710/21/2011
If i want the sum of hours for 3 days range ,how should i do it.
E.g. say
name hrs startdate enddate
a 19 10/11/2011 10/13/2011 a 22 10/14/201110/16/2011 a 23 10/17/2011 10/19/2011
How can I get the grp_id for unique combination of manager and department, grp_id should be created on asc order of manager_id. In this example manager_id 100 is minimum, so it should be grp 1 and all the employees with that manager_id should be in grp_id 1, for manager_id 114 grp_id should be 2.
If, there is manager_id 117, it should create grp_id 3.
To get grp_num ,I can use row_number() over (partition by department_id,manager_id order by employee_id) grp_num
I am looking for an update statement for this issue.
i am getting a below error whenever executing the below select query. some times it will show dead lock detected while waiting for resource and terminated...some times it executes and gives result..but all the time it writes an alert to alert log
Env: Linux / Oracle 11.2.0.3.3..Error from alert log:Errors in file /u01/oracle/oracle/diag/rdbms/bdrdb/bdrdb/trace/bdrdb_p017_6076.trc:ORA-00060: deadlock detected while waiting for resourceORA-10387: parallel query server interrupt (normal) Trace file info... bdrdb_p017_6076.trc:Trace file /u01/oracle/oracle/diag/rdbms/bdrdb/bdrdb/trace/bdrdb_p017_6076.trcOracle Database 11g Enterprise Edition Release 11.2.0.3.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsORACLE_HOME = /u01/oracle/oracle/product/11.2.0/dbhome_1System. [code]....
Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 13 0.00 0.00 SQL*Net more data to client 24 0.00 0.00 SQL*Net message from client 13 5.33 5.47
Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 3139
No Row Source Operation information here Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 1 0.00 0.00 db file sequential read 73062 0.25 692.23 db file scattered read 16099 0.18 76.65 latch: shared pool 4 0.02 0.03 latch: cache buffers chains 3 0.04 0.04 latch free 16 0.00 0.00
And the problem is, that when i use sutp_price_proc and pbk_price in grouping, it splits my results by those rows. If i delete them from grouping, sql gives me error about not a single grouping in line 1.
pas_codepas_profilesutp_idsutp_pricex 2664good stuff310069< because pbk_price is like 67 from that period 2664good stuff310071< because pbk_price is like 50 from other period
