Stats gathering (8i) to non-data dictionary tables
02/01/2018 11:47
Can anyone help. I have a requirement to gather stats in prod withoutthe values being put into the data dictionary and used immediately.

Instead I'd like them to go into a user stattab from where I can movethem into a non-prod instance.

That is, I'd like to do a gather_table_stats but specify the gatheredstats go to the stattab instead of a backup of the previous values.

Has anyone done this? Many thanks in advance.


Source is Usenet: comp.databases.oracle.server
Sign in to add a comment

Answer score: 5
02/01/2018 11:47 - Mark, Thanks, I have. Crucially I want to gather stats straight to astattab, that is the issue and the thing that I think I can't do withthe supplied procs. I just wanted to know if anyone else had had thatrequirement and if they were able to overcome it.

Thanks for the reply.


Source is Usenet: comp.databases.oracle.server
Sign in to add a comment

Answer score: 5
02/01/2018 11:47 - :-) I miss stuff daily I think.

Anyway, my understanding here is that stattab (if used) will be filledwith a backup of the current stats PRIOR to them being gathered (sothey can be restored if all goes wrong).

I have just proved this on my home Linux box with the followingsteps...

(1) Create a test table(2) Create a stattab(3) Gather stats on test table specifying stattab (at end it is emptyas there are no stats to backup)(3) Gather stats again on test table specifying stattab (this timethere's rows in stattab - the backup of the stats just prior to thisgather) What I want it to gather table stats and have those fresh stats gostraight to the stattab. Does that make sense?

Source is Usenet: comp.databases.oracle.server
Sign in to add a comment

Answer score: 5
02/01/2018 11:47 - Mike, try looking again and look at the stattab entry >> DBMS_STATS.GATHER_TABLE_STATS ( ownname VARCHAR2, tabname VARCHAR2, partname VARCHAR2 DEFAULT NULL, estimate_percent NUMBER DEFAULT NULL, block_sample BOOLEAN DEFAULT FALSE, method_opt VARCHAR2 DEFAULT 'FOR ALL COLUMNS SIZE 1', degree NUMBER DEFAULT NULL, granularity VARCHAR2 DEFAULT 'DEFAULT', cascade BOOLEAN DEFAULT FALSE, stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL); (Everybody misses stuff now and then)HTH -- Mark D Powell --

Source is Usenet: comp.databases.oracle.server
Sign in to add a comment

Answer score: 5
02/01/2018 11:47 - Response from Oracle below. Looks like it isn't possible withouthitting the dd...

hi, In theory , there is a roundabout way of doing it:- 1) Save the current stats:- exec dbms_stats.create_stat_table('user','CURRENT_STATSTAB'); exec dbms_stats.gather_schema_stats(STATID => 'current',ownname=>'user', stattab=>'CURRENT_STATSTAB', statown=>'owner ofcurrent_statstab',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt=>'FOR ALL',granularity=>'ALL' ); This will collect the stats and place the 'old' stats in thecurrent_statstab table.

Create a table to hold the new stats in :- exec dbms_stats.create_stat_table('user','NEW_STATSTAB'); And regather again so the 'new' stats are saved:- exec dbms_stats.gather_schema_stats(STATID => 'new',ownname=>'user', stattab=>'NEW_STATSTAB', statown=>'owner ofcurrent_statstab',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt=>'FOR ALL',granularity=>'ALL' ); Now, restore the old stats:- execdbms_stats.import_table_stats('user',NULL,NULL,'CURRENT_STATSTAB','current',TRUE); ..this is the only possible way of doing it and is not ideal by anystretch. You will need to test the above on a test instance.


Source is Usenet: comp.databases.oracle.server
Sign in to add a comment

Answer score: 5
02/01/2018 11:47 - Mike, have you looked in the Oracle 8i Supplied PL/SQL PackagesReference manual at the dbms_stats package? It has a statistics tablecreate and statistics transfer procedures.

HTH -- Mark D Powell --

Source is Usenet: comp.databases.oracle.server
Sign in to add a comment

Answer score: 5
02/01/2018 11:47 - On 2 Aug 2005 08:00:45 -0700, Mike Collier <mcollier@btinternet.com> Your understanding is incorrect.


Source is Usenet: comp.databases.oracle.server
Sign in to add a comment

eDiscover
Helpforce eDiscover provides technical articles updated each dayHelpforce eDiscover RSS feed contains the latest technical articles in RSS
Click the logo to go back to the main page
Search eDiscover
  
Categories

Click an icon to go to that category

Helpforce eDiscover contains articles about Microsoft Windows Helpforce eDiscover contains articles about Apple products and MacOS Helpforce eDiscover contains articles about Linux and POSIX operating systems Helpforce eDiscover contains articles about Helpforce Helpforce has a large variety of technical information and articles for you to read Helpforce eDiscover contains articles about databases, MYSQL, SQL Server Oracle Helpforce eDiscover contains articles about Java, JVM and the JRE Helpforce eDiscover contains articles about the QNX operating system Helpforce eDiscover contains articles about Oracle Solaris and Open Solaris Helpforce eDiscover contains articles about RISC OS, Acorn and the BBC Micro Helpforce eDiscover contains articles about Amiga and AmigaOS

Type your comment into the box below