Re: A question about huge difference in cardinality of a query with 3 predicates
Date: Wed, 1 Nov 2017 18:32:42 +0000
Message-ID: <SYXPR01MB09595EA09BF1CDF79BAB88ECC15F0_at_SYXPR01MB0959.ausprd01.prod.outlook.com>
Hi Jonathan and Andrew,
Further updates on this issue : I had an "aha moment" ....I recalculated stats after going over your suggestions with ESTIMATE_PERCENT=>100 (All other options at default value, just changed this one) and the cardinality came to almost right ballpark.
There were some other changes I had to do - when I posted earlier , in order to hide the actual data, in the post I had shown some other values (but actual data in table had other values) - so I cleaned up that mess -though I don't know if this makes any difference in the issue but this is one other thing I did. So I have to clarify that there was no data that was "out of bound" - it looked like that since I had just modified the data to be different from the actual value whereas the meta data showed the actual values so you thought it is "out of bound". So I think the one question I amleft with is: "was it just the sample size that was the root cause of the issue?" - that is what this makes me believe but can you please share your views on it.
Following is the new plan (and much better cardinality now)
23:12:03 SQL>
23:12:03 SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last peeked_binds'));
PLAN_TABLE_OUTPUT
SQL_ID fp3xsgn8u168q, child number 1
SELECT COUNT (*) FROM processes wpi WHERE WPI.CLIENTID = :v_coid
AND wpi.status = :v_status AND WPI.evt_type_id =
:v_evt_type_id
Plan hash value: 3304333532
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.09 | 11406 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.09 | 11406 | |* 2 | TABLE ACCESS FULL| PROCESSES | 1 | 17573 | 10404 |00:00:00.08 | 11406 |
------------------------------------------------------------------------------------------
Peeked Binds (identified by position):
1 - (VARCHAR2(30), CSID=873): 'G31MQQNM99ABCA9V' 2 - (VARCHAR2(30), CSID=873): 'INP' PLAN_TABLE_OUTPUT
3 - (NUMBER): 5014 Predicate Information (identified by operation id):
2 - filter(("WPI"."CLIENTID"=:V_COID AND "WPI"."STATUS"=:V_STATUS AND
"WPI"."EVT_TYPE_ID"=:V_EVT_TYPE_ID)) 29 rows selected.
Elapsed: 00:00:00.84
Also below is the data from meta tables after this round of stats recalculation:
23:28:17 SQL> EXEC PRINT_TABLE('SELECT * FROM ALL_TAB_COLUMNS WHERE TABLE_NAME=''PROCESSES''')
OWNER : PAASDP003_PORTAL TABLE_NAME : PROCESSES COLUMN_NAME : PROCESS_ID DATA_TYPE : NUMBER DATA_TYPE_MOD : DATA_TYPE_OWNER : DATA_LENGTH : 22 DATA_PRECISION : 12 DATA_SCALE : 0 NULLABLE : N COLUMN_ID : 1 DEFAULT_LENGTH : DATA_DEFAULT : NUM_DISTINCT : 2231714 LOW_VALUE : C50D01015C04 HIGH_VALUE : C5170B1A5218 DENSITY : .000000448086089884277 NUM_NULLS : 0 NUM_BUCKETS : 1 LAST_ANALYZED : 01-nov-2017 13:39:51 SAMPLE_SIZE : 2231714 CHARACTER_SET_NAME : CHAR_COL_DECL_LENGTH : GLOBAL_STATS : YES USER_STATS : NO AVG_COL_LEN : 7 CHAR_LENGTH : 0 CHAR_USED : V80_FMT_IMAGE : NO DATA_UPGRADED : YES HISTOGRAM : NONE ----------------- OWNER : PAASDP003_PORTAL TABLE_NAME : PROCESSES COLUMN_NAME : CLIENTID DATA_TYPE : VARCHAR2 DATA_TYPE_MOD : DATA_TYPE_OWNER : DATA_LENGTH : 20 DATA_PRECISION : DATA_SCALE : NULLABLE : N COLUMN_ID : 2 DEFAULT_LENGTH : DATA_DEFAULT : NUM_DISTINCT : 104 LOW_VALUE : 30324652425132433157563030303936 HIGH_VALUE : 47354D384A4D54394545484A51475859 DENSITY : .000000224043044942139 NUM_NULLS : 0 NUM_BUCKETS : 104 LAST_ANALYZED : 01-nov-2017 13:39:51 SAMPLE_SIZE : 2231714 CHARACTER_SET_NAME : CHAR_CS CHAR_COL_DECL_LENGTH : 20 GLOBAL_STATS : YES USER_STATS : NO AVG_COL_LEN : 17 CHAR_LENGTH : 20 CHAR_USED : B V80_FMT_IMAGE : NO DATA_UPGRADED : YES HISTOGRAM : FREQUENCY ----------------- OWNER : PAASDP003_PORTAL TABLE_NAME : PROCESSES COLUMN_NAME : EVT_TYPE_ID DATA_TYPE : NUMBER DATA_TYPE_MOD : DATA_TYPE_OWNER : DATA_LENGTH : 22 DATA_PRECISION : 12 DATA_SCALE : 0 NULLABLE : N COLUMN_ID : 3 DEFAULT_LENGTH : DATA_DEFAULT : NUM_DISTINCT : 50 LOW_VALUE : C102 HIGH_VALUE : C2330F DENSITY : .000000224043044942139 NUM_NULLS : 0 NUM_BUCKETS : 50 LAST_ANALYZED : 01-nov-2017 13:39:51 SAMPLE_SIZE : 2231714 CHARACTER_SET_NAME : CHAR_COL_DECL_LENGTH : GLOBAL_STATS : YES USER_STATS : NO AVG_COL_LEN : 4 CHAR_LENGTH : 0 CHAR_USED : V80_FMT_IMAGE : NO DATA_UPGRADED : YES HISTOGRAM : FREQUENCY ----------------- OWNER : PAASDP003_PORTAL TABLE_NAME : PROCESSES COLUMN_NAME : STATUS DATA_TYPE : VARCHAR2 DATA_TYPE_MOD : DATA_TYPE_OWNER : DATA_LENGTH : 20 DATA_PRECISION : DATA_SCALE : NULLABLE : N COLUMN_ID : 4 DEFAULT_LENGTH : DATA_DEFAULT : NUM_DISTINCT : 3 LOW_VALUE : 434F4D HIGH_VALUE : 494E50 DENSITY : .000000224043044942139 NUM_NULLS : 0 NUM_BUCKETS : 3 LAST_ANALYZED : 01-nov-2017 13:39:51 SAMPLE_SIZE : 2231714 CHARACTER_SET_NAME : CHAR_CS CHAR_COL_DECL_LENGTH : 20 GLOBAL_STATS : YES USER_STATS : NO AVG_COL_LEN : 4 CHAR_LENGTH : 20 CHAR_USED : B V80_FMT_IMAGE : NO DATA_UPGRADED : YES HISTOGRAM : FREQUENCY -----------------
PL/SQL procedure successfully completed.
Thanks,
Nirav
From: Nirav A Shah <shivam71_at_hotmail.com> Sent: Sunday, October 29, 2017 10:22 PM
To: Jonathan Lewis; oracle-l_at_freelists.org Subject: Re: A question about huge difference in cardinality of a query with 3 predicates
Thanks a lot Jonathan and Andrew!! I am now getting convinced that it is histogram that is causing the issue but would love to see how in a test setup the issue could get resolved. Meaning , how can I differently create a histogram in a test setup that gets the cardinality in a nearly ballpark range...
I will go over the suggestions you have mentioned and post back in next two days time.
Thanks again,
Nirav
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Nov 01 2017 - 19:32:42 CET