SQL>conn / as sysdba; Connected. SQL>create user anqing identified by anqing default tablespace users temporarytablespace temp quota 10M on users; Usercreated.

 

查询用户配额的信息： SQL>select tablespace_name,username,max_bytes from  DBA_TS_QUOTAS whereusername=‘ANQING‘;

 

TABLESPACE_NAME     USERNAME    MAX_BYTES ---------------------------------------- ---------- USERS                   ANQING      10485760

 

2.更改用户的表空间限额：

 

不对用户做表空间限额控制:

 

SQL>grant unlimited tablespace to anqing; Grantsucceeded.

 

这种方式是全局性的，修改的是当前用户对于所有表空间的配额，即赋予该用户无限个表空间创建表权限，每个表空间下都没有配额限制。

 

如果我们想改某个具体的,即针对用户的某个特定的表空间，可以使用如下SQL：

 

SQL>alter user anqing quota unlimited on users; Useraltered.

 

查看配额： SQL>select tablespace_name,username,max_bytes from  DBA_TS_QUOTAS whereusername=‘ANQING‘;

 

TABLESPACE_NAME               USERNAME   MAX_BYTES ---------------------------------------- ---------- USERS                        ANQING             -1

 

这时候max_bytes为-1，即不受限制。

 

3.回收用户对表空间的配额: 回收空间配额有两种方式：         1）全局： SQL>revoke unlimited tablespace from anqing; Revokesucceeded.

 

在查看配额，已经没有了相关信息： SQL>select tablespace_name,username,max_bytes from  DBA_TS_QUOTAS whereusername=‘ANQING‘; no rowsselected

 

         2）特定表空间： SQL>alter user anqing quota 0 on users;  

oracle数据库的内存结构比较复杂，下面对pga/sga/uga做比较分析。

1. sga组成:

    database buffer cache:包括 default pool,keep pool,recycle pool；

    redo log buffer
    share pool:包括 library cache,dictionary cache
    large pool
    java pool
    streams pool
    fixed sga
2.pga组成：

    1）sql工作区：sort area(排序区),hash area(构造hash表）,bitmap merge area(索引区)

     2）uga区
3.pga和uga比较：
uga：user global area ，是会话含义的内存区
        为了保证数据可以被会话访问到，所以mts模式属于sga中的大池，专有模式属于pga，属于用户的内存区。
         uga保存当前会话相关的信息，比如会话登录信息、pl／sql包的参数信息,绑定变量的值。
pga：program global area，是操作系统含义上的内存区，
       可以理解为操作系统在一个进程启动时，为他分配的内存空间
        查询使用 show pga;
4.sga和pga比较：
sga：共享数据块，所有进程可以访问,数据并发访问
         涉及lock，latch,锁定和队列
        是数据库最主要优化区域，一些重要的指标：data buffer hit,library hit(hard/soft parse),hot blocks
pga：为专有进程服务，进程间无法数据共享，数据独占
       无需锁定机制
        性能优化只需要考虑它的大小。
---------------------

四、MySQL 数据库性能优化之缓存参数优化

数据库属于 IO 密集型的应用程序，其主要职责就是数据的管理及存储工作。而我们知道，从内存中读取一个数据库的时间是微秒级别，而从一块普通硬盘上读取一个IO是在毫秒级别，二者相差3个数量级。所以，要优化数据库，首先第一步需要优化的就是 IO，尽可能将磁盘IO转化为内存IO。本文先从 MySQL 数据库IO相关参数（缓存参数）的角度来看看可以通过哪些参数进行IO优化：

• query_cache_size/query_cache_type （global） Query cache 作用于整个 MySQL Instance，主要用来缓存 MySQL 中的 ResultSet，也就是一条SQL语句执行的结果集，所以仅仅只能针对select语句。当我们打开了 Query Cache 功能，MySQL在接受到一条select语句的请求后，如果该语句满足Query Cache的要求（未显式说明不允许使用Query Cache，或者已经显式申明需要使用Query Cache），MySQL 会直接根据预先设定好的HASH算法将接受到的select语句以字符串方式进行hash，然后到Query Cache 中直接查找是否已经缓存。也就是说，如果已经在缓存中，该select请求就会直接将数据返回，从而省略了后面所有的步骤（如 SQL语句的解析，优化器优化以及向存储引擎请求数据等），极大的提高性能。当然，Query Cache 也有一个致命的缺陷，那就是当某个表的数据有任何任何变化，都会导致所有引用了该表的select语句在Query Cache 中的缓存数据失效。所以，当我们的数据变化非常频繁的情况下，使用Query Cache 可能会得不偿失。Query Cache的使用需要多个参数配合，其中最为关键的是 query_cache_size 和 query_cache_type ，前者设置用于缓存 ResultSet 的内存大小，后者设置在何场景下使用 Query Cache。在以往的经验来看，如果不是用来缓存基本不变的数据的MySQL数据库，query_cache_size 一般 256MB 是一个比较合适的大小。当然，这可以通过计算Query Cache的命中率（Qcache_hits/(Qcache_hits+Qcache_inserts)*100)）来进行调整。query_cache_type可以设置为0(OFF)，1(ON)或者2(DEMOND)，分别表示完全不使用query cache，除显式要求不使用query cache（使用sql_no_cache）之外的所有的select都使用query cache，只有显示要求才使用query cache（使用sql_cache）。
• binlog_cache_size （global） Binlog Cache 用于在打开了二进制日志（binlog）记录功能的环境，是 MySQL 用来提高binlog的记录效率而设计的一个用于短时间内临时缓存binlog数据的内存区域。一般来说，如果我们的数据库中没有什么大事务，写入也不是特别频繁，2MB～4MB是一个合适的选择。但是如果我们的数据库大事务较多，写入量比较大，可与适当调高binlog_cache_size。同时，我们可以通过binlog_cache_use 以及 binlog_cache_disk_use来分析设置的binlog_cache_size是否足够，是否有大量的binlog_cache由于内存大小不够而使用临时文件（binlog_cache_disk_use）来缓存了。
• key_buffer_size （global） Key Buffer 可能是大家最为熟悉的一个 MySQL 缓存参数了，尤其是在 MySQL 没有更换默认存储引擎的时候，很多朋友可能会发现，默认的 MySQL 配置文件中设置最大的一个内存参数就是这个参数了。key_buffer_size 参数用来设置用于缓存 MyISAM存储引擎中索引文件的内存区域大小。如果我们有足够的内存，这个缓存区域最好是能够存放下我们所有的 MyISAM 引擎表的所有索引，以尽可能提高性能。此外，当我们在使用MyISAM 存储的时候有一个及其重要的点需要注意，由于 MyISAM 引擎的特性限制了他仅仅只会缓存索引块到内存中，而不会缓存表数据库块。所以，我们的 SQL 一定要尽可能让过滤条件都在索引中，以便让缓存帮助我们提高查询效率。
• bulk_insert_buffer_size （thread）和key_buffer_size一样，这个参数同样也仅作用于使用 MyISAM存储引擎，用来缓存批量插入数据的时候临时缓存写入数据。当我们使用如下几种数据写入语句的时候，会使用这个内存区域来缓存批量结构的数据以帮助批量写入数据文件：insert … select …
  insert … values (…) ,(…),(…)…
  load data infile… into… (非空表)
• innodb_buffer_pool_size（global）当我们使用InnoDB存储引擎的时候，innodb_buffer_pool_size 参数可能是影响我们性能的最为关键的一个参数了，他用来设置用于缓存 InnoDB 索引及数据块的内存区域大小，类似于 MyISAM 存储引擎的 key_buffer_size 参数，当然，可能更像是 Oracle 的 db_cache_size。简单来说，当我们操作一个 InnoDB 表的时候，返回的所有数据或者去数据过程中用到的任何一个索引块，都会在这个内存区域中走一遭。和key_buffer_size 对于 MyISAM 引擎一样，innodb_buffer_pool_size 设置了 InnoDB 存储引擎需求最大的一块内存区域的大小，直接关系到 InnoDB存储引擎的性能，所以如果我们有足够的内存，尽可将该参数设置到足够打，将尽可能多的 InnoDB 的索引及数据都放入到该缓存区域中，直至全部。我们可以通过 (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100% 计算缓存命中率，并根据命中率来调整 innodb_buffer_pool_size 参数大小进行优化。
• innodb_additional_mem_pool_size（global）这个参数我们平时调整的可能不是太多，很多人都使用了默认值，可能很多人都不是太熟悉这个参数的作用。innodb_additional_mem_pool_size 设置了InnoDB存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小，所以当我们一个MySQL Instance中的数据库对象非常多的时候，是需要适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率的。这个参数大小是否足够还是比较容易知道的，因为当过小的时候，MySQL 会记录 Warning 信息到数据库的 error log 中，这时候你就知道该调整这个参数大小了。
• innodb_log_buffer_size （global）这是 InnoDB 存储引擎的事务日志所使用的缓冲区。类似于 Binlog Buffer，InnoDB 在写事务日志的时候，为了提高性能，也是先将信息写入 Innofb Log Buffer 中，当满足 innodb_flush_log_trx_commit 参数所设置的相应条件（或者日志缓冲区写满）之后，才会将日志写到文件（或者同步到磁盘）中。可以通过 innodb_log_buffer_size 参数设置其可以使用的最大内存空间。
  注：innodb_flush_log_trx_commit 参数对 InnoDB Log 的写入性能有非常关键的影响。该参数可以设置为0，1，2，解释如下：0：log buffer中的数据将以每秒一次的频率写入到log file中，且同时会进行文件系统到磁盘的同步操作，但是每个事务的commit并不会触发任何log buffer 到log file的刷新或者文件系统到磁盘的刷新操作；
  1：在每次事务提交的时候将log buffer 中的数据都会写入到log file，同时也会触发文件系统到磁盘的同步；
  2：事务提交会触发log buffer 到log file的刷新，但并不会触发磁盘文件系统到磁盘的同步。此外，每秒会有一次文件系统到磁盘同步操作。此外，MySQL文档中还提到，这几种设置中的每秒同步一次的机制，可能并不会完全确保非常准确的每秒就一定会发生同步，还取决于进程调度的问题。实际上，InnoDB 能否真正满足此参数所设置值代表的意义正常 Recovery 还是受到了不同 OS 下文件系统以及磁盘本身的限制，可能有些时候在并没有真正完成磁盘同步的情况下也会告诉 mysqld 已经完成了磁盘同步。
• innodb_max_dirty_pages_pct （global）这个参数和上面的各个参数不同，他不是用来设置用于缓存某种数据的内存大小的一个参数，而是用来控制在 InnoDB Buffer Pool 中可以不用写入数据文件中的Dirty Page 的比例（已经被修但还没有从内存中写入到数据文件的脏数据）。这个比例值越大，从内存到磁盘的写入操作就会相对减少，所以能够一定程度下减少写入操作的磁盘IO。但是，如果这个比例值过大，当数据库 Crash 之后重启的时间可能就会很长，因为会有大量的事务数据需要从日志文件恢复出来写入数据文件中。同时，过大的比例值同时可能也会造成在达到比例设定上限后的 flush 操作“过猛”而导致性能波动很大。

上面这几个参数是 MySQL 中为了减少磁盘物理IO而设计的主要参数，对 MySQL 的性能起到了至关重要的作用。   —EOF—   按照 mcsrainbow 朋友的要求，这里列一下根据以往经验得到的相关参数的建议值：

• query_cache_type : 如果全部使用innodb存储引擎，建议为0，如果使用MyISAM 存储引擎，建议为2，同时在SQL语句中显式控制是否是哟你gquery cache
• query_cache_size: 根据 命中率（Qcache_hits/(Qcache_hits+Qcache_inserts)*100)）进行调整，一般不建议太大，256MB可能已经差不多了，大型的配置型静态数据可适当调大
• binlog_cache_size: 一般环境2MB～4MB是一个合适的选择，事务较大且写入频繁的数据库环境可以适当调大，但不建议超过32MB
• key_buffer_size: 如果不使用MyISAM存储引擎，16MB足以，用来缓存一些系统表信息等。如果使用 MyISAM存储引擎，在内存允许的情况下，尽可能将所有索引放入内存，简单来说就是“越大越好”
• bulk_insert_buffer_size: 如果经常性的需要使用批量插入的特殊语句（上面有说明）来插入数据，可以适当调大该参数至16MB～32MB，不建议继续增大，某人8MB
• innodb_buffer_pool_size: 如果不使用InnoDB存储引擎，可以不用调整这个参数，如果需要使用，在内存允许的情况下，尽可能将所有的InnoDB数据文件存放如内存中，同样将但来说也是“越大越好”
• innodb_additional_mem_pool_size: 一般的数据库建议调整到8MB～16MB，如果表特别多，可以调整到32MB，可以根据error log中的信息判断是否需要增大
• innodb_log_buffer_size: 默认是1MB，系的如频繁的系统可适当增大至4MB～8MB。当然如上面介绍所说，这个参数实际上还和另外的flush参数相关。一般来说不建议超过32MB
• innodb_max_dirty_pages_pct: 根据以往的经验，重启恢复的数据如果要超过1GB的话，启动速度会比较慢，几乎难以接受，所以建议不大于 1GB/innodb_buffer_pool_size(GB)*100 这个值。当然，如果你能够忍受启动时间比较长，而且希望尽量减少内存至磁盘的flush，可以将这个值调整到90，但不建议超过90

注：以上取值范围仅仅只是我的根据以往遇到的数据库场景所得到的一些优化经验值，并不一定适用于所有场景，所以在实际优化过程中还需要大家自己不断的调整分析，也欢迎大家随时通过 Mail 与我联系沟通交流优化或者是架构方面的技术，一起探讨相互学习。    

Mysql优化总结

一、索引
1、创建索引：
（1）．ALTER TABLE   
 ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。    
    
 ALTER TABLE table_name ADD INDEX index_name (column_list)   
  
 ALTER TABLE table_name ADD UNIQUE (column_list)   
  
 ALTER TABLE table_name ADD PRIMARY KEY (column_list)   
  
（2）、CREATE INDEX   
 CREATE INDEX可对表增加普通索引或UNIQUE索引。   
  
 CREATE INDEX index_name ON table_name (column_list)   
  
 CREATE UNIQUE INDEX index_name ON table_name (column_list)  
2、查看索引  
  
 mysql> show index from tblname;   
  
 mysql> show keys from tblname; 
3、删除索引
 可利用ALTER TABLE或DROP INDEX语句来删除索引。类似于CREATE INDEX语句，DROP INDEX可以在ALTER TABLE 内部作为一条语句处理，语法如下。  
 DROP INDEX index_name ON talbe_name   
  
 ALTER TABLE table_name DROP INDEX index_name   
  
 ALTER TABLE table_name DROP PRIMARY KEY   

索引：http://www.cnblogs.com/hustcat/archive/2009/10/28/1591648.html
**explain +select ·····用来获取select语句的执行的相关信息及索引的使用等
**describe table table_name;
**analyze table table_name;查看表的信息，帮助优化
**show 查看执行状态

二、my.ini中的配置
http://www.chinaz.com/program/2009/1210/100740.shtml
mysql > show status; 可以查看具体的设置 服务器的状态
具体的配置呀什么，没有亲自试验过

三、数据表引擎
 1、MyISAM：mysql默认的
 2、InnoDB：支持事务、锁、外键、聚簇索引
引擎介绍：http://blog.csdn.net/cheungjustin/article/details/5999880
 http://limaolinjia.blog.163.com/blog/static/539162282011012145139/

四、索引的类型：
 1、B-Tree索引
 2、hash索引
具体的参考还是一）

五、事务
数据表引擎使用InnoDB
http://www.cnblogs.com/winner/archive/2011/11/09/2242272.html

六、存储过程
经编译和优化后存储在数据库服务器中，运行效率高，可以降低客户机和服务器之间的通信量，有利于集中控制，易于维护 （P247）
http://blog.sina.com.cn/s/blog_52d20fbf0100ofd5.html

七、mysql profiling（mysql性能分析器）优化sql语句
查看SQL执行消耗系统资源的信息
++++需要开启+++
具体使用：http://www.jiunile.com/mysql-profiling%E7%9A%84%E4%BD%BF%E7%94%A8.html

八、慢查询日志
++++需要开启++++
通过慢日志查询可以知道哪些SQL语句执行效率低下，那些sql语句使用的频率高等
对MySQL查询语句的监控、分析、优化是MySQL优化非常重要的一步。开启慢查询日志后，由于日志记录操作，在一定程度上会占用CPU资源影响mysql的性能，但是可以阶段性开启来定位性能瓶颈。
具体参考：http://blog.csdn.net/renzhenhuai/article/details/8839874

关于mysql的一些讲解：http://www.ccvita.com/category/mysql

 

Oracle数据库管理----性能优化

标签：arc   比例   addition   二进制日志   connected   评估   details   刷新   lib