Hands-On
#1:
Examples of using the dbms_stats package:
To gather statistics for entire database:
SQL> declare
v_time varchar2(50);
begin
-- Display starting time. Make sure set serveroutpu on
select to_char(sysdate(), 'DD-MM-YY HH24:SS:MI') into v_time from dual;
dbms_output.put_line('Started: ' || v_time);
-- gather statistics for entire database.
dbms_stats.gather_database_stats();
-- Display ending time:
select to_char(sysdate(), 'DD-MM-YY HH24:SS:MI') into v_time from dual;
dbms_output.put_line(' Ended: ' || v_time);
end;
/
Example of gather statistics for entire schema (tables and indexes).
SQL> declare
v_time varchar2(50);
begin
-- Display starting date and time.
select to_char(sysdate(), 'DD-MM-YY HH24:SS:MI') into v_time from dual;
dbms_output.put_line('Started: ' || v_time);
-- Gather statistics for a schema
dbms_stats.gather_schema_stats(
ownname => 'FALCON',
method_opt => 'FOR ALL COLUMNS SIZE 1', -
granularity => 'ALL',
options => 'GATHER',
cascade => TRUE);
-- Display end date and time
select to_char(sysdate(), 'DD-MM-YY HH24:SS:MI') into v_time from dual;
dbms_output.put_line(' Ended: ' || v_time);
end;
/
Example of gather statistics for entire schema (tables and indexes).
--Make sure your buffer size is big enough.
SQL> SET SERVEROUTPUT ON SIZE 1000000
Create a PL/SQL
procedure to gather statistics for each schema in the database.
SQL> CREATE OR REPLACE PROCEDURE gather_stats
IS
v_time VARCHAR2(50);
CURSOR c_schema IS
SELECT username FROM dba_users;
BEGIN
FOR v_user IN c_schema LOOP
-- Display Starting Time.
SELECT to_char(sysdate(), 'DD-MM-YY HH24:SS:MI')
INTO v_time FROM dual;
dbms_output.put_line('Started: ' || v_time);
-- Gather statistics for each schema.
dbms_stats.gather_schema_stats(
ownname => v_user.username,
method_opt => 'FOR ALL COLUMNS SIZE 1',
granularity => 'ALL',
options => 'GATHER',
cascade => TRUE);
-- Display ending time.
SELECT to_char(sysdate(), 'DD-MM-YY HH24:SS:MI')
INTO v_time FROM dual;
dbms_output.put_line(' Ended: ' || v_time);
END LOOP;
END gather_stats;
/
You should grant the following object and system privileges to the user (For example: dba1) who
was assigned to run the gather_stats procedure.
SQL> GRANT execute ON gather_stats TO dba1;
SQL> GRANT ANALYZE ANY TO dba1;
Hands-On
#2:
Create
a binding variable and then move your query into it.
SQL>
VARIABLE my_query VARCHAR2(1000)
SQL>
BEGIN
:my_query
:= "SELECT ename FROM iself.emp WHERE empno = 100;"
END;
/
Use
the DBMS_SQLTUNE package to create a tuning task by calling the
CREATE_TUNING_TASK function. This procedure creates an advisor task and sets its
corresponding parameters according to the user-provided input arguments. To
execute this you need one more binding variable to keep your task name.
SQL>
VARIABLE my_task VARCHAR2(100)
SQL>
BEGIN
:my_task
:= DBMS_SQLTUNE.create_tuning_task (
SQL_TEXT =>
:my_query,
BIND_LIST
=>
SQL_BINDS(anydata.ConvertNumber(100)),
USER_NAME =>
"ISELF",
SCOPE
=>
"COMPREHENSIVE",
TIME_LIMIT =>
60,
TASK_NAME =>
"my_tuning_task",
DESCRIPTION =>
"Query on EMP table "");
END;
/
Invoke
the EXECUTE_TUNING_TASK procedure to start the tuning process.
SQL>
BEGIN
DBMS_SQLTUNE.execute_tuning_task
(TASK_NAME=>:my_task);
END;
Call
the REPORT_TUNING_TASK function to visualize the tuning results.
SQL>
SQL> SELECT DBMS_SQLTUNE.report_tuning_task
(TASK_NAME=>:my_task)
FROM dual;
When
a SQL Profile is recommended by the SQL Tuning Advisor, then create the SQL
Profile by calling the ACCEPT_SQL_PROFILE function, which stores it in the data
dictionary. You should have the CREATE ANY SQL PROFILE privilege.
SQL>
VARIABLE my_profile VARCHAR2(1000)
SQL>
BEGIN
:my_profile
:= DBMS_SQLTUNE.accept_sql_profile
(TASK_NAME =>
"my_tuning_task");
END;
/
SQL>
SELECT :my_profile FROM dual;
Hands-On
# 2:
Let
see the advantage of creating mview.
Connect
as the ISELF user.
SQL>
CONNECT iself/schooling
Create
a materialized view called my_mview using the EMP table. This view summarize
department total salaries.
SQL>
CREATE MATERIALIZED VIEW my_mview
BUILD
IMMEDIATE
ENABLE
QUERY REWRITE
AS
SELECT deptno, SUM(sal) as salary
FROM EMP
GROUP BY deptno;
Gather
statistics against the new materialized view.
SQL>
BEGIN
DBMS_STATS.gather_table_stats(USER,
"MY_MVIEW");
END;
/
Query
from new materialized view.
SQL>
SELECT * FROM my_mview;
Execute
EXPLAIN PLAN against the query in the previous step.
SQL>
EXPLAIN PLAN FOR
SELECT
deptno, SUM(sal) as salary
FROM
EMP
GROUP
BY deptno;
SQL>
SELECT * FROM (dbms_xplan.display);
Now,
use the DBMS_MVIEW.EXPLAIN_REWRITE procedure against the query and the MY_MVIEW
materialized view, then query the REWRITE_TABLE. If you don"t have
REWRITE_TABLE, then run the utlxrw.sql script.
SQL>
@$ORACLE_HOME/rdbms/admin/utlxrw.sql
SQL>
BEGIN
DBMS_MVIEW.explain_rewrite
(
"SELECT
deptno, SUM(sal) as salary FROM EMP GROUP BY deptno",
"ISELF.MY_MVIEW",
"This is my explain_rewrite practice"");
END;
/
SQL>
COL message FORMAT a35
SQL>
SELECT message, original_cost, rewritten_cost
FROM
rewrite_table;