要能顯示Execution Plan 首先一定要先執行utlxplan.sql，一般而言這個檔案會被放在
$ORACLE_HOME/rdbms/admin中，因此可透過sqlplus來執行
SQL>@?/rdbms/admin/utlxplan.sql
Table created.
建立好後，顯示execution plan的方法有：
1、利用autotrace
set autotrace on (explain) (statistics)
on 查詢執行路徑和效能統計
on explain 只產生查詢執行路徑
on statistics 只產生效能統計
off 預設值 不產生報表
ex. select count(*) from dba_users;
SQL> /
COUNT(*)
----------
26
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=23 Card=1 Bytes=
195)
1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS (Cost=23 Card=6698 Bytes=1306110)
3 2 NESTED LOOPS (Cost=23 Card=82 Bytes=14924)
4 3 HASH JOIN (Cost=23 Card=1 Bytes=169)
5 4 HASH JOIN (Cost=21 Card=1 Bytes=156)
6 5 HASH JOIN (Cost=19 Card=1 Bytes=143)
7 6 MERGE JOIN (CARTESIAN) (Cost=2 Card=1 Bytes=78
)
8 7 TABLE ACCESS (BY INDEX ROWID) OF 'PROFILE$'
(Cost=1 Card=1 Bytes=39)
9 8 INDEX (RANGE SCAN) OF 'I_PROFILE' (NON-UNI
QUE) (Cost=1 Card=1)
10 7 SORT (JOIN) (Cost=1 Card=1 Bytes=39)
11 10 TABLE ACCESS (FULL) OF 'PROFILE$' (Cost=1
Card=1 Bytes=39)
12 6 TABLE ACCESS (FULL) OF 'USER$' (Cost=16 Card=8
2 Bytes=5330)
13 5 TABLE ACCESS (FULL) OF 'PROFNAME$' (Cost=1 Card=
82 Bytes=1066)
14 4 TABLE ACCESS (FULL) OF 'USER_ASTATUS_MAP' (Cost=1
Card=82 Bytes=1066)
15 3 TABLE ACCESS (CLUSTER) OF 'TS$'
16 15 INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)
17 2 TABLE ACCESS (CLUSTER) OF 'TS$'
18 17 INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)
2、利用tkprof 來進行sql trace
決定trace的level
a、db level
修改init中，timed_statistics=true
b、session level
alter session set sql_trace=true;
c、對某個session進行trace ，可用dbms_session
exec dbms_session.set_sql_trace_in_session(sid,serial#,true);
產生的trace會在user_dump_dest所指定的目錄中。
利用tkprof程式來分析trace file
tkprof tracefile outfile explain=user/passwd #記得user要和執行trace 的session相同
tkprof ora_21968_t.trc outfile.txt explain=xxx/ooo
TKPROF: Release 8.1.7.4.0 - Production on Mon Feb 12 14:08:02 2007
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Trace file: ora_21968_t.trc
Sort options: default
******************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
******************************************************************************
select KCDE_1,KRMK,KCDE_2,KCNT
from
SRKEYN where 1=1 and KCDE_1='06' and substr(KCDE_2,1,2) <> '/*' order by
KCDE_1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 9 0.00 0.00 0 11 0 177
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 0.00 0.00 0 11 0 177
Misses in library cache during parse: 1
Optimizer goal: FIRST_ROWS
Parsing user id: 24 (MOICAD)
Rows Row Source Operation
------- ---------------------------------------------------
177 INDEX RANGE SCAN (object id 3291)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: FIRST_ROWS
177 INDEX (RANGE SCAN) OF 'RKEYN_IDX1' (UNIQUE)
********************************************************************************
SELECT LPAD(TO_CHAR(SYSDATE,'YYYY')-1911,3,'0')
||TO_CHAR(SYSDATE,'MMDDHH24MISS') from DUAL
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 2 8 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.00 0.00 0 2 8 2
Misses in library cache during parse: 0
Optimizer goal: FIRST_ROWS
Parsing user id: 24 (MOICAD)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL DUAL
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: FIRST_ROWS
SELECT LPAD(TO_CHAR(SYSDATE,'YYYY')-1911,3,'0')
||TO_CHAR(SYSDATE,'MMDDHH24MISS') from DUAL
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 2 8 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.00 0.00 0 2 8 2
Misses in library cache during parse: 0
Optimizer goal: FIRST_ROWS
Parsing user id: 24 (MOICAD)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL DUAL
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: FIRST_ROWS
...
中間就省略不顯示。
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: FIRST_ROWS
0 SORT (UNIQUE)
0 UNION-ALL
0 CONCATENATION
0 FILTER
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'CRSMS'
0 FILTER
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'CRSMS'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'CRSMS_IDX1'
(UNIQUE)
0 CONCATENATION
0 FILTER
0 FILTER
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'CRSMS'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'CRSMS_IDX3'
只列部份內容參考…
。
星期日, 2月 11, 2007
星期三, 2月 07, 2007
How to user perl DBI/DBD calling a stored procedure
metalink 62329.1
PURPOSE
This document provides an example of the method to call a PL/SQL-stored
procedure from a PERL script using the DBI/DBD interface.
SCOPE & APPLICATION
PERL cartridge developers, Web site administrators
RELATED DOCUMENTS
Note 62320.1 - Upgrading DBD/DBI
perldoc DBD::Oracle
This bulletin provides an example of calling a PL/SQL procedure from a PERL
script. It may be necessary to follow the steps in Note 62320.1 to upgrade
the versions of DBD/DBI if you intend to run this script through the PERL
cartridge.
This example is from the PERL DBD:Oracle documentation.
Create the following package in the schema you want to use:
CREATE OR REPLACE PACKAGE plsql_example
IS
PROCEDURE proc_np;
PROCEDURE proc_in (err_code IN NUMBER);
PROCEDURE proc_in_inout(test_num IN NUMBER,
is_odd IN OUT NUMBER);
FUNCTION func_np RETURN VARCHAR2;
END plsql_example;
/
CREATE OR REPLACE PACKAGE BODY plsql_example
IS
PROCEDURE proc_np IS
whoami VARCHAR2(20) := NULL;
BEGIN
SELECT USER INTO whoami FROM DUAL;
END;
PROCEDURE proc_in (err_code IN NUMBER) IS
BEGIN
RAISE_APPLICATION_ERROR(err_code, 'This is a test.');
END;
PROCEDURE proc_in_inout (test_num IN NUMBER,
is_odd IN OUT NUMBER) IS
BEGIN
is_odd := MOD(test_num, 2);
END;
FUNCTION func_np RETURN VARCHAR2 IS
ret_val VARCHAR2(20);
BEGIN
SELECT USER INTO ret_val FROM DUAL;
RETURN ret_val;
END;
END plsql_example;
The PERL script:
----------------- CUT HERE ----------------------
#!/usr/local/bin/perl
use DBI;
my($db, $csr, $ret_val);
# Connect to an Oracle database.
# Modify the following with suitable values for the net alias and your
# db userid and password
$db = DBI->connect('dbi:Oracle:your_db_alias','your_user','your_password')
or die "Unable to connect: $DBI::errstr";
# Set Raise Error so you do not have to check every DBI call.
# See the DBI (perldoc DBI) documentss now if you are not familiar with
# RaiseError.
$db->{RaiseError} = 1;
# Example 1
# This example demonstrates how to call a PL/SQL procedure that takes no
# parameters. This shows you the fundamentals of what you need to execute a
# PL/SQL procedure. Wrap your procedure call in a BEGIN END; block as you
# do with the SQL*Plus execute command.
print "Example 1...";
$csr = $db->prepare(q{
BEGIN
PLSQL_EXAMPLE.PROC_NP;
END;
});
$csr->execute;
print "PLSQL_EXAMPLE.PROC_NP called successfully\n\n";
# Example 2
# For this example, call a procedure that has one IN parameter. Here, use
# bind_param to bind out parameter to the prepared statement, as you can do for
# an INSERT, UPDATE, DELETE, or SELECT statement.
# You can use positional placeholders (for example, :1, :2, etc.) or Open
# Database Connectivity (ODBC) style placeholders (for example, ?). However,
# it is ideal to use Oracle's named placeholders if the DBI drivers support
# them (selected DBI drivers support them, so they are not portable).
my $err_code = -20001;
print "Example 2: Passing a parameter and catching an exception\n";
$csr = $db->prepare(q{
BEGIN
PLSQL_EXAMPLE.PROC_IN(:err_code);
END;
});
$csr->bind_param(":err_code", $err_code);
# PROC_IN will RAISE_APPLICATION_ERROR which will cause the execute to fail.
# Because we set RaiseError, the DBI will die, so we catch that with eval.
eval {
$csr->execute;
};
print "Called PLSQL_EXAMPLE.PROC_IN($err_code): \n",
"\$\@='$@'\n", "errstr=$DBI::errstr \n", "ret_val=$ret_val\n\n";
# Example 3
# Building on the last example, add one IN OUT parameter. Use a placeholder in
# the call to prepare. The difference is that we now call bind_param_inout to
# bind the value to the placeholder.
# The third parameter to bind_param_inout is the maximum size of the variable.
# You normally make this slightly larger than necessary. However, the PERL
# variable will have that much memory assigned to it, even if the actual value
# returned is shorter.
my $test_num = 5;
my $is_odd;
print
"Example 3: Calling PLSQL_EXAMPLE.PROC_IN_INOUT($test_num, \$is_odd)\n";
$csr = $db->prepare(q{
BEGIN
PLSQL_EXAMPLE.PROC_IN_INOUT(:test_num, :is_odd);
END;
});
# The value of $test_num is _copied_ here
$csr->bind_param(":test_num", $test_num);
$csr->bind_param_inout(":is_odd", \$is_odd, 1);
# The execute automagically updateS the value of $is_odd.
$csr->execute;
print "$test_num is ", ($is_odd) ? "odd - ok" : "even - error!", "\n\n";
# Example 4
# Treat the return value of a PL/SQL function the same as you treat a call to
# a function from SQL*Plus. Add a placeholder for the return value and bind it
# with a call to bind_param_inout so we can access its value after execute.
my $whoami = "";
print "Example 4: Calling funtion PLSQL_EXAMPLE.FUNC_NP\n";
$csr = $db->prepare(q{
BEGIN
:whoami := PLSQL_EXAMPLE.FUNC_NP;
END;
});
$csr->bind_param_inout(":whoami", \$whoami, 20);
$csr->execute;
print "Your database user name is $whoami\n\n";
# Close the cursor.
$csr->finish;
# Disconnect from the database.
$db->disconnect;
PURPOSE
This document provides an example of the method to call a PL/SQL-stored
procedure from a PERL script using the DBI/DBD interface.
SCOPE & APPLICATION
PERL cartridge developers, Web site administrators
RELATED DOCUMENTS
Note 62320.1 - Upgrading DBD/DBI
perldoc DBD::Oracle
This bulletin provides an example of calling a PL/SQL procedure from a PERL
script. It may be necessary to follow the steps in Note 62320.1 to upgrade
the versions of DBD/DBI if you intend to run this script through the PERL
cartridge.
This example is from the PERL DBD:Oracle documentation.
Create the following package in the schema you want to use:
CREATE OR REPLACE PACKAGE plsql_example
IS
PROCEDURE proc_np;
PROCEDURE proc_in (err_code IN NUMBER);
PROCEDURE proc_in_inout(test_num IN NUMBER,
is_odd IN OUT NUMBER);
FUNCTION func_np RETURN VARCHAR2;
END plsql_example;
/
CREATE OR REPLACE PACKAGE BODY plsql_example
IS
PROCEDURE proc_np IS
whoami VARCHAR2(20) := NULL;
BEGIN
SELECT USER INTO whoami FROM DUAL;
END;
PROCEDURE proc_in (err_code IN NUMBER) IS
BEGIN
RAISE_APPLICATION_ERROR(err_code, 'This is a test.');
END;
PROCEDURE proc_in_inout (test_num IN NUMBER,
is_odd IN OUT NUMBER) IS
BEGIN
is_odd := MOD(test_num, 2);
END;
FUNCTION func_np RETURN VARCHAR2 IS
ret_val VARCHAR2(20);
BEGIN
SELECT USER INTO ret_val FROM DUAL;
RETURN ret_val;
END;
END plsql_example;
The PERL script:
----------------- CUT HERE ----------------------
#!/usr/local/bin/perl
use DBI;
my($db, $csr, $ret_val);
# Connect to an Oracle database.
# Modify the following with suitable values for the net alias and your
# db userid and password
$db = DBI->connect('dbi:Oracle:your_db_alias','your_user','your_password')
or die "Unable to connect: $DBI::errstr";
# Set Raise Error so you do not have to check every DBI call.
# See the DBI (perldoc DBI) documentss now if you are not familiar with
# RaiseError.
$db->{RaiseError} = 1;
# Example 1
# This example demonstrates how to call a PL/SQL procedure that takes no
# parameters. This shows you the fundamentals of what you need to execute a
# PL/SQL procedure. Wrap your procedure call in a BEGIN END; block as you
# do with the SQL*Plus execute command.
print "Example 1...";
$csr = $db->prepare(q{
BEGIN
PLSQL_EXAMPLE.PROC_NP;
END;
});
$csr->execute;
print "PLSQL_EXAMPLE.PROC_NP called successfully\n\n";
# Example 2
# For this example, call a procedure that has one IN parameter. Here, use
# bind_param to bind out parameter to the prepared statement, as you can do for
# an INSERT, UPDATE, DELETE, or SELECT statement.
# You can use positional placeholders (for example, :1, :2, etc.) or Open
# Database Connectivity (ODBC) style placeholders (for example, ?). However,
# it is ideal to use Oracle's named placeholders if the DBI drivers support
# them (selected DBI drivers support them, so they are not portable).
my $err_code = -20001;
print "Example 2: Passing a parameter and catching an exception\n";
$csr = $db->prepare(q{
BEGIN
PLSQL_EXAMPLE.PROC_IN(:err_code);
END;
});
$csr->bind_param(":err_code", $err_code);
# PROC_IN will RAISE_APPLICATION_ERROR which will cause the execute to fail.
# Because we set RaiseError, the DBI will die, so we catch that with eval.
eval {
$csr->execute;
};
print "Called PLSQL_EXAMPLE.PROC_IN($err_code): \n",
"\$\@='$@'\n", "errstr=$DBI::errstr \n", "ret_val=$ret_val\n\n";
# Example 3
# Building on the last example, add one IN OUT parameter. Use a placeholder in
# the call to prepare. The difference is that we now call bind_param_inout to
# bind the value to the placeholder.
# The third parameter to bind_param_inout is the maximum size of the variable.
# You normally make this slightly larger than necessary. However, the PERL
# variable will have that much memory assigned to it, even if the actual value
# returned is shorter.
my $test_num = 5;
my $is_odd;
"Example 3: Calling PLSQL_EXAMPLE.PROC_IN_INOUT($test_num, \$is_odd)\n";
$csr = $db->prepare(q{
BEGIN
PLSQL_EXAMPLE.PROC_IN_INOUT(:test_num, :is_odd);
END;
});
# The value of $test_num is _copied_ here
$csr->bind_param(":test_num", $test_num);
$csr->bind_param_inout(":is_odd", \$is_odd, 1);
# The execute automagically updateS the value of $is_odd.
$csr->execute;
print "$test_num is ", ($is_odd) ? "odd - ok" : "even - error!", "\n\n";
# Example 4
# Treat the return value of a PL/SQL function the same as you treat a call to
# a function from SQL*Plus. Add a placeholder for the return value and bind it
# with a call to bind_param_inout so we can access its value after execute.
my $whoami = "";
print "Example 4: Calling funtion PLSQL_EXAMPLE.FUNC_NP\n";
$csr = $db->prepare(q{
BEGIN
:whoami := PLSQL_EXAMPLE.FUNC_NP;
END;
});
$csr->bind_param_inout(":whoami", \$whoami, 20);
$csr->execute;
print "Your database user name is $whoami\n\n";
# Close the cursor.
$csr->finish;
# Disconnect from the database.
$db->disconnect;
如何利用vncview將防火牆後端的畫面導到外部電腦上
公司的伺服器，除非有特別指定，否則是無法由外部直接存取的
利用vncview可以簡單到做到這項功能
公司 ==> 外部pc
vncserver ==> vncviewer(listener mode)
vncserver 如何開啟就不多說了
vncviewer就直接由<程式集>==>UltraVnc==>UltraVnc Viewer==>Run UltraVnc Viewer(Listener Mode)來執行
接著在vncserver的icon上，按右鍵出現選項，選項中的"add new client"
將vncviewer的ip打入，即可將畫面post到遠端的電腦上了
ps、vncviewer預設為5500 port，如果要改port的話，請修改捷徑
改為 vncviewer.exe" -listen 8800 <== 8800即為你要改變的port number
利用vncview可以簡單到做到這項功能
公司 ==> 外部pc
vncserver ==> vncviewer(listener mode)
vncserver 如何開啟就不多說了
vncviewer就直接由<程式集>==>UltraVnc==>UltraVnc Viewer==>Run UltraVnc Viewer(Listener Mode)來執行
接著在vncserver的icon上，按右鍵出現選項，選項中的"add new client"
將vncviewer的ip打入，即可將畫面post到遠端的電腦上了
ps、vncviewer預設為5500 port，如果要改port的話，請修改捷徑
改為 vncviewer.exe" -listen 8800 <== 8800即為你要改變的port number
訂閱：
文章 (Atom)