My Oracle Support Banner

v$asm_alias query slow when using CONNECT BY PRIOR (Doc ID 2878585.1)

Last updated on JUNE 30, 2022

Applies to:

Oracle Database - Enterprise Edition - Version 18.0.0.0 and later
Information in this document applies to any platform.

Symptoms

 Below query is running slow when executed while database is in open mode.

SELECT gnum,filnum,concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path
FROM (SELECT g.name gname, a.parent_index pindex, a.name aname,
a.reference_index rindex,a.group_number gnum,a.file_number filnum
FROM v$asm_alias a, v$asm_diskgroup g
WHERE a.group_number = g.group_number)
START WITH (mod(pindex, power(2, 24))) = 0 CONNECT BY PRIOR rindex = pindex

0 This query runs slow irrespective of number of files
0 It works fine when excuted while database is mount mode.
0 it works fine when executed on ASM Instance.

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.