| | |
| | |
安装好Oracle数据库和客户端后, 服务器端用lsnrctl start启动监听程序, 在客户端用
net config 配置,连接数据库, 最后生成tnsnames.ora文件， 格式如下(这是管理两个节点的
#C:oracleora92NETWORKADMIN nsnames.ora
servicename =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.120.1.70)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = test)
)
)
TEST_2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.120.1.71)(PORT = 1526))
)
(CONNECT_DATA =
(SERVICE_NAME = myora)
)
)
可以用tnsping servicename来测试连接然后sqlplus username/passwd@servicename 连
常见问题：
<> ORA-12537: TNS: 连接已关闭
A:监听程序没有启动， 运行lsnrctl start命令
<>ORA-12545: 因目标主机或对象不存在，连接失败
A: 检查tnsnames.ora文件配置， 主机名和端口是否正确, 监听程序是否启动.
A: 检查tnsnames.ora文件配置,主机名和端口是否正确, 监听程序是否启动. SID是否正
确,可以用tnsping 检测
A:这个原因的问题很多， 一般应检查网络状况， 或者系统参数的配置
具体见： http://www.chinaunix.net/cgi-bin/bbs/topic.cgi?forum=8&
topic=393&show=2340
A: select * from table_name where rownum<=1000;
select * from table_name where rownum<=2000 minus select * from table_name where rownum<=1000;
select * from table_name where rownum<=3000 minus select * from table_name where rownum<=2000;
<>怎样能够查到数据库的名字?
A: select value from v$parameter where upper(name) like '%DB_NAME%'
A: 1. 打开审计功能, 设置初始化文件: AUDIT_TRAIL = true
2. 重新启动instance.
3. 审计表：
AUDIT INSERT,SELECT,DELETE,UPDATE on TableName by ACCESS WHENEVER SUCCESSFUL
4. 得到详细信息：
SELECT OBJ_NAME,ACTION_NAME ,to_char(timestamp,'dd/mm/yyyy , HH:MM:SS') from
sys.dba_audit_object.
A; select table_name from dba_tables where tablespace_name='xxx';
$ sqlplus
exec(): 0509-036 Cannot load program sqlplus because of the following errors:
0509-130 Symbol resolution failed for sqlplus because:
0509-136 Symbol pw_post (number 272) is not exported from dependent module
0509-136 Symbol pw_wait (number 273) is not exported from dependent module
0509-136 Symbol pw_config (number 274) is not exported from dependent module
0509-136 Symbol aix_ora_pw_version3_required (number 275) is not exported
0509-192 Examine .loader section symbols with the
'dump -Tv' command.
A: 重新 /etc/loadext -l /etc/pw-syscall (reload)
可能是 Oracle Kernel Extension for aix 在服务器重启动的时候没 load
SQLPLUS的AutoTrace是分析SQL的执行计划，执行效率的一个非常简单方便的工具，在绝
这里，我们试图归纳一些常见的问题，并进行一定的分析。
1。如何设置和使用AUTOTRACE
SQL> connect / as sysdba
SQL> @?/rdbms/admin/utlxplan.sql
Table created.
SQL> create public synonym plan_table for plan_table;
Synonym created.
SQL> grant select,update,insert,delete on plan_table to public;
Grant succeeded.
SQL> @?/sqlplus/admin/plustrce.sql
SQL>grant plustrace to public.
2. 理解和使用AutoTrace
对于SQL 调整，使用Autotrace是最简单的方法了，我们只需要做： SQL>SET AUTOTRACE ON
我们就可以看到我们SQL的执行计划，执行成本（PHYSICAL READ/CONSISTENT READ...)
加上SET Timing On或者Set Time On，我们可以得到很多我们需要的数据。
NVL(TITLE,')
--------------------------------------------------
阎王令
Elapsed: 00:00:00.00 SQL> set autotrace on
SQL> /
NVL(TITLE,')
--------------------------------------------------
阎王令
Elapsed: 00:00:00.71
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=32)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'PUNISHINFO_CS' (Cost=2 C
ard=1 Bytes=32)
2 1 INDEX (UNIQUE SCAN) OF 'SYS_C001084' (UNIQUE) (Cost=1 Ca
rd=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
376 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
3.关于使用Autotrace的一些常见疑问：
a.比如我上面的例子，我不用Autotrace，我的时间小于0.01S，但是用了Autotrace，我
不注意的人往往会认为，或者没有测试不用Autotrace时候的情况，往往会忽视这个数字
实际上，这个0.7S，是花在Autotrace里面的时间。由于Autotrace需要记录你的SQL执行
的成本，这个本身是往数据库里面读取和写入一定的数据的，需要一定的时间。当你的SQL执
行时间足够短的时候，这个由于Autotrace带来的时间就变成非常可观的了。我们就需要通过
不用Autotrace的时间，和使用Autotrace的执行成本来结合比较。
我们通过结合Autotrace和Tkprof/SQLTRACE，很容易知道，AUtotrace就近作了什么：
select nvl(title,' ') from punishinfo_cs where ci_id=45672 call count cpu
elapsed disk query current rows ------- ------ -------- ---------- ----------
---------- ---------- ---------- Parse 1 0.00 0.01 0 0 0 0 Execute 1 0.00 0.00 0
0 0 0 Fetch 2 0.00 0.00 0 3 0 1 ------- ------ -------- ---------- ----------
---------- ---------- ---------- total 4 0.00 0.01 0 3 0 1 DELETE FROM PLAN_TABLE
WHERE STATEMENT_ID=:1 EXPLAIN PLAN SET STATEMENT_ID='PLUS185025' FOR select
nvl(title,' ') from punishinfo_cs where ci_id=45672 insert into plan_table
(statement_id, timestamp, operation, options, object_node, object_owner,
object_name, object_instance, object_type, search_columns, id, parent_id,
position, other,optimizer, cost, cardinality, bytes, other_tag, partition_start,
partition_stop, partition_id, distribution ) values (:1,SYSDATE,:2,:
3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19, :20,:21,:22) SELECT
ID ID_PLUS_EXP,PARENT_ID PARENT_ID_PLUS_EXP,LPAD(' ',2*(LEVEL-1)) ||
OPERATION||DECODE(OTHER_TAG,NULL,','*')||DECODE(OPTIONS,NULL,',' (
'||OPTIONS||')')||DECODE(OBJECT_NAME,NULL,',' OF ''||OBJECT_NAME||'')
||DECODE(OBJECT_TYPE,NULL,',' ('||OBJECT_TYPE||')')||DECODE(ID,0, D
ECODE(OPTIMIZER,NULL,',' Optimizer='||OPTIMIZER))||DECODE(COST,NULL,','
(Cost='||COST||DECODE(CARDINALITY,NULL,',' Card='||CARDINALITY) ||D
ECODE(BYTES,NULL,',' Bytes='||BYTES)||')') PLAN_PLUS_EXP,OBJECT_NODE
OBJECT_NODE_PLUS_EXP FROM PLAN_TABLE START WITH ID=0 AND STATEMENT_ID=:1 CONNECT
BY PRIOR ID=PARENT_ID AND STATEMENT_ID=:1 ORDER BY ID,POSITION SELECT ID
ID_PLUS_EXP,OTHER_TAG OTHER_TAG_PLUS_EXP,OTHER OTHER_PLUS_EXP FROM PLAN_TABLE
WHERE STATEMENT_ID=:1 AND OTHER_TAG IS NOT NULL ORDER BY ID
我们看到，由于我们的Autotrace，简简单单的一句话，实际上oracle 做了那么多的事
3.关于使用Autotrace的一些常见疑问(2)
B。什么叫做Recursive Call？
为什么Recursive Call那么多？
首先我们要明白，什么是Recursive Call，为什么需要Recursive Call：
下面是我摘自Oracle 9.2的Document的一段话（oracle 9.2 performance and tuning
Understanding Recursive Calls
Sometimes, in order to execute a SQL statement issued by a user, Oracle must
issue additional statements. Such statements are called recursive calls or
recursive SQL statements. For example, if you insert a row into a table that does
not have enough space to hold that row, then Oracle makes recursive calls to
allocate the space dynamically. Recursive calls are also generated when data
dictionary information is not available in the data dictionary cache and must be
If recursive calls occur while the SQL trace facility is enabled, then TKPROF
produces statistics for the recursive SQL statements and marks them clearly as
recursive SQL statements in the output file. You can suppress the listing of
Oracle internal recursive calls (for example, space management) in the output
file by setting the SYS command-line parameter to NO. The statistics for a
recursive SQL statement are included in the listing for that statement, not in
the listing for the SQL statement that caused the recursive call. So, when you
are calculating the total resources required to process a SQL statement, consider
the statistics for that statement as well as those for recursive calls caused by
有时候我们会看到一些看上去很奇怪的问题，比如，有时候，我们的Autotrace，会显示
，Select语句也会有Redo 产生。这里可能有两种情况：
1。Delayed Block Cleanout：
比如下面这个例子：
SQL>DELETE FROM T WHERE ROWNUM<100;
SQL>DELETE FROM T WHERE ROWNUM<100;
SQL>DELETE FROM T WHERE ROWNUM<100;
SQL>DELETE FROM T WHERE ROWNUM<100;
COMMIT;
SQL> select count(*) from t;
COUNT(*)
----------
25606
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T'
Statistics
----------------------------------------------------------
0 recursive calls
12 db block gets
326 consistent gets
0 physical reads
360 redo size
369 bytes sent via SQL*Net to client
426 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
第二种情况是：
用户用来排序的临时表空间不是真正的临时表空间：
例子：
SQL> L
1* ALTER USER TEST TEMPORARY TABLESPACE SYSTEM
User altered.
SQL> SELECT * FROM T ORDER BY 1,2,3,4,5,6;
25606 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (ORDER BY)
2 1 TABLE ACCESS (FULL) OF 'T'
Statistics
----------------------------------------------------------
1382 recursive calls
286 db block gets
740 consistent gets
809 physical reads
28264 redo size
1239304 bytes sent via SQL*Net to client
189903 bytes received via SQL*Net from client
1709 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
25606 rows processed
我们把用户的临时表空间重新改成Locally MANAGED TEMPORARY TABLESPACE:
User altered.
SQL> CONN TEST/TEST
Connected. SQL> SET AUTOTRACE TRACEONLY;
SQL> SELECT * FROM T ORDER BY 1,2,3,4,5,6;
25606 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (ORDER BY)
2 1 TABLE ACCESS (FULL) OF 'T'
Statistics
----------------------------------------------------------
0 recursive calls
53 db block gets
320 consistent gets
808 physical reads
0 redo size
1239304 bytes sent via SQL*Net to client
189903 bytes received via SQL*Net from client
1709 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
25606 rows processed
还有一个挺难理解的现象：
SQL> conn internal
Connected. SQL> set autotrace traceonly;
SQL> select * from test.t ORDER BY 1,2,3,4,5,6;
25606 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (ORDER BY)
2 1 TABLE ACCESS (FULL) OF 'T'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
25606 rows processed
同样的语句，没有任何Trace的值。这里，是因为我用Internal用户的连接，或者说，
SYSDBA权限的连接。用这个用户连接，没有Trace的结果的。
这一点，特别感谢Oldwain老哥，我当时也是想了很久也没有想出来。
| |
|
|
| |
| |
|
