SQL学习资料PPT课件

1 OracleDatabase SQLAdvance Tuning 亚信联创管理学院聂耿青 目录 Oracle数据库体系结构与SQL开发环境简介数据查询语言DQL介绍数据操纵语言DML介绍数据定义语言DDL介绍SQL优化原则与工具简介SQL优化技巧与实例讲解 3 Oracle数据库体系结构与SQL开发环境简介 Database OracleDatabaseServerArchitecture Overview Instance SGA BGP2 BGP1 BGPn BGP3 ConnectingtotheDatabaseInstance SQL Select Connection User Userprocess Serverprocess Session Specificconnecteddatabaseuser S000 Userprocess Userprocess DedicatedServer SharedServer D000 Dispatcher SGA Listenerprocess Serverhost Connection BidirectionalnetworkpathwaybetweenauserprocessonaclientormiddletierandanOracleprocessontheserverSession Representationofaspecificloginbyauser OracleDatabaseMemoryStructures Overview Backgroundprocess Serverprocess Serverprocess SGA Redologbuffer Databasebuffercache Javapool Streamspool Sharedpool Largepool AggregatedPGA DatabaseBufferCache Databasewriterprocess Databasebuffercache SGA Datafiles DBWn Serverprocess IsapartoftheSGAHoldscopiesofdatablocksthatarereadfromdatafilesIssharedbyallconcurrentprocesses RedoLogBuffer Logwriterprocess Redologbuffer SGA Redologfiles LGWR Serverprocess IsacircularbufferintheSGA basedonthenumberofCPUs Containsredoentriesthathavetheinformationtoredochangesmadebyoperations suchasDMLandDDL SGA SharedPool Librarycache Datadictionarycache rowcache Controlstructures Resultcache Serverprocess Sharedpool IspartoftheSGAContains LibrarycacheSharedpartsofSQLandPL SQLstatementsDatadictionarycacheResultcache SQLqueriesPL SQLfunctionsControlstructuresLocks ProcessingaDMLStatement Example Database Datafiles Controlfiles Redologfiles Userprocess Sharedpool Redologbuffer Serverprocess 3 5 1 Librarycache 2 4 Databasebuffercache DBWn SGA 2 COMMITProcessing Example Database Datafiles Controlfiles Redologfiles Userprocess SGA Sharedpool Redologbuffer Serverprocess 1 3 Librarycache Databasebuffercache DBWn 2 LGWR SGA ProgramGlobalArea PGA Serverprocess PGAisamemoryareathatcontains SessioninformationCursorinformationSQLexecutionworkareas SortareaHashjoinareaBitmapmergeareaBitmapcreateareaWorkareasizeinfluencesSQLperformance Workareascanbeautomaticallyormanuallymanaged BackgroundProcessRoles PMON SMON ARCn DBWn LGWR CKPT Databasebuffercache Sharedpool SGA Redologbuffer MMON CJQ0 QMNn RCBG MMAN SQLStatementImplementation SGA SharedSQLarea Librarycache Datadictionarycache Resultcache SharedSQLarea Other SHARED POOL Userprocess Serverprocess PrivateSQLarea Userprocess Serverprocess Userprocess Serverprocess Userprocess Serverprocess Userprocess Serverprocess PrivateSQLarea PrivateSQLarea PrivateSQLarea PrivateSQLarea Javapool Streamspool Buffercache Redologbuffer Client Server AggregatedPGA Syntacticandsemanticcheck SQLStatementParsing Overview Privilegescheck AllocateprivateSQLArea ExistingsharedSQLarea AllocatesharedSQLarea Executestatement No Hardparse Yes Softparse Parsecall Parseoperation Optimization PrivateSQLarea SharedSQLarea Parsedrepresentation DatabaseStorageArchitecture Onlineredologfiles Passwordfile Parameterfile Archivedredologfiles Controlfiles Datafiles Alertlogandtracefiles Backupfiles LogicalandPhysicalDatabaseStructures Database Logical Physical Tablespace Datafile OSblock Segment Extent Oracledatablock Schema 0 1 ormany Only1withbigfiletablespaces Undotablespacesneverhave0 Segments Extents andBlocks Segmentsexistinatablespace Segmentsarecollectionsofextents Extentsarecollectionsofdatablocks Datablocksaremappedtodiskblocks Segment Extents Datablocks Diskblocks SQL PLSQL编程环境简介 InstantClient SQL PlusSQLDeveloper 11g内置 推荐并介绍 PLSQLDeveloper 第三方 推荐并介绍 iSQL Plus 通过浏览器访问 Reports Forms etc其他第三方工具 InstallOracleClient OracleSQLDevTools exe 仅限用于教学和练习 未经授权 安装 复制文件OracleSQLDevTools exe到C C盘根目录 点击并回车即可 文件会自解压到C 即缺省目录 不要更改 其中内置OracleClient SQL PLUS PL SQLDeveloper LoggingIntoSQL Plus FromWindowsenvironment Fromcommandline sqlplus username password database conn HostString或Database conn是指连接字符串 在tnsnames ora寻找 若连接有问题 注意检查Server端的sqlnet ora及lsnrctlstatus tnsping NetServices ApplicationOracleNet RDBMSOracleNet Client Databaseserver TCP IPnetwork Listener