| dbms_stats.gather_table_stats in stored procedure [message #285810] |
Wed, 05 December 2007 13:58  |
TLegend33
Messages: 203
Registered: March 2005
|
Senior Member |
|
|
I have a stored procedure that creates several tables. When a particular table creation is complete, I would like to analyze that table for improved performance. Currently, I employ this syntax in my stored procedure:
create or replace PROCEDURE test_test_analyze_sp as
BEGIN
dbms_output.put_line('BEGIN PROCEDURE ' || sysdate);
EXECUTE IMMEDIATE 'CREATE TABLE test_analyze as select * from temp_test_analyze';
dbms_output.put_line('test_analyze created '|| sysdate);
EXECUTE IMMEDIATE 'analyze table test_analyze estimate statistics sample 10 percent';
dbms_output.put_line('test_analyze analyzed '|| sysdate);
END;
/
This analyzes the table, but I understand that dbms_stats.gather_table_stats is the preferred method for analyzing a table. Thus, I'm testing out this syntax:
create or replace PROCEDURE test_test_analyze_sp as
BEGIN
dbms_output.put_line('BEGIN PROCEDURE ' || sysdate);
EXECUTE IMMEDIATE 'CREATE TABLE test_analyze as select * from temp_test_analyze';
dbms_output.put_line('test_analyze created '|| sysdate);
EXECUTE IMMEDIATE 'exec sys.dbms_stats.gather_table_stats('DB1','TEST_ANALYZE',estimate_percent=>5, cascade=>TRUE);
dbms_output.put_line('test_analyze analyzed '|| sysdate);
While this stored procedure will compile correctly, I get the below error when I attempt to execute the code:
SQL> @test_test_analyze_sp.sql
Procedure created.
SQL> show errors
No errors.
SQL> exec test_test_analyze_sp;
BEGIN test_test_analyze_sp; END;
*
ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at "DB1.TEST_TEST_ANALYZE_SP", line 4
ORA-06512: at line 1
Any idea of how I can utilize dbms_stats in my stored procedure?
Thanks.
|
|
|
|
|
|
| Re: dbms_stats.gather_table_stats in stored procedure [message #285815 is a reply to message #285814] |
Wed, 05 December 2007 14:10  |
TLegend33
Messages: 203
Registered: March 2005
|
Senior Member |
|
|
Is this what you're talking about?
create or replace PROCEDURE test_test_analyze_sp as
BEGIN
dbms_output.put_line('BEGIN PROCEDURE ' || sysdate);
EXECUTE IMMEDIATE 'CREATE TABLE test_analyze as select * from temp_test_analyze';
dbms_output.put_line('test_analyze created '|| sysdate);
EXECUTE IMMEDIATE 'exec sys.dbms_stats.gather_table_stats(''DB1'',''TEST_ANALYZE'',estimate_percent=>5, cascade=>TRUE);
dbms_output.put_line('test_analyze analyzed '|| sysdate);
Unfortunately, I get the same error:
SQL> @test_test_analyze_sp.sql
Procedure created.
SQL> show errors
No errors.
SQL> exec test_test_analyze_sp;
BEGIN test_test_analyze_sp; END;
*
ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at "DB1.TEST_TEST_ANALYZE_SP", line 4
ORA-06512: at line 1 |
|
|
|
|
|
|
|
|
|
|
|
| Re: dbms_stats.gather_table_stats in stored procedure [message #286148 is a reply to message #286142] |
Thu, 06 December 2007 10:43 |
TLegend33
Messages: 203
Registered: March 2005
|
Senior Member |
|
|
|
Ok, wrong choice of words. It's not a temporary table, but rather a staging table. This table contains updated records and recreating it was part of the initial process. I'm thinking of replacing the table recreate with a materialized view, however I'm concerned that the table may be too big at 20 million+ rows.
|
|
|
|
|
|
| Re: dbms_stats.gather_table_stats in stored procedure [message #286165 is a reply to message #286141] |
Thu, 06 December 2007 11:24 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
| TLegend33 wrote on Thu, 06 December 2007 17:22 |
Why is creating temporary tables on the fly so silly?
|
* Because all code referring to objects created at runtime nee to be dynamic. Dynamic sql is very error-prone and is hard to maintain.
* Because DDL is expensive
* Because you should define your data-design once and reuse it over and over, not use it once and redesign it over and over.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
