Wednesday, August 7, 2013

Index/table fragmentation scripts

-- identify all tables with more than 20% of rows deleted

select
T.TABLE_NAME,
t.partition_name,
a.num_rows,
sum(t.inserts) ins,
sum(t.updates) upd,
sum(t.deletes) del,
sum(t.updates)+sum(t.inserts)+sum(t.deletes) tot_chgs,
to_char((sum(t.deletes)/(decode(a.num_rows,0,1,a.num_rows)))*100.0,'999999.99') per_del,
ROUND(((SUM(T.UPDATES)+SUM(T.INSERTS)+SUM(T.DELETES))/(DECODE(a.NUM_ROWS,0,1,a.NUM_ROWS))*100.0),2) PER_CHG
from ALL_TAB_MODIFICATIONS T,
all_TABLES a
where T.timestamp >= TO_DATE('01-JAN-2001','dd-mon-yyyy')
and T.TABLE_NAME=a.TABLE_NAME
having (sum(t.deletes)/(decode(a.num_rows,0,1,a.num_rows)))*100 >=20
group by
T.TABLE_NAME, t.partition_name, a.NUM_ROWS
order by NUM_ROWS desc, t.table_name;

-- get fragmentation factor

select OWNER,TABLE_NAME, LAST_ANALYZED, NUM_ROWS,AVG_ROW_LEN,ROUND(blocks*8/1024) MB, ROUND(blocks*7297/AVG_ROW_LEN/NUM_ROWS,1)"factor" from DBA_TABLES
where blocks>1000 and num_rows>0 and owner not like 'SYS%' order by 1,7;

-- get BLEVEL for indexes (BLEVEL>=4 must be rebuild)

select t.owner,t.table_name,round(t.blocks*8/1024) "tMB", i.index_name, round(i.leaf_blocks*8/1024) "iMB", i.blevel from DBA_INDEXES i, DBA_TABLES t where t.table_name=i.table_name AND t.blocks<i.leaf_blocks*3 and t.blocks>6400 and t.owner not like 'SYS%' order by 1,3;

Wednesday, July 17, 2013

Move from ASMM to AMM

In a RAC architecture is easy to move from ASMM to AMM

SQL> conn / as sysdba
Connected.

SQL> alter system set memory_max_target=9G scope=spfile sid='*';

System altered.

SQL> alter system set memory_target=8G scope=spfile sid='*';

System altered.

SQL> alter system set sga_target=0 scope=spfile sid='*';

System altered.

SQL> alter system set pga_aggregate_target=0 scope=spfile sid='*';

System altered.

Then for each instance do a stop and a start:

srvctl stop instance -d dbora -i dbora2
srvctl start instance -d dbora -i dbora2

srvctl stop instance -d dbora -i dbora1
srvctl start instance -d dbora -i dbora1

Anyway, if you try to increase even more memory_max_target, at restart you might get the following error:

srvctl start instance -d dbora -i dbora2

PRCR-1013 : Failed to start resource ora.dbora.db

PRCR-1064 : Failed to start resource ora.dbora.db on node node-2

CRS-5017: The resource action "ora.dbora.db start" encountered the following error:

ORA-00845: MEMORY_TARGET not supported on this system

This error is generated by the size of memory chunks in memory allocation that is different depending on the size of memory allocated.

A workaround might be to decrease MEMORY_MAX_TARGET or increase also MEMORY_TARGET

Tuesday, July 2, 2013

MYSQL - Purge binary logs

1. SHOW SLAVE STATUS on slave server

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.120.251.21
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000047
Read_Master_Log_Pos: 925196802
Relay_Log_File: relay-bin.000140
Relay_Log_Pos: 925196965
Relay_Master_Log_File: mysql-bin.000047
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 925196802
Relay_Log_Space: 925197179
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 55f6585c-bd69-11e2-90a5-ac162d8b1c44
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)

2. SHOW BINARY LOGS on master server

mysql> show binary logs;
+------------------+------------+
| Log_name | File_size |
+------------------+------------+
| mysql-bin.000001 | 540 |
| mysql-bin.000002 | 143 |
| mysql-bin.000003 | 143 |
| mysql-bin.000004 | 143 |
| mysql-bin.000005 | 143 |
| mysql-bin.000006 | 143 |
| mysql-bin.000007 | 4810 |
| mysql-bin.000008 | 224 |
| mysql-bin.000009 | 2090270 |
| mysql-bin.000010 | 1073795562 |
| mysql-bin.000011 | 1073787709 |
| mysql-bin.000012 | 1073755369 |
| mysql-bin.000013 | 1073760500 |
| mysql-bin.000014 | 13629930 |
| mysql-bin.000015 | 20507 |
| mysql-bin.000016 | 1073756709 |
| mysql-bin.000017 | 1073786502 |
| mysql-bin.000018 | 656410524 |
| mysql-bin.000019 | 11416561 |
| mysql-bin.000020 | 38092807 |
| mysql-bin.000021 | 1073764283 |
| mysql-bin.000022 | 1073763265 |
| mysql-bin.000023 | 1073758670 |
| mysql-bin.000024 | 1073761848 |
| mysql-bin.000025 | 1073767027 |
| mysql-bin.000026 | 1073807701 |
| mysql-bin.000027 | 1073815447 |
| mysql-bin.000028 | 1073746876 |
| mysql-bin.000029 | 1073752803 |
| mysql-bin.000030 | 1073810003 |
| mysql-bin.000031 | 1073742110 |
| mysql-bin.000032 | 1073807390 |
| mysql-bin.000033 | 1073759944 |
| mysql-bin.000034 | 1073742378 |
| mysql-bin.000035 | 1073764107 |
| mysql-bin.000036 | 1073775793 |
| mysql-bin.000037 | 996879580 |
| mysql-bin.000038 | 3833572 |
| mysql-bin.000039 | 240904 |
| mysql-bin.000040 | 610464 |
| mysql-bin.000041 | 1073800478 |
| mysql-bin.000042 | 1073791022 |
| mysql-bin.000043 | 1073796481 |
| mysql-bin.000044 | 1073756133 |
| mysql-bin.000045 | 1073764282 |
| mysql-bin.000046 | 1073796542 |
| mysql-bin.000047 | 925713238 |
+------------------+------------+
47 rows in set (0.01 sec)

3. Determine the earliest log file among all the slaves. This is the target file. If all the slaves are up to date, this is the last log file on the list. (mysql-bin.000046)

4. Copy all binary logs that will be deleted

[root@mysql]# cp mysql-bin.00000* /db/backup_logs/
[root@mysql]# cp mysql-bin.00001* /db/backup_logs/
[root@mysql]# cp mysql-bin.00002* /db/backup_logs/

[root@mysql]# cp mysql-bin.00003* /db/backup_logs/

5. Purge all log files except target log file

mysql> purge binary logs to 'mysql-bin.000039';

Query OK, 0 rows affected (1.00 sec)

6. List remaning binary logs on master

mysql> show binary logs;
+------------------+------------+
| Log_name | File_size |
+------------------+------------+
| mysql-bin.000039 | 240904 |
| mysql-bin.000040 | 610464 |
| mysql-bin.000041 | 1073800478 |
| mysql-bin.000042 | 1073791022 |
| mysql-bin.000043 | 1073796481 |
| mysql-bin.000044 | 1073756133 |
| mysql-bin.000045 | 1073764282 |
| mysql-bin.000046 | 1073796542 |
| mysql-bin.000047 | 935732985 |
+------------------+------------+
9 rows in set (0.02 sec)

Wednesday, June 26, 2013

Use Stored Outlines in Oracle 10.2

Get cursors from shared pool

SQL> select * from table(dbms_xplan.display_cursor('3sdab0awk8g3c',null));

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID 3sdab0awk8g3c, child number 0

-------------------------------------

select fp_id from ftable where ftable.fp_name like :"SYS_B_0" and rownum=:"SYS_B_1"

Plan hash value: 3326224603

-------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 622K(100)| | | | |

|* 1 | COUNT STOPKEY | | | | | | | | |

| 2 | PX COORDINATOR | | | | | | | | |

| 3 | PX SEND QC (RANDOM) | :TQ10002 | 1353K| 81M| 622K (12)| 00:04:05 | Q1,02 | P->S | QC (RAND) |

|* 4 | COUNT STOPKEY | | | | | | Q1,02 | PCWC | |

| 5 | VIEW | index$_join$_001 | 1353K| 81M| 622K (12)| 00:04:05 | Q1,02 | PCWP | |

|* 6 | HASH JOIN BUFFERED | | | | | | Q1,02 | PCWP | |

| 7 | BUFFER SORT | | | | | | Q1,02 | PCWC | |

| 8 | PX RECEIVE | | 1353K| 81M| 196K (12)| 00:01:17 | Q1,02 | PCWP | |

| 9 | PX SEND HASH | :TQ10000 | 1353K| 81M| 196K (12)| 00:01:17 | | S->P | HASH |

| 10 | INDEX FAST FULL SCAN| PK_FTABLE | 1353K| 81M| 196K (12)| 00:01:17 | | | |

| 11 | PX RECEIVE | | 1353K| 81M| 439K (7)| 00:02:53 | Q1,02 | PCWP | |

| 12 | PX SEND HASH | :TQ10001 | 1353K| 81M| 439K (7)| 00:02:53 | Q1,01 | P->P | HASH |

| 13 | PX BLOCK ITERATOR | | 1353K| 81M| 439K (7)| 00:02:53 | Q1,01 | PCWC | |

|* 14 | INDEX FAST FULL SCAN| UNIQUE_FP_NAME | 1353K| 81M| 439K (7)| 00:02:53 | Q1,01 | PCWP | |

-------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter(ROWNUM=:SYS_B_1)

4 - filter(ROWNUM=:SYS_B_1)

6 - access(ROWID=ROWID)

14 - access(:Z>=:Z AND :Z<=:Z)

filter("FTABLE"."FP_NAME" LIKE :SYS_B_0)

SQL_ID 3sdab0awk8g3c, child number 1

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------------------------------------------------------------------

select fp_id from ftable where ftable.fp_name like :"SYS_B_0" and rownum=:"SYS_B_1"

NOTE: cannot fetch plan for SQL_ID: 3sdab0awk8g3c, CHILD_NUMBER: 1

Please verify value of SQL_ID and CHILD_NUMBER;

It could also be that the plan is no longer in cursor cache (check v$sql_plan)

SQL_ID 3sdab0awk8g3c, child number 2