No question at this time
DBA Top 10
1 M. Cadot 17000
2 A. Kavsek 14300
3 B. Vroman 10300
4 M. Hidayathullah ... 8400
5 T. Boles 4900
6 P. Wisse 4400
7 J. Schnackenberg 3500
8 K. Van Nieuwenhov... 2100
9 A. Hudspith 800
9 T. P 800
About
DBA-Village
Download PLATO
The free tool for auditing and tuning your database
Version 55 now available
Sep 02, 2016
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48097
Total active users1702
Act. users last 24h1
Act. users last hour0
Registered user hits last week237
Registered user hits last month1339
Go up

execution plan
Next thread: full data pump import
Prev thread: oem monitoring

Message Score Author Date
Hi Guys I have an sql statement that when I run...... Tso P Feb 28, 2017, 11:30
Hi Guys To narrow my question I have tested an ...... Tso P Mar 01, 2017, 13:54
I would look at the relative cost numbers. Dev us...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Jim Berg Mar 01, 2017, 14:28
Thanks Jim What might be the problem here... ...... Tso P Mar 01, 2017, 15:01
Hi Tso, on DEV your query gets the result from ...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Ales Kavsek Mar 01, 2017, 20:26
Hi Tso, I have come across this behavior... sev...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts raghavendra rao yella Mar 01, 2017, 22:19
Thanks I have exported the table from DEV then...... Tso P Mar 02, 2017, 11:39
Hi, > Could the db_file_multiblock_read_count p...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Ales Kavsek Mar 02, 2017, 12:37
Dear, check below mos on db_file_mutiblock_read...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Mirza Hidayathullah Baig Mar 02, 2017, 13:25
Thanks My apologies... I actually mixed thi...... Tso P Mar 02, 2017, 13:49
Hi, > Should I export: Nope. As I said, don...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Ales Kavsek Mar 02, 2017, 14:24
Thanks Ales Below are the QA workload system st...... Tso P Mar 02, 2017, 22:52
Hi, sorry, I'm busy installing 12c R2....so I'l...... Score: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 Pts Ales Kavsek Mar 03, 2017, 19:17
Thanks I have followed the Tom's link... I ...... Tso P Mar 06, 2017, 10:24
Hi, first, delete system statistics (defaults w...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Ales Kavsek Mar 06, 2017, 11:14

Follow up by mail Click here


Subject: execution plan
Author: Tso P, South Africa
Date: Feb 28, 2017, 11:30, 292 days ago
Os info: rhel5
Oracle info: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
Message: Hi Guys

I have an sql statement that when I run on DEV is performing better but when I ran it on QA is bad...

I checked the execution plans on both environments they look completely different.

On QA most of the tables are full table scans but on DEV they use indexes, now I exported/imported stats from DEV to QA.

I still get that poor performance...

Is it wise to delete the schema stats before importing the stats?

Is it possible that I can import even the fixed stats...

or should I import the whole sys stats instead of the schema?

I have a different schema from DEV to QA then I used remap_schema to impdp but I can see in the stats table column c5 I have dev schema.

Should I update this before importing the stats using dbms_stats?

Please help!!!

Thanks in advance.
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: execution plan
Author: Tso P, South Africa
Date: Mar 01, 2017, 13:54, 291 days ago
Message: Hi Guys

To narrow my question I have tested an sql statement on both instances.

I see two different plans...

select table_rule_uid from table_rule where product_detail_uid='137';

DEV:

---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 127K| 1120K| 3090 (1)| 00:00:38 |
|* 1 | VIEW | index$_join$_001 | 127K| 1120K| 3090 (1)| 00:00:38 |
|* 2 | HASH JOIN | | | | | |
|* 3 | INDEX RANGE SCAN | TABLE_RULE_CODE_UN | 127K| 1120K| 1312 (1)| 00:00:16 |
| 4 | INDEX FAST FULL SCAN| TABLE_RULE_PK | 127K| 1120K| 1195 (1)| 00:00:15 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("PRODUCT_DETAIL_UID"=137)
2 - access(ROWID=ROWID)
3 - access("PRODUCT_DETAIL_UID"=137)


UAT:


Execution Plan
----------------------------------------------------------
Plan hash value: 3804989137

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 183K| 1437K| 693 (1)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID| TABLE_RULE | 183K| 1437K| 693 (1)| 00:00:02 |
|* 2 | INDEX RANGE SCAN | TABLE_RULE_CODE_UN | 183K| | 637 (1)| 00:00:02 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("PRODUCT_DETAIL_UID"=137)

Note
-----
- dynamic sampling used for this statement


On dev I see index$_join$_001 of which it does not appear on UAT.

Please can someone help me with opinion as to which one would be an effective plan...

What is the index$_join$_001?

Thanks in adavance...

Please Help !!!
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: execution plan
Author: Jim Berg, United States
Date: Mar 01, 2017, 14:28, 291 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: I would look at the relative cost numbers. Dev uses the index but the cost is 5X the UAT execution plan
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: execution plan
Author: Tso P, South Africa
Date: Mar 01, 2017, 15:01, 291 days ago
Message: Thanks Jim

What might be the problem here...

I am struggling with the massive query that seem to the running faster on DEV but not good on QA.

I am trying to find way of making it perform better on QA as in DEV.

On DEV indexes are used whereas on QA we have full table scans...

Thanks in advance...




Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: execution plan
Author: Ales Kavsek, Slovenia
Date: Mar 01, 2017, 20:26, 290 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hi Tso,

on DEV your query gets the result from indexes only, Oracle hash joins two indexes where rowids matches and returns the result without the need to access the table.

Do you have a PK on a table in UAT with a proper index?
Double check every single part that is involved in the query (table, indexes, constraints, statistics against real number of rows...) between DEV and UART.

On UAT we can see a note about dynamic sampling, which indicate some lack of statistics on UAT. Use DBMS_STATS and analyze the schema after import; don't fiddle with export/import of statistics (I mean if you're using datapump, use export parameter exclude=statistics, after you import schema, gather complete statistics with dbms_stats).

Regards,
Ales
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: execution plan
Author: raghavendra rao yella, United States
Date: Mar 01, 2017, 22:19, 290 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hi Tso,

I have come across this behavior... several times between various environments. As each environment is unique.. with respect to data/stats/resources... so on.

As Alas stated.. try collecting fresh stats... and make sure that objects(indexes, columns,constraints,..) in UAT are same as that of Dev.

Note#
Based on how much data (current stats information) you have in UAT/Devl.. optimizer decides to perform the best access path... It might be FULL TABLE SCAN... but performers faster.

If you are seeing poor performance of the query.. even after collecting stats.. try running the tuning advisor and see what it recommends...


I use sql profiles.. for production.. if the query is static and doesn't change...
Script coe_xfr_sql_profile.sql from SQLT download is very useful (Correcting Optimizer Cost Estimates to Encourage Good Execution Plans Using the COE XFR SQL Profile Script (Doc ID 1955195.1))

This script will give you various execution plans and the execution timings for a particular sql id in that database.

Hope this helps...

Thanks,
Raghu


Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: execution plan
Author: Tso P, South Africa
Date: Mar 02, 2017, 11:39, 290 days ago
Message: Thanks

I have exported the table from DEV then import it QA environment I still get data from the table instead of indexes...

I gathered the stats using dbms_stats for table I still get data from the table.

Now I took the same dump and imported it to two different environments for test I get data from the indexes like on DEV.

What I have realized is that db_file_multiblock_read_count on QA is 30 whereas on all these environments it's 128.

I now suspect that there is something wrong with the instance...

Could the db_file_multiblock_read_count parameter the cauase of this issue?

Please Help!!!

Thanks in adavance...




Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: execution plan
Author: Ales Kavsek, Slovenia
Date: Mar 02, 2017, 12:37, 290 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Hi,

> Could the db_file_multiblock_read_count parameter the cauase of this issue?

Could be. Change it and you'll see.

You should check and compare other parameters also...why do you have a discrepancy between DEV and QA anyway?

Regards,
Ales
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: execution plan
Author: Mirza Hidayathullah Baig, United Kingdom
Date: Mar 02, 2017, 13:25, 290 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Dear,

check below mos on db_file_mutiblock_read_count if you are going to change the value of this parameter.

DB_FILE_MULTIBLOCK_READ_COUNT' AND EXTENTS MANAGEMENT (Doc ID 181272.1)

How is Parameter DB_FILE_MULTIBLOCK_READ_COUNT Calculated? (Note 1398860.1)

Regards
Mirza
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: execution plan
Author: Tso P, South Africa
Date: Mar 02, 2017, 13:49, 290 days ago
Message: Thanks

My apologies...

I actually mixed things the 30 value is on the test environment that I was using.

Now, if I were to export/import DEV stats on QA what would be the best steps...

Should I export:

1. system stats
2. database
3. schema stats

Then import the stats in that order?

Please Help!!!
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: execution plan
Author: Ales Kavsek, Slovenia
Date: Mar 02, 2017, 14:24, 290 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hi,

> Should I export:

Nope. As I said, don't fiddle with statistics with exporting/importing them. Calculate them if you can't afford to have a binary clone of your DEV database env. on QA.

Fix the instance parameters discrepancy first. If your QA has the same instance parameters as PROD, then you should change the db_file_multiblock_read_count on DEV to 100 from 30 as well. That's the point of QA server, it should resemble PROD as much as possible to minimize nasty surprises. Now, I realize that this also means that query might run slow on DEV, but that's where you should try to fix it. Your current problem is not of why the query runs slow on QA, but why it's running fast on DEV.

With MBRC = 30 on DEV, you're telling CBO that sequential (multiblock) reads are three times more expensive compared to QA where it's 128. Someone, obviously changed the MBRC on DEV with a reason, perhaps to solve problem for some query (and very likely cause a slow down for some others).
Personally, I would completely remove MBRC parameter from spfile (on DEV), letting Oracle choose the proper one (this is really not my recommendation but from Jonathan L.), then I would check that system statistics (not object statistics!) are reasonably set on both DEV and QA.

Regards,
Ales
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: execution plan
Author: Tso P, South Africa
Date: Mar 02, 2017, 22:52, 289 days ago
Message: Thanks Ales

Below are the QA workload system stats:


PNAME PVAL1 CALCULATED FORMULA
------------------------------ ---------- ---------- ------------------------------------------------------------
CPUSPEED 917
CPUSPEEDNW 1737.78664
IOSEEKTIM 10
IOTFRSPEED 4096
MAXTHR
MBRC 81 81 MBRC
MREADTIM 6.239 6.239
SLAVETHR
SREADTIM 2.762 2.762
maximum mbrc 323.74259 = buffer cache size in blocks / sessions
single block Cost per block 1 by definition
multi block Cost per block .0279 = 1/MBRC * MREADTIM/SREADTIM



These looks like they are explicitely set...I stand to be corrected.

Below is DEV:


PNAME PVAL1 CALCULATED FORMULA
------------------------------ ---------- ---------- ------------------------------------------------------------
CPUSPEED
CPUSPEEDNW 1737.78664
IOSEEKTIM 10
IOTFRSPEED 4096
MAXTHR
MBRC 8 = _db_file_optimizer_read_count
MREADTIM 26 = IOSEEKTIM + db_block_size * MBRC / IOTFRSPEED
SLAVETHR
SREADTIM 12 = IOSEEKTIM + db_block_size / IOTFRSPEED
maximum mbrc 1297.37557 = buffer cache size in blocks / sessions
single block Cost per block 1 by definition
multi block Cost per block .2708 = 1/MBRC * MREADTIM/SREADTIM


Please Help!!!
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: execution plan
Author: Ales Kavsek, Slovenia
Date: Mar 03, 2017, 19:17, 288 days ago
Score:   Score: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 Pts
Message: Hi,

sorry, I'm busy installing 12c R2....so I'll be short.
Now you know why CBO executes differently on DEV vs. QA.
Did you try anything I suggested above? It's up to you of what will you do. If QA matches PROD (and you can't change parameters on PROD) then you should "reset" the DEV as well to match QA & PROD.
Personally, I would try to remove MBRC parameter from init file and gather representative (real) workload system statistics (default is NOWORKLOAD!) at DEV during the period when you run typical workload on the server, then repeat on QA (check SYS.AUX_STATS$). Refer to this asktom thread for guidance...

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:499197100346264909

Regards,
Ales
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: execution plan
Author: Tso P, South Africa
Date: Mar 06, 2017, 10:24, 286 days ago
Message: Thanks

I have followed the Tom's link...

I have checked the system stats using the below statement:

select * from sys.aux_stats$;

DEV:


SNAME PNAME PVAL1 PVAL2
-------------------- ------------------------------ ---------- --------------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 10-14-2007 16:11
SYSSTATS_INFO DSTOP 10-14-2007 16:11
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 1737.78664
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM
SYSSTATS_MAIN MREADTIM
SYSSTATS_MAIN CPUSPEED
SYSSTATS_MAIN MBRC
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN SLAVETHR

13 rows selected.



PROD:


SNAME PNAME PVAL1 PVAL2
------------------------------ ------------------------------ ---------- -----------------------------------------------------------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 10-14-2007 16:11
SYSSTATS_INFO DSTOP 10-14-2007 16:11
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 1737.78664
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM
SYSSTATS_MAIN MREADTIM
SYSSTATS_MAIN CPUSPEED
SYSSTATS_MAIN MBRC
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN SLAVETHR

13 rows selected.



QA:


NAME PNAME PVAL1 PVAL2
------------------------------ ------------------------------ ---------- ---------------------------------------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 03-03-2017 11:46
SYSSTATS_INFO DSTOP 03-03-2017 11:46
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 931.842
SYSSTATS_MAIN IOSEEKTIM 17.426
SYSSTATS_MAIN IOTFRSPEED 35548.695
SYSSTATS_MAIN SREADTIM 2.762
SYSSTATS_MAIN MREADTIM 6.239
SYSSTATS_MAIN CPUSPEED 917
SYSSTATS_MAIN MBRC 81
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN SLAVETHR

13 rows selected.


How can I make the QA MBRC values to be default?

Please help!!!
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: execution plan
Author: Ales Kavsek, Slovenia
Date: Mar 06, 2017, 11:14, 286 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hi,

first, delete system statistics (defaults will remain!) with:

exec DBMS_STATS.DELETE_SYSTEM_STATS;

Set db_file_multiblock_read to the same value as it is on DEV and PROD (128).

Regards,
Ales
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here