Performance Tuning :: Maximum Wait Events On PX Deq - Execute Reply?

Oct 19, 2010

I have a partitioned table with degree for parallelism defined as 10.

I am getting maximum wait events on PX Deq: Execute Reply.For 2 hours trace the wait event is almost 1.5 hour.

I have done some seraching and i found this.

Quote:
In principle:A parallel query against a partitioned table will use one slave per partition if the query is thought to span multiple partitions, and it can use all slaves on a single partition if the query is thought to target just one partition. Unfortunately, this is NOT strictly true.

It is possible for the optimizer to decide to use parallelism at degree M when accessing N partitions. Sometimes this can lead to very inefficient, brute-force, processing when a more efficient path is available. This can be a particular problem with multi-table joins that should be partition-wise joins. You may be better off leaving the tables defined as non-parallel and adding explicit parallel hints to the code for critical queries.

So i have following questions
1) What is the meaning of PX Deq: Execute Reply.
2) Is this not recommended to use DEGREE clause in Partitioning.
3) Defining DEGREE clause in partioning of table will automatically executes DML on table PARALLELY same a PARALLEL hint clause

View 6 Replies


ADVERTISEMENT

Performance Tuning :: Digging Out Wait Events

Oct 15, 2013

I am using 11.2.0.3.0 version of oracle. As told by the DBAs that two days back the system realised high wait_class i.e 'Application' wait, and its a lot high than regular value as per our system and we need to dig it down, to avoid any future issue. now using below query , i found that the high wait time due to wait class 'Application' is actually belongs to particular event 'SQL*Net break/reset to client', and the sample time was '9 AM' morning.

select time,
round(max(case when event = 'SQL*Net break/reset to client' then time_waited_delta/1e3/decode(total_waits_delta,0,1,nvl(total_waits_delta,1)) end) ,2) SQL_break_reset_client,
round(max(case when event = 'Wait for Table Lock' then time_waited_delta/1e3/decode(total_waits_delta,0,1,nvl(total_waits_delta,1)) end),2) Wait_for_Table_Lock ,
round(max(case when event = 'enq: KO - fast object checkpoint' then
[code]....

Now i want to track it down further to the 'session/sql query/application' level resulting into such high value of wait time. so i queried, dba_hist_ active_ session_history, for the same sample duration (8.30 to 9 am) having event 'SQL*Net break/reset to client', and got two sessions(123,154) and their serial# , but there i got one sql_id(3ahgrey10ogh i.e a 'SELECT' query) specific to one session(123) but for other(125) no sqlid, and also SUM (wait_time + time_waited) is showing '0'.

Then i just removed the sampletime filter from the below query and observed that the same 'same session(123)+session serial#' was activated since 4 days back and was experiencing same waitevent 'SQL*Net break/reset to client', (it was having normal wait event 'db file sequential read' for sometime then after it went for this event ).

SELECT user_id,
PROGRAM,
machine,
session_id,
SESSION_SERIAL#,
sample_time-7/24,
session_state,
[code]....

View 14 Replies View Related

Performance Tuning :: AWR Report Network Foreground Wait Events?

Jun 29, 2011

I ran an AWR report. The database looks fine, but a data load that loaded 1 Million rows an hour is now doing 500K per hour.

Wait Class Waits %Time -outs Total Wait Time (s) Avg wait (ms) %DB time
DB CPU 224 80.70
Other 2,668 0 28 10 9.99
System I/O 4,753 0 9 2 3.23
Administrative 1 0 6 5543 2.00
Commit 357 0 4 11 1.46

[code]....

The network value for wait: 630,601. What does this mean? Anything I should look at? When it was 1million per hour, the value was 4,563,000.

Top 5 Timed Foreground Events

Event Waits Time(s) Avg wait (ms) % DB time Wait Class
DB CPU 224 80.70
unspecified wait event 2,666 28 10 9.99 Other
control file sequential read 4,753 9 2 3.23 System I/O
switch logfile command 1 6 5543 2.00 Administrative
log file sync 357 4 11 1.46 Commit

View 8 Replies View Related

Performance Tuning :: Top Wait Events - Specific Document To Analyze AWR Report

Jun 24, 2013

We are using 11.2.0.3.0 on solaris 10 facing slow performance, following are the Wait Events in AWR report, Also if any specific document to analyze AWR report and to pin point the performance bottleneck.

Foreground Wait Events
**********************
Avg
%Time Total Wait wait Waits % DB
Event Waits -outs Time (s) (ms) /txn time
-------------------------- ------------ ----- ---------- ------- -------- ------
direct path read 308,729 0 21,191 69 58.0 39.5
db file sequential read 208,754 0 3,742 18 39.2 7.0
cursor: pin S 19,541,899 0 2,561 0 3,668.5 4.8
[code]....

View 2 Replies View Related

Performance Tuning :: Top 5 Timed Events?

Aug 31, 2010

In my awr reports I have CPU time(in seconds) as one of top 5 timed events.Does that mean oracle is waiting for my cpu to execute the statements.But my cpu usage is 0-10% always

View 4 Replies View Related

Performance Tuning :: Wait / Sleep Command In SQL?

Mar 15, 2012

How to use wait/Sleep command in SQL. I have to write a script that takes about 20 min to execute.

View 1 Replies View Related

Performance Tuning :: Buffer Busy Wait?

Dec 20, 2011

I noticed oracle background process ora_fbda_padwsdpr is suffering from buffer busy wait. When i further finding the object, it was on SYS_FBA_FA tables.

what is this is causing BUFFER BUSY WAIT. Also to add we have disabled flashback database.

View 4 Replies View Related

Performance Tuning :: Buffer Busy Wait DBMS_ALERT_INFO

Oct 2, 2013

I've have a system where the most buffer waits are spent on system tables like

SYS.DBMS_ALERT_INFO
and SYS.DBMS_LOCK_ALLOCATED

Would it convert the system tablespace from dictionary managed to locally managed?

View 7 Replies View Related

Performance Tuning :: Undo Tablespace Full / Which Is Rectified But Now Having Big WAIT

Mar 7, 2012

we have a situation where both undo tablespaces were almost filled i.e UNDOTBS1 99% and UNDOTBS2 100% filled so i add data files to it and then i found a lot of blocking session and was just killing them through EM then i stop my front end listener and also down the service, now i don't have any blocking session but on EM a big WAIT is coming. alert log shows nothing serious, it was showing deadlock but now it is over as well.

View 8 Replies View Related

Performance Tuning :: How To Avoid Virtual Circuit Wait As Application Developer

Aug 7, 2013

I'm an application developer of an automotive company and developing a lot of database-based applications with either oracle forms or c#.Since we've moved from a 10g rac to 11g using a shared server configuration, the prevailing and overwhelming topic of addm performance analysis is "unusual network wait event" caused by virtual circuit waits. Therefore I cannot use grid control to detect bad sql as I could in 10g anymore, because all "tunable" sql is wiped out by virtual circuit wait.In top activity, I see virtual circuit wait on every type of statement (select, insert...) and pl/sql execution.

What do I have to do as an application developer to avoid virtual circuit waits? Especially in C#: we normally use auto committed dml statements and selects to fill either a datatable or generic list with a data reader. Usually we close a connection after each statement, but/and we are using connection pooling. How can such a activity cause virtual circuit waits? In Oracle Forms: Seems that we have a virtual circuit wait if we show sorted data in a block where not all records are fetched from database. It doesn't make sense to us to rewrite all blocks to always get all records due to performance reasons.

How do I have to write and execute my statements in C#, oracle forms and/or pl/sql to avoid virtual circuit wait?

View 4 Replies View Related

PL/SQL :: Wait Event Starts With SQL*Net Message From Client - Time Wait 178577 Units

Dec 11, 2012

I am trying to look at wait events for a long running query in TOAD.I start the query on one instance of TOAD and open the Session Browser on another instance.But I am surprised to find that in "TOtal Waits" on the RHS-> SQL*Net message from client is the longest time taking and is already -> 178577 units whereas I have just started the query.

Whereas in the Current Waits it shows DB File Scattered Read currectly as some seconds.

View 5 Replies View Related

Performance Tuning :: Tools For Database Tuning And Instance Tuning

Jul 12, 2010

Looking to understand the difference between instance tuning and database tuning.

What is the difference between these two tuning exercises? I understand that an instance is memory based structures (logical) where as database consists of physical structures.

However, how does one tune a database the physical structure? Does it have to do with file placements/block sizes etc. Would you agree that a lot of that is taken care by ASM now in 11g? What tools are required/available (third party as well as oracle supplied) for these types of tuning scenarios?

View 1 Replies View Related

PL/SQL :: Sending Email With Reply To Sender?

Sep 3, 2013

In the database there is a job that monitors the delivered date.5 weeks before this date a email is send.I this mail i would like to implement a notification button. the receiver will get a mail with notification:o Yes i will succeedo No, i wil not succeed this answer has to be send back to the sender . how to do this. note: the receivers can have different mail clients!

View 10 Replies View Related

Performance Tuning :: Merge Statement Tuning For 100M Records In Table?

Oct 31, 2011

I have two tables with 113M records in DWH_BILL_DET & 103M in prd_rerate_chg_que and Im running following merge query, which is running for 13 hrs to update records, which is quiet longer time.

SQL> explain plan for MERGE /*+ parallel (rq, 16) */
INTO DWH_BILL_DET rq
USING (SELECT rated_que_rowid,
detail_rerate_flag_code,
rerate_sel_key,

[code].....

View 39 Replies View Related

Performance Tuning :: How Length Of Column Width Effects Index Performance

Sep 30, 2010

How the length of column width effects index performance?

For example if i had IOT table emp_iot with columns:
(id number,
job varchar2(20),
time date,
plan number)

Table key consist of(id, job, time)

Column JOB has fixed list of distinct values ('ANALYST', 'NIGHT_WORKED', etc...).

What performance increase i could expect if in column "job" i would store not names but concrete numbers identifying job names.
For e.g. i would store "1" instead 'ANALYST' and "2" instead 'NIGHT_WORKED'.

View 24 Replies View Related

Performance Tuning :: Fragmentation Can Reduce Performance In Query Times

Jun 16, 2010

I have a question about database fragmentation.I know that fragmentation can reduce performance in query times. The blocks are distributed in many extents and scans process takes a long time. Oracle engine have to locate the address of the next extent..

I want to know if there is any system view in which you can check if your table or index has high fragmentation. If it's needed I will have to re-create, move or rebulid the table or index, but before I want to know if the degree of fragmentation is high.

Any useful script or query to do this, any interesting oracle system view?

View 2 Replies View Related

Performance Tuning :: Method Of Tuning Database - Row Reduction?

Oct 20, 2010

There is a simple way to increase the performance of a query by reducing the row-size of the table it hits. I used it in the past by dividing the table into smaller parts and querying respective smaller table in each query.

what is this method called ? just forgot the method and can't recall it. what this type of row-reduction optimization is called ?

View 6 Replies View Related

Performance Tuning :: Performance Standard Edition Without Partitioning?

Jun 16, 2011

How many records could I have in a single table without performance degradation with Standard Edition without partitioning with cutting-edge server (8 or 12 cores, 72 GB RAM, FC 4 Gbit, etc...) and good storage?

300 Millions in only one table with 500K transactions / day is too much?

Simple database with simple schema.

How many records begin to be too many?

View 2 Replies View Related

Performance Tuning :: Procedure Performance On New Database Import?

Nov 15, 2010

Testing our 9i to 11g upgrade, we've imported the entire DB into the new machine.We've found that certain procedures are really suffering performance problems. BUT, we've also found, that if we check out a production copy of the procedure from our source code control, and reinstall it, the performance issue goes away. Just alter the procedure and recompiling does NOT work.

The new machine where the 11g database exists is slightly different than the source, but it's not like we have this problem with every procedure. It's only a couple.

any possible reason that we'd have to re-install a procedure to correct a performance problem?

View 13 Replies View Related

Performance Tuning :: Checking Delete Performance In Package

Apr 12, 2013

I need to check the package performance and need to improve the package performance.

1. how to check the package performance(each and every statement in the package)?
2. In the package using the delete statement to delete all records and observed that delete is taking long time to delete all the records in the table(Table records 7000000). This table is like staging table.Daily need to clean the data before inserting the data into it. what can I use instead of Delete.

View 13 Replies View Related

Performance Tuning :: Query Performance Gain Using Statistics?

Aug 9, 2010

Somewhere I read that we should not use hints in Oracle production environments, but we can use hints in the development environment and on achieving the desired execution plan we can adjust the 'statistics' to follow that plan without hints.

Q1. If it is true what statistics do we adjust for influencing the execution plan and how?

For example, I have the following simple query:

select e.empid, e.ename, d.dname
from emp e, dept d
where e.deptno=d.deptno;

emp.empid, emp.deptno and dep.deptno columns have indexes and the tables have the standard structure as found in the basic oracle examples.

If I look at the execution plan of the above query then I see that the driving table is empand the driven table is dept.Also the type of join that is taking place is 'Nested Loop'.

Questions: With respect to the above query,
Q 2. If I want to make dept the driving table and emp the driven table then how can I adjust the statistics to achieve that?
Q 3. If I want to use hash join instead of a nested loop join then then how can I adjust the statistics to achieve that?

I can put the ordered and the use_hash hint to effect this but again I have heard that altering statistics is a more robust way to control an execution plan as compared to hints.

View 6 Replies View Related

Performance Tuning :: How To Improve The Performance Of Export Job (expdp)

Dec 6, 2011

I have an issue with export(expdp).

When i exporting an user using expdp utility, the load the on the server is going up-to 5. The size of the database is 180GB. Below is the command that i use for export.

expdp sys/xxxx directory=dbpdump dumpfile=expdp_trk_backup.dmp logfile=expdp_trk_backup.log exclude=statistics schemas=trk

Do i need any look into any memory parameters for this?

View 1 Replies View Related

Performance Tuning :: DECODE In WHERE CLAUSE Performance?

Oct 17, 2011

The following query gets input parameter from the Front End application, which User queries to get Reports.There are many drop down boxes like LOB, FAMILY, BRAND etc., The user may or may not select values from drop down boxes.

If the user select any one or more values ( against each drop down box) it has to fetch all matching values from DB. If the user does'nt select any values it has to fetch all the records, in this case application will send a value 'DEFAULT' (which is not a value in DB ) so that the DB will fetch all the records.

For getting this I wrote a query like below using DECODE, which colleague suggested that will hamper performance.From the below query all the variables V_ are defined in procedure which gets the values selected by user as a comma separated string here V_SELLOB and LOB_DESC is column in DB.

DECODE (V_SELLOB, 'DEFAULT', V_SELLOB, LOB_DESC) IN
OPEN v_refcursor FOR
SELECT /*+ FULL(a) PARALLEL(a, 5) */
*
FROM items a
WHERE a.sku_status = 'A'

[code]...

View 9 Replies View Related

Performance Tuning :: Same Data But Different Performance Results

Sep 3, 2010

what the principal things to look at when we have for the same query different performance results are?I have 2 different bases: the plan and data are the same but performance results are very differents.

View 10 Replies View Related

Semantic Technologies :: Maximum Node ID Larger Than Java Maximum Integer Value

Mar 7, 2013

I'm currently doing migration from Oracle 10gR2 RDF to Oracle 11gR2 Semantic Technology.I followed the steps on the documentation and successfully created the network using the following:

-----
EXECUTE SEM_APIS.CREATE_SEM_NETWORK('rdf_tblspace');
CREATE TABLE rdf_network_trace (id NUMBER, triple SDO_RDF_TRIPLE_S);
--Created SEQUENCE andTRIGGER FOR rdf_network_trace id
[code]....

when I looked at my Node Ids, they were like +635762253807433724+, +6118969225776891730+. The problem is, I am not the one who is assigning Node Ids, They were automatically generated when inserting TRIPLE data to the rdf table.

Did I miss something when I created my network?

View 11 Replies View Related

Performance Tuning :: DB Performance Keys?

Mar 17, 2012

are the most important performance keys we have to calculate or take in account to preserve or to increase the DB performance in terms of response times, and whatsoever according to performance ?

View 8 Replies View Related

AWR Report Shows Wait Time Is High But OS Shows Wait Time As Normal

Oct 2, 2012

We have a Oracle 10g database with RAC and Dataguard. When we look at the AWR report, the wait time shown by Oracle for this database is very high.

Service Time : 15.36%
Wait Time : 84.64%

This would imply Oracle is waiting for resources 85% of the time and only processing SQL queries during 15% of its non-idle time. However when we check the OS (RHEL), the iowait is only about 10% and the CPU is 80% idle. This means that that processing horsepower is available.

As such, the results between the OS and Oracle database (AWR report) seems contradictory. OS says we have CPU/IO capacity, however Oracle says we don't.

View 17 Replies View Related

Performance Tuning :: Options While Using RBO?

Mar 27, 2013

I am working on an assignement where client is using Oracle 10g but stuck to using RBO Now the application team, from the GUI available to them build dynamic queries and some of them run very slow.

Neither the code can not be changed to tune the queries nor do we get the exact step in the plan which is an issue (being RBO).For some long running queries the Tuning advisor is not producing any recommendations.

Another hurdle is that all the application users are using same application user id so we can not write a logon trigger to use CBO for some particular queries to see what is happening in the background!

View 11 Replies View Related

Performance Tuning :: Next SQL Sentence

Jul 4, 2012

I want to tuning the next sql sentence. In this sql I want to get the hash_value and sql_text of the sentences that it's causing TX blocks. Is it possible?. This sentence works fine but sometimes It's slow.

SELECT DISTINCT hash_value,
sql_text
FROM gv$sql sq
WHERE hash_value IN (SELECT DISTINCT prev_hash_value
FROM gv$session se
WHERE sid IN (SELECT sid
FROM gv$lock l
WHERE type = 'TX'
AND ctime >= 2000
AND l.inst_id = se.inst_id
AND l.sid = se.sid)
AND sq.inst_id = se.inst_id);
[code]....

View 7 Replies View Related

Performance Tuning :: Different SQL ID For Same HASH-VALUE?

Nov 2, 2012

I see one of my SQL's which is ran by the user on a 10.2.0.3 database changing its SQL_ID after some runs even if the query is not changed a bit! However the HASH VALUE for this query remains the same.

how a same query can have different SQL_ID's but same HASH_VALUE?

Note: Statistics are not modified on the base tables of this query.

View 10 Replies View Related







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