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?
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.
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 ).
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
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.
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
Below query is getting delayed becasue of BitMap Indexes on the table. I am trying to avoid indexes by using Hints in the query but unable to do so, Details are as follows.
explain plan for SELECT cbu_cid, cbu_cid_customer_en_nm, COUNT (billg_acct_no) AS billg_acct_no, SUM (subscriber_cnt) AS subscriber_cnt FROM daily_view WHERE (billg_system_id = 'TM' AND mktg_sub_segment_a_nm = 'TM') AND (cbu_cid NOT IN ('0001988048', '0001379962', '0001350469')) GROUP BY cbu_cid, cbu_cid_customer_en_nm HAVING SUM (subscriber_cnt) > 10 ORDER BY subscriber_cnt DESC; [code]....
I have tried with ALL_ROWS & PARALLEL.how to avoid above two indexes in a query.
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]....
avoid duplication of **where** clause in my query.
In my below query, **JOIN** condition is same for both the queries and **WHERE** condition also same except this clause "and code.code_name="transaction_1" In **IF ** condition only credit and debit is swapped on both queries, due to this **Credit and Debit** and this where clause "and code.code_name="transaction_1" I am duplicating the query. avoid this duplication. I am using oracle 11g
SELECT day AS business_date, SUM(amount) AS AMOUNT, type_amnt AS amount_type,
There is coulmn called DATA in a table with LONG RAW datatype. we are facing more than 60% chained rows in this table because of this LONG RAW column.
It is very difficult to clean up these chained rows periodically. Since an application using this table is a business critical interms of high availability.Hence, is there any other way in oracle to avoid chained rows permanently in future?
We have a DELETE statement when coming from application is not using index but when run from Toad or SQLplus as same user uses index. Explain plan also shows using index.I did a query on v$sql below is the output of the query( I have attached the same as a txt file). All the stats are up to date and confirmed from the developer the variable B1 is using the same datatype as column MAXMKY.
SQL_TEXTSQL_ID DISK_READSOPTIMIZER_HASH_VALUE DELETE LOTA WHERE MAXMKY=:B1 2g2prrp3z56ah19,099,1891,846,735,884 DELETE LOTA WHERE MAXMKY=:B1 2g2prrp3z56ah0 1,846,735,884 OPTIMIZER_COST HASH_VALUEPLAN_HASH_VALUE MODULEPARSING_SCHEMA_NAME
My ERP Application is responding fast while running reports or saving entries, if Oracle 10g Express Edition (XE) is installed. But in Oracle 10g Enterprise Edition or Standard Editions the same application is running very slow.
Name some database tool from which I can check the SQL Queries which my application is running.
NOTE: I do not want to check the queries which I am executing at the SQL command prompt but queries that are being run by my application at the backend.
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.
What could be the reasons that some queries execute fast when executed on sqlplus on server, whereas the same queries run slower with same input values fed from application screen?
One issue I guess would be bind variable peeking while using application whereas executing from sqlplus is causing hard parsing and thus getting rid of "peeking"
If displaying the data on application screen is taking time after data has been fetched, where I can see this delay?
I understand the elapsed time under 'Fetch' in tkprof will show time taken to fetch from database and not the time taken to be displayed in the application GUI
finally how to set arraysize in jdbc to improve performance by reducing roundtrips?
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?
on our 10.2.0.5 database, when we run full backup, my system performance comes to an halt. we run full backup and then do a validate backup to validate the structure of the database etc. Database performance takes a hit and all of the application connections goes in wait mode: On ASH or AWR - this is the top wait i see:
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,
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'.
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?
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 ?
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?
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?
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.
