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 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
does parallel hint in query can create bottleneck/Slowdown/crash database..??
View 4 Replies View RelatedI 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.
I have table A with size 120 Million and two more tables are of size 2 Million on Table B and less than 1 Million size on table C.I had created Partition and Parallel degree 4 on the table A. Created table B with Parallel degree 2 and Created table C with NOPARALLEL.
My query is using above tables with joins and inserting into table D using HINT /*+ APPEND NOLOGGING*/I had executed the explain on the above criteria the cost is showing 20767.
Later created Tables A,B and C with NOPARALLEL. Applied HINT on Table D /*+ APPEND NOLOGGING*/ and als applied HINT /*+ PARALLEL(A, 4) PARALLEL(B C, 2) on select query which uses to insert into Table D.
My question which is best practice on PARALLEL degree creation at table level or query level:
a) Creating table with Paralle (degree 4)
b) Applying HINT /*+ PARALLEL (TABLE A , 4) */ at query level
In my recent performance improvement tasks I was experimenting with Parallel hints. I have been getting good results with them, however the question arises "How is Parallel degree proportionate to number of CPUs used during execution?
/*+ parallel(emp_tab,8) */
Oracle Version: Oracle 10g
OS : UNIX
Client OS: Windows XP
V$PARAMETER VARIABLES as follows:
Total CPUs available: 10
No of CPUs per core: 2
Max parallel : 100
Min Parallel : 5
Question 1: So how do I map the degree of 8 with CPUs. I believe that degree of 8 does not mean its going to use 8 CPUs. If so, how do I prove it. I tried SQL_TRACE and cpu comes 0. And I believe its not the CPU count.
Question2: How do I know how many CPUs were used to execute a SQL query ?
I am executing a sql statement which is doing FTS in parallel mode The server has 8 cpus and threads_per_cpu is 2
The v$sql shows PX_SERVERS_EXECUTIONS as 8
select PX_SERVERS_EXECUTIONS, sql_text from v$sql where sql_id='0q0nk5117yth2'
8, select /*+ full(a) parallel(a)....
however the px_sessions shows 17 sessions (16 parallel session + 1 parent session (where sid = qcsid) Now in px_sessions, these 16 parallel session are divided in 2 server sets 1 and 2 and values for degree and required degree are 8 and 16 respectively
However, all the time, only 8 sessions which belong to server set = 1, were active though its state was waiting with event "PX Deq Credit: send blkd"
The other session which belong to server set = 2 were never active and always had waint event ='PX Deq: Execution Msg'
what could be the reason that 16 parallel session could not be started though I am the only person using the server, there aren't any batch jobs, dbms_jobs,even archivelogs (not a prod system)?
Note that paralel_max_servers setting is 16
Another issue being the duing start of the query approximately 100-115 blocks were read for the query (checked from longops) however after 60-70% blocks are read the number of blocks read / seconds falls down to 10-20 blocks / second across all parallel sessions.
I am writing below MERGE statement. In this cardinality between table_a and table_b is 1:2. I.e. each record in table_b corresponds to 2 records in table_a based on columns in ON clause.
Well this query throws below error.
----Error---
ORA-12801: error signaled in parallel query server P011
ORA-30926: unable to get a stable set of rows in the source tables
However, the same statement executes successfully when PARALLEL hint is removed altogether. (There are no duplicates in table_b based on unit,group,loc columns.)
-----Query--------
MERGE /*+ PARALLEL(8) */
INTO table_a a
USING table_b b
ON (a.unit = b.unit AND a.group = b.group AND a.loc = b.loc)
[Code]....
Does parallel hint works in cursor queries? The cursor query is something like :
cursor c is
select /*+ parallel(s,8) */
from table ref_tab s ---- >>
<where condition>;
The table ref_tab hold data for a single day at any point of time and gets truncate before loading the next days data.On average the table holds around 7 million rows and doesn't contains any index (think that's fine as all together we are loading the whole set).And, we are using bulk logic with save exceptions to open the cursor and load the data into the target table.
I have a SQL query where I am making UNION of two select statements. The table that I am joining in each select statement have indexes defined for those tables.
Now the UNION of the two select statements again in enclosed in an inline view , from which I fetching my final field values.
The select statements inside the inline view returns huge number of row (like 50 million rows).
The whole query fails with time out.
How can I optimize this query further?
Is there a way to pass Oracle Hints so that Oracle uses indexes?
I have a SQL query where I am making UNION of two select statements. The table that I am joining in each select statement have indexes defined for those tables.
Now the UNION of the two select statements again in enclosed in an inline view , from which I fetching my final field values.
The select statements inside the inline view returns huge number of row (like 50 million rows).
The whole query fails with time out.
Is there a way to pass Oracle Hints so that Oracle uses indexes?
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
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 RelatedWe 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?
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"..
We are facing the below error in our Production environment.
ORA-12827: insufficient parallel query slaves available
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]....
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?
ORA-12805: parallel query server died unexpectedly
View 4 Replies View RelatedThere is an index with degree 16 on a rac env. The base table has no degree. The table and index are not partitioned. Does the degree of index only affect index DDL (alter, rebuild etc)? Any effects on query (PQ)?
View 4 Replies View RelatedI'm very happy about materialize hint and I use it a lot. But is it possible to set some hint indicating that I want to materialize some inline view results (temp table transformation) as IOT (index organized table)?
IOT must have primary key. Ok, could it be all the columns in listing order? That would convenient. I know I can use global temporary with index, but this forces me to split one statement into parts.
I've got SQL code generated by an HP tool to make an update of itself. This code is not modifiable because was execute 'in background' from the tool updater.
The macro operations made by the updater (through sql code) are:
--create a copy of actual table and rename with '_old' suffix
--create new table, with indexes and constraints
--insert data into new table from old tables
Now, only in one table, when the data were inserted, an ORA-00001: unique constraint (ASSET_SVIL.CFG_CFGSECTIONCFGE) violated, appear.
To insert data is used an insert/select statement with an HINT /* APPEND */. We verify all the data existing in the old table (that is the data to 'migrate') but there aren't duplicate record!
BUT IF WE REMOVE THE HINT /* APPEND */, THE INSERT/SELECT STATEMENT WORKS WITHOUT GENERATE ERRORS!!
I have been asked to rewrite the following update statement without using the hint BYPASS_UJVC.
l_new_CFT_ID CASHFLOW_TYPE.CFT_ID%TYPE;
if (l_Record > 0) then
-- since at least 1 loan was found with the old type, process the actual update
update /*+BYPASS_UJVC*/ (
select
cfa.CFA_CFT_ID
[code]......
I think I am supposed to be using the Merge statement but I am not sure on how to go about it.
I have a query with FULL hint that is behaving in a strange manner. The query fetches around 700000 of data. Sometimes it fetches the data with the hint and sometimes it does not fetch any data with the hint and then I have to remove the hint and have to fetch the data. Below is the query,
select /*+ FULL(COMP_TM) FULL(TRANS_TM) FULL(INVC_TM) */
CUST_BE_ID ,
DISTR_BE_ID ,
FG_BE_ID ,
KIT_BE_ID ,
BG_ID_NO_BE_ID ,
[code]....
The statistics gathering activity of FACT_DLY_ALGND_SLS table takes around 5 hours to complete. It is a range partitioned table with subpartitions.
I am trying to understand "ordered" hint. I want to use it in my sql where I am using some left outer joins. I believe if I use this ordered hint it will be much faster.
But the problem is I am not able to understand the concept of this hint. As i did alot of search on this eveyone says it will be , even I got some similar references where it worked But I am getting contradicting explanation for on this.
I have an APP that truncates tables and loads data, which in turn makes the stats stale. I ran the query advisor (see attachment) and of course it ecommends running stats or accept a profile.I really don't want to do that as it may cause a load on my DB.
In turn, I would like to consider having my APP team change the query to pass a hint to use the best query plan.syntax to pass the hint to emulate good attached plan? Or is this a bad way to proceed?
select /* INDEX FAST FULL SCAN PK_PLACEMENT_REQUEST_QUEUE */
sum(lastshares) as "ROSEN"
from nyeo.fix_exec_reports fer, nyeo.placement_request_queue q,
nyeo.nyeo_block_control bc
where fer.clordid = q.sequence_number
and q.blockid = bc.blockid
and upper(bc.deskname) like '%ROSEN%'
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 Relatedwe 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.
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 ?
Is it possible to run a single procedure in parallel. (Not looking for multifple, using DBMS_JOB)
I am using Oracle 9i.