Archive for the 'database' Category

MySQL regex_replace Function

Published on January 18, 2012 in MySQL. 0 Comments Tags: .
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
<pre>DELIMITER $$
CREATE FUNCTION  `regex_replace`(pattern VARCHAR(1000),replacement VARCHAR(1000),original VARCHAR(1000))

RETURNS VARCHAR(1000)
DETERMINISTIC
BEGIN
 DECLARE temp VARCHAR(1000);
 DECLARE ch VARCHAR(1);
 DECLARE i INT;
 SET i = 1;
 SET temp = '';
 IF original REGEXP pattern THEN
  loop_label: LOOP
   IF i&gt;CHAR_LENGTH(original) THEN
    LEAVE loop_label;
   END IF;
   SET ch = SUBSTRING(original,i,1);
   IF NOT ch REGEXP pattern THEN
    SET temp = CONCAT(temp,ch);
   ELSE
    SET temp = CONCAT(temp,replacement);
   END IF;
   SET i=i+1;
  END LOOP;
 ELSE
  SET temp = original;
 END IF;
 RETURN temp;
END$$
DELIMITER ;</pre>

AWR报告生成

Published on September 2, 2011 in oracle. 0 Comments Tags: .

一.生成AWR报告

[ractest@sun880-1 /u01/app/oracle/orahome/rdbms/admin]$ cd $ORACLE_HOME/rdbms/admin
[ractest@sun880-1 /u01/app/oracle/orahome/rdbms/admin]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Sun Jan 27 20:47:38 2008
Copyright (c) 1982, 2007, Oracle. All Rights Reserved/span>.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> @awrrpt
......

//根据Oracle提示生成报告……

 注：报告生成在$ORACLE_HOME/rdbms/admin/目录下。

二.修改awr的默认设置

//半小时采集一次，采集信息保存3天。

SQL>EXEC dbms_workload_repository.modify_snapshot_settings (INTERVAL=>30,retention =>3*24*60);

三.关闭AWR自动收集

SQL>EXEC dbms_workload_repository.modify_snapshot_settings (INTERVAL=>0,retention =>24*60);

注：10g默认是自动开启awr信息收集的，会对系统有一定的影响（很小）；如果要关闭awr信息收集，只需设置interval参数为0即可。但interval设0后，AWR报告无法生成。


Oracle常用命令

Published on September 2, 2011 in oracle. 0 Comments Tags: .

一.基础SQL语句

  • 查：select * from employees;
  • 增：insert into employees (Name , Birthday , Location , Salary) values (‘hyddd’,’1984-10-1′,’guangzhou’,999999);
  • 改：update employees set Salary=999999999 where Name=’hyddd’;
  • 删：delete from employees where name=’zhangsan’;

更多技巧请参考：SQL基本语句

 

二.PFile，SPFile管理

  • SPFile->PFile：

  • ##用生成对应SID的spfile生成pfile，生成的pfile位置：$ORACLE_HOME/dbs/init$ORACLE_SID.ora
    SQL    > create pfile from spfile;
    –——
    ##自己指定生成文件的位置
    SQL    > create pfile=‘/home/oracle/initorcl.ora‘ from spfile;

  • PFile->SPFile

    • SQL> create spfile from pfile;

     

    三.启动，关闭数据库

    • 启动

      • SQL>startup

    • 关闭

    • SQL>shutdown normal
      SQL      >shutdown transactional
      SQL      >shutdown immediate
      SQL      >shutdown abort

    四.修改SGA参数
    • 修改SGA的原则
      1. sga_target <= sga_max_size
      2. SGA加上PGA等其他进程占用的内存必须少于机器物理内存。
    • 命令

      • SQL>alter system set sga_max_size=2048m scope=spfile;
        SQL        >alter system set sga_target=2048m scope=spfile;
        ##修改SGA的相关参数，只能spfile，然后重启数据库。不能直接scope        =both!

    五.查询Oracle配置参数
    • 命令

      • SQL>show parameter;

    • 具体查询某个Oracle参数，只需输入部分关键字即可，比如：查询sga_target的值。

      • SQL> show parameter sga;

        NAME                                 TYPE        VALUE
        –———————————- ———– ——————————
        lock_sga                             boolean     FALSE
        pre_page_sga                         boolean     FALSE
        sga_max_size                         big         integer 2G
        sga_target                           big         integer 2G
        SQL        >

    Continue reading ‘Oracle常用命令’

    MySQL锁表机制分析

    Published on September 13, 2010 in database and MySQL. 0 Comments Tags: .

    为了给高并发情况下的mysql进行更好的优化，有必要了解一下mysql查询更新时的锁表机制。
    一、概述
    MySQL有三种锁的级别：页级、表级、行级。
    MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；BDB存储引擎采用的是页面锁（page-level
    locking），但也支持表级锁；InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。
    Continue reading ‘MySQL锁表机制分析’

    HOWTO: configure MySQL’s my.cnf file

    Published on September 9, 2010 in database and MySQL. 0 Comments Tags: .
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    [mysqld]
    user=mysql
    bind-address=127.0.0.1
    datadir=/var/lib/mysql
    pid-file=/var/run/mysqld/mysqld.pid
    socket=/var/run/mysql/mysql.sock
    port=3306
    tmpdir=/tmp
    language=/usr/share/mysql/english
    skip-external-locking
    query_cache_limit=64M
    query_cache_size=32M
    query_cache_type=1
    max_connections=15
    max_user_connections=300
    interactive_timeout=100
    wait_timeout=100
    connect_timeout=10
    thread_stack=128K
    thread_cache_size=128
    myisam-recover=BACKUP
    key_buffer=64M
    join_buffer=1M
    max_allowed_packet=32M
    table_cache=512M
    sort_buffer_size=1M
    read_buffer_size=1M
    read_rnd_buffer_size=768K
    max_connect_errors=10
    thread_concurrency=4
    myisam_sort_buffer_size=32M
    skip-locking
    skip-bdb
    expire_logs_days=10
    max_binlog_size=100M
    server-id=1
    [mysql.server]
    user=mysql
    basedir=/usr
    [safe_mysqld]
    bind-address=127.0.0.1
    err-log=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
    open_files_limit=8192
    SAFE_MYSQLD_OPTIONS=”–defaults-file=/etc/my.cnf –log-slow-queries=/var/log/slow-queries.log”
    [mysql]
    [isamchk]
    key_buffer=64M
    sort_buffer=64M
    read_buffer=16M
    write_buffer=16M
    [myisamchk]
    key_buffer=64M
    sort_buffer=64M
    read_buffer=16M
    write_buffer=16M
    [mysqlhotcopy]
    interactive-timeout
    max_heap_table_size = 64 M
    tmp_table_size = 64 M
    !includedir /etc/mysql/conf.d/

    Continue reading ‘HOWTO: configure MySQL’s my.cnf file’

    MySQL性能优化21 – 使用随机函数产生采样

    Published on September 8, 2010 in database and MySQL. 0 Comments Tags: .

    RAND() 函数是 MySQL 提供的产生随机数的函数。没有指定参数时，每次执行会返回 0-1 的浮点数：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    mysql> select rand();

    +------------------+
    | rand() |
    +------------------+
    | 0.56110724207117 |
    +------------------+

    1 row in set (0.02 sec)

    mysql> select rand();

    +-----------------+
    | rand() |
    +-----------------+
    | 0.3343874880433 |
    +-----------------+

    1 row in set (0.00 sec)

    Continue reading ‘MySQL性能优化21 – 使用随机函数产生采样’

    MySQL性能优化20 – ORDER BY 操作的优化

    Published on September 8, 2010 in database and MySQL. 0 Comments Tags: .

    在某些情况中， MySQL 可以使用一个索引来满足 ORDER BY 子句，而不需要额外的排序。 where 条件和 order by 使用相同的索引，并且 order by 的顺序和索引顺序相同，并且 order by 的字段都是升序或者都是降序。

    例如：下列 sql 可以使用索引。

    1
    2
    3
    4
    5
    SELECT * FROM t1 ORDER BY key_part1,key_part2,... ;

    SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;

    SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;

    但是以下情况不使用索引：

    1
    SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC ；

    –order by 的字段混合 ASC 和 DESC

    1
    SELECT * FROM t1 WHERE key2=constant ORDER BY key1 ；

    – 用于查询行的关键字与 ORDER BY 中所使用的不相同

    1
    SELECT * FROM t1 ORDER BY key1, key2 ；

    – 对不同的关键字使用 ORDER BY 。

    MySQL性能优化19 – GROUP BY 操作的优化

    Published on September 8, 2010 in database and MySQL. 0 Comments Tags: .

    默认情况下， MySQL 在执行 GROUP BY col1 ， col2.... 操作的时候，会按照 GROUP BY 字段的顺序进行排序。如果显式包括一个包含相同的列的 ORDER BY 子句，则对 MySQL 的实际执行性能没有什么额外的影响。

    如果查询包括 GROUP BY 操作， 但是不需要对结果进行排序，或者对默认的排序结果不满意，希望获得结果后再由程序进一步处理的时候，可以指定 ORDER BY NULL 禁止排序，从而避免排序结果的消耗。 Continue reading ‘MySQL性能优化19 – GROUP BY 操作的优化’

    MySQL性能优化18 – 使用 GROUP BY WITH ROLLUP 改善统计性能

    Published on September 8, 2010 in database and MySQL. 0 Comments Tags: .

    使用 GROUP BY 的 WITH ROLLUP 字句可以检索出更多的分组聚合信息，它不仅仅能像一般的 GROUP BY 语句那样检索出各组的聚合信息，还能检索出本组类的整体聚合信息。 Continue reading ‘MySQL性能优化18 – 使用 GROUP BY WITH ROLLUP 改善统计性能’

    MySQL性能优化17 – INSERT操作的优化

    Published on September 8, 2010 in database and MySQL. 0 Comments Tags: .

    执行 INSERT 操作的时候，可以考虑使用以下的方式优化 SQL 的执行效率：

    • 如果你同时从同一客户插入很多行，使用多个值表的 INSERT 语句。这比使用分开 INSERT 语句快。
      1
      Insert into test values(1,2),(1,3),(1,4) …
    • 如果你从不同客户插入很多行，能通过使用 INSERT DELAYED 语句得到更高的速度。 Delayed的含义是让 INSERT 语句马上执行，其实数据都被放在内存的队列中，并没有真正写入磁盘；这比每条语句分别插入要快的多； LOW_PRIORITY 刚好相反，在所有其他用户对表的读写完后才进行插入；
    • 将索引文件和数据文件分在不同的磁盘上存放；
    • 如果进行批量插入，可以增加 bulk_insert_buffer_size 变量值的方法来提高速度，但是，这只能对 MyISAM 表使用；
    • 当从一个文本文件装载一个表时，使用 LOAD DATA INFILE。这通常比使用很多 INSERT 语句快 20 倍；
    • 根据应用情况使用 REPLACE 语句代替 INSERT ；
    • 根据应用情况使用 IGNORE 关键字忽略重复记录。