Performance Tuning :: Session Tracing For Shared Servers
Jan 12, 2011How to enable tracing for sessions connected using shared servers ?
View 1 Replies
ADVERTISEMENT
Performance Tuning :: Tracing Long Running Program?
Oct 25, 2011We have a program that is taking about 13-14 hours to run and we need to generate traces to see where it is taking so long. I usually use 10046 for the tracing, I'm wondering if the traces can be built incremently so that it doesn't become one huge trace file.
View 14 Replies View RelatedPerformance Tuning :: Servers Will Be Running SELECT Which Returns Zero Rows All Time
Feb 11, 2011Our application servers will be running a SELECT which returns zero rows all the time.This SELECT is put into a package and this package will be called by application servers very frequently which is causing unnecessary CPU.
Original query and plan
SQL> SELECT SEGMENT_JOB_ID, SEGMENT_SET_JOB_ID, SEGMENT_ID, TARGET_VERSION
FROM AIMUSER.SEGMENT_JOBS
WHERE SEGMENT_JOB_ID NOT IN
(SELECT SEGMENT_JOB_ID
FROM AIMUSER.SEGMENT_JOBS) 2 3 4 5 ;
[code]....
Which option will be better or do we have other options?They need to pass the column's with zero rows to a ref cursor.
Performance Tuning :: SGA-TARGET And Shared Pool?
Aug 25, 2011If I have specify the SGA_TARGET, do I still need to specify the amount of memory for SHARED_POOL_SIZE? I thought once the SGA_TARGET, auto memory management is in place and SHARED_POOL is not neccessary.
View 2 Replies View RelatedPerformance Tuning :: Shared Pool Utilization?
Jun 14, 2010we are running a 2 node rac database of oracle 10.2.0.4 in aix 6.3. The shared pool utilisation goes up when we run our jobs and sql statements. But it does not come down. It been 2 gig for over 3 days without any processes running.
Is there a threshold time period after which oracle will release the space utilisation from the shared pool????
Performance Tuning :: Pin Sql Query In Shared Pool?
Jun 25, 2010We are facing performance issues on our production instance 10g(10.2.0.4) 32-bit OELinux 5.3 2GB SGA. The performance is mainly related to one of the table which is sized about 32Gb. We have rebuild the indexes as well but problemstill persist. We are considering to pin SQL statement in shared pool which is hitting the same table frequently. But as far what we have find, is that we can only pin procedures or function in shared pool. True/false?If we can, then how to pin SQL statement in shared pool?If we can not, then is there any other way?
View 3 Replies View RelatedPerformance Tuning :: Looking For More Than Last 16 Operations For Session
May 25, 2010Doing some data conversion at the moment and using V$SESSION_LONGOPS to predict when the current task will be finished so I can run the next one.
V$SESSION_LONGOPS seems to have only the last 16 long operations for the session. Older operations are automatically purged. My bigger tables have 32 partitions, so after the first 16 are processed, I cannot tell which partition I am up to.
Googling "old longops" and "longops history" didn't work, nor did the same searches on this site. The Oracle Reference manual section on V$SESSION_LONGOPS did not mention that older entries are purged.
Performance Tuning :: SID Column In V$SESSION?
Dec 12, 2010Even though the users are not logged in why the V$SESSOIN shows the SID and why many times?
View 2 Replies View RelatedPerformance Tuning :: TKPROF Output For Session
Jun 9, 2010understanding the TKPROF output for the session that was executing an insert statement(inserting 70 lakh data) on which a row level trigger get fired and select from following table.The trace was run for 1 hour.
Table name - > GS_MAP_RCC_CCIT
This table is having 37 rows as in on single block and having primary key index on RCC_NUM that is also contained on single block.We are getting maximum wait events on db_file_sequential_read.
As per my understanding this is due to the contention for the same block because for each row these queries get fired.
Performance Tuning :: Session Doing FTS Not Appearing In Longops?
Apr 4, 2012dbo is a table with 1M records and structure similar to dba_objects
The following queries which does FTS, access same number of blocks as observed in v$session_longops
select * from dbo;
and
select * from dbo where object_type='VIEW';
Ex.
select * from dbo where object_type='VIEW';
select * from table(dbms_xplan.display_CURSOR(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
------------------------------------------------------------------------------
SQL_ID 5mh418w9stax2, child number 0
-------------------------------------
select * from dbo where object_type in('VIEW')
Plan hash value: 2675347415
-------------------------------------------
| Id | Operation | Name | E-Rows |
-------------------------------------------
|* 1 | TABLE ACCESS FULL| DBO | 77611 |
-------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_TYPE"='VIEW')
I understand that because the query is accessing large number of blocks which exceeds 6 seconds threshold, the session appears in v$session_longops And this has nothing to do with the time spent in displaying the records on the screen
Now, why the following query does not appear in v$session_longops?
select /*+ full(dbo) */ count(*) from dbo;
select /*+ full(dbo) */ count(*) from dbo;
COUNT(*)
----------
1006525
Elapsed: 00:00:01.36
dv3_erie-dev_08 >select * from table(dbms_xplan.display_CURSOR(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------
SQL_ID 4mgjwp3tv70db, child number 0
-------------------------------------
select /*+ full(dbo) */ count(*) from dbo
Plan hash value: 1423969929
--------------------------------------------
| Id | Operation | Name | E-Rows |
--------------------------------------------
| 1 | SORT AGGREGATE | | 1 |
| 2 | TABLE ACCESS FULL| DBO | 1007K|
--------------------------------------------
The table has all nullable columns and thus I assume the index on ID can't be used for this COUNT Thus ideally it shall be accessing same number of blocks and thus shall appear in v$session_longops
Performance Tuning :: How To Set Commit_write Parameter To (BATCH) In Another Session
May 16, 2013How to set commit_write parameter to 'BATCH' in another session.
View 3 Replies View RelatedPerformance Tuning :: How To Trace Different Session From Current With LEVEL 4
Jun 13, 2012I've been searching the web for examples of how to run a trace.It's needed for a session different then current in trace LEVEL 4 (I need the bind variables values in the trace).
Unfortunately, I couldn't trace with DBMS_SUPPORT.START_TRACE_IN_SESSION, I understand that this is because it was only introduced in Oracle 11g.
how can i trace a session in level 4 on Oracle 10g for another session?
Performance Tuning :: SQL ID And Text - No Record Being Found At Session Level
Feb 22, 2012At my prod instance one sql query ran for about 9 hours.Now the sql query completed with success.Suddenly our ops team want to know the which sql query was running for 9 hours.As the query got success no record is being found at session level.
I know the SID.How Do I get the SQL query ?
Performance Tuning :: How To Find Out Session Consuming High Resource In Past
Mar 28, 2013How can I find out the particular oracle session which was consuming high memory in the past?
I can't get the data in v$sessstat
Unable to get the information in AWR
dba_hist_active_session_history do not have field which indicate memory related information
Shall I concetrate on EVENT in dba_hist_active_session_history which continuosly had sort, direct path read
Or
Locate sql_id from dba_hist_sqlstat with high SORTS_DELTA for snapshots belonging to problematic time period and then using the sql_id query dba_hist_active_session_history
which approach I shall take to find out the session which consumed most memory in the past?
Updating Table In Session - Shared Versus Exclusive Lock
Jan 28, 2013I have a question. If we have two scott sessions. I am updating a table EMP in session
1. It means it is exclusively locked.It cannot be used by session 2. Then can we use select command on table EMP in session
2.? This command should not work according to me. But it is working.
Performance Tuning :: Tools For Database Tuning And Instance Tuning
Jul 12, 2010Looking 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?
Performance Tuning :: Merge Statement Tuning For 100M Records In Table?
Oct 31, 2011I 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].....
Performance Tuning :: How Length Of Column Width Effects Index Performance
Sep 30, 2010How 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'.
Performance Tuning :: Fragmentation Can Reduce Performance In Query Times
Jun 16, 2010I 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?
Performance Tuning :: Method Of Tuning Database - Row Reduction?
Oct 20, 2010There 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 ?
Performance Tuning :: Performance Standard Edition Without Partitioning?
Jun 16, 2011How 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?
Performance Tuning :: Procedure Performance On New Database Import?
Nov 15, 2010Testing 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?
Performance Tuning :: Checking Delete Performance In Package
Apr 12, 2013I 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.
Performance Tuning :: Query Performance Gain Using Statistics?
Aug 9, 2010Somewhere 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.
Performance Tuning :: How To Improve The Performance Of Export Job (expdp)
Dec 6, 2011I 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?
Server Administration :: Oracle Tracing At Stop And Start
Jan 20, 2012I need to add some kind of tracing in oracle 11g on linux when oracle starts and stops - as at times oracle takes a long time to start.
View 9 Replies View RelatedPerformance Tuning :: DECODE In WHERE CLAUSE Performance?
Oct 17, 2011The 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]...
Performance Tuning :: Same Data But Different Performance Results
Sep 3, 2010what 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 RelatedPerformance Tuning :: DB Performance Keys?
Mar 17, 2012are 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 RelatedPerformance Tuning :: Options While Using RBO?
Mar 27, 2013I 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!