Tagged Questions
5
votes
1answer
53 views
Sudden increase in log_file_sync waits
I'm on Oracle 11gR2 with a 2 node RAC system. It's shared fiber storage to an EMC Clariion.
Last friday things went bad..fast. All of the sudden processes that normally ran fine for years became ...
1
vote
0answers
54 views
Performance Hit when upgrading to Oracle 11gr2
I am experiencing the same symptoms as noted in this question which was closed and does not have a satisfactory answer.
Original Question
We've recently upgraded from Oracle 10gR2 (10.2.0.4 ) to ...
0
votes
1answer
48 views
Efficient way to perform approximated search?
I have to perform a join between table_a and table_b, using three fields as key being one of them the date of the event, say, both tables have id1, id2 and evnt_time for eache record.
As it happens ...
0
votes
0answers
36 views
Temporarly disable cache in oracle 11g
I want to disable the caching for my session in oracle 11g to see how long a query takes.
Is this possible or is there a better way for meassuring the time a query takes to run?
0
votes
1answer
109 views
What is the best way to insert a XML input to an oracle table in stored procedure?
I want to write a stored procedure which has a XML input (clob type) and inserts the data to a table. my XML structure is something like this:
<rowset>
<row>
...
3
votes
1answer
259 views
Oracle insert through dblink performance issue
I'm trying to do an insert as select to a table via dblink. The query looks like this:
Insert into MARS_SUPERVISORS_PUB@EDHPUB
select
{...}
from edhper a, edhper b
where ...
4
votes
1answer
64 views
Oracle BLOB store performance
LOBs in Oracle are stored in-row when they are less than 4K in size and out-of-row otherwise.
I'm wondering how do SECUREFILE storage options COMPRESS and ENABLE STORAGE IN ROW work?
I have a lot of ...
0
votes
0answers
45 views
Database too slow after CHARSET Conversion
I have an Oracle database which runs on version 9.0.1 with WE8859P1 charset and i'm trying to migrate it to the version 9.2i which has the WE8MSWIN1252 charset.
The conversion is made without any ...
3
votes
1answer
446 views
What queries is Oracle executing, how frequently, and time-taken?
I have a (Java) web-application, that of course executes many queries (selects, inserts, updates, deletes) to the Oracle Database.
I want to find out the following for last 7 days:
SQLQuery ...
5
votes
1answer
286 views
Oracle aggregate functions slows query down massively
I have a table with ~12M rows of data in it. Here is the table structure:
SYSTEM_ID
BATCH_ID
MEASUREMENT_INDEX,
PARAMETER_ONE,
PARAMETER_TWO
The primary key is made up of the first three columns. I ...
1
vote
1answer
551 views
Oracle 11g: “With Clause” very slow
I have written a query utilizing Oracle's with clause. I have identified one particular with clause in a larger query to be really, really slow. Here are three versions of the same query, they all ...
3
votes
2answers
238 views
Increase Oracle buffer cache gets slower
So we're contemplating increasing our Oracle 11gR2 buffer cache. They said they tried it in the past and things appeared to get worse - so they undid the change. They, however, have no idea why ...
2
votes
0answers
401 views
Oracle high Paging Faults
What are the reasons that lead to terrible high Paging faults.
The following is the topas information and background about the Environment.
OS: AIX 6.1
Softwares: Oracle 11gR2
Paging Space:30720 ...
0
votes
0answers
257 views
Oracle 10g - Sudden change in execution time for specific query
After the DBAs fiddled with some parameters in our Oracle 10g Production Environment, most queries became a little faster. But a single query (a quite complex one) became dramaticaly slower - it went ...
1
vote
2answers
264 views
How to isolate general Oracle 10g issues through SQL
Background:
I've been working on a performance analysis on a system with major performance issues on a Oracle 10g server. Much of the SQL is badly written, or at least not written with performance in ...
2
votes
3answers
89 views
How can I identify and fix bottlenecks in an Oracle11gR2 import
I've recently been tasked with moving our Oracle 11g database from a linux CentOS desktop to a windows 2008 server. Given I've not done much behind the scenes stuff with oracle before I'm a bit out of ...
4
votes
2answers
455 views
Hibernate Query Running Slow
I am currently facing a problem with a slow running Hibernate query that is being kicked off from a Java program. It is making a call to Oracle 11g at the back end.
This query is taking anything from ...
3
votes
3answers
264 views
Why does the query cost change so much and how to prevent it
The query causing me the issue is too big, but the piece that seems to be the core is quite simple, I will try to go with that:
The query has an structure like:
SELECT
... a lot of stuff ...
...
2
votes
1answer
157 views
Oracle sequence generation performance during persists of large data sets
In the context of an Oracle sequence, when persisting > 200 million records, is there a performance hit on the database if the sequence cache is the default value of 10000? That is to say does ...
-2
votes
1answer
761 views
What are performance benefits when using number type instead of using char/varchar?
From various posts, I have summarized about using number type instead of char/varchar for PK:
If you choose char/varchar type for PK and your values are short (just some alpha numberic codes) then it ...
2
votes
1answer
535 views
Why Is oracle ignoring my index?
This query is not too complex:
select
trunc(created_date, 'MONTH') as created_date,
al.op_name,
al.region,
al.alarm_type,
COUNT(1) as total_new,
SUM(
(SELECT
...
3
votes
2answers
318 views
Help to understand explain plan in Oracle
I am running a query in some big tables, and although it runs fine even tough is a lot of data, I'd like to understand what part of it weighs on the execution. Unfortunately I am not too good with ...
14
votes
4answers
1k views
Why not use a table instead of a materialized view?
I'm new to Oracle databases. If I have understood correctly, materialized view is a view which result set is saved as a physical table in the database and this view/table is refreshed bases on some ...
0
votes
0answers
101 views
Reusing a query with multiple queries [closed]
Here is my query
select *
from
(select a.*, rownum rnum
from
(select id, data
from t
order by id) a
where rownum <= HIGHER
)
where rnum >= LOWER;
Say i want to use the innermost ...
1
vote
2answers
447 views
Querying a database efficiently for a huge chunk of data
Here is my query
SELECT *
FROM
(
SELECT a.*, rownum rnum
FROM
(
SELECT id, data
FROM t
ORDER BY id
) a
...
0
votes
0answers
39 views
Resource to learn writing most efficient and highly optimized sql queries [duplicate]
Possible Duplicate:
Learning to Optimize SQL Queries and Understand Execution Plans - Resources?
Can some one point me to a great book/great books or some really solid reference/references ...
1
vote
1answer
429 views
Oracle listagg forces SORT (GROUP BY) execution plan
I have the following query
SELECT /*+ USE_HASH_AGGREGATION */ id
--, count(id)
, listagg(type, ', ') within group (order by null) types
FROM test
group by id
Both type and id are short strings.
...
2
votes
0answers
126 views
How to Connect R to Oracle?
I need to connect R to oracle and I have been unsuccessful so far. I downloaded two packages: RODBC & RODM.
This is the statement that I've been using: DB <- ...
3
votes
2answers
257 views
What exactly is “pinning” in relation to indexes?
When one talks about "pinning" in indexes, what exactly is this? Is there some other word/term I can search for, as google has not provided any solutions.
It is part of a test question, where ...
5
votes
1answer
631 views
Oracle's Merge v. Select->Update/Insert
What is the faster statement? A MERGE or Select->Update/Insert depending on if it returns a value?
I'm trying to optimize a database, but I haven't found any speed tests comparing the two.
I found ...
3
votes
1answer
298 views
Oracle explain for cascading delete is wrong
My deletes in Oracle 11 are very slow. The tables are linked by foreign keys and every foreign key constraint has a delete cascade set on it.
If I hit the explain button on a statement like this ...
2
votes
1answer
135 views
Oracle ash report
Recently, i have been handed over with an ash report from DBA.
To me this report is like french. I have no idea what it is about and what all is written in this report. Can someone please guide me in ...
2
votes
2answers
1k views
Oracle aggregate function performance
Can Oracle be smart about aggregate functions, such as MIN(), MAX(), and (AVG)? My testing shows that it seems to be surprisingly stupid.
I have the following query:
SELECT COUNT(userId), AVG(age), ...
3
votes
2answers
1k views
oracle distinct doing sort
I'm getting poor performance from DISTINCT. The explain plan indicates that it is doing SORT (GROUP BY) which doesn't sound right. I would expect some kind of HASH aggregation to produce much better ...
1
vote
1answer
165 views
SQL from/where or join?
Hi I was wondering performance wise what would be faster a join or a from and where query.
I am trying merge and update query but performance is terrible?
2
votes
2answers
380 views
How to compare performances of two databases
There are two database servers and I want to compare their performances.
Both have Windows operating system and and Oracle (10.2.0.4 and 11.0.2) database.
Both are OLAP databases with 15-20 users ...
6
votes
3answers
1k views
Why performance degradation with query hint FIRST_ROWS(1000)? [closed]
I have a query with user supplied arguments that can produce a very high amount of rows depending on the user's input.
In case the user supplies very unselective arguments, the query takes a lot of ...
1
vote
2answers
475 views
Oracle 11. Updating BLOB field. Db file sequential read inappropriately slow?
I've and Oracle (11 Enterprise) schema with a table
CREATE TABLE USER.WSP_BUNDLE (
NODE_ID RAW(16) NOT NULL,
BUNDLE_DATA BLOB NOT NULL
);
CREATE UNIQUE INDEX USER.WSP_BUNDLE_IDX ...
2
votes
2answers
4k views
Improve BLOB Writing Performance in Oracle 11g
I have an application that writes a lot of rows into a table that contains a blob column. The average size of the blob is 3k bytes.
Is there any buffer that i can resize to improve inserts into this ...
6
votes
5answers
2k views
Tools to monitor Oracle Database Performance on Standard Edition
Here is what I am looking for:
Works on Standard Edition i.e. No Diagnostic Pack.
Provides both current and historical performance data.
Good visuals for easy spotting of trends and presentation ...
2
votes
2answers
201 views
Improving the performance of a long query
I have a query that i have let run for a few hours. I am forced to kill the query because it is taking up 60% of the CPU. Is there anything i can do to improve its performance?
select ...
9
votes
2answers
1k views
Updating 700 million rows to same value
I've got a data warehouse (oracle) where I need to set a column to the same value for all 700 million rows.
I don't have admin access, or access to an admin, so this needs to be accomplished with ...
11
votes
3answers
749 views
Is there any point in undersizing VARCHAR columns?
Googling around there seems to be mixed reports whether the size of a VARCHAR2 column in Oracle impacts performance or not.
I would like to give the question of VARCHAR size a little twist and hope ...
8
votes
3answers
2k views
How do indices impact query performance?
Obviously keeping several different indices has a negative impact on insert and delete performance. How about query performance: Does it make sense at all keeping too many indices on a table? Will the ...
12
votes
2answers
899 views
Index vs. partition
Why is it not possible to achieve performance improvements with indexes only, so that other techniques like table partitioning becomes necessary? The question relates only to performance, of course ...
7
votes
3answers
2k views
Multiple Oracle Instances — is this a good practice?
One of my clients has deployed our product's db into a Solaris machine which already has 3 instances of Oracle. So, right now there are 4 instances of Oracle running on the same machine. And now we're ...
1
vote
1answer
5k views
Where all can the /*+ PARALLEL */ hint be used?
It works with SELECT and UPDATE statements.
Are there some more statements with which it works?
I was able to use it with create, but am not sure if the hint was actually considered.
SQL> create ...
15
votes
4answers
1k views
SSDs with Oracle
We've been looking into using SSDs with Oracle to speed up our test migration runs. It currently takes 12-18 hours to complete a migration run, depnding on the volume of data (we're obviously doing ...
