| dbms_stats and bind variables [message #625752] |
Mon, 13 October 2014 16:36  |
projob66
Messages: 2
Registered: April 2007
|
Junior Member |
|
|
I am trying to programatically use pl/sql and loop through successive calls to dbms_stats.gather_table_stats using an in_memory table for the stattab parameter. This is done on Active DG so read_only. It has a CLOB so no remote link stuff or synonyms or views.. I was thinking I could have it populate one row in my in-memory table and then process that row and pass it off over a link to a writeable destination.
So far, the processor has not been impressed with my attempts..
like this:
create or replace package body sysrep.gather_stats is
procedure gather_one_table IS
gath_full_rec gathfull_tab_type;
BEGIN
select 13579 into gath_full_rec(0).statid from dual where rownum < 2;
commit;
dbms_stats.gather_table_stats (
ownname => 'GRATISADMIN',
tabname => 'ACCOUNT_ATTRIBUTES_BASE',
estimate_percent => 40,
block_sample => FALSE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO',
degree => 1,
granularity => 'ALL',
cascade => TRUE,
stattab => 'gath_full_rec',
statid => 'gadmin_schema_1',
statown => 'SYSREP',
invalidate =>TRUE,
stattype => 'DATA',
force => TRUE );
END gather_one_table;
END gather_stats;
/
SQL> @gath_stats
Package created.
Warning: Package Body created with compilation errors.
SQL> show error
Errors for PACKAGE BODY SYSREP.GATHER_STATS:
LINE/COL ERROR
-------- -----------------------------------------------------------------
8/5 PL/SQL: Statement ignored
8/5 PLS-00306: wrong number or types of arguments in call to
'GATHER_TABLE_STATS'
|
|
|
|
|
|
|
|
|
|
| Re: dbms_stats and bind variables [message #625783 is a reply to message #625763] |
Tue, 14 October 2014 04:02  |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
And if you want to pass an array to a procedure you need to pass the array, not a string containing the name of the array.
And as BS sort of pointed out invalidate is not called invalidate.
|
|
|
|
| Re: dbms_stats and bind variables [message #625812 is a reply to message #625752] |
Tue, 14 October 2014 07:32 |
projob66
Messages: 2
Registered: April 2007
|
Junior Member |
|
|
Thank you for the assist. I have the no_invalidate => TRUE correct in the code. Just not in the copy to the topic here. sorry for that. I do appreciate the comment on the DD being read only and that is the actual reason for my work. They want to use the CPU here on the Active DG cluster and not on the real cluster. I am trying to unravel the dbms_stats.gather_table_stats into an in-memory object to get around the read only aspect of the DD. Once I get it in the in-memory object I can split off and unravel the CLOB field of the standard stats output 'CL1' and then push all of it over a db_link to somewhere I can write to tables... So here is all of the stuff, hopefully indented, and this poor humble genuflection is sufficient to continue to get your asssistance... I thank you again. I am assuming the comment dont pass the name of the array, pass the array, meant to pass the array without single-quotes?
ralph
grant execute on SYS.DBMS_STATS to sysrep;
DECLARE
-- gath_full_rec sysrep.stat_schema_bucket%ROWTYPE;
TYPE gathfull_tab_type IS TABLE of sysrep.stat_schema_bucket%ROWTYPE
INDEX BY BINARY_INTEGER;
gath_full_rec gathfull_tab_type;
BEGIN
dbms_stats.gather_table_stats (
ownname => 'GRATISADMIN',
tabname => 'ACCOUNT_ATTRIBUTES_BASE',
partname => NULL,
estimate_percent => 40,
block_sample => FALSE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO',
degree => 1,
granularity => 'ALL',
cascade => TRUE,
stattab => gath_full_rec,
statid => 'gadmin_schema_1',
statown => 'SYSREP',
no_invalidate =>TRUE,
stattype => 'DATA',
force => TRUE,
context => NULL );
END;
/
|
|
|
|
| Re: dbms_stats and bind variables [message #625815 is a reply to message #625812] |
Tue, 14 October 2014 07:41 |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
When you post code, please enclose it within tags. You have been asked to do this already. Furthermore, there is no point in posting code without showing what is wrong with it. Did you run that block? What happened?
And lastly, I can only repeat: you cannot analyze tables in a read only database. You seem to be under the impression that the STATTAB argument specifies the destination for the new statistics. It doesn't. The new stats go to the data dictionary, it is the existing stats that go the STATTAB. But that is ancient history: no-one uses it any more. From release 10.x, the existing stats go to the AWR automatically. You can't stop it. Except of course they can't go anywhere in a read only database.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
