Downloaded 879 times
Uploaded bySidney Chen
Oracle Basics and Architecture
Oracle Architecture document discusses: 1. The cost of an Oracle Enterprise Edition license is $47,500 per processor. 2. It provides an overview of key Oracle components like the instance, database, listener and cost based optimizer. 3. It demonstrates how to start an Oracle instance, check active processes, mount and open a database, and query it locally and remotely after starting the listener.
Oracle Basics and Architecture
- 1.
- 2. Cost: Enterprise EditionUS$47,500.00 /Processor2
- 3. AgendaWhat is OracleinstanceDatabaseListenerCostBase Optimizer3
- 4. 4The more youunderstandThe less you need to memorizemoreMemorizelessShallowIn-depthUnderstanding
- 5.
- 6. Before startupConnected toan idle instance.sys@CS11GR2> !ps -ef | grepcs11gR2 | grep -v grepsys@CS11GR2> !ipcs -m------ Shared Memory Segments --------key shmid owner perms bytes nattch status6
- 7. instance startupsys@CS11GR2> startupnomountORACLE instance started.Total System Global Area 535662592 bytesFixed Size 1337720 bytesVariable Size 322963080 bytesDatabase Buffers 205520896 bytesRedo Buffers 5840896 bytes7
- 8. After instance isstartedsys@CS11GR2> !ipcs -m------ Shared Memory Segments --------key shmid owner perms bytes nattch status0x44c4474c 360449 oracle 660 538968064 168
- 9. 9InstanceSystem Global AreaSharedPoolServer ProcessesDatabaseBuffer CacheLibrary CacheRedo Log BufferData Dictionary CacheListenerOracle Background ProcessesSystem Monitor(SMON)DatabaseWriter(DBW0)ProcessMonitor(PMON)Check Point(CKPT)LogWriter(LGWR)Archiver(ARC0)Client ProcessesDataBaseDataFilesControlFilesRedo Log Files
- 10. After instance isstartedsys@CS11GR2> !ps -ef | grepcs11gR2| grep -v greporacle 13134 1 0 13:54 ? 00:00:00 ora_dbw0_cs11gR2oracle 13136 1 0 13:54 ? 00:00:00 ora_lgwr_cs11gR2 . . .oracle 13116 1 0 13:54 ? 00:00:00 ora_pmon_cs11gR2 oracle 13140 1 0 13:54 ? 00:00:00 ora_smon_cs11gR2SQL> !ps -ef | grep cs11gR2 | grep -v grep | wc -l1610
- 11. 11InstanceSystem Global AreaSharedPoolServer ProcessesDatabaseBuffer CacheLibrary CacheRedo Log BufferData Dictionary CacheListenerOracle Background ProcessesSystem Monitor(SMON)DatabaseWriter(DBW0)ProcessMonitor(PMON)Check Point(CKPT)LogWriter(LGWR)Archiver(ARC0)Client ProcessesDataBaseDataFilesControlFilesRedo Log Files
- 12. Open the Databasesys@CS11GR2>alter database mount;Database altered.sys@CS11GR2> alter database open;Database altered.sys@CS11GR2> select * from sid.emp where ename='SMITH';EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO------ ---------- --------- ------ --------- ----- ------ -------- 7369 SMITH CLERK 7902 17-DEC-80 800 201 row selected.12
- 13. 13InstanceSystem Global AreaSharedPoolServer ProcessesDatabaseBuffer CacheLibrary CacheRedo Log BufferData Dictionary CacheListenerOracle Background ProcessesSystem Monitor(SMON)DatabaseWriter(DBW0)ProcessMonitor(PMON)Check Point(CKPT)LogWriter(LGWR)Archiver(ARC0)Client ProcessesDataBaseDataFilesControlFilesRedo Log Files
- 14. Connect from RemoteFailed!C:\Documentsand Settings\chensi4>sqlplussid/sid@cs11gSQL*Plus: Release 10.2.0.1.0 - Production on Sun Jul 24 20:29:17 2011Copyright (c) 1982, 2005, Oracle. All rights reserved.ERROR:ORA-12541: TNS:no listener14
- 15. Startup the Listeneroracle@cargosmart:~/scripts$lsnrctl startService "cs11gR2" has 1 instance(s). Instance "cs11gR2", status READY, has 1 handler(s) for this service...The command completed successfully15
- 16. Now query fromRemoteC:\Documents and Settings\chensi4>sqlplussid/sid@cs11gR2Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> select * from emp where ename='SMITH'; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO------- ------- ------- ----- --------- ----- ------ -------- 7369 SMITH CLERK 7902 17-DEC-80 800 201 row selected.16
- 17. 17InstanceSystem Global AreaSharedPoolServer ProcessesDatabaseBuffer CacheLibrary CacheRedo Log BufferData Dictionary CacheListenerOracle Background ProcessesSystem Monitor(SMON)DatabaseWriter(DBW0)ProcessMonitor(PMON)Check Point(CKPT)LogWriter(LGWR)Archiver(ARC0)Client ProcessesDataBaseDataFilesControlFilesRedo Log Files
- 18. What Oracle DoWhenyou submit a SQLFrom pl/sql developFrom the JDBC API in your java applicationFrom sqlplus18
- 19. 19SELECT AVG(SAL) FROMEMP WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE DNAME='SALES')Parser
- 20. 20SELECT AVG(SAL) FROMEMP WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE DNAME='SALES')Parser
- 21. 21SELECT AVG(SAL) FROMDEPT, EMP WHERE EMP.DEPTNO=DEPT.DEPTNO AND DEPT.DNAME='SALES';Query TransformerTransformed QueryStatisticsEstimatorData DictionaryQuery + CostPlan Generator
- 22. 22Query TransformerTransformed QueryStatisticsEstimatorDataDictionaryQuery + CostPlan Generator
- 23. 23Query PlanRowSourceGeneratorSQL ExecutionResult
- 24. A Real Plan----------------------------------------------------------------------|Id | Operation | Name | Rows | Cost (%CPU)|----------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 6 (17)|| 1 | SORT AGGREGATE | | 1 | || 2 | MERGE JOIN | | 5 | 6 (17)||* 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 2 (0)|| 4 | INDEX FULL SCAN | DEPT_PK | 4 | 1 (0)||* 5 | SORT JOIN | | 14 | 4 (25)|| 6 | TABLE ACCESS FULL | EMP | 14 | 3 (0)|----------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 3 - filter("DNAME"='SALES') 5 - access("DEPTNO"="DEPTNO") filter("DEPTNO"="DEPTNO")24
- 25.
Editor's Notes
- #3 From Shop.oracle.comUS$47,500.00 / Processor for Enterprise EditionUS$23,000.00 / Processor for RACUS$11,500.00 / Processor for Partition