Performance Tuning :: How To Find Out Session Consuming High Resource In Past

Mar 28, 2013

How 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?

View 8 Replies


ADVERTISEMENT

Performance Tuning :: How To Find Tables In Database On Which High DMLs Firing

Feb 4, 2011

How to find the tables in the database on which high DMLs are firing.

View 5 Replies View Related

Windows :: TNSLSNR.EXE Consuming High Memory

Apr 12, 2011

I am using Oracle 11g On windows 2003 Server, and in the task manager, tnslsnr.exe comsuming approx 1 GB of RAM. After restarting the listener it came to some mb(20-30). but Again after few days it raies and consume lots of memory..

View 11 Replies View Related

Performance Tuning :: Why There Is No Row Resource Operation Information In Tkprof Output

May 13, 2011

why there is no "Row Source Operation" information when I trace a long and complicated SQL query (It takes at least 25 minutes ro run)?

However, I can get the "Row Source Operation" information for a simple SQL Query.

Here are the tkprof output for a simple query and complicated query:

tkprof Output for a simple query
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 13 0.00 0.00 0 16 0 173
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 15 0.00 0.00 0 16 0 173

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 3139

Rows Row Source Operation
------- ---------------------------------------------------
173 TABLE ACCESS FULL MY_TABLE (cr=16 pr=0 pw=0 time=0 us)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 13 0.00 0.00
SQL*Net more data to client 24 0.00 0.00
SQL*Net message from client 13 5.33 5.47

Tkprof output for a complex query
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.38 0.40 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.38 0.40 0 0 0 0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 3139

No Row Source Operation information here Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
db file sequential read 73062 0.25 692.23
db file scattered read 16099 0.18 76.65
latch: shared pool 4 0.02 0.03
latch: cache buffers chains 3 0.04 0.04
latch free 16 0.00 0.00

View 1 Replies View Related

Performance Tuning :: Call From Specified Machine Not Assigned To Database Resource Consumer

Sep 6, 2013

I am using Release 11.2.0.3.0 - 64bit Production version of oracle. Now we are having 3-tier architecture, (firewal/web/app/DB).Now i saw , some of the 'sql' queries, running till ~10hrs in my database and those are part of application(module JDBC THIN CLIENT). After had a talk java guys, they ask to kill the sessions specific to those queries. They are part of search TO, in which user put some large values for the date range and went to other TAB, but these queries gets running infinitely in the database, and user is not interested in the result set.

So how to avoid these things, as because in past, our database has suffered resource contention leading to application slowness. So i was planing to set different timeouts using 'database resource consumer group' for online user request and batch request depending on the app server(that is by machine names) request.

So i have done below set up in my local to test one scenario, in which i will try give a database call from difference machine, and it should get timeout after the specified duration. But its not working , as expected. The calls from the specified machine are not getting assigned to the created 'Consumer group'.

Begin
-- create the pending area
dbms_resource_manager.create_pending_area();
END;
/
BEGIN
-- Create the consumer group

[code]....

After this when i am verifying calls from machine, 'LR9XY7T8' they are belongs to the consumer group 'OTHER_GROUPS' and sql query not getting timed out within 60 seconds as mentioned.

View 9 Replies View Related

Performance Tuning :: Low Cost And High Cpu?

Jul 23, 2010

I am facing one performance issue, in which the query cost is very low compare to cpu cost and as a result the cpu always show the high graph.I am also attaching the gv$sql and gv$sql_plan data of this query.

This is the query:

SELECT PTLS.ITEMTYPE , PTLS.ITEMID , PTLS.STAGEID, TS.USERID, SUM(PREVIOUSHOURS) AS PREVIOUSHOURS, MIN(STARTDATE) AS STARTDATE, MAX(STARTDATE) AS ENDDATE FROM PROJECTTIMELOGSSTAGE PTLS, PROJECTTIMESHEETITEM PTSI, TIMESHEET TS WHERE PTLS.PROJECTID = :B2 AND TS.TIMESHEETID = PTSI.TIMESHEETID AND TS.USERID = :B1 AND PTSI.TIMESHEETID = PTLS.TIMESHEETID AND PTSI.ITEMTYPE = PTLS.ITEMTYPE AND PTSI.ITEMID = PTLS.ITEMID AND (PTSI.ISPWFITEM = 'N' OR PTSI.ISPWFITEM IS NULL) AND PTLS.ITEMTYPE NOT IN ('OtherTsk','NewTsk','Loc','Glb') AND (PTLS.ITEMTYPE, PTLS.ITEMID ) IN (SELECT ITEMTYPE, ITEMID FROM PROJECTTIMELOGSSTAGE PTLS1 WHERE PTLS1.PROJECTID = :B2 AND PTLS1.TIMESHEETID = :B3 ) GROUP BY PTLS.ITEMTYPE, PTLS.ITEMID, PTLS.STAGEID, TS.USERID

View 17 Replies View Related

Performance Tuning :: Query With High Cpu Usage?

Jan 13, 2009

Is there any way to tune the following query using lot of CPU:-select description,time_stamp,user_id from bhi_tracking where description like 'Multilateral:%'The explain plan for this is query is:-

---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 178K| 6609K| 129K|
| 1 | TABLE ACCESS FULL| BHI_TRACKING | 178K| 6609K| 129K|
----------------------------------------------------------------

Bhi_tracking is used for reporting purpose and contain millions of records.Generally we keep one year data in this table and delete the remaining.Can I drop the table after taking export and then import it back or can i truncatethe table and then insert the rows into it to enhancethe performance.

View 14 Replies View Related

Performance Tuning :: High CPU Cost And Low Optimizer?

Jan 22, 2009

how to reduce the cpu cost for a query at query level.

View 10 Replies View Related

Performance Tuning :: Collection Count High In Memory

Feb 13, 2013

I have two tables with same columns(15 of them), I am trying to find difference between two tables using minus operator and then insert in stage table using below code

Issue is table1 has 50 million records
table2 is empty

so when first time when we execute this v_collection1,v_collection2 collection will have 50 million records in it which will go in memory, I think this is not good, because going in memory will eat memory and resources while sorting and other activities ?

After fetching records in collection we are inserting that in stage table and then COMMIT so i think that wont be good because committing 50 million will generate large amount of redo?

below is snippet of my code

DECLARE
type lst_collection1
IS
TABLE OF table1.col1%type INDEX BY binary_integer;
type lst_collection2
IS
[code].......

View 4 Replies View Related

Performance Tuning :: Does High Volume Of Data Can Change Plan

Jun 4, 2010

The prod stats has been implemented in development. The stats has been gathered 2 months back on dev while in production the stats has been gathered 2 weeks back.

My question shouldn't the high volume of data causes changes in plan in both the environment? My thinking is that plan can be different as the high volume of data are changing in prod it may lead to a different plan.

View 6 Replies View Related

Performance Tuning :: Query High Temp Utilization (17GB)

Nov 27, 2012

The below query is utilizing more than 17 Gb temp space. But still it is getting failed out due to insufficient temp space. is there any way to rewrite this query to reduce the temp utilization?

SELECT T12.FRGHT_AMT_CURCY_CD,T23.LAST_UPD,T11.PAR_OU_ID,T9.MAIN_PH_NUM,T23.DISCNT_PERCENT,T23.X_ERROR_NUM,T18.ADDR,T14.X_ECO_B_END_1141,
T14.X_ECO_A_END_1141,T9.X_ECO_VALIDATION_FLG,T23.X_ECO_ERR_DESCR,T14.ASSET_NUM,T20.NAME,T23.X_ECO_REASON2,T14.X_ECO_B_END_ID,
T14.ASSET_NUM,T14.X_ECO_B_END_IWPC,T23.X_AE_CON_PH_NUM,T23.SHIP_ADDR_ID,T19.NAME,T23.X_BE_CON_LST_NAME,T23.CREATED_BY,T23.X_ECO_LOCATION,T8.LOC,
T3.MODIFICATION_NUM,T10.INTEGRATION_ID,T23.INTEGRATION_ID,T23.X_MESSAGE,T9.PR_ADDR_ID,T12.ACCNT_ID,T23.X_BEARERNO,T23.X_SUB_STATUS_CD,
[code]....

View 3 Replies View Related

Performance Tuning :: High CPU Utilization In RAC And Log File Sequential Read Event

Apr 13, 2011

In a 3-node RAC setup; one node is showing high CPU utilization around 40~50%. The CPU utilization was less than 20% 10 days back but from 9th oldest day it jumped and consistently shows the double figure. I ran AWR reports on all three nodes and found one node with high CPU utilization and shows below tops events-

EVENT WAITS TIME(S) AVG WAIT(MS) %TOTAL CALL TIME WAIT CLASS
CPU
time 5,802 34.9

RFS
ping 15 5,118 33,671 30.8 Other

Log file sequential
read 234,831 5,036 21 30.3 System I/O

Sql*Net
more data from
client 24,1711,08745 6.5 Network

Db file sequential
read130,939 4533 2.7 User I/O

Findings:-
On AWR report(file attached) for node= sipd207; we can see that "RFS PING" wait event takes 30% of the waits and "log file sequential read" wait event takes 30% of the waits that occurs in database.

1)Are these symptoms of undersized log buffer?
2)I feel Network wait can be reduced by tweaking SDU & TDU values based on MDU.

View 2 Replies View Related

Performance Tuning :: Oracle 11g High Memory Usage Dbw0 And Dbw1

Jul 31, 2012

We are using the 11g AMM feature and Memory_Target set to 96GB and total RAM on the Server is 128GB Now the top and free shows up only 200MB memory free on the system.

There are 2 process dbw0 and dbw1 which consumes the top memory and this is 30GB per dbw.

Why is the dbw process taking up so much memory when there is not much load on the database.

View 4 Replies View Related

Performance Tuning :: Query Taking High CPU And Execution Time In Oracle 11gR2

Dec 24, 2012

The below query is taking high CPU almost 98% and longer time to execute.

SELECT ancestor,
Max(D.alarmstate) ALARMSTATE,
Max(D.sialarmstate) SIALARMSTATE,
Max(D.uncralarmstate) UNCRALARMSTATE,
Max(M.commstate) COMMSTATE,
Max(M.nncommstate) NNCOMMSTATE,
Max(M.servicestate) SERVICESTATE,
Max(M.abnormal) ABNORMAL,
CASE
[code]....

View 15 Replies View Related

Performance Tuning :: Looking For More Than Last 16 Operations For Session

May 25, 2010

Doing 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.

View 3 Replies View Related

Performance Tuning :: SID Column In V$SESSION?

Dec 12, 2010

Even though the users are not logged in why the V$SESSOIN shows the SID and why many times?

View 2 Replies View Related

Performance Tuning :: TKPROF Output For Session

Jun 9, 2010

understanding 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.

View 14 Replies View Related

Performance Tuning :: Session Doing FTS Not Appearing In Longops?

Apr 4, 2012

dbo 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

View 6 Replies View Related

Performance Tuning :: How To Set Commit_write Parameter To (BATCH) In Another Session

May 16, 2013

How to set commit_write parameter to 'BATCH' in another session.

View 3 Replies View Related

Performance Tuning :: Session Tracing For Shared Servers

Jan 12, 2011

How to enable tracing for sessions connected using shared servers ?

View 1 Replies View Related

Performance Tuning :: How To Trace Different Session From Current With LEVEL 4

Jun 13, 2012

I'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?

View 8 Replies View Related

Performance Tuning :: SQL ID And Text - No Record Being Found At Session Level

Feb 22, 2012

At 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 ?

View 4 Replies View Related

Performance Tuning :: How To Find Index To Rebuilt

Oct 3, 2011

i am trying to find the index want to rebuild or not for that i have analyzed that index after that i don't know how to calculate the ration could any one steps to do calculate the following ratio

Run the ANALYZE INDEX command on the index to validate its structure and then calculate the ratio of LF_BLK_LEN/LF_BLK_LEN+BR_BLK_LEN and if it isn?t near 1.0 (i.e. greater than 0.7 or so) then the index should be rebuilt. Or if the ratio BR_BLK_LEN/ LF_BLK_LEN+BR_BLK_LEN is nearing 0.3.

View 2 Replies View Related

Performance Tuning :: How To Find Literal SQL Statement

Jan 30, 2012

Is there is any view/query from where I can find how many sql using literals.

View 4 Replies View Related

Performance Tuning :: Find SQL Using Hash Value - ORA-00060

Nov 18, 2010

Getting an ORA-00060 in our database. This is the alert log:

Wed Nov 10 08:01:54 2010
Global Enqueue Services Deadlock detected. More info in file
/opt/oracle/admin/ngboot/bdump/ngboot1_lmd0_13119.trc.

This is part of the lmd file:

oprocp : (nil)
opid : 0
group lock owner : (nil)
xid : 0000-0000-00000000
dd_time : 0.0 secs
dd_count : 0
timeout : 0.0 secs

[Code] ......

How do I find the SQL causing this? I have the hash value of 3180952482. should I open up en SR as this i logged in an lmd trace?

View 2 Replies View Related

Performance Tuning :: AWR Report - Find Disk I/O

Mar 11, 2012

In AWR report, in order to find the disk i/o, Should I see the avg read(ms) under Tablespace I/O and Filesystem I/O columns?

View 1 Replies View Related

SQL & PL/SQL :: Query To Find User Who Did Not Login From Past 3 Years?

Nov 2, 2012

I have two table APPLICATION_1 and APPLICATION_2, where user's login date is captured.There will be multiple records for each user.Same user may or may not be present in both tables.

The requirement is to find those users who have not logged-in in the last 3 years.

-- Test Case

DROP TABLE application_1;
DROP TABLE application_2;
CREATE TABLE application_1
(
seq NUMBER ,
user_id VARCHAR2(30),

[code].....

Using the below query I'm able to get the desired output.

SELECT user_id, MAX(login_date) last_login_date FROM (
SELECT user_id, MAX(login_date) login_date FROM application_1 GROUP BY user_id
UNION ALL
SELECT user_id, MAX(login_date) login_date FROM application_2 GROUP BY user_id
) GROUP BY user_id

[code].....

View 10 Replies View Related

Performance Tuning :: How To Find All Indexes Scripts In Particular Schema

Aug 16, 2012

getting all indexes script in particular schema.

View 4 Replies View Related

Performance Tuning :: How To Find Top Query Running On A Table

Feb 19, 2012

How do i find a particular SQL or a set of SQL's which are excuted against a table (user identified table) that is either a very frequently executed query against that table or high impact SQL against that table? I am currently looking through the AWR reports to go through all the queries but i was wondering if there are any dictionary views where we can find this info from?

View 2 Replies View Related

Performance Tuning :: Find The Last User Analyzed Particular Table?

Aug 9, 2012

How can i find the last user analyzed particular table?

I can able to find last analyzed date, but can't able find the which user has done it.

View 2 Replies View Related







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