Archive

Archive for October, 2011

Oracle: Historical Elapsed time by Queries

October 26, 2011 Leave a comment

SELECT SQL_TEXT,ROUND(X.ELAPSED_TIME) FROM DBA_HIST_SQLTEXT DHST,
(
SELECT DHSS.SQL_ID SQL_ID, SUM(DHSS.ELAPSED_TIME_DELTA/greatest(DHSS.EXECUTIONS_DELTA,1)/1000000) ELAPSED_TIME
FROM DBA_HIST_SQLSTAT DHSS
WHERE DHSS.SNAP_ID IN
(
SELECT SNAP_ID FROM DBA_HIST_SNAPSHOT WHERE
BEGIN_INTERVAL_TIME > to_date(’10/26/2011 08:59:00′,’mm/dd/yyyy hh24:mi:ss’) AND
END_INTERVAL_TIME < to_date('10/26/2011 10:01:00','mm/dd/yyyy hh24:mi:ss')

)
GROUP BY DHSS.SQL_ID) X
WHERE X.SQL_ID=DHST.SQL_ID ORDER BY X.ELAPSED_TIME DESC;

You can get the snapshot id using following query.
select * from DBA_HIST_SNAPSHOT order by BEGIN_INTERVAL_TIME desc

Advertisements
Categories: Oracle Tags:

How to generate AWR report if no access to Enterprise Manager

October 26, 2011 Leave a comment

Here is how you can generate AWR report through backend if you have no access to Enterprise Manager or browser.

Login to DB server.
cd $ORACLE_HOME/rdbms/admin
sqlplus / as sysdba
@awrrpt.sql

Example:
____________________________

$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 – Production on Wed Oct 26 16:00:29 2011

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, Data Mining and Real Application Testing options

SQL> @awrrpt

Current Instance
~~~~~~~~~~~~~~~~

DB Id DB Name Inst Num Instance
———– ———— ——– ————
793887637DEVDB1 1 DEVDB1

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter ‘html’ for an HTML report, or ‘text’ for plain text
Defaults to ‘html’
Enter value for report_type: text

Type Specified: text

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id Inst Num DB Name Instance Host
———— ——– ———— ———— ————
* 793887637 1 DEVDB1 DEVDB1 usamt75uxpro
d01

Using 793887637for database Id
Using 1 for instance number

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing without
specifying a number lists all completed snapshots.

Enter value for num_days: 1

Listing the last day’s Completed Snapshots

Snap
Instance DB Name Snap Id Snap Started Level
———— ———— ——— —————— —–
DEVDB1 DEVDB1 24778 26 Oct 2011 00:00 1
24779 26 Oct 2011 01:00 1
24780 26 Oct 2011 02:00 1
24781 26 Oct 2011 03:01 1
24782 26 Oct 2011 04:00 1
24783 26 Oct 2011 05:00 1
24784 26 Oct 2011 06:00 1
24785 26 Oct 2011 07:00 1
24786 26 Oct 2011 08:00 1
24787 26 Oct 2011 09:00 1
24788 26 Oct 2011 10:00 1
24789 26 Oct 2011 11:00 1
24790 26 Oct 2011 12:00 1
24791 26 Oct 2011 13:00 1
24792 26 Oct 2011 14:00 1
24793 26 Oct 2011 15:00 1
24794 26 Oct 2011 16:00 1

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 24788
Begin Snapshot Id specified: 24788

Enter value for end_snap: 24789
End Snapshot Id specified: 24789

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_24788_24789.txt. To use this name,
press to continue, otherwise enter an alternative.

Enter value for report_name: oct_26_2011_10am_to_11am

Categories: Oracle Tags:

Oracle: Which Top 10 queries are taking Time NOW

October 26, 2011 Leave a comment

SELECT * FROM
(SELECT
sql_fulltext,
elapsed_time/executions/1000000,
executions,
last_load_time,
LAST_ACTIVE_TIME
FROM v$sql
Where executions > 0
ORDER BY elapsed_time desc )
WHERE ROWNUM < 11

Categories: Oracle Tags:

Free Great Book on PowerShell

October 21, 2011 Leave a comment

This is a great easy to read and follow book on Powershell. Read on

Mastering-PowerShell.pdf

Categories: Powershell Tags:

SSRS: List Dataset associated with Report parameters

October 20, 2011 Leave a comment

;WITH
XMLNAMESPACES
(DEFAULT ‘http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition&#8217;
,’http://schemas.microsoft.com/sqlserver/reporting/reportdesigner&#8217;
AS rd)
,DEF AS
(SELECT RPT.ReportPath ,
ISNULL
((ISNULL(R.RptNode.value(‘(./DefaultValue/DataSetReference/DataSetName)[1]’, ‘nvarchar(425)’),R.RptNode.value(‘(./ValidValues/DataSetReference/DataSetName)[1]’, ‘nvarchar(425)’))),
R.RptNode.value(‘(./DefaultValue/Values/Value)[1]’, ‘nvarchar(425)’))
AS DataSetName
,R.RptNode.value(‘@Name[1]’, ‘nvarchar(425)’) AS ParameterName
FROM (SELECT RPT.Path AS ReportPath
,RPT.name AS ReportName
,CONVERT(xml, CONVERT(varbinary(max), RPT.content)) AS contentXML
FROM [ReportServer$SQLEXPRESS].[dbo].[Catalog] AS RPT
WHERE RPT.Type = 2 — 2 = Reports
) AS RPT
CROSS APPLY RPT.contentXML.nodes(‘/Report/ReportParameters/ReportParameter’) AS R(RptNode)
)
SELECT DEF.ReportPath
,DEF.ParameterName
,DEF.DataSetName
FROM DEF
— Optional filter:
–WHERE DEF.ReportPath LIKE ‘Report_PATH’
ORDER BY DEF.ReportPath
,DEF.ParameterName
,DEF.DataSetName

Categories: SSRS Tags:

Powershell: Function to Query SQL DB and return Array

October 20, 2011 Leave a comment

# Functions used for repeated Query tasks
function exec-query ($SqlQuery)
{
$SQLServer = “10.100.99.99\SQLEXPRESS” #use Server\Instance for named SQL instances!
$SQLDBName = “ReportServer$SQLEXPRESS”
$ReportArray = @()

$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = “Server = $SQLServer; Integrated Security = True”
$Connection.Open()

$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
$Command.CommandText = $SqlQuery

$Reader = $Command.ExecuteReader()
$Counter = $Reader.FieldCount

while ($Reader.Read())
{
for ($i = 0; $i -lt $Counter; $i++)
{
$ReportArray+=$Reader.GetValue($i)
}
}
$Connection.Close()
Return $ReportArray
}

# Main starts here
$OutArray = @()
$OutArray2 = @()

$InQuery = “Select PATH FROM [ReportServer`$SQLEXPRESS].[dbo].[Catalog]
where Type = 2
and PATH like ‘%/My Dashboard/%’
and PATH not like ‘%SUB%’
and PATH in (‘/People Management/My Dashboard/Data_Summary’,’/People Management/My Dashboard/Data_Achieved_YTD’)”

$OutArray=exec-query($InQuery)

for ($i = -1; $i -lt ($OutArray.length-1); $i++)
{
$param=$OutArray[$i]
$InQuery2 = “;WITH DEF AS (SELECT R.RptNode.value(‘Name[1]’, ‘nvarchar(425)’) AS [Name] FROM (SELECT RPT.name AS ReportName
,CONVERT(xml, RPT.Parameter) AS contentXML FROM [ReportServer`$SQLEXPRESS].[dbo].[Catalog] AS RPT
WHERE RPT.Type = 2 AND RPT.PATH LIKE ‘$param’ ) AS RPT CROSS APPLY RPT.contentXML.nodes(‘/Parameters/Parameter’) AS R(RptNode)
) SELECT * FROM DEF”

$OutArray2=exec-query($InQuery2)
for ($j = -1; $j -lt ($OutArray2.length-1); $j++)
{
Write $OutArray2[$j]
}
}

Categories: Powershell Tags: ,

Facebook MySQL DBA gives tips on server management

October 17, 2011 Leave a comment
Categories: Server Mgmt Tags: