RAC & Failsafe :: Query Doesn't Have Any Parallel Hints

Jul 10, 2013

Below query doesn't have any parallel hints. Though, it is without where clause, so full scan is must but why "PX Deq Credit: send blkd" occurred?

SELECT ROWID, REGION, STATE, CITY, DEALERNAME, DEALERCODE, DEALERID, BUSINESSUNIT, GARDEALERID, MASTERCLAIMNUMBER, CLAIMNUMBER, COMPANY, POLICYNUMBER, STARTDATE, ENDDATE, POLICYISSUEDEALERNAME, ISSUEDEALERCODE, CUSTOMERNAME, CUSTOMERADDRESS, ACCIDENTDATE, ACCIDENTTIME, INTIMATIONDATE, INTIMATIONTIME, REGISTRATIONDATE, REGISTRATIONTIME, DRIVERNAME, ACCIDENT_DESCRIPTION, INTIMATION_DELAY_REASON, INTIMATORNAME, INTIMATORMOBILENO, CLAIM_INTIMATED_BY, TOWING_ASSISTANCE, ACCIDENTPLACE, ACCIDENTLOCATION, GARAGENAME, WORKSHOPADDRESS, WORKSHOPMOBILENO, MANUFACTURINGYEAR, CHASSISNUMBER, ENGINENUMBER, REGISTRATIONNUMBER, VEHICLEMODEL, BUSINESSTYPE, TOTALSI, CLAIMEDAMOUNT, ESTIMATEDAMOUNT, "LABOUR(E)", "PARTS(E)", "PAINTING(E)", EXCESS,
[code]....

understand about "PX Deq Credit: send blkd"..

View 1 Replies


ADVERTISEMENT

RAC & Failsafe :: Listener - VIP Doesn't Migrate From One Node To Another

Apr 17, 2012

I have the following problem on a 10.2.0.4 Oracle on AIX 1.6...

I have 2 RAC nodes as the node # 2 goes down, the VIP migrated correctly to the node # 1, but the listener # 1 do not see the change, and just resolve to the local ip.

The same does not apply in the reverse case, when it drops the node N 1, the listener attends to the fallen VIP node and migrated.The configuration is correct and identical in both nodes, according to the documentation, but I can not find a reasonable answer.

IP node # 1.
scm1or01 192.68.60.11
scm1or01-VIP 192.68.60.13

IP node # 2
scm1or02 192.168.60.12
scm1or01-VIP 192.168.60.14

Below I write as I detect the problem: when scm1or02 is down:

scm1or01:/cots/oracle/app/oracle/product/10.2.0/db_1/network/admin> lsnrctl status
LSNRCTL for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Production on 11-APR-2012 19:56:46
Copyright © 1991, 2007, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
[code]...

Also the listener.ora in scm1or01 starts how LISTENER, but the crsstat says:

scm1or01:/cots/oracle/app/oracle/product/10.2.0/db_1/network/admin>crsstat listener_scm1oro1 ONLINE

I do not understand the reason, I've rebuilt the configuration of the listener, and netmgr netca tools, but the problem persists.

View 5 Replies View Related

Get A Query To Run In Parallel Using Index

Jun 21, 2012

oracle: 10.2.0.5.7...I can get this to work, but not the way the docs seem to say. I am wondering if I am reading the docs wrong or missing something.

The docs seem to say to get a query to run in parallel using an index you use the PARALLEL_INDEX hint. This doesn't seem to work for me. I have to do one of the following

1. change the parallel degree with an alter index, then use the PARALLEL hint (parallel index hint does nothing in this case)
2. use both the parallel_index and parallel hint

View 4 Replies View Related

Parallel Query Option

Oct 23, 2013

Just a general query on parallel query.  My customer having 4 cpus and running the database in 11.2.0.3 in AIX 5.3(One is in AIX 6.1). Under which circumstances, we can propose to user parallel query options.

View 2 Replies View Related

Parallel Query Block Of Union All Set

Sep 24, 2010

We have very large table having data more than 1000 millions rows. We divide this table into four physical tables say A, B, C and D. The physical horizontal partition of data of this original table is done based upon their business policy.

Each partitioned table has contained data of particular business entity. Further each table has partition and sub partitions based upon business rule.

We have to retrieve data from all these tables as follows:

select a1, a2, a3, a4, a5, a6
from A
where < logical filter condition>
union all
select b1, b2, b3, b4, b5, b6
[code].....

We observed that above each query block execute in serial one after another and individual each query block capable to process data in parallel from respective table.

How does this above query able to execute each query block in parallel?

View 14 Replies View Related

SQL & PL/SQL :: Query Regarding Parallel Hint Degree?

Jan 3, 2012

I have been told that i should use multiple's of 4 as degree in the parallel hint to get maximum performance, so i am wondering is it true? that i should always use multiples of 4 or i can use any number inside the parallel hint.

View 4 Replies View Related

SQL & PL/SQL :: ORA-12827 / Insufficient Parallel Query Slaves Available

Nov 25, 2010

We are facing the below error in our Production environment.

ORA-12827: insufficient parallel query slaves available

View 6 Replies View Related

Parallel Query ORA-01002 - Fetch Out Of Sequence

Feb 16, 2013

While executing parallel query we are getting the below errors.

(SQLState = HY010) - java.sql.SQLException: [tibcosoftwareinc][Oracle JDBC Driver][Oracle]ORA-01002: fetch out of sequence"
(SQLState = HY000) - java.sql.SQLException: [tibcosoftwareinc][Oracle JDBC Driver][Oracle]ORA-12842: Cursor invalidated during parallel execution

Database Version: Release 11.2.0.1.0 (Non-RAC)

SQL Query:
SELECT /*+ PARALLEL 4 */
DISTINCT
AI.STORE_ID STORE_ID, AI.DUE_DATE DUE_DATE, AI.INSTRUCTION_ID INSTRUCTION_ID,
DECODE (ASR.ADT_INSTR_KEY, NULL, 'F', ASR.ADT_INSTR_KEY, 'S') QCCHECK
FROM NSOAPP.DA_INSTRUCTION_INFO AI,
[code]....

View 4 Replies View Related

Performance Tuning :: Parallel Index With Non-partitioned No-parallel Table?

Apr 30, 2012

If we have not set parallel degree for a table then we can ( try to ) force parallel execution on a table using a parallel hint Does this 'parallelism' works on the index search in the query as well?

In which situations non-parallel non-partitioned table but parallel index (degree>2) will facilitate a query?

View 5 Replies View Related

SQL & PL/SQL :: ORA-12805 - Parallel Query Server Died Unexpectedly?

May 24, 2010

ORA-12805: parallel query server died unexpectedly

View 4 Replies View Related

Reports & Discoverer :: How To Execute Query 2 If 1 Doesn't Retrieve Anything

Dec 3, 2010

In oracle report how do we execute query2 if query1 doesn't retrieved any records.

View 4 Replies View Related

PL/SQL :: Code_combination_id Doesn't Exist In Outer Query Table

Aug 20, 2013

I have query in which having some issues with outer join.When I run the inner query, I am getting 121 rows and when i put outer query and run it, I am getting 69 rows coz the code_combination_id does not exist in the outer query table.But even when I say ( + ) , its not giving 121 rows..  

SELECT  DISTINCT t1.* --hr3.NAME prj_org --, p.segment1 prj_no, p.org_id --pt.task_number, pt.task_name,  --paei.expenditure_type, pacdl. acct_ currency_code functional_curr from        (    SELECT DISTINCT gll.ledger_id, gljeh.period_name "Fiscal Period", gll.NAME "Company Name", gljeh.NAME "Journal Name ",  gljeh.je_source "Accounting Document Source",  gljeb.NAME "Journal batch name", gljel. accounted_ dr,glcc.code_ combination_ id,glcc.segment1,glcc.segment2,glcc.segment3,glcc.segment4,glcc.segment5,glcc.segment6,glcc.segment7,glcc.segment8,gljel. accounted_ cr  FROM gl_je_headers gljeh, gl_je_batches gljeb,  gl_je_lines gljel,gl_code_combinations glcc, gl_ledgers gll  WHERE gll.ledger_id =
[code]....

View 5 Replies View Related

Performance Tuning :: Select Query Taking Time Even After Using Parallel Hint?

Sep 25, 2013

select
serialnumber from product where productid in
(select /*+ full parallel(producttask 16) */productid from producttask where
startedtimestamp > to_date('2013-07-04 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and startedtimestamp < to_date('2013-07-05 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and producttasktypeid in

[code]....

Explain plan output:

Plan hash value: 2779236890
-----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name| Rows| Bytes | Cost (%CPU)| Time| Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT || 1 | 29 | 9633M (8)|999:59:59 |||
|* 1 | FILTER |||| ||||
| 2 | PARTITION RANGE ALL || 738M| 19G| 6321K (1)| 21:04:17 | 1 | 6821 |

[code]....

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (<not feasible>)
4 - filter("PRODUCTID"=:B1)
5 - filter(ROWNUM<100)
12 - access("MODELID"=:B1)

[code]....

Note: - SQL profile "SYS_SQLPROF_014153616b850002" used for this statement

View 2 Replies View Related

Performance Tuning :: Does Parallel Hint In Query Can Create Bottleneck / Slowdown / Crash Database

Oct 20, 2011

does parallel hint in query can create bottleneck/Slowdown/crash database..??

View 4 Replies View Related

SQL & PL/SQL :: Usage Of Hints In The Views

Oct 11, 2011

I have a table "tl" which is partitioned--say 30 partition and for each partition there is a seperate view like as follows

create view view_t130 as select * from tl partition (p30);
create view view_t129 as select * from t1 partition (p29);
.
.
.
create view view_t101 as select * from t1 partition (p01);

my question is how to use hints on this table if your are using view to access the data from internal table.

Normal structure is if i don't wrong:-

index( <<view name|view alia name>> <<table name|table alias name>> name of index)

Consider my case

select * from view_t130 where <index_column> --not picking up index

i want to give expicit index hint.so i used the same structure that i specified above but it didn't work.

select /*+ index( view_t130 t1 <index_name) */ * from view_t130 where <index_name>

how to give explicit index hint..but one constraint is i cannot give any alias names for internal tables because those(view structure) are generated by predefined scripts..so it's not possible to change it.

View 1 Replies View Related

SQL & PL/SQL :: Fix Execution Plan Using Hints

Mar 6, 2012

I am executing below query, but optimizer generating 2 different plans for the same. I don't want to use sql profiles to fix execution plan.

Query
SELECT R.VENDOR_RECORD_SEQ_NO ,
R.VENDOR_SUBJECT_SEQ_NO ,
NVL(D.RESOLVED_VALUE, D.ORIGINAL_VALUE) VAL,
D.CONTROL_COLUMN_SEQ_NO
[code]....

View 3 Replies View Related

Ordered Hints In Oracle

Aug 5, 2007

There's a fairly popular Ordered Hint example on the web as follows:

<CODE>
select /*+ ordered use_nl(bonus) parallel(e, 4) */
e.ename,
hiredate,
b.comm
from
emp e,
bonus b
where
e.ename = b.ename
;
</CODE>

I would like to know what the "parallel(e, 4)" clause does. Where does the "4" come from? What is "parallel" here? I also have another question: If I have 5 tables--T_OREGON, T_UTAH, T_VIRGINIA, T_TEXAS, and T_OKLAHOMA--lined up in a join right behind a FROM, coming in at row counts of

T_OREGON: [a lot of rows; a lot more than T_UTAH]
T_UTAH: [smaller than T_OREGON] 40550 rows
T_VIRGINIA: 14 rows
T_TEXAS: 66 rows
T_OKLAHOMA: 8 rows
from T_OREGON or, T_UTAH ut, T_VIRGINIA va, T_TEXAS tx, T_OKLAHOMA ok...

my question is, if an Ordered hint can be used here, is it that the smallest table (in this case T_OKLAHOMA) gets placed first in the join and the rest of the table ascension in the join doesn't matter? Or is it that T_OKLAHOMA gets placed first in the join, followed by T_VIRGINIA [at 14 rows], followed by T_TEXAS, T_UTAH, and finally T_OREGON?

View 3 Replies View Related

Server Administration :: Oracle Hints In SQL Statement

Sep 30, 2011

What is the use of using Hints within SQL statements w.r.t query tuning? How to know which hint to use when?

View 1 Replies View Related

RAC & Failsafe :: Query On Dependency Between Database Instance And Service?

Jul 8, 2013

suggest on the dependenecy between the database service and the database instance?

srvctl start instance -i db1
will this start service as well for which the above instance is either preferred or available instance?
will it start the listener as well?

srvctl stop instance -i db1
will this stop service as well for which the above instance is either preferred or available instance?
will it stop the listener as well?

srvctl start service -d db -s dbserve
will this start service as well for which the above instance is either preferred or available instance?

srvctl stop servive -d db -s dbserve
will this stop service as well for which the above instance is either preferred or available instance?

srvctl start database -d db
will it start instance, listener and service as well?

srvctl stop database -d db
will it stop instance, listener and service as well?

View 1 Replies View Related

SQL & PL/SQL :: Union Run In Parallel

Sep 30, 2010

I have a query which has 5 unions, each clause of the union takes 1 hr to run and query results come back in 5 hrs, Is there any way I can make these clause to run in parallel?

View 13 Replies View Related

SQL & PL/SQL :: How To Run Procedure In Parallel

Dec 28, 2012

we have many tables in Cursor For Loop :

CREATE OR REPLACE PROCEDURE EBILL_BULK_UPDATE_SERVICE(in_cycle VARCHAR2)
AS
v_cnt NUMBER; -----Variable used for checking table is partitioned or not partitioned
CURSOR cur_update -----Cursor defined for Updating EBILL tables for service_id
is
SELECT table_name
, cycle_name
FROM NNP_EBILL_UPDATE

[code]....

As our requirement that Execute Immediate should work for 5 or more tables updation parallely at a time.If one table get completed then it should take next table from loop and then start the code till completion of all tables.

View 11 Replies View Related

Process Queries Parallel?

Feb 2, 2005

On a tab page should be displayed the result of four indifferent queries, each based on a stored procedure.At the moment, the queries are processed serially, by the statements:

GO_BLOCK('one');
CLEAR_BLOCK(No_Validate);
EXECUTE_QUERY;
GO_BLOCK('two');
CLEAR_BLOCK(No_Validate);
EXECUTE_QUERY;

Is there a way to processes the queries parallel ?

View 1 Replies View Related

SQL & PL/SQL :: Parallel Execution Of A Procedure?

Jul 22, 2010

Is it possible to run a single procedure in parallel. (Not looking for multifple, using DBMS_JOB)

I am using Oracle 9i.

View 2 Replies View Related

SQL & PL/SQL :: Partition Truncation Parallel?

Dec 15, 2011

I have to truncate two partitions from the same table parallelly at a time.

ALTER TABLE tablename TRUNCATE PARTITION partitioname1;
ALTER TABLE tablename TRUNCATE PARTITION partitioname2;

Huge data is present in each partition.is this possible, means it does not lock table for another alter truncate partition statement?

View 1 Replies View Related

SQL & PL/SQL :: How To Find Degree For Parallel DML

Feb 12, 2013

I am inserting 50 million records into a table MAIL_LOG. I am using the hint /*+ append parallel (MAIL_LOG, 12) */. But for my table degree is 1.

SELECT table_name, degree
FROM user_tables
WHERE table_name = 'MAIL_LOG';

I have following clarifications.
1) What degree I should use.
2) On what basis I have to give the degree.
3) Have we use constant degree all the times.
4) How to check my insert statement is using parallel degree.
5) How to find the degree at session level.

View 24 Replies View Related

Parallel Keyword In Expdp

Dec 17, 2012

i am trying to export table using datapump in oracle 10g, this expdp takes 5 hours time, so i want use use parallel keyword in expdp,
my question is how should i know number of parallels can i use...?

View 3 Replies View Related

PL/SQL :: How To Run DBMS_JOB In Parallel And Serial

Jul 26, 2012

I have total 8 procedure to run in parallel . and after that my 9th procedure should run.

below is my job submission procedure

create or replace procedure DURATION_ALARM_WEEKLY as
l_job number ;
begin
dbms_job.submit(l_job,'begin ALARMS_WEEKLY_CALL_OUT ; end;');
dbms_job.submit(l_job,'begin ALARMS_WEEKLY_CALL_IN ; end;');
dbms_job.submit(l_job,'begin ALARMS_WEEKLY_DURATIN_OUT ; end;');
[code].......

what is the syntax I have to do in my FINALE procedure . using DBMS_ALERT.REGISTER , DBMS_ALERT.WAITANY .....?

View 9 Replies View Related

SQL & PL/SQL :: Why Doesn't Number Add Up When Do SUM On Column

Mar 21, 2011

I think I am having a rounding issue, but not sure. If I add all 4 rows below I get 1530.06. But when I use SUM, I get 1529.87

select PAY_EVENTS_AMOUNT
from PAYROLL_PAY_EVENTS_T1 PPET1
where ppet1.regnum = 130402134
and ppet1.pay_period = 2

PAY_EVENTS_AMOUNT
=================
-1499.19
2998.38
-30.68
61.36
=======
1530.06 TOTAL

========================================================

select sum(PAY_EVENTS_AMOUNT ) PAY_EVENTS_AMOUNT
from PAYROLL_PAY_EVENTS_T1 PPET1
where ppet1.regnum = 130402134
and ppet1.pay_period = 2

PAY_EVENTS_AMOUNT
=================
1529.87 TOTAL

View 12 Replies View Related

Starting A Function In Parallel Using DBMS_JOB

Jun 30, 2011

We have Data Migration for our application coded in PL/SQL. The DB server has 64 Cores available (Solaris 10 OS) however running the migration code written as a function, utilizes very little CPU and CPU utilization is to max 2%. To utilize CPU power available to increase the speed of migration, we are using DBMS_JOB to schedule this function multiple times.

However scheduling the function 10 times, we are seeing that at any moment only 4/5 oracle processes are active and utilizing the CPU and CPU utilization has gone up to 5-6%. The speed of migration is increased but not to a great extend which I feel would work if we could utilize more CPU.

I see a parameter job_queue_processes is set to 10 currently in the database and am planning to increase this (currently to 25 as I don't have exact count of how many other jobs may be running in the database).

View 1 Replies View Related

Running Parallel / Concurrent Scripts?

Feb 24, 2011

I am trying to execute two scripts at the same time (concurrent) in Oracle SQL Developer. I know we can schedule a job using DBMS_job package and define the job. But is there any other way of doing it using Threads ?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved