zip -sf myzipfile.zip
zipinfo myzipfile.zip
Minimalistic Oracle contains a collection of practical examples from my encounters with Oracle technologies. When relevant, I also write about other technologies, like Linux or PostgreSQL. Many of the posts starts with "how to" since they derive directly from my own personal experience. My goal is to provide simple examples, so that they can be easily adapted to other situations.
SET SERVEROUTPUT ON DECLARE l_plans_altered PLS_INTEGER; BEGIN l_plans_altered := DBMS_SPM.alter_sql_plan_baseline( sql_handle => 'SQL_7ff180a4583f257d', plan_name => 'SQL_PLAN_7zwc0njc3y9bx2c993bf4', attribute_name => 'fixed', attribute_value => 'YES'); DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered); END; /The details for the plan baselines can be found like this:
SELECT sql_handle,plan_name,origin,enabled,accepted,fixed FROM dba_sql_plan_baselines WHERE plan_name ='SQL_PLAN_7zwc0njc3y9bx2c993bf4';Result:
SQL_HANDLE | PLAN_NAME | ORIGIN | ENABLED | ACCEPTED | FIXED |
---|---|---|---|---|---|
SQL_7ff180a4583f257d | SQL_PLAN_7zwc0njc3y9bx2c993bf4 | AUTO-CAPTURE | YES | YES | YES |
SELECT TO_CHAR(LAST_ACTIVE_TIME, 'dd.mm.yyyy hh24:mi') "last active", SQL_ID, PLAN_HASH_VALUE, SQL_PLAN_BASELINE "BASELINE", SQL_PROFILE, IS_RESOLVED_ADAPTIVE_PLAN, CHILD_NUMBER AS "Child Num", ELAPSED_TIME, EXECUTIONS, ROUND(ELAPSED_TIME/1000000) "duration (sec)" ,CASE WHEN EXECUTIONS > 0 THEN ROUND( (ELAPSED_TIME/EXECUTIONS)/1000000, 1) ELSE NULL END "sec per exe" FROM V$SQL WHERE SQL_ID in('2mympbsn3r4rk') ORDER BY sql_id,LAST_ACTIVE_TIME DESC;Result:
last active | SQL_ID | PLAN_HASH_VALUE | BASELINE | SQL_PROFILE | IS_RESOLVED_ADAPTIVE_PLAN | Child Num | ELAPSED_TIME | EXECUTIONS | duration (sec) | sec per exe |
---|---|---|---|---|---|---|---|---|---|---|
04.12.2020 09:49 | 2mympbsn3r4rk | 480132689 |
SQL_PLAN_7zwc0njc3y9bx3bf43977 | 1 |
612838711 |
116 |
613 |
5,3 |
||
04.12.2020 09:49 | 2mympbsn3r4rk | 3102497174 |
SQL_PLAN_7zwc0njc3y9bx2c993bf4 | Y | 2 |
203961 |
191 |
0 |
0 |
|
04.12.2020 08:49 | 2mympbsn3r4rk | 480132689 |
SQL_PLAN_7zwc0njc3y9bx3bf43977 | 0 |
2247452482 |
30 |
2247 |
74,9 |
action_type | to_char | count -----------------+-------------+------- action1 | 27 Oct 2020 | 47831 action1 | 22 Oct 2020 | 640 action1 | 20 Oct 2020 | 1 action1 | 22 Oct 2020 | 1654 action1 | 26 Oct 2020 | 290 action2 | 21 Oct 2020 | 8 action2 | 27 Oct 2020 | 140 action2 | 26 Oct 2020 | 900
SQL> alter system set control_files='/oradata/proddb01/control01.ctl, /fra/proddb01/control02.ctl' scope=spfile; System altered.During mount stage, this will give you the following message in the database's alert log:
ORA-205 signalled during: ALTER DATABASE MOUNT... ORA-00202: control file: /oradata/proddb01/control01.ctl, /fra/proddb01/control02.ctl ORA-27037: cannot obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 7Works:
SQL> alter system set control_files='/oradata/proddb01/control01.ctl','/fra/proddb01/control02.ctl' scope=spfile; System altered.You should enclose both values within ' ' apostroph characters.
select * from dba_sql_profiles order by created;The query above will return the SQL Profiles in the order in which they were created. Notice the name of the relevant one, as this will be used as an argument in the procedures later.
vi 2.cre_SQLProfile_stgtab.sqlAdd the following:
connect dbadmin BEGIN DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF ( table_name => 'MYSTAGETAB_SQLPROF' , schema_name => 'DBADMIN' ); END; / exitExecute the file:
sqlplus /nolog @2.cre_SQLProfile_stgtab.sql3. Pack your SQL Profile into your staging table
vi 3.pack_SQLProfile_stgtab.sqlAdd the following:
conn dbadmin alter session set nls_language='american'; BEGIN DBMS_SQLTUNE.PACK_STGTAB_SQLPROF ( profile_name => 'SYS_SQLPROF_014fb0ca24980001' , staging_table_name => 'MYSTAGETAB_SQLPROF' , staging_schema_owner => 'DBADMIN' ); END; / exitRun the file:
sqlplus /nolog @3.pack_SQLProfile_stgtab.sqlLet's verify that the staging table now has some rows in it:
connect dbadmin select count(*) from MYSTAGETAB_SQLPROF;
create directory DP as '/exports/';Then export the table:
expdp parfile=4.expdp_SQLPROFILE_stgtab.sqlLogon to the destination server.
vi 5.impdp_SQLProfile.parAdd the following:
userid=dbadmin tables=MYSTAGETAB_SQLPROF logfile=impdp_MYSTAGETAB_SQLPROF.log dumpfile=MYSTAGETAB_SQLPROF.dmp job_name=imp_MYSTAGETAB_SQLPROF directory=DPRun the import:
impdp parfile=5.impdp_SQLProfile.par6. Unpack the information in the staging table
vi 6.unpack.sqlAdd the following:
conn dbadmin alter session set nls_language='american'; set serveroutput on BEGIN DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF( replace => true , staging_table_name => 'MYSTAGETAB_SQLPROF' , profile_name => 'SYS_SQLPROF_014fb0ca24980001' ); END; / exitExecute the file:
sqlplus /nolog @6.unpack.sql
SELECT TO_CHAR(LAST_ACTIVE_TIME, 'dd.mm.yyyy hh24:mi') "last active", SQL_ID, PLAN_HASH_VALUE, SQL_PLAN_BASELINE "BASELINE", SQL_PROFILE, IS_RESOLVED_ADAPTIVE_PLAN, CHILD_NUMBER AS "Child Num", ELAPSED_TIME, EXECUTIONS, ROUND(ELAPSED_TIME/1000000) "duration (sec)" ,CASE WHEN EXECUTIONS > 0 THEN ROUND( (ELAPSED_TIME/EXECUTIONS)/1000000, 1) ELSE NULL END "sec per exe" FROM V$SQL WHERE SQL_ID in('5cbuqgt5m5961') ORDER BY LAST_ACTIVE_TIME DESC;Output:
last active | SQL_ID | PLAN_HASH_VALUE | BASELINE | SQL_PROFILE | AdaptivePlan | Child Num | ELAPSED_TIME | EXECUTIONS | duration (sec) | sec per exe |
---|---|---|---|---|---|---|---|---|---|---|
21.10.2020 13:19 | 5cbuqgt5m5961 | 1355560190 |
1 |
20850937263 |
1488 |
20851 |
14 |
|||
21.10.2020 12:55 | 5cbuqgt5m5961 | 1355560190 |
0 |
112190741129 |
27134 |
112191 |
4,1 |
|||
21.10.2020 14:47 | 5cbuqgt5m5961 | 3389950398 |
SQL_PLAN_ds58srfg86uf4f5a21fb1 | SYS_SQLPROF_014fb0ca24980001 | 3 |
116651 |
302 |
0 |
0 |
SELECT TO_CHAR(LAST_ACTIVE_TIME, 'dd.mm.yyyy hh24:mi') "last active", SQL_ID, PLAN_HASH_VALUE, SQL_PLAN_BASELINE "BASELINE", SQL_PROFILE, IS_RESOLVED_ADAPTIVE_PLAN "AdaptivePlan", CHILD_NUMBER AS "Child Num", ELAPSED_TIME, EXECUTIONS, ROUND(ELAPSED_TIME/1000000) "duration (sec)" ,CASE WHEN EXECUTIONS > 0 THEN ROUND( (ELAPSED_TIME/EXECUTIONS)/1000000, 1) ELSE NULL END "sec per exe" FROM V$SQL WHERE SQL_ID in('5cbuqgt5m5961') ORDER BY LAST_ACTIVE_TIME DESC;
last active | SQL_ID | PLAN_HASH_VALUE | BASELINE | SQL_PROFILE | AdaptivePlan | Child Num | ELAPSED_TIME | EXECUTIONS | duration (sec) | sec per exe |
---|---|---|---|---|---|---|---|---|---|---|
21.10.2020 13:34 | 5cbuqgt5m5961 | 3389950398 |
SQL_PLAN_ds58srfg86uf4f5a21fb1 | SYS_SQLPROF_014fb0ca24980001 | 1 |
210182320 |
1402169 |
210 |
0 |
last active | SQL_ID | PLAN_HASH_VALUE | BASELINE | SQL_PROFILE | AdaptivePlan | Child Num | ELAPSED_TIME | EXECUTIONS | duration (sec) | sec per exe |
---|---|---|---|---|---|---|---|---|---|---|
21.10.2020 10:47 | 5cbuqgt5m5961 | 1355560190 |
0 |
110508458900 |
27032 |
110508 |
4,1 |
vi 1.cre_stagetab.sqlAdd the following:
connect dbadmin BEGIN DBMS_SPM.CREATE_STGTAB_BASELINE ( table_name => 'MYSTAGETAB'); END; /Execute the file:
sqlplus /nolog @1.cre_stagetab.sqlStill in the source database, check who the owner of the baseline is:
SELECT plan_name, sql_handle, creator, origin, TO_CHAR (created, 'dd.mm.yyyy hh24:mi') "created", TO_CHAR (last_executed, 'dd.mm.yyyy hh24:mi') "lastexecuted", enabled, accepted, fixed, autopurge, module, ROUND (ELAPSED_TIME / 1000000) "duration in seconds", cpu_time, buffer_gets, disk_reads FROM DBA_SQL_PLAN_BASELINES where plan_name='SQL_PLAN_ds58srfg86uf4f5a21fb1';The output shows that the owner is SYS:
PLAN_NAME | SQL_HANDLE | CREATOR | ORIGIN | created | lastexecuted | ENABLED | ACCEPTED | FIXED | AUTOPURGE | MODULE | duration in seconds | CPU_TIME | BUFFER_GETS | DISK_READS |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
SQL_PLAN_ds58srfg86uf4f5a21fb1 | SQL_dc1518bb9e8369c4 | SYS | MANUAL-LOAD-FROM-STS | 30.03.2020 15:13 | 18.10.2020 03:13 | YES | YES | YES | YES | JDBC Thin Client | 8 |
3897100 |
597270 |
715 |
vi 2.pack_stagetab.sqlAdd the following:
conn dbadmin alter session set nls_language='american'; DECLARE v_plan_cnt NUMBER; BEGIN v_plan_cnt := DBMS_SPM.PACK_STGTAB_BASELINE ( table_name => 'MYSTAGETAB' , enabled => 'yes' , creator => 'SYS' , plan_name => 'SQL_PLAN_ds58srfg86uf4f5a21fb1' ); END; / exitRun the file:
sqlplus /nolog @2.pack_stagetab.sqlLet's verify that the staging table has some rows in it:
connect dbadmin select count(*) from MYSTAGETAB;3. Create an export parameter file:
vi 3.expdp.parAdd the following. Make sure the directory DP exist and is writable:
userid=dbadmin tables=MYSTAGETAB logfile=expdp_MYSTAGETAB.log dumpfile=MYSTAGETAB.dmp job_name=exp_MYSTAGETAB directory=DPRun the export:
expdp parfile=3.expdp.par
vi 4.impdp.parAdd the following. Make sure the directory DP exist and is writable:
userid=dbadmin tables=MYSTAGETAB logfile=impdp_MYSTAGETAB.log dumpfile=MYSTAGETAB.dmp job_name=imp_MYSTAGETAB directory=DPRun the import:
impdp parfile=4.impdp.par5. Create the file for unpacking the SQL Plans to the local SMB:
vi 5.unpack.sqlAdd the following:
conn dbadmin alter session set nls_language='american'; set serveroutput on DECLARE v_plan_cnt NUMBER; BEGIN v_plan_cnt := DBMS_SPM.UNPACK_STGTAB_BASELINE ( table_name => 'MYSTAGETAB' ,plan_name => 'SQL_PLAN_ds58srfg86uf4f5a21fb1' ); DBMS_OUTPUT.put_line('Number of plans loaded: ' || v_plan_cnt); END; / exitRun the file:
sqlplus /nolog @5.unpack.sqlYou should now be able to see a different execution plan when executing the query at the top of this article:
last active | SQL_ID | PLAN_HASH_VALUE | BASELINE | SQL_PROFILE | AdaptivePlan | Child Num | ELAPSED_TIME | EXECUTIONS | duration (sec) | sec per exe |
---|---|---|---|---|---|---|---|---|---|---|
21.10.2020 13:19 | 5cbuqgt5m5961 | 1355560190 |
1 |
20850937263 |
1488 |
20851 |
14 |
|||
21.10.2020 12:55 | 5cbuqgt5m5961 | 1355560190 |
0 |
112190741129 |
27134 |
112191 |
4,1 |
Clone Volume: true Clone Parent server Name: myserver1-cluster49 SnapClone Parent Volume: myvolume_mirror1
cat clonetext.txt | grep Clone Clone Volume: true Clone Parent server Name: myserver1-cluster49 SnapClone Parent Volume: myvolume_mirror1Grepping for the 4th column helps, but you still get two rows, not just the single one you're interested in:
oracle@oric-db01:[mydb01]# cat clonetext.txt | grep Clone | awk -F' ' '{print $4}' Name: myvolume_mirror1Adding the NR flag to your command solves the problem:
cat clonetext.txt | grep Clone | awk -F' ' 'NR==3 {print $4}'
myvolume_mirror1