OUTLINE: Replace Bad Plan with Good one.
Below is the scenario that I used when we deployed new version of the application. We had very bad performance issue because of slow query which was deployed to the production during the new software release. But because we couldn’t change the query until the next maintenance window, I had to use outline to fix the performance issue.
1) Create outline for bad sql.
|
create or replace outline outln1 on select /*+ ordered index( pi IDX_PRICING_INPUT_03 ) */ tr.trade_request_key,pibl.bank_list_name from whitney.pricing_input pi, whitney.trade_request tr, whitney.customer c, whitney.price_input_bank_list pibl, whitney.pricing_round pr, ( select distinct pricing_round_key, nvl(substr(sys_connect_by_path(trade_id, ‘/’), 2, instr(sys_connect_by_path(trade_id, ‘/’), ‘/’, 2)-2), trade_id) path from trade where pricing_round_key is not null connect by prior parent_trade_key = trade_key start with trade_id in (‘B200925702L1R00′) ) ilv where tr.customer_key = c.customer_key and pi.trade_request_key = tr.trade_request_key and pibl.pricing_input_key = pi.pricing_input_key and pr.pricing_input_key = pi.pricing_input_key and pr.pricing_round_key = ilv.pricing_round_key order by tr.trade_request_key / |
2) Create outline for good sql.
|
create or replace outline outln2 on select tr.trade_request_key,pibl.bank_list_name from whitney.pricing_input pi, whitney.trade_request tr, whitney.customer c, whitney.price_input_bank_list pibl, whitney.pricing_round pr, ( select distinct pricing_round_key, nvl(substr(sys_connect_by_path(trade_id, ‘/’), 2, instr(sys_connect_by_path(trade_id, ‘/’), ‘/’, 2)-2), trade_id) path from trade where pricing_round_key is not null connect by prior parent_trade_key = trade_key start with trade_id in (‘B200925702L1R00′) ) ilv where tr.customer_key = c.customer_key and pi.trade_request_key = tr.trade_request_key and pibl.pricing_input_key = pi.pricing_input_key and pr.pricing_input_key = pi.pricing_input_key and pr.pricing_round_key = ilv.pricing_round_key order by tr.trade_request_key / |
3. Overwrite bad outline with the contents of good outline.
|
rem rem This script overwrite bad outline with good one. rem update outln.ol$ delete from outln.ol$hints update outln.ol$hints delete from outln.ol$nodes update outln.ol$nodes commit |
4. Issue the command for the users to use stored outline.
SQL> alter system set use_stored_outlines = true;
5. Check the plan if the bad sql is now using good plan.
Copy archive logs through ternel
I have had some situation which I don’t have connection to the third(remote) backup database and had to workaround through ssh ternel. Here is the example,
I have a primary database machine called test1 and wanted to transfer to test3 machine but they don’t have direct connection. There is one machine called gate1 which you can connect from test1 machine and from there you can connect to test3 machine.
test1 –> gate1 –> test3
1. Set up the ternel on test1 machine
# ssh -L 2222:test3:22 user@gate1 -N
user@gete1’s password:
2. Open another window and connect to test1 machine
Below command will forward all archivelogs from test1 machine to test3 machine.
# scp -P 2222 *.arc oracle@localhost:/archive_dest/
Manage OCR(Oracle Cluster Repository)
1. Backup
You must be logged in as the root user, because root owns the OCR files.
- To see the recent copy of OCR file
# ocrconfig -showbackup
export OCR file
# ocrconfig -export /oracle/backup/cluster/backup01.ocr -s online
2. Restore
import OCR file
If you should need to recover this file, the following command can be used.
# ocrconfig -import /oracle/backup/cluster/backup01.ocr
To see whether your OCR’s are in sync and healthy run an ocrcheck, which should return with “succeeded“, like below.
| # ocrcheck Status of Oracle Cluster Registry is as follows : Version : 2 Total space (kbytes) : 280908 Used space (kbytes) : 4612 Available space (kbytes) : 276296 ID : 1065955458 Device/File Name : /dev/raw/ocr01.dat Device/File integrity check succeeded Device/File Name : /dev/raw/ocr02.dat Device/File integrity check succeededCluster registry integrity check succeeded |
Retore from your OCR backup
You system automatically backup the OCR periodically. So, you can just resotre if it you need to.
# ocrconfig -restore /home/app/oracle/product/crs/cdata/crs/backup02.ocr
3. Add or replace OCR files
If you setup only one OCR file during the RAC configuration and need to add additional mirrored file(s). You can apply same rule to convert OCR raw devices into OCR block devices.(Please refer metalink doc 428681.1 for OCR/Vote disk maintenance Operations)
* This command replaces your primary OCR file with /dev/cciss/ocr01.dat
# ocrconfig -replace ocr /dev/cciss/ocr01.dat
* This command replaces your mirrored OCR file with /dev/cciss/ocr02.dat
# ocrconfig -replace ocrmirror /dev/cciss/ocr02.dat
Creating a Single Instance Physical Standby for a RAC Primary
When people configure physical dataguard it is common for most of DBAs that they manually copy datafiles and create standby controlfiles prior to configure any required parameters. But when the primary database become ASM database copying datafiles are not as easy as OS commands and it will be tedious to transfer file by file using oracle provided procedure DBMS_FILE_TRANSFER.COPY.
In addition that the primary database must be force logging mode to avoid potential block corruption when it has nologging option enabled on table level.
SQL> alter database force logging;
This article explains how to create physical dataguard through RMAN specially when the primary database is on RAC and physical dataguard is on filesystem.
1. Perform rman backup and transfer to dataguard machine.
| $ rman target / RMAN> backup device type disk format ‘/backup/rman/%U’ database plus archivelog; RMAN> backup device type disk format ‘/backup/rman/%U’ current controlfile for standby; |
| $ scp /backup/rman/* oracle@dgmachine:/backup/rman/ |
2. Prepare configuration files
2.1 Create service and prepare tnsnames.ora file.
We want seamless failover to the standby database in case if primary RAC is unreachable which mean that we should not change any configuration on both clients and server side to failover or switchover to the physical standby database. This require us to create services on primary and standby database. (This way, you can maintain inentical configuration files on both primary and standby database.)
| (On Primary Server) $ srvctl add service -d FXPRET -s FXPRET_OLTP -r “db01,pret,db02.pret” -P BASIC(On Standby Server) * Add service_names parameter. SQL> alter system set service_names=”FXPRET,FXPRET_STDBY”; |
| $ cat tnsnames.ora FXPRET = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db01-vip.pret)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = db02-vip.pret)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = pret-db3)(PORT = 1521)) (LOAD_BALANCE = yes) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = FXPRET_OLTP) ) )FXPRET_STDBY = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db01-vip.pret)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = db02-vip.pret)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = pret-db3)(PORT = 1521)) (LOAD_BALANCE = yes) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = FXPRET_STDBY) ) ) |
2.2 Prepare password and parameter file.
* To enable secure transmission of redo data, make sure the primary and physical standby databases use a password file, and make sure the password for SYS user is identical on both systems.
$orapwd file=orapwFXPRET password=oracle entries=30
* Add below below dataguard specific parameters on both primary and standby database in case when primary become a standby database.
| *.remote_login_passwordfile=’exclusive’ *.fal_client=’FXPRET_STDBY’ *.fal_server=’FXPRET’ *.log_archive_config=’DG_CONFIG=(FXPRET,FXPRET_STDBY)’ *.standby_archive_dest=’/oracle/archive/FXPRET’ |
Add below physical dataguard specific to the physical dataguard machine’s parameter file.
| *.db_create_file_dest=’/oradata *.db_create_online_log_dest_1=’/oradata *.db_create_online_log_dest_2=’/oradata |
* Create spfile from pfile on physical standby database.
3. Duplicate standby database from the primary database’s backup.
| SQL> startup nomount $ rman target sys/oracle@fxpret auxiliary / RMAN> duplicate target database for standby; |
4. Create standby log files on primary and standby servers.
It is recommended to create standby logfile on both primary and standby servers. It is also recommended to create standby logfile for each thread if your primary is RAC.
| SQL> alter database add standby logfile thread 1; SQL> alter database add standby logfile thread 1; SQL> alter database add standby logfile thread 1; SQL> alter database add standby logfile thread 2; SQL> alter database add standby logfile thread 2; SQL> alter database add standby logfile thread 2; |
5. Start managed recovery mode
| SQL> alter database recover managed standby database using current logfile disconnect from session; |
Multi-table insert statement
A multi-table insert statement allows you to insert the results of a single select statement into multiple tables. Parallel insert statements can take advantage of parallelism and Oracle’s direct-load mechanisms.
Unconditional insert Statements
The unconditional INSERT inserts the rows into all of the tables without any conditions. All inserts statements will execute simultaneously.
INSERT ALL
INTO product_totals VALUES(product_id, today, total)
INTO product_history VALUES(product_id, today, quantity)
SELECT product_id, trunc(sysdate) today,
sum(unit_price*quantity) total, sum(quantity) quantity
FROM orders, order_items
WHERE orders.order_id = order_items.order_id
AND trunc(order_date) = trunc(sysdate)
GROUP BY product_id;
Pivoting insert statements
The pivoting INSERT inserts into the same table several times pivoting from a non-normalized form to a normalized form.
INSERT ALL
INTO sales (product_id, customer_id, week, amount)
VALUES (product_id, customer_id, weekly_start_date, sales_sun)
INTO sales (product_id, customer_id, week, amount)
VALUES (product_id, customer_id, weekly_start_date+1, sales_mon)
INTO sales (product_id, customer_id, week, amount)
VALUES (product_id, customer_id, weekly_start_date+2, sales_tue)
INTO sales (product_id, customer_id, week, amount)
VALUES (product_id, customer_id, weekly_start_date+3, sales_wed)
INTO sales (product_id, customer_id, week, amount)
VALUES (product_id, customer_id, weekly_start_date+4, sales_thu)
INTO sales (product_id, customer_id, week, amount)
VALUES (product_id, customer_id, weekly_start_date+5, sales_fri)
INTO sales (product_id, customer_id, week, amount)
VALUES (product_id, customer_id, weekly_start_date+6, sales_sat)
SELECT product_id, customer_id, weekly_start_date, sales_sun,
sales_mon, sales_tue, sales_wed, sales_thu, sales_fri, sales_sat
FROM sales_summary;
Conditional insert statements
The conditional ALL INSERT evaluates each of the WHEN clauses to determine if the row is inserted into each table. All WHEN clauses are evaluated, so some source rows may be inserted into more than one target while others may not be inserted at all. An optional ELSE clause can also be used.
INSERT ALL
WHEN order_mode=’online’
THEN INTO web_orders
VALUES (order_id, order_date, order_total)
WHEN coupon is not null
THEN INTO coupon_sales
VALUES (order_id, order_date, order_total)
SELECT order_id, order_date, order_total, coupon, order_mode
FROM orders;
Insert first
The conditional FIRST INSERT evaluates each WHEN clause in the order in which it appears in the statement and only executes the INTO clause for the first one which is TRUE. An optional ELSE clause can also be used.
INSERT FIRST
WHEN order_total > 10000 THEN
INTO priority_handling VALUES(order_id)
WHEN order_total > 5000 THEN
INTO special_handling VALUES(order_id)
WHEN order_total > 3000 THEN
INTO privilege_handling VALUES(order_id)
ELSE
INTO regular_handling VALUES(order_id)
SELECT order_id, order_total
FROM orders;
External Table
External table feature was introduced in Oracle9i version. I had tested long time before but didn’t have chance to summarize it.
I have chance to review this feature thesedays and now I decided to put some trace here so that I may be able to revisit this note later.
I am not write the details of the feature in this page since this page is just a note for my memory refresh and nothing to do with any theoretical documentation.
Objective
I will create two external tables called PASSWD and GROUP. These tables will be mapped to /tmp/passwd and /tmp/group file which I copied from /etc directory.
1. Create Directory
SQL> create directory ETC as ‘/tmp’;
SQL> grant read,write on directory ETC to scott; — If the directory is created by other user.
2. Create Tables
SQL> create table passwd_ext — Creating PASSWD_EXT table.
( userid varchar2(30),
password varchar2(10),
usernum number,
groupnum number,
description varchar2(50),
home varchar2(100),
shell varchar2(100))
organization external
( type oracle_loader
default directory etc
access parameters
( records delimited by newline
fields terminated by ‘:’
missing field values are null )
location ( ‘passwd’ ))
/
SQL> create table group_ext — Create GROUP_EXT table
( groupid varchar2(30),
password varchar2(10),
groupnum number,
member varchar2(100))
organization external
( type oracle_loader
default directory etc
access parameters
( records delimited by newline
fields terminated by ‘:’
missing field values are null )
location ( ‘group’ ))
/
3. Query/Join tables
Here I am joining two tables and produces output which show groupname,username and description order by groupname.
SQL> select g.groupid,p.userid,p.description from passwd_ext p, group_ext g
where p.groupnum = g.groupnum
order by groupid
/
GROUPID USERID DESCRIPTION
—————————— —————————— ————————————————–
adm adm adm
bin bin bin
daemon daemon daemon
dba racle Oracle10g
dbus dbus System message bus
ftp ftp FTP User
gopher gopher gopher
haldaemon haldaemon HAL daemon
hpsmh hpsmh
lp lp lp
mail mail mail
…
Conclusion
The external table is very powerful feature that enables you to map your external files to the database and even allow you to manipulate the data by joining either with external or internal table. This will make your job much flexible especially when you deal with ETL type jobs.
SQLServer Summary
sp_dboption
sp_help titles
sp_helptext
sp_spaceused mydb2
sp_spaceused billable_trade
sp_linkedservers
sp_addumpdevice ‘disk’, ‘mydb_dump’, ‘c:\temp\my_dump’
sp_attatch_db ‘pubs’, ‘c:\data\pubs.mdf’, ‘c:\data\pubs_log.ldf’
sp_attachsingle_file_db ‘pubs’ , ‘c:\data\pubs.mdf’
sp_detatch_db ‘pubs’
dbcc opentrans
alter database pubs set recovery full
alter database pubs set single_user with rollback immediate( = sp_dboption pubs,’single user’, true )
alter database pubs set auto_shrink
alter database pubs set recovery simple
> * Change database name
> alter database oldDB set single_user with rollback immediate go
> sp_renamedb ‘oldDB’ , ‘newDB’
> go
> alter database newDB set multi_user with rollback immediate
>
>
> * Show log file size and usage
> dbcc sqlperf (logspace)
>
>
restore database mydb from mydb_device with file=2
restore database mydb from mydb_device move ‘c:\myfile’ to ‘d:\myfile’
move log files to ‘d:\mylog’
==> When log file is full and you have to empty without backup. This command will truncate log.
backup log pubs with no_log
==> This NO_TRUNCATE option can be used even though sqlserver is not in normal status and this command will not delete log even after the backup.
backup log mydb to mydb_dump with no_truncate
Review Oracle’s hidden parameters
set line 180
col name format a40
col value format a20
col deflt format a20
col description format a60
col type format a15
select a.ksppinm name,b.ksppstvl value, b.ksppstdf deflt, decode (a.ksppity, 1, ‘boolean’, 2, ’string’, 3, ‘number’, 4, ‘file’, a.ksppity) type,a.ksppdesc description
from sys.x$ksppi a, sys.x$ksppcv b
where a.indx = b.indx
and a.ksppinm like ‘_%’
order by name
/
PGA_AGGREGATE_TARGET
As you may know that you can define instance level maximum PGA memory size for sort, group by, hash join and bitmap merge to reduce system overhead by setting proper optimal size for SQL working area.
| PGA_AGGREGATE_TARGET | 1G | Instance level total PGA memory amount |
| _PGA_MAX_SIZE | 214732800 | Maximum PGA size that one process can use ( Size in bytes ) |
| _SMM_MAX_SIZE | 104850 | Maximum Work Area size that one process can use ( Size in kilo bytes ) |
| _SMM_PX_MAX_SIZE | 524288 | Total amount of work area size that all slave processes under a PQ coordinator Session can utilize. ( Size in kilo bytes ) |
According to above table, the maximum amount sort_are that can be allocated in case if we define pga_aggregate_target size of 1 giga bytes is 100m.
So, if we are runing a batch jobs that need lots of sorts then we’d better unset PGA_AGGREGATE_TARGET size and maunally set SORT_AREA_SIZE after set WORK_AREA_SIZE=manual or we need to set undocumented parameter “_SMM_MAX_SIZE” to allow oracle to allocate large work area size. If you need to set this value for utilities lik exp/imp or datapump etc, I would like to set database level trigger then you can set above parameter on a specific sessions that you want to.
You can monitor PGA memory usage for a specific process from v$process view.
- PGA_USED_MEM – Current PGA memory size used by a process
- PGA_ALLOC_MEM – Currently allocated PGA memory size including free memory.
- PGA_MAX_MEM – Maximum PGA memory which has assigned to a processes.
select sid,s.username,spid,PGA_USED_MEM,PGA_ALLOC_MEM,PGA_FREEABLE_MEM,PGA_MAX_MEM
from gv$session s, gv$process p
where s.paddr = p.addr
and p.spid in ( 26072, 15862 )
/
Dynamic Resource Mastering
In database releases before 10g (10.1.0.2) once a cache resource is mastered on an instance, a re-mastering or a change in the master would take place only during a reconfiguration that would happen automatically during both normal operations like instance startup or instance shutdown or abnormal events like Node eviction by Cluster Manager. So if Node B is the master of a cache resource, this resource will remain mastered on Node B until reconfiguration.
10g introduces a concept of resource remastering via DRM. With DRM a resource can be re-mastered on another node say from Node B to Node A if it is found that the cache resource is accessed more frequently from Node A. A reconfiguration is no longer the only reason for a resource to be re-mastered.
Oracle 10g Release 1 performs resource re-mastering on file level but from Oracle 10g Release 2 it performs on segment level. You can monitor dynamic resource mastering through V$GCSPMASTER_INFO view.
SQL> select file_id,object_id,current_master,previous_master,remaster_cnt
from v$gcspfmaster_info;
FILE_ID OBJECT_ID CURRENT_MASTER PREVIOUS_MASTER REMASTER_CNT
———- ———- ————– ————— ————
0 65763 1 32767 1
0 65949 1 32767 1
0 91316 1 0 5
0 107842 0 32767 1
You can perform resource mastering manually by using oradebug lkdebug command. Below command changes master of objectid 91316 to the node that you execute this command.
SQL> oradebug lkdebug -m pkey 91316
Hidden Parameters
_GC_AFFINITY_LIMIT – Default value is 50(times) which means that if there is more than 50 times access difference between the nodes for a specific object then it changes master to more frequently accessed node.
_GC_AFFINITY_MINIMUN – Default value is 600(times) which means a object must be accesses at least 600 times per minutes to be qualified as a new master.
_GC_AFFINITY_TIME – It defines how often oracle will execute resource re-mastering. Default is 10(minutes). You can disable resource re-mastering by setting this value to 0.
leave a comment