{"id":2344,"date":"2023-07-11T12:23:03","date_gmt":"2023-07-11T10:23:03","guid":{"rendered":"http:\/\/clemente.pamplona.name\/dba\/?p=2344"},"modified":"2023-08-14T14:15:08","modified_gmt":"2023-08-14T12:15:08","slug":"jugando-con-las-estadisticas-de-un-esquema","status":"publish","type":"post","link":"http:\/\/clemente.pamplona.name\/dba\/jugando-con-las-estadisticas-de-un-esquema\/","title":{"rendered":"Jugando con las estadisticas de un esquema"},"content":{"rendered":"<p>Hoy vamos a ver algunas consultas practicas de las estadisticas de sistema<\/p>\n<h1>Esquemas o objetos de un esquema con las estadisticas bloqueadas<\/h1>\n<p>Veamos algunas consultas utiles para comprobar cuales son las tablas que estan bloqueadas o para trabajar con las estadistias del esquema APPSCHEMA<\/p>\n<h2>Tablas conlas estadisticas bloqueadas para un esquema<\/h2>\n<p><syntax><br \/>\nselect owner, table_name, stattype_locked<br \/>\nfrom dba_tab_statistics<br \/>\nwhere<br \/>\nstattype_locked is not null and OWNER=&#8217;APPSCHEMA&#8217;;<br \/>\n<\/syntax><\/p>\n<h2>Cuando fueron bloqueadas las estadisticas en estas tablas?<\/h2>\n<p><syntax><br \/>\nset linesize 200<br \/>\nset pagesize 0<br \/>\ncol owner format a20;<br \/>\ncol table_name format a30;<br \/>\nSelect table_name,last_analyzed  from dba_tables<br \/>\nwhere table_name in<br \/>\n(select table_name from dba_tab_statistics where stattype_locked is not null and OWNER=&#8217;APPSCHEMA&#8217;)<br \/>\norder by last_analyzed asc ;<br \/>\n<\/syntax><\/p>\n<h2>Tenemos una metaconsulta para desbloquearlas todas?<\/h2>\n<pre>  select 'exec dbms_stats.unlock_table_stats('||chr(39)||'APPSCHEMA'||chr(39)||','||chr(39)||TABLE_NAME||chr(39)||' );'\r\n       from dba_tab_statistics where stattype_locked is not null and OWNER='APPSCHEMA';\r\n<\/pre>\n<h2>Ver las fechas de las estadisticas que han sido analizadas en los ultimos 2 dias<\/h2>\n<pre>set pagesize 9000;\r\ncolumn owner format a20;\r\ncolumn TABLE_NAME format a30;\r\ncolumn PARTITION_NAME format a30;\r\nselect owner,table_name,partition_name,to_char(LAST_ANALYZED,'dd-mm-yy hh:mi'),NUM_ROWS,STATTYPE_LOCKED from dba_TAB_STATISTICS\r\nwhere OWNER='APPSCHEMA' \r\nand LAST_ANALYZED  &lt; sysdate-2\r\norder by LAST_ANALYZED asc ;\r\n<\/pre>\n<h2>Ver la fecha en la que fueron obtenidas las estadisticas de una tabla en concreto<\/h2>\n<pre>set pagesize 9000;\r\ncolumn owner format a20;\r\ncolumn TABLE_NAME format a30;\r\ncolumn PARTITION_NAME format a30;\r\nselect owner,table_name,partition_name,to_char(LAST_ANALYZED,'dd-mm-yy hh:mi'),NUM_ROWS,STATTYPE_LOCKED from dba_TAB_STATISTICS\r\nwhere TABLE_NAME='APPSCHEMA' \r\norder by LAST_ANALYZED asc ;\r\n<\/pre>\n<h2>Como salvamos las estadisticas de un determinado esquema<\/h2>\n<p>Creamos una tabla de estadisticas llamada \u00ab<em>MISTATS<\/em> bajo el esquema APPSCHEMA<\/p>\n<pre>exec dbms_stats.create_stat_table(ownname =&gt; 'APPSCHEMA', stattab =&gt; 'MISTATS');\r\n<\/pre>\n<p>Si quisiesemos guardar solo las estadisticas de una tabla<\/p>\n<pre>  \r\nexec dbms_stats.export_table_stats(ownname=&gt;'APPSCHEMA', tabname=&gt;'MISTATS', statown=&gt;'APPSCHEMA', stattab=&gt;'MISTATS', cascade=&gt;true);\r\n<\/pre>\n<p>Si queremos salvar los de un esquema<br \/>\n<code><br \/>\nEXEC DBMS_STATS.export_schema_stats('APPSCHEMA','MISTATS',NULL,'APPSCHEMA');<br \/>\n<\/code><br \/>\nMas informacion en<\/p>\n<ul>\n<li><a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/18\/tgsql\/managing-historical-optimizer-statistics.html#GUID-F1399D77-7E2E-434F-A67C-6ADB4C648D95\" target=\"_blank\" rel=\"noopener\">Managing Historical Optimizer Statistics<\/a><\/li>\n<li><a href=\"https:\/\/support.oracle.com\/epmos\/faces\/DocumentDisplay?id=464939.1\" target=\"_blank\" rel=\"noopener\"> Statistics Best Practices: How to Backup and Restore Statistics (Doc ID 464939.1)<\/a><\/li>\n<li><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>Hoy vamos a ver algunas consultas practicas de las estadisticas de sistema Esquemas o objetos de un esquema con las estadisticas bloqueadas Veamos algunas consultas utiles para comprobar cuales son las tablas que estan bloqueadas o para trabajar con las &hellip; <a href=\"http:\/\/clemente.pamplona.name\/dba\/jugando-con-las-estadisticas-de-un-esquema\/\">Sigue leyendo <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[975,1035,9,199,114,13,7,3],"tags":[1034,1022,1033,68,1032],"class_list":["post-2344","post","type-post","status-publish","format-standard","hentry","category-19c","category-consultillas","category-dummie","category-oracle","category-plsql","category-scripting","category-sqlplus","category-tunning","tag-dba_tab_statistics","tag-dbms_stats","tag-dbms_stats-restore_schema_stats","tag-estadisticas","tag-stats"],"_links":{"self":[{"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/posts\/2344","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/comments?post=2344"}],"version-history":[{"count":11,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/posts\/2344\/revisions"}],"predecessor-version":[{"id":2355,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/posts\/2344\/revisions\/2355"}],"wp:attachment":[{"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/media?parent=2344"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/categories?post=2344"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/tags?post=2344"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}