Printable View
The sql in our database is log and complicated. There were a few report sql statements getting so much slower in these a few weeks. It only took about 5 minutes before, but now it needs 2 hours.
I checked explain plan, there are a lot full table scan, even though there are indexes on the tables for those columns.
What could be the cause for the sql being so slow? Why does the optimizer not picking up the indexes?
Thanks!
You must put more details about the problem SQL statements. The reasons can be many.
Perhaps it is still using too many indexes.
Are you using CBO ?
Are your stats for the tables and indexes up to date ?
1. Here is the sql that is having problems.
select a12.DIAGNOSTIC_TROUBLE_CODE_STRING
DIAGNOSTIC_TROUBLE_CODE_STRING,
a12.DTC_DESC DTC_DESCRIPTION,
count(a11.DIAGNOSTIC_TROUBLE_CODE_KEY) WJXBFS1,
count(distinct a11.VEHICLE_KEY) WJXBFS2
from QIS2OWNER.FACT_DTC_INCIDENCE a11,
QIS2OWNER.DIAGNOSTIC_TROUBLE_CODE a12,
(select pa1.DIAGNOSTIC_TROUBLE_CODE_STRING
DIAGNOSTIC_TROUBLE_CODE_STRING
from (select a15.DIAGNOSTIC_TROUBLE_CODE_STRING
DIAGNOSTIC_TROUBLE_CODE_STRING,
rank () over( order by
count(a11.DIAGNOSTIC_TROUBLE_CODE_KEY) desc nulls last) WJXBFS1
from QIS2OWNER.FACT_DTC_INCIDENCE a11,
QIS2OWNER.CLAIM_DTC_MAP a12,
QIS2OWNER.FACT_CLAIM a13,
QIS2OWNER.DATE_DIMENSION a14,
QIS2OWNER.DIAGNOSTIC_TROUBLE_CODE a15,
QIS2OWNER.GMPT_RPOS a16,
QIS2OWNER.VU_ENGINE_RPO a17,
QIS2OWNER.LABOR_CODE a18
where a11.DTC_INCIDENCE_KEY = a12.DTC_INCIDENCE_KEY and
a12.CLAIM_KEY = a13.CLAIM_KEY and
a12.MODEL_YEAR = a13.MODEL_YEAR and
a13.SERVICE_CASE_PROCESS_DATE_KEY = a14.DATE_KEY and
a11.DIAGNOSTIC_TROUBLE_CODE_KEY =
a15.DIAGNOSTIC_TROUBLE_CODE_KEY and
a11.MODEL_YEAR = a16.MODEL_YEAR and
a11.VEHICLE_KEY = a16.VEHICLE_KEY and
a16.ENGINE_RPO_KEY = a17.RPO_KEY and
a13.LABOR_CODE_KEY = a18.LABOR_CODE_KEY
and (a11.MODEL_YEAR in (2005)
and a17.ENGINE_RPO_CODE in ('L52')
and a18.SECTOR in ('ENGINE')
and a11.SCAN_SOURCE_KEY < 9
and a14.START_DATE_OF_THE_WEEK >= To_Date('18-Jul-05'))
group by a15.DIAGNOSTIC_TROUBLE_CODE_STRING
) pa1
where (pa1.WJXBFS1 <= 10.0)
) pa2,
QIS2OWNER.CLAIM_DTC_MAP a13,
QIS2OWNER.FACT_CLAIM a14,
QIS2OWNER.DATE_DIMENSION a15,
QIS2OWNER.GMPT_RPOS a16,
QIS2OWNER.VU_ENGINE_RPO a17,
QIS2OWNER.LABOR_CODE a18
where a11.DIAGNOSTIC_TROUBLE_CODE_KEY = a12.DIAGNOSTIC_TROUBLE_CODE_KEY and
a12.DIAGNOSTIC_TROUBLE_CODE_STRING =
pa2.DIAGNOSTIC_TROUBLE_CODE_STRING and
a11.DTC_INCIDENCE_KEY = a13.DTC_INCIDENCE_KEY and
a13.CLAIM_KEY = a14.CLAIM_KEY and
a13.MODEL_YEAR = a14.MODEL_YEAR and
a14.SERVICE_CASE_PROCESS_DATE_KEY = a15.DATE_KEY and
a11.MODEL_YEAR = a16.MODEL_YEAR and
a11.VEHICLE_KEY = a16.VEHICLE_KEY and
a16.ENGINE_RPO_KEY = a17.RPO_KEY and
a14.LABOR_CODE_KEY = a18.LABOR_CODE_KEY
and (a11.MODEL_YEAR in (2005)
and a17.ENGINE_RPO_CODE in ('L52')
and a18.SECTOR in ('ENGINE')
and a11.SCAN_SOURCE_KEY < 9
and a15.START_DATE_OF_THE_WEEK >= To_Date('18-Jul-05'))
group by a12.DIAGNOSTIC_TROUBLE_CODE_STRING,
a12.DTC_DESC
2. Does too many indexes stop optimizer to use index?
3. The optimizer_mode = CHOOSE.
As usual, I analyze all tables every week. I newly created some more indexes on one of the major tables with compute statistics.
Thanks for the help!
Do you analyze columns (gathering histograms) as well as the tables and indexes?
Try setting OPTIMIZER_INDEX_COST_ADJ to a low value (10). ;)
Alter session set optimizer_index_cost_adj = 1
and then check the explain plan for the sql. It did pick up the indexes. But the problem is the query is still running very slow! What could have happened to the database? This query was running more than ten times faster a couple of weeks ago!
In retrospect, it would have been a good idea to store execution plans for queries while they were running well, for comparison when things go badly. Not too late to start n that now though.
You can have a shot at event 10046 tracing, to see what is delaying the result, but it seems as if something has changed and that prompted a change to the execution plan.
Can 'event 10046 tracing' be set at session level? That all I can do!
What information can I get from this event tracing?
As I mentioned before, I did created more indexes on one of the fact tables about three weeks ago.
Thanks!
yes it can be set at a session level
you can see the waits in it then
maybe ur tables have grown bigger and fragmented. You may consider reorganizing it. Consider partitioning and run parallel query on all partitions
i) Fragmented how?Quote:
Originally Posted by yxez
ii) Why partition?
iii) Parallelism would speed the result, but use more resources ... seems rather like a brute force method if all else fails.
1.) frequent deletes makes tables fragmented.
2.) large tables are good candidates for partitioning.
3.) sacrifice resources just to get valuable data.
4.) :) peace :)
i) Ah, you mean that there are fewer rows in a table with the same high water mark. Hmmm, I'm having trouble thinking of how this might cause a longer running query though...
ii) This will have to be a fundamental area of disagreement .. maybe the reverse is true, that small tables are less likely to be candidates for partitioning, but partitioning is powerful ju-ju which will often cause a slow down in queries if not properly applied.
iii) Again, powerful stuff. There are many "if's" to work out before commiting to such a plan.
iv) Of course! :D
The fact table in those queries are partitioned. As I mentioned before, there are two changes here. 1. I created some bitmap indexes on some foreign key columns. I was planning to enable star transformation. 2. We loaded some historical data over the last a couple of month to the fact table.
How do u analyze your tables and indexes. Oracle recommends dbms_stats.
Thanks,
Chucks
here is from v$session_wait:
EVENT WAIT_TIME SECONDS_IN_WAIT
------------------------------ ---------- ---------------
pmon timer 0 8197
rdbms ipc message 0 18
rdbms ipc message 0 3
rdbms ipc message 0 15
rdbms ipc message 0 0
rdbms ipc message 0 1
smon timer 0 62
rdbms ipc message 0 214
rdbms ipc message 0 4943
rdbms ipc message 0 30
rdbms ipc message 0 4566
EVENT WAIT_TIME SECONDS_IN_WAIT
------------------------------ ---------- ---------------
SQL*Net message from client 0 87
library cache lock 0 8487
SQL*Net message from client 0 1673
library cache lock 0 2256
library cache lock 0 9692
library cache lock 0 8971
SQL*Net message from client 0 3
SQL*Net message from client 0 945
SQL*Net message from client 0 6000
db file sequential read 1 66308
library cache lock 0 8861
EVENT WAIT_TIME SECONDS_IN_WAIT
------------------------------ ---------- ---------------
SQL*Net message from client 0 1673
library cache lock 0 1896
library cache lock 0 8911
library cache lock 0 7020
library cache lock 0 14339
db file sequential read -1 73926
library cache lock 0 6263
library cache lock 0 6426
db file scattered read 0 0
SQL*Net message from client 0 7646
library cache lock 0 7911
EVENT WAIT_TIME SECONDS_IN_WAIT
------------------------------ ---------- ---------------
library cache lock 0 2562
library cache lock 0 7919
library cache lock 0 3
library cache lock 0 10958
library cache lock 0 6835
SQL*Net message from client 0 4322
library cache lock 0 8971
library cache lock 0 6852
library cache lock 0 7467
SQL*Net message from client 0 746
SQL*Net message from client 0 1703
EVENT WAIT_TIME SECONDS_IN_WAIT
------------------------------ ---------- ---------------
SQL*Net message to client -1 0
SQL*Net message from client 0 10001
SQL*Net message from client 0 6610
library cache lock 0 7859
SQL*Net message from client 0 1651
library cache lock 0 2622
SQL*Net message from client 0 2658
library cache lock 0 6806
SQL*Net message from client 0 1
library cache lock 0 1056
library cache lock 0 7737
55 rows selected.
Are the bitmap indexes you have recently createed now being used? If so, what are the cardinalities of the columns you have created bitmap indexes on?
Well, you'll definitely be wanting to enable star transformations. Try that and see what happens.Quote:
Originally Posted by lgaorcl
What column are you partitioning on, and by what method? If it is by SERVICE_CASE_PROCESS_DATE_KEY then I doubt that you're getting the partition pruning that you deserve.
Is this query generated by some tool or other? I'm intrigued by the complex in-line view you have in there.
The table is partitioned range-hash at model_year and vehicle_key(subparition).
I am going to implement star transformation after this performance issue get resolved. I tried enable star transformation at session level, and then check the explain plan. The plan was much better. But now, because the entire database is doing very bad, so it didn't make too much difference in response time.
Did you set optimizer_mode = all_rows while you enabled star transformation at your database? What would be the best for optimizer_mode for a database warehouse environment? Currently, our optimizer_mode = choose.
What can I do with these wait?
rdbms ipc message
library cache lock
db file sequential read
Thanks!
"all rows" would be fine ... it's what you'll be getting with that kind of query anyway.
rdbms ipc message: background wait event, not a problem
library cache lock: what version are you on?
db file sequential read: http://metalink.oracle.com/metalink/...T&p_id=34559.1
DaveQuote:
Originally Posted by slimdave
When you said store execution plans for queries whilst they are running well, are you refering to the use of PLAN_TABLE for all queries against the database for the application? How on earth would you go about collecting that? Sounds like a huge project!! If you know some sneaky trick to make said collection easier, i'd be very happy to hear it :)
...I assume, you aren't talking about using stored outlines...
I shouldn't think it necessary to do all of them, just a good representative sample. You're hopefully not using bind variables so the same query with different values could yield different execution plans anyway.
commonly executed reports would be a good place to start, along with the longer running reports. You might be able to set tracing on at the instance level and capture most of them, but then if you have your front-end metadata in the same database then you'll get a load that you're not so interested in as well. Watch your disk space if you go down that route.
Our database versioin is 9.2.0.6.
What could we do with 'library cache lock'?
It seems that's the reason for our database slow down. What's the reason for 'library chache lock?
Thanks!
Quote:
Originally Posted by lgaorcl
Taken from the manuals:
Library Cache Locks
When a database object, such as a table, view, procedure, package, or index, is referenced during the parsing of a SQL, DML, DDL, PL/SQL, or Java statement, the process that parses the statement acquires a library cache lock. In Oracle9i, the lock is held only until the parse or compilation completes, or, for the duration of the parse call.
So my guess is you aren't using bind variables, so you're spending alot more time hard parsing than you should be.
Use a 10046 trace to get the wait events, not v$session_wait.
I really don't think that you want bind variables here. You want the optimizer to be sensitive to column histograms in the fact table (although the heavy use here of synthetic keys may be making that impossible). (Yes, I know. Ironic, huh?)