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


ADVERTISEMENT

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 :: 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 View Related

Performance Tuning :: Oracle10g Standard Edition On Linux Machine

Jan 16, 2012

In my production ENV there is two node RAC database is running , and on both machine RAM size is (35GB on node1,41GB on Node2) and other configuration is like below.

SGA SGA_TARGET DB_BLOCK_SIZE DB_MULTIBLOCK_READ_COUNT
NODE1 16G 14G 8K 16K
NODE2 16G 14G 8K 16K

Our Database size is 700GB approx.

The above SGA configuration is ok or need to be increase the size of SGA , our HIT RATIO is almost 99%.

View 5 Replies View Related

Performance Tuning :: Sub Queries With Function Call?

May 24, 2011

I am stuck with a query which is taking a lot of time to execute. Below is the pseudo code of the same:

SELECT TAB_ALIAS1.COL1,TAB_ALIAS1.COL2,TAB_ALIAS1.COL3
FROM TABLE1 TAB_ALIAS1
WHERE TAB_ALIAS1.COL4 = <INPUT PARAMETER1>
AND TRUNC(TAB_ALIAS1.ELAP_TIME) =
(
SELECT MAX(ELAP_TIME)

[code]....

View 6 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

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 :: 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 :: Export SQL Plan From Test Database To Prod Database?

Jul 16, 2013

An SQL query is taking a lot of time than usual and not completing even left after hours! The query joins a table with a quite complex view.

The same query in a test database completes in less than 2 mins.

I would like to export the sql plan from test database to prod database.

how to export/import in 10.2.0.4 version for a particular sql statement's execution plan.

View 2 Replies View Related

Performance Tuning :: SSL On Database Connections?

Oct 10, 2012

insight into the overheads for mutally authenticated SSL for database connections? This is over a fast local network, to a RAC cluster, with DB firewall in front. There's always a large element of "it depends"

Information I'm interested in are things like latency for initial session setup and subsequent data transfer. Also the increase in network packet size, and the increase in CPU cost for the database server. I guess there is some implications for session memory usage as well.

View 4 Replies View Related

Performance Tuning :: Big Database Migration?

Jul 4, 2012

I have one big database which I need to migrate to Oracle, because it rocks with big databases, instead of other databases and when I was made transfer software and all works great except one more think. During this process I found that Oracle normally fill log & undo table, and my question is how to migrate (or can I migrate) database to oracle without filling undo database (deactivate this process) and after that to put database to work normally, because I just need to transfer data as is and from that point Oracle goes on...

View 4 Replies View Related

Performance Tuning :: Database Crash Due To CPU Starvation?

Sep 1, 2013

i am using 11.2.0.3 version of oracle. We have recently migrated to 11g, after 1 month of smooth and comparatively better performance, we are suddenly facing performance issues with our database and it got crashed twice within 5 days. even we didnt push any new code to our database in recent past, atleast after the 11G migration. And after getting feedback from the ORACLE corporation guys , they pointed out about the default database stats gathering job, which was eating most of the CPU, because of the default degree mentioned So it was running in 160 parallel threads causing resource starvation.so we reduce the degree of the stats gathering job to 8 .

But the database crashed again two days back, and rebooted within 3 mins to back to normal, even after this default degree changed to 8. This is happening due to any specific application related sql or anything else.

View 9 Replies View Related

Performance Tuning :: Import And Export Of Database

Oct 14, 2010

I am doing import and export of database.Before loading data i drop all the tables and import.Is there any issue if we do drop tables and import data frequently.

View 2 Replies View Related

Performance Tuning :: Database Time In AWR Report

Jul 9, 2012

I understand that when data is read from the disk, I/O is done..And When computations are done then CPU is used..Then where the following equation fits?

DB Time = sum of database CPU time + waits

Is I/O considered as a part of CPU time?

Does this equation changes with SAN, OS caching?

View 3 Replies View Related

Performance Tuning :: Database Is Slow On Insert

Mar 23, 2012

we are using oracle 9i on AIX Server. When Customer were accessing the database, accidentally power was shut down. we restarted the Server,and Oracle database. all resumed successfully.

However while doing "Payments by the customer" it takes a lot of time to insert even a single payment record on database.The database is Live and our customer are very much frustrated,

View 1 Replies View Related

Performance Tuning :: Restart Database After Increase Db_cache_size?

Aug 23, 2012

SQL> show parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 1152M
sga_target big integer 0

[code]....

in scenario above, the database do not using ASMM, and spfile If I wan to increase db_cache_size parameter, do i need to rebounce instance?

View 6 Replies View Related

Performance Tuning :: Same Query With Different Explain Plan In Two Database?

Aug 17, 2012

I am facing a weird situation wherein the explain plan of same sql in SIT and PROD is different.In fact the explain plan is very costly in Prod.Also the DB version of both SIT and PROD is same.

Below is the sql and corresponding explain plan in Prod and SIT respectively.

Query:
SELECT seq,CCN,ProcessorPart,root_item,comp_path,Item,comp_item,comp_item_type,
lag(comp_item_type,1,'PART') over(PARTITION BY seq ORDER BY lvl)Nxt_comp_item_type,lvl,bom_qty,
ROUND(CASE min(abs(bom_qty)) OVER (PARTITION BY seq ORDER BY lvl)
WHEN 0 THEN 0 ELSE 1 END * EXP (SUM (LN (nullif(abs(bom_qty),0))) OVER (PARTITION BY seq ORDER BY lvl))) Ulti_qty,
'AMER'

[code]...

The tables referred in above query is small tables containing arnd 10k records.The above tables are partitioned on Region and not indexed.

Explain Plan in Prod: COST CARDINALITY BYTES

SELECT STATEMENT, GOAL = ALL_ROWS165173613539322883634804
SORT UNIQUE236360
UNION-ALL
PARTITION LIST SINGLE117240

[code]...

Explain Plan in SIT: COST CARDINALITY BYTES

SELECT STATEMENT, GOAL = ALL_ROWS3211689
SORT UNIQUE347240
UNION-ALL
PARTITION LIST SINGLE172120

[code]...

I am not able to attribute why there is a huge change in Cost between SIT and Prod.Apparently the Job is going for 3-5 hours which used to get completed within 20mins in SIT.

View 5 Replies View Related

Performance Tuning :: Handling NULL Values In The Database?

Feb 6, 2012

We have database with multiple fields containing NULL values and in many queries we have NVL function which in turn is suppressing the index usage when in fact it is really essential (selectying very few rows from massive data) instead of creating lot of Function based indexes (NVL) or composite indexes with (nullable_column, constant) I am thinking of settting a default value for most of the fields In that regard I have some queries :

Which approach is better - setting default value for the fields or updating the fields with default value and modyfing inserts to take care of future data? Though altering table and modifing column to set default value looks better considering it will take care of data inserted in the future, it will invalidate the subroutines.I understand in 10g both statement will generate lot of undo (though in 11g, I heard things changed for setting default value of a column) How to take care of all the queries which are using the criteria 'where column1 IS NULL' or 'where column1 IS NOT NULL'. It will be really difficult task to manually change each and every occurrence of such condition even using user_source.

Finally for numeric values say for ID field which starts from 1 onwards 2,3,4 etc, we can set 0 as sensible default so that the performance is not affected.

Is there such precaution for varchar2 field purely from performance point of view?

View 3 Replies View Related

Performance Tuning :: Memory Parameters In Oracle Database 9.2.0.8

Nov 18, 2012

I have a question regarding memory parameters in oracle database 9.2.0.8, especially sga_max_size and db_cache_size. Database server has 32G of ram. Oracle parameter on server shmmax is set to 16G. Is reasonable to set sga_max_size to the same value, and db_cache_size to 80% of that size?

View 2 Replies View Related

Performance Tuning :: How To Check Table Has Changed 10% In Database

Dec 13, 2011

By default the DBMS_STATS package runs once every 24 hours to collect statistics for database objects and Oracle collects new statistics when enough of the data (about 10%) has changed.

My question here is how to check the table has changed 10% in database?

View 23 Replies View Related

Performance Tuning :: Tools For Monitoring Load Of Database?

Dec 3, 2010

which tools are available for monitoring load of the database?

View 4 Replies View Related

Performance Tuning :: No Significant Database Activity To Run ADDM

Apr 11, 2012

As there was a database performance, we started to analyse the issue by running ADDM using below.

@?/rdbms/admin/addmrpt.sql.

The result of running ADD is below.

DETAILED ADDM REPORT FOR TASK 'TASK_64718' WITH ID 64718
Analysis Period: 10-APR-2012 from 06:00:15 to 07:00:22
Database ID/Instance: 324353546/1
Database/Instance Names: JACK/jack1
Host Name: fdsfggwe001
[code].....

There was no significant database activity to run the ADDM.The database's maintenance windows were active during 100% of the analysis period.The analysis of I/O performance is based on the default assumption that the average read time for one database block is 10000 micro-seconds. An explanation of the terminology used in this report is available when you run the report with the 'ALL' level of detail.

Current parameters set in database are below

statistics_level ---- string
timed_statistics --- boolean

1.Do i need to set the initialization parameter to some other value?

2.should i run the below command before executing the ADD report ? exec dbms_workload_repository.create_snapshot();

View 10 Replies View Related

Performance Tuning :: Audit Analyze Table On 9i Database?

Apr 19, 2011

I support to get a handle on statistics collectionn in their data warehouses. It seems developers have created several ANALYZE TABLE jobs but the code for these is not stored as PLSQL in the database and thus it is problematic for statistics collection. Even if we collect stats that way we want, these jobs kick in and overlay the statistics we collect every day.

Is there a way to AUDIT ANALYZE TABLE? I can't find it anywhere.

Is there a way to globally turn of ANALYZE TABLE in a 9i database?

View 2 Replies View Related

Performance Tuning :: Database Links - Display Consolidated Data?

Oct 14, 2013

A website requires to display consolidated data from databases located in different geographical regions (India, London and New York). The application server for the website is hosted only in one location India. What are the techniques that can be used for faster retrieval of data from all 3 databases?

Note: There is no need of real time data retrieval from different regions; however the user should able to view the updated data at predefined intervals.

View 9 Replies View Related

Performance Tuning :: Can Have Multiple LGWR Processes For Single Database

Jul 2, 2010

its possible to have multiple LGWR processes for a single database.If its possible how does the multiple processes write from redo log buffer to online redo log file.

View 3 Replies View Related

Performance Tuning :: Oracle Database 10g Enterprise Edition 10.2.0.4.0 - 64bi

Sep 2, 2011

I am querying v$sga and getting variable size : 211337216 bytes.when querying v$sgastat then getting

java Pool : 16777216
Large Pool : 41943040
Shared pool : 398560392

But as per my knowledge following condition should satisfy,but not getting

[code]

Variable sga = java pool + large pool + shared pool
select pool,name,sum(bytes)
from v$sgastat
where pool in ('shared pool','java pool','large pool')
group by pool,name;

Here variable size using v$sga : 211337216 bytes

and java pool + large pool + shared pool : 211302536 bytes.

[/code]
but it should match?

View 5 Replies View Related

Performance Tuning :: Tablespace With Different Block Size Inside Same Database?

Nov 25, 2011

All the analysis till now on our system proves that our system is clearly I/O bound and db sequential read is the biggest culprit.

We have even identified the index which is being affected by sequential read. I am thinking of creating a new tablespace with 32K blocksize (currently all table spaces are 8k) and migrate this index to the new space. That way, Oracle will have to do less number of reads to get the required data.

But is there anything wrong in having just one tablespace with a differnt block size? Or is there anything that I have to be watchful about while doing it?

View 14 Replies View Related

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

Performance Tuning :: Oracle Server - Analyze Table In Main Database

Jul 13, 2010

I have a procedure which mainly run queries on a Table which has nearly 9.5 million recodes. This procedures takes nearly 15 min to complete execution on our main database. I exported and imported the schema to our backup database and the same procedure just took 3 seconds to complete.

I tried to analyze the table in our main database and tried to execute the procedure again but did not show any improvements. ANALYZE TABLE DN_ACTIONS COMPUTE STATISTICS;

I am not sure computing the statistics for all the tables in the schema will work. I also checked there is enough disk space where oracle data files are stored. I am also turning on the sql trace to see what sql statements in the procedure is taking longer time.

View 8 Replies View Related

Performance Tuning :: Tune Data Coming From Legacy System Into Oracle Database

May 22, 2012

Customer is sending data from legacy system (Source) with the web service which in turn calls a package lying on Oracle server (Target). Now this package is simply inserting data passed by legacy system into master staging table in Oracle database. When they started this process in Sept 2011 then 4 lack records were inserted into staging table. In Oct 11 it was 0 records Nov 11 it was 2 lack records, Dec 11 it was 1 lack records, in Jan 12 it was 1 lac records, Feb 12 73k records, Mar 12 0 records, Apr 12 52k records.

As we see that number of records inserted in the table got reduced with time.. what should be the starting point here since web service is calling that package on the fly, how can i enable trace for that package? I cannot replicate this is Dev as this process is only working in PROD.

View 6 Replies View Related







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