On which object is my query stuck (spending) time? Using v$session_wait and dba_extents

How to find the object name where our query is spending time?
In case the wait event in v$session_wait is "file sequential read", we can use the join below, to identify the index_name:



SQL> select segment_name,segment_type,owner,tablespace_name from
    dba_extents,v$session_wait
    where file_id=p1
   and p2 between block_id and block_id + blocks -1
   and sid=4933;

SEGMENT_NAME                                                                      SEGMENT_TYPE       OWNER                          TABLESPACE_NAME
--------------------------------------------------------------------------------- ------------------ ------------------------------ ------------------------------
TABLE_TEST_4IX                                                               INDEX PARTITION    MY_USER                        APL_LARGE_IX

invoker_rights_clause to the rescue

The issue: we are trying to call a stored procedure defined in a different user, using a synonym, to truncate a table in our account. This is failing, since by default the procedure is ran using the "definer rights".

The solution: define the procedure/package to run using "invoker rights", as below:

CREATE PACKAGE     "TRUNC_TAB" AUTHID CURRENT_USER AS
    PROCEDURE TRUNCATE_TABLE (i_table_name in varchar2);
    PROCEDURE TRUNCATE_PARTITION (i_table_name in varchar2,i_partition_name  in varchar2);
END TRUNC_TAB ;
/

How to get run time statistics for a query?

Step 1:
Add the hint gather_plan_statistics to the select statement:


SELECT /*+ GATHER_PLAN_STATISTICS */
name,address,code
from address_name_table;

Step 2:
Generate the plan and the run time statistics:


select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));

How to export/import a very big compressed table?

The challenge here is to transfer a very big table from one DB to another, both fast and using as little space as possible. Since the table is compressed in the source DB, we'll need to take advantage of this.
The table will stay compressed throughout the process if we'll use expdp and impdp, with the option compression=ALL.
See example below of par file for expdp:



 >cat expdp.par
userid=myuser/mypass
dumpfile=big_dir:big_table_1011.dmp
logfile=big_dir:big_table_1011.exp.log
compression=ALL


 To check expdp status:

 expdp myuser/mypass attach=SYS_EXPORT_SCHEMA_01

 Export>Status

How to find out CPU details on the UNIX box?

# of CPUs + details:

vi /proc/cpuinfo

How to pause UNIX script and wait until we press ?

Sometimes the above scenario makes sense, we want to check some log files before proceeding with the next step.
The solution is to use:

"read -p"

More details in the above nice blog entry:

http://www.cyberciti.biz/tips/linux-unix-pause-command.html

Where is the "glance" utility located on Linux?

/opt/perf/bin/glance