This content has been marked as final. Show 6 replies
1 person found this helpful
Ora DBA wrote:Usual questions...
RDBMS - 18.104.22.168. I'm running below insert statement from SQL Plus and right below query is its explain plan.
It's running too long, any suggestions please?
1) What are your expectations about performance and how much time does it take at present?
2) Are the stats up-to-date?
3) Why are you using a SQL Profile?
4) Post other details as mentioned in those FAQ threads
If your estimates in EXPLAIN PLAN are anywhere near actual data volume, then the obvious time (& resource) consuming operation that stands out is
It will take a lot of time to do a NL join on 77m records. It could be due to that sql profile.
| 18 | NESTED LOOPS | | 43M| 4164M| | 320K (2)| 01:04:08 | | 19 | TABLE ACCESS FULL | ORDER_RELEASE_STATUS | 77M| 5894M| | 318K (1)| 01:03:38 | |* 20 | INDEX RANGE SCAN | XIE1_SNI_PA_OTM_ARC_DRVR | 1 | 20 | | 1 (0)| 00:00:01 |
Thanks for the reply. I see most tables in the schema have old stats, gathering stats now to see if it helps
Why will gathering stats make any difference if there is a SQL profile in place?1 person found this helpful
Actually the problem is with a number of Jobs running slow in the database. I noticed schema has almost all tables with stale stats. So, I've decided to gather stats for all tables.
As for SQL profile in place, Yes, I believe it wouldn't make a difference as it used the profile. Can I just disable that? or drop it completely?
I'd check why it is there first....
Typically things have been added for a reason, and although it may not be obvious to you why that was...I'd still find that out.
You may be able to fudge the query a little to 'break' it from picking up the profile to test that execution plan/change your category in your session so it is not picked up and check that 'default' plan.
Can I just disable that? or drop it completely?Yes. See [url http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_sqltun.htm]DBMS_SQLTUNE.ALTER_SQL_PROFILE to disable.
Disabling will be better than dropping in case you change your mind about dropping.