Forum Stats

  • 3,826,733 Users
  • 2,260,699 Discussions
  • 7,897,064 Comments

Discussions

Idea: have filter and access predicates populated by AWR snapshots

Franck Pachot
Franck Pachot Member Posts: 912 Bronze Trophy
edited May 12, 2020 2:08PM in Database Ideas - Ideas

Hi,

Currently, neither AWR nor Statspack are collecting the access_predicates and filter_prediates from the execution plan. There may have been some reasons in the pas (bugs un unparse) but that information is of major importance when we try to tune a query. Curretnly, those columns are collected by dbms_xplan.display_cursor without any problem so I guess it can be collected.

So my idea of the day - and wish - would be to collect access_predicates and filter predicates.

That means: put them as commented out in spcpkg.sql and remove the PARAM('_cursor_plan_unparse_enabled','FALSE') in the query that collects the plan from v$sql_plan

Detailed information about the issue and my current workaround in my blog: AWR don't store explain plan predicates - dbi services Blog

Regards,

Franck

This request is now referenced as: Enh 28146375 - HAVE FILTER AND ACCESS PREDICATES POPULATED BY AWR SNAPSHOTS

This has been implemented in Oracle 20c.

Chris AntogniniFranck PachotUser_DGG74Cherif bhberxuser633661Jonathan LewisUser259623 -OracleDeepak MahtoGeeky NerdmanDavid HueberUser_T3EKAnico_nijhervedbi880720Lothar FlatzborneselFidel ValesIvica ArsovGugs-OracleNikolaus HornErnst LeberMaurice MüllerMikhail Velikikhuser11980779User_5U0K3vinaykumar2Manish ChaturvediKayKrohanwaliaBPeaslandDBAcaadecarvalhomarkmevansTSharma-OraclectriebtmbeetzbhagatsinghMartin Preissuser13654308Mohamed HourisanjeevmkNiall LitchfieldnemecjRandolf GeistHans Forbrichuser12973592pudgeUser_67QPLGogala MladenSayan MalakshinovMahmoudHaStefan KoehlerHemant K ChitaleIggy Fernandezanil pratapanitanmoydcRumpiMarco GralikeMaris Elsinsabhinivesh.jainGalo BaldaNikolay SavvinovasdPro945271William RobertsonBrian BakulaAndreas BuckenhofershiwoPksysassysdbauser12238076991901Jose.CarlosVladimirSitnikovEmad Al-MousaSven W.BeGinAndrewSayerkulikouskiMartinKlierDBAulohmannmoniquevdb-OraclePravin Takpireuser12218407IT-HILLSOzgur Umut VurgunDonatello SettembrinoMautro PaganoFoued1499900Marco MischkejgarrySuntrupthDom BrooksVISHWAKARMAPARDEEPSvetoslav GyurovRobert OliynichenkomtefftPat The DBApattonjg2983299User_502VB2752782anjulsNelson CaleroCraig ElliottLaurens WagemakersToon_Koppelaars-Oracle324185531786761483588MKJ10930279sensoftAparna Dutta-Oracleuser7633857User_IYCH6Raj JamadagniGbenga Ajakaye741405Nenad Noveljicuser321697632241083397048Anand YadavSimonab-OracleperfdbadherzhauPeter HraškoUser_RWAINchristof_bTEAM DB-Supportuser3928122rchemZoran PavlovicPiotr WrzosekRami_robotJanuary Tabaka-OracleKnut Härtelblessed DBAfloo_barDear DBA Frankreguchi_brOleh TyshchenkoGerald Venzl-OraclegassenmjcklammerUser_ZJBSZMartinDecker_ora-solutionsRobKUser_4JTP1Sengottaiyan TEmad_AlMousaAlex Lamar-Oraclegreggaf10r1n-OracleDirk.NachbarUser_HGB7XUser_4DLAW2921359Abhijit GourDaniele BoccioliniRajeshwaran, JeyabalThorsten Kettnervikramrathour
162
166 votes

Delivered · Last Updated

«1

Comments