安裝好Oracle數(shù)據(jù)庫和客戶端后, 服務(wù)器端用lsnrctl start啟動監(jiān)聽程序, 在客戶端用net config 配置，
連接數(shù)據(jù)庫, 最后生成tnsnames.ora文件， 格式如下(這是管理兩個節(jié)點的配置).
#C:\oracle\ora92\NETWORK\ADMIN\tnsnames.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: 連接已關(guān)閉
A:監(jiān)聽程序沒有啟動， 運行l(wèi)snrctl start命令
<>;ORA-12545: 因目標主機或?qū)ο蟛淮嬖冢B接失敗
A: 檢查tnsnames.ora文件配置， 主機名和端口是否正確, 監(jiān)聽程序是否啟動.
<>;ORA-12560: TNS:protocol adapter error
A: 檢查tnsnames.ora文件配置， 主機名和端口是否正確, 監(jiān)聽程序是否啟動. SID是否正確，
可以用tnsping 檢測
<>;ORA-03113 ：通信通道的文件結(jié)束
A:這個原因的問題很多， 一般應(yīng)檢查網(wǎng)絡(luò)狀況， 或者系統(tǒng)參數(shù)的配置
具體見： http://www./cgi-bin/bbs/topic.cgi?forum=8&topic=393&show=2340
<>; select 查詢時,有2000條記錄符合條件,如何先取出符合條件前1000條,然后再取出符合條件的后1000條?
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;
<>;怎樣能夠查到數(shù)據(jù)庫的名字?
A: select value from v$parameter where upper(name) like ‘%DB_NAME%‘
<>;怎樣得到一個表的最后更新時間?
A: 1. 打開審計功能, 設(shè)置初始化文件: 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 /unix.
0509-136 Symbol pw_wait (number 273) is not exported from
dependent module /unix.
0509-136 Symbol pw_config (number 274) is not exported from
dependent module /unix.
0509-136 Symbol aix_ora_pw_version3_required (number 275) is not exported from
dependent module /unix.
0509-192 Examine .loader section symbols with the
‘dump -Tv‘ command.
A: 重新 /etc/loadext -l /etc/pw-syscall (reload)
可能是 Oracle Kernel Extension for aix 在服務(wù)器重啟動的時候沒 load
大家補充
hzhrh 回復(fù)于：2002-12-16 17:10:39
我覺得這樣FAQ有當(dāng)然好，但怎樣找問題是根本，用Oerr Ora ErrNo，其中ErrNo是錯誤號。這樣每個人都可以知道，問題的所在及知道解決的方法。
yikaikai 回復(fù)于：2002-12-16 17:15:54
但那只是Unix下的啊， Windows下有嗎？
freebob 回復(fù)于：2002-12-16 17:40:19
可以把論壇里大家問的，已經(jīng)解決的，有代表性的，作為本FAQ,以便后來人參考
也是好主意
chaoping 回復(fù)于：2002-12-16 21:45:24
I think i write some useful articles about oracle dbms.
Maybe they are also frequently asked.
Hope it useful to you:)
I post some of them here:
SQLPLUS的AutoTrace是分析SQL的執(zhí)行計劃，執(zhí)行效率的一個非常簡單方便的工具，在絕大多數(shù)情況下，也是非常有用的工具。
這里，我們試圖歸納一些常見的問題，并進行一定的分析。
1。如何設(shè)置和使用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 調(diào)整，使用Autotrace是最簡單的方法了，我們只需要做：
SQL>;SET AUTOTRACE ON
我們就可以看到我們SQL的執(zhí)行計劃，執(zhí)行成本（PHYSICAL READ/CONSISTENT READ...)
加上SET Timing On或者Set Time On，我們可以得到很多我們需要的數(shù)據(jù)。
SQL>; select nvl(title,‘ ‘) from punishinfo_cs where ci_id=45672 ;
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.關(guān)于使用Autotrace的一些常見疑問：
a.比如我上面的例子，我不用Autotrace，我的時間小于0.01S，但是用了Autotrace，我的執(zhí)行時間變成了0.71S.
不注意的人往往會認為，或者沒有測試不用Autotrace時候的情況，往往會忽視這個數(shù)字，認為時間就是0.71S.
實際上，這個0.7S，是花在Autotrace里面的時間。由于Autotrace需要記錄你的SQL執(zhí)行的成本，這個本身是往數(shù)據(jù)庫里面讀取和寫入一定的數(shù)據(jù)的，需要一定的時間。當(dāng)你的SQL執(zhí)行時間足夠短的時候，這個由于Autotrace帶來的時間就變成非?？捎^的了。我們就需要通過不用Autotrace的時間，和使用Autotrace的執(zhí)行成本來結(jié)合比較。
我們通過結(jié)合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, DECODE(OPTIMIZER,NULL,‘‘,‘ Optimizer=‘||OPTIMIZER))||DECODE(COST,NULL,‘‘,‘ (Cost=‘||COST||DECODE(CARDINALITY,NULL,‘‘,‘ Card=‘||CARDINALITY) ||DECODE(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.關(guān)于使用Autotrace的一些常見疑問(2)
B。什么叫做Recursive Call？
為什么Recursive Call那么多？
首先我們要明白，什么是Recursive Call，為什么需要Recursive Call：
下面是我摘自O(shè)racle 9.2的Document的一段話（oracle 9.2 performance and tuning reference)
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 retrieved from disk.
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 that statement.
有時候我們會看到一些看上去很奇怪的問題，比如，有時候，我們的Autotrace，會顯示，Select語句也會有Redo 產(chǎn)生。
這里可能有兩種情況：
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
SQL>; /
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:
SQL>; ALTER USER TEST TEMPORARY TABLESPACE TEMP;
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
還有一個挺難理解的現(xiàn)象：
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權(quán)限的連接。
用這個用戶連接，沒有Trace的結(jié)果的。
這一點，特別感謝Oldwain老哥，我當(dāng)時也是想了很久也沒有想出來。
chaoping 回復(fù)于：2002-12-16 21:46:47
If you have further questions on this topic, please post your question to my mailbox or :
http://www./bin/ut/topic_show.cgi?id=229&h=1&bpg=1&age=0
chaoping 回復(fù)于：2002-12-16 21:47:20
This topic is about Oracle process memory utilization in linux:
前面brain 講述了如何查看Solaris下面的Oracle的連接所使用的內(nèi)存，我們來看看Linux下，這個問題如何分析。
Solaris用來查看內(nèi)存占用的命令主要是pmap , 位于
$ which pmap
/usr/proc/bin/pmap
我們通過Pmap可以看到每個進程的heap area, stack area, shared memory, etc.
Linux平臺沒有Pmap命令，但是Linux提供了強大的Proc文件系統(tǒng)。
我們通過Proc文件系統(tǒng)，可以看到很多的秘密。
--------------------------------------------------------------------------------
為了一個共同的夢
China Oracle User Group
chao_ping
版主
發(fā)帖: 301
于2002-12-03 13:52
--------------------------------------------------------------------------------
每對應(yīng)一個進程，在Linux Proc文件系統(tǒng)下面，都會有一個新的目錄生成，目錄名字就是相應(yīng)的進程號。
我們檢查當(dāng)前狀態(tài)下這個進程的資源占用：
[oracle@ish3 8229]$ cat status
Name: oracle
State: S (sleeping)
Pid: 8229
PPid: 8228
TracerPid: 0
Uid: 504 504 504 504
Gid: 505 505 505 505
FDSize: 1024
Groups: 505
VmSize: 821544 kB
VmLck: 0 kB
VmRSS: 40224 kB
VmData: 428 kB
VmStk: 52 kB
VmExe: 22752 kB
VmLib: 5024 kB
SigPnd: 0000000000000000
SigBlk: 0000000080000000
SigIgn: 0000000006005203
SigCgt: 0000000389c02cfc
CapInh: 0000000000000000
CapPrm: 0000000000000000
CapEff: 0000000000000000
SQL>; @myspid
SPID
---------
8229
SQL>; host ps -aux|grep 8229
oracle 8229 0.8 2.4 831612 51456 ? S 11:23 0:16 oraclecatalog (DE
oracle 8712 0.0 0.0 1628 616 pts/2 S 11:55 0:00 grep 8229
SQL>; @whoisit
Enter value for spid: 8229
old 3: select addr from v$process where spid in(&spid))
new 3: select addr from v$process where spid in(8229))
SID SERIAL# USERNAME OSUSER MACHINE PROGRAM PROCESS TO_CHAR(LOGON_TIME,
---------- ---------- ------------------------------ ------------------------------ ------------------------------ ---------------------------------------- --------- -------------------
36 21957 USER oracle ish3 sqlplus@ish3 (TNS V1-V3) 8228 2002/12/03 11:23:17
SQL>; @sesstat
Enter value for sid: 36
old 3: where a.sid=&sid
new 3: where a.sid=36
VALUE NAME
---------- --------------------------------------------------
48136 session uga memory
80192 session uga memory max
170224 session pga memory
170224 session pga memory max
34 sorts (memory)
chao_ping 編輯于 2002-12-03 13:54
--------------------------------------------------------------------------------
為了一個共同的夢
China Oracle User Group
chao_ping
版主
發(fā)帖: 301
于2002-12-03 13:58
--------------------------------------------------------------------------------
我現(xiàn)在做一個小測試，
我修改我的sort_area_size,到10M（當(dāng)前默認是64k). 然后我做一個大的排序。(test.sql: select * from some_big_table order by 1,2,3,4,5,6).
SQL>;
1* alter session set sort_area_size=10000000
SQL>; /
Session altered.
Elapsed: 00:00:00.06
SQL>; @test.sql
－－這里做測試，用完所有的sort_area_size.
SQL>; set term on
SQL>; @sesstat
Enter value for sid: 36
old 3: where a.sid=&sid
new 3: where a.sid=36
VALUE NAME
---------- --------------------------------------------------
54828 session uga memory
121176 session uga memory max
10496600 session pga memory
10496600 session pga memory max
43 sorts (memory)
我們看到，PGA的占用馬上就達到了10M。
我們再檢查相應(yīng)的/proc文件系統(tǒng)下面的oracle進程的狀態(tài)：
[oracle@ish3 8229]$ cat status
Name: oracle
State: D (disk sleep)
Pid: 8229
PPid: 8228
TracerPid: 0
Uid: 504 504 504 504
Gid: 505 505 505 505
FDSize: 1024
Groups: 505
VmSize: 831612 kB
VmLck: 0 kB
VmRSS: 51236 kB
VmData: 10496 kB
VmStk: 52 kB
VmExe: 22752 kB
VmLib: 5024 kB
SigPnd: 0000000000000000
SigBlk: 0000000080000000
SigIgn: 0000000006005203
SigCgt: 0000000389c02cfc
CapInh: 0000000000000000
CapPrm: 0000000000000000
CapEff: 0000000000000000
我們看到，從v$sesstat和/proc，這個進程的使用資源狀態(tài)都明顯變化。
chao_ping 編輯于 2002-12-03 13:59
--------------------------------------------------------------------------------
為了一個共同的夢
China Oracle User Group
chao_ping
版主
發(fā)帖: 301
于2002-12-03 14:02
--------------------------------------------------------------------------------
其實，Solaris和Linux本身自帶的ps命令或者Top之類，對于這些使用共享內(nèi)存的進程，無法準確看到這個進程使用了多少資源。
因為這些進程也可以讀寫共享內(nèi)存，ps把共享內(nèi)存（對于Oracle，就是SGA）也包含在里面了。
這樣的話，就無法準確看到某個進程占用了多少內(nèi)存。
我們使用proc文件系統(tǒng)，或者Pmap來準確的看究竟系統(tǒng)使用了多少資源。
對于別的Unix，由于沒有環(huán)境，無法準確用實例來說明，歡迎大家各抒己見，解決這個問題。
|
|
|
|
