有好长一段时间没碰数据库,一直以为自己的状态应该依旧,可是,当真正面临实战的时候,才发现头脑里只有概念了.现在将当初使用一些工具的备忘录整理一下.也以此告诫自己要踏实,勤学,切勿浮躁,将心态归零

一:使用explain plan

1:条件:对要进行explain plan的用户建立plan_table

       如要对user nphtl下的update,delete,insert,selet 进行explain plan

       connect nphtl/nphtl

       sql@%ORACLE_HOME%RDBMSadminutlxplan.sql

2:对语句进行explain plan的例子

       explain plan for select * FROM table;

3:查看执行计划:

       sql@%ORACLE_HOME%RDBMSadminutlxpls.sql或utlxplp.sql

二:使用tkprof

1:条件:init.ora中的4个参数:

    (1)设置user_dump_dest的路径

  (2)设置time_statistics=true 也可用alter system,

    (3)max_dump_file_size设得较大或用alter system设置为unlimited.

    (4)alter session set sql_trace=true

2:sql@%ORACLE_HOME%RDBMSadminutltfprf.sql

3:在操作系统命令状态执行:tkpro %user_dump_dest路径%ora02336.trc output.prf explain=nphtl/nphtl(其它参数见tkpro命令)

  查看output.prf可以看到执行的情况

rem -----------------------------------------------------------------------
rem Filename: x_plan.sql
rem Purpose: Explain SQL Execution plan
rem -----------------------------------------------------------------------

DELETE FROM plan_table WHERE statement_id = 'XXX';
COMMIT;

EXPLAIN PLAN
SET STATEMENT_ID = 'XXX'
FOR
select * from dual -----

-- This script analyzes a given table and all underlying indexes
-- within the executing schema.
-- Create this stored procedure under the executing schema (because it
-- uses user_indexes).
-- This script uses ESTIMATE STATISTICS SAMPLE 40 PERCENT which
-- is appropriate for most situations.
-- This script is useful right after creating or importing a large table
-- with many associated indexes where statistics either don't exist or are out of date.
-- Sample: connect scott/password
-- exec p_analyze_table_indexes(EMP)
-- Tested on Oracle Database Release 9.2.

create or replace procedure
p_analyze_table_indexes(a_table_name IN
user_indexes.TABLE_NAME%type)
as
v_cursorid integer;
status integer;
cursor c_user_indexes is
select TABLE_NAME,INDEX_NAME
from user_indexes
where status='VALID'
and TABLE_NAME=upper(a_table_name);
v_user_indexes c_user_indexes%rowtype;
begin
open c_user_indexes;
v_cursorid:=dbms_sql.open_cursor;
fetch c_user_indexes into v_user_indexes;
while ( c_user_indexes%found ) loop
dbms_sql.parse(v_cursorid,
'analyze index
'||v_user_indexes.INDEX_NAME||' ESTIMATE STATISTICS SAMPLE 40 PERCENT
',dbms_sql.native);
status:=dbms_sql.execute(v_cursorid);
fetch c_user_indexes into
v_user_indexes;
end loop;
dbms_sql.parse(v_cursorid,
'analyze table '||v_user_indexes.TABLE_NAME||'
ESTIMATE STATISTICS SAMPLE 40 PERCENT
',dbms_sql.native);
status:=dbms_sql.execute(v_cursorid);
close c_user_indexes;
dbms_sql.close_cursor(v_cursorid);
exception
when others then
dbms_output.put_line('Error...... ');
dbms_sql.close_cursor(v_cursorid);
raise;
end p_analyze_table_indexes;
/

RDBMS version, do the following:
Oracle 8.1.6:
on Unix:
SQL> connect / as sysdba
SQL> @?/rdbms/admin/statscre
on NT:
SQL> connect / as sysdba
SQL> @%ORACLE_HOME%rdbmsadminstatscre
on VMS
SQL> connect / as sysdba
SQL> @ora_rdbms_admin:statscre
Oracle8i 8.1.7 and Oracle9i 9.x
on Unix:
SQL> connect / as sysdba
SQL> @?/rdbms/admin/spcreate
on NT:
SQL> connect / as sysdba
SQL> @%ORACLE_HOME%rdbmsadminspcreate
on VMS
SQL> connect / as sysdba
SQL> @ora_rdbms_admin:spcreate
Batch mode installation
=======================
To install in batch mode, you must assign values to the SQL*Plus
variables which specify the default and temporary tablespaces before
running spcreate.
The variables are:
default_tablespace -> for the default tablespace
temporary_tablespace -> for the temporary tablespace
e.g.
on Unix:
SQL> connect / as sysdba
SQL> define default_tablespace='tools'
SQL> define temporary_tablespace='temp'
SQL> @?/rdbms/admin/spcreate
spcreate will no longer prompt for the above information.
- Log files created during installation
The StatsPack installation script runs 3 other scripts - you do not need
to run these - the scripts are called automatically:
Oracle8i 8.1.6
1. statscusr -> creates the user and grants privileges
2. statsctab -> creates the tables
3. statspack -> creates the package
Oracle8i 8.1.7 or Oracle9i 9.x
1. spcusr -> creates the user and grants privileges
2. spctab -> creates the tables
3. spcpkg -> creates the package
The installation script will generate spooled output file based on the name of
the script being run and end with a 'lis' extension. Check each of the three
output files produced by the installation to ensure no errors were encountered,
before continuing on to the next step. For example, on Oracle8i 8.1.6, a
output file called statcusr.lis will be created. Under Oracle8i 8.1.7 or
Oracle9i 9.x, a output file spcusr.lis will be created.
- Errors found during installation process
If the scripts were incorrectly run while in SVRMGRL, an ORA-1012 error
indicating that the session is not logged in or a PLS-00201 error indicating
that stats$statspack_parameter must be declared may be generated. To correct
this problem, first use SQL*Plus to remove StatsPack, and attempt installation
using the above steps:
Oracle 8.1.6:
on Unix:
SQL> connect / as sysdba
SQL> @?/rdbms/admin/statsdrp
on NT:
SQL> connect / as sysdba
SQL> @%ORACLE_HOME%rdbmsadminstatsdrp
on VMS
SQL> connect / as sysdba
SQL> @ora_rdbms_admin:statsdrp
Oracle8i 8.1.7 and Oracle9i 9.x
on Unix:
SQL> connect / as sysdba
SQL> @?/rdbms/admin/spdrop
on NT:
SQL> connect / as sysdba
SQL> @%ORACLE_HOME%rdbmsadminspdrop
on VMS
SQL> connect / as sysdba
SQL> @ora_rdbms_admin:spdrop
- Batch mode installation
There are two ways to install Statspack - interactively (as shown above),
or in 'batch' mode (as shown below). Batch mode is useful when you do not
wish to be prompted for the PERFSTAT user's default and temporary tablespaces.
To install in batch mode, you must assign values to the SQL*Plus
variables which specify the default and temporary tablespaces before running
the StatsPack installation script.
The variables are:
default_tablespace -> for the default tablespace
temporary_tablespace -> for the temporary tablespace
e.g.
on Unix:
SQL> connect / as sysdba
SQL> define default_tablespace='tools'
SQL> define temporary_tablespace='temp'
Oracle8i 8.1.6
SQL> @?/rdbms/admin/statscre
Oracle8i 8.1.7 or Oracle9i 9.x
SQL> @?/rdbms/admin/spcreate

看不懂