{"id":2208,"date":"2011-04-12T08:39:00","date_gmt":"2011-04-12T06:39:00","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/a-sql-statement-is-slow-and-suddenly-fast-have-a-look-at-cardinality-feedback\/"},"modified":"2011-04-12T08:39:00","modified_gmt":"2011-04-12T06:39:00","slug":"a-sql-statement-is-slow-and-suddenly-fast-have-a-look-at-cardinality-feedback","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/a-sql-statement-is-slow-and-suddenly-fast-have-a-look-at-cardinality-feedback\/","title":{"rendered":"A SQL statement is slow and suddenly fast? Have a look at “Cardinality Feedback”!"},"content":{"rendered":"

While discussing with some tuning gurus, I was made aware about this feature. It is not so far from the adaptive cursor sharing perfectly described in Chris Antognini’s blog: http:\/\/antognini.ch\/papers\/BindVariablePeeking_20090718.pdf<\/a><\/p>\n

This feature compares the real execution (number of returned\/actual rows – “A-rows”) with some expectations (number of expected rows – “E-rows”) and chooses another plan for the next executions if the difference between the expectation and the actual result is too big. “Cardinality Feedback” is not well documented in the Oracle documentation.<\/p>\n

The purpose of this post is not to dive into the technical details, but just to make you aware that this feature exists :-), it might explain some strange figures :-). The first execution time of some SQL statements might be poor\/slow and afterwards they may be very fast. Not due to caching, not due to parsing, but due to a complete explain plan change – even if data and statistics are exactly the same.<\/p>\n

Let’s have a look. First of all, we deactivated this functionality with the parameter below :<\/p>\n

<\/code>SQL> alter session set \"_optimizer_use_feedback\"=false;\nSession altered.<\/pre>\n
Then, we start an SQL statement several times – the result was always “slow” (>20s) as you can see below:<\/p>\n
SQL> set timing on\nSQL> select \/* HERVE TEST ADVISOR *\/\u00a0 all d.object_name, f.r_folder_path from dev_common_sp\u00a0 d, loby_az.DM_TABLEB_R f, loby_az.DM_BIG_TABLE_R s where ((d.r_lock_owner='loby eh 3') and (s.r_object_id=d.r_object_id) and (s.i_folder_id=f.r_object_id) and f.r_folder_path != ' ' and d.object_name != ' ' and (d.i_is_reference=0)) and (d.i_has_folder = 1 and d.i_is_deleted = 0) and ( ( d.owner_name in ('loby eh 3','loby clinical reader')) or (exists (select 1 from dm_acl_s ACL_S0, dm_acl_r ACL_R where ACL_S0.r_object_id = ACL_R.r_object_id and d.acl_domain = ACL_S0.owner_name and d.acl_name = ACL_S0.object_name and ((ACL_R.r_accessor_name in ('loby eh 3','dm_world') or (ACL_R.r_is_group = 1 and (ACL_R.r_accessor_name in ('loby eh 3','loby clinical reader','dm_world','loby crd luda','loby cp contributor','loby luda','ploby reader','loby users','loby reader','rr reader','loby old products reader','loby users','loby contributor','enterprise reader','ploby users','test dz','loby enterprise reader','enterprise loby_az reader')))) and ((ACL_R.r_permit_type = 0 or ACL_R.r_permit_type is null) and (((ACL_R.r_accessor_permit >= 2))))))));<\/pre>\n
OBJECT_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 R_FOLDER_PATH\n-----------------------------------\u00a0 \u00a0\u00a0 ----------------------------------------\n1234test\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0 \/loby Projects\/X\/WDBIOA\/BLOG studies\/CSP \n00012abcd_2011-march-13 16:23:1510\u00a0\u00a0\u00a0\u00a0\u00a0 \/loby Projects\/X\/WDBIOA\/BLOG studies\/CSP \n\nElapsed: 00:00:27.23\nSQL> \/\nOBJECT_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 R_FOLDER_PATH\n -------------------------- \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0 ----------------------------------------\n1234test\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/loby Projects\/X\/WDBIOA\/BLOG studies\/CSP\n00012abcd_2011-march-13 16:23:1510\u00a0\u00a0\u00a0\u00a0\u00a0 \/loby Projects\/X\/WDBIOA\/BLOG studies\/CSP\nElapsed: 00:00:23.97<\/pre>\n
If Cardinality Feedback is deactivated, the statement is always slow and it uses an identical execution plan – which is bad. Then, we activate the functionality again (true = default as of 11.2):<\/p>\n
SQL> alter session set \"_optimizer_use_feedback\"=true;\nSession altered.<\/pre>\n
In order to see how Oracle handles the Cardinality Feedback feature, we decided to collect the “plan statistics” to print out the estimations calculated by Oracle using the hint “\/*+ gather_plan_statistics *\/”<\/p>\n
SQL> select \/*+ gather_plan_statistics *\/ all d.object_name, f.r_folder_path from dev_common_sp\u00a0 d, loby_az.DM_TABLEB_R f, loby_az.DM_BIG_TABLE_R s where ((d.r_lock_owner='loby eh 3') and (s.r_object_id=d.r_object_id) and (s.i_folder_id=f.r_object_id) and f.r_folder_path != ' ' and d.object_name != ' ' and (d.i_is_reference=0)) and (d.i_has_folder = 1 and d.i_is_deleted = 0) and ( ( d.owner_name in ('loby eh 3','loby clinical reader')) or (exists (select 1 from dm_acl_s ACL_S0, dm_acl_r ACL_R where ACL_S0.r_object_id = ACL_R.r_object_id and d.acl_domain = ACL_S0.owner_name and d.acl_name = ACL_S0.object_name and ((ACL_R.r_accessor_name in ('loby eh 3','dm_world') or (ACL_R.r_is_group = 1 and (ACL_R.r_accessor_name in ('loby eh 3','loby clinical reader','dm_world','loby crd luda','loby cp contributor','loby luda','ploby reader','loby users','loby reader','rr reader','loby old products reader','loby users','loby contributor','enterprise reader','ploby users','test dz','loby enterprise reader','enterprise loby_az reader')))) and ((ACL_R.r_permit_type = 0 or ACL_R.r_permit_type is null) and (((ACL_R.r_accessor_permit >= 2))))))));\n\u00a0\nOBJECT_NAME R_FOLDER_PATH\n-------------------------- \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0 ----------------------------------------\n1234test\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0 \/loby Projects\/X\/WDBIOA\/BLOG studies\/CSP\n00012abcd_2011-march-13 16:23:1510\u00a0\u00a0\u00a0\u00a0\u00a0 \/loby Projects\/X\/WDBIOA\/BLOG studies\/CSP\nElapsed: 00:00:35.04<\/pre>\n
SQL> select * from table (dbms_xplan.display_cursor (format=>'ALLSTATS LAST'));\n------------------------------------------------------------------------------------\n| Id\u00a0 | Operation\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Starts | E-Rows | A-Rows | |-----------------------------------------------------------------------------------\n|\u00a0\u00a0 0 | SELECT STATEMENT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \n|*\u00a0 1 |\u00a0 FILTER\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 \u00a0 \u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \n|*\u00a0 2 |\u00a0\u00a0 HASH JOIN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 \u00a0 \u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0 945K|\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0 \n|*\u00a0 3 |\u00a0\u00a0\u00a0 TABLE ACCESS FULL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | DM_TABLEB_R\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0 1379K|\u00a0\u00a0 1382K|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \n|*\u00a0 4 |\u00a0\u00a0\u00a0 HASH JOIN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0 626K|\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0 \n|*\u00a0 5 |\u00a0\u00a0\u00a0\u00a0 HASH JOIN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 \u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0 429K|\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0 \n|*\u00a0 6 |\u00a0\u00a0\u00a0\u00a0\u00a0 TABLE ACCESS BY INDEX | DM_BIGTABLE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0 429K|\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \n|*\u00a0 7 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INDEX RANGE SCAN \u00a0\u00a0\u00a0 | D_1F0095AF8000002A |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0 3439K|\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0 \n|\u00a0\u00a0 8 |\u00a0\u00a0\u00a0\u00a0\u00a0 INDEX FAST FULL SCAN\u00a0 | D_1F0095AF80000500 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0 4613K|\u00a0\u00a0 4633K|\u00a0 \n|*\u00a0 9 |\u00a0\u00a0\u00a0\u00a0 INDEX FAST FULL SCAN\u00a0\u00a0 | D_1F0095AF80000010 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0 6943K|\u00a0\u00a0 6961K| \n|\u00a0 10 |\u00a0\u00a0 NESTED LOOPS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\u00a0\u00a0\u00a0\u00a0 \n|\u00a0 11 |\u00a0\u00a0\u00a0 NESTED LOOPS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\u00a0\u00a0\u00a0\u00a0\u00a0 \n|* 12 |\u00a0\u00a0\u00a0\u00a0 INDEX RANGE SCAN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | D_1F0095AF80002D01 |\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\u00a0 \n|* 13 |\u00a0\u00a0\u00a0\u00a0 INDEX RANGE SCAN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | D_1F0095AF80000102 |\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\u00a0\u00a0\u00a0\u00a0 12 |\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\u00a0\u00a0\u00a0\u00a0 \n|* 14 |\u00a0\u00a0\u00a0 TABLE ACCESS BY INDEX\u00a0\u00a0 | DM_ACL_R\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \n------------------------------------------------------------------------------------<\/pre>\n
As you can see, there is a huge difference between the E-rows and the A-rows in some lines of the plan. In particular this one:<\/samp><\/samp><\/p>\n
|*\u00a0 7 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INDEX RANGE SCAN\u00a0\u00a0\u00a0\u00a0 | D_1F0095AF8000002A |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0 3439K|\u00a0\u00a0\u00a0\u00a0\u00a0 1 |00:00:00.01 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3 |\n\u00a0\nSQL> \/\nOBJECT_NAME R_FOLDER_PATH\n -------------------------- \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0 ----------------------------------------\n1234test\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/loby Projects\/X\/WDBIOA\/BLOG studies\/CSP\n00012abcd_2011-march-13 16:23:1510\u00a0\u00a0\u00a0\u00a0\u00a0 \/loby Projects\/X\/WDBIOA\/BLOG studies\/CSP\nElapsed: 00:00:23.97<\/pre>\n
The second execution of this statement was fast. Once again, not due to caching or faster parsing, but due to “Cardinality Feedback”. Oracle tried to take care of these large differences between E-Rows and A-Rows.<\/p>\n
The second time, the explain plan is completely reconsidered:<\/p>\n
OBJECT_NAME R_FOLDER_PATH\n -------------------------- \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0 ---------------------------------------- 1234test\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/loby Projects\/X\/WDBIOA\/BLOG studies\/CSP\n00012abcd_2011-march-13 16:23:1510\u00a0\u00a0\u00a0\u00a0\u00a0 \/loby Projects\/X\/WDBIOA\/BLOG studies\/CSP\nElapsed: 00:00:00.01<\/pre>\n
This second start was fast because of the recalculation of the plan. The estimated rows (E-rows) and the real output rows (A-rows) had too much differences in the first run, therefore Oracle recalculated the plan.<\/p>\n
If you generate the SQL plan for this statement, you will see in the “Note” at the end if “Cardinality Feedback” was used :<\/p>\n
<\/code><\/samp>SQL> select * from table (dbms_xplan.display_cursor (format=>'ALLSTATS LAST'));\nPLAN_TABLE_OUTPUT\n--------------------------------------------------------------------------------\nSQL_ID\u00a0 5w3td7kauxkkh, child number 1\n-------------------------------------\n select \/*+ gather_plan_statistics *\/\u00a0 all d.object_name, f.r_folder_path from\ndev_common_sp\u00a0 d, loby_az.DM_TABLEB_R f, loby_az.DM_BIG_TABLE_R s\nwhere ((d.r_lock_owner='loby eh 3') and (s.r_object_id=d.r_object_id)\nand (s.i_folder_id=f.r_object_id) and f.r_folder_path != ' ' and\nd.object_name != ' ' and (d.i_is_reference=0)) and (d.i_has_folder = 1\nand d.i_is_deleted = 0) and ( ( d.owner_name in ('loby eh 3','credi\nclinical reader')) or (exists (select 1 from dm_acl_s\nACL_S0, dm_acl_r ACL_R where ACL_S0.r_object_id = ACL_R.r_object_id and\nd.acl_domain = ACL_S0.owner_name and d.acl_name = ACL_S0.object_name\nand ((ACL_R.r_accessor_name in ('loby eh 3','dm_world') or\n(ACL_R.r_is_group = 1 and (ACL_R.r_accessor_name in ('loby eh 3','cr\nPlan hash value: 683492381\n\n------------------------------------------------------------------------------------------\n| Id\u00a0 | Operation\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Starts | E-Rows | A-Rows |\n------------------------------------------------------------------------------------------\n|\u00a0\u00a0 0 | SELECT STATEMENT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\n|*\u00a0 1 |\u00a0 FILTER\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\n|\u00a0\u00a0 2 |\u00a0\u00a0 NESTED LOOPS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\n|\u00a0\u00a0 3 |\u00a0\u00a0\u00a0 NESTED LOOPS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0 2 |\u00a0\u00a0\u00a0\u00a0\u00a0 6 |\n|\u00a0\u00a0 4 |\u00a0\u00a0\u00a0\u00a0 NESTED LOOPS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\n|\u00a0\u00a0 5 |\u00a0\u00a0\u00a0\u00a0\u00a0 NESTED LOOPS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\n|*\u00a0 6 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 TABLE ACCESS BY INDEX ROWID| DM_BIG_TABLE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\n|*\u00a0 7 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INDEX RANGE SCAN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | D_1F0095AF8000002A |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\n|*\u00a0 8 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INDEX UNIQUE SCAN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | D_1F0095AF80000500 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\n|*\u00a0 9 |\u00a0\u00a0\u00a0\u00a0\u00a0 TABLE ACCESS BY INDEX ROWID | DM_BIG_TABLE_R\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\n|* 10 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INDEX RANGE SCAN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | D_1F0095AF80000109 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0 4 |\u00a0\u00a0\u00a0\u00a0\u00a0 4 |\n|* 11 |\u00a0\u00a0\u00a0\u00a0 INDEX RANGE SCAN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | D_1F0095AF80000132 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0 7 |\u00a0\u00a0\u00a0\u00a0\u00a0 6 |\n|* 12 |\u00a0\u00a0\u00a0 TABLE ACCESS BY INDEX ROWID\u00a0\u00a0 | DM_TABLEB_R\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 6 |\u00a0\u00a0\u00a0\u00a0\u00a0 2 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\n|\u00a0 13 |\u00a0\u00a0 NESTED LOOPS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\n|\u00a0 14 |\u00a0\u00a0\u00a0 NESTED LOOPS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\n|* 15 |\u00a0\u00a0\u00a0\u00a0 INDEX RANGE SCAN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | D_1F0095AF80002D01 |\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\n|* 16 |\u00a0\u00a0\u00a0\u00a0 INDEX RANGE SCAN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | D_1F0095AF80000102 |\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\u00a0\u00a0\u00a0\u00a0 12 |\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\n|* 17 |\u00a0\u00a0\u00a0 TABLE ACCESS BY INDEX ROWID\u00a0\u00a0 | DM_ACL_R\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\n------------------------------------------------------------------------------------------<\/code><\/pre>\n
Here, the E-rows and A-rows are quite similar, Oracle will not re-evaluate the plan in the future.<\/p>\n
Query Block Name \/ Object Alias (identified by operation id):\n-------------------------------------------------------------\n1 - SEL$F5BB74E1\nPLAN_TABLE_OUTPUT\n-----------------------------\n 6 - SEL$F5BB74E1 \/ YG_@SEL$2\n 7 - SEL$F5BB74E1 \/ YG_@SEL$2\n 8 - SEL$F5BB74E1 \/ OTC_@SEL$2\n 9 - SEL$F5BB74E1 \/ S@SEL$1\n 10 - SEL$F5BB74E1 \/ S@SEL$1\n 11 - SEL$F5BB74E1 \/ F@SEL$1\n 12 - SEL$F5BB74E1 \/ F@SEL$1\n 13 - SEL$3\n 15 - SEL$3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/ ACL_S0@SEL$3\n 16 - SEL$3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/ ACL_R@SEL$3\n 17 - SEL$3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/ ACL_R@SEL$3\nPredicate Information (identified by operation id):\n---------------------------------------------------\n 1 - filter((INTERNAL_FUNCTION(\"YG_\".\"OWNER_NAME\") OR\u00a0 IS NOT NULL))\n 6 - filter((\"YG_\".\"I_HAS_FOLDER\"=1 AND \"YG_\".\"I_IS_DELETED\"=0 AND \"YG_\".\"I_IS_REFERENCE\"=0\n AND \"YG_\".\"OBJECT_NAME\"<>' '))\n 7 - access(\"YG_\".\"R_LOCK_OWNER\"='loby eh 3')\n 8 - access(\"YG_\".\"R_OBJECT_ID\"=\"OTC_\".\"R_OBJECT_ID\")\n 9 - filter(\"S\".\"I_FOLDER_ID\" IS NOT NULL)\n 10 - access(\"S\".\"R_OBJECT_ID\"=\"YG_\".\"R_OBJECT_ID\")\n 11 - access(\"S\".\"I_FOLDER_ID\"=\"F\".\"R_OBJECT_ID\")\n 12 - filter(\"F\".\"R_FOLDER_PATH\"<>' ')\n 15 - access(\"ACL_S0\".\"OWNER_NAME\"=:B1 AND \"ACL_S0\".\"OBJECT_NAME\"=:B2)\n 16 - access(\"ACL_S0\".\"R_OBJECT_ID\"=\"ACL_R\".\"R_OBJECT_ID\")\n 17 - filter(((INTERNAL_FUNCTION(\"ACL_R\".\"R_ACCESSOR_NAME\") OR (\"ACL_R\".\"R_IS_GROUP\"=1 AND\n INTERNAL_FUNCTION(\"ACL_R\".\"R_ACCESSOR_NAME\"))) AND \"ACL_R\".\"R_ACCESSOR_PERMIT\">=2 AND\n (\"ACL_R\".\"R_PERMIT_TYPE\"=0 OR \"ACL_R\".\"R_PERMIT_TYPE\" IS NULL)))\nColumn Projection Information (identified by operation id):\n-----------------------------------------------------------\n 1 - \"YG_\".\"OBJECT_NAME\"[VARCHAR2,255], \"F\".\"R_FOLDER_PATH\"[VARCHAR2,740]\n 2 - \"YG_\".\"OBJECT_NAME\"[VARCHAR2,255], \"YG_\".\"OWNER_NAME\"[VARCHAR2,32],\"YG_\".\"ACL_DOMAIN\"[VARCHAR2,32], \"YG_\".\"ACL_NAME\"[VARCHAR2,32],\"F\".\"R_FOLDER_PATH\"[VARCHAR2,740]\n 3 - \"YG_\".\"OBJECT_NAME\"[VARCHAR2,255], \"YG_\".\"OWNER_NAME\"[VARCHAR2,32],\"YG_\".\"ACL_DOMAIN\"[VARCHAR2,32], \"YG_\".\"ACL_NAME\"[VARCHAR2,32], \"F\".ROWID[ROWID,10]\n 4 - \"YG_\".\"OBJECT_NAME\"[VARCHAR2,255], \"YG_\".\"OWNER_NAME\"[VARCHAR2,32],\"YG_\".\"ACL_DOMAIN\"[VARCHAR2,32], \"YG_\".\"ACL_NAME\"[VARCHAR2,32], \"S\".\"I_FOLDER_ID\"[VARCHAR2,16]\n 5 - \"YG_\".\"R_OBJECT_ID\"[VARCHAR2,16], \"YG_\".\"OBJECT_NAME\"[VARCHAR2,255],\"YG_\".\"OWNER_NAME\"[VARCHAR2,32], \"YG_\".\"ACL_DOMAIN\"[VARCHAR2,32], \"YG_\".\"ACL_NAME\"[VARCHAR2,32]\n 6 - \"YG_\".\"R_OBJECT_ID\"[VARCHAR2,16], \"YG_\".\"OBJECT_NAME\"[VARCHAR2,255],\"YG_\".\"OWNER_NAME\"[VARCHAR2,32], \"YG_\".\"ACL_DOMAIN\"[VARCHAR2,32], \"YG_\".\"ACL_NAME\"[VARCHAR2,32]\n 7 - \"SYS_ALIAS_2\".ROWID[ROWID,10]\n 9 - \"S\".\"I_FOLDER_ID\"[VARCHAR2,16]\nPLAN_TABLE_OUTPUT\n-------------------------------------------------------------------------------------------------------\n 10 - \"S\".ROWID[ROWID,10]\n 11 - \"F\".ROWID[ROWID,10]\n 12 - \"F\".\"R_FOLDER_PATH\"[VARCHAR2,740]\n 14 - \"ACL_R\".ROWID[ROWID,10]\n 15 - \"ACL_S0\".\"R_OBJECT_ID\"[VARCHAR2,16]\n 16 - \"ACL_R\".ROWID[ROWID,10]\nNote\n-----\n - cardinality feedback used for this statement <===== !!! Cardinality Feedback has been used !!!\n105 rows selected.<\/pre>\n
However in this particular case, we have not solved anything, since the first execution was important.
\nI will tell you in the next posts how I have solved the problem and identified the real problem \ud83d\ude42<\/p>\n
in this particular <\/span><\/div>\n","protected":false},"excerpt":{"rendered":"
Discussing with some tuning gourous we were made aware about this feature. It is not so far from the adaptive cursor sharing perfectly described in  Chris’s blog : http:\/\/antognini.ch\/papers\/BindVariablePeeking_20090718.pdf<\/a><\/p>\n
This feature, compares the real execution (number of returned\/actual rows “A-rows”) with some expectations (number of expected rows “E-rows”) and chooses another plan for the next executions if the difference between the expectation and the actual result is too big. “Cardinality Feedback” is not well documented in the Oracle documentation.<\/p>\n","protected":false},"author":8,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[197],"tags":[260,17,18],"type_dbi":[],"class_list":["post-2208","post","type-post","status-publish","format-standard","hentry","category-application-integration-middleware","tag-cardinality-feedback","tag-oracle-11g","tag-oracle-optimizer"],"acf":[],"yoast_head":"\nA SQL statement is slow and suddenly fast? Have a look at "Cardinality Feedback"! - dbi Blog<\/title>\n<meta name=\"description\" content=\"Cardinality Feedback compares the number of returned\/actual rows ("A-rows") with the number of expected rows ("E-rows"). It chooses another plan for the next executions if the difference between the expectation and the actual result is too big.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/a-sql-statement-is-slow-and-suddenly-fast-have-a-look-at-cardinality-feedback\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"A SQL statement is slow and suddenly fast? Have a look at "Cardinality Feedback"!\" \/>\n<meta property=\"og:description\" content=\"Cardinality Feedback compares the number of returned\/actual rows ("A-rows") with the number of expected rows ("E-rows"). It chooses another plan for the next executions if the difference between the expectation and the actual result is too big.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/a-sql-statement-is-slow-and-suddenly-fast-have-a-look-at-cardinality-feedback\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2011-04-12T06:39:00+00:00\" \/>\n<meta name=\"author\" content=\"Herv\u00e9 Schweitzer\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Herv\u00e9 Schweitzer\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"9 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/a-sql-statement-is-slow-and-suddenly-fast-have-a-look-at-cardinality-feedback\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/a-sql-statement-is-slow-and-suddenly-fast-have-a-look-at-cardinality-feedback\\\/\"},\"author\":{\"name\":\"Herv\u00e9 Schweitzer\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/7fb08fbefcb9b2c146ef4533cfee00c7\"},\"headline\":\"A SQL statement is slow and suddenly fast? Have a look at “Cardinality Feedback”!\",\"datePublished\":\"2011-04-12T06:39:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/a-sql-statement-is-slow-and-suddenly-fast-have-a-look-at-cardinality-feedback\\\/\"},\"wordCount\":462,\"commentCount\":0,\"keywords\":[\"Cardinality feedback\",\"Oracle 11g\",\"Oracle optimizer\"],\"articleSection\":[\"Application integration & Middleware\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/a-sql-statement-is-slow-and-suddenly-fast-have-a-look-at-cardinality-feedback\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/a-sql-statement-is-slow-and-suddenly-fast-have-a-look-at-cardinality-feedback\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/a-sql-statement-is-slow-and-suddenly-fast-have-a-look-at-cardinality-feedback\\\/\",\"name\":\"A SQL statement is slow and suddenly fast? Have a look at \\\"Cardinality Feedback\\\"! - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2011-04-12T06:39:00+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/7fb08fbefcb9b2c146ef4533cfee00c7\"},\"description\":\"Cardinality Feedback compares the number of returned\\\/actual rows (\\\"A-rows\\\") with the number of expected rows (\\\"E-rows\\\"). It chooses another plan for the next executions if the difference between the expectation and the actual result is too big.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/a-sql-statement-is-slow-and-suddenly-fast-have-a-look-at-cardinality-feedback\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/a-sql-statement-is-slow-and-suddenly-fast-have-a-look-at-cardinality-feedback\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/a-sql-statement-is-slow-and-suddenly-fast-have-a-look-at-cardinality-feedback\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"A SQL statement is slow and suddenly fast? Have a look at “Cardinality Feedback”!\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/7fb08fbefcb9b2c146ef4533cfee00c7\",\"name\":\"Herv\u00e9 Schweitzer\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/0730552c0cfc0a9297c56f2efe56dadd8de399885ca6161a2bee83aebe291afc?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/0730552c0cfc0a9297c56f2efe56dadd8de399885ca6161a2bee83aebe291afc?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/0730552c0cfc0a9297c56f2efe56dadd8de399885ca6161a2bee83aebe291afc?s=96&d=mm&r=g\",\"caption\":\"Herv\u00e9 Schweitzer\"},\"description\":\"Herv\u00e9 Schweitzer has more than ten years of experience in database and infrastructure management, engineering, and optimization. He is specialized in Oracle technologies such as standardisation, Backup and Recovery, Tuning, and in High Availability solutions such as Oracle Data Guard, Oracle Grid Infrastructure, Oracle Real Application Clusters (RAC), Oracle GoldenGate, and Oracle Failsafe. Herv\u00e9 Schweitzer is \\\"Oracle Certified Master 11g (OCM 11g)\\\". Prior to joining dbi services, Herv\u00e9 Schweitzer was Senior Consultant at Trivadis in Basel. He also worked as an IT Administrator and Oracle DBA at Crossair in Basel. Herv\u00e9 Schweitzer holds a BTS degree in Information Systems from France. His branch-related experience covers Pharma, Transport and Logistics, Banking, Energy, Automotive etc.\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/herve-schweitzer\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"A SQL statement is slow and suddenly fast? Have a look at \"Cardinality Feedback\"! - dbi Blog","description":"Cardinality Feedback compares the number of returned\/actual rows (\"A-rows\") with the number of expected rows (\"E-rows\"). It chooses another plan for the next executions if the difference between the expectation and the actual result is too big.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/a-sql-statement-is-slow-and-suddenly-fast-have-a-look-at-cardinality-feedback\/","og_locale":"en_US","og_type":"article","og_title":"A SQL statement is slow and suddenly fast? Have a look at \"Cardinality Feedback\"!","og_description":"Cardinality Feedback compares the number of returned\/actual rows (\"A-rows\") with the number of expected rows (\"E-rows\"). It chooses another plan for the next executions if the difference between the expectation and the actual result is too big.","og_url":"https:\/\/www.dbi-services.com\/blog\/a-sql-statement-is-slow-and-suddenly-fast-have-a-look-at-cardinality-feedback\/","og_site_name":"dbi Blog","article_published_time":"2011-04-12T06:39:00+00:00","author":"Herv\u00e9 Schweitzer","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Herv\u00e9 Schweitzer","Est. reading time":"9 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/a-sql-statement-is-slow-and-suddenly-fast-have-a-look-at-cardinality-feedback\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/a-sql-statement-is-slow-and-suddenly-fast-have-a-look-at-cardinality-feedback\/"},"author":{"name":"Herv\u00e9 Schweitzer","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/7fb08fbefcb9b2c146ef4533cfee00c7"},"headline":"A SQL statement is slow and suddenly fast? Have a look at “Cardinality Feedback”!","datePublished":"2011-04-12T06:39:00+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/a-sql-statement-is-slow-and-suddenly-fast-have-a-look-at-cardinality-feedback\/"},"wordCount":462,"commentCount":0,"keywords":["Cardinality feedback","Oracle 11g","Oracle optimizer"],"articleSection":["Application integration & Middleware"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/a-sql-statement-is-slow-and-suddenly-fast-have-a-look-at-cardinality-feedback\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/a-sql-statement-is-slow-and-suddenly-fast-have-a-look-at-cardinality-feedback\/","url":"https:\/\/www.dbi-services.com\/blog\/a-sql-statement-is-slow-and-suddenly-fast-have-a-look-at-cardinality-feedback\/","name":"A SQL statement is slow and suddenly fast? Have a look at \"Cardinality Feedback\"! - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2011-04-12T06:39:00+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/7fb08fbefcb9b2c146ef4533cfee00c7"},"description":"Cardinality Feedback compares the number of returned\/actual rows (\"A-rows\") with the number of expected rows (\"E-rows\"). It chooses another plan for the next executions if the difference between the expectation and the actual result is too big.","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/a-sql-statement-is-slow-and-suddenly-fast-have-a-look-at-cardinality-feedback\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/a-sql-statement-is-slow-and-suddenly-fast-have-a-look-at-cardinality-feedback\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/a-sql-statement-is-slow-and-suddenly-fast-have-a-look-at-cardinality-feedback\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"A SQL statement is slow and suddenly fast? Have a look at “Cardinality Feedback”!"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/7fb08fbefcb9b2c146ef4533cfee00c7","name":"Herv\u00e9 Schweitzer","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/0730552c0cfc0a9297c56f2efe56dadd8de399885ca6161a2bee83aebe291afc?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/0730552c0cfc0a9297c56f2efe56dadd8de399885ca6161a2bee83aebe291afc?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0730552c0cfc0a9297c56f2efe56dadd8de399885ca6161a2bee83aebe291afc?s=96&d=mm&r=g","caption":"Herv\u00e9 Schweitzer"},"description":"Herv\u00e9 Schweitzer has more than ten years of experience in database and infrastructure management, engineering, and optimization. He is specialized in Oracle technologies such as standardisation, Backup and Recovery, Tuning, and in High Availability solutions such as Oracle Data Guard, Oracle Grid Infrastructure, Oracle Real Application Clusters (RAC), Oracle GoldenGate, and Oracle Failsafe. Herv\u00e9 Schweitzer is \"Oracle Certified Master 11g (OCM 11g)\". Prior to joining dbi services, Herv\u00e9 Schweitzer was Senior Consultant at Trivadis in Basel. He also worked as an IT Administrator and Oracle DBA at Crossair in Basel. Herv\u00e9 Schweitzer holds a BTS degree in Information Systems from France. His branch-related experience covers Pharma, Transport and Logistics, Banking, Energy, Automotive etc.","url":"https:\/\/www.dbi-services.com\/blog\/author\/herve-schweitzer\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/2208","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/8"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=2208"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/2208\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=2208"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=2208"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=2208"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=2208"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}