All Questions
13
questions
2
votes
1
answer
45
views
Statistics rapidly changing within transaction - fixing execution plan
A problem I'm facing (Oracle 11g):
I create a table, let's call it table_xyz, with index (not unique, no primary key).
I create package with procedure that will insert let's say 10 millions ofrecords ...
- 954
1
vote
0
answers
2k
views
Is there a way replace index fast full scan with index range scan in Oracle execution plan?
Here is a table A, and the primary keys of this table A are A1_ID and A2_ID.
And to avoid Table Full Scan of PARENT_A1_ID, an index was created.
/* The status of the index is as follows. */
A_PK_IDX ...
- 47
0
votes
1
answer
471
views
SQL insert and update high elapsed time happening only at certain time of the day
At about 7 PM everyday, I notice that my application runs slower than usual.
Upon checking AWR report, I noticed that 2 frequently used SQL statements are having high elapsed time (about 1 second per ...
- 85
0
votes
0
answers
241
views
How to force the use of index in a specific relation on Oracle sql sentence
First, these queries are executed on Oracle 11g
I am trying to force the Oracle to execute the index on the condition manh_dist(tjoin1.fv,tjoin2.fv)<=8000 of the query bellow I am using hints to ...
- 443
0
votes
1
answer
3k
views
choose one partition but optimizer use partition list all
I have query like this :
select * from P2P_TST2.kh4rqolog t where t.kh4ldat472 between to_date('20170121120001', 'YYYYMMDDHH24MISS') and
to_date('20170121130101', 'YYYYMMDDHH24MISS')
and ...
- 117
0
votes
0
answers
778
views
Oracle SQL queries - explain plan big estimated/actual row differences
I have a problem which I'm finding hard to understand. I have two queries that are running slow. So I started to investigate the performance issues. All the tables in both queries have had there stats ...
- 535
2
votes
4
answers
3k
views
Oracle SQL query running slow, full table scan on primary key, why?
I have a problem with a piece of code, I can't understand why the below query is doing a full table scan on the works table when wrk.cre_surr_id is the primary key. The stats on both tables are both ...
- 535
1
vote
1
answer
302
views
Performance Tuning an Existing Oracle Query
I'm a newbie to Oracle and have been given a task of improving some existing SQL queries currently running in a web based application. I've extracted the following query and ran it in SQL Developer to ...
- 1,129
1
vote
2
answers
3k
views
Oracle ORDERED hint cost vs speed
So, a few weeks ago, I asked about Oracle execution plan cost vs speed in relation to the FIRST_ROWS(n) hint. I've run into a similar issue, but this time around the ORDERED hint. When I use the hint,...
- 4,320
1
vote
1
answer
247
views
Oracle - why is this paging code not using the stopkey optimization
I've run across a strange situation regarding a paginated query and the stopkey optimization on 11G (I've been able to reproduce on full 11G as well as XE installs). I'm using the alexa top 1M sites ...
- 1,985
13
votes
2
answers
749
views
Large amount of projected I/O with Oracle, even if only a single record is fetched
I often encounter the following situation in my Oracle execution plans:
Operation | Object | Order | Rows | Bytes | Projection
----------------------------+---------+-------+------+...
- 218k
8
votes
1
answer
6k
views
DBMS_XPLAN.DISPLAY_CURSOR vs Explain Plan if not using gather_plan_statistics hint
Just requesting some clarification on the difference between the 2. From what I understand, EXPLAIN PLAN gives you the theoretical execution plan while DBMS_XPLAN.DISPLAY_CURSOR gives you the actual ...
- 5,249
3
votes
1
answer
182
views
Oracle plan for comparing 'b' vs. 'B' 998x as slow (10g or 11g)
Have a query to set records as bad by comparing with another table. To save time, I exclude records that have already been marked as 'bad'.
I wrote a query, but accidentally checked for != 'b' ...
- 6,323