Home » SQL & PL/SQL » SQL & PL/SQL » dbms_stats.gather_table_stats in stored procedure (Oracle 10.2, RHEL4)
dbms_stats.gather_table_stats in stored procedure [message #285810] Wed, 05 December 2007 13:58 Go to next message
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 #285814 is a reply to message #285810] Wed, 05 December 2007 14:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ You have to double the ' in a string
2/ It is a silly thing to create table on the fly.

Regards
Michel
Re: dbms_stats.gather_table_stats in stored procedure [message #285815 is a reply to message #285814] Wed, 05 December 2007 14:10 Go to previous messageGo to next message
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 #285816 is a reply to message #285810] Wed, 05 December 2007 14:14 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
3/ EXEC is NOT a PL/SQL command; use BEGIN ... END; instead
4/ The final single quote of the EXECUTE IMMEDIATE command is missing
Re: dbms_stats.gather_table_stats in stored procedure [message #285881 is a reply to message #285816] Thu, 06 December 2007 00:03 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
5/ It is a silly thing to create table on the fly.
6/ It is a silly thing to create table on the fly.
7/ It is a silly thing to create table on the fly.
Re: dbms_stats.gather_table_stats in stored procedure [message #286141 is a reply to message #285881] Thu, 06 December 2007 10:22 Go to previous messageGo to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
Why is creating temporary tables on the fly so silly?
Re: dbms_stats.gather_table_stats in stored procedure [message #286142 is a reply to message #286141] Thu, 06 December 2007 10:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because permanent "temporary" table does not exist in Oracle and is useless.

Regards
Michel
Re: dbms_stats.gather_table_stats in stored procedure [message #286148 is a reply to message #286142] Thu, 06 December 2007 10:43 Go to previous messageGo to next message
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 #286150 is a reply to message #286148] Thu, 06 December 2007 10:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Staging table is almost never useful on Oracle.
If it is, then use a global temporary table and not a permanent table.
So you don't have to create it on the fly.

Regards
Michel
Re: dbms_stats.gather_table_stats in stored procedure [message #286165 is a reply to message #286141] Thu, 06 December 2007 11:24 Go to previous message
Frank
Messages: 7880
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.
Previous Topic: & sign in an SQL statement
Next Topic: Error using dbms_sql package
Goto Forum:
  


Current Time: Wed Dec 07 14:42:18 CST 2016

Total time taken to generate the page: 0.07544 seconds